View Issue Details

IDProjectCategoryView StatusLast Update
0005546SymmetricDSNew Featurepublic2022-12-16 15:20
Reporteremiller Assigned To 
Status closedResolutionno change required 
Product Version3.14.0 
Summary0005546: Add conversion tool for upgrading from H2 1.x to H2 2.x
DescriptionWhen upgrading to 3.15, any existing H2 databases will have to be upgraded to the latest 2.x version. It would be convenient if there was a conversion tool available to fix the existing H2 database URL(s) and migrate existing data to the new H2 database(s).
TagsNo tags attached.


related to 0005545 closedemiller SymmetricDS Add support for H2 2.x and upgrade the H2 driver to the latest version 
related to 0005637 closedpmarzullo SymmetricDS Pro Set SET MODE LEGACY in db.init.sql for H2 databases in engines, remove DB_CLOSE_ON_EXIT from db URL if AUTO_SERVER also set 



2022-12-13 13:23

developer   ~0002242

This conversion tool will be necessary for any load only, extract only, or log mining nodes.


2022-12-13 13:25

developer   ~0002243

Can we set MODE=LEGACY on the db.url for a smoother upgrade?


2022-12-13 13:50

developer   ~0002244

The necessity of the conversion tool has become not necessary now since the new H2 version will continue to run in page mode if the database already exists


2022-12-13 13:53

developer   ~0002245

Here is the list of functionalities supported by MODE=LEGACY:

Empty IN predicate is allowed.
TOP clause in SELECT is allowed.
OFFSET/LIMIT clauses are allowed.
MINUS can be used instead of EXCEPT.
IDENTITY can be used as a data type.
MS SQL Server-style IDENTITY clause is supported.
Legacy SERIAL and BIGSERIAL data types are supported.
If a value for identity column was specified in an INSERT command the base value of sequence generator of this column is updated if current value of generator was smaller (larger for generators with negative increment) than the inserted value.
Identity columns have implicit DEFAULT ON NULL clause. It means a NULL value may be specified for this column in INSERT command and it will be treated as DEFAULT.
Oracle-style CURRVAL and NEXTVAL can be used on sequences.
TOP clause can be used in DELETE and UPDATE.
Non-standard Oracle-style WHERE clause can be used in standard MERGE command.
Attempt to reference a non-unique set of columns from a referential constraint will create an UNIQUE constraint on them automatically.
Unsafe comparison operators between numeric and boolean values are allowed.
IDENTITY() and SCOPE_IDENTITY() are supported, but both are implemented like SCOPE_IDENTITY()

I don't see anything on there that would be required, but we could add the string to the URL or add the SET MODE LEGACY to the db.init.sql property in the engine file.


2022-12-16 15:20

developer   ~0002247

Closing, we will set db.init.sql=Set SET MODE LEGACY in engine file instead

Issue History

Date Modified Username Field Change
2022-10-21 14:20 emiller New Issue
2022-10-21 14:21 emiller Relationship added related to 0005545
2022-11-09 20:35 cquamme Assigned To => cquamme
2022-11-09 20:35 cquamme Status new => assigned
2022-11-14 15:14 cquamme Assigned To cquamme =>
2022-11-16 19:41 cquamme Status assigned => acknowledged
2022-12-13 13:23 pmarzullo Note Added: 0002242
2022-12-13 13:25 elong Note Added: 0002243
2022-12-13 13:50 pmarzullo Note Added: 0002244
2022-12-13 13:53 pmarzullo Note Added: 0002245
2022-12-16 15:19 elong Relationship added related to 0005637
2022-12-16 15:20 elong Status acknowledged => closed
2022-12-16 15:20 elong Resolution open => no change required
2022-12-16 15:20 elong Note Added: 0002247