Camunda not compatible with h2 2.0.202+

boris · January 5, 2022, 8:30am

Updating h2 to 2.0.202+ leads to:

org.camunda.bpm.engine.context - ENGINE-16004 Exception while closing command context: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
org.camunda.bpm.engine.ProcessEngineException: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
        at org.camunda.bpm.engine.impl.util.ExceptionUtil.wrapPersistenceException(ExceptionUtil.java:263)
        at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:257)
        at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeSelectList(DbSqlSession.java:111)
        at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:103)
        at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:182)
        at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:174)
        at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:170)
        at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:159)
        at org.camunda.bpm.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:238)
        at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:66)
        at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:44)
        at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
        at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:110)
        at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
        at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35)
        at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.acquireJobs(SequentialJobAcquisitionRunnable.java:164)
        at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.run(SequentialJobAcquisitionRunnable.java:80)
        at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BOOLEAN" and "INTEGER" are not comparable; SQL statement:
select RES.ID_,
      RES.REV_,
      RES.DUEDATE_,
      RES.PROCESS_INSTANCE_ID_,
      RES.EXCLUSIVE_

    from ACT_RU_JOB RES

    where (RES.RETRIES_ > 0)
      and (

          RES.DUEDATE_ is null or

          RES.DUEDATE_ <= ?
      )
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)
      and RES.SUSPENSION_STATE_ = 1






      and (
    (
    RES.EXCLUSIVE_ = 1
          and not exists(
            select J2.ID_ from ACT_RU_JOB J2
            where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_                                           -- from the same proc. inst.
            and (J2.EXCLUSIVE_ = 1)                                                              -- also exclusive
            and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?)  -- in progress
            )
   )
    or

    RES.EXCLUSIVE_ = 0

   )




    LIMIT ? OFFSET ? [90110-206]

And other similar errors. I’ve been searching for a resolution for a while now but I can’t find one. This seems relevant:

https://groups.google.com/g/h2-database/c/AKjKqvGr9j8

But it’s for Hibernate (and it was a bug in it and not in h2). Is there some solution for Camunda?

eileen · January 5, 2022, 3:22pm

Hi,
I don’t have any advice or workarounds unfortunately, officially H2 is supported by Camunda but in version 1.4: Supported Environments | docs.camunda.org
I’ve been using it without problems in local development in that version.

langfr · January 6, 2022, 1:50pm

There is work in progress to support h2 2.0.x database.
See https://jira.camunda.com/browse/CAM-14205 and
code changes in https://github.com/camunda/camunda-bpm-platform/pull/1717/files.

So the next version of Camunda might support the latest h2 database version.

DavidZang · January 7, 2022, 9:39am

As a workaround for your issue you could add a ProcessEnginePlugin to alter the database-specific SQL. For Spring (Boot) you can just add this ProcessEnginePlugin as a Component:

import org.camunda.bpm.engine.ProcessEngine;
 import org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl;
 import org.camunda.bpm.engine.impl.cfg.ProcessEnginePlugin;
 import org.camunda.bpm.engine.impl.db.sql.DbSqlSessionFactory;
 import org.camunda.bpm.spring.boot.starter.configuration.Ordering;
 import org.springframework.core.annotation.Order;
 import org.springframework.stereotype.Component;
 
 @Component
 @Order(Ordering.DEFAULT_ORDER + 2)
 public class H2SqlConfigurationProcessEnginePlugin implements ProcessEnginePlugin {
   
   @Override
   public void preInit(ProcessEngineConfigurationImpl processEngineConfiguration) {
     DbSqlSessionFactory.databaseSpecificTrueConstant.put("h2", "true");
     DbSqlSessionFactory.databaseSpecificFalseConstant.put("h2", "false");
     DbSqlSessionFactory.databaseSpecificBitAnd2.put("h2", ",CAST(");
     DbSqlSessionFactory.databaseSpecificBitAnd3.put("h2", " AS BIGINT))");
   }
   
   @Override
   public void postInit(ProcessEngineConfigurationImpl processEngineConfiguration) {
      // in this case only preInit is necessary
   }
   
   @Override
   public void postProcessEngineBuild(ProcessEngine processEngine) {
     // in this case only preInit is necessary
   }
 }

boris · January 7, 2022, 9:42am

Thank you all for the support! Knowing that h2 2.0+ will be supported in the next version of Camunda is good enough for me. Thanks!

Kirthana-Gomathy · January 10, 2022, 9:08am

Hi,
When will be the next version of camunda be released?

Niall · January 10, 2022, 9:44am

Camunda 7.17.0 will be released the second Tuesday of April.
You can read more about the release cadence here or check out this handy picture:

Kirthana-Gomathy · January 11, 2022, 6:38am

Will the camunda 7.17.0 release fix this h2 database issue?

Niall · January 11, 2022, 8:11am

Too early to say - but the ticket will be updated with any progress,

awi · January 11, 2022, 8:35am

Hello all,

what does this mean for existing installations in the light of CVE-2021-42392 (NVD - CVE-2021-42392) ? Will Camunda look into this?

Best regards and thanks in advance

Niall · January 11, 2022, 9:06am

Hi @awi

Certainly! we’re aware of the problem and are right now looking into fixing it.

awi · January 20, 2022, 8:12am

Good morning.

Are there any news on this topic? The CVE-Score has been set to 9.8. (NVD - cve-2021-42392)
I did not find information here: Security Notices | docs.camunda.org

If there’s no reason for concern, I would gladly take it off my list.

Best regards and thanks a lot!
André

Niall · January 20, 2022, 8:15am

Hi @awi

I just took a look a the internal ticket for this and the fix is currently in progress.
I’d expect to see it as part of the next release.

walia · April 19, 2022, 10:18am

I tried with 7.17.0 release but still seems h2 2.0.+ versions are not compatible

Niall · April 19, 2022, 10:26am

Hi @walia
Can you explain what happened when you ran Camunda with H2 2.0.
It should be supported with 7.17.0.

walia · April 19, 2022, 10:31am

Hi Niall,

It is working perfectly fine with 1.4.200 but when I try to upgrade H2 to 2.1.210 I get the below exception

2022-04-19 12:29:16.677 [JobExecutor[org.camunda.bpm.engine.spring.components.jobexecutor.SpringJobExecutor]]org.camunda.bpm.engine.ProcessEngineException: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
	at org.camunda.bpm.engine.impl.util.ExceptionUtil.wrapPersistenceException(ExceptionUtil.java:263)
	at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:257)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeSelectList(DbSqlSession.java:111)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:103)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:182)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:174)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:170)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:159)
	at org.camunda.bpm.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:238)
	at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:66)
	at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:44)
	at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:110)
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:72)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:70)
	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
	at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35)
	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
	at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.acquireJobs(SequentialJobAcquisitionRunnable.java:164)
	at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.run(SequentialJobAcquisitionRunnable.java:80)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 

Error querying database.  Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select RES.ID_,\000a      RES.REV_,\000a      RES.DUEDATE_,\000a      RES.PROCESS_INSTANCE_ID_,\000a      RES.EXCLUSIVE_\000a    \000a    from ACT_RU_JOB RES\000a\000a    where (RES.RETRIES_ > 0)\000a      and (\000a       \000a          RES.DUEDATE_ is null or\000a       \000a          RES.DUEDATE_ <= ?\000a      )\000a      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)\000a      and RES.SUSPENSION_STATE_ = 1\000a\000a       \000a\000a       \000a       \000a\000a      and ( \000a    ( \000a    RES.EXCLUSIVE_ = true\000a          and not exists(\000a            select J2.ID_ from ACT_RU_JOB J2\000a            where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_ from the same proc. inst.\000a            and (J2.EXCLUSIVE_ = true)                                                              -- also exclusive\000a            and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?)  -- in progress\000a            )\000a   )\000a    or\000a     \000a    RES.EXCLUSIVE_ = false\000a   \000a   )\000a\000a       \000a\000a     \000a    [*]LIMIT ? OFFSET ?"; SQL statement:


    select RES.ID_,
      RES.REV_,
      RES.DUEDATE_,
      RES.PROCESS_INSTANCE_ID_,
      RES.EXCLUSIVE_
     from ACT_RU_JOB RES

    where (RES.RETRIES_ > 0)
      and (
       
          RES.DUEDATE_ is null or
       
          RES.DUEDATE_ <= ?
      )
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)
      and RES.SUSPENSION_STATE_ = 1

       

       
       

      and ( 
    ( 
    RES.EXCLUSIVE_ = true
          and not exists(
            select J2.ID_ from ACT_RU_JOB J2
            where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_                                           -- from the same proc. inst.
            and (J2.EXCLUSIVE_ = true)                                                              -- also exclusive
            and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?)  -- in progress
            )
   )
    or
     
    RES.EXCLUSIVE_ = false
   
   )

       

     
    LIMIT ? OFFSET ? [42000-210]
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Job.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute
### The error occurred while executing a query

Niall · April 19, 2022, 10:33am

Can you format this so that it’s readable?

walia · April 19, 2022, 10:39am

Not really sure how to do it here but tried my best to edit it

Niall · April 19, 2022, 10:40am

You can check out this handy page that explains how to format things in Markdown.

walia · April 22, 2022, 11:23am

Any updates on this and when can this be fixed ?