How to Migrate your H2 Databases to the latest H2 version

5 min readMar 14, 2022

Migration vector created by stories — www.freepick.com

With the recent security vulnerabilities discovered for the H2 engine, most of the H2 DB users using 1.x versions had to upgrade the H2 version to a 2.x version.

Upgrading the H2 version from a 1.x version to a 2.x version is not an easy task. Between the versions 1.4.200 and 2.0.202, there has been some major changes including INFORMATION_SCHEMA changes which have been made to be compliant with SQL standards but unfortunately making it non-compliant with the previous H2 versions.

Therefore, we simply cannot upgrade the version to 2.x from 1.x version without migrating our databases. In this tutorial, I will take you through the steps to migrate your databases to the 2.x version.

If you are already using a H2 database of a 1.x version and has tried to simply upgrade the H2 version and open your databases, you may have come across the following error.

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "The write format 1 is smaller than the supported format 2 [2.1.210/5]" [50000-210]

It is not possible to even open a database file created from the 1.x version using a H2-engine of 2.x version. You will always get the above error if you try doing so. Therefore you will have to backup your data from the older database and create the database from the new version and migrate the data.

Here is an easy method for you to do this:

You can download the H2 jars of previous and new versions from the H2 official website. Use the older H2 version to export the data of the required DB to a backup file using the SCRIPT command. Then create a new database from the new version and use the RUNSCRIPT command to import the data from the backup file to the new DB.

Here, I will show 2 step-by-step approaches to migrate your DB from 1.4.199 to 2.1.210.

Method 1:

Download the 1.4.199 H2 jar and 2.1.210 H2 jar from the H2 website and unzip these jars to a new folder.
Open a terminal inside the <1.4.199-folder>/bin and type:

java -cp h2-1.4.199.jar org.h2.tools.Script -url jdbc:h2:/<path-to-old-db-file>/<DB-name> -user <username> -password <password> -script backup.zip -options compression zip

The above command will use the Script tool of 1.4.199 H2 jar to backup the content of the given database to a zip file at the current directory. Provide the URL with the correct location of the database file you need to migrate,

e.g. jdbc:h2:/Users/databases/OLD_DATABASE

and provide the username and password of that database. Provide the location to the backup file to be created after the -script flag.

4. Open a terminal inside the <2.1.210-folder>/bin and type:

java -cp h2-2.1.210.jar org.h2.tools.RunScript -url jdbc:h2:/<path-to-new-db-file>/<DB-name> -user <username> -password <password> -script backup.zip -options compression zip FROM_1X

The above command will use the RunScript tool of the 2.1.210 H2 jar to create a new database with the provided credentials and import the content from the backup.zip to the new DB. Provide the URL with the location of the new database file to be created,

e.g. jdbc:h2:/Users/databases/NEW_DATABASE

and the username and password for the new DB. The FROM_1X flag is required since this is a migration from the 1.x version.

Now your new database file will be successfully created with the migrated data from the previous database, at the given location with the given credentials.

Method 2:

If you need to get a more hands-on experience with the data while doing the migration, you can use the H2 console to backup and restore the data instead of using the terminal.

Download the 1.4.199 H2 jar and 2.1.210 H2 jar from the H2 website and unzip these jars to a new folder.
Open a terminal inside the <1.4.199-folder>/bin and type the following command to log into the H2 console:

java -jar h2*.jar

This will automatically open the H2 browser console at http://localhost:8082/.

3. Provide the DB file location and the credentials to log into the old database at the following prompt.

4. Type the following command in the console and click Run.

SCRIPT TO '<backup-file-path>/backup.sql';

The above command will export the content of the logged in database to a sql file with the provided name at the given location.

5. Now, after shutting down the previous H2 server, open a terminal inside the <2.1.210-folder>/bin and type the following command to log into the H2 console:

java -cp h2*.jar org.h2.tools.Server -ifNotExists

Similarly, this command will open a new H2 browser console with a prompt to enter the DB URL and credentials with the new H2 version. The -ifNotExists flag allows us to create a new database with the given name, location and credentials if it does not exist already.

6. Log into the new database and use the following command to import the data from the backup file.

RUNSCRIPT FROM '<backup-file-path>/backup.sql';

Provide the file path you entered before to create the backup.sql file. After running the above command you will see the newly created tables with the old DB data.

If you are not up for manually doing the above tasks, here I have attached a bash script that would allow you to migrate your databases to the newer H2 version.

Migration Tool

The following script will download the 1.4.199 and 2.1.210 H2 jars from the maven central repository and use the above mentioned Method 1 to migrate data from the DB files you need.

This script can be used to migrate multiple databases available inside a given directory having the same credentials.

Note: I have used 1.4.199 and 2.1.210 jars in this script. Change the jar versions as required.

src_dir=<file-path>/old-databases
dest_dir=<file-path>/new-databases
username=<user-name>
password=<password>wget https://repo1.maven.org/maven2/com/h2database/h2/2.1.210/h2-2.1.210.jar
wget https://repo1.maven.org/maven2/com/h2database/h2/1.4.199/h2-1.4.199.jarfor filepath in $src_dir/*.mv.db; do
    dbname=$(basename "$filepath" .mv.db)
    
    # Export data from old db file to backup.zip
    echo "Exporting database..."
    java -cp h2-1.4.199.jar org.h2.tools.Script -url jdbc:h2:$src_dir/$dbname -user $username -password $password -script backup.zip -options compression zip
    rm -f $dest_dir/$dbname.mv.db# Import data from the backup.zip to the new db file
    echo "Importing data..."
    java -cp h2-2.1.210.jar org.h2.tools.RunScript -url jdbc:h2:$dest_dir/$dbname -user $username -password $password -script ./backup.zip -options compression zip
    rm -f backup.zip    echo "$dbname migrated succesfully"
donerm -f h2-1.4.199.jar
rm -f h2-2.1.210.jar

Provide the location of the old DB files for src_dir field and the location of the new directory where you need to create the new DB files for the dest_dir field.
Provide the correct username and password you use to log into the old databases and this script will create new databases with the same name and credentials at the provided location
Copy the above script and save it as migration.sh inside a new directory.
Open a terminal and run:

sh migration.sh

With this command all your databases inside the src_dir folder will be migrated to the newer version and will be available at the dest_dir folder.

Hope this article was helpful for developers struggling with the new H2 version.

Cheers!

How to Migrate your H2 Databases to the latest H2 version

References

Written by Imalsha Gunasekara