Restore SQLite DB from WAL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Restore SQLite DB from WAL

gsm-ginger
Hi, everyone

Is it possible to restore deleted rows or dropped tables provided the WAL and shm files are still intact and contain the data you want to restore?

Example scenario where vast swathes of content have vanished from mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty Android SMS app:

ls -al /data/data/com.android.providers.telephony/databases/mm*
-rw-rw----    1 root     root         60416 Jul 17 20:16 mmssms.db`
-rw-rw----    1 radio    radio        32768 Jul 17 16:18 mmssms.db-shm
-rw-rw----    1 radio    radio       628832 Jun 30 19:23 mmssms.db-wal

TIA,

Mandy
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

Richard Hipp-3
On Mon, Jul 23, 2012 at 12:10 PM, <[hidden email]> wrote:

> Hi, everyone
>
> Is it possible to restore deleted rows or dropped tables provided the WAL
> and shm files are still intact and contain the data you want to restore?
>

The WAL file does not contain the old data, it contains the new data.  If
the WAL file has not been checkpointed, you should be able to simply delete
the WAL file and the old data will still be in the original database file.
A "checkpoint" operation is the act of moving the new database from the WAL
file back into the original database file, presumably overwriting the old
data (depending on your settings and other factors).

But, if the WAL file has been partially checkpointed, and that checkpoint
was interrupted by a crash or power failure, deleting the WAL file will
corrupt your database.  So you should probably run the experiment on a
backup.  :-)


>
> Example scenario where vast swathes of content have vanished from
> mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty
> Android SMS app:
>
> ls -al /data/data/com.android.providers.telephony/databases/mm*
> -rw-rw----    1 root     root         60416 Jul 17 20:16 mmssms.db`
> -rw-rw----    1 radio    radio        32768 Jul 17 16:18 mmssms.db-shm
> -rw-rw----    1 radio    radio       628832 Jun 30 19:23 mmssms.db-wal
>
> TIA,
>
> Mandy
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

gsm-ginger
In reply to this post by gsm-ginger
So close, yet so far! This is really getting on my wick...

>> Example scenario where vast swathes of content have vanished from
>> mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty
>> Android SMS app:
>>
>> ls -al /data/data/com.android.providers.telephony/databases/mm*
>> -rw-rw---- 1 root root 60416 Jul 17 20:16 mmssms.db
>> -rw-rw---- 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm
>> -rw-rw---- 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal
> The WAL file does not contain the old data, it contains the new data. If
> the WAL file has not been checkpointed, you should be able to simply delete
> the WAL file and the old data will still be in the original database file.
> A "checkpoint" operation is the act of moving the new database from the WAL
> file back into the original database file, presumably overwriting the old
> data (depending on your settings and other factors).
> But, if the WAL file has been partially checkpointed, and that checkpoint
> was interrupted by a crash or power failure, deleting the WAL file will
> corrupt your database. So you should probably run the experiment on a
> backup. :-)

In my test, I started the phone in single-user/recovery mode, deleted
mmssms.db-wal and restarted. The deleted messages showed very briefly,
then vanished.
I then restored mmssms.db to its original (just after deletion) state
and deleted both mmssms.db-wal and mmssms.db-shm. Same result.
Not to be deterred, I then restored the mmssms.db, deleted the
mmssms.db-shm and created a zero-length file. In all cases the owner was
set to radio:radio (in the initial example below mmssms.db was incorrectly
set to root.)
In each case the same thing happens, the messages briefly display, then
disappear, mmssms.db, then mmssms.db-wal and mmssms.db-shm with the much
sought-after deleted messages, so (if I'm not mistaken) at least some of
the info is retained in mmssms.db itself, albeit with instructions to
purge.

Is there any way to merge the lost data stored in mmssms.db-wal /
mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a
bunch of DELETE FROM statements could they be changed to INSERT INTO?

Mandy
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

Richard Hipp-3
On Mon, Jul 23, 2012 at 3:51 PM, <[hidden email]> wrote:

>
> Is there any way to merge the lost data stored in mmssms.db-wal /
> mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a
> bunch of DELETE FROM statements could they be changed to INSERT INTO?
>


The WAL file does not store a bunch of DELETE FROM statements.  The WAL
file stores 4k pages of the database file as they will appear after the
delete occurs.  Details at http://www.sqlite.org/fileformat2.html

So, no, there is no way to do what you are asking.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

gsm-ginger
In reply to this post by gsm-ginger
> The WAL file does not store a bunch of DELETE FROM statements. The WAL
> file stores 4k pages of the database file as they will appear after the
> delete occurs. Details at http://www.sqlite.org/fileformat2.html
> So, no, there is no way to do what you are asking.

Damn!

Failing that, is there a means of extracting the data from the WAL / SHM
by some similar means to extracting from the DB?

Thanks

Mandy
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

Pavel Ivanov-2
In reply to this post by gsm-ginger
> In each case the same thing happens, the messages briefly display, then
> disappear

I think your sms-controlling app has synced the whole sms database
with some server. When you start your phone it shows you local data
but then it sees that server has latest data (maybe using modification
date on the database, maybe some synchronization token stored in the
database) and restores everything from there.

So maybe you need to look for some setting saying "allow to sync
everything with server" and "allow to sync everything back from
server".


Pavel


On Mon, Jul 23, 2012 at 3:51 PM,  <[hidden email]> wrote:

> So close, yet so far! This is really getting on my wick...
>
>>> Example scenario where vast swathes of content have vanished from
>>> mmssms.db thanks to an unintentional mass-DELETE FROM through the naughty
>>> Android SMS app:
>>>
>>> ls -al /data/data/com.android.providers.telephony/databases/mm*
>>> -rw-rw---- 1 root root 60416 Jul 17 20:16 mmssms.db
>>> -rw-rw---- 1 radio radio 32768 Jul 17 16:18 mmssms.db-shm
>>> -rw-rw---- 1 radio radio 628832 Jun 30 19:23 mmssms.db-wal
>> The WAL file does not contain the old data, it contains the new data. If
>> the WAL file has not been checkpointed, you should be able to simply delete
>> the WAL file and the old data will still be in the original database file.
>> A "checkpoint" operation is the act of moving the new database from the WAL
>> file back into the original database file, presumably overwriting the old
>> data (depending on your settings and other factors).
>> But, if the WAL file has been partially checkpointed, and that checkpoint
>> was interrupted by a crash or power failure, deleting the WAL file will
>> corrupt your database. So you should probably run the experiment on a
>> backup. :-)
>
> In my test, I started the phone in single-user/recovery mode, deleted
> mmssms.db-wal and restarted. The deleted messages showed very briefly,
> then vanished.
> I then restored mmssms.db to its original (just after deletion) state
> and deleted both mmssms.db-wal and mmssms.db-shm. Same result.
> Not to be deterred, I then restored the mmssms.db, deleted the
> mmssms.db-shm and created a zero-length file. In all cases the owner was
> set to radio:radio (in the initial example below mmssms.db was incorrectly
> set to root.)
> In each case the same thing happens, the messages briefly display, then
> disappear, mmssms.db, then mmssms.db-wal and mmssms.db-shm with the much
> sought-after deleted messages, so (if I'm not mistaken) at least some of
> the info is retained in mmssms.db itself, albeit with instructions to
> purge.
>
> Is there any way to merge the lost data stored in mmssms.db-wal /
> mmssms.db-shm back into mmssms.db? For example, if the DB/WAL stores a
> bunch of DELETE FROM statements could they be changed to INSERT INTO?
>
> Mandy
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

gsm-ginger
In reply to this post by gsm-ginger
> I think your sms-controlling app has synced the whole sms database
> with some server. When you start your phone it shows you local data
> but then it sees that server has latest data (maybe using modification
> date on the database, maybe some synchronization token stored in the
> database) and restores everything from there.
>
> So maybe you need to look for some setting saying "allow to sync
> everything with server" and "allow to sync everything back from
> server".
>
>
>Pavel

Quite possible, although AFAICT this is not the default behaviour unless the
SMS Backup / Restore is installed and set to sync to GMail.

This would be consistent with the fact that data was turned off for the entire
accidental delete, imaging and restore attempt.

Thanks anyway, though :)

M
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

gsm-ginger
In reply to this post by gsm-ginger
> The WAL file does not store a bunch of DELETE FROM statements. The WAL
> file stores 4k pages of the database file as they will appear after the
> delete occurs. Details at http://www.sqlite.org/fileformat2.html
> So, no, there is no way to do what you are asking

Is there, instead, a method for extracting the pages marked for ignore from the
DB/ WAL / SHM files and manually piecing back together, or issuing a directive
to alter the relevant page markers from "ignore" to their previous state?

This article implies there is:
http://digitalinvestigation.wordpress.com/2012/05/04/the-forensic-implications-of-sqlites-write-ahead-log/

That said, I'm guessing it would be a non-trivial task even for a seasoned DB programmer...

The only other way I can see out of it would be to create a dummy SMS DB
(e.g. with one test SMS), then use this method to create a XML template to
use with SMS Backup / restore, manually piecing the XML file togther using
data extracted with carefully-chosen* GNU strings parameters:

http://forum.xda-developers.com/showthread.php?t=1585957
http://forum.xda-developers.com/showthread.php?t=1683608

That or stump up the 250 GBP for Epilog SQLite analysis ;)

M

*At least it looked that way when running the SHM file through strings
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

LincolnBurrows
In reply to this post by Richard Hipp-3
Sometimes, the corruption is only or mostly in indexes, in which case it
would be possible to get some or most records by trying to dump the entire
database with .dump, and use those commands to create a new database:

$ sqlite3 mydata.db ".dump" | sqlite3 new.db

The easiest and most reliable way is to use  Sqlite Recovery Software
<http://www.sqlrecoverytool.com/recover-sqlite-database.html>   to fix
corruption in SQLite Database.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore SQLite DB from WAL

Simon Slavin-3


On 1 Sep 2017, at 1:23pm, LincolnBurrows <[hidden email]> wrote:

> Sometimes, the corruption is only or mostly in indexes, in which case it
> would be possible to get some or most records by trying to dump the entire
> database with .dump, and use those commands to create a new database:
>
> $ sqlite3 mydata.db ".dump" | sqlite3 new.db

SQLite now has a command suited for this purpose:

<https://sqlite.org/lang_reindex.html>

The REINDEX command by itself will automatically DROP and reCREATE all indexes in a database in one operation.  Like VACUUM it locks the database, so it’s not a good idea to use this when other threads/processes are trying to use the database.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users