'Data truncated for column string_value' after upg

[sarfata]

Hi,

I recently upgraded our Alfresco from 1.2.1 to 1.3 community edition.

I fixed the SQL script following instructions in wiki, and I had to disable "patch.updateForumsIcons" because alfresco crashed when starting (I read on this forum that this should be fixed in 1.4).


Sometimes (I have not been able to determine why), we can't upload a file or create a new space. Error is :

14:46:19,018 ERROR [org.hibernate.util.JDBCExceptionReporter] Data truncation: Data truncated for column 'string_value' at row 1
14:46:19,042 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

I thought string_value was not wide enough but it's a TEXT column. We are running mysql 4.1. Is TEXT type different in 5.0 ?

Anybody else seen this before ?

Thanks,
thomas

Full Stack Trace :
14:46:19,018 ERROR [org.hibernate.util.JDBCExceptionReporter] Data truncation: Data truncated for column 'string_value' at row 1
14:46:19,042 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)
        at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1026)
        at org.hibernate.action.CollectionRecreateAction.execute(CollectionRecreateAction.java:26)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:143)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
        at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.flush(HibernateNodeDaoServiceImpl.java:124)
        at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.flush(AlfrescoTransactionSupport.java:528)
        at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.beforeCommit(AlfrescoTransactionSupport.java:589)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:657)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:482)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:469)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:266)
        at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:397)
        at org.alfresco.web.bean.dialog.BaseDialogBean.finish(BaseDialogBean.java:85)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
        at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:63)
        at javax.faces.component.UICommand.broadcast(UICommand.java:106)
        at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:90)
        at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:164)
        at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:316)
        at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
        at javax.faces.webapp.FacesServlet.service(FacesServlet.java:105)
        at org.alfresco.web.app.servlet.AlfrescoFacesServlet.service(AlfrescoFacesServlet.java:49)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:73)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
        at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
        at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754)
        at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684)
        at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: Data truncation: Data truncated for column 'string_value' at row 1
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1093)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:851)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
        … 51 more
14:46:19,189 ERROR [org.alfresco.util.transaction.SpringAwareUserTransaction] Transaction didn't commit
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)
        at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1026)
        at org.hibernate.action.CollectionRecreateAction.execute(CollectionRecreateAction.java:26)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:143)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
        at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.flush(HibernateNodeDaoServiceImpl.java:124)
        at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.flush(AlfrescoTransactionSupport.java:528)
        at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.beforeCommit(AlfrescoTransactionSupport.java:589)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:657)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:482)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:469)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:266)
        at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:397)
        at org.alfresco.web.bean.dialog.BaseDialogBean.finish(BaseDialogBean.java:85)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
        at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:63)
        at javax.faces.component.UICommand.broadcast(UICommand.java:106)
        at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:90)
        at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:164)
        at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:316)
        at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
        at javax.faces.webapp.FacesServlet.service(FacesServlet.java:105)
        at org.alfresco.web.app.servlet.AlfrescoFacesServlet.service(AlfrescoFacesServlet.java:49)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:73)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
        at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
        at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:754)
        at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:684)
        at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:876)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: Data truncation: Data truncated for column 'string_value' at row 1
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1093)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:851)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
        … 51 more

[derek]

Hi,

Under normal use, I would not expect to see this issue.  Certainly, the fact that you can't even create a simple file or folder indicates that there is something fundamentally wrong with the table.

In order to resolve this, could you post the following info, please:

SQL: describe node_properties;
What is the engine, charset and collation type for the table?
Turn on SQL statement logging in hibernate-cfg.properties.
Details of the JDBC driver you are using.
What happens if you alter the string_value column to varchar?
What happens if you use use MySQL 5 with JConnector 5.0.3?

Regards

[sarfata]

Hi,

We can post some files. This bug shows up randomly. It seems to be file dependant and not space or user dependant.

mysql> describe node_properties;
+——————–+————–+——+—–+———+——-+
| Field              | Type         | Null | Key | Default | Extra |
+——————–+————–+——+—–+———+——-+
| node_id            | bigint(20)   |      | PRI | 0       |       |
| actual_type        | varchar(15)  |      |     |         |       |
| multi_valued       | tinyint(1)   |      |     | 0       |       |
| persisted_type     | varchar(15)  |      |     |         |       |
| boolean_value      | tinyint(1)   | YES  |     | NULL    |       |
| long_value         | bigint(20)   | YES  |     | NULL    |       |
| float_value        | float        | YES  |     | NULL    |       |
| double_value       | double       | YES  |     | NULL    |       |
| string_value       | text         | YES  |     | NULL    |       |
| serializable_value | blob         | YES  |     | NULL    |       |
| qname              | varchar(200) |      | PRI |         |       |
+——————–+————–+——+—–+———+——-+
11 rows in set (0.00 sec)

From a mysql dump (Engine=innodb ; charset=utf8) :
–
– Table structure for table `node_properties`
–

DROP TABLE IF EXISTS `node_properties`;
CREATE TABLE `node_properties` (
  `node_id` bigint(20) NOT NULL default '0',
  `actual_type` varchar(15) NOT NULL default '',
  `multi_valued` tinyint(1) NOT NULL default '0',
  `persisted_type` varchar(15) NOT NULL default '',
  `boolean_value` tinyint(1) default NULL,
  `long_value` bigint(20) default NULL,
  `float_value` float default NULL,
  `double_value` double default NULL,
  `string_value` text,
  `serializable_value` blob,
  `qname` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`node_id`,`qname`),
  KEY `FKC962BF907F2C8017` (`node_id`),
  CONSTRAINT `FKC962BF907F2C8017` FOREIGN KEY (`node_id`) REFERENCES `node` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


JDBC Driver is : mysql-connector-java-3.1.13-bin.jar

With show_sql=true :
Hibernate: insert into node (protocol, identifier, uuid, type_qname, acl_id) values (?, ?, ?, ?, ?)
Hibernate: insert into node_status (node_id, change_txn_id, protocol, identifier, guid) values (?, ?, ?, ?, ?)
Hibernate: insert into child_assoc (parent_node_id, child_node_id, type_qname, qname, is_primary, assoc_index) values (?, ?, ?, ?, ?, ?)
Hibernate: select properties0_.node_id as node1_0_, properties0_.actual_type as actual2_0_, properties0_.multi_valued as multi3_0_, properties0_.persisted_type as persisted4_0_,
properties0_.boolean_value as boolean5_0_, properties0_.long_value as long6_0_, properties0_.float_value as float7_0_, properties0_.double_value as double8_0_, properties0_.str
ing_value as string9_0_, properties0_.serializable_value as seriali10_0_, properties0_.qname as qname0_ from node_properties properties0_ where properties0_.node_id=?
Hibernate: update node_status set node_id=?, change_txn_id=? where protocol=? and identifier=? and guid=?
Hibernate: update node_properties set actual_type=?, multi_valued=?, persisted_type=?, boolean_value=?, long_value=?, float_value=?, double_value=?, string_value=?, serializable
_value=? where node_id=? and qname=?
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into node_properties (node_id, qname, actual_type, multi_valued, persisted_type, boolean_value, long_value, float_value, double_value, string_value, serializab
le_value) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
12:29:49,190 ERROR [hibernate.util.JDBCExceptionReporter] Data truncation: Data truncated for column 'string_value' at row 1
12:29:49,191 ERROR [event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

…

and upgrading to connector/j 5 solved my problem … shame on me.

Thanks a lot for your help, and a great open source tool.

Regards,
thomas

[derek]

You're welcome.