Joins failing as a result of a corrupt index on a primary key

[Gingda]

I am executing a query that is failing to join on the primary key.

If I query by other values the record is present in the table with a matching index, but if I attempt to query by the index value no records are returned.

I poked around on it trying to get it to reconstruct the index but I could not figure out how to drop the index because it isn't possible to drop it as it is referenced in constraints.

I attempted a dummy update of the record, again to try and force a reconstruction, but it gave a "Timeout trying to lock table" error though the database was opened in embedded mode in SQuirreL with no pending transactions.

We are using the latest stable version of H2DB.

The database is not large, and it is easy to illustrate the problem with 3-4 queries, so if Thomas sees that and would like to see it, there is no problem supplying a test case.

[Noel Grandin]

You have a corrupted database. Your best bet is to export to script and re-create it.

[Gingda]

Thanks for the reply.

Tried that this morning and it seemed to work fine. Had to edit the .sql file created by the recovery tool to eliminate a record that it was attempting to insert that violated the primary key on a table. I assume because the corrupt index was not recognizing at the time that the record that was being inserted violated the unique constraint on the table.

Haven't verified, but I suspect that the corruption was the result of mix and matching a .mv.db file with a .trace.db file from an earlier/previous state on the database.