Oracle Constraint violation error submitting workflow in WCM

[epaquette75]

I'm having issues when I execute a second instance of a workflow that contain sub-process's in a Oracle database:

[20/12/07 14:25:33:757 EST] 00000029 ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl initialize FFDC0009I: FFDC opened incident stream file C:\Program Files\IBM\SDP70\runtimes\base_v61\profiles\AppSrv01\logs\ffdc\server1_6500650_07.12.20_14.25.33_0.txt
[20/12/07 14:25:33:978 EST] 00000029 ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl resetIncidentStream FFDC0010I: FFDC closed incident stream file C:\Program Files\IBM\SDP70\runtimes\base_v61\profiles\AppSrv01\logs\ffdc\server1_6500650_07.12.20_14.25.33_0.txt
[20/12/07 14:25:34:018 EST] 00000029 ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl open FFDC0009I: FFDC opened incident stream file C:\Program Files\IBM\SDP70\runtimes\base_v61\profiles\AppSrv01\logs\ffdc\server1_6500650_07.12.20_14.25.34_0.txt
[20/12/07 14:25:34:859 EST] 00000029 ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl resetIncidentStream FFDC0010I: FFDC closed incident stream file C:\Program Files\IBM\SDP70\runtimes\base_v61\profiles\AppSrv01\logs\ffdc\server1_6500650_07.12.20_14.25.34_0.txt
[20/12/07 14:25:35:249 EST] 00000029 SystemOut     O 14:25:34,929 User:admin ERROR [ui.common.Utils] A system error happened during the operation: Transaction didn't commit: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
javax.transaction.RollbackException: Transaction didn't commit: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:430)
   at org.alfresco.web.bean.workflow.ManageTaskDialog.transition(ManageTaskDialog.java:396)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:615)
   at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:132)
   at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:61)
   at javax.faces.component.UICommand.broadcast(UICommand.java:109)
   at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:97)
   at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:171)
   at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:32)
   at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:95)
   at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:70)
   at javax.faces.webapp.FacesServlet.service(FacesServlet.java:139)
   at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:966)
   at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:907)
   at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:145)
   at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:81)
   at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:190)
   at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:130)
   at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:87)
   at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:701)
   at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:646)
   at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:475)
   at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:463)
   at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:92)
   at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:744)
   at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1433)
   at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:93)
   at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465)
   at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:394)
   at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:102)
   at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:152)
   at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:213)
   at com.ibm.io.async.AbstractAsyncFuture.fireCompletionActions(AbstractAsyncFuture.java:195)
   at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
   at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:194)
   at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:741)
   at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:863)
   at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1510)
Caused by: org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:622)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:714)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:583)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:662)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:632)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:314)
   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:420)
   … 40 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:249)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
   at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2174)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2610)
   at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:52)
   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:139)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:575)
   … 44 more
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (NHPOLS.SYS_C007328) violated

   at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459)
   at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:4368)
   at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch(WSJdbcPreparedStatement.java:808)
   at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:612)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
   … 59 more

Same kind of issue that was fixed in version 2.1, but one step further:

http://issues.alfresco.com/browse/AR-1551

Thanks in advance

[epaquette75]

After a lot of debugging, we found that the org.jbpm.graph.exe.ProcessInstance.hbm.xml had to be modified. The definition of "superProcessToken" required a new unique-key "UNI_BUSINESS_KEY" be added to it. This now causes the unique constraint to be generated accross all 3 columns (KEY_ , PROCESSDEFINITION_ and SUPERPROCESSTOKEN_) instead of only the first two (KEY_ and PROCESSDEFINITION_).

[derek]

Thanks.
http://issues.alfresco.com/browse/AR-2020