I backed up the database by copying the db file, and then recovered the database by overwriting the db file. After recovery, the data of one of the tables was lost. This problem is occasional.

The recovery process is as follows:

1. Before starting to recover the database, close the currently running database and delete the corresponding database files, including shm and wal files

public void closeDb() {
        if (db != null) {
            if (db.isOpen()) {
                db.getOpenHelper().close();
            }
            db = null;
        }
    }

2. Delete the currently running database files, including shm and wal files, and copy the backup db files (only db files) to the database directory. This step uses the Java File API. It has been confirmed that the file operation result is correct

3. Reopen the database using the method of initializing the database.

public static DBManager init() {
        if (db == null) {
            db = Room.databaseBuilder(application,
                    MyDb.class, dbPath).allowMainThreadQueries().addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4, MIGRATION_4_5, MIGRATION_5_6).build();
        }
        return db;
    }

There are no errors in the whole execution process. However, the data of a table in the database cannot be queried. After I try to delete the shm and wal files and restart the app, the data can be queried.

Seen from the problem phenomenon, it seems to be the problem of shm and wal files, but I can't find a solution. I tried to recover the db file, re-open the database, delete the shm and wal files, and then open the database again. There is still a problem that the data of a table cannot be queried.

Has anyone encountered similar problems? Please help me. Thank you

I don't have any ideas to solve this problem

CodePudding user response：

Seen from the problem phenomenon, it seems to be the problem of shm and wal files, but I can't find a solution.

I believe that your issue is that you are not backing up the wal and shm files if they exist and are not empty. Such a backup would be an incomplete copy.

The wal file contains part of the database until it is fully checkpointed. If you do not cater for backing up a wal file that has any data, then you will lose data at best you may even corrupt the database.

You should always either copy the wal and shm file as well as the database if they exist or fully checkpoint, in which case the wal and shm files will be empty or deleted (should be the latter).

The easiest way to fully checkpoint the database is to close it and then backup the file (still best to be safe and see if the wal and shm file exists and back them up if there size is not 0 bytes).

You may wish to refer to https://www.sqlite.org/lang_vacuum.html

The restore should restore all 3 files if the 3 files were backed up (hence closing the database prior to backup is the way to go). The restore should also delete the wal and shm just in case they exist (shouldn't as you are closing the database before the restore).

Another backup option would be to to use VACUUM INTO, which I believe would be a wal/shm free copy of the file as the wal data is considered part of the database.

As for recovering you cannot, as there is no way to know what was in the wal file (the shm file isn't so important as it's a wal file for the wal file).