topic SQL exception when using complex process variables in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/sql-exception-when-using-complex-process-variables/m-p/165520#M119194 <HTML><HEAD></HEAD><BODY><SPAN>I am getting a foreign key violation exception when Activiti is trying to log the value of my complex variables to the database. This happens in DbSqlSession#flushInserts, and debugging shows that the statements to insert the serialized content into the byte array table come after the statement that references it in the local insertedObjects field that the method is looping over.</SPAN><BR /><BR /><SPAN>In the dump below variable id=4745 is the second statement but it comes before ByteArrayEntity id=4751 it refers to, the the insert fails.</SPAN><BR /><BR /><SPAN>Any suggestion as to what may be causing this? Thanks!</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR /> insertedObjects=<BR /> ProcessInstance[4744], <BR /> VariableInstanceEntity[id=4745, name=initiator, type=serializable, byteArrayValueId=4751],<BR /> HistoricVariableInstanceEntity[id=4745, name=initiator, type=serializable, byteArrayValueId=4752],<BR /> IdentityLinkEntity[id=4746, type=starter, userId=june, processInstanceId=4744],<BR /> org.activiti.engine.impl.persistence.entity.HistoricIdentityLinkEntity@40044be8, HistoricProcessInstanceEntity[superProcessInstanceId=null],<BR /> HistoricActivityInstanceEntity[activityId=sid-4C7C5325-DABF-4ADD-9AA0-FC9B5B6B33D3, activityName=null],<BR /> ByteArrayEntity[id=4748, name=var-asset, size=9561],<BR /> VariableInstanceEntity[id=4749, name=asset, type=serializable, byteArrayValueId=4748],<BR /> ByteArrayEntity[id=4750, name=hist.var-asset, size=9561],<BR /> HistoricVariableInstanceEntity[id=4749, name=asset, type=serializable, byteArrayValueId=4750],<BR /> ByteArrayEntity[id=4751, name=var-initiator, size=600],<BR /> ByteArrayEntity[id=4752, name=hist.var-initiator, size=600],<BR /> HistoricActivityInstanceEntity[activityId=sid-A8604028-D517-4A46-8069-8C173AF62633, activityName=Notify Approvers],<BR /> HistoricActivityInstanceEntity[activityId=sid-4635F0C0-B76A-4EC7-B995-2EDF55BE1E3E, activityName=Approve Model],<BR /> Task[id=4755, name=Approve Model],<BR /> org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity@5a138d2d, IdentityLinkEntity[id=4756, type=candidate, groupId=mcentral-admin, taskId=4755],<BR /> org.activiti.engine.impl.persistence.entity.HistoricIdentityLinkEntity@3999b38e]<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>The complete stack trace is:</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />[[14-01-24 09:32:26] WARN&nbsp; [http-bio-8080-exec-5] .C.MCActivitiRestServicesApplication&nbsp; Exception or error caught in resource org.apache.ibatis.exceptions.PersistenceException: <BR />### Error updating database.&nbsp; Cause: org.postgresql.util.PSQLException: ERROR: insert or update on table "act_ru_variable" violates foreign key constraint "act_fk_var_bytearray"<BR />&nbsp; Detail: Key (bytearray_id_)=(4738) is not present in table "act_ge_bytearray".<BR />### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.insertVariableInstance-Inline<BR />### The error occurred while setting parameters<BR />### SQL: insert into ACT_RU_VARIABLE (ID_, REV_,&nbsp;&nbsp;&nbsp;&nbsp; TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_,&nbsp;&nbsp;&nbsp;&nbsp; DOUBLE_, LONG_ , TEXT_, TEXT2_)&nbsp;&nbsp;&nbsp;&nbsp; values (&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; 1,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?,&nbsp;&nbsp;&nbsp;&nbsp; ?&nbsp;&nbsp;&nbsp;&nbsp; )<BR />### Cause: org.postgresql.util.PSQLException: ERROR: insert or update on table "act_ru_variable" violates foreign key constraint "act_fk_var_bytearray"<BR />&nbsp; Detail: Key (bytearray_id_)=(4738) is not present in table "act_ge_bytearray".<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:540)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:443)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:170)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:117)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:37)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:68)<BR />&nbsp;&nbsp;&nbsp;at org.activiti.rest.service.api.runtime.process.ProcessInstanceCollectionResource.createProcessInstance(ProcessInstanceCollectionResource.java:132)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<BR />&nbsp;&nbsp;&nbsp;at java.lang.reflect.Method.invoke(Method.java:601)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.ServerResource.doHandle(ServerResource.java:449)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.ServerResource.post(ServerResource.java:1114)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.ServerResource.doHandle(ServerResource.java:533)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.ServerResource.doNegotiatedHandle(ServerResource.java:590)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.ServerResource.doConditionalHandle(ServerResource.java:302)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.ServerResource.handle(ServerResource.java:849)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.resource.Finder.handle(Finder.java:513)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Router.doHandle(Router.java:500)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Router.handle(Router.java:740)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:155)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.engine.ChainHelper.handle(ChainHelper.java:114)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.engine.application.ApplicationHelper.handle(ApplicationHelper.java:75)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.Application.handle(Application.java:391)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Router.doHandle(Router.java:500)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Router.handle(Router.java:740)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Router.doHandle(Router.java:500)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Router.handle(Router.java:740)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.doHandle(Filter.java:159)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.routing.Filter.handle(Filter.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.engine.ChainHelper.handle(ChainHelper.java:114)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.Component.handle(Component.java:391)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.Server.handle(Server.java:491)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.engine.ServerHelper.handle(ServerHelper.java:74)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.engine.http.HttpServerHelper.handle(HttpServerHelper.java:153)<BR />&nbsp;&nbsp;&nbsp;at org.restlet.ext.servlet.ServerServlet.service(ServerServlet.java:1031)<BR />&nbsp;&nbsp;&nbsp;at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)<BR />&nbsp;&nbsp;&nbsp;at org.eclipse.virgo.web.tomcat.support.ApplicationNameTrackingValve.invoke(ApplicationNameTrackingValve.java:33)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)<BR />&nbsp;&nbsp;&nbsp;at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)<BR />&nbsp;&nbsp;&nbsp;at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)<BR />&nbsp;&nbsp;&nbsp;at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)<BR />&nbsp;&nbsp;&nbsp;at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)<BR />&nbsp;&nbsp;&nbsp;at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)<BR />&nbsp;&nbsp;&nbsp;at java.lang.Thread.run(Thread.java:722)<BR />Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "act_ru_variable" violates foreign key constraint "act_fk_var_bytearray"<BR />&nbsp; Detail: Key (bytearray_id_)=(4738) is not present in table "act_ge_bytearray".<BR />&nbsp;&nbsp;&nbsp;at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)<BR />&nbsp;&nbsp;&nbsp;at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)<BR />&nbsp;&nbsp;&nbsp;at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)<BR />&nbsp;&nbsp;&nbsp;at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)<BR />&nbsp;&nbsp;&nbsp;at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)<BR />&nbsp;&nbsp;&nbsp;at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<BR />&nbsp;&nbsp;&nbsp;at java.lang.reflect.Method.invoke(Method.java:601)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)<BR />&nbsp;&nbsp;&nbsp;at $Proxy157.execute(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)<BR />&nbsp;&nbsp;&nbsp;at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)<BR />&nbsp;&nbsp;&nbsp;… 76 common frames omitted<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /></BODY></HTML> Fri, 24 Jan 2014 08:48:31 GMT franck102 2014-01-24T08:48:31Z SQL exception when using complex process variables https://connect.hyland.com/t5/alfresco-archive/sql-exception-when-using-complex-process-variables/m-p/165520#M119194 I am getting a foreign key violation exception when Activiti is trying to log the value of my complex variables to the database. This happens in DbSqlSession#flushInserts, and debugging shows that the statements to insert the serialized content into the byte array table come after the statement that Fri, 24 Jan 2014 08:48:31 GMT https://connect.hyland.com/t5/alfresco-archive/sql-exception-when-using-complex-process-variables/m-p/165520#M119194 franck102 2014-01-24T08:48:31Z Re: SQL exception when using complex process variables https://connect.hyland.com/t5/alfresco-archive/sql-exception-when-using-complex-process-variables/m-p/165521#M119195 <HTML><HEAD></HEAD><BODY><SPAN>This seems to be a known issue: </SPAN><A href="http://jira.codehaus.org/browse/ACT-1839" rel="nofollow noopener noreferrer">http://jira.codehaus.org/browse/ACT-1839</A><SPAN>.</SPAN><BR /><SPAN>Not sure why my initial google search didn't bring it up, I guess we will just have to wait for 5.15…</SPAN></BODY></HTML> Fri, 24 Jan 2014 08:52:11 GMT https://connect.hyland.com/t5/alfresco-archive/sql-exception-when-using-complex-process-variables/m-p/165521#M119195 franck102 2014-01-24T08:52:11Z