On Oct 13, 2009, at 1:56 AM, McClellen, Chris wrote: > What is your synchronous set to? Full? FYI If you are using .NET > data > providers, it is set to "Normal" by default. > > If it is not set to full, I have seen corruption when an application > crashes, or exits when a thread is in the middle of updating the db > (Synchronous = OFF makes corruption even easier in this case). I have > seen apps that do not wait for background threads to finish before > termination, and without full sync on, either the db or the log gets > corrupted. A corrupted log can cause problems for you db on next run > when recovery happens.
In theory, this shouldn't happen. Unless the application is actually buffering data that SQLite thinks has been written to the database or journal file in the process space on some systems. The "synchronous" setting should only make a difference in the event of a power or OS failure. That's the theory, anyway. Dan. > > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filip Navara > Sent: Monday, October 12, 2009 12:38 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Corrupted database > > Hello, > > for a few months we have been occasionally getting corrupted databases > in the field. So far we were unable to acquire any of them from our > customers, but this week I finally got hold of one. Output from > "pragma integrity_check" is included below. > > The schema is the following: > > CREATE VIRTUAL TABLE "LocalMailsIndex3" USING fts3 (id INTEGER, > partName TEXT, content TEXT); > CREATE TABLE LocalMailsIndex3_content( docid INTEGER PRIMARY > KEY,c0id, c1partName, c2content); > CREATE TABLE LocalMailsIndex3_segdir( level integer, idx integer, > start_block integer, leaves_end_block integer, end_block integer, > root blob, primary key(level, idx)); > CREATE TABLE LocalMailsIndex3_segments( blockid INTEGER PRIMARY KEY, > block blob); > CREATE INDEX "LocalMailsIndex3_contentIndex" ON > "LocalMailsIndex3_content" ("c0id", "c1partName"); > > The database is created using SQLite 3.6.14.2, thread safe, on Windows > with auto_vacuum=incremental. It is always opened as attached database > with journal_mode=persist. Application crashes were most probably > involved, but no operating system / power crashes as far as I know. > > One thread in the application is periodically running "pragma > freelist_count" and "pragma incremental_vacuum(...)". Other threads > are running combination of the following commands and no other: > > INSERT INTO mail_fti.LocalMailsIndex3("id", "partName", "content") > VALUES (@id, @partName, @content) > SELECT c2content AS content FROM mail_fti.LocalMailsIndex3_content > WHERE c0...@id AND c1partna...@partname > SELECT docid FROM mail_fti.LocalMailsIndex3_content WHERE c0id IN > (...) > DELETE FROM mail_fti.LocalMailsIndex3 WHERE doc...@docid > SELECT ... WHERE "id" IN (SELECT "id" FROM mail_fti.LocalMailsIndex3 > WHERE content MATCH "...") > > Anybody has seen something like this? > Anybody willing to look at it? I can send the database privately. > > Best regards, > Filip Navara > > Main freelist: Bad ptr map entry key=5143 expected=(2,0) got=(3,4467) > Main freelist: freelist leaf count too big on page 5143 > Main freelist: Bad ptr map entry key=5449 expected=(2,0) got=(4,5143) > Main freelist: freelist leaf count too big on page 5449 > Main freelist: 904 of 908 pages missing from overflow list starting at > 5143 > On tree page 3878 cell 26: invalid page number 5737 > On tree page 3878 cell 26: Child page depth differs > On tree page 3878 cell 27: Failed to read ptrmap key=5746 > On tree page 3878 cell 27: invalid page number 5746 > On tree page 3878 cell 28: Failed to read ptrmap key=5748 > On tree page 3878 cell 28: invalid page number 5748 > On tree page 3878 cell 29: Failed to read ptrmap key=5749 > On tree page 3878 cell 29: invalid page number 5749 > On tree page 3878 cell 30: Failed to read ptrmap key=5755 > On tree page 3878 cell 30: invalid page number 5755 > On tree page 3878 cell 31: Failed to read ptrmap key=5757 > On tree page 3878 cell 31: invalid page number 5757 > On tree page 3878 cell 32: Failed to read ptrmap key=5759 > On tree page 3878 cell 32: invalid page number 5759 > On tree page 3878 cell 33: Failed to read ptrmap key=5761 > On tree page 3878 cell 33: invalid page number 5761 > On tree page 3878 cell 34: Failed to read ptrmap key=5763 > On tree page 3878 cell 34: invalid page number 5763 > On tree page 3878 cell 35: Failed to read ptrmap key=5767 > On tree page 3878 cell 35: invalid page number 5767 > On tree page 3878 cell 36: Failed to read ptrmap key=5769 > On tree page 3878 cell 36: invalid page number 5769 > On tree page 3878 cell 37: Failed to read ptrmap key=5771 > On tree page 3878 cell 37: invalid page number 5771 > On tree page 3878 cell 38: Failed to read ptrmap key=5773 > On tree page 3878 cell 38: invalid page number 5773 > On tree page 3878 cell 39: Failed to read ptrmap key=5775 > On tree page 3878 cell 39: invalid page number 5775 > On tree page 3878 cell 40: Failed to read ptrmap key=5777 > On tree page 3878 cell 40: invalid page number 5777 > On tree page 3878 cell 41: Failed to read ptrmap key=5780 > On tree page 3878 cell 41: invalid page number 5780 > On tree page 3878 cell 42: Failed to read ptrmap key=5783 > On tree page 3878 cell 42: invalid page number 5783 > On tree page 3878 cell 43: Failed to read ptrmap key=5787 > On tree page 3878 cell 43: invalid page number 5787 > On tree page 3878 cell 44: Failed to read ptrmap key=5789 > On tree page 3878 cell 44: invalid page number 5789 > On tree page 3878 cell 45: Failed to read ptrmap key=5793 > On tree page 3878 cell 45: invalid page number 5793 > On tree page 3878 cell 46: Failed to read ptrmap key=5795 > On tree page 3878 cell 46: invalid page number 5795 > On tree page 3878 cell 47: Failed to read ptrmap key=5797 > On tree page 3878 cell 47: invalid page number 5797 > On tree page 3878 cell 48: Failed to read ptrmap key=5801 > On tree page 3878 cell 48: invalid page number 5801 > On tree page 3878 cell 49: Failed to read ptrmap key=5805 > On tree page 3878 cell 49: invalid page number 5805 > On tree page 3878 cell 50: Failed to read ptrmap key=5807 > On tree page 3878 cell 50: invalid page number 5807 > On tree page 3878 cell 51: Failed to read ptrmap key=5810 > On tree page 3878 cell 51: invalid page number 5810 > On tree page 3878 cell 52: Failed to read ptrmap key=5813 > On tree page 3878 cell 52: invalid page number 5813 > On tree page 3878 cell 53: Failed to read ptrmap key=5817 > On tree page 3878 cell 53: invalid page number 5817 > On tree page 3878 cell 54: Failed to read ptrmap key=5822 > On tree page 3878 cell 54: invalid page number 5822 > On tree page 3878 cell 55: Failed to read ptrmap key=5826 > On tree page 3878 cell 55: invalid page number 5826 > On tree page 3878 cell 56: Failed to read ptrmap key=5831 > On tree page 3878 cell 56: invalid page number 5831 > On tree page 3878 cell 57: Failed to read ptrmap key=5836 > On tree page 3878 cell 57: invalid page number 5836 > On tree page 3878 cell 58: Failed to read ptrmap key=5838 > On tree page 3878 cell 58: invalid page number 5838 > On tree page 3878 cell 59: Failed to read ptrmap key=5841 > On tree page 3878 cell 59: invalid page number 5841 > On tree page 3878 cell 60: Failed to read ptrmap key=5843 > On tree page 3878 cell 60: invalid page number 5843 > On tree page 3878 cell 61: Failed to read ptrmap key=5846 > On tree page 3878 cell 61: invalid page number 5846 > On tree page 3878 cell 62: Failed to read ptrmap key=5848 > On tree page 3878 cell 62: invalid page number 5848 > On tree page 3878 cell 63: Failed to read ptrmap key=5850 > On tree page 3878 cell 63: invalid page number 5850 > On tree page 3878 cell 64: Failed to read ptrmap key=5853 > On tree page 3878 cell 64: invalid page number 5853 > On tree page 3878 cell 65: Failed to read ptrmap key=5857 > On tree page 3878 cell 65: invalid page number 5857 > On tree page 3878 cell 66: Failed to read ptrmap key=5859 > On tree page 3878 cell 66: invalid page number 5859 > On tree page 3878 cell 67: Failed to read ptrmap key=5862 > On tree page 3878 cell 67: invalid page number 5862 > On tree page 3878 cell 68: Failed to read ptrmap key=5868 > On tree page 3878 cell 68: invalid page number 5868 > On tree page 3878 cell 69: Failed to read ptrmap key=5870 > On tree page 3878 cell 69: invalid page number 5870 > On tree page 3878 cell 70: Failed to read ptrmap key=5874 > On tree page 3878 cell 70: invalid page number 5874 > On tree page 3878 cell 71: Failed to read ptrmap key=5876 > On tree page 3878 cell 71: invalid page number 5876 > On tree page 3878 cell 72: Bad ptr map entry key=4495 > expected=(5,3878) > got=(2,0 > ) > On tree page 4495 cell 0: Bad ptr map entry key=4492 expected=(3,4495) > got=(2,0) > > On tree page 3878 cell 72: Child page depth differs > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
