Recovering using a .data.sql file

[witerat]

I had to recover a corrupted database that was still damaged after using the repair tool. the database was so damaged attempts to connect resulted in "Connection broken" jdbc exceptions.
repairs would allow connection but inserts in a junit test fell over with an IOException.

Anyway, the .data.sql file allow m to reconstruct the database from scratch, however it was exactly smooth. Two problems needed fixing to succeed:

• CREATE USER -- fell over because SA always exists in new databases and can't be drop because they are the current user.

 I fixed this by adding IF NOT EXISTS

• Referential integrity errors -- Data added to in an order that did not respect foreign key dependencies.

 I rearranged the insert commands, so that dependencies were added first

The script was generated by h2 version 1.1.111, if these issues haven't addressed yet, I think would be worth considering.
Trivially adding IF NOT EXISTS to the script generating code.solves one problem.
The other problem might be harder, using graph analysis to determine the order of inserts taking into account referential integrity contrstaints. Alternatively, the script could turn off referential integrity, insert data in arbitrary order, then turn referential integrity. Though the second option  doesn't catch problems that exist in the reconstructed dataset, I doubt that is an overriding concern when trying to recover a database.

[Noel Grandin]

Thanks, but that's a truly ancient version of H2.

[witerat]

Yep, it's ancient, alright. However that doesn't necessarily have any bearing on the question of whether the issues remain in the code base for more current versions.
:)

[Noel Grandin]

No, but in general, we're not likely to spend any effort checking precisely because that version is so ancient.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/DEQD7KcMlr4J.

To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[witerat]

I checked the org.h2.command.dml.Script class, in 1.3.169, it sorts tables by id, which puts them in the right order with respect to views, but no regard to dependencies in respect to each and no mention of "REFERENTIAL".
I'm not sure if this is the class that generates .data.sql files it is the best candidate I could find. It also does not appear to use temporary tables anymore.

[Ryan How]

Just my 2 cents. But if a database is corrupt then it may not have referential integrity anyway. I think there is always going to be a level of manual recovery of a corrupted database.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/uh5Lc1hpb0sJ.

[Noel Grandin]

Thanks for checking.

A while ago, Thomas changed it to create the constraints after the
tables, so there should be no dependency problems any more.

If you have a look in SVN, here:
https://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/command/dml/ScriptCommand.java
I've commented this class a little more to make it easier to read.