Room DB file import error ('no such table: room_table_modification_log')

Question

I'm trying to get my app to backup & restore its DB file (e.g. mydatabase.db). Backup runs fine - it exports a single .db file. It doesn't export the -shm / -wal files because I run a 'pragma wal_checkpoint(full)' to flush any updates to the main file. Restore appears to run fine but afterwards when my app queries the newly copied data I get this error (but the app doesn't crash).

E/ROOM: Invalidation tracker is initialized twice :/.
E/SQLiteLog: (1) no such table: room_table_modification_log
E/ROOM: Cannot run invalidation tracker. Is the db closed?
    android.database.sqlite.SQLiteException: no such table: room_table_modification_log 

If I then try to update any of the data I get these 2 errors (and now the app does crash):

java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
...
E/ROOM: Cannot run invalidation tracker. Is the db closed?
    android.database.sqlite.SQLiteException: no such table: room_table_modification_log (code 1 SQLITE_ERROR): 

If I close my app, and re-start it, everything runs fine and the new data can be queried/updated without error. I'm using Room 2.2.5 and see this error on different devices I have (Android 6 and 10).

My code below. I close the DB before doing the restore otherwise the app DB doesn't get updated.

private void dbImport() {

    // DB Backup file to import (e.g. mydatabasebackup.db)
    final Uri backupFile = viewModel.getDBImportFile();

    String internalDBFile = context.getDatabasePath(APP_DATABASE_NAME).toString();

    // Close app database
    MyDatabase.destroyInstance();

    restoreDatabase(backupFile, internalDBFile);
}

private void restoreDatabase(Uri backupFile, String internalDBFile) {
    
    InputStream inputStream = context.getContentResolver().openInputStream(backupFile);
    OutputStream outputStream = new FileOutputStream(internalDBFile);

    // Copy backup -> DB
    byte[] buffer = new byte[1024];
        int read;
        while ((read = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, read);
        }
        inputStream.close();

        // Write the output file (this has now copied the file)
        outputStream.flush();
        outputStream.close();
    }

And my Room Db class

public abstract class MyDatabase extends RoomDatabase {

    private static MyDatabase DB_INSTANCE;

    public static MyDatabase getDatabase(final Context context) {
        if (DB_INSTANCE == null) {
            synchronized (MyDatabase.class) {
                if (DB_INSTANCE == null) {
                    DB_INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                            MyDatabase.class, APP_DATABASE_NAME)
                            .addMigrations(new Migration_1_2(context, 1, 2))
                            .build();
                }
            }
        }
        return DB_INSTANCE;
    }

    public static void destroyInstance() {
        if (DB_INSTANCE != null) {
            if (DB_INSTANCE.isOpen()) {
                DB_INSTANCE.close();
            }
            DB_INSTANCE = null;
        }
    }

Farreaching

1. Can you try deleting the -shm and -wal files prior to calling restoreDatabase? 2. Can you add Migration_1_2? – Stanzel

mcemak - Thanks for the reply. When destroyInstance() runs just before restoreDatabase() it closes the DB and those 2 files are no longer there at that point - just the .db file. About the migration, I don't think it's been running when the errors happen. I'll try going back a DB version and see if that makes a difference. – Farreaching

Answer 1

Answering my own question in case anyone else has the same problem.

I do the DB file import in BackupActivity and once done it returns to my MainActivity (that queries the DB and displays the data). Back in the MainActivity I re-create my LiveData observer and re-run my DB queries again to get the data from the newly imported DB. The problem seemed to be that I wasn't re-creating the Repo/DAO for the new DB. The ViewModel was still holding on to the previous Repo reference because of the if/null check.

ViewModel

   LiveData<User> userLD = getUserRepo().getUser(); 
    
   private UserRepo getUserRepo() {
        if (userRepo == null) {
            userRepo = new UserRepo(getApplication());
        }
        return userRepo;
    }

UserRepo

    // Constructor
    public UserRepo(Application application) {
        super();
        MyDatabase db = MyDatabase.getDatabase(application);
        dao = db.getUserDao();
    }

By recreating the UserRepo (getting rid of the if/null check) the new Repo/DAO were based upon the new DB and now all works fine.

Another solution that also worked, was that when returning from BackupActivity after doing the DB Import I re-start the MainActivity from scratch with these flags (to finish all activities in the stack - including BackupActivity).

Intent intent = new Intent(this, MainActivity.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TASK | Intent.FLAG_ACTIVITY_NEW_TASK);
startActivity(intent);

Farreaching