Dear forum members / sqlite experts,
I am relatively new to sqlite and database programming is not at the center of my work, so I have a hard time finding the right keywords to search the forum. I apologize for questions with answers obvious to you or questions answered in previous posts or missing crucial information.
We are writing mixed image (BLOBs of constant size) and numerical (mixed INTEGER/REAL) information to a sqlite db table from a time critical data acquisition program (sqlite 3.33 on linux/debian, database resides on an ext4 file system). The database contains 5 (different) tables, three of which grow during the acquition process by being INSERTed INTO. The data base is opened by the spell
sqlite3_open_v2(full_path.string().c_str(), &db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_NOMUTEX, nullptr)
Later,
char pragma[] = "PRAGMA synchronous = OFF";
int res = sqlite3_exec(db, pragma, NULL, NULL, &sql3_err);
is used for performance reasons. File access is from a single thread, writing typically 100 MB/s (The application is multi-threaded and writes in parallel to multiple data bases, but to each one only from a single thread).
The INSERTs are issued at more or less constant intervals, bracketed between BEGIN and END TRANSACTION, with a period of 5 s.
The tables' PRIMARY key is either a single INTEGER, growing by 1 from INSERT to INSERT or a pair of INTEGERs, of which the first grows by 1 and remains constant while a second INTEGER ranging from 0 to a small count (say typically 1..5) increases.
At the end of a data acquistion run the databases have typically reached sizes of 30-100 GB. Unfortunately, our acquisition program acquired the bad habit to crash from time to time at the end of the acquisiton. At this time, data writing has slowed down to 0, the last transaction is not yet finalized, but no new INSERT takes place. In this situation the crash happens, leaving the multi-10-GB .db file and a .db-journal file behind. I am trying to recover as much data as possible from these two files. I have checked the following steps
on multiple damaged data bases:
1. simply re-opening the database (e.g. in the sqlite3 shell) seems to replay the journal with the effect that the two largest growing tables are truncated to 0 records, the third largest still has about 1% of its expected number of records and the two initially written tables are correctly recovered. The database shrinks to approx. 500 kB in this process and the journal is deleted.
2. If I rename the journal and perform a dump on the remaining .db file (following the ideas/suggestions in https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database):
echo ".dump" \| sqlite3 "faulty.db" > sql
the sql shows that the two truncated tables will be created, but no data will be inserted after that. Using "PRAGMA integrity_check" returns "ok", but the database is truncated as in (1)
3. I have used demo versions of two affordable commercial data base recovery tools (SQLiteDoctor/SQLite Database Recovery), who arrive at mostly the same conclusion. Both large tables will be truncated to 0 records.
My questions:
- is there anything obvious I could try in addition to the above in order to recover some of the data of the growing tables?
- is there any procedure by which to make the database more 'resistant' or to help the recovery process [with the exception of touching the setting of synchronous = OFF?
- Would you think that it is feasible or reasonable to attempt to write code to parse the database file for the remnants of the data tables (I am afraid that I will not be able to go beyond what the commercial solutions would do...)?
Thanks in advance for your help/opinions,
Stefan