AnsweredAssumed Answered

The DELETE statement conflicted with the REFERENCE constraint 'ACT_FK_VAR_EXE'

Question asked by cyr1 on Aug 4, 2015
Latest reply on Aug 4, 2015 by cyr1
We have an error in the logs which appears about every 5min.

2015-08-04 10:27:29,534 ERROR [pool-18-thread-40] mpl.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_VAR_EXE". The conflict occurred in database "XXX", table "dbo.ACT_RU_VARIABLE", column '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.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "ACT_FK_VAR_EXE". The conflict occurred in database "XXX", table "dbo.ACT_RU_VARIABLE", column '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:225)
   at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:577)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:445)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:170)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:117)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)
   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.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:46)
   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.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "ACT_FK_VAR_EXE". The conflict occurred in database "XXX", table "dbo.ACT_RU_VARIABLE", column 'EXECUTION_ID_'.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
   at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   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)
   … 17 more

By Looking at error and "ACT_FK_VAR_EXE" i made this Query:
SELECT TOP 1000 [ID_]
      ,[REV_]
      ,[PROC_INST_ID_]
      ,[BUSINESS_KEY_]
      ,[PARENT_ID_]
      ,[PROC_DEF_ID_]
      ,[SUPER_EXEC_]
      ,[ACT_ID_]
      ,[IS_ACTIVE_]
      ,[IS_CONCURRENT_]
      ,[IS_SCOPE_]
      ,[SUSPENSION_STATE_]
      ,[IS_EVENT_SCOPE_]
      ,[CACHED_ENT_STATE_]
  FROM [gnt2sql].[dbo].[ACT_RU_EXECUTION]
  WHERE ID_ NOT IN (SELECT DISTINCT EXECUTION_ID_ FROM ACT_RU_VARIABLE)

The result are 2 rows with PROC_INST_ID_ which aren't in ACT_RU_TASK or ACT_RU_VARIABLE.
The History ACT_HI_TASKINST shows 1 row for each PROC_INST_ID_ but END_TIME_ is null.

What should i do with these Records in ACT_RU_EXECUTION? Can I delete them?
Is this the source of the Errors in the log?

Thx in advance
Cyrill



Outcomes