Compability Issue with JDBC Persistence Addon an SQL Server #4035
Description
Describe the bug
Hi team,
when using Kogito with Spring Boot and the JDBC Persistence addon to store the process data in an SQL Server, we get the following error in the first Flyway (V1.35.0__create_runtime_ansi.sql) script:
Line 4: The size (1000000) given to the column 'payload' exceeds the maximum allowed for any data type (8000).
What works is changing varbinary(1000000) to varbinary(max).
Furthermore, the next script V10.0.0__add_business_key_ansi.sql, gives us the error:
[2025-08-22 15:19:21] [S0000][1778] Line 1: Column 'process_instances.id' is not the same data type as referencing column 'business_key_mapping.process_instance_id' in foreign key 'fk_process_instances'.
[2025-08-22 15:19:21] [S0001][1750] Line 1: Could not create constraint or index. See previous errors.
Can be fixed with going from VARCHAR to CHARACTER for process_instance_id.
From the docs I see that Kogito has main support for Postgresql and other databases should be also supported. I'm aware that you can't model the SQL scripts for all vendors and it may fail for some database.
Expected behavior
The Flyway scripts all run succesfully.
Actual behavior
Script V1.35.0__create_runtime_ansi.sql and V10.0.0__add_business_key_ansi.sql fails
How to Reproduce?
Use the JDBC Persistence Addon and work with the SQL Server, e.g. locally the azure-sql-edge Docker image.
Output of uname -a or ver
24.6.0 Darwin Kernel Version 24.6.0: Mon Jul 14 11:30:40 PDT 2025; root:xnu-11417.140.69~1/RELEASE_ARM64_T6041 arm64
Output of java -version
openjdk version "24" 2025-03-18 OpenJDK Runtime Environment Temurin-24+36 (build 24+36) OpenJDK 64-Bit Server VM Temurin-24+36 (build 24+36, mixed mode, sharing)
GraalVM version (if different from Java)
No response
Kogito version or git rev (or at least Quarkus version if you are using Kogito via Quarkus platform BOM)
10.1.0
Build tool (ie. output of mvnw --version or gradlew --version)
Apache Maven 3.9.6 (bc0240f3c744dd6b6ec2920b3cd08dcc295161ae) Maven home: /Users/rieckpil/.m2/wrapper/dists/apache-maven-3.9.6-bin/f58cd88c/apache-maven-3.9.6 Java version: 24, vendor: Eclipse Adoptium, runtime: /Users/rieckpil/.sdkman/candidates/java/24-tem Default locale: en_US, platform encoding: UTF-8 OS name: "mac os x", version: "15.6", arch: "aarch64", family: "mac"
Additional information
As a workaround we disabled Flyway with kie.flyway.enabled=false and added the required tables with the fixes on our own.
This isn't the best scenario as updating Kogito then requires an additional check for new table adjustments.