Error in Android Room Migration When Adding a New Column

Question

I'm migrating to a new schema where the table 'folders_table' receives a new column that is designated as the primary key. When I attempt manual migration, it throws an error, stating that it was expecting an 'id' column. However, the point here is that the 'id' column is based on the new schema, set to auto-increment, and designated as the primary key. I am aware of this.

I've applied the following migration to the new schema:

 private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(
                    """
    DROP TABLE IF EXISTS new_folders_table;
    CREATE TABLE IF NOT EXISTS new_folders_table (     
        folderName TEXT NOT NULL,
        infoForSaving TEXT NOT NULL,
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
    );
    INSERT INTO new_folders_table (folderName, infoForSaving)
    SELECT folderName, infoForSaving FROM folders_table;
    DROP TABLE IF EXISTS folders_table;
    ALTER TABLE new_folders_table RENAME TO folders_table;
    """
                )
}

And the previous schema's table looks like:

@Entity(tableName = "folders_table")
data class FoldersTable(
    @PrimaryKey val folderName: String,
    val infoForSaving: String,
)

The new table, which was also introduced in the migration, looks like:

@Entity(tableName = "folders_table")
data class FoldersTable(
    var folderName: String,
    var infoForSaving: String,

    @PrimaryKey(autoGenerate = true)
    var id: Long = 0
)

It should do what I wanted, but it throws the following error:

Expected:
                                                    TableInfo{name='folders_table', columns={infoForSaving=Column{name='infoForSaving', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, folderName=Column{name='folderName', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[], indices=[]}
                                                     Found:
                                                    TableInfo{name='folders_table', columns={folderName=Column{name='folderName', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, infoForSaving=Column{name='infoForSaving', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}

generated SQL statement from java package:

CREATE TABLE IF NOT EXISTS `folders_table` (`folderName` TEXT NOT NULL, `infoForSaving` TEXT NOT NULL, `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

I've gone through similar questions on stack overflow and reddit, i didn't get my answer :(

How can i fix this? Thank you:)

Answer 1

It would appear that you have had issues and have somehow created the new_folders_table as if it were the original table prior to migration.

As such you need to DROP the table otherwise the table will exist and remain as it is. e.g. precede the CREATE with:-

    DROP TABLE IF EXISTS `new_folders_table`;

If you examine the expected message you will see that the folderName and infoForSaving columns both have notNull=true

As such you have to specify the NOT NULL constraint when defining the columns. As such the SQL for the create should be based upon:-

CREATE TABLE IF NOT EXISTS `folders_table` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `folderName` TEXT NOT NULL, `infoForSaving` TEXT NOT NULL)

• BUT WITH THE TABLE NAME CHANGED

The above SQL is EXACTLY according to what Room expects. Not because it was determined, but because it has been copied from the code that Room generates, after compiling.

The generated code can be found by:-

1. Making the changes to the @Entity annotated class(es)
2. Compiling the Project
3. Switch to Android View in Android Studio if not already in Android View
4. locate and expand the java(generated) directory.
5. expand the sub-directories until you locate the class/file that is the same name as the @Database annotated class but suffixed with _Impl and then open the class.
6. Locate the method (function) that is name createALLTables, this method has the SQL that is executed for creating each of the tables (and indexes if any).
   1. the room_master table is a table where room stores a hashed schema that is compared with the compiled hash.

e.g.

Working Demo with suggested code

Using you initial code (as version 1) the database is :-

• i.e. it has been loaded with 3 rows

Now after changing to Version 2 and the with Migration as:-

    val migration_1_to_2 = object : Migration(1,2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("DROP TABLE IF EXISTS new_folders_table")
            database.execSQL("CREATE TABLE IF NOT EXISTS `new_folders_table` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `folderName` TEXT NOT NULL, `infoForSaving` TEXT NOT NULL);")
            database.execSQL("INSERT INTO `new_folders_table` (folderName,infoForSaving) SELECT folderName,infoForSaving FROM folders_table;")
            database.execSQL("DROP TABLE IF EXISTS `folders_table`;")
            database.execSQL("ALTER TABLE `new_folders_table` RENAME TO `folders_table`;")
        }
    } 

Then:-

HOWEVER you have:-

database.execSQL(
                    """
    DROP TABLE IF EXISTS new_folders_table;
    CREATE TABLE IF NOT EXISTS new_folders_table (     
        folderName TEXT NOT NULL,
        infoForSaving TEXT NOT NULL,
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
    );
    INSERT INTO new_folders_table (folderName, infoForSaving)
    SELECT folderName, infoForSaving FROM folders_table;
    DROP TABLE IF EXISTS folders_table;
    ALTER TABLE new_folders_table RENAME TO folders_table;
    """
                )

Which will not work as expected. The execSQL includes the following:-

Execute a single SQL statement that does not return any data.

• (bold added)

As such only the first statement of the 5 attempted will be undertaken. The end result being that the folders_table will not be renamed and thus remain as it was prior to the Migration.

The fix is to use an execSQL per statement, that is 5 (see working demo above).