Ok...it's happened again and I've decided that I need to track this down
once and for all!
Here is what I'm seeing: I get errors when I do a integrity_check (see
below), but I can .dump it to a text file and then .read it into another
database ok.
It seems to me that I'm screwing up an index or something. Are indexes
stored at the end of the database file? All I can think of is that my
compression/encryption routines are messing something up and I'm trying to
figure out 'where' to look.
I guess the real question is, what would I have to do to make an
integrity_check fail, but still let a dump work correctly?
Many thanks for any advice on tracking down this ugliness.
Gene
>>Output details
sqlite> pragma integrity_check;
*** in database main ***
On tree page 3 cell 26: invalid page number 469
On tree page 3 cell 26: Child page depth differs
On tree page 3 cell 27: invalid page number 490
On tree page 3 cell 28: invalid page number 511
On tree page 3 cell 29: invalid page number 533
On tree page 3 cell 30: invalid page number 554
On tree page 3 cell 31: invalid page number 576
On tree page 3 cell 32: invalid page number 598
On tree page 3 cell 33: invalid page number 620
On tree page 3 cell 34: invalid page number 642
On tree page 3 cell 35: invalid page number 666
On tree page 3 cell 36: invalid page number 688
On tree page 3 cell 37: invalid page number 709
On tree page 3 cell 38: invalid page number 730
On tree page 3 cell 39: invalid page number 752
On page 3 at right child: invalid page number 773
On tree page 419 cell 86: invalid page number 462
On tree page 419 cell 86: Child page depth differs
On tree page 419 cell 87: invalid page number 463
I said...hmmmm...so I do a .dump to see if I can
sqlite> .output c:\\a.sql
sqlite> .dump
sqlite> .quit
resulting in:
05/27/2009 10:08 AM 496,051 a.sql
1 File(s) 496,051 bytes
0 Dir(s) 104,974,647,296 bytes free
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin
Sent: Saturday, April 25, 2009 8:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] corrupt database recovery
On 26/04/2009 5:47 AM, Gene wrote:
> Every now and again, we have a database that gets corrupt in the field
(bad
> coding on our end, not sqlite).
>
Hi Gene,
The obvious question: why not fix the bad code?
What does 'PRAGMA integrity_check;' say about these corrupt databases?
> When we get one of these corrupt databases, we recover what we can by get
> one row at a time by rowid, like 'select * from mytable where rowid = 1'
and
> we inc the row number every time. Each row we successfully recover we
> insert into a clean database.
How do you know when to stop incrementing the row number?
Does 'select min(rowid), max(rowid) from mytable' give you believable
answers?
What happens when you do 'select * from mytable' ?
Approximately how many rows are there?
How many 'select * from mytable where rowid = <some_number>' attempts
fail, and for what reasons (previously deleted, some other result code(s))?
Are the failures restricted to a relatively small range of rowids?
> Works pretty well, except over time we've added more and more columns,
each
> one that has to be pulled and bound to get it into the new table.
What is causing this "pull and bind" problem, the fact that some columns
weren't present initially? or just the sheer number of columns i.e.
you need to "pull and bind" all columns (not just the later additions)?
In any case, please explain what you mean by "pulled" and "bound".
> We tried
> to do an 'attach' so we could do a 'select into' the clean database
directly
> from the corrupt one. But as soon as we attempt to 'attach' the corrupt
> database, we understandable get a 'database is malformed' error.
It's not quite so understandable why 'select * from mytable where rowid
= 1' doesn't get an error.
> Is there an easier way to pull the good records out of a corrupt database
> and put them into a new one without binding each column by hand?
Can you give us an example of a row or two of (a) what you get from the
'select * from mytable where rowid = <whatever>' (b) the insert
statement that you need to do to insert that data into the clean
database? Doesn't have to be real data -- e.g. assume 3 columns
initially, now grown to 5.
What rules/procedure/recipe do you follow when producing (b) from (a) by
hand?
Assuming that 'select * from mytable' doesn't work, and subject to
understanding the pulling and binding by hand thing, I would have
thought the solution would look something like this:
Write a script that loops around doing 'select * from mytable where
rowid = ?' on a connection to your corrupt database and doing 'insert
into mytable values(?,?,?,?,? etc etc etc)' on a connection to your
clean database.
HTH,
John
_______________________________________________
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
