On Oct 6, 2009, at 1:19 PM, Reusche, Andrew wrote: > When I run pragma synchronous" I receive a 2. Documentation says this > is the value for "Full" synchronous mode. I've checked this using the > routine through our application, and also using sqlite3.exe. This > is on > a w32 system, if it matters. > > We experienced another "corruption" issue with the DB. Details below. > When we tried to write to this DB, we received the "malformed image" > error. Sorry I don't have the exact text. We have now received 4 > problems with the database in 1000 installs since early July. The > installs each reboot 2-3 times per day, and run every single day. Is > this the typical failure rate you would expect to see?
We expect to see an error rate of zero. It should not be possible to corrupt an SQLite database even via power failure as long as the disk drive hardware plays by the assumptions outlined in http://www.sqlite.org/atomiccommit.html > > We are using a similar installation, also with SQLite, on 200 > installations elsewhere for the past 12 months, and have not had any > reports of this type of problem. I reviewed the syncing primary key > code and it looks ok, but I am still suspicious, as that was the code > change that seemed to immediately proceed these problems. > > - [0] {name="integrity_check" value="*** in database > main *** > On tree page 2801 cell 5: 2nd reference to page 2765 > On tree page 2801 cell 5: Child page depth differs > On tree page 2801 cell 6: Child page depth differs > On tree page 2685 cell 65: 2nd reference to page 2766 > On tree page 2685 cell 65: Child page depth differs > On tree page 2685 cell 66: Child page depth differs > Page 167 is never used > Page 186 is never used > Page 232 is never used > Page 332 is never used > Page 335 is never used > Page 337 is never used > Page 347 is never used > Page 364 is never used > Page 379 is never used > Page 383 is never used > Page 398 is never used > Page 399 is never used > Page 407 is never used > Page 412 is never used > Page 417 is never used > Page 439 is never used > Page 458 is never used > Page 469 is never used > Page 483 is never used > Page 500 is never used > Page 502 is never used > Page 505 is never used > Page 508 is never used > Page 510 is never used > Page 512 is never used > Page 535 is never used > Page 547 is never used > Page 548 is never used > Page 562 i sgi::DbColumn > + name "integrity_check" > std::basic_string<char,std::char_traits<char>,std::allocator<char> > > + value "*** in database main *** > On tree page 2801 cell 5: 2nd reference to page 2765 > On tree page 2801 cell 5: Child page depth differs > On tree page 2801 cell 6: Child page depth differs > On tree page 2685 cell 65: 2nd reference to page 2766 > On tree page 2685 cell 65: Child page depth differs > On tree page 2685 cell 66: Child page depth differs > Page 167 is never used > Page 186 is never used > Page 232 is never used > Page 332 is never used > Page 335 is never used > Page 337 is never used > Page 347 is never used > Page 364 is never used > Page 379 is never used > Page 383 is never used > Page 398 is never used > Page 399 is never used > Page 407 is never used > Page 412 is never used > Page 417 is never used > Page 439 is never used > Page 458 is never used > Page 469 is never used > Page 483 is never used > Page 500 is never used > Page 502 is never used > Page 505 is never used > Page 508 is never used > Page 510 is never used > Page 512 is never used > Page 535 is never used > Page 547 is never used > Page 548 is never used > Page 562 is never used > Page 608 is nev" > std::basic_string<char,std::char_traits<char>,std::allocator<char> > > > Andrew Reusche > Software Engineer > 678-297-5236 > > > -----Original Message----- > From: D. Richard Hipp [mailto:d...@hwaci.com] > Sent: Tuesday, October 06, 2009 7:32 AM > To: Reusche, Andrew > Cc: General Discussion of SQLite Database > Subject: Re: [Retrieved]Re: [sqlite] DB Corruption > > > On Oct 5, 2009, at 9:59 AM, Reusche, Andrew wrote: > >> Thanks. We are beyond the point of recovering data at this point, we >> will just start over with a fresh db. Two questions pertaining to >> what >> may have caused this: >> >> 1) We are trying to programmatically update the primary keys on >> this >> table, in order to sync it up with a remote database. In the event >> that >> this needs to occur, we typically need to take a chunk of records in >> this table, and increment all the "sales_id"'s - integer, primary >> key - >> to a higher number. Do you anticipate that this could cause a >> problem? > > If the "from" range and the "to" range of the primary key overlap, you > may bump uniqueness constraints. > > If you are asking if this can cause database corruption, the answer is > "no". It is not possible to cause database corruption using SQL > (valid or invalid) as far as we know, and this is very carefully > tested. Please see http://www.sqlite.org/atomiccommit.html and > http://www.sqlite.org/testing.html > for further information. > >> >> 2) In the routine mentioned above in 1), and in a number of various >> inserts and updates, I found out that we were not calling "begin" or >> "commit." From the documentation on >> http://www.sqlite.org/lang_transaction.html it looks like a >> transaction >> is automatically started, so this should not be a problem. Do you >> recommend that I call begin and commit anyway, or do you think that >> will >> not provide any additional benefit? > > BEGIN...COMMIT will make your code run much faster, but it will not do > anything to prevent database corruption. See the reference above. > > If you are already doing hundreds or thousands of updates without > BEGIN...COMMIT and you haven't already complained about the speed, > this suggests that your database is running asynchronously (i.e. with > PRAGMA synchronous=OFF or on a system that does not support fsync() or > the equivalent). In that case, a simple power failure is all that it > takes to corrupt the database file. > > D. Richard Hipp > d...@hwaci.com > > This communication (including any attachments) is intended for the > use of the intended recipient(s) only and may contain information > that is confidential, privileged or legally protected. Any > unauthorized use or dissemination of this communication is strictly > prohibited. If you have received this communication in error, please > immediately notify the sender by return e-mail message and delete > all copies of the original communication. Thank you for your > cooperation. D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
