Room Database Migration didn’t properly handle : Caused by: java.lang.IllegalStateException:

Caused by: java.lang.IllegalStateException: Migration didn’t properly handle: Expected
TableInfo{name=’project’, columns={venueName=Column{name=’venueName’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, nfcMerchantId=Column{name=’nfcMerchantId’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, nfcPrivateKey=Column{name=’nfcPrivateKey’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, endDate=Column{name=’endDate’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, scheduledAnonymisationDate=Column{name=’scheduledAnonymisationDate’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, nfcProfileId=Column{name=’nfcProfileId’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, name=Column{name=’name’, type=’TEXT’, affinity=’2′, notNull=true, primaryKeyPosition=0, defaultValue=’null’}, timeZone=Column{name=’timeZone’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, id=Column{name=’id’, type=’TEXT’, affinity=’2′, notNull=true, primaryKeyPosition=1, defaultValue=’null’}, uuid=Column{name=’uuid’, type=’TEXT’, affinity=’2′, notNull=true, primaryKeyPosition=0, defaultValue=’null’}, nfcCollectorId=Column{name=’nfcCollectorId’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, startDate=Column{name=’startDate’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}}, foreignKeys=[], indices=[Index{name=’index_project_uuid’, unique=false, columns=[uuid], orders=[ASC]}]}

Found
TableInfo{name=’project’, columns={venueName=Column{name=’venueName’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, endDate=Column{name=’endDate’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, scheduledAnonymisationDate=Column{name=’scheduledAnonymisationDate’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, nfcProfileId=Column{name=’nfcProfileId’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, name=Column{name=’name’, type=’TEXT’, affinity=’2′, notNull=true, primaryKeyPosition=0, defaultValue=’null’}, timeZone=Column{name=’timeZone’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}, id=Column{name=’id’, type=’TEXT’, affinity=’2′, notNull=true, primaryKeyPosition=1, defaultValue=’null’}, uuid=Column{name=’uuid’, type=’TEXT’, affinity=’2′, notNull=true, primaryKeyPosition=0, defaultValue=’null’}, startDate=Column{name=’startDate’, type=’TEXT’, affinity=’2′, notNull=false, primaryKeyPosition=0, defaultValue=’null’}}, foreignKeys=[], indices=[Index{name=’index_project_uuid’, unique=false, columns=[uuid], orders=[ASC]}]}

I have upgraded my app on top of the previous version and I got this error when I open the new upgraded version of my app. I have added migration class and added the newly added fields and also I have increased the database version. This is my entity class.

@Entity(
    tableName = "project",
    indices = [Index("uuid")]
)
data class ProjectEntity(
    @PrimaryKey
    val id: String,
    val uuid: String,
    val name: String,
    val startDate: String?,
    val endDate: String?,
    val venueName: String?,
    val timeZone: String?,
    val scheduledAnonymisationDate: String?,
    val nfcCollectorId: String?,
    val nfcMerchantId: String?,
    val nfcPrivateKey: String?,
    val nfcProfileId: String?
)

This is my database class.

@Database(entities = {ProjectEntity.class}, version = 23)
public abstract class RoomDatabaseImpl extends RoomDatabase {
    abstract fun projectDao(): ProjectDao
}

This is my migration class.

internal object Migration18To19 : Migration(18, 19) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcCollectorId` TEXT")
    database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcMerchantId` TEXT")
    database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcPrivateKey` TEXT")
    database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcProfileId` TEXT")
  }
}

and my current database version is 23.

I have also made few other changes in the other tables and added migration class for all that and incremented the database version accordingly. My current latest migration class is Migration22To23 and version is 23. I am not really sure why I am getting this error. I have also tried debugging logs and got the below details.

45 {
 type=table
 name=project
 tbl_name=project
 rootpage=49
 sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcCollectorId` TEXT, `nfcMerchantId` TEXT, `nfcPrivateKey` TEXT, `nfcProfileId` TEXT, PRIMARY KEY(`id`))
 }
46 {
 type=index
 name=sqlite_autoindex_project_1
 tbl_name=project
 rootpage=50
 sql=null
 }
47 {
 type=index
 name=index_project_uuid
 tbl_name=project
 rootpage=52
 sql=CREATE INDEX `index_project_uuid` ON `project` (`uuid`)
 }

It seems all the columns are added in 46 migration. But I am not sure why it is not working. I have also found one more information. We initially made nfcCollectorId,nfcMerchantId,nfcPrivateKey,nfcProfileId as not nullable and in between we have changed that as nullable in ProjectEntity. I am not sure that is causing the issue.

Considering that you have picked one Migration (18-19) from many and that that Migration should not be relevant as the version is supposedly moving from 22-23.

There could be numerous causes.

Certainly from the evidence, that:-

nfcCollectorId
nfcMerchantId
nfcPrivateKey

are Expected but not Found, it would be the 18-19 Migration that is required. However, 18-19 would fail as the nfcProfileId column has been found unless there is a Migration that Drops the column.

Could someone help me to solve this issue?

Perhaps the following could be of use

You need to get some understanding of what is going on, from what version to what version and whether or not all migrations are being processed and what is changing when those migrations are invoked.

I would suggest considering the following code that enables such debugging information to be extracted (to replace/amend RoomDatabaseImpl):-

const val DB_VERSION = 3 /* being a constant allows the value to be used e.g. in preOpenSchemaDump */
const val DB_NAME = "room_database_impl.db" /* similar */
const val TAG="DBINFO" /* tag so all debug messages (bar schema dumps) can be easily located */
@Database(entities = [ProjectEntity::class], version = DB_VERSION)
abstract class RoomDatabaseImpl: RoomDatabase() {
    abstract fun projectDao(): ProjectDao

    companion object {
        private var instance: RoomDatabaseImpl?=null
        fun getInstance(context: Context): RoomDatabaseImpl {
            if (instance==null) {
                preOpenSchemaDump(context)
                instance= Room.databaseBuilder(context,RoomDatabaseImpl::class.java, DB_NAME)
                    .allowMainThreadQueries() /* For brevity */
                    .addCallback(CB) /* added for debugging */
                    .addMigrations(Migration1To2,Migration2To3)
                    .build()
            }
            return instance as RoomDatabaseImpl
        }

        fun preOpenSchemaDump(context: Context) {
            if (context.getDatabasePath(DB_NAME).exists()) {
                val db = SQLiteDatabase.openDatabase(
                    context.getDatabasePath(DB_NAME).path,
                    null,
                    SQLiteDatabase.OPEN_READWRITE
                )
                Log.d(TAG,"Database Version is ${db.version}: Room version will be ${DB_VERSION}" )
                dumpSchema("PreOpen invoked", db)
                db.close()
            } else {
                Log.d(TAG,"PreOpen Invoked but Database does not exist yet (nothing to dump so skipped)")
            }
        }

        internal object Migration2To3 : Migration(2, 3) {
            override fun migrate(database: SupportSQLiteDatabase) {
                dumpSchema("Before Migration 2 to 3 invoked", database)
                database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcCollectorId` TEXT")
                database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcMerchantId` TEXT")
                database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcPrivateKey` TEXT")
                //database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcProfileId` TEXT") /* commented out as would fail */
                dumpSchema("Post Migration 2 to 3 invoked", database)
            }
        }

        internal object Migration1To2 : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                dumpSchema("Before Migration 1 to 2 invoked", database)
                database.execSQL("ALTER TABLE `project` ADD COLUMN `nfcProfileId` TEXT")
                dumpSchema("Post Migration 1 to 2 invoked", database)
            }
        }
        /* CallBack for logging purposes */
        internal object CB: Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                dumpSchema("OnCreate Callback initiated",db)
            }

            override fun onOpen(db: SupportSQLiteDatabase) {
                super.onOpen(db)
                dumpSchema("OnOpen Callback initiated",db)
            }

            override fun onDestructiveMigration(db: SupportSQLiteDatabase) {
                super.onDestructiveMigration(db)
                dumpSchema("OnDestructiveMigration Callback initiated",db)
            }
        }
        private fun dumpSchema(reason: String, db: SupportSQLiteDatabase, suppressDump: Boolean=false) {
            Log.d(TAG,"$reason - dumping schema to log:-")
            if (!suppressDump) {
                val csr = db.query("SELECT * FROM sqlite_master")
                DatabaseUtils.dumpCursor(csr)
                csr.close()
            }
        }
        private fun dumpSchema(reason: String, db: SQLiteDatabase, suppressDump: Boolean=false) {
            Log.d(TAG,"$reason - dumping schema to log:-")
            if (!suppressDump) {
                val csr = db.rawQuery("SELECT * FROM sqlite_master", null)
                DatabaseUtils.dumpCursor(csr)
                csr.close()
            }
        }
    }

the migrations in the above are only like what you may have and the versions have obviously been changed to facilitate testing of the code.

As an example.

Initially DB_VERSION is 1 and all the nfc…. columns have been commented out and a fresh install is performed. The log includes:-

2024-04-24 14:12:16.466 D/DBINFO: PreOpen Invoked but Database does not exist yet (nothing to dump so skipped)
2024-04-24 14:12:16.533 D/DBINFO: OnCreate Callback initiated - dumping schema to log:-
2024-04-24 14:12:16.533 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@68ac7e0
2024-04-24 14:12:16.534 I/System.out: 0 {
2024-04-24 14:12:16.534 I/System.out:    type=table
2024-04-24 14:12:16.534 I/System.out:    name=android_metadata
2024-04-24 14:12:16.534 I/System.out:    tbl_name=android_metadata
2024-04-24 14:12:16.534 I/System.out:    rootpage=3
2024-04-24 14:12:16.534 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2024-04-24 14:12:16.534 I/System.out: }
2024-04-24 14:12:16.534 I/System.out: 1 {
2024-04-24 14:12:16.534 I/System.out:    type=table
2024-04-24 14:12:16.534 I/System.out:    name=project
2024-04-24 14:12:16.534 I/System.out:    tbl_name=project
2024-04-24 14:12:16.534 I/System.out:    rootpage=4
2024-04-24 14:12:16.534 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, PRIMARY KEY(`id`))
2024-04-24 14:12:16.535 I/System.out: }
....
2024-04-24 14:12:16.544 D/DBINFO: OnOpen Callback initiated - dumping schema to log:-
2024-04-24 14:12:16.547 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@a4bf93f
....

i.e. Database doesn’t exist (fresh install)
onCreate has been called
onOpen has been called.

Then the nfc columns are uncommented and DB_VERSION set to 3 and the App rerun, the log then includes:-

2024-04-24 14:18:13.256 D/DBINFO: Database Version is 1: Room version will be 3
2024-04-24 14:18:13.256 D/DBINFO: PreOpen invoked - dumping schema to log:-
2024-04-24 14:18:13.257 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@aa6612
....
2024-04-24 14:18:13.258 I/System.out: 1 {
2024-04-24 14:18:13.258 I/System.out:    type=table
2024-04-24 14:18:13.258 I/System.out:    name=project
2024-04-24 14:18:13.258 I/System.out:    tbl_name=project
2024-04-24 14:18:13.258 I/System.out:    rootpage=4
2024-04-24 14:18:13.258 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, PRIMARY KEY(`id`))
....
2024-04-24 14:18:13.322 D/DBINFO: Before Migration 1 to 2 invoked - dumping schema to log:-
2024-04-24 14:18:13.323 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@a4bf93f
....
2024-04-24 14:18:13.323 I/System.out: 1 {
2024-04-24 14:18:13.323 I/System.out:    type=table
2024-04-24 14:18:13.323 I/System.out:    name=project
2024-04-24 14:18:13.323 I/System.out:    tbl_name=project
2024-04-24 14:18:13.323 I/System.out:    rootpage=4
2024-04-24 14:18:13.323 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, PRIMARY KEY(`id`))
....
2024-04-24 14:18:13.334 D/DBINFO: Post Migration 2 to 3 invoked - dumping schema to log:-

2024-04-24 14:18:13.330 D/DBINFO: Before Migration 2 to 3 invoked - dumping schema to log:-
2024-04-24 14:18:13.330 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8d33755
....
2024-04-24 14:18:13.331 I/System.out: 1 {
2024-04-24 14:18:13.331 I/System.out:    type=table
2024-04-24 14:18:13.331 I/System.out:    name=project
2024-04-24 14:18:13.331 I/System.out:    tbl_name=project
2024-04-24 14:18:13.331 I/System.out:    rootpage=4
2024-04-24 14:18:13.331 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcProfileId` TEXT, PRIMARY KEY(`id`))
....
2024-04-24 14:18:13.334 D/DBINFO: Post Migration 2 to 3 invoked - dumping schema to log:-
2024-04-24 14:18:13.334 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@d88a56a
....
2024-04-24 14:18:13.335 I/System.out: 1 {
2024-04-24 14:18:13.335 I/System.out:    type=table
2024-04-24 14:18:13.335 I/System.out:    name=project
2024-04-24 14:18:13.336 I/System.out:    tbl_name=project
2024-04-24 14:18:13.336 I/System.out:    rootpage=4
2024-04-24 14:18:13.336 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcProfileId` TEXT, `nfcCollectorId` TEXT, `nfcMerchantId` TEXT, `nfcPrivateKey` TEXT, PRIMARY KEY(`id`))

as can be seen the database Version was 1 and the Room version 3
that the migrations have been progressively actioned.

Now if a deliberate issue is introduced (in 1-2) e.g.

database.execSQL(“ALTER TABLE project ADD COLUMN nfcOOOPS TEXT”)

And then version 1 is installed followed by version 3 as done previously then

1 installs fine

But 3 gets the Expected not Found but not until after both migrations as per the following extracts from the log:-

2024-04-24 14:34:12.289 D/DBINFO: Database Version is 1: Room version will be 3
2024-04-24 14:34:12.289 D/DBINFO: PreOpen invoked - dumping schema to log:-
2024-04-24 14:34:12.289 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@aa6612
....
2024-04-24 14:34:12.290 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, PRIMARY KEY(`id`))
....
2024-04-24 14:34:12.348 D/DBINFO: Before Migration 1 to 2 invoked - dumping schema to log:-
2024-04-24 14:34:12.349 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@a4bf93f
....
2024-04-24 14:34:12.353 D/DBINFO: Post Migration 1 to 2 invoked - dumping schema to log:-
2024-04-24 14:34:12.354 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@e04820c
....
2024-04-24 14:34:12.355 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcOOOPS` TEXT, PRIMARY KEY(`id`))
....
2024-04-24 14:34:12.356 D/DBINFO: Before Migration 2 to 3 invoked - dumping schema to log:-
2024-04-24 14:34:12.357 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8d33755
....
2024-04-24 14:34:12.360 D/DBINFO: Post Migration 2 to 3 invoked - dumping schema to log:-
2024-04-24 14:34:12.360 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@d88a56a
....
2024-04-24 14:34:12.361 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcOOOPS` TEXT, `nfcCollectorId` TEXT, `nfcMerchantId` TEXT, `nfcPrivateKey` TEXT, PRIMARY KEY(`id`))
....
2024-04-24 14:34:12.947 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so78373886multiplemigrations, PID: 16141
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so78373886multiplemigrations/a.a.so78373886multiplemigrations.MainActivity}: java.lang.IllegalStateException: Migration didn't properly handle: project(a.a.so78373886multiplemigrations.ProjectEntity).
     Expected:
    TableInfo{name='project', columns={venueName=Column{name='venueName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcMerchantId=Column{name='nfcMerchantId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcPrivateKey=Column{name='nfcPrivateKey', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, endDate=Column{name='endDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, scheduledAnonymisationDate=Column{name='scheduledAnonymisationDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcProfileId=Column{name='nfcProfileId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, timeZone=Column{name='timeZone', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, id=Column{name='id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, uuid=Column{name='uuid', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, nfcCollectorId=Column{name='nfcCollectorId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, startDate=Column{name='startDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[Index{name='index_project_uuid', unique=false, columns=[uuid], orders=[ASC]'}]}
     Found:
    TableInfo{name='project', columns={id=Column{name='id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, uuid=Column{name='uuid', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, startDate=Column{name='startDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, endDate=Column{name='endDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, venueName=Column{name='venueName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, timeZone=Column{name='timeZone', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, scheduledAnonymisationDate=Column{name='scheduledAnonymisationDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcOOOPS=Column{name='nfcOOOPS', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcCollectorId=Column{name='nfcCollectorId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcMerchantId=Column{name='nfcMerchantId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, nfcPrivateKey=Column{name='nfcPrivateKey', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[Index{name='index_project_uuid', unique=false, columns=[uuid], orders=[ASC]'}]}

as can be seen inadvertently changing an existing required Migration could be an issue
the debugging could help to make finding whatever the issue is easier.

Rather than adding to the initial “how to debug answer”. This supplementary answer hopes to address some comments.

First, the comment

Could you please check and help me what is the issue?

The bad news not really as the states of the schemas are unknown.

However, the comment

I have noticed that these nfcCollectorId,nfcMerchantId,nfcPrivateKey,nfcProfileId was non nullable during the initial migration and in between it has changed to nullbale in ProjectEntity. Is it causing an issue? If that is the reason then how can I solve the issue?

can be addressed.

In short YES such a change will have issues and require Migration.

Room most certainly will have an issue between a field that is changed from non-nullable to nullable (or vice-versa).

If a field is non-nullable e.g. val x: String then the column will be defined with the NOT NULL constraint. If the fields is nullable e.g. val x: String? then the column will not have the NOT NULL constraint.

So:-

....
val nfcCollectorId: String,
val nfcMerchantId: String,
val nfcPrivateKey: String,
val nfcProfileId: String
....

would result in Room building the create SQL to include:-

.... `nfcCollectorId` TEXT NOT NULL, `nfcMerchantId` TEXT NOT NULL, `nfcPrivateKey` TEXT NOT NULL, `nfcProfileId` TEXT NOT NULL ....

Whilst:-

....
val nfcCollectorId: String?,
val nfcMerchantId: String?,
val nfcPrivateKey: String?,
val nfcProfileId: String?
....

would result in the create SQL being:-

.... `nfcCollectorId` TEXT, `nfcMerchantId` TEXT, `nfcPrivateKey` TEXT, `nfcProfileId` TEXT ....

Room expects (demands) the schema to be to exact (in this regard). As such a change to nullable from non-nullable will need a Migration if the data in the database is to be retained.

exact but not necessarily the whole schema, You could for example, add a non Room table and Room would not care. But for @Entity annotated classes that are included as entities in the @Database annotation the schema/definition must match.

This is a little involved. If you were to drop a column to then add the column with a different constraint (no constraint to NOT NULL) then the data would be lost.

As such it is probably easiest to rename the entire table in question, to then create the table with the new/replacement schema and to then populate the new table using the data in the renamed table.

However, it may not be as simple as that and in fact indices = [Index("uuid")] introduces a complication.

Renaming the original table will result in the association between the index and the table being changed BUT the index will still have the same name. So that index needs to be dropped and to suit Room’s expectations the index will have to be recreated.

Example/Demo (based upon the nfc? columns being changed from non-nullable to nullable)

In regard to what Room expects, this is relatively easy to ascertain as Room builds the SQL that is used to create the components (tables, indexes, views). This SQL is in the generated(jav) visible via the Android View within Android Studio e.g. (after the change to nullable):-

enter image description here

With the above then the Migration can be coded (in the example version 3 to 4). This will:-

Just in case it exists drop the table that the original will be renamed to
Rename the original table
Create the new changed table according to the SQL copied from the generated SQL (so it will be exactly what Room expects)
Drop the index on the uuid column
Copy the data from the original to the new/replacement table
Drop the renamed original table
Create the index on the uuid column using the SQL copied from the generated SQL (again exactly as Room expects)

This would look something like:-

    /* Existing Columns nfcCollectorId, nfcMerchantId, nfcPrivateKey and nfcProfileId being changed from
        NOT NULL to nullable.
    */
    internal object Migration3To4 : Migration(3, 4) {
        override fun migrate(database: SupportSQLiteDatabase) {
            val original_suffix = "_original"
            dumpSchema("Before Migration 3 to 4 invoked", database)
            /* just in case drop the table that the original table will be renamed to */
            database.execSQL("DROP TABLE IF EXISTS `project${original_suffix}`;")
            database.execSQL("ALTER TABLE `project` RENAME TO  `project${original_suffix}`")
            /* AFTER COMPILING PROJECT copy CREATE TABLE SQL FROM java(generated) i.e. RoomDatabaseImpl_Impl */
            database.execSQL("CREATE TABLE IF NOT EXISTS `project` (" +
                    "`id` TEXT NOT NULL, " +
                    "`uuid` TEXT NOT NULL, " +
                    "`name` TEXT NOT NULL, " +
                    "`startDate` TEXT, " +
                    "`endDate` TEXT, " +
                    "`venueName` TEXT, " +
                    "`timeZone` TEXT, " +
                    "`scheduledAnonymisationDate` TEXT, " +
                    "`nfcCollectorId` TEXT, " +
                    "`nfcMerchantId` TEXT, " +
                    "`nfcPrivateKey` TEXT, " +
                    "`nfcProfileId` TEXT, " +
                    "PRIMARY KEY(`id`));")
            /* DROP the renamed original table's index */
            database.execSQL("DROP INDEX IF EXISTS `index_project_uuid`;")
            /* note that ALL  columns names have been coded to ENSURE that like for like (column wise) data is copied */
            /* rather than the simpler INSERT INTO `project` SELECT * FROM `project_original`; */

            database.execSQL("INSERT INTO `project` (id,uuid,name,startDate,endDate,venueName,timeZone,scheduledAnonymisationDate,nfcCollectorId,nfcMerchantId,nfcPrivateKey,nfcProfileId) " +
                    " SELECT id,uuid,name,startDate,endDate,venueName,timeZone,scheduledAnonymisationDate,nfcCollectorId,nfcMerchantId,nfcPrivateKey,nfcProfileId FROM `project${original_suffix}`;")

            /* NOT USED database.execSQL("INSERT INTO `project` SELECT * FROM `project${original_suffix}`") */
            /* Optional DROP the renamed original table */
            database.execSQL("DROP TABLE IF EXISTS `project${original_suffix}`;")
            /* create the index (more efficient to do after data has been loaded) SQL ALSO copied from generated java */
            database.execSQL("CREATE INDEX IF NOT EXISTS `index_project_uuid` ON `project` (`uuid`)")
            /* Optional DROP the renamed original table */
            dumpSchema("Post Migration 3 to 4 invoked", database)
        }
    }

the comments are intended to be helpful
Note that the INSERT …. SELECT defines the columns explicitly, this ensures that the column order does not impact. The simpler INSERT INTO project SELECT * FROM project_original would likely work but, is potentially unsafe data-wise.

As the debugging code was kept then actually running the above, when the version was 3 (with non-nullable nfc? columns) to version 4 (with nullable nfc? columns) then the log includes:-

2024-04-25 07:48:49.016 D/DBINFO: Database Version is 3: Room version will be 4
2024-04-25 07:48:49.016 D/DBINFO: PreOpen invoked - dumping schema to log:-
2024-04-25 07:48:49.016 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@aa6612
....
2024-04-25 07:48:49.017 I/System.out: 1 {
2024-04-25 07:48:49.017 I/System.out:    type=table
2024-04-25 07:48:49.017 I/System.out:    name=project
2024-04-25 07:48:49.017 I/System.out:    tbl_name=project
2024-04-25 07:48:49.017 I/System.out:    rootpage=4
2024-04-25 07:48:49.017 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcCollectorId` TEXT NOT NULL, `nfcMerchantId` TEXT NOT NULL, `nfcPrivateKey` TEXT NOT NULL, `nfcProfileId` TEXT NOT NULL, PRIMARY KEY(`id`))
2024-04-25 07:48:49.017 I/System.out: }
....
....
2024-04-25 07:48:49.077 D/DBINFO: Before Migration 3 to 4 invoked - dumping schema to log:-
2024-04-25 07:48:49.077 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@a4bf93f
2024-04-25 07:48:49.078 I/System.out: 1 {
2024-04-25 07:48:49.078 I/System.out:    type=table
2024-04-25 07:48:49.078 I/System.out:    name=project
2024-04-25 07:48:49.078 I/System.out:    tbl_name=project
2024-04-25 07:48:49.078 I/System.out:    rootpage=4
2024-04-25 07:48:49.078 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcCollectorId` TEXT NOT NULL, `nfcMerchantId` TEXT NOT NULL, `nfcPrivateKey` TEXT NOT NULL, `nfcProfileId` TEXT NOT NULL, PRIMARY KEY(`id`))
2024-04-25 07:48:49.079 I/System.out: }
....
....
2024-04-25 07:48:49.086 D/DBINFO: Post Migration 3 to 4 invoked - dumping schema to log:-
2024-04-25 07:48:49.087 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@e04820c
....
2024-04-25 07:48:49.088 I/System.out: 2 {
2024-04-25 07:48:49.088 I/System.out:    type=table
2024-04-25 07:48:49.088 I/System.out:    name=project
2024-04-25 07:48:49.088 I/System.out:    tbl_name=project
2024-04-25 07:48:49.088 I/System.out:    rootpage=5
2024-04-25 07:48:49.088 I/System.out:    sql=CREATE TABLE `project` (`id` TEXT NOT NULL, `uuid` TEXT NOT NULL, `name` TEXT NOT NULL, `startDate` TEXT, `endDate` TEXT, `venueName` TEXT, `timeZone` TEXT, `scheduledAnonymisationDate` TEXT, `nfcCollectorId` TEXT, `nfcMerchantId` TEXT, `nfcPrivateKey` TEXT, `nfcProfileId` TEXT, PRIMARY KEY(`id`))
2024-04-25 07:48:49.088 I/System.out: }
....
....
2024-04-25 07:48:49.144 D/DBINFO: OnOpen Callback initiated - dumping schema to log:-
....

Filed under: Kiến thức lập trình - @ 00:11

Thẻ: androidkotlinsqliteandroid-room

Thiết kế website giá rẻ

Danh mục

Room Database Migration didn’t properly handle : Caused by: java.lang.IllegalStateException: