Thanks Simon. See inline -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Simon Slavin Sent: Thursday, November 07, 2013 4:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite db getting corrupt on power outage scenarios
On 7 Nov 2013, at 11:50pm, Mayank Kumar (mayankum) <[email protected]> wrote: > Are there known tools which tell us what data corrupted the db or some way of > removing only the corrupted section of the db ? Download the SQLite shell tool for your platform. Use .dump to dump the database to a SQL command file. Then use the same shell tool to create a blank database and .read those commands to read that data back in. We have no way of knowing how much, if any, of the data from the original database this will recover. Take a look at the text file yourself and see if you can figure out how much went missing. [MK] Sounds interesting. Will look into this. > I will definitely look into the wal mode. Are there any test results for disk > failure robustness of this mode compared to journal mode. This won't help. As long as you're using a network file system which does not support fsync() properly you will get more corruption. Is this database on the hard disk in the computer running the SQLite application, or are you accessing it across a network ? What OS and disk format are you using ? [MK] I am using a flash drive running on linux kernel 2.6.27.10(SMP). The corruption is sometimes seen on sqlite db on the flash drive and sometimes seen on sqlite db in nvram. Network file system is not involved. >>> 1. rc = sqlite3_open("test.db", &db); change your app to look at this result code and check that it equals SQLITE_OK. And do the same thing with the result codes returned by all your other sqlite3_ calls. If any of them are not SQLITE_OK report the value and quit. [MK] I do look at all return codes. I just pasted the sequence of apis not the actual code. >>> 1. when the db gets corrupt, I expect that the next read/write >>> statement will look at the journal and recover from it . Depends on the nature of the corruption. Some corruption can't be detected. >>> 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 ? Simply ignoring the journal file, or having had the journal file deleted should yield an uncorrupted database. Possibly slightly out of date, but uncorrupted. This is SQLite's fall-back situation. Apparently it's not working in your case. [MK] in my case the db is corrupted but there is also a journal file present. When I try to write a new transaction to the corrupted db, the new transaction is successful and at the same time the journal file gets deleted. The resultant db is still corrupted. Can we assume from this behavior that the corruption happened before the last failed transaction for which there is a journal file assuming that a journal file is created per transaction. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
