duplicate column name: <Column Name>(code 1 SQLITE_ERROR): What’s wrong with Room?

Published

I had in my app a SQLiteDataBase with three columns. Than I migrated it to Room database as follows:

@Database(entities = [Vehicle::class], version = 6, exportSchema = false)
abstract class VehicleDatabase : RoomDatabase() {

    abstract val dao: Dao

    companion object {

        @Volatile
        private var INSTANCE: VehicleDatabase? = null

        private val migration = object : Migration(1, 5) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.apply {
                    execSQL("CREATE TABLE vehicles_tmp (_id INTEGER, Manufacturer TEXT, Model TEXT, Created TEXT, Modified TEXT, PRIMARY KEY(_id))")
                    execSQL("INSERT INTO vehicles_tmp (_id, Manufacturer, Model, Created, Modified) SELECT _id, Manufacturer, Model, Date, Date FROM Vehicles")
                    execSQL("DROP TABLE Vehicles")
                    execSQL("ALTER TABLE vehicles_tmp RENAME TO Vehicles")
                    execSQL("ALTER TABLE Vehicles ADD COLUMN Tags TEXT")
                }
            }
        }

        private val addColor = object : Migration(5, 6) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL("ALTER TABLE Vehicles ADD COLUMN Color TEXT")
            }
        }

        fun getInstance(context: Context): VehicleDatabase = INSTANCE ?: synchronized(this) {
            val instance = Room.databaseBuilder(
                context.applicationContext,
                VehicleDatabase::class.java,
                "Vehicles.db"
            )
                .addMigrations(migration, addColor)
                .build()
            INSTANCE = instance
            return instance
        }

    }
}

The first migration was from version 1 to 5 and as you can see, I added one more column (Color). Therefore, I added migration from 5 to 6.

Everything went ok, but now I got on my Crashlytics in Firebase an exception

Fatal Exception: android.database.sqlite.SQLiteException: duplicate column name: Color (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE Vehicles ADD COLUMN Color TEXT
       at android.database.sqlite.SQLiteConnection.nativePrepareStatement(SQLiteConnection.java)
       at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1055)
       at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:662)
       at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
       at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
       at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:33)
       at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1949)
       at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1871)
       at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:265)
       at com.xxxxxxxx.xxxxxxxx.data.vehicles.NoteDatabase$Companion$addColor$1.migrate(VehicleDatabase.java:39)
       at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:99)
       at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.java:183)
       at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:421)
       at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:321)
       at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableSupportDatabase(FrameworkSQLiteOpenHelper.java:151)
       at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.java:112)
       at androidx.room.RoomDatabase.inTransaction(RoomDatabase.java:706)
       at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.java:483)
       at androidx.room.RoomDatabase.query(RoomDatabase.java:526)
       at androidx.room.util.DBUtil.query(DBUtil.java:86)
       at com.xxxxxxxx.xxxxxxxx.data.vehicles.Dao_Impl$9.call(Dao_Impl.java:262)
       at com.xxxxxxxx.xxxxxxxx.data.vehicles.Dao_Impl$9.call(Dao_Impl.java:259)
       at androidx.room.CoroutinesRoom$Companion$createFlow$1$1$1.invokeSuspend(CoroutinesRoom.java:128)
       at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(BaseContinuationImpl.java:33)
       at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.java:106)
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
       at java.lang.Thread.run(Thread.java:923)

Why it throws that exception? Seems that it tries to add that column wenn it is already added.

Or did I something wrong?

BR

Source: Android Questions

Published
Categorized as android, android-room

Answers

Leave a Reply

Still Have Questions?


Our dedicated development team is here for you!

We can help you find answers to your question for as low as 5$.

Contact Us
faq