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.

Answer 1

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.

Answer 2

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).

Answer 3

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

Answer 4

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/

Answer 5

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.)

Answer 6

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);