How can I repair database with error "#Row {1} not found in primary index "PUBLIC.SYS_DATA: 1570" [90143-196]"?

[Thomas Frick]

Hi there,

after trying to open an existing but unmounted/inactive database within Squirrel SQL Client, i got this error message:

[...]
class org.h2.jdbc.JdbcSQLException: #Row {1} not found in primary index "PUBLIC.SYS_DATA: 1570" [90143-196]

[...]

*urgs*

Each further try to open the DB in Squirrel or in it's owning application gives the same error.

Now it seems that this database (or the mentioned index) got corrupted and needs to be repaired - but how?

Can anyone here please give me a hint on how to repair the DB and rescue all the data in it (if it's possible)?

Thank you so far,
Thomas

[Andreas Reichel]

On Tue, 2021-02-23 at 10:09 -0800, Thomas Frick wrote:

Can anyone here please give me a hint on how to repair the DB and rescue all the data in it (if it's possible)?

Good Morning Thomas.

http://www.h2database.com/javadoc/org/h2/tools/Recover.html

(Although this has never worked for me.)

Please note: You should avoid opening a H2 database (e.g. 1.4.199) with any different H2 driver (e.g. 1.4.200).

Instead you are supposed to extract to SQL script and create the DB afresh from script.

http://www.h2database.com/javadoc/org/h2/tools/Script.html

http://www.h2database.com/javadoc/org/h2/tools/RunScript.html

Good luck and best regards

Andreas

[Thomas Frick]

Hi Andreas,

it worked perfectly for me!! :-) I was able to rescue every data-byte out of the damaged database with your suggested solution. Thank you very, very much. That saved my day - oh no - week. :-)

Lessons learned: NEVER use a different driver version to open an existing H2 DB! (application uses .196, but I gave .200 to Squirrel - I'm so stupid)

Thanks again, problem is SOLVED.