Hello Chris, Customer PC's right? I've never had a corrupt DB3 here and that includes me jumping out of the debugger mid-transaction but, I do have customers who get corrupted DB's even with sync set to full. For some customers, deleting the DB3's, running once to let them get created and then restarted is enough to damage them.
I've come to the conclusion that in many cases, the PC's themselves are the culprit. In many cases, my idiot users (not all are idiots) will have multiple security packages installed, each with kernel drivers that watch disk IO and interfere with it under the hood. These same users usually have a litany of weird problems while the majority of user have none. I typically ask these "problem children" to remove their security packages and reboot. Then test with no security packages installed. Many times this solves the issue. Sometimes simply upgrading to the most current version is enough to solve the problem. I've come to the conclusion that AV packages and software firewalls are more of a problem than the viruses they're trying to catch. PC hardware really isn't that reliable either. I know this doesn't help you but, you might want to consider the PC's themselves as you investigate this. I have >10,000 active users. The number of users reporting these issues is perhaps 50-100. C Wednesday, October 14, 2009, 1:48:36 PM, you wrote: MC> Yes, I agree. What I am now trying to find out is if things like MC> running a service or .NET service causes terminatethread to be called MC> behind the scenes as some kind of cleanup. The testing was to show that MC> this can be a problem, to characterize why some dbs can get corrupted on MC> "normal exits" MC> -----Original Message----- MC> From: sqlite-users-boun...@sqlite.org MC> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Teg MC> Sent: Wednesday, October 14, 2009 12:10 PM MC> To: General Discussion of SQLite Database MC> Subject: Re: [sqlite] Corrupted database MC> Hello Chris, MC> It's always a mistake to use TerminateThread. Even Microsoft warns MC> against it. >>From MSDN: MC> - If the target thread owns a critical section, the critical section MC> will not be released. MC> - If the target thread is allocating memory from the heap, the heap lock MC> will not be released. MC> - If the target thread is executing certain kernel32 calls when it is MC> terminated, the kernel32 state for the thread's process could be MC> inconsistent. MC> - If the target thread is manipulating the global state of a shared DLL, MC> the state of the DLL could be destroyed, affecting other users of the MC> DLL. MC> Terminating a thread can hang the whole application of I read #1 and #2 MC> correctly. If you use TerminateThread regularly, you should really MC> re-consider your design. MC> C MC> Wednesday, October 14, 2009, 11:29:29 AM, you wrote: MC>> Yes, if we are in the middle of a lot of updates/inserts and just MC>> terminate the thread, pragma integrity_check from the sqlite3 MC> command MC>> line tool will report corruption at times. Normally, when we hard MC> kill MC>> a thread in the middle of these ops, a journal is left behind. I MC> think MC>> we only see corruption in this case (journal left behind), but MC> cannot be MC>> sure. Our transactions can be large (a few thousand records MC> totaling a MC>> few megabytes). MC>> Summary of steps: MC>> 1) hard-Kill a thread in the middle of inserting/updating a large MC>> transaction (2mb+ transaction). MC>> 2) with nothing else running, do a pragma integrity_check in sqlite3 MC>> command line client against the db. Obviously after executing MC> sqlite3 MC>> client, the journal disappears since I guess recovery ran. MC>> 3) integrity_check spits out lots of errors. MC>> Note that we have never had this happen with Synchronous=Full, only MC>> Synchronous=Off. Have not tried normal. That's all I was trying to MC>> tell the original person asking. I don't know why this would MC> happen; it MC>> seems logical that once you execute a write to the FS, whether or MC> not MC>> the app crashes/spontaneously exits that the write would make it. MC>> However, all of this may be a red herring.. It turns out we moved to MC>> Synch=Full after we sped up our db operations greatly... see below MC> as to MC>> why that may be the factor (speed), and not what Synch is set to. MC>> By the way, this is all under windows. MC>> I'm assuming that sqlite3 uses sync i/o via writefile. Here's a MC> quick MC>> read on sync IO for windows: MC>> http://msdn.microsoft.com/en-us/library/aa365683(VS.85).aspx -- MC>> specifically what worries me is that TerminateThread() looks like it MC> can MC>> interrupt an I/O operation. MC>> The problem I think may be people using TerminateThread(); that's MC> how MC>> you hard kill a thread. It seems that can interrupt an I/O MC> operation -- MC>> ie an operation that writes more than one cluster at a time. MC> Meaning, MC>> synch = full may have nothing to do with it. If you have to say MC> write MC>> more than one cluster (disk block), TerminateThread looks like it MC> can MC>> abort the IO op in the middle of a multi-block op? I'm trying to MC> run MC>> that down but can't yet find anything that verifies this. MC>> So, here is what I think: MC>> 1) You need to write some data, lets say a 8K write. In this MC>> theoretical example, disk blocks are 4k. MC>> 2) You call WriteFile(8K). MC>> 3) WriteFile causes a system call, and the system schedules the MC> first MC>> bit of the i/o (1st 4k). MC>> 4) terminatethread() is called MC>> 5) I/O operation is cancelled (ie, as if CancelIO had been called?), MC>> meaning block #2 was never scheduled. Database now corrupt. MC>> A lot of people using windows tend to have this kind of threading MC>> architecture: MC>> 1) Signal the thread to exit on its own MC>> 2) Wait for some grace period. MC>> 3) if grace period expires, and thread is still running -- MC>> TerminateThread() because once the grace period expires, the thread MC> is MC>> considered hung. MC>> So, large transactions in a thread could cause people to use MC>> TerminateThread() at a critical time, especially if that causes the MC>> thread to go over its grace period. For us, these large MC> transactions MC>> took longer than our grace period to complete, and thus were subject MC> to MC>> TerminateThread -- and lots of corruption. Once we sped everything MC> up MC>> and moved to synch=full, no transaction was even close to the grace MC>> period, and such, terminatethread() is never called.. and we get no MC>> corruption. MC>> Just a thought. MC>> -----Original Message----- MC>> From: sqlite-users-boun...@sqlite.org MC>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy MC>> Sent: Wednesday, October 14, 2009 12:36 AM MC>> To: General Discussion of SQLite Database MC>> Subject: Re: [sqlite] Corrupted database MC>> On Oct 14, 2009, at 12:42 AM, McClellen, Chris wrote: >>> But it does happen and we can reproduce it. Hard killing a thread is >>> essentially equivalent to turning off the power. MC>> We have always assumed that it is different. When you write data to MC>> a file, the write is buffered in volatile memory by the OS for a MC> time. MC>> If a power failure occurs during this time, the write is lost. But MC> if MC>> a thread is killed, the OS should still eventually make sure the MC> data MC>> gets to stable storage. MC>> If you kill the application, then open the database using the shell MC>> tool, is the database corrupted? MC>> Dan. >>> -----Original Message----- >>> From: sqlite-users-boun...@sqlite.org >>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy >>> Sent: Tuesday, October 13, 2009 12:35 AM >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] Corrupted database >>> >>> >>> 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 MC> 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 MC> 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) MC> got=(3,4467) >>>> Main freelist: freelist leaf count too big on page 5143 >>>> Main freelist: Bad ptr map entry key=5449 expected=(2,0) MC> 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 >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users MC>> _______________________________________________ MC>> sqlite-users mailing list MC>> sqlite-users@sqlite.org MC>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users MC>> _______________________________________________ MC>> sqlite-users mailing list MC>> sqlite-users@sqlite.org MC>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Teg mailto:t...@djii.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
