Re: [sqlite] Corrupt Database Problems

[Jim Wilcoxson]

On 5/18/09, Kevin Gale  wrote:

> According to the PRAGMA command documentation the database might become
> corrupted if the o/s crashes or the computer loses power before the data has
> been written to the disk surface. From the information we have from the
> customer they continued to use the computer for some time after performing
> the final save. This indicates that there could be other situations that
> also cause this problem.

It really depends on when the OS decides to flush its cache.  All/most
OS's cache data you write before actually putting it on the drive.
These days, drives also cache writes.  When the application says "sync
this to disk", it's supposed to wait until the data is physically
recorded on the drive before returning.  But because of all the
caching going on at various levels, it's sometimes hard to ensure this
happens, and it's hard to verify that the write really did happen:
when you try to read the data, the OS gives it to you, either from its
cache (most likely) or from the drive, but you can't tell which was
the source.

You might want to do some crash tests with a virtual machine to find
out what's happening.

Good luck!
Jim
-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupt Database Problems

[Kevin Gale]

Hi Jim.

Thanks for your reply.

Unfortunately, it looks like the database is beyond repair. I can pull some of 
the data back via the rowid but it is only the configuration data for the 
document and not the user's data.

According to the PRAGMA command documentation the database might become 
corrupted if the o/s crashes or the computer loses power before the data has 
been written to the disk surface. From the information we have from the 
customer they continued to use the computer for some time after performing the 
final save. This indicates that there could be other situations that also cause 
this problem.

Anyway, in the next build of the software we have removed the PRAGMA command 
and have also changed the save code so that it keeps the previous couple of 
versions in a history sub-folder. If customers still get problems at least they 
should now be able to go back to a previous version.


Regards,
Kev.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Jim Wilcoxson [pri...@gmail.com]
Sent: 15 May 2009 18:08
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupt Database Problems

I think you have answered your own question.  If you use
synchronous=off, you are saying "I don't care much about this
database."  When you "save" documents, you are merely putting them in
a computer's cache (memory) and then confirming to the user that they
are on the hard drive, when they aren't necessarily there.

So, user clicks Save, program says it saved it, user turns off
computer, database is corrupt.  Don't know why this would happen all
of a sudden, unless maybe they upgraded their OS and it has decided to
cache volatile data longer to increase performance at the expense of
data integrity.

I hope you're able to rescue your data.  Someone else mentioned on
this list a while back that they could recover their data by doing
retrieval based on rowid: do a select * where rowid=1, then 2, then 3,
etc. until you get a failure.  Once you get a failure, the rest is
lost.

Good luck,
Jim

On 5/15/09, Kevin Gale <kev...@transeomedia.com> wrote:
...
> 4. synchronous is OFF (we have stopped setting this in the new build of our
> app).

--
Software first.  Software lasts!
___
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

Re: [sqlite] Corrupt Database Problems

[John Machin]

On 16/05/2009 3:08 AM, Jim Wilcoxson wrote:
> I think you have answered your own question.  If you use
> synchronous=off, you are saying "I don't care much about this
> database."  When you "save" documents, you are merely putting them in
> a computer's cache (memory) and then confirming to the user that they
> are on the hard drive, when they aren't necessarily there.
> 
> So, user clicks Save, program says it saved it, user turns off
> computer, database is corrupt.  Don't know why this would happen all
> of a sudden, unless maybe they upgraded their OS and it has decided to
> cache volatile data longer to increase performance at the expense of
> data integrity.
> 
> I hope you're able to rescue your data.  Someone else mentioned on
> this list a while back that they could recover their data by doing
> retrieval based on rowid: do a select * where rowid=1, then 2, then 3,
> etc. until you get a failure.  Once you get a failure, the rest is
> lost.

Not necessarily. I have been looking at a corrupt cookies.sqlite 
database from a FireFox incident a few months ago.  It has similar 
output from pragma integrity_check to what Kevin posted, with an added 
bonus: one case where an overflow page chain points to a data page 
instead of an overflow page. Doing a less tedious equivalent of the 
above (follow the B-tree until the first appearance of corruption) 
yielded 74 rows. Note that stopping at the first select failure is 
equivalent to stopping at the first "error code 11" error message. Not 
much of the file has been traversed. Skipping bad rows and bad pages 
yielded 853 rows with 833 unique rowids -- I haven't yet checked whether 
same rowid means same contents always/sometimes/never.

There were 3 "never used" pages; should possibly be described as 
"orphan" rather than "never used", because one was all zeroes but the 
other 2 look like data pages -- not yet explored.

Cheers,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupt Database Problems

[Jim Wilcoxson]

I think you have answered your own question.  If you use
synchronous=off, you are saying "I don't care much about this
database."  When you "save" documents, you are merely putting them in
a computer's cache (memory) and then confirming to the user that they
are on the hard drive, when they aren't necessarily there.

So, user clicks Save, program says it saved it, user turns off
computer, database is corrupt.  Don't know why this would happen all
of a sudden, unless maybe they upgraded their OS and it has decided to
cache volatile data longer to increase performance at the expense of
data integrity.

I hope you're able to rescue your data.  Someone else mentioned on
this list a while back that they could recover their data by doing
retrieval based on rowid: do a select * where rowid=1, then 2, then 3,
etc. until you get a failure.  Once you get a failure, the rest is
lost.

Good luck,
Jim

On 5/15/09, Kevin Gale  wrote:
...
> 4. synchronous is OFF (we have stopped setting this in the new build of our
> app).

-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Corrupt Database Problems

[Kevin Gale]

Hi.

We are using SQLite 3.3.6 via Realbasic & the REALSQLDatabase plug-in. Over the 
past few weeks we have had calls from different customers stating that the 
documents that they have been saving from our application (a sqlite database) 
are now empty.

I have received the database from the customer and when I step through the 
statements I get the following RB error when I perform a select on one of the 
tables:
SQL logic error or missing database

If I open the database using SQLiteManager (an OS X GUI application) I get 
several errors (including the following) when I analyse the database:
*** in database main ***
Page 5517: initPage() returns error code 11
Page 5518: initPage() returns error code 11
Page 5491: initPage() returns error code 11
On tree page 22 cell 58: Child page depth differs
Page 5508: initPage() returns error code 11
Page 5510: initPage() returns error code 11
Page 5520: initPage() returns error code 11
Page 5584: initPage() returns error code 11
Page 5758: initPage() returns error code 11
On tree page 17 cell 15: Child page depth differs
Page 5817: initPage() returns error code 11
Page 5993: initPage() returns error code 11
Page 6172: initPage() returns error code 11
Page 6362: initPage() returns error code 11
Page 6582: initPage() returns error code 11
Page 515 is never used
Page 516 is never used
Page 517 is never used
Page 518 is never used
Page 519 is never used
Page 520 is never used
Page 521 is never used
Page 522 is never used
Page 523 is never used


When we received the first report of the problem we passed it off as a problem 
with the user's computer. However, we have now had at least 5 of the reports so 
we now need to find the cause of the problem.
Here is more info on what we are doing:
1. The database is always ran single user.

2. A new database file is always created on the local hard disk every time the 
user saves.

3. AutoCommit is disabled.

4. synchronous is OFF (we have stopped setting this in the new build of our 
app).

5. The databases are encrypted.


We are now in a position where we are going to have to tell the customer that 
they are going to have to start again. This is going to be very upsetting as 
they will have spent a long time creating this document (my guess is that they 
won't have a backup).

What we are looking for is help in trying to understand why the problem is 
occurring and what we can do to prevent it. If anyone could help trying to 
restore the database file that would also help us.


Best Regards,
Kevin Gale
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users