Data is lost after successful room migration when using SQLCipher

SQLCipher
1

Hi,

@Database(
    entities = [DLDataItem::class],
    version = 9,
    autoMigrations = [
        AutoMigration(from = 4, to = 5),
        AutoMigration(from = 5, to = 6, spec = MIGRATION_5_6::class),
        AutoMigration(from = 6, to = 7),
        AutoMigration(from = 7, to = 8),
        AutoMigration(from = 8, to = 9, spec = DLRoomMigrations.MIGRATION_8_9::class),
    ]
)
@TypeConverters(DLConverter::class)
abstract class DLDatabase : RoomDatabase() {
    companion object {
        private const val DATABASE_NAME = "Database.db"
        
        @Volatile
        private var INSTANCE: DLDatabase? = null

        fun getInstance(context: Context, encryptionKey: ByteArray?): DLDatabase {
            System.loadLibrary( "sqlcipher")
            val factory = SupportOpenHelperFactory(encryptionKey)
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    DLDatabase::class.java,
                    DATABASE_NAME
                )
                    .openHelperFactory(factory)
                    .addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4)
                    .build()
                INSTANCE = instance
                instance
            }
        }
    }

The above is the database logics I am using. When there is schema changes in the db table and if we add room migration for it, let’s say from version 8 to version 9, after app update the migration succeeds but the existing old records in the database is wiped from all the tables.

May be I am missing something between room migration and SQLCipher integration. Also I couldn’t find a proper reference to handle room migrations with SQLCipher.

We are facing this issue in production code. So could anyone please help on this?

2

Hi @Dhana,

Do you have a small reproduction application that showcases what you are seeing? A few other questions come to mind about your scenario:

  • Is the database already encrypted with SQLCipher?
  • What version of SQLCipher was used to create the database?
  • What version of sqlcipher-android are you using?
  • What are the migrations prior to 8-9 doing?
  • What is occurring within migration 8-9?
  • Do any errors/exceptions occur when running any migrations?
  • Are you able to reproduce the data loss locally?
  • Is data gone from all tables, or specific tables?
  • Do all tables still exist?
1 Like
3

Hi @developernotes,

A reproducible app would not help because I could not see any crash or errors. After updating the app to the latest version which contains DB migration, the data in the db table is cleared.

  • Is the database already encrypted with SQLCipher?
    Yes, it is already encrypted.

  • What version of SQLCipher was used to create the database?
    The version I am using is “net.zetetic:sqlcipher-android:4.5.4@aar” and ‘androidx.sqlite:sqlite:2.2.0’

  • What version of sqlcipher-android are you using?
    The version I am using is “net.zetetic:sqlcipher-android:4.5.4@aar”

  • What are the migrations prior to 8-9 doing?
    The prior migration include addition of columns.

  • What is occurring within migration 8-9?
    Here the migration 8-9 is posted just for example in real case the latest migration was an room auto migration which adds a new column to one of the tables.

  • Do any errors/exceptions occur when running any migrations?
    No error or exceptions occured.

  • Are you able to reproduce the data loss locally?
    Yes, it is reproduced each time when the migration runs.

  • Is data gone from all tables, or specific tables?
    Data is gone from all tables.

  • Do all tables still exist?
    This question is not clear.

4

Hi @Dhana,

A small, isolated reproduction example would actually be best as it would allow us to verify whether the issue is directly related to the library vs. a behavior triggered by the application. The lack of an error message is not relevant in this particular case. Additionally, if the library is at fault, we can look at migrating the scenario into a unit test within the existing suite to prevent a regression in the future and to verify our fix correctly addresses the issue.

I was attempting to clarify that the schema itself was still present in your situation.

Again, if you can provide a small reproduction application that is publicly available (e.g., GitHub) we’d be happy to look into this further. If you are unable to reproduce the behavior within an isolated application, I would suggest further review of what the application is doing.

5

@developernotes I could reproduce the same issue in a sample app.

https://github.com/dhana-prakash-m/SqlCipherSample - This is the sample project I’ve created to reproduce the issue.

To reproduce the issue, kindly follow the below steps:

  1. Checkout the ‘master’ branch and install the app. This branch contains app with db schema version 1 with the initial setup. On installing this app you could see three items displayed representing the details of any jobs. These 3 items are saved in the db using room.

  2. Now check out the branch ‘migrate-db-schema-1-to-2’. This branch includes schema changes in the existing Job table and we are adding a room auto migration for the schema changes from version 1 to 2 to add a new column(venue). Please install this version of app over the previously installed app.

  3. The expected functionality is, the old data saved in the Job table in the first version of the app should be persisted after the app update to the latest version which includes a db migration. But the actual behaviour is the old data saved in the db is cleared after the old app is updated with the existing version.

6

Hi @Dhana,

Thanks for this, once we get the opportunity to review this we will reach out with further comments.

7

Hi @developernotes,

If possible could you please share the things to be considered or the code implementation steps for room migration when using SQLCipher? Because until you analyse the sample code, I will check if any issues with my code.

For your kind information, we are facing this issue in production app and this issue brings lots of trouble.

8

Hi @developernotes,

implementation ‘net.zetetic:sqlcipher-android:4.5.4@aar’
implementation ‘androidx.sqlite:sqlite:2.2.0’

I was using the above dependencies in which the issues which I had mentioned occured.

Upgrading the dependencies to the below fixed the issue,

implementation ‘net.zetetic:sqlcipher-android:4.5.5@aar’
implementation ‘androidx.sqlite:sqlite:2.2.0’

@developernotes Room Migration is a usual thing that many of the offline first apps do. Also the above mentioned issue occurred in a straight forward case. This issue is a major one and it is not addressed anywhere.

9

Hello @Dhana,

Thank you for preparing the demo, that was helpful. We believe the issue you are seeing is due to the issue that was reported and fixed here in the 4.5.5 release.