sqlite> PRAGMA integrity_check; *** in database main *** List of tree roots: invalid page number 24 List of tree roots: invalid page number 23
What would be the reasons for a database to go corrupt? Anyone have any ideas
Versions of SQLite prior to 2.8.12 contain bugs that can (in rare circumstances) corrupt the database file. See
http://www.sqlite.org/cvstrac/tktview?tn=529 http://www.sqlite.org/cvstrac/tktview?tn=552 http://www.sqlite.org/cvstrac/tktview?tn=561
Or, there could be undiscovered bugs in SQLite.
The other thing is that a process can go haywire and either write garbage directly into your database file, or into memory used by the SQLite library.
An example of the latter happened to me a few days ago. XFree86 segfaulted on me. After I logged back in, I found that one of my databases was no longer working correctly. Further investigation showed that one the CREATE TABLE statements that SQLite stores in the SQLITE_MASTER table had been corrupted, preventing SQLite from initializing correctly. The correct SQL should have been:
CREATE TABLE _Peer( peerid INTEGER PRIMARY KEY, -- ID number for this peer dbproxy name TEXT UNIQUE, -- name of the peer dbproxy ip TEXT UNIQUE, -- IP address of the peer contact TIME, -- Time of last known contact attempt TIME, -- Time of last attempt to contact desired BOOLEAN -- True if we want this link to be up )
But after the XFree86 crash, that entry contained the following text instead:
CREATE TABLE _Peer( peerid INTEGER PRIMARY KEY, -- ID number for this peer dbproxy name TEXT UNIQUE, -- name of the peer dbproxy ip TEXT UNIQUE, -- IP address of the peer nnection to :0.0 broken (explicit kill or server shutdown). attempt TIME, -- Time of last attempt to contact desired BOOLEAN -- True if we want this link to be up )
Luckily for me, the overwrite occurred in the middle of a text field where it could be corrected. If the overwrite had occurred in some other part of the database file it might have corrupted the file structure making recovery more difficult.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
