Details
-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
None
-
None
Description
The query to select cleanable historic process instances uses a ORDER BY to delete the most oldest historic process instances first. There it calculates difference between the current timestamp and the end time of a historic process instance, this will be ordered in descending order (i.e. ORDER BY <current timestmap> - pi.END_TIME_ DESC).
This causes a problem on DB2, whereby the execution of the query fails with the following exception:
23-Jun-2017 13:36:15.945 WARNING [pool-1-thread-3] org.camunda.commons.logging.BaseLogger.logWarn ENGINE-14006 Exception while executing job 2d7ccc88-4a97-11e7-9466-901b0e367a78: org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.rethrow(CommandInvocationContext.java:148) at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:173) at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:113) at org.camunda.bpm.engine.impl.interceptor.JtaTransactionInterceptor.execute(JtaTransactionInterceptor.java:58) at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:66) at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobHelper.executeJob(ExecuteJobHelper.java:36) at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobHelper.executeJob(ExecuteJobHelper.java:29) at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.executeJob(ExecuteJobsRunnable.java:88) at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:57) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-402, SQLSTATE=42819, SQLERRMC=-, DRIVER=4.19.26 ### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/HistoricProcessInstance.xml ### The error may involve org.camunda.bpm.engine.impl.persistence.entity.HistoricProcessInstanceEntity.selectHistoricProcessInstanceIdsForCleanup_db2-Inline ### The error occurred while setting parameters ### SQL: SELECT PROC_INST_ID_ FROM (SELECT pi.PROC_INST_ID_, ROW_NUMBER() OVER(ORDER BY ? - pi.END_TIME_ DESC) as rownumber FROM ACT_HI_PROCINST pi, ACT_RE_PROCDEF pd where pi.PROC_DEF_ID_ = pd.ID_ AND not pi.END_TIME_ is NULL AND not pd.HISTORY_TTL_ is NULL AND pi.END_TIME_ + pd.HISTORY_TTL_ DAYS <= ?) as SUB where rownumber between 1 and ? ### Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-402, SQLSTATE=42819, SQLERRMC=-, DRIVER=4.19.26 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:88) at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:168) at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:160) at org.camunda.bpm.engine.impl.persistence.entity.HistoricProcessInstanceManager.findHistoricProcessInstanceIdsForCleanup(HistoricProcessInstanceManager.java:143) at org.camunda.bpm.engine.impl.jobexecutor.historycleanup.HistoryCleanupHelper.getNextBatch(HistoryCleanupHelper.java:112) at org.camunda.bpm.engine.impl.jobexecutor.historycleanup.HistoryCleanupJobHandler.execute(HistoryCleanupJobHandler.java:36) at org.camunda.bpm.engine.impl.jobexecutor.historycleanup.HistoryCleanupJobHandler.execute(HistoryCleanupJobHandler.java:16) at org.camunda.bpm.engine.impl.persistence.entity.JobEntity.execute(JobEntity.java:129) at org.camunda.bpm.engine.impl.cmd.ExecuteJobsCmd.execute(ExecuteJobsCmd.java:99) at org.camunda.bpm.engine.impl.cmd.ExecuteJobsCmd.execute(ExecuteJobsCmd.java:36) at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24) at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104) ... 10 more Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-402, SQLSTATE=42819, SQLERRMC=-, DRIVER=4.19.26 at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.fp.c(Unknown Source) at com.ibm.db2.jcc.am.fp.d(Unknown Source) at com.ibm.db2.jcc.am.fp.a(Unknown Source) at com.ibm.db2.jcc.am.gp.a(Unknown Source) at com.ibm.db2.jcc.t4.bb.h(Unknown Source) at com.ibm.db2.jcc.t4.bb.b(Unknown Source) at com.ibm.db2.jcc.t4.p.a(Unknown Source) at com.ibm.db2.jcc.t4.vb.i(Unknown Source) at com.ibm.db2.jcc.am.fp.kb(Unknown Source) at com.ibm.db2.jcc.am.gp.xc(Unknown Source) at com.ibm.db2.jcc.am.gp.b(Unknown Source) at com.ibm.db2.jcc.am.gp.nc(Unknown Source) at com.ibm.db2.jcc.am.gp.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) ... 23 more
The ordering could be simplified by just ordering by the end time in ascending order: ORDER BY pi.END_TIME_ ASC
Hint:
- At the moment we are not able to reproduce the exception.
- With this kind of ordering the queries can be simplified also for the other databases.
- The fix should also be applied for CMMN and DMN.