Android SqlCipher Room Migration failed

SQLCipher Issues
1

Hi, we are just switching our db to sqlcipher but found that foreign key update failed during migration.

We were using Android Room so we followed the guide here: How to apply SQLCipher to pre-existing Room database?
and encrypt our db using SQLCipherUtils.java provided by the link. Everything seems working fine until migration.

In our migration, we have a tableA which has a foreign key point to tableB, and for some reason we need to recreate tableB so we do something like:

private val MIGRATION_2_3: Migration = object : Migration(2, 3) {
override fun migrate(database: SupportSQLiteDatabase) {
database.execSQL(“PRAGMA foreign_keys=OFF;”)
database.execSQL(“CREATE TABLE TABLEB_NEW (id INTEGER primary key autoincrement NOT NULL, name TEXT NOT NULL)”)
database.execSQL(“INSERT INTO TABLEB_NEW SELECT id, name FROM TABLEB;”)
database.execSQL(“ALTER TABLE TABLEB RENAME TO _OLD_TABLE”)
database.execSQL(“ALTER TABLE TABLEB_NEW RENAME TO TABLEB”)
database.execSQL(“DROP TABLE _OLD_TABLE”)
database.execSQL(“PRAGMA foreign_keys=ON;”)
}
}

but compiler throws exception says TableA’s foreign key is still pointing to “_OLD_TABLE” which should have been updated to the new TABLEB.

the “PRAGMA foreign_key” command was working fine with the normal Room migration until I have encrypted the db. Any one got any clue how to fix this? Thanks

sqlcipher version:
implementation “net.zetetic:android-database-sqlcipher:4.4.0”

2

Hi @jay

Is this behavior something you are able to easily reproduce within the SQLCipher for Android test suite? There is a separate package of tests which provide support for running tests with Room. We would be happy to review a reproduction case if you can provide that. Thanks

3

Hi @developernotes , it seems pretty hard to reproduce with the test suite so I have created a sample project for you to test.

Steps to reproduce:

  1. Checkout the repo and switch to master branch.
  2. run the project and install it to a device/emulator for DB version 1
  3. Confirm unencrypted DB created(can check with Database inspector)
  4. Switch to main branch and run the project again
  5. app crash during migration for foreign key not updated.

Temporarily workaround:
In class WordRoomDatabase.kt, uncomment line 139-158 to recreate tables that with foreignkey reference to other tables and go through the above steps again.

Please let me know if it doesn’t work. Cheers.

4

Hi @jay

Did this behavior work for you prior to using SQLCipher, but while still using Room? From what I can tell, Room is starting a transaction prior to the call into migrate, thus you aren’t able to disable the foreign key constraints as you are. For example, if you call the following:

database.setForeignKeyConstraintsEnabled(false)

you will see that an exception is thrown due to a transaction already existing.

5

Hi @developernotes

It did work prior to using SQLCipher with Room, with these magic commands in my migration code:
database.execSQL(“PRAGMA foreign_keys=OFF;”)
database.execSQL(“PRAGMA foreign_keys=ON;”)

You can try it by commenting out the sqlcipher encryption code in WordRoomDatabase class on main branch and just let it run as a normal unencrypted room database, the foreignkeys should be updated normally.

6

Hi @jay

According to the SQLite documentation here, you should be executing the drop first, then the rename:

database.execSQL("DROP TABLE " + TABLE_COOLERS_CACHE)
database.execSQL("ALTER TABLE  " + TABLE_COOLERS_CACHE + "_NEW"
                        + " RENAME TO " + TABLE_COOLERS_CACHE)

When you adjust as above, the migration completes successfully with SQLCipher.