[CAM-7977] Ordering of cleanable historic process instances fails with an exception Created: 27/Jun/17  Updated: 28/Jul/17  Resolved: 14/Jul/17

Status: Closed
Project: camunda BPM
Component/s: engine
Affects Version/s: None
Fix Version/s: 7.8.0, 7.8.0-alpha2, 7.7.2

Type: Bug Report Priority: L3 - Default
Reporter: Smirnov Roman Assignee: Svetlana Dorokhova
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: 0 minutes
Time Spent: Not Specified
Original Estimate: 0 minutes

Issue Links:
Depedendency

 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.

Generated at Sat Nov 25 06:50:46 CET 2017 using JIRA 6.4.6#64021-sha1:33e5b454af4594f54560ac233c30a6e00459507e.