Re: [sqlite] Use journal after program crashed
Cecil, Richard If the WAL file contains multiple commits then it is possible (with appropriate tools) to use any of commits and effectively roll back to a chosen previous version. How much of this data you can get at depends upon the size that the WAL file will auto checkpoint and also the size of your transactions. Big transactions mean less of them are stored in a given WAL. Even when a WAL is in use the occasions where this technique is useful are limited. Other than big transactions being a problem, if the application is correctly shut down and the WAL checkpointed it will be deleted so previous transactions will be lost, i.e. in order to engineer a situation when a WAL is present I need to kill my application rather than close it down. My software can show how the database looked at each available checkpoint but it is aimed at forensic use rather than actually making a working database from a previous transaction. There is an article on my web site that goes into a little detail on this and shows how you can, in some circumstances, use multiple transactions in a WAL file to determine when a record was deleted: http://sandersonforensics.com/forum/content.php?261-Detecting-when-a-record-was-deleted-in-SQLite Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 25 May 2016 at 04:36, Richard Hipp wrote: > On 5/24/16, Cecil Westerhof wrote: >> 2016-05-25 3:49 GMT+02:00 Richard Hipp : >> >>> On 5/24/16, Cecil Westerhof wrote: >>> > Until now I did not need it, but better inform before I do. ;-) >>> > >>> > When a program crashes it is possible that you have a journal file with >>> > things that are not committed. Is there a way to find out what those >>> > are? >>> > (And selectively commit?) >>> > > A rollback journal contains the original content of the database. The > database file itself contains the partial uncommitted changes. When > the automatic rollback occurs, this will restore the database to its > original pre-transaction state. > > If you try to prevent the automatic rollback in order to "keep" some > of your uncommitted changes you will corrupt your database. > > The WAL file contains changes that have already been committed but not > moved back into the main database, and possible uncommitted changes. > The committed changes will automatically be moved into the main > database the next time the database is opened. You cannot force > uncommitted changes to commit. If you try, you will corrupt the > database. > > Bottom line: The transaction mechanism is there to protect you from > problems on a power failure. If you try to defeat that mechanism, you > will run into corruption problems. > > -- > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use journal after program crashed
On 5/24/16, Cecil Westerhof wrote: > 2016-05-25 3:49 GMT+02:00 Richard Hipp : > >> On 5/24/16, Cecil Westerhof wrote: >> > Until now I did not need it, but better inform before I do. ;-) >> > >> > When a program crashes it is possible that you have a journal file with >> > things that are not committed. Is there a way to find out what those >> > are? >> > (And selectively commit?) >> A rollback journal contains the original content of the database. The database file itself contains the partial uncommitted changes. When the automatic rollback occurs, this will restore the database to its original pre-transaction state. If you try to prevent the automatic rollback in order to "keep" some of your uncommitted changes you will corrupt your database. The WAL file contains changes that have already been committed but not moved back into the main database, and possible uncommitted changes. The committed changes will automatically be moved into the main database the next time the database is opened. You cannot force uncommitted changes to commit. If you try, you will corrupt the database. Bottom line: The transaction mechanism is there to protect you from problems on a power failure. If you try to defeat that mechanism, you will run into corruption problems. -- 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
Re: [sqlite] Use journal after program crashed
2016-05-25 3:49 GMT+02:00 Richard Hipp : > On 5/24/16, Cecil Westerhof wrote: > > Until now I did not need it, but better inform before I do. ;-) > > > > When a program crashes it is possible that you have a journal file with > > things that are not committed. Is there a way to find out what those are? > > (And selectively commit?) > > Are you talking about a rollback journal or a write-ahead log (WAL) file? > ​Probably both. ;-) (If that is possible.) As said: at the moment I do not need it. But I just want to be prepared when I do. I have had a few times when playing with SQLite that my program crashed. In those instances it was not a big problem that I lost my data. But it made me think about what to do if it would be a problem. Fast response by the way. :-) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use journal after program crashed
On 5/24/16, Cecil Westerhof wrote: > Until now I did not need it, but better inform before I do. ;-) > > When a program crashes it is possible that you have a journal file with > things that are not committed. Is there a way to find out what those are? > (And selectively commit?) Are you talking about a rollback journal or a write-ahead log (WAL) file? -- 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] Use journal after program crashed
Until now I did not need it, but better inform before I do. ;-) When a program crashes it is possible that you have a journal file with things that are not committed. Is there a way to find out what those are? (And selectively commit?) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
