Corrupted database - new scenario

[Silvio]

I have another database corruption case. But this time it is on a PageStore DB and the exception contained a different message:

Row not found when trying to delete from index """"".I18: ( /* key:9812 */ 'ea5d1a40-2af9-4d88-8694-bac2c989f28d', '83e2f118-2201-475d-9746-a60a97155cb7', 4, 4, '490c65e0-dc34-46d2-94a0-6b481581ac65', TIMESTAMP '2018-09-27 09:48:55.191', TIMESTAMP '2019-05-27 17:31:34.966', 6, NULL, NULL, TIMESTAMP '2018-09-27 10:20:52.713', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TIMESTAMP '2019-05-27 17:31:34.957', NULL, NULL, 1, NULL, NULL, 2, NULL, NULL, NULL, NULL, NULL)" [90112-199]

I tried to use the recover tool on the database. Where my previous attempts to recover corrupted databases would only succeed using version 197 or older this time 199 has no problems creating a perfectly plausible SQL file of about twice the size of the database.

However, trying to re-create the database using the RunScript tool with 199 gives the following exception:

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "

CREATE CACHED TABLE IF NOT EXISTS INFORMATION_SCHEMA.LOBS(

    ID BIGINT NOT NULL,

    BYTE_COUNT BIGINT,

    TABLE[*] INT

)

HIDDEN"; expected "identifier"; SQL statement:

CREATE CACHED TABLE IF NOT EXISTS INFORMATION_SCHEMA.LOBS(

    ID BIGINT NOT NULL,

    BYTE_COUNT BIGINT,

    TABLE INT

)

HIDDEN [42001-199]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:451)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)

at org.h2.message.DbException.getSyntaxError(DbException.java:243)

at org.h2.command.Parser.readColumnIdentifier(Parser.java:4530)

at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:7927)

at org.h2.command.Parser.parseCreateTable(Parser.java:7832)

at org.h2.command.Parser.parseCreate(Parser.java:5818)

at org.h2.command.Parser.parsePrepared(Parser.java:846)

at org.h2.command.Parser.parse(Parser.java:788)

at org.h2.command.Parser.parse(Parser.java:760)

at org.h2.command.Parser.prepareCommand(Parser.java:683)

at org.h2.engine.Session.prepareLocal(Session.java:627)

at org.h2.engine.Session.prepareCommand(Session.java:565)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292)

at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)

at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)

at org.h2.tools.RunScript.process(RunScript.java:261)

at org.h2.tools.RunScript.process(RunScript.java:192)

at org.h2.tools.RunScript.process(RunScript.java:328)

at org.h2.tools.RunScript.runTool(RunScript.java:143)

at org.h2.tools.RunScript.main(RunScript.java:70)

Using the RunScript tool with 196 to recreate the database works without any issues.

What could be wrong here?

[Evgenij Ryazanov]

Unfortunately, you need to enclose the "TABLE" into parentheses here with a some text editor. It is a new keyword.

Recent versions of H2 can load old databases with such unquoted identifiers, but they can't load a script that contains them.

Recent versions also always quote all identifiers during SQL generation, so this issue will not appear again when new keywords will appear.

[Silvio]

Thanks Evgenij,

That sounds plausible. It is just that I would have expected the Recover tool to be updated as well. Now the Recovery tool in 199 generates SQL that 199 can not process. A minor detail but that threw me off a bit. Fortunately I was able to recover the database so little harm was done.

[Evgenij Ryazanov]

Unfortunately, this tool exports medatadata (that may contain unquoted identifiers) as is.

Databases that were created by 1.4.199 should not be affected.

[Silvio]

Thanks Evgenij, that also makes sense. The database was indeed created using an older version of H2.