Is it safe to delete sqlite's WAL file?

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?

  • Speed up saving image - iOS
  • Find UIGestureRecognizer action (selector) name and target
  • Using size classes programmatically
  • How to develop mobile device management application in iOS
  • Open specific viewcontroller when opening app from push notification
  • Turn page programmatically in UIPageViewController when using UIPageViewControllerDataSource?
  • 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.

    5 Solutions Collect From Internet About “Is it safe to delete sqlite's WAL file?”

    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.

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

    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/

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

    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.

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