We couldn't load all Actvitity tabs. Refresh the page to try again.
If the problem persists, contact your Jira admin.
camunda BPMProject Type: software

camunda BPM

Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-7977

Ordering of cleanable historic process instances fails with an exception

XMLWordPrintable

    • Icon: Bug Report Bug Report
    • Resolution: Fixed
    • Icon: L3 - Default L3 - Default
    • 7.8.0, 7.8.0-alpha2, 7.7.2
    • None
    • engine
    • None

      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.

        This is the controller panel for Smart Panels app

            Loading...
            Uploaded image for project: 'camunda BPM'
            1. camunda BPM
            2. CAM-7977

            Ordering of cleanable historic process instances fails with an exception

            XMLWordPrintable

              • Icon: Bug Report Bug Report
              • Resolution: Fixed
              • Icon: L3 - Default L3 - Default
              • 7.8.0, 7.8.0-alpha2, 7.7.2
              • None
              • engine
              • None

                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.

                  This is the controller panel for Smart Panels app

                        svetlana.dorokhova Svetlana Dorokhova
                        roman.smirnov Roman Smirnov
                        Votes:
                        0 Vote for this issue
                        Watchers:
                        2 Start watching this issue

                          Created:
                          Updated:
                          Resolved:

                              svetlana.dorokhova Svetlana Dorokhova
                              roman.smirnov Roman Smirnov
                              Votes:
                              0 Vote for this issue
                              Watchers:
                              2 Start watching this issue

                                Created:
                                Updated:
                                Resolved: