H2 2.1.210 SQL output of SCRIPT not backward compatible with 1.4.200

[Silvio]

Hello all,

Executing SCRIPT TO command from H2 2.1.210 generates an SQL file that can not be processed by an application using 1.4.200. The culprit seems to be that in INSERT statements UUID values are exported in their hex-string format prefix by the UUID keyword. So it looks like

UUID 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

instead of plain

'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

which breaks 1.4.200.

Although most of our conversions are going the other way (which does work) we can currently not prevent having to move a database from a server running 210 to one that still runs 200. Is there a flag or something that can make 210 generate the legacy syntax?

[Evgenij Ryazanov]

Hello!

H2 doesn't support and never supported downgrades. The oldest version that can open database files or SQL scripts from 2.1.210 is H2 2.0.202.

You need to edit the exported SQL by yourself. For example, you can use a stream editor:

sed "s/UUID '/'/" source.sql > target.sql

But due to huge number of differences you may need to also edit many other declarations.

[Silvio]

Thank you. I already expected that. I did not seek full backward compatibility but since we use the database as a very plain table storage without foreign keys, complex joins etc we can live with compatibility for basic CREATE TABLE and INSERT statements only. So far we never had version differences manifest themselves in the SQL and I was hoping there was some kind of workaround.

[Silvio]

Well, that unfortunately does not solve the issue. After pulling out the UUID prefix for UUID-literals (did the same for the TIMESTAMP prefix) the generated SQL contains the following fragment:

U&'Co-effici\00ebnt'

which looks like an obscure escape syntax for an extended character. Trying to import this with H2-1.4.200 fails.

But since I use

SCRIPT TO 'xxx' CHARSET 'UTF-8' 

I would expect no such escaping to be present in the file.  Is there anything I can do to get rid of these escapes?

[Silvio]

I found the escape syntax in the SQL reference but I did not find anything in the change log mentioning this behavior. Am I correct in assuming that this was added after H2-1.4.200?

[Evgenij Ryazanov]

> did the same for the TIMESTAMP prefix

You don't need that. H2 1.4.200 is able to read all datetime literals properly.

> U&'Co-effici\00ebnt'

> which looks like an obscure escape syntax

H2 always quotes exported character string literals with non-printable on non-ASCII characters, I don't know why.

H2 1.x uses an own function not compatible with anything, it means script from 1.4 cannot be imported into any other database system.

H2 2.x uses more compact and more portable standard-compliant syntax, it is also parsed faster. Unfortunately, old unsupported versions of H2 can't read it.

[Silvio]

Thank you for the info. I think I will resort to just dumping the database structure and then writing all table contents in separate CSV files.