0

I have a database that somehow got corrupted in the following way:
A record in table A has a value in a foreign key column pointing to table B, but no such record exists in B.
The db was created in h2 version 1.3.152 and I recovered it with 1.3.170 (latest at the moment of this post).
Its also strange that the recover tool didn't complain about this (but the problem was not solved)
Any ideas of why/how this happened?
Any ideas of how to recover?

Actual example:

select * from punto where punto_id = 3309; --> does not return anything

select * from tramo where caseta_punto_id = 3309; --> returns 1 record

punto.punto_id references tramo.caseta_punto_id

You can download a copy of the database here.

Thanks.

pakman
  • 1,676
  • 3
  • 23
  • 41

1 Answers1

0

The database was actually created with H2 version 1.2.140 from 2010. (The 'CREATE_BUILD' setting in the database file is 140, that means version 1.2.140 is used.)

It's quite hard to say how referential integrity got violated. The problem could have occurred when the database was corrupt, or it could have occurred while referential integrity was disabled (if it ever was disabled).

The recover tool is meant to recover as much of the data in the database file as possible. It does not fix referential integrity problems.

There are multiple way to fix referential integrity problems. One solution is to delete superfluous records, another is to add missing records. I currently don't have a automated way to fix them however.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • Thank you Thomas for taking the time to answer. Sorry for the misinformation regarding the version. It worries me that the db can get silently currupted in such a way. The issue was discovered at runtime when a query started to fail. I couldn't manually fix the issue (setting to null the fk columnd and re-inserting missing records) until I re-started the db. Something was locking the thing up. – pakman Dec 11 '12 at 17:38
  • It worries me as well... Unfortunately, I can't tell what the reason is that the referential integrity was broken. It could be that referential integrity was manually disabled, I guess you didn't do that? In the past, between version 1.2.140 and now, there were some bugs on the storage engine level that could result in broken indexes (see the change log for details). Not many, and they are fixed now, but that could have been the reason. I'm currently working on a new, simpler storage engine. This should the reduce the risk of bugs on the storage engine level. – Thomas Mueller Dec 12 '12 at 06:10