The idea behind the transaction file is the following:
The database is stored in pages of a fixed size. The first time a page is modified after the start of a transaction the old contents of the page are saved in the transaction log together with its index. Once the transaction is committed the log file is removed, making the change permanent. If your program crashes the next user of the database will notice the existence of the transaction log, and restore the database to its pre-transaction state before doing its own thing.
Deleting the transaction log will leave you with corrupted tables, out-of-date indexes and other ugly stuff, because your database was only half-updated, the cache was not flushed etc., which will almost certainly turn your database into toast.
In short: don't delete this file, let the sqlite library manage it.
If you want to copy a database file you either have to open the database file first to make sure the file is in a consistent state, or you have to copy both the database and the associated transaction log if it exists, which will roll back any uncommitted changes once you open the copy.
Gé
Aaron Schneider wrote:
Hello all,
We've been using SQLite for our database needs in our new program version, and unfortunately, we've run into some corruption issues. We believe that the corruption has been caused by deleting journal files.
The program is a music manager and when managing a portable device, we copy the database locally before opening it. If the program crashed in the middle of a transaction, a journal file would be left there. Therefore, the next time the database was opened, it did not match the journal file, and would cause "strange stuff" to happen. We detected the problems this caused, and our fix was to always delete the journal file before opening with sqlite_open(). After some more research, we discovered that always deleting the journal wasn't quite right. We now only delete the journal file when we know that the database and journal don't match.
So are there any other cases when we should delete the journal file when opening a database? Can always we leave the journal alone if we open the database while a transaction is active?
We are using SQLite 2.8.16.
Thanks, Aaron
