Re: [sqlite] Magic self-healing database!

[Richard Hipp]

On 2/22/17, Ward WIllats  wrote:
> sqlite> pragma integrity_check;
> *** in database main ***
> On tree page 598 cell 2: Offset 65026 out of range 178..1020
> On tree page 598 cell 1: Offset 21743 out of range 178..1020
>
> Then I SIGKILL'd my application and restarted it, and it recovered 8 frames
> from the WAL file, and the app seemed to be working again. So I used the
> shell again while the app was running and lo and behold! The database was
> fixed!
>
> sqlite> pragma integrity_check;
> ok
>
> Sodoes this sorcery seem possible?

Somehow some bad data got written into the WAL file.  Or perhaps part
of the WAL file was overwritten by an error somewhere else in the
code.  (See, for example, paragraph 1.1 of
https://www.sqlite.org/howtocorrupt.html)  At recovery, SQLite saw
that the checksums did not match and rolled back the transaction
containing the error (and all subsequent transactions, if any) thus
fixing the problem.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Magic self-healing database!

[Ward WIllats]

Hello List.

Well, here's something I haven't seen before.

I've got a DB in WAL mode on an embedded Linux system. My app (which has 
several threads, each with a connection, with Sqlite running fully serialized) 
was reporting database corruption in its logs.

Sun Feb 19 07:52:45 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 07:52:45 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 07:58:46 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:06:24 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:06:24 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:11:05 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:11:05 2017 user.debug NTPd: ntpd event: stratum
Sun Feb 19 08:20:03 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:20:03 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:30:00 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:36:43 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:36:43 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:48:10 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:51:50 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:51:50 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 09:04:46 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
...and so on...

I shelled into the device with the app still running and ran the sqlite shell 
and did a "pragma integrity_check;" which reported:

sqlite> pragma integrity_check;
*** in database main ***
On tree page 598 cell 2: Offset 65026 out of range 178..1020
On tree page 598 cell 1: Offset 21743 out of range 178..1020

Then I SIGKILL'd my application and restarted it, and it recovered 8 frames 
from the WAL file, and the app seemed to be working again. So I used the shell 
again while the app was running and lo and behold! The database was fixed!

sqlite> pragma integrity_check;
ok

Sodoes this sorcery seem possible? Or have I just fooled myself somehow? 
What conclusions (if any) can I draw from this?

Thanks,

-- Ward

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