Hi Sqlite Users
I am running into a situation where sometimes after the power is restored my
sqlite db running on linux is corrupted. Here is what I see with
integrity_check:-
*** in database main ***
Page 895: btreeInitPage() returns error code 11
On tree page 2 cell 765: Child page depth differs
On tree page 2 cell 766: Child page depth differs
Whenever this occurs I have a journal file as well as the db file . I have the
following questions:-
I am using the following sequence of statements to commit a tx to sqlite:-
1. rc = sqlite3_open("test.db", &db);
2. repeat in a loop for different key and blob
3. lRc = sqlite3_exec(db, CREATE_TABLE, callback, (void*)data, &zErrMsg);
4. sqlite3_prepare_v2(db, BEGIN_TX, strlen(BEGIN_TX), &beginTxStmt, NULL)
5. sqlite3_prepare_v2(db, COMMIT_TX, strlen(COMMIT_TX), &commitTxStmt,
NULL)
6. sqlite3_prepare_v2(db, INSERT_RECORD, strlen(INSERT_RECORD),
&storeStmt, NULL)
7. sqlite3_step(beginTxStmt);
8. sqlite3_reset(beginTxStmt);
9. sqlite3_bind_int64(storeStmt, 1, key);
10. sqlite3_bind_blob(storeStmt, 2, str, len, SQLITE_STATIC);
11. sqlite3_step(storeStmt);
12. sqlite3_reset(storeStmt);
13. lRc = sqlite3_step(commitTxStmt);
14. sqlite3_reset(commitTxStmt);
15. end repeat
My questions are:-
1. when the db gets corrupt, I expect that the next read/write statement
will look at the journal and recover from it . So I see the journal file does
gets deleted after the power is restored and the sqlite tries to write again,
but the sqlite db is still corrupt. Is there a way to verify the rollback using
the journal file was successful or not ?
2. Is there a way to verify if the journal file is correct and is itself
not corrupt ?
3. Assuming that the journal file is a copy of the original db before the
transaction, is there a manual recovery possible using only the journal file.
For e.g. can I delete the corrupted db and rename the journal file as the db
file ? Are there tools which can convert the journal file to the sqlite db file
?
4. is there a way I can verify what data is causing the corruption ?
5. can my application run some on the fly checks on the db after each
write to verify the database is sane and not corrupted ?
6. do you see any issues with the sequence of statements I am using to do
multiple transactions ?
Sorry for too many questions , but this is a high priority issue for me and I
want to make sure I have enough data to tackle it.
Cheers
Mayank
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
