Room auto migration no such column: column_name (code 1 SQLITE_ERROR): , while compiling: INSERT INTO

Question

I try to update my Room entity by adding new field:

version 1:

@Entity(tableName = TABLE_NAME)
data class AvailableAccountRoom(
    @PrimaryKey
    val id: String = "",
    [...]
)

version 2:

@Entity(tableName = TABLE_NAME)
data class AvailableAccountRoom(
    @PrimaryKey
    val id: String = "",
    [...]
    val isExchangeSupported: Boolean,
)

I want to use auto migrations so I also add this line to my db:

autoMigrations = [
   AutoMigration(from = 1, to = 2)
]

But I have runtime crash with next message:

(1) no such column: isExchangeSupported in "INSERT INTO `_new_table_name` (`id`,`isExchangeSupported`
(1) no such column: isExchangeSupported in "INSERT INTO `_new_table_name` (`id`,`isExchangeSupported`
FATAL EXCEPTION: OkHttp Dispatcher
                 Process: ...
                 android.database.sqlite.SQLiteException: no such column: isExchangeSupported (code 1 SQLITE_ERROR): , while compiling: INSERT INTO
                 `_new_table_name` (`id`,`isExchangeSupported`) SELECT
                 `id`,`isExchangeSupported` FROM
                 `table_name `
                    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1068)
                    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:673)
                    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
                    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:62)
                    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:34)
                    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2086)
                    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2008)
                    at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.kt:246)
                    at com.###.AppDatabase_AutoMigration_1_2_Impl.migrate(AppDatabase_AutoMigration_1_2_Impl.java:18)
                    at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.kt:91)
                    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.kt:253)
                    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:416)
                    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:316)
                    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableOrReadableDatabase(FrameworkSQLiteOpenHelper.kt:232)
                    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.innerGetDatabase(FrameworkSQLiteOpenHelper.kt:190)
                    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getSupportDatabase(FrameworkSQLiteOpenHelper.kt:151)
                    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.kt:104)
                    at androidx.room.RoomDatabase.inTransaction(RoomDatabase.kt:638)
                    at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.kt:457)
                    at androidx.room.RoomDatabase.query(RoomDatabase.kt:486)
                    at androidx.room.util.DBUtil.query(DBUtil.kt:75)
                    at com.###Dao_Impl$6.call(LoginTokenDao_Impl.java:156)
                    at com.###Dao_Impl$6.call(LoginTokenDao_Impl.java:153)
                    at androidx.room.CoroutinesRoom$Companion$execute$4$job$1.invokeSuspend(CoroutinesRoom.kt:88)
                    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)

I also tried with specifying default value of new field in the Entity:

val isExchangeSupported: Boolean = false,

But I have the same error

What am I doing wrong? In general, I only need to migrate one table that does not change, the rest can be dropped, but I did not find a way to do this, so I use automigration. But this does not work. Any solution will good for me, thanks in advance

Answer 1

To anyone who might come across the same issue,

Observation: this might happen if you build your app before incrementing the database version, when room then tries to do the migration, the source JSON schema would be interpreted as the same as the target JSON schema

Solution: Modify the source JSON schema to match the old version then redo a build, or if you have it under version control just reset to the committed version

Note: By JSON schema, I am referring to the JSON files auto-generated by room defining your database schema

Answer 2

@Remc4 comment: Also check your version 1 schema file. If it includes the isExchangeSupported field, you probably ran build project before increasing the version number.

And he was absolutely right, the project was build before I added the migration, so it already contained this field in the 1 schema.

So to fix it, just

• remove the previously created schema file
• rebuild the project.