Invalid SQL upgrade scripts for HSQL database (7.0.0 -> 7.0.1)

[gorbunkov]

Describe the bug

After upgrading Flowable version from 7.0.0 to 7.0.1 application fails to start if HSQL database is used.

The exception:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'appRepositoryServiceBean' defined in class path resource [org/flowable/spring/boot/app/AppEngineServicesAutoConfiguration.class]: Unsatisfied dependency expressed through method 'appRepositoryServiceBean' parameter 0: Error creating bean with name 'flowableAppEngine': FactoryBean threw exception on object creation
	at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:795) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:542) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1335) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1165) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:562) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:522) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:975) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:962) ~[spring-context-6.1.6.jar:6.1.6]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:624) ~[spring-context-6.1.6.jar:6.1.6]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:754) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:456) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:334) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1354) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1343) ~[spring-boot-3.2.5.jar:3.2.5]
	at com.example.demo.FlowableHsqlUpgradeApplication.main(FlowableHsqlUpgradeApplication.java:10) ~[main/:na]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flowableAppEngine': FactoryBean threw exception on object creation
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:188) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:124) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1850) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getObjectForBeanInstance(AbstractAutowireCapableBeanFactory.java:1276) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:259) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:254) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.addCandidateEntry(DefaultListableBeanFactory.java:1696) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1653) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1397) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1353) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:904) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:782) ~[spring-beans-6.1.6.jar:6.1.6]
	... 18 common frames omitted
Caused by: org.flowable.common.engine.api.FlowableException: couldn't upgrade db schema: alter table ACT_RU_TASK add ( 
STATE_ varchar(255), 
IN_PROGRESS_TIME_ timestamp, 
IN_PROGRESS_STARTED_BY_ varchar(255), 
CLAIMED_BY_ varchar(255), 
SUSPENDED_TIME_ timestamp, 
SUSPENDED_BY_ varchar(255), 
IN_PROGRESS_DUE_DATE_ timestamp)
	at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.executeSchemaResource(AbstractSqlScriptBasedDbSchemaManager.java:354) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.executeSchemaResource(AbstractSqlScriptBasedDbSchemaManager.java:238) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.dbSchemaUpgrade(AbstractSqlScriptBasedDbSchemaManager.java:106) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.db.ServiceSqlScriptBasedDbSchemaManager.schemaUpdate(ServiceSqlScriptBasedDbSchemaManager.java:83) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.db.ProcessDbSchemaManager.schemaUpdate(ProcessDbSchemaManager.java:254) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:54) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:28) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:72) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:176) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:121) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:77) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:26) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:105) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:57) ~[flowable-spring-common-7.0.1.jar:7.0.1]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-6.1.6.jar:6.1.6]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:57) ~[flowable-spring-common-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:83) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:874) ~[flowable-engine-7.0.1.jar:7.0.1]
	at org.flowable.spring.SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:76) ~[flowable-spring-7.0.1.jar:7.0.1]
	at org.flowable.engine.spring.configurator.SpringProcessEngineConfigurator.initProcessEngine(SpringProcessEngineConfigurator.java:58) ~[flowable-spring-configurator-7.0.1.jar:7.0.1]
	at org.flowable.engine.spring.configurator.SpringProcessEngineConfigurator.configure(SpringProcessEngineConfigurator.java:47) ~[flowable-spring-configurator-7.0.1.jar:7.0.1]
	at org.flowable.common.engine.impl.AbstractEngineConfiguration.configuratorsAfterInit(AbstractEngineConfiguration.java:1122) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	at org.flowable.app.engine.AppEngineConfiguration.init(AppEngineConfiguration.java:239) ~[flowable-app-engine-7.0.1.jar:7.0.1]
	at org.flowable.app.engine.AppEngineConfiguration.buildAppEngine(AppEngineConfiguration.java:195) ~[flowable-app-engine-7.0.1.jar:7.0.1]
	at org.flowable.app.spring.SpringAppEngineConfiguration.buildAppEngine(SpringAppEngineConfiguration.java:66) ~[flowable-app-engine-spring-7.0.1.jar:7.0.1]
	at org.flowable.app.spring.AppEngineFactoryBean.getObject(AppEngineFactoryBean.java:58) ~[flowable-app-engine-spring-7.0.1.jar:7.0.1]
	at org.flowable.app.spring.AppEngineFactoryBean.getObject(AppEngineFactoryBean.java:31) ~[flowable-app-engine-spring-7.0.1.jar:7.0.1]
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:182) ~[spring-beans-6.1.6.jar:6.1.6]
	... 30 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: (
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.7.2.jar:2.7.2]
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.7.2.jar:2.7.2]
	at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.7.2.jar:2.7.2]
	at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.7.2.jar:2.7.2]
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.0.1.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
	at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.executeSchemaResource(AbstractSqlScriptBasedDbSchemaManager.java:325) ~[flowable-engine-common-7.0.1.jar:7.0.1]
	... 60 common frames omitted
Caused by: org.hsqldb.HsqlException: unexpected token: (
	at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.7.2.jar:2.7.2]
	at org.hsqldb.result.Result.getException(Unknown Source) ~[hsqldb-2.7.2.jar:2.7.2]
	... 66 common frames omitted

It looks like the problem is in the following SQL update scripts:

• /org/flowable/task/service/db/upgrade/flowable.hsql.upgradestep.7000.to.7010.task.sql
• /org/flowable/task/service/db/upgrade/flowable.hsql.upgradestep.7000.to.7010.task.history.sql

These files should have the following content:

alter table ACT_RU_TASK add column STATE_ varchar(255);
alter table ACT_RU_TASK add column IN_PROGRESS_TIME_ timestamp;
alter table ACT_RU_TASK add column IN_PROGRESS_STARTED_BY_ varchar(255);
alter table ACT_RU_TASK add column CLAIMED_BY_ varchar(255);
alter table ACT_RU_TASK add column SUSPENDED_TIME_ timestamp;
alter table ACT_RU_TASK add column SUSPENDED_BY_ varchar(255);
alter table ACT_RU_TASK add column IN_PROGRESS_DUE_DATE_ timestamp;

update ACT_GE_PROPERTY set VALUE_ = '7.0.1.0' where NAME_ = 'task.schema.version';

alter table ACT_HI_TASKINST add column STATE_ varchar(255);
alter table ACT_HI_TASKINST add column IN_PROGRESS_TIME_ timestamp;
alter table ACT_HI_TASKINST add column IN_PROGRESS_STARTED_BY_ varchar(255);
alter table ACT_HI_TASKINST add column CLAIMED_BY_ varchar(255);
alter table ACT_HI_TASKINST add column SUSPENDED_TIME_ timestamp;
alter table ACT_HI_TASKINST add column SUSPENDED_BY_ varchar(255);
alter table ACT_HI_TASKINST add column COMPLETED_BY_ varchar(255);
alter table ACT_HI_TASKINST add column IN_PROGRESS_DUE_DATE_ timestamp;