DROP COLUMN cause table table to disappear

[Meni Hillel]

I have a DB created with older version 1.4.96 and we've upgraded to 1.4.99. When altering a table to drop a column, it cause table to be renamed, to something like <table>_COPY_##_## where ## is a number. Once this happens, it get DB to a corrupted state and it is no longer usable. Is this a known issue? Is there a workaround?

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DEPLOYMENT_SPEC" not found; SQL statement:

ALTER TABLE deployment_spec DROP COLUMN vlan_pool_fk [42102-199]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[factory.jar:sxrel2.0]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[factory.jar:sxrel2.0]

at org.h2.message.DbException.get(DbException.java:205) ~[factory.jar:sxrel2.0]

at org.h2.message.DbException.get(DbException.java:181) ~[factory.jar:sxrel2.0]

at org.h2.command.Parser.tableIfTableExists(Parser.java:7497) ~[factory.jar:sxrel2.0]

at org.h2.command.Parser.parseAlterTable(Parser.java:7307) ~[factory.jar:sxrel2.0]

at org.h2.command.Parser.parseAlter(Parser.java:6546) ~[factory.jar:sxrel2.0]

at org.h2.command.Parser.parsePrepared(Parser.java:828) ~[factory.jar:sxrel2.0]

at org.h2.command.Parser.parse(Parser.java:788) ~[factory.jar:sxrel2.0]

at org.h2.command.Parser.parse(Parser.java:760) ~[factory.jar:sxrel2.0]

at org.h2.command.CommandContainer.recompileIfRequired(CommandContainer.java:108) ~[factory.jar:sxrel2.0]

at org.h2.command.CommandContainer.update(CommandContainer.java:128) ~[factory.jar:sxrel2.0]

at org.h2.command.Command.executeUpdate(Command.java:267) ~[factory.jar:sxrel2.0]

at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:233) ~[factory.jar:sxrel2.0]

at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[factory.jar:sxrel2.0]

[Evgenij Ryazanov]

1.4.196 and older versions have a problem with some constraints. This issue was fixed in 1.4.197, but the fix isn't fully compatible with older databases. Execution of DDL command in 1.4.197 in the database that was initially created by some older version may corrupt a database. This issue was fixed for default MVStore engine and wasn't fixed for legacy PageStore engine (there is a separate issue on GitHub for it).

I suggest you to use the recommended way to upgrade your databases.

1. Execute a

SCRIPT TO 'filename.sql'

command with old version of H2.

2. Create a new empty database with a new version of H2 and execute a

RUNSCRIPT FROM 'filename.sql'

You can also use the Java tools as described in the documentation:

https://h2database.com/html/tutorial.html#upgrade_backup_restore

Usually it's not really required and database can be used as it, but due to large amount of changes in recent releases it's much safer to use the recommended way.

If you don't have a backup copy you can add SET_EVENT_LISTENER parameter to the connection URL:

https://h2database.com/html/commands.html#set_database_event_listener

The listener must implement org.h2.api.DatabaseEventListener interface. With such listener some exceptions can be ignored.

You need to check a content of the database with H2 Console and fix the problems.

[Meni Hillel]

Thank you Evgenij, It seems to work.

Meni

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/jsWQhRGORnw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/7d35d455-f1ae-4a77-8c59-d5740b0c92f1%40googlegroups.com.