Magic self-healing database!

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

So....does 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Magic self-healing database!

Richard Hipp-3
On 2/22/17, Ward WIllats <[hidden email]> 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
>
> So....does 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...