Problem

When a H2 database is migrated from 1.4.197 to 2.1.214 via SQL script and FROM_1X option, some BLOBs contain unusable data afterwards.

Steps to reproduce

1. Extract attached from_x1_bug.zip
2. Call run-test.bat
3. Open SQL script exported\script-demo-214.sql
4. Check SYSTEM_LOB_STREAM statements for TABLE_B

Expected

-- 1 +/- SELECT COUNT(*) FROM PUBLIC.TABLE_B; 
INSERT INTO SYSTEM_LOB_STREAM VALUES(0, 0, NULL, X'deadbeefdeadbeef...
INSERT INTO SYSTEM_LOB_STREAM VALUES(0, 1, NULL, X'deadbeefdeadbeef...

Actual

-- 1 +/- SELECT COUNT(*) FROM PUBLIC.TABLE_B; 
INSERT INTO SYSTEM_LOB_STREAM VALUES(1, 0, NULL, X'6465616462656566...
INSERT INTO SYSTEM_LOB_STREAM VALUES(1, 1, NULL, X'6465616462656566...
INSERT INTO SYSTEM_LOB_STREAM VALUES(1, 2, NULL, X'deadbeefdeadbeef...

The original hex data ('deafbeef...') is hex-encoded again ('6465616462656566...'). It seems that 'deadbeef...' was interpreted as a string instead of hex data.

Root Cause

The FROM_1X option adds the necessary 'X' in front of each hex data string during migration, but fails to do so when the previous line is a comment (here: "-- 1 +/- SELECT COUNT(*) FROM PUBLIC.TABLE_B;"). See RunScriptCommand.java:

    private void execute(String sql) {
        if (from1X) {
            sql = sql.trim();
            if (sql.startsWith("INSERT INTO SYSTEM_LOB_STREAM VALUES(")) {
                int idx = sql.indexOf(", NULL, '");
                if (idx >= 0) {
                    sql = new StringBuilder(sql.length() + 1).append(sql, 0, idx + 8).append("X'")
                            .append(sql, idx + 9, sql.length()).toString();
                }
            }
        }

The variable sql contains the string "-- 1 +/- SELECT COUNT(*) FROM PUBLIC.TABLE_B; \r\nINSERT INTO SYSTEM_LOB_STREAM VALUES(1, 0, NULL, 'deadbeefdeadbeef...')" when processing the affected line, so that the condition sql.startsWith("INSERT INTO SYSTEM_LOB_STREAM... does not hold.

As a consequence, the hex data is interpreted as a string by 2.1.214.

Workaround

Manually adjust the SQL dump by adding an X in front of each hex data string before importing it with version 2.1.214.