Table not found after ALTER table - leftover "_COPY_" table

[jackp...@gmail.com]

I have an application that uses H2 v1.4.199, with versioning provided by Flyway.

For roughly 25% of the users, this migration ends up in an unusual state.

The migration is performing a number ALTER TABLE commands (typically dropping columns) on one database (lets call the table "fruits").

When I connect to the database, I get the following:

2021-02-02 10:11:18 database: opening mydatabase

org.h2.message.DbException: Table "FRUITS" not found [42102-200]

at org.h2.message.DbException.get(DbException.java:205)

at org.h2.message.DbException.get(DbException.java:181)

at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:108)

at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:78)

at org.h2.engine.MetaRecord.execute(MetaRecord.java:60)

at org.h2.engine.Database.open(Database.java:759)

at org.h2.engine.Database.openDatabase(Database.java:307)

at org.h2.engine.Database.<init>(Database.java:301)

at org.h2.engine.Engine.openSession(Engine.java:74)

at org.h2.engine.Engine.openSession(Engine.java:192)

at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)

at org.h2.engine.Engine.createSession(Engine.java:166)

at org.h2.engine.Engine.createSession(Engine.java:29)

at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:340)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:173)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)

at org.h2.Driver.connect(Driver.java:69)

at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:309)

at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:289)

at org.h2.server.web.WebServer.getConnection(WebServer.java:785)

at org.h2.server.web.WebApp.login(WebApp.java:1008)

at org.h2.server.web.WebApp.process(WebApp.java:223)

at org.h2.server.web.WebApp.processRequest(WebApp.java:173)

at org.h2.server.web.WebThread.process(WebThread.java:139)

at org.h2.server.web.WebThread.run(WebThread.java:94)

at java.base/java.lang.Thread.run(Thread.java:832)

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "FRUITS" not found; SQL statement:

ALTER TABLE PUBLIC.FRUITS ADD CONSTRAINT PUBLIC.CONSTRAINT_23 PRIMARY KEY(ID) INDEX PUBLIC.PRIMARY_KEY_23 [42102-200]

The "fruits" table has a constraint, but they end up without the "fruits" table that acts on and ends up stuck - and you can't even connect to the database.

Looking through the debug logs, I can see the following:

2021-02-02 10:11:18 lock: 1 exclusive write lock requesting for FRUITS_COPY_4_3 

2021-02-02 10:11:18 lock: 1 exclusive write lock added for FRUITS_COPY_4_3 

Looking through the H2 source code, I can see that a temporary table is created with the suffix of "_COPY_", so I'm assuming the existing table is dropped but somehow the temporary table is persisted with the new name,, which means the existing table name is no longer being used.

Unfortunately I'm not able to reproduce this so it's difficult to provide any further information.

Can anyone give me any pointers as to where to look next?

[Evgenij Ryazanov]

Hello.

Error code 42102-200 is from H2 1.4.200, you should not try to use both 1.4.199 and 1.4.200 with the same database file.

If you have a database with corrupted metadata, you can add

;DATABASE_EVENT_LISTENER='path.to.Listener'

to the connection url, where path.to.Listener is a fully-qualified class name.

Your class must implement org.h2.api.DatabaseEventListener and it needs public no-arg constructor.

[jackp...@gmail.com]

Apologies, I had already tried this in the environment the database was created with, using H2 1.4.199 and got the following error:

2021-01-25 14:57:04,226 ERROR main h2database - mydatabase:database opening mydatabase

org.h2.message.DbException: Table "FRUITS" not found [42102-199]

at org.h2.message.DbException.get(DbException.java:205)

at org.h2.message.DbException.get(DbException.java:181)

at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:108)

at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:78)

at org.h2.engine.MetaRecord.execute(MetaRecord.java:60)

at org.h2.engine.Database.open(Database.java:842)

at org.h2.engine.Database.openDatabase(Database.java:319)

at org.h2.engine.Database.<init>(Database.java:313)

at org.h2.engine.Engine.openSession(Engine.java:69)

at org.h2.engine.Engine.openSession(Engine.java:201)

at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)

at org.h2.engine.Engine.createSession(Engine.java:161)

at org.h2.engine.Engine.createSession(Engine.java:31)

at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:336)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:169)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:148)

at org.h2.Driver.connect(Driver.java:69)

at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)

... more

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "FRUITS" not found; SQL statement:

ALTER TABLE PUBLIC.FRUITS ADD CONSTRAINT PUBLIC.CONSTRAINT_23 PRIMARY KEY(ID) INDEX PUBLIC.PRIMARY_KEY_23 [42102-199]

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

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

... 80 more

This isn't related to switching versions, I just recreated it to get the logs on the wrong version.

[jackp...@gmail.com]

Hi, just wondered if there were any more thoughts?

[Shubham kapoor]

Hi,

Were you able to find the cause as I am also getting the same issue in my application. 

Thanks,

Shubham