Reopen ROOM database

Question

I'm trying to close and then reopen Room database. (The purpose is to backup SQLite file)

This is how I close it:

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

INSTANCE is a RoomDatabase object

And to reopen I'm initializing INSTANCE object again by calling:

Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, C.ROOM_DB_NAME)

After I move to another activity, I'm seeing this error in logcat: E/ROOM: Invalidation tracker is initialized twice

SELECT queries work fine, but INSERT fails with the following errors:

E/SQLiteLog: (1) no such table: room_table_modification_log

E/ROOM: Cannot run invalidation tracker. Is the db closed?
java.lang.IllegalStateException: The database '/data/user/0/ro.example.example/databases/mi_room.db' is not open.

Although INSTANCE.isOpen() returns true

Room version: 1.1.1

Does anyone know what is with this "room_table_modification_log" table?

Answer 1

For future readers: You don't need to close the database to copy the file to another location (create a backup).

The Android implementation for a SQLite database is normally in WAL (write-ahead log) mode. This uses 3 files in the background: The first with the name of your database (i.e. my_db, the second one with that name and the "-shm" suffix (my_db-shm) and the third one with the "-wal" suffix (my_db-wal). The -wal file will save changes.

If you want to make a backup using the normal path for your database (my_db file), you need to make sure that it is up to date with the latest transactions. You do this by running a checkpoint on the database. After this, you can copy this file to the desired location on the phone and then keep using your database without issues. The accepted answer on this question explains it well:

But if moving everything to the original database file is what you want to do, then you don't have to close the database.

You can force a checkpoint using the wal_checkpoint pragma instead. Query the following statement against the database. We use raw queries here as pragma is not yet supported by Room (it will trigger a UNKNOWN query type error).

Have this query inside of your DAO:

@RawQuery
int checkpoint(SupportSQLiteQuery supportSQLiteQuery);

And then when you call the checkpoint method, use the query then:

myDAO.checkpoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));

Answer 2

Downgrade your Room version to 1.1.1-rc1 and the problem will be gone. Keep an eye out on updates as this is a bug in 1.1.1

Answer 3

After spending so many days finally I found the solution to this, you need to create the table room_table_modification_log in onOpen Db callback.

Like below:

private fun buildDatabase(context: Context): MainDatabase {
            return Room.databaseBuilder(
                context.applicationContext,
                MainDatabase::class.java,
                databaseName
            ).addMigrations(MIGRATION_2_3)
                .addCallback(getCallback())
                .build()
        }

And implement the getCallback() function like this:

fun getCallback(): Callback {
    return object : Callback() {
        override fun onOpen(db: SupportSQLiteDatabase) {
            super.onOpen(db)
                db.execSQL("CREATE TEMP TABLE room_table_modification_log(table_id INTEGER PRIMARY KEY, invalidated INTEGER NOT NULL DEFAULT 0)")          
            }
        }
}

After doing the above things you will not able to get the error room_table_modification_log