Error migration a process

Flowable Engine
1

As we try to migrate the process we keep getting this error:

2020-09-21 15:05:22,320 [default task-1] ERROR org.flowable.common.engine.impl.interceptor.CommandContext Error while closing command context    org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "ACT_FK_TIMER_JOB_PROCESS_INSTANCE". The conflict occurred in database "WF", table "i2sflow.ACT_RU_TIMER_JOB", column 'PROCESS_INSTANCE_ID_'.
### The error may exist in org/flowable/db/mapping/entity/Execution.xml
### The error may involve org.flowable.engine.impl.persistence.entity.ExecutionEntityImpl.deleteExecution-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "ACT_FK_TIMER_JOB_PROCESS_INSTANCE". The conflict occurred in database "WF", table "i2sflow.ACT_RU_TIMER_JOB", column 'PROCESS_INSTANCE_ID_'.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:212)
at org.flowable.common.engine.impl.db.DbSqlSession.flushDeleteEntities(DbSqlSession.java:569)
at org.flowable.common.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:526)
at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:293)
at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:191)
at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61)
at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81)
at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56)
at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
at org.flowable.engine.impl.RuntimeServiceImpl.migrateProcessInstancesOfProcessDefinition(RuntimeServiceImpl.java:777)
at org.flowable.engine.impl.migration.ProcessInstanceMigrationBuilderImpl.migrateProcessInstances(ProcessInstanceMigrationBuilderImpl.java:108)
at pt.i2s.fwk.workflow.processes.beans.ProcessDefinitionMigrationService.migrateProcessInstances(ProcessDefinitionMigrationService.java:54)
at pt.i2s.fwk.workflow.services.impl.WorkflowProcessesServicesImpl.migrateProcessInstances(WorkflowProcessesServicesImpl.java:165)
... 73 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "ACT_FK_TIMER_JOB_PROCESS_INSTANCE". The conflict occurred in database "WF", table "i2sflow.ACT_RU_TIMER_JOB", column 'PROCESS_INSTANCE_ID_'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)ni
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:493)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:442)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
... 88 more

At first i thought that i was doing something wrong, so i double checked everything, the processes were fine but with more investigation we found out that the query that the engine was trying to do was “ delete from ACT_RU_EXECUTION where ID_ = ‘58b804eb-15b5-11ea-b7a8-00155dfc8176’ and REV_ = 2 ” and this query is what is causing the error.
For that process instance in question we have 2 records on the table ACT_RU_EXECUTION and the process migration fails due to a FK constraint as we can see here:

image
image1368×61 4.16 KB

Some more information about this process, it has a timer job and after the timer a sub process is called with a single service task in it.
Also, we are using Flowable 6.4.2.
How can i solve this problem?

Thanks in advance.

2

Hard to say without the process model. Can you share something so we can try to reproduce the problem?

3

Hi @joram

I’ve found a way to isolate the problem.
We have an event listener on the process that listens for “JOB_CANCELED” events. This listener deletes the process instance once it’s triggered.

I’m not sure what’s happening behind the scenes when Flowable is performing the migration, but for some reason, this listener is being triggered and I think that’s why you get this error. I’ve tried migrating the exact same process without the listener and the error does not occur.
It looks to me like this listener shouldn’t be triggered at that time.

<?xml version="1.0" encoding="UTF-8"?>
<definitions xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:flowable="http://flowable.org/bpmn" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:omgdc="http://www.omg.org/spec/DD/20100524/DC" xmlns:omgdi="http://www.omg.org/spec/DD/20100524/DI" typeLanguage="http://www.w3.org/2001/XMLSchema" expressionLanguage="http://www.w3.org/1999/XPath" targetNamespace="ModuleMacroProcess">
  <process id="bpmnMigration" name="Bpmn Migration" isExecutable="true" flowable:candidateStarterGroups="flowableUser">
    <extensionElements>
      <flowable:eventListener events="JOB_CANCELED" class="com.flowable.bpmn.migration.CancelProcess" onTransaction="committed"></flowable:eventListener>
    </extensionElements>
    <startEvent id="startnoneevent1" flowable:initiator="initiator" flowable:formFieldValidation="false"></startEvent>
    <intermediateCatchEvent id="theTimer" name="Timer">
      <timerEventDefinition>
        <timeDuration>${timerduration}</timeDuration>
      </timerEventDefinition>
    </intermediateCatchEvent>
    <sequenceFlow id="sequenceFlow2" sourceRef="startnoneevent1" targetRef="theTimer">
    </sequenceFlow>
    <endEvent id="endNoneEvent1">
    </endEvent>
    <sequenceFlow id="sequenceFlow3" sourceRef="theTimer" targetRef="scriptTask1">
    </sequenceFlow>
    <scriptTask id="scriptTask1" name="Script task" scriptFormat="groovy" flowable:autoStoreVariables="false">
      <script><![CDATA[sum = 0
      test = 2
    for ( i in inputArray ) {
      sum += i
    }]]></script>
    </scriptTask>
    <sequenceFlow id="sequenceFlow4" sourceRef="scriptTask1" targetRef="endNoneEvent1">
    </sequenceFlow>
  </process>
</definitions>

package com.flowable.bpmn.migration;

import org.flowable.common.engine.api.delegate.event.FlowableEngineEventType;
import org.flowable.common.engine.api.delegate.event.FlowableEntityEvent;
import org.flowable.common.engine.api.delegate.event.FlowableEvent;
import org.flowable.common.engine.api.delegate.event.FlowableEventListener;
import org.flowable.engine.ProcessEngines;
import org.flowable.job.service.impl.persistence.entity.TimerJobEntity;

public class CancelProcess implements FlowableEventListener {
    @Override
    public void onEvent(FlowableEvent event) {

        if (event.getType().equals(FlowableEngineEventType.JOB_CANCELED)) {
            FlowableEntityEvent flowableEntityEvent = (FlowableEntityEvent) event;
            TimerJobEntity timerJobEntity = (TimerJobEntity) flowableEntityEvent.getEntity();
            ProcessEngines.getDefaultProcessEngine().getRuntimeService().deleteProcessInstance(timerJobEntity.getProcessInstanceId(), "TEST");
        }
    }

    @Override
    public boolean isFailOnException() {
        return true;
    }

    @Override
    public boolean isFireOnTransactionLifecycleEvent() {
        return false;
    }

    @Override
    public String getOnTransaction() {
        return null;
    }
}

Is there anything that can be done to prevent this from happening?
I have considered disabling the eventDispatcher while this migration is happening but I’m afraid that might have negative side effects…

4

Thanks @DavidPLamas, that’s very useful. Will try to reproduce it with that.

That’s an option indeed. If nothing else is running at that moment, the event dispatched can be disabled. However, I assume other instances will still need it while this one is being migrated?

5

Yes, you’re right.
I was thinking about creating a jar/war application just to migrate this process. It would set up the engine using the same configuration but disabling the event dispatcher.
Do you think this is viable?