"no such table" exception after restoring database

SQLCipher Issues
1

Hello, I am trying to restore my exported database on Android, but after restoration, I get an error:

net.sqlcipher.database.SQLiteException: no such table: 
room_table_modification_log: INSERT OR IGNORE INTO 
room_table_modification_log VALUES(0, 0)

I use hilt android to manage my database. To initialize the database I use:

fun initialize(passphrase: String): Boolean {
        this.passphrase = SQLiteDatabase.getBytes(passphrase.toCharArray())
        this.factory = SupportFactory(this.passphrase, null, false)

        return try {
            myDatabase = Room.databaseBuilder(
                context.applicationContext,
                MyDatabase::class.java,
                DATABASE_NAME
            )
                .openHelperFactory(factory)
                .build()

            true
        } catch (e: Exception) {
            false
        }
    }

I use this function then to create my database backup:

fun createDatabaseBackup(backupFilePath: Uri): Boolean {
        return try {
            val dbFile = context.getDatabasePath(DATABASE_NAME)

            val inputStream = FileInputStream(dbFile)
            val outputStream =
                context.contentResolver.openOutputStream(backupFilePath) ?: return false

            val buffer = ByteArray(1024)
            var length: Int

            while (inputStream.read(buffer).also { length = it } > 0) {
                outputStream.write(buffer, 0, length)
            }

            outputStream.flush()
            outputStream.close()
            inputStream.close()

            true
        } catch (e: Exception) {
            e.printStackTrace()
            false
        }
    }

Then I can restore the database via function:

fun restoreDatabase(backupFilePath: Uri): Boolean {
        return try {
            val dbFile = context.getDatabasePath(DATABASE_NAME)
            val file = createTempDatabaseFile(backupFilePath) ?: return false
            file.copyTo(dbFile, overwrite = true)

            true
        } catch (e: Exception) {
            e.printStackTrace()
            false
        }
    }

After I restore the database I close the app, open it again and it causes the crashes. Not sure why. Opening the second time after the crash it does not crash anymore.

2

Hello @cjacky - One thing to note is that your backup and restore approach does not seem safe at all. I would not expect it to work properly if the database is being actively used since you could potentially copy in-flight changes out to the backup file resulting in an inconsistent database state. Similarly, when you restore, it doesn’t appear that you are taking into account the possibility journal files being present.

I would strongly consider trying to use sqlcipher_export() to backup the database contents to an attached database instead. This should be much more reliable that doing a byte-by-byte copy. If you are going to use your copy method, you should execute a BEGIN EXCLUSIVE; on the database to ensure it is not written to while your copy operation is occurring. Similarly, when you go about restoring, you should make sure that there are no open connections to the database at all, and ensure that there are no journal files (e.g. db.shm, db.wal, db-journal) present before attempting to restore the database file.

Finally, outside of the potential issues with the backup and restore logic, we have seen similar errors but they should be resolved in 4.5.4. Please make sure you are using the latest version of SQLCipher.

3

Hi, @sjlombardo, thanks for the answer. I’ve managed to export successfully (as I believe) my database content to my given file using sqlcipher_export(). However, I do still face the same error mentioned in the post when I restore the database.

I did modify the restoreDatabase method. I first close the database, delete the file itself, remove any journal files (as you mentioned), reinitialize the database file using databaseBuilder and then export the content from the previously exported database to the currently initialized database using sqlcipher_export(). Any modifications made after this method throws the error mentioned in the post. If I reopen the app, everything is fine. The error I get:

Cannot run invalidation tracker. Is the db closed?

android.database.sqlite.SQLiteException: no such table: room_table_modification_log: 
, while compiling: SELECT * FROM room_table_modification_log WHERE invalidated = 1;

Here is the code to restore the database:

val backup = createTempDatabaseFile(backupFilePath) ?: return false
val backupDB = SQLiteDatabase.openOrCreateDatabase(backup.absolutePath, passphrase, null)
var currentDB = context.getDatabasePath(DATABASE_NAME)

db.close() // currently initialized Room DB

currentDB.delete()

val journalFiles = listOf(
    File(currentDB.path + "-journal"),
    File(currentDB.path + "-wal"),
    File(currentDB.path + "-shm")
)
for (file in journalFiles) {
    if (file.exists()) {
        file.delete()
    }
}

db = Room.databaseBuilder(
    context.applicationContext,
    MyDatabase::class.java,
    DATABASE_NAME
)
    .openHelperFactory(factory)
    .build()

currentDB = context.getDatabasePath(DATABASE_NAME)

backupDB.rawExecSQL("ATTACH DATABASE '${currentDB.absolutePath}' AS target;")
backupDB.rawExecSQL("SELECT sqlcipher_export('target');")
backupDB.rawExecSQL("DETACH DATABASE target;")

Is there anything I am missing? Thanks.