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 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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
