Re: [sqlite] When is corruption acceptable?

[Dustin Sallings]

On Nov 19, 2010, at 12:17, Pavel Ivanov wrote:

>>1. When is it acceptable for sqlite to leave a corrupt database that 
>> can't be used?
> 
> It's never acceptable. SQLite specifically written and tested to
> manager "out of disk space" errors appropriately without database
> corruption.

Yes, this is the argument I've been making, and it kind of comes back 
to me when stuff is corrupt.  :/  Then people ask me why I don't just make 
something better.  While I do think I could build something that fit our 
application a little better, my confidence in sqlite is higher than whatever I 
come up with over the weekend.

>>I've got the data moved off to the side after it broke.
> 
> Did you move journal along with database? If not you made a big
> mistake. If it's not deleted yet and you didn't change anything in the
> database you can close all connections to it, put journal nearby and
> run command line utility again. After that integrity check should be
> okay.

I didn't actually do the move, but I've been told all of the files came 
together while the application was shut down.  There is no journal.

I've read the shell history and found that it appears that someone 
copied the files out from under the application while it was still running.  
This copy is what was corrupt.  We later removed the original and replaced them 
with their copy.

So, yay sqlite, confidence restored (now just wait for the data to be).

>>2. Is there any way to recover the data that didn't get corrupt 
>> (which should be lots)?
> 
> If there's no way to restore journal then you can only try .dump
> command from command line utility.


Thank you for the suggestion.  This looks like it's going to be helpful 
to get a lot of the data out.

-- 
Dustin Sallings

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

Re: [sqlite] When is corruption acceptable?

[Pavel Ivanov]

>        1. When is it acceptable for sqlite to leave a corrupt database that 
> can't be used?

It's never acceptable. SQLite specifically written and tested to
manager "out of disk space" errors appropriately without database
corruption.

>        I've got the data moved off to the side after it broke.

Did you move journal along with database? If not you made a big
mistake. If it's not deleted yet and you didn't change anything in the
database you can close all connections to it, put journal nearby and
run command line utility again. After that integrity check should be
okay.

>        2. Is there any way to recover the data that didn't get corrupt (which 
> should be lots)?

If there's no way to restore journal then you can only try .dump
command from command line utility.


Pavel

On Fri, Nov 19, 2010 at 3:12 PM, Dustin Sallings  wrote:
>
>        I have an application that was writing to a sqlite db until we ran out 
> of disk space.  The app failed to store data, commit, etc... and then was 
> killed.
>
>        I've got the data moved off to the side after it broke.  integrity 
> check says this:
>
> *** in database main ***
> Main freelist: 231854296 of 5 pages missing from overflow list starting at 
> 7184967
> On tree page 187114 cell 0: 4 of 5 pages missing from overflow list starting 
> at 5920373
> Page 5510841 is never used
> Page 5985431 is never used
> Page 6127423 is never used
> Page 6324952 is never used
> Page 7156369 is never used
> Page 7179495 is never used
> Page 7184966 is never used
> Page 7184968 is never used
>
>
>        -- clearly, overflow broke a bit.  I can do a count(*) on the table, 
> but I can't grab all of the rows due to overflow.
>
>        It seems a bit... delicate at this point, which leads to two questions:
>
>        1. When is it acceptable for sqlite to leave a corrupt database that 
> can't be used?
>        2. Is there any way to recover the data that didn't get corrupt (which 
> should be lots)?
>
> --
> Dustin Sallings
>
> ___
> 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] When is corruption acceptable?

[Black, Michael (IS)]

Did you try and do an .export of your database?
That would be my first thing to try.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dustin Sallings
Sent: Fri 11/19/2010 2:12 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:[sqlite] When is corruption acceptable?




I have an application that was writing to a sqlite db until we ran out 
of disk space.  The app failed to store data, commit, etc... and then was 
killed.

I've got the data moved off to the side after it broke.  integrity 
check says this:

*** in database main ***
Main freelist: 231854296 of 5 pages missing from overflow list starting at 
7184967
On tree page 187114 cell 0: 4 of 5 pages missing from overflow list starting at 
5920373
Page 5510841 is never used
Page 5985431 is never used
Page 6127423 is never used
Page 6324952 is never used
Page 7156369 is never used
Page 7179495 is never used
Page 7184966 is never used
Page 7184968 is never used


-- clearly, overflow broke a bit.  I can do a count(*) on the table, 
but I can't grab all of the rows due to overflow.

It seems a bit... delicate at this point, which leads to two questions:

1. When is it acceptable for sqlite to leave a corrupt database that 
can't be used?
2. Is there any way to recover the data that didn't get corrupt (which 
should be lots)?

--
Dustin Sallings

___
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] When is corruption acceptable?

[Dustin Sallings]

I have an application that was writing to a sqlite db until we ran out 
of disk space.  The app failed to store data, commit, etc... and then was 
killed.

I've got the data moved off to the side after it broke.  integrity 
check says this:

*** in database main ***
Main freelist: 231854296 of 5 pages missing from overflow list starting at 
7184967
On tree page 187114 cell 0: 4 of 5 pages missing from overflow list starting at 
5920373
Page 5510841 is never used
Page 5985431 is never used
Page 6127423 is never used
Page 6324952 is never used
Page 7156369 is never used
Page 7179495 is never used
Page 7184966 is never used
Page 7184968 is never used


-- clearly, overflow broke a bit.  I can do a count(*) on the table, 
but I can't grab all of the rows due to overflow.

It seems a bit... delicate at this point, which leads to two questions:

1. When is it acceptable for sqlite to leave a corrupt database that 
can't be used?
2. Is there any way to recover the data that didn't get corrupt (which 
should be lots)?

-- 
Dustin Sallings

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