Database malformed

[Pierre]

Hi !

I'm sorry if this problem has already been answered somewhere, but I couldn't find a solution to my specific issue. I have been scanning with an iPhone and the iPhone turned off during the scan because of low battery. The temp db file is about 5gb and it would be a real shame to lose it (extreme scanning conditions let's say).

I have tried:

- rtabmap-recovery:

[FATAL] (2024-10-06 13:03:33.633) DBDriverSqlite3.cpp:521::executeNoResultQuery() Condition (rc == SQLITE_OK) not met! [DB error: database disk image is malformed, the query is PRAGMA cache_size = 10000;]
terminate called after throwing an instance of 'UException'
  what():  [FATAL] (2024-10-06 13:03:33.633) DBDriverSqlite3.cpp:521::executeNoResultQuery() Condition (rc == SQLITE_OK) not met! [DB error: database disk image is malformed, the query is PRAGMA cache_size = 10000;]

Signal 6 caught...
Abandon (core dumped)

- sqlite3 .recover : produces a .sql file, then trying to extract a .db gives

...
Parse error near line 33252: file is not a database (26)
Parse error near line 33253: file is not a database (26)
Parse error near line 33254: file is not a database (26)
Parse error near line 33255: file is not a database (26)
Parse error near line 33256: file is not a database (26)
...

Somehow the file is big so I know everything is in there. Would be amazing if you can help with that ! :)

Thanks
Pierre

[matlabbe]

Hi Pierre,

That kind of error would happen when sqlite3 is not able to read the file. You may try this: https://stackoverflow.com/questions/5274202/sqlite3-database-or-disk-is-full-the-database-disk-image-is-malformed before doing rtabmap-recovery. As long as the Node and Data tables are readable, rtabmap should be able to recover some of it. I could be very interested to know if this could work! There are many answers on that post, if you could be able to share that database to me (to my username at gmail), I could try some of them to see which one can recover the most data in your case.

EDIT: found also these posts with similar issue:
 * https://github.com/ros2/rosbag2/issues/1029
 * https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/57872238#57872238

cheers,
Mathieu

[Pierre]

Hi Mathieu,

Thanks a lot for your answer. I tried many of the options and none seemed to work. Dumping, cloning, all of that doesn't work because sqlite just doesn't seem to be able to parse the db.

I will send you the file in case you find some magic to repair it :) thank you so much for offering this solution and hopefully it works eventually.

Cheers
Pierre

[matlabbe]

Hi Pierre,

I have good news, I was able to get most data (I think). I followed the instructions of this post, that I copy here. Assuming "rtabmap.db" is the file name of the corrupted database.

1. Dump SQL

sqlite3 rtabmap.db
.mode insert
.output dump_all.sql
.dump
.exit

2. Extract all data without transactions:

cat dump_all.sql | grep -v TRANSACTION | grep -v ROLLBACK | grep -v COMMIT > dump_all_notrans.sql

3. Re-create a RTAB-Map database:

sqlite3 rtabmap_data.db ".read dump_all_notrans.sql"

4. At this step, you could already browse data in that database with rtabmap-databaseViewer. It may be not needed in your case as visual features (Kp/MaxFeatures=-1) are disabled, but for others who need a working database for a robot, you should be able at this point to re-use rtabmap-recovery to fix the database:

rtabmap-recovery rtabmap_data.db

For info, here what I could recover:

rtabmap-info rtabmap_data.db
Info:

Path:               data.db
Version:            0.21.6
Sessions:           1
Total odometry length:748.832764 m
Total time:         2428.857190s
LTM:                5911 nodes and 0 words (dim=0 type=8U)
WM:                 308 nodes and 0 words
Global graph:       5911 poses and 15821 links
Optimized graph:    309 poses (x=15821->-165, y=-62->-24, z=2->0)
Maps in graph:      1/1 [0(308)]
Ground truth:       0 poses
GPS:                0 poses
Links:
  Neighbor:         4954	(length avg: 0.15m, std: 0.15m, max: 1.43m)
  GlobalClosure:    0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  LocalSpaceClosure:0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  LocalTimeClosure: 0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  UserClosure:      0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  VirtualClosure:   0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  NeighborMerged:   0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  PosePrior:        0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  Landmark:         2	(length avg: 1.58m, std: 0.28m, max: 1.78m)
  Gravity:          5911	(length avg: 0.00m, std: 0.00m, max: 0.00m)

Database size:      5078 MB
Nodes size:         1 MB	(0.02%)
Links size:         5 MB	(0.11%)
RGB Images size:    4242 MB	(83.55%)
Depth Images size:  796 MB	(15.68%)
Calibrations size:  969 KB	(0.02%)
Grids size:         70 KB	(0.00%)
Scans size:         9 MB	(0.19%)
User data size:     0 Bytes	(0.00%)
Dictionary size:    0 Bytes	(0.00%)
Features size:      0 Bytes	(0.00%)
Statistics size:    6 MB	(0.12%)
Other (indexing, unused):15 MB	(0.31%)

cheers,
Mathieu

[Pierre]

Hi Mathieu,

This is awesome !

Although, I did try that actually... Tried again and it still doesn't work on my computer which is weird.
When I do .dump I get

sqlite> .dump
sql error: database disk image is malformed (11)

The .sql file then just contains an error message and is unusable.
I'm reassured that you managed to make something out of the file at least.

My SQLite version is 3.37.2. Did you do anything else to the file by any chance ?

Thank you so much,
Pierre

[matlabbe]

Hi Pierre,

it is strange indeed. Just re-did this just after re-downloading the file (renamed database to corrupted.db):

$ ls -l corrupted.db
-rw-rw-r-- 1 mathieu mathieu 5069553664 Oct  9 08:47 corrupted.db

### Showing  original error ###
$ rtabmap-info corrupted.db 
terminate called after throwing an instance of 'UException'
  what():  [FATAL] (2024-10-09 09:13:40.244) DBDriverSqlite3.cpp:521::executeNoResultQuery() Condition (rc == SQLITE_OK) not met! [DB error: database disk image is malformed, the query is PRAGMA cache_size = 10000;]
Aborted (core dumped)

### Recovering with sqlite3 ###
$ sqlite3 corrupted.db 
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .mode insert
sqlite> .output dump_all.sql
sqlite> .dump
sqlite> .exit

$ ls -l dump_all.sql
-rw-rw-r-- 1 mathieu mathieu 10109822559 Oct  9 09:06 dump_all.sql

$ cat dump_all.sql | grep -v TRANSACTION | grep -v ROLLBACK | grep -v COMMIT > dump_all_notrans.sql

$ ls -l dump_all_notrans.sql
-rw-rw-r-- 1 mathieu mathieu 10109822513 Oct  9 09:14 dump_all_notrans.sql

$ sqlite3 recovered.db ".read dump_all_notrans.sql"

$ ls -l recovered.db 
-rw-r--r-- 1 mathieu mathieu 5066268672 Oct  9 09:21 recovered.db

### Showing  no more error ###
$ rtabmap-info recovered.db 
[...]
Info:

Path:               recovered.db
Version:            0.21.1
Sessions:           1
Total odometry length:748.832764 m
Total time:         2428.857190s
LTM:                5911 nodes and 0 words (dim=0 type=8U)
WM:                 5911 nodes and 0 words
Global graph:       5911 poses and 15526 links
Optimized graph:    0 poses (x=15526->0, y=0->0, z=0->0)
Maps in graph:      0/1 []
Ground truth:       0 poses
GPS:                0 poses
Links:
  Neighbor:         4956	(length avg: 0.15m, std: 0.15m, max: 1.43m)
  GlobalClosure:    0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  LocalSpaceClosure:0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  LocalTimeClosure: 0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  UserClosure:      0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  VirtualClosure:   0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  NeighborMerged:   0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  PosePrior:        0	(length avg: 0.00m, std: 0.00m, max: 0.00m)
  Landmark:         10	(length avg: 1.11m, std: 0.27m, max: 1.78m)
  Gravity:          5911	(length avg: 0.00m, std: 0.00m, max: 0.00m)

Database size:      5066 MB
Nodes size:         1 MB	(0.02%)
Links size:         5 MB	(0.11%)
RGB Images size:    4234 MB	(83.58%)
Depth Images size:  796 MB	(15.72%)
Calibrations size:  969 KB	(0.02%)
Grids size:         70 KB	(0.00%)
Scans size:         9 MB	(0.19%)
User data size:     0 Bytes	(0.00%)
Dictionary size:    0 Bytes	(0.00%)
Features size:      0 Bytes	(0.00%)
Statistics size:    6 MB	(0.12%)
Other (indexing, unused):11 MB	(0.24%)

### Final step to make the full recovery ###
$ rtabmap-recovery  recovered.db 
Recovering "recovered.db"
Found 5911 nodes to recover.
Database is indeed corrupted, found one or more neighbor links missing.
Recovering data of "recovered.db"...
Processed 1/5911 nodes...
Processed 2/5911 nodes...
Processed 3/5911 nodes...
Processed 4/5911 nodes...
Processed 5/5911 nodes...
[...]

It seems we have same sqlite3 version. I'll check tonight how I can re-share you the recovered database (I am low in space left on my google drive).

[Pierre]

Hi everyone,

Mathieu ended up extremely kindly sending me the recovered file. I couldn't reproduce the steps to recover it myself on my computer, which I cannot explain why at the moment.

So thank you again Mathieu for your extreme generosity.

Best
Pierre