Is it safe to delete sqlite's WAL file?

Question

I have a strange problem with Core Data in an iOS app where sometimes the WAL file becomes huge (~1GB). It appears there are other people with the problem (e.g. Core Data sqlite-wal file gets MASSIVE (>7GB) when inserting ~5000 rows).

My initial thought is to delete the WAL file at app launch. It seems from reading the sqlite documentation on the matter that this will be fine. But does anyone know of any downsides to doing this?

I'd of course like to get to the bottom of why the WAL file is growing so big, but I can't get to the bottom of it right now and want to put in a workaround while I dig deeper into the problem.

It's worth pointing out that my Core Data database is more of a cache. So it doesn't matter if I lose data that's in the WAL. What I really need to know is, will the database be completely corrupted if I delete the WAL? My suspicion is no, otherwise the WAL doesn't serve one of its purposes.

Lashundalasker

About "otherwise the WAL doesn't serve one of its purposes" -- The purpose of WAL is not to protect the database from corruption due to deleting the WAL. How can it possibly protect anything if doesn't exist anymore? See my answer. It's like saying you can delete redundancy bits from Hamming coded data because the purpose of those bits is protecting data from corruption. – Novak

Answer 1

It baffles me how many people here are suggesting it's safe to delete WAL files, without even bad looks in their direction.

The documentation explicitly lists this as one of the official ways to corrupt a database. It doesn't say deleting a hot WAL may cause you to lose most recent transactions or something benign like that. It says it may corrupt the database.

Why? Because an application may have crashed in the middle of a checkpointing operation. When this happens, the database file itself is in an invalid state unless paired with the new data contained in the WAL.

So the answer is a clear no. Don't delete WAL files.

What you can do to clear the file is call PRAGMA schema.wal_checkpoint(TRUNCATE);

Novak

Answer 2

Couple of things:

1. You can certainly delete the WAL file. You will lose any committed transactions that haven't been checkpointed back to the main file. (Thus violating the "durability" part of ACID, but perhaps you don't care.)

2. You can control the size of the WAL file on disk with the journal_size_limit pragma (if it bothers you). You may want to manually checkpoint more often too. See "Avoiding Excessively Large WAL files" here: https://www.sqlite.org/wal.html

3. I dislike all the superstitious bashing of WAL mode. WAL mode is faster, more concurrent, and much simpler since it dispenses with the all the locking level shenanigans (and most "database is busy" problems) that go with rollback journals. WAL mode is the right choice in almost every situation. (The only place it is problematic is on flash filesystems that don't support shared memory-mapped access to files. In that case, the "unofficial" SQLITE_SHM_DIRECTORY compile directive can be used to move the .shm file to a different kind of filesystem -- e.g. tmpfs -- but this should not be a concern on iOS.)

Devolve

I like this answer. WAL is the right choice. It has better concurrency feature. Lock less items in the database than journal mode does. – Overmodest

Since this is the most upvoted answer, I need to comment. You certainly must not delete the WAL file. See my answer for details. You don't just lose durability, you also lose the atomicity and consistency parts. I agree with the rest of the answer, but this should be corrected. – Novak

Answer 3

WAL mode has problems, don't use it. Problems vary but the very large size your report is one, other problems include failure during migration (using NSPersistentStoreCoordinators migratePersistentStore) and failure during importing of iCloud transaction logs. So while there are reported benefits until these bugs are fixed its probably unwise to use WAL mode.

And NO you can't delete the Write Ahead Log, because that contains the most recent data.

Set the database to use rollback journal mode and I think you will find you no longer have these very large files when loading lots of data.

Here is an extract which explains how WAL works. Unless you can guarantee that your app has run a checkpoint I don't see how you can delete the WAL file without running the risk of deleting committed transactions.

How WAL Works

The traditional rollback journal works by writing a copy of the original unchanged database content into a separate rollback journal file and then writing changes directly into the database file. In the event of a crash or ROLLBACK, the original content contained in the rollback journal is played back into the database file to revert the database file to its original state. The COMMIT occurs when the rollback journal is deleted.

The WAL approach inverts this. The original content is preserved in the database file and the changes are appended into a separate WAL file. A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL. Multiple transactions can be appended to the end of a single WAL file.

Checkpointing

Of course, one wants to eventually transfer all the transactions that are appended in the WAL file back into the original database. Moving the WAL file transactions back into the database is called a "checkpoint".

Another way to think about the difference between rollback and write-ahead log is that in the rollback-journal approach, there are two primitive operations, reading and writing, whereas with a write-ahead log there are now three primitive operations: reading, writing, and checkpointing.

By default, SQLite does a checkpoint automatically when the WAL file reaches a threshold size of 1000 pages. (The SQLITE_DEFAULT_WAL_AUTOCHECKPOINT compile-time option can be used to specify a different default.) Applications using WAL do not have to do anything in order to for these checkpoints to occur. But if they want to, applications can adjust the automatic checkpoint threshold. Or they can turn off the automatic checkpoints and run checkpoints during idle moments or in a separate thread or process.

Gaur

WAL has benefits though. And if it really is that bad, then why did Apple choose it as default on iOS 7? I don't want to delete the WAL randomly, I want to delete it just when the app opens. Will that be OK? – Lashundalasker

No you can't delete it ever, read up on the SQLite docs to find out what it is. – Gaur

Yes, I know what it is. So far from testing I've not had any issues arise from deleting it on app startup, but I'm still testing. – Lashundalasker

What if I said I don't care about losing data? In this app, it's used more as a cache of data downloaded from the internet. – Lashundalasker

After having our database go corrupt on numerous occasions (mostly due to -shm file being somehow out of sync, or due to some sudden app crash etc even with synchronous mode set to FULL), I can safely say WAL has several benefits but fragile as glass. I'm now hoping to move back to DELETE mode given the number of complaints we've had over the past few months. – Seesaw

Answer 4

I have been seeing quite a few negative reports on WAL in iOS 7. I have had to disable it on several projects until I have had time to explore the issues more throughly.

I would not delete the journal file but you could play with the option of vacuuming the SQLite file which will cause SQLite to "consume" the journal file. You can do this by adding the NSSQLiteManualVacuumOption as part of the options when you add the NSPersistentStore to the NSPersistentStoreCoordinator.

If that ends up being time consuming then I would suggest disabling WAL. I have not seen any ill effects to disabling it (yet).

Yesteryear

Thanks Marcus! I'm still to find a truly definitive answer on whether it's OK or not to delete the WAL file. It just seems to me like it would be fine, because of the way it's meant to work. Never-the-less, your idea about vacuuming gives me some food for thought. Disabling WAL sounds like a possibility - but I need to instrument again. I thought I was seeing extremely improved performance with WAL (I am doing a lot of writing when reads are going on as well). – Lashundalasker

I would not delete the journal there is a risk there that an incomplete write is sitting in that file. I would either vacuum or turn it off. Deleting has a lot of theoretical risk. – Yesteryear

What if I said I don't care about losing data? In this app, it's used more as a cache of data downloaded from the internet. – Lashundalasker

Then I would delete the sqlite file at the same time. I have seen situations where replacing the sqlite and not replacing the journal corrupts the sqlite file. It stands to reason that the reverse should also be true. I have not seen the reverse yet but it has only been 6 months or so :) – Yesteryear

Heh, yeh makes sense. Thanks for your inout Marcus! Much appreciated. – Lashundalasker

Have issues with WAL persisted in iOS 8? I have been struggling with occasional reports of the database "rolling back" to a prior state and was wondering if this could be the culprit. Thanks for a great book btw Marcus. – Loiretcher

I have not heard of any issues recently except when the app deletes the SQLite file but doesn't delete the WAL files. Otherwise it has been stable and solid. – Yesteryear

Rolling back to me sounds like a save failing @jeffmax329. – Yesteryear

Answer 5

There are quite good answers on this thread, but i'm adding this one to link to the Apple official QnA about journaling mode in iOS7 Core Data: https://developer.apple.com/library/ios/qa/qa1809/_index.html

They give differents solutions:

To safely back up and restore a Core Data SQLite store, you can do the following:

Use the following method of NSPersistentStoreCoordinator class, rather than file system APIs, to back up and restore the Core Data store:

- (NSPersistentStore *)migratePersistentStore:(NSPersistentStore *)store toURL:(NSURL *)URL options:(NSDictionary *)options withType:(NSString *)storeType error:(NSError **)error 

Note that this is the option we recommend.

Change to rollback journaling mode when adding the store to a persistent store coordinator if you have to copy the store file. Listing 1 is the code showing how to do this:

Listing 1 Use rollback journaling mode when adding a persistent store

NSDictionary *options = @{NSSQLitePragmasOption:@{@"journal_mode":@"DELETE"}}; if (! [persistentStoreCoordinator addPersistentStoreWithType:NSSQLiteStoreType
                                            configuration:nil
                                            URL:storeURL
                                            options:options
                                            error:&error]) 
{
    // error handling. 
} 

For a store that was loaded with the WAL mode, if both the main store file and the corresponding -wal file exist, using rollback journaling mode to add the store to a persistent store coordinator will force Core Data to perform a checkpoint operation, which merges the data in the -wal file to the store file. This is actually the Core Data way to perform a checkpoint operation. On the other hand, if the -wal file is not present, using this approach to add the store won't cause any exceptions, but the transactions recorded in the missing -wal file will be lost.

VERY IMPORTANT EDIT

If some of your users are on iOS 8.1 and you chose the first solution (the one Apple recommends), note that their many-to-many data relationships will be completely discarded. Lost. Deleted. In the entire migrated database.

This is a nasty bug apparently fixed in iOS 8.2. More info here http://mjtsai.com/blog/2014/11/22/core-data-relationships-data-loss-bug/

Pharos

Answer 6

You should never delete the sqlite WAL file, it contains transactions that haven't been written to the actual sqlite file yet. Instead force the database to checkpoint, and then clean up the WAL file for you.

In CoreData the best way to do this is to open the database with the DELETE journal mode pragma. This will checkpoint and then delete the WAL file for you.

NSDictionary *options = @{ NSSQLitePragmasOption: @{ @"journal_mode": @"DELETE"}};
[psc addPersistentStoreWithType:NSSQLiteStoreType
                  configuration:nil
                            URL:_storeURL
                        options:options
                          error:&localError];

For sanity sake you should ensure you only have one connection to the persistent store when you do this, i.e. only one persistent store instance in a single persistent store coordinator.

FWIW in your particular case you may wish to use TRUNCATE or OFF for your initial database import, and switch to WAL for updates.

https://www.sqlite.org/pragma.html#pragma_journal_mode

Ephraimite

The problem is though that the time it takes to load in the 1GB WAL file is just too long, so there's no way to do that. And I don't care about any data that hasn't been committed. It's purely being used as a cache. Would that change your answer at all? – Lashundalasker

The second part of my answer still stands, use OFF so there's no journal at all. Thereby avoiding the problem entirely. The downside of that is a crash will pwn your database if it's in the middle of a commit. But if this is just a cache you can always rebuild it. – Ephraimite