升级Spring Boot至2.7后Flowable引擎表创建及连接异常

将spring-boot-starter-parent升级至2.7.1、flowable-engine升级至6.8.0后，JUnit测试出现两类问题：

1. H2数据库表创建失败：无法生成ACT_HI_TSK_LOG、ACT_EVT_LOG表，H2报错提示未知数据类型IDENTITY
2. 升级至Spring Boot 2.7.7后SQL Server SSL连接失败：出现PKIX路径构建失败，找不到有效证书路径的错误

此前使用Spring Boot 2.6.x版本时所有测试均正常。

依赖配置 #

<dependency>
    <groupId>org.flowable</groupId>
    <artifactId>flowable-engine</artifactId>
    <version>6.8.0</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.1</version>
    <type>pom</type>
</dependency>

2.7.1版本错误栈 #

nested exception is org.flowable.common.engine.api.FlowableException: couldn’t create db schema: create table ACT_HI_TSK_LOG (column names .....)
..
..
..
Caused by: org.h2.jdbc.JdbcSqlNonTransmissionException: Unknown data type: "IDENTITY"; SQL Statement: create table ACT_HI_TSK_LOG (ID_ identity, TYPE_ varchar(64), TASK_ID_ varchar(64) not null, .....)

2.7.7版本错误栈 #

Error creating bean with name 'processEngine': FactoryBean threw the exception on object creation; nested exception is java.lang.RuntimeException: Exception while initializing Database connection at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:660) .........
................
ommitted caused by: java.lang.RuntimeException: Exception while initializing Database connection at
...................
......................
org.flowable.spring/SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:76) at org.flowable.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:59) at
org.flowable.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:32) at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:169) ... 65 common frames omitted Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The drive could not establish a secure connection to SQL Server by using Secure Socket Layer (SSL) exception. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:a31221-r15-erer-gdf45451 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3680) at
com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2047) at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3204) at
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833) at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671) at

一、H2数据库IDENTITY类型错误 #

问题根源是Spring Boot 2.7.x默认升级了H2版本，而Flowable 6.8.0的建表语句中IDENTITY类型在新版本H2中需要兼容模式支持。

可选解决方式：

1. 修改H2 JDBC URL添加兼容参数：
   在测试用的H2连接URL中加入MODE=LEGACY，示例：

   jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=LEGACY
   

2. 指定兼容的H2版本：
   在pom.xml中显式声明Flowable 6.8.0适配的H2版本：

   <dependency>
       <groupId>com.h2database</groupId>
       <artifactId>h2</artifactId>
       <version>1.4.200</version>
       <scope>test</scope>
   </dependency>
   

二、SQL Server SSL连接PKIX路径错误 #

Spring Boot 2.7.7默认强制SQL Server使用SSL连接，环境中缺少对应根证书导致验证失败。

可选解决方式：

1. 测试环境临时禁用SSL：
   在JDBC URL中添加encrypt=false参数，示例：

   jdbc:sqlserver://localhost:1433;databaseName=testdb;encrypt=false
   

2. 生产环境导入证书到JVM信任库：
   • 导出SQL Server的SSL证书文件
   • 使用keytool命令导入到JVM默认信任库：

     keytool -import -alias sqlserver-cert -file /path/to/your-cert.cer -keystore $JAVA_HOME/jre/lib/security/cacerts
     

   • 默认信任库密码为changeit
3. 配置自定义信任库：
   在application.properties中指定自定义信任库路径和密码：

   spring.datasource.hikari.data-source-properties.sslTrustStore=/path/to/truststore.jks
   spring.datasource.hikari.data-source-properties.sslTrustStorePassword=your-truststore-password
   

内容的提问来源于stack exchange，提问作者Soft Onz