Search code examples
androidandroid-room

Room migration adding column fail

I this class:

data class UserEntity(
@PrimaryKey var uid: String,
var name: String?,
var photo: String?,
var status: Int = 0,
var interaction: Long? = null,
var additional: String? = ""

)

When trying to run migration:

private val MIGRATION_1_2 = object : Migration(1, 2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("ALTER TABLE UserEntity ADD COLUMN additional TEXT NOT NULL DEFAULT ''")
        }
    }

I get:

Caused by: java.lang.IllegalStateException: Migration didn't properly handle: UserEntity(com.michlindevelopment.way.mainapp.database.UserEntity).
                                                                                                 Expected:
                                                                                                TableInfo{name='UserEntity', columns={name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, interaction=Column{name='interaction', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, uid=Column{name='uid', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, photo=Column{name='photo', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, status=Column{name='status', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, additional=Column{name='additional', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}
                                                                                                 Found:
                                                                                                TableInfo{name='UserEntity', columns={uid=Column{name='uid', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, photo=Column{name='photo', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, status=Column{name='status', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, interaction=Column{name='interaction', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, additional=Column{name='additional', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue=''''}}, foreignKeys=[], indices=[]}

There is problem with the order maybe? I expected the name column is first, in found uid is first

Solution

  • The order of the columns is not an issue.

    There are issues with the new column as per the expected is :-

    additional=Column{name='additional', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}

    and the found is

    additional=Column{name='additional', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue=''''

    i.e. Room (according to the @Entity definition) is that:-

    • a) Room expects no NOT NULL i.e. nulls are allowed as per var additional: String? = "" i.e. the ? says that the value is nullable.
    • b) Room expects no DEFAULT, whilst the DEFAULT value is an empty String as per DEFAULT '' and thus defaultValue=''''

    To fix the issues above you should use:-

    ALTER TABLE UserEntity ADD COLUMN additional TEXT
    • note the assumption is that no other changes have been made other than adding this column and thus that other column comparisons between expected and found have not been looked at (also that the recommended way is as described below)

    i.e. remove the NOT NULL and have no DEFAULT and thus the default will be null.

    However, the easier way to avoid issues with Room's expectations is to let Room tell you exactly what it expects, as it creates the SQL used to create the tables whenever you compile the project if the entities parameter of the @Database annotation includes the @Entity annotated class.

    So to get the exact column definition, you:-

    1. compile the project after making the changes.
    2. Using Android View find the java (generated)
    3. Find the class that is the same name as the @Database annotated class but suffixed with _Impl.
    4. Find the method/function on the class that is named createAllTables.
    5. The SQL for the creation of the table and thus the column definitions will be within the method.

    Note if you need a DEFAULT value then you need to specify this in the defaultValue parameter of the @ColumnInfo annotation (otherwise the expected but found issue). Furthermore if the column has the NOT NULL constraint then a default value is required as it cannot be null as per

    If a NOT NULL constraint is specified, then the column must have a default value other than NULL.