topic Re: help! Error after migrating from HSQL -> MySQL -> Orac in Alfresco Archive

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

gjw — Mon, 29 Oct 2007 00:46:35 GMT

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, a

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

gjw — Mon, 29 Oct 2007 02:20:21 GMT

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

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

derek — Fri, 02 Nov 2007 17:40:34 GMT

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

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

gjw — Wed, 07 Nov 2007 02:09:15 GMT

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)

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

derek — Wed, 07 Nov 2007 09:30:14 GMT

I assume that you have the correct database dialect configured.

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

gjw — Wed, 07 Nov 2007 23:26:03 GMT

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?

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

gjw — Thu, 08 Nov 2007 00:24:48 GMT

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

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

derek — Thu, 08 Nov 2007 00:25:45 GMT

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

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

gjw — Thu, 08 Nov 2007 00:45:42 GMT

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

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

derek — Thu, 08 Nov 2007 11:16:25 GMT

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

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

gjw — Mon, 12 Nov 2007 08:14:25 GMT

That seems to have done the trick. Cheers!

See how the Oracle migration goes now…

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

gjw — Tue, 13 Nov 2007 23:43:35 GMT

still no luck getting the schema into Oracle…

09:50:45,163 WARN [org.springframework.remoting.rmi.RmiRegistryFactoryBean] Could not detect RMI registry - creating new one
09:51:37,080 INFO [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script: C:\alfresco201new\tomcat\temp\Alfresco\AlfrescoSchemaUpdate-org.hibernate.dialect.Oracle9Dialect-40029.sql
09:51:45,720 WARN [org.alfresco.service.descriptor.DescriptorService] Alfresco license: Failed to verify license - Invalid License!
09:51:45,720 WARN [org.alfresco.service.descriptor.DescriptorService] Alfresco license: Restricted Alfresco Repository to read-only capability
09:51:45,876 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_4) 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
09:51:45,938 ERROR [org.springframework.web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Unexpected rollback of exception:
Transaction didn't commit: Could not execute JDBC batch update
   at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:192)
   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: javax.transaction.RollbackException: Transaction didn't commit: Could not execute JDBC batch update
   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:412)
   at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:186)
   … 34 more
Caused by: 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)
   … 35 more
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (ALFRESCO_MYSQL.PRIMARY_4) 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
Here is the table DDL if that helps:

CREATE TABLE "ALFRESCO_MYSQL"."ALF_TRANSACTION"
   (   "ID" NUMBER(19,0) NOT NULL ENABLE,
   "SERVER_ID" NUMBER(19,0),
   "CHANGE_TXN_ID" VARCHAR2(56 BYTE) NOT NULL ENABLE,
    CONSTRAINT "PRIMARY_4" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
    CONSTRAINT "FKB8761A3A9AE340B7" FOREIGN KEY ("SERVER_ID")
     REFERENCES "ALFRESCO_MYSQL"."ALF_SERVER" ("ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

CREATE INDEX "ALFRESCO_MYSQL"."FKB8761A3A9AE340B7" ON "ALFRESCO_MYSQL"."ALF_TRANSACTION" ("SERVER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

CREATE UNIQUE INDEX "ALFRESCO_MYSQL"."PRIMARY_4" ON "ALFRESCO_MYSQL"."ALF_TRANSACTION" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

CREATE OR REPLACE TRIGGER "ALFRESCO_MYSQL"."ALF_TRANSACTION_ID_TRG" BEFORE INSERT OR UPDATE ON alf_transaction
FOR EACH ROW
BEGIN
if inserting and :new.ID is NULL then
SELECT alf_transaction_ID_SEQ.nextval into :new.ID FROM DUAL;
end if;
END;
/
ALTER TRIGGER "ALFRESCO_MYSQL"."ALF_TRANSACTION_ID_TRG" ENABLE;

Here is the DDL for the alf_transaction sequence:

CREATE SEQUENCE "ALFRESCO_MYSQL"."ALF_TRANSACTION_ID_SEQ" MINVALUE 28 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 28 CACHE 20 NOORDER NOCYCLE ;

The last entry in the ID column of ALF_TRANSACTION is 28 so can't see a problem there…

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

derek — Tue, 13 Nov 2007 23:53:07 GMT

The sequence used in Oracle is a single sequence called hibernate_sequence. You can discard all other sequences and make sure that the one has a min value greater than any ID value across all the tables.

It's a pain…

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

gjw — Wed, 14 Nov 2007 22:45:43 GMT

Yes that did the trick. I misunderstood what you had meant in your earlier post and was checking the table specific sequence previously. All good now, but agree that is a pain. Would that info be worth adding to the wiki?Thanks.

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

gjw — Wed, 21 Nov 2007 01:25:54 GMT

Unfortunately seem to have some other error post -migration with a scheduled clean up task…

09:17:15,992 ERROR [org.alfresco.repo.avm.OrphanReaper] Garbage collector error
org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [org.alfresco.repo.avm.AVMNodeImpl#6265]
            at org.hibernate.ObjectNotFoundException.throwIfNull(ObjectNotFoundException.java:27)
            at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:65)
            at org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:98)
            at org.hibernate.proxy.CGLIBLazyInitializer.intercept(CGLIBLazyInitializer.java:158)
            at org.alfresco.repo.avm.PlainFileNode$$EnhancerByCGLIB$$7ee5d04a.setAncestor(<generated>)
            at org.alfresco.repo.avm.OrphanReaper$1TxnWork.doWork(OrphanReaper.java:290)
            at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:176)
            at org.alfresco.repo.transaction.TransactionUtil.executeInUserTransaction(TransactionUtil.java:82)
            at org.alfresco.repo.avm.OrphanReaper.doBatch(OrphanReaper.java:340)
            at org.alfresco.repo.avm.OrphanReaper.run(OrphanReaper.java:213)
            at java.lang.Thread.run(Thread.java:595)

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

derek — Fri, 23 Nov 2007 11:59:00 GMT

Yes that did the trick. I misunderstood what you had meant in your earlier post and was checking the table specific sequence previously. All good now, but agree that is a pain. Would that info be worth adding to the wiki?Thanks.

The Wiki is freely editable :wink: