Unable to migrate Android SQL to Room manually

Question

I'm going crazy trying to figure this out.

Originally I tried doing an auto migration from SQL to Room which "almost" worked. I get the following difference between the old DB and the new one causing auto migrate not to work due to the non null and null differences and ALTER TABLE being unable to convert columns.

private val MIGRATION_1_2 = object : Migration(1, 2) {
  override fun migrate(database: SupportSQLiteDatabase) {
    //Empty since we aren't altering the DB
  }
}

java.lang.IllegalStateException: Migration didn't properly handle:
     Expected:
    TableInfo{name='tea_cabinet', columns={steep_time=Column{name='steep_time', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, amount_per_cup=Column{name='amount_per_cup', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, water_temp=Column{name='water_temp', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, rating=Column{name='rating', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, tea_name=Column{name='tea_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, tea_type=Column{name='tea_type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='tea_cabinet', columns={steep_time=Column{name='steep_time', type='real', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, amount_per_cup=Column{name='amount_per_cup', type='real', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, water_temp=Column{name='water_temp', type='integer', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, rating=Column{name='rating', type='integer', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='integer', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, tea_name=Column{name='tea_name', type='text', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, tea_type=Column{name='tea_type', type='text', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

Main issue: (Thanks to: https://hrankit.github.io/RoomSQLiteDifferenceFinder/)

When I try to do it manually as suggested from various places:

It doesn't even find the SAME table the auto migration finds. It shows it with no columns!

private val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        //Empty since we aren't altering the DB
        
        // Does not work
        //database.execSQL("ALTER TABLE 'tea_cabinet' ADD COLUMN '_id' INTEGER NOT NULL");

        // Create the new table
        database.execSQL(
            "CREATE TABLE IF NOT EXISTS tea_cabinet_tmp (_id INTEGER NOT NULL, tea_name TEXT, tea_type TEXT, water_temp INTEGER NOT NULL, steep_time REAL NOT NULL, amount_per_cup REAL NOT NULL, rating INTEGER NOT NULL, PRIMARY KEY(_id))"
        )

        // Copy the data
        database.execSQL(
            "INSERT INTO tea_cabinet_tmp (_id, tea_name, tea_type ,water_temp, steep_time, amount_per_cup, rating) SELECT _id, tea_name, tea_type ,water_temp, steep_time, amount_per_cup, rating FROM `tea_cabinet`")

        // Remove the old table
        database.execSQL("DROP TABLE tea_cabinet")

        // Change the table name to the correct one
        database.execSQL("ALTER TABLE tea_cabinet_tmp RENAME TO tea")

    }
}

    java.lang.IllegalStateException: Migration didn't properly handle:
     Expected:
    TableInfo{name='tea_cabinet', columns={steep_time=Column{name='steep_time', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, amount_per_cup=Column{name='amount_per_cup', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, water_temp=Column{name='water_temp', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, rating=Column{name='rating', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, tea_name=Column{name='tea_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, tea_type=Column{name='tea_type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='tea_cabinet', columns={}, foreignKeys=[], indices=[]}

How could this possibly be happening? I can see the data properly in the old version and I have the same table names, columns, file.db name and everything on the next version even looking at the Database Inspector. Totally lost.

Well you did `RENAME TO tea`, not `RENAME TO tea_cabinet`. Was that intentional? — ianhanniballake, Feb 05 '22 at 19:10
Yeah, that was intentional to create a brand new Room table just called "tea" instead of tea_cabinet after copying the old data older but it never makes it to that point. I of course just resolved it, will post my solution. — rwarner, Feb 05 '22 at 19:34

rwarner · Answer 1 · 2022-02-05T19:43:51.040

Well what do you know, you post to ask for help and of course you solve your own problem...

I ended up changing the following class from this:

@Entity(tableName = "tea_cabinet")
class Tea : Serializable {

    @PrimaryKey
    @ColumnInfo(name = "_id")
    var iD: Long = 0

    @ColumnInfo(name = "tea_name")
    var teaName: String? = null

    @ColumnInfo(name = "tea_type")
    var typeOfTea: String? = null

    @ColumnInfo(name = "water_temp")
    var waterTemp = 0

    @ColumnInfo(name = "steep_time")
    var steepTime = 0.0

    @ColumnInfo(name = "amount_per_cup")
    var amountPerCup = 0.0

    @ColumnInfo(name = "rating")
    var rating = 0
}

to

@Entity(tableName = "tea_cabinet")
class Tea : Serializable {

    @PrimaryKey
    @ColumnInfo(name = "_id")
    var iD: Long? = 0

    @ColumnInfo(name = "tea_name")
    var teaName: String = ""

    @ColumnInfo(name = "tea_type")
    var typeOfTea: String = ""

    @ColumnInfo(name = "water_temp")
    var waterTemp = 0

    @ColumnInfo(name = "steep_time")
    var steepTime = 0.0

    @ColumnInfo(name = "amount_per_cup")
    var amountPerCup = 0.0

    @ColumnInfo(name = "rating")
    var rating = 0
}

To match the non-null of the previous table's column. From an idea here: https://stackoverflow.com/a/52275534/638220

Then the auto migration worked. Just hesitant if this was the "right" thing to do for those three top variables.

You can let room do the work of getting the EXACT schema simply by creating the entities adding them to the list of entities in the @Database annotated class, compiling and then looking at the generated java (visible via Android View). There will be a class name the same as the `@Database` annotated class in that class there will be a method named `createAllTables`. The EXACT SQL for creating the tables is included in that class from which you can extract how room expects the columns to be defined. — MikeT, Feb 06 '22 at 18:50

Unable to migrate Android SQL to Room manually

1 Answers1