help! Error after migrating from HSQL -> MySQL -> Orac

[gjw]

A standard all-in-one Alfresco 2.0.1E/WCM 2.0.1/HSQL combination was running on a pc. Moved entire directory to another test pc. Alfresco and WCM component ran fine with configuration left as is. When migrated from HSQL -> Oracle 10g (2 step migration - HSQL to MySQL using MySQL Migration tool, as advised on Alfresco Wiki, and then from MySQL -> Oracle 10g using Oracle SQL Developer Migration Wizard).

10:21:53,626 ERROR [org.alfresco.util.transaction.SpringAwareUserTransaction] Transaction didn't commit
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
   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:281)
   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.flush(AlfrescoTransactionSupport.java:569)
   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.beforeCommit(AlfrescoTransactionSupport.java:630)
   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:405)
   at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:186)
   at org.alfresco.repo.transaction.TransactionUtil.executeInUserTransaction(TransactionUtil.java:82)
   at org.alfresco.repo.descriptor.DescriptorServiceImpl.onBootstrap(DescriptorServiceImpl.java:182)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3763)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4211)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:809)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:698)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1122)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1021)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1013)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
   at org.apache.catalina.core.StandardService.start(StandardService.java:450)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:709)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:551)
   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.catalina.startup.Bootstrap.start(Bootstrap.java:294)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:432)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (ALFRESCO_MYSQL.PRIMARY_13) violated

   at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
   at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10720)
   at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
   … 48 more

[gjw]

The ALFRESCO_MYSQL.PRIMARY_13 is the primary key constraint for the table ALF_TRANSACTION

[derek]

Hi,
Check where the hibernate_sequence is.  My guess is that it is dishing out low and clashing IDs.
Regards

[gjw]

I checked the hibernate_sequence in oracle (where is it stored in MySQL since there is no sequences in MySQL?) and the number was much higher than any of the id's in the tables… so would not appear to be from clashing id's…

The exception I get when running against MySQL is:

15:36:42,882 ERROR [org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/alfresco]] Exception sending context initialized event to listener instance of class org.alfresco.web.app.ContextListener
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [org.alfresco.repo.domain.hibernate.TransactionImpl]; uncategorized SQLException for SQL [insert into alf_transaction (server_id, change_txn_id) values (?, ?)]; SQL state [HY000]; error code [1364]; Field 'ID' doesn't have a default value; nested exception is java.sql.SQLException: Field 'ID' doesn't have a default value
java.sql.SQLException: Field 'ID' doesn't have a default value
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
            at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1160)
            at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:685)
            at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1400)
            at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1314)
            at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1299)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
            at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:1968)
            at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2405)
            at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:37)
            at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
            at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:269)
            at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
            at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
            at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
            at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
            at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
            at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
            at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
            at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:530)
            at org.hibernate.impl.SessionImpl.save(SessionImpl.java:518)
            at org.hibernate.impl.SessionImpl.save(SessionImpl.java:514)
            at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:615)
            at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
            at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:612)
            at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.getCurrentTransaction(HibernateNodeDaoServiceImpl.java:231)
            at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.recordChangeId(HibernateNodeDaoServiceImpl.java:391)
            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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
            at org.alfresco.repo.transaction.TransactionalDaoInterceptor.invoke(TransactionalDaoInterceptor.java:68)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
            at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
            at $Proxy1.recordChangeId(Unknown Source)
            at org.alfresco.repo.node.db.DbNodeServiceImpl.setPropertyImpl(DbNodeServiceImpl.java:1061)
            at org.alfresco.repo.node.db.DbNodeServiceImpl.setProperty(DbNodeServiceImpl.java:1028)
            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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
            at org.alfresco.repo.transaction.TransactionResourceInterceptor.invoke(TransactionResourceInterceptor.java:138)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
            at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
            at $Proxy2.setProperty(Unknown Source)
            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.alfresco.repo.service.StoreRedirectorProxyFactory$RedirectorInvocationHandler.invoke(StoreRedirectorProxyFactory.java:221)
            at $Proxy3.setProperty(Unknown Source)
            at org.alfresco.repo.node.MLPropertyInterceptor.invoke(MLPropertyInterceptor.java:224)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
            at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
            at $Proxy2.setProperty(Unknown Source)
            at org.alfresco.repo.descriptor.DescriptorServiceImpl.updateCurrentRepositoryDescriptor(DescriptorServiceImpl.java:260)
            at org.alfresco.repo.descriptor.DescriptorServiceImpl.access$300(DescriptorServiceImpl.java:66)
            at org.alfresco.repo.descriptor.DescriptorServiceImpl$1.doWork(DescriptorServiceImpl.java:176)
            at org.alfresco.repo.descriptor.DescriptorServiceImpl$1.doWork(DescriptorServiceImpl.java:167)
            at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:176)
            at org.alfresco.repo.transaction.TransactionUtil.executeInUserTransaction(TransactionUtil.java:82)
            at org.alfresco.repo.descriptor.DescriptorServiceImpl.onBootstrap(DescriptorServiceImpl.java:182)
            at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
            at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
            at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
            at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
            at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
            at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
            at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
            at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
            at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3763)
            at org.apache.catalina.core.StandardContext.start(StandardContext.java:4211)
            at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
            at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
            at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
            at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:809)
            at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:698)
            at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
            at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1122)
            at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
            at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
            at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1021)
            at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
            at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1013)
            at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
            at org.apache.catalina.core.StandardService.start(StandardService.java:450)
            at org.apache.catalina.core.StandardServer.start(StandardServer.java:709)
            at org.apache.catalina.startup.Catalina.start(Catalina.java:551)
            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.catalina.startup.Bootstrap.start(Bootstrap.java:294)
            at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:432)

[derek]

I assume that you have the correct database dialect configured.

[gjw]

The com.mysql.jdbc.* classes showing in the stacktrace indicate that the correct hibernate dialect property is entered/read by the context. Is that a valid assumption?

[gjw]

Can you advise where to look for the hibernate sequence number in the MySQL schema? Cheers.

[derek]

No.  It shows you have a database connection to MySQL but not whether your are sending MySQL-compatible statements over the wire.

What database dialect are you using?
What is the result of: describe alf_transaction;

Regards

[gjw]

Here is the csv export of the resultset for alf_transaction:

"Field","Type","Null","Key","Default","Extra"
"ID","bigint(20)","NO","PRI","",""
"SERVER_ID","bigint(20)","YES","MUL","",""
"CHANGE_TXN_ID","varchar(56)","NO","","",""

The db dialect is org.hibernate.dialect.MySQLInnoDBDialect

I'm using MySQL version 5.0.45 and mysql driver 5.0.3

[derek]

Oracle and MySQL work differently w.r.t. the assignment of IDs.  Oracle doesn't do auto-incrementing and therefore Hibernate has to create a sequence for this.  MySQL, on the other hand, should be doing auto-increments on the ID columns.  Your table doesn't have that - ofcourse, it's not on Oracle so there is no way for the migration tool to know it is required.

Visit all the tables in MySQL Administrator and switch on AUTO_INCREMENT for the primary keys where they are a single ID (long) value.

Regards