CODEWITHSUNDEEP
h2
funkyjive
funkyjive

Reputation: 65

Upgrade problem with h2 database when upgrading from 192 to 200 : Scale must not be bigger than precision

Years ago I wrote an app to capture data into H2 datafiles for easy transport and archival purposes. The application was written with H2 1.4.192.

Recently, I have been revisiting some load code relative to that application and I have found that there are some substantial gains to be had in some things I am doing in H2 1.4.200.

I would like to be able to load the data that I had previously saved to the other databases. But I had some tables that used a now invalid precision scale specification. Here is an example:

CREATE CACHED TABLE PUBLIC.MY_TABLE(MY_COLUMN DATETIME(23,3) SELECTIVITY 5)

H2 databases created with 1.4.192 that contain tables like this will not load on 1.4.200, they will get the following error:

Scale($"23") must not be bigger than precision({1}); SQL statement: CREATE CACHED TABLE PUBLIC.MY_TABLE(MY_COLUMN DATETIME(23,3) SELECTIVITY 5) [90051-200] 90051/90051 (Help)

My question is how can I go about correcting the invalid table schema? My application utilizes a connection to an H2 database and then loads the data it contains into another database. Ideally I'd like to have my application be able to detect this situation and repair it automatically so the app can simply utilize the older data files. But in H2 1.4.200 I get the error right up front upon connection.

Is there a secret/special mode that will allow me to connect 1.4.200 to the database to repair its schema? I hope???

Outside of that it seems like my only option is have a separate classloader for different versions of H2 and have remedial operations happen in a classloader and the load operations happen in another classloader. Either that or start another instance of the JVM to do remedial operations.

I wanted to check for options before I did a bunch of work.

This problem is similar to this reported issue, but there was no specifics in how he performed his resolution.

Upvotes: 0

Views: 654

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

This data type is not valid and was never supported by H2, but old H2, due to bug, somehow accepted it.

  1. You need to export your database to a script with 1.4.192 Beta using
SCRIPT TO 'source.sql'

You need to use the original database file, because if you opened file from 1.4.192 Beta with 1.4.200, it may be corrupted by it, such automatic upgrade is not supported.

  1. You need to replace DATETIME(23,3) with TIMESTAMP(3) or whatever you need using a some text editor. If exported SQL is too large for regular text editors, you can use a stream editor, such as sed:
sed 's/DATETIME(23,3)/TIMESTAMP(3)/g' source.sql > fixed.sql
  1. Now you can create a new database with 1.4.200 and import the edited script into it:
RUNSCRIPT FROM 'fixed.sql'

Upvotes: 1

Related Questions