Migrating 2.0.201 to 2.0.203-Snapshot: Table "SYSTEM_LOB_STREAM" already exists

[manticore-projects]

Greetings!

I have a valid 2.0.201 DB which I wanted to migrate to latest 2.0.203 GIT Snapshot. Normal "Export to Script and Create DB from Script" route. Export to script worked well, but create from script fails:

    [java] Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SYSTEM_LOB_STREAM" already exists; SQL statement:
     [java]            
     [java] CREATE CACHED TABLE "PUBLIC"."SYSTEM_LOB_STREAM"(
     [java]     "ID" INTEGER SELECTIVITY 1 NOT NULL,
     [java]     "PART" INTEGER SELECTIVITY 99 NOT NULL,
     [java]     "CDATA" CHARACTER VARYING SELECTIVITY 1,
     [java]     "BDATA" BINARY VARYING SELECTIVITY 100
     [java] ) [42101-203]
     [java] 	at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
     [java] 	at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
     [java] 	at org.h2.message.DbException.get(DbException.java:227)
     [java] 	at org.h2.message.DbException.get(DbException.java:203)
     [java] 	at org.h2.command.ddl.CreateTable.update(CreateTable.java:88)
     [java] 	at org.h2.command.CommandContainer.update(CommandContainer.java:173)
     [java] 	at org.h2.command.Command.executeUpdate(Command.java:247)
     [java] 	at org.h2.command.dml.RunScriptCommand.execute(RunScriptCommand.java:111)
     [java] 	at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:73)
     [java] 	at org.h2.command.CommandContainer.update(CommandContainer.java:173)
     [java] 	at org.h2.command.Command.executeUpdate(Command.java:247)
     [java] 	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252)
     [java] 	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
     [java] 	at com.manticore.h2.H2MigrationTool.createFromScript(H2MigrationTool.java:802)
     [java] 	at com.manticore.h2.H2MigrationTool.migrate(H2MigrationTool.java:913)

"QUIRKS MODE" has been activated, as well as "Convert BINARY to VARBINARY".

[manticore-projects]

It is not a general error, I have been able to migrate another database going the same route without any problems.
Maybe check:

a) why SYSTEM_LOB_STREAM could be created twice
b) or skip creation of SYSTEM_LOB_STREAM silently when it exists already?

Problem is: I can't even repair the DB because I do not know, what/why triggers is.

Would the DB SQL script help you? It is fairly simple and not too large.

[katzyn]

SCRIPT command exports data in order.

There is a table export stage. During it H2 writes table definitions (excluding non-primary key constraints) with CREATE TABLE, data with INSERT, and indexes with CREATE INDEX.

If some table has a LOB in it, H2 creates temporary objects before related INSERT command.
Their definitions look like

CREATE TABLE IF NOT EXISTS SYSTEM_LOB_STREAM(ID INT NOT NULL, PART INT NOT NULL, CDATA VARCHAR, BDATA VARBINARY);
ALTER TABLE SYSTEM_LOB_STREAM ADD CONSTRAINT SYSTEM_LOB_STREAM_PRIMARY_KEY PRIMARY KEY(ID, PART);
CREATE ALIAS IF NOT EXISTS SYSTEM_COMBINE_CLOB FOR 'org.h2.command.dml.ScriptCommand.combineClob';
CREATE ALIAS IF NOT EXISTS SYSTEM_COMBINE_BLOB FOR 'org.h2.command.dml.ScriptCommand.combineBlob';

They should be written only once.

After completion of this stage if these objects were created, H2 writes the following commands

DROP TABLE IF EXISTS SYSTEM_LOB_STREAM;
CALL SYSTEM_COMBINE_BLOB(-1);
DROP ALIAS IF EXISTS SYSTEM_COMBINE_CLOB;
DROP ALIAS IF EXISTS SYSTEM_COMBINE_BLOB;

to destroy them.

CREATE CACHED TABLE "PUBLIC"."SYSTEM_LOB_STREAM" looks like an exported leftover table after incomplete execution of data import script. If your old database has such table, it should be dropped together with related function aliases before execution of SCRIPT command. SCRIPT command doesn't detect presence of these objects by itself, it assumes a consistent state of database.

[katzyn]

BTW, H2 1.1.100 (2008-10-04) was the latest version where CALL SYSTEM_COMBINE_BLOB(-1) was needed. There are no reasons to write this command anymore.

[manticore-projects]

Thank you for the prompt response @katzyn.

Of course I can run those DROP ... commands on the old database, before exporting to script.

However, I still wonder if it would not make sense to run it as part of the EXPORT TO SCRIPT automatically, maybe on all similar internal tables?

Somehow, those tables have been left over and I do not think it has been my fault (except being guilty of trying almost all possible snapshots during the 2.0.201 development).

My point is: Even when I drop this particular tables and aliases now, maybe more similar problems would show? Why not tackle it at the source?

[katzyn]

If SCRIPT command was aborted before completion, already exported commands shouldn't be used.

If RUNSCRIPT command was aborted before completion, this database shouldn't be used.

If dump needs to be edited, this operation should be performed carefully.

I think SCRIPT shouldn't ever try to modify the database. Hypothetically it can warn about strange unexpected tables, but H2 still doesn't have SQL warnings.

[katzyn]

I think H2 should create such tables as LOCAL TEMPORARY tables and possibly not in the PUBLIC schema.

But there is a big question about temporary aliases. From my point of view H2 should use some internal built-in functions instead of them.

[manticore-projects]

Maybe also flag them as "SYSTEM" or "INTERNAL" and prevent from using?
I mean, what if accidentally I would have created a Business Object Schema with a table "SYSTEM_LOB_STREAM" for my own purpose? It was not impossible when storing/converting large Excel Files or SVG Files as we do (although I do admit it would be a very unlucky punch).

Is there a list of "RESERVED OBJECT NAMES" similar to the "RESERVED KEYWORDS" list?

[katzyn]

I mean, what if accidentally I would have created a Business Object Schema with a table "SYSTEM_LOB_STREAM" for my own purpose?

That's why H2 shouldn't create anything in the PUBLIC schema by itself. But to fix this potential problem we need many changes.

Is there a list of "RESERVED OBJECT NAMES" similar to the "RESERVED KEYWORDS" list?

No.

But if you didn't specify a name of constraint, H2 will choose an own one (CONSTRAINT_*). If constraint needs an index and compatible index doesn't exists yet, H2 will create an own one (PRIMARY_KEY_*, CONSTRAINT_INDEX_*). Identity columns have hidden sequences (SYSTEM_SEQUENCE_*). CTE creates a temporary view (still in the wrong scope). ALTER TABLE creates a temporary clone. (Tables and views share the same namespace, but other kinds of objects have own namespaces.)