Tips for upgrading H2 database in your test-suite

Blog: Drools & jBPM Blog

“Companies pay too much attention to the cost of doing something. They should worry more about the cost of not doing it.” (Philip Kotler)

In the world of IT, there’s an old adage that states: “Months of debugging can save days of reading the documentation.” Jokes aside, while documentation is crucial for understanding software libraries, it may not always provide all the answers, especially when upgrading a library to newer versions. Unexpected issues and regressions can arise, requiring additional help from the developer community.

In this brief post, we aim to give some tips for H2 upgrade (based on our recent experience) with the purpose of assisting other developers facing challenges when upgrading the H2 library from an early version (e.g., 1.4.19x) to a newer release (e.g., 2.2.220, as in our case).

The H2 in-memory database is widely used for persistence tests in KIE projects and hadn’t been updated to recent versions for years. As a result, we foresaw that there would be a significant challenge. “Between version 1.4.200 and version 2.0.202 there have been considerable changes, such that a simple update is not possible” documentation warned us, and we were even before that point.

So hands-on, and let’s dive in and explore some tips for H2 upgrade with samples and considerations we followed for a smooth transition.

First tip: you may have reserved words in your tables

Our first step was to delete "MVCC=true" from the JDBC URL parameters and add "MODE=LEGACY" to keep the compatibility. However, this was not enough as the following exception was raised.

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "CORRELATIONPROPERTYINFO" not found; SQL statement:

insert into CorrelationPropertyInfo (correlationKey_keyId, name, value, OPTLOCK, propertyId) values (?, ?, ?, ?, ?) [42102-220]

Diving in the logs, we found the culprit:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: 

Syntax error in SQL statement 

"create table CorrelationPropertyInfo (propertyId bigint generated by default as identity, name varchar(255), [*]value varchar(255), OPTLOCK integer, correlationKey_keyId bigint, primary key (propertyId))"; 

expected "identifier"; SQL statement:

So, the exception message states that the CorrelationPropertyInfo table cannot be created. The root cause is this table has a column named "value" and this is a reserved word for H2; therefore, it fails from version 200.

In this case, H2 documentation mentions a workaround we can use:

“NON_KEYWORDS setting that can be used as a temporary workaround if your application uses them as unquoted identifiers.”

For a quick replacement in all files, we used this “sed” command:

find ./ -type f -exec sed -i 's/MVCC=[Tt][Rr][Uu][Ee]/MODE=LEGACY;NON_KEYWORDS=VALUE/g' {} ;

Second tip: hibernate perhaps need to be upgraded too

The next challenge was a little bit difficult to find out. We realized that database cleanup was not correctly done, and after activating logs we saw this exception in tests:

h2 SchemaDropperImpl$DelayedDropActionImpl] ERROR HHH000478: Unsuccessful: drop table if exists

This hibernate issue HHH-13711 explains the details about it, and the disruptive change in H2 that provokes it.

Therefore, we need to upgrade hibernate-core to at least 5.4.13.Final or 6.0.0.Final.

NOTE: In case your code contains some of the StandardDialectResolver constants, notice that they were removed in 5.4.0 as stated in HHH-12879.

Then, you will have to replace:

DialectResolver resolver = StandardDialectResolver.INSTANCE;

DialectResolver resolver = new StandardDialectResolver();

Third tip: drop-source strategy switches to “script” with DROP ALL OBJECTS

In our container tests, however, the previous tip was not easy to apply. Wildfly version 23 is not updated yet to the hibernate-core library version that we need:

./cargo/installs/wildfly-dist-23.0.0.Final/wildfly-23.0.0.Final/modules/system/layers/base/org/hibernate/main/hibernate-core-5.3.20.Final.jar

So what to do in this case? Instead of relying on metadata for dropping the tables (default behavior), let’s make a trick and set that the schema shall be dropped based on a script. We need to add these two properties with their corresponding variables:

<property name="javax.persistence.schema-generation.drop-source" value="${org.jbpm.drop.source}" />


<property name="javax.persistence.schema-generation.drop-script-source" value="${org.jbpm.drop.script}" />

Where default values are defined as:

script


drop-tables.sql

Notice that they can be overridden for keeping the same behavior for the rest of the databases.

And for H2, drop-tables.sql is not considering a CASCADE strategy but a single-shot action, avoiding the issue with the foreign keys:

DROP ALL OBJECTS

Fourth tip: creating database from TCP connection needs “ifNotExists”

The next error was for those databases created remotely from a TCP connection. Exception showed us that the database didn’t exist:

Database … not found, either pre-create it or allow remote database creation (not recommended in secure environments)

Older versions of H2 (up to 1.4.197) allowed the database creation by default, introducing a security hole in your system.

So, we need to set up the parameter "ifNotExists" explicitly when creating the H2 database. Again, with the "sed" command, we can rapidly modify all files containing that pattern:

find ./ -type f -exec sed -i 's/createTcpServer(new String[0])/createTcpServer(new String[]{"-ifNotExists"})/g' {} ;

NOTE: Take into account this advice from H2 documentation:

“Its combination with -tcpAllowOthers, -pgAllowOthers, or -webAllowOthers effectively creates a remote security hole in your system, if you use it, always guard your ports with a firewall or some other solution and use such combination of settings only in trusted networks.”

Conclusion

For many reasons (security and bug fixes, performance improvements, compatibility, new features, community support, and ecosystem compatibility) is very important to upgrade open-source libraries to the latest stable version.

In this post, we have followed the process for stabilizing our tests after upgrading H2 from 1.4.197 (set two years ago) to the 2.2.220 version.

We will be delighted if any of the tips for H2 upgrade shared here prove helpful to the community.

Happy upgraded testing!

The post Tips for upgrading H2 database in your test-suite appeared first on KIE Community.