AnsweredAssumed Answered

MySQLIntegrityConstraintViolationException

Question asked by halfsoft on Nov 18, 2015
Latest reply on Nov 18, 2015 by halfsoft
Hello all,
I have a flow that is something like this:

Start->ServiceTask1..N->UserTask->Exclusive Gateway(depending on the user task result) -> ServiceTasks->Exit

1.The ServiceTask1..N set up some variables in my db.
2.The user task has a TaskListener set up (whenever the task is created it executes a http request sending its own taskId, so it can continue the execution when some async response comes at a later date):
<!–break–>

public class BillTaskListener implements org.activiti.engine.delegate.TaskListener {

    private Expression price;

    @Override
    public void notify(DelegateTask delegateTask) {
        if (delegateTask.getEventName().equals("create")) {
            int percent = Integer.parseInt(price.getValue(delegateTask).toString());
            try {
                ExecutionEntity executionEntity = (ExecutionEntity) delegateTask.getExecution();
                Core core = new Core(true);
                core.sendBillRequest(percent, delegateTask.getId(), executionEntity);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}



3. At a different part of the system, when we receive an async event we find the taskId for the specific event and try to continue the execution

    @Transactional(Transactional.TxType.REQUIRES_NEW)
    public int processResponse(ProcessEngine processEngine, final String taskId, HashMap params) {
        try {
            TaskService taskService = processEngine.getTaskService();
            taskService.setVariable(taskId, "lastTaskId", taskId);
            taskService.setVariable(taskId, taskId, params);
            taskService.complete(taskId);
            return SUCCESS;
        } catch (ActivitiOptimisticLockingException e) {
            System.out.println(e.getClass().getSimpleName() + " catched..");
            context.setRollbackOnly();
            return RETRY;
        } catch (Exception e) {
            context.setRollbackOnly();
            return FAILURE;
        }
    }



If 95% of the cases everything works correctly, sometimes however the execution finishes (everything after the user task gets called) but the execution does not get deleted from the database(I think that also sometimes we get a ActivitiOptimisticLockingException but I am not sure if the two exceptions are related). Even stranger all the variables of the execution are deleted successfully , so in the end I get a process instance that retries the service tasks again, but they fail because all the instance variables are null. These processes need to be deleted manually.
Here is a log of the exception:

<blockcode>16:04:31,345 ERROR [org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable] (pool-22-thread-69) Job 62345 failed: org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`activiti5`.`act_ru_execution`, CONSTRAINT `ACT_FK_EXE_PARENT` FOREIGN KEY (`PARENT_ID_`) REFERENCES `act_ru_execution` (`ID_`))
### The error may involve org.activiti.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`activiti5`.`act_ru_execution`, CONSTRAINT `ACT_FK_EXE_PARENT` FOREIGN KEY (`PARENT_ID_`) REFERENCES `act_ru_execution` (`ID_`))
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:161)
   at org.activiti.engine.impl.db.DbSqlSession$CheckedDeleteOperation.execute(DbSqlSession.java:293)
   at org.activiti.engine.impl.db.DbSqlSession.flushRegularDeletes(DbSqlSession.java:922)
   at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:888)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:613)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:212)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:138)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
   at org.activiti.engine.impl.interceptor.JtaTransactionInterceptor.execute(JtaTransactionInterceptor.java:65)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
   at org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:69)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
   at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`activiti5`.`act_ru_execution`, CONSTRAINT `ACT_FK_EXE_PARENT` FOREIGN KEY (`PARENT_ID_`) REFERENCES `act_ru_execution` (`ID_`))
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
   at com.mysql.jdbc.Util.getInstance(Util.java:383)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
   at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1193)
   at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:306)
   at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:442)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
   at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
   at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
   … 16 more
</blockcode>

Outcomes