can not recover from corruption

[jonas]

Dear,

after a shutdown/restart of our webserver, the content can not be
fetched from our embedded H2 database. It is running H2 1.0.68. I know
this version is old and that we should have migrated much sooner to
newer versions; but everything worked fine until the issue I'm
describing below.

The returned error is "General error: java.lang.Error: File ID
mismatch got=1845493760 expected=31 pos=281601 true
org.h2.store.DiskFile:/Users/jofallon81/db_27_07/test.data.db
blockCount:6666"

This is important prod data. We tried the following to recover :
1. append ";RECOVER=1" at the end of the url when connecting. The
returned error is then
General error: java.lang.Error: double allocation in file /Users/
jofallon81/db_27_07/test.data.db page 4400 blocks 281600-281663
[50000-68] HY000/50000

2. use the "org.h2.tools.Recover" tool. The output was the
following :
Created file: /Users/jofallon81/db_27_07/test.171.log.txt
Created file: /Users/jofallon81/db_27_07/test.data.sql
Error: java.lang.Error: read len -128

Please advise on how to recover our data. We are ready to pay for this
service. I know we should have migrated much sooner to newer versions;
but are there some migration tools to help doing that ? I can not open
my DB with the latest H2, it says it is not the same database version.

Any help would be appreciated,

Many thanks,

Jonathan.

[Thomas Mueller]

Hi,

The Recover tool was improved in later versions, could you try it with
version 1.0.75 (2008-07-14)? You can download it from
http://code.google.com/p/h2database/downloads/list

This will create a file named test.data.sql - it will create it even
if there are errors. You can then open this file using a text editor,
and recover most (if not all) of the data in this way.

Regards,
Thomas

[jonas]

Hi,

Many thanks for quick answer Thomas.

Our DB is +/- 100 MB, but the export seems to be anormally long. With
sql files reaching more than 30 Gigs... Is this normal ?

In the output, we can see error reports like this :

INSERT INTO O_31 VALUES(131201, 'Pompe piscine', 257, TIMESTAMP
'2011-07-03 14:22:32.0', STRINGDECODE('\u20ac'), TIMESTAMP '2011-07-03
14:22:58.0', 2.0, 18488, 6666, 'simple', NULL);
-- block 281601 - 288266
-- ERROR: wrong checksum block:281601 blockCount:6666 storageId:-1
recordLength: -1 valueId:-1
-- dump: ????n????complex-??-mple-imple-B?[-???@??C#???3-340442????
34756269-28270-28271-28586-28587-29072-29073-30575-30576-30577-31337?3
-- dump: 00 00 1a 0a 6e 00 00 00 07 63 6f 6d 70 6c 65 78 2d cf c0 2d
6d 70 6c 65 2d 69 6d 70 6c 65 2d 42 0e 5b 2d 0b bd fb 40 0b ad 43 23
f1 0c c0 33 2d 33 34 30 34 34 32 00 00 00 05 33 34 37 35 36 32 36 39
2d 32 38 32 37 30 2d 32 38 32 37 31 2d 32 38 35 38 36 2d 32 38 35 38
37 2d 32 39 30 37 32 2d 32 39 30 37 33 2d 33 30 35 37 35 2d 33 30 35
37 36 2d 33 30 35 37 37 2d 33 31 33 33 37 fc 33
-- block 281602 - 281602

Note the block mentionned is the same as in the first error message in
my previous message : pos=281601

Is this lost data ? or he could recover from the wrong checksum you
think ?

Thanks,

jonathan.

[Thomas Mueller]

Hi,

> Our DB is +/- 100 MB, but the export seems to be anormally long. With
> sql files reaching more than 30 Gigs...  Is this normal ?

Probably there are a lot of corruptions.

> recover from the wrong checksum

I'm sorry, but I believe it is unlikely to recover all data.
Typically, a wrong checksum means there was a power failure or other
external corruption in the database file, so most likely this data is
lost I'm afraid. The recover tool does dump the data, but if it's 30
GB then it will be almost impossible to reconstruct everything.

Regards,
Thomas