Cannot Use H2 Version 2.0.202

[nosheenzaza]

I tried to use H2 Version 2.0.202. in order to mitigate https://nvd.nist.gov/vuln/detail/CVE-2021-23463, simply by adding this section to my Spring cloud task pom file, initially generated with spring intializr https://start.spring.io/

<dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.0.202</version>
</dependency>

It complains that TASK_SEQ.nextval is not found, which is strange because I can see that the db is initialized correctly

[2021-12-20T18:21:07.647Z][main][DEBUG][org.springframework.cloud.task.repository.support.TaskRepositoryInitializer]Initializing task schema for h2 database
[2021-12-20T18:21:07.659Z][main][DEBUG][org.springframework.jdbc.datasource.DataSourceUtils]Fetching JDBC Connection from DataSource
[2021-12-20T18:21:07.662Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]Executing SQL script from class path resource [org/springframework/cloud/task/schema-h2.sql]
[2021-12-20T18:21:07.974Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]0 returned as update count for SQL: CREATE TABLE TASK_EXECUTION ( TASK_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY , START_TIME TIMESTAMP DEFAULT NULL , END_TIME TIMESTAMP DEFAULT NULL , TASK_NAME VARCHAR(100) , EXIT_CODE INTEGER , EXIT_MESSAGE VARCHAR(2500) , ERROR_MESSAGE VARCHAR(2500) , LAST_UPDATED TIMESTAMP, EXTERNAL_EXECUTION_ID VARCHAR(255), PARENT_EXECUTION_ID BIGINT )
[2021-12-20T18:21:08.229Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]0 returned as update count for SQL: CREATE TABLE TASK_EXECUTION_PARAMS ( TASK_EXECUTION_ID BIGINT NOT NULL , TASK_PARAM VARCHAR(2500) , constraint TASK_EXEC_PARAMS_FK foreign key (TASK_EXECUTION_ID) references TASK_EXECUTION(TASK_EXECUTION_ID) ) 
[2021-12-20T18:21:08.248Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]0 returned as update count for SQL: CREATE TABLE TASK_TASK_BATCH ( TASK_EXECUTION_ID BIGINT NOT NULL , JOB_EXECUTION_ID BIGINT NOT NULL , constraint TASK_EXEC_BATCH_FK foreign key (TASK_EXECUTION_ID) references TASK_EXECUTION(TASK_EXECUTION_ID) ) 
[2021-12-20T18:21:08.282Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]0 returned as update count for SQL: CREATE SEQUENCE TASK_SEQ 
[2021-12-20T18:21:08.312Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]0 returned as update count for SQL: CREATE TABLE TASK_LOCK ( LOCK_KEY CHAR(36) NOT NULL, REGION VARCHAR(100) NOT NULL, CLIENT_ID CHAR(36), CREATED_DATE TIMESTAMP NOT NULL, constraint LOCK_PK primary key (LOCK_KEY, REGION) )
[2021-12-20T18:21:08.329Z][main][DEBUG][org.springframework.jdbc.datasource.init.ScriptUtils]Executed SQL script from class path resource [org/springframework/cloud/task/schema-h2.sql] in 665 ms.

Here is the stack trace:

Caused by: org.springframework.context.ApplicationContextException: Failed to start bean 'taskLifecycleListener'; nested exception is org.springframework.dao.DataAccessResourceFailureException: Could not obtain sequence value; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "TASK_SEQ.NEXTVAL" not found; SQL statement:
select TASK_SEQ.nextval from dual [42122-202]
	at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:181)
	at org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:54)
	at org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:356)
	at java.base/java.lang.Iterable.forEach(Iterable.java:75)
	at org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:155)
	at org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:123)
	at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:935)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:586)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:754)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:434)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:338)
	at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:123)
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99)
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:124)
	... 67 more
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not obtain sequence value; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "TASK_SEQ.NEXTVAL" not found; SQL statement:
select TASK_SEQ.nextval from dual [42122-202]
	at org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer.getNextKey(AbstractSequenceMaxValueIncrementer.java:79)
	at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:128)
	at org.springframework.cloud.task.repository.dao.JdbcTaskExecutionDao.getNextExecutionId(JdbcTaskExecutionDao.java:451)
	at org.springframework.cloud.task.repository.dao.JdbcTaskExecutionDao.createTaskExecution(JdbcTaskExecutionDao.java:211)
	at org.springframework.cloud.task.repository.support.SimpleTaskRepository.createTaskExecution(SimpleTaskRepository.java:121)
	at org.springframework.cloud.task.repository.support.SimpleTaskRepository$$FastClassBySpringCGLIB$$c7861b7b.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at org.springframework.cloud.task.repository.support.SimpleTaskRepository$$EnhancerBySpringCGLIB$$157891ed.createTaskExecution(<generated>)
	at org.springframework.cloud.task.listener.TaskLifecycleListener.doTaskStart(TaskLifecycleListener.java:298)
	at org.springframework.cloud.task.listener.TaskLifecycleListener.start(TaskLifecycleListener.java:419)
	at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:178)
	... 80 more
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "TASK_SEQ.NEXTVAL" not found; SQL statement:
select TASK_SEQ.nextval from dual [42122-202]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.expression.ExpressionColumn.getColumnException(ExpressionColumn.java:248)
	at org.h2.expression.ExpressionColumn.optimizeOther(ExpressionColumn.java:230)
	at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:213)
	at org.h2.command.query.Select.prepare(Select.java:1177)
	at org.h2.command.Parser.prepareCommand(Parser.java:971)
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:614)
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:552)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111)
	at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:92)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at io.opentracing.contrib.jdbc.TracingStatement.executeQuery(TracingStatement.java:60)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at io.opentracing.contrib.common.WrapperProxy$1.invoke(WrapperProxy.java:73)
	at com.sun.proxy.$Proxy158.executeQuery(Unknown Source)
	at org.springframework.jdbc.support.incrementer.AbstractSequenceMaxValueIncrementer.getNextKey(AbstractSequenceMaxValueIncrementer.java:70)
	... 99 more

Can you please take a look? I also think that upgrading the H2 version in Spring Cloud Task base would be a good idea given the high score of the vulnerability.

[hpoettker]

The application fails because H2 does not allow Oracle-style NEXTVAL on sequences as of 2.0.x unless legacy compatibility mode is activated: http://www.h2database.com/html/features.html

You can activate the mode by appending your JDBC URL with ;MODE=LEGACY. For e.g. by setting

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;MODE=LEGACY

in your application.properties.

[cppwfs]

Thank you for opening the issue.
The resolution to the issue will be provided by the Spring framework as discussed here: spring-projects/spring-framework#27870. Once the Spring framework and Spring boot provide their releases I will schedule a release for Task as well.
Thanks to @hpoettker for providing the solution in the Spring framework. I used MODE=Oracle as a workaround; because H2 threw an exception when using MODE=LEGACY

[cppwfs]

This issue was resolved with the following PR. Use strict H2 query syntax for paging query provider #822