Cause of Fatal Exception: android.database.sqlite.SQLiteException duplicate column

Question

Getting this crash repost on Firebase Crashlytics : android.database.sqlite.SQLiteException duplicate column name: run_in_foreground (Sqlite code 1 SQLITE_ERROR): , while compiling: ALTER TABLE workspec ADD COLUMN run_in_foreground INTEGER NOT NULL DEFAULT 0, (OS error - 11:Try again)

Not able to find root cause of this problem. Below is the stacktrace

Fatal Exception: android.database.sqlite.SQLiteException: duplicate column name: run_in_foreground (Sqlite code 1 SQLITE_ERROR): , while compiling: ALTER TABLE workspec ADD COLUMN `run_in_foreground` INTEGER NOT NULL DEFAULT 0, (OS error - 11:Try again)
   at android.database.sqlite.SQLiteConnection.nativePrepareStatement(SQLiteConnection.java)
   at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:948)
   at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:559)
   at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
   at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
   at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
   at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2081)
   at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2003)
   at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:242)
   at androidx.work.impl.WorkDatabaseMigrations$6.migrate(WorkDatabaseMigrations.java:209)
   at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:99)
   at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.java:177)
   at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:417)
   at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:313)
   at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableSupportDatabase(FrameworkSQLiteOpenHelper.java:145)
   at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.java:106)
   at androidx.room.RoomDatabase.beginTransaction(RoomDatabase.java:352)
   at androidx.work.impl.utils.ForceStopRunnable.cleanUp(ForceStopRunnable.java:156)
   at androidx.work.impl.utils.ForceStopRunnable.run(ForceStopRunnable.java:87)
   at androidx.work.impl.utils.SerialExecutor$Task.run(SerialExecutor.java:91)
   at java.util.concurrent.ThreadPoolExecutor.processTask(ThreadPoolExecutor.java:1187)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
   at java.lang.Thread.run(Thread.java:784)

Need help here as app release is stuck because of this problem.

https://issuetracker.google.com/issues/159195172 – laalto Apr 21 '22 at 11:37 — laalto, Apr 21 '22 at 11:37

score 0 · Answer 1 · answered Apr 21 '22 at 11:33

It would be unlike SQLite to say that a column already exists if it does not, as such the cause will very likely be that the column does in fact already exist.

As a starter and as a temporary measure you could see if the table does in fact have the column already. As an example you could have :-

    val migration1_2 = Migration(1,2) {
        /* Will extract the SQL (schema) of the workspec table and write it to the log */
        DatabaseUtils.dumpCursor(it.query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name LIKE 'workspec'"))
        it.execSQL("ALTER TABLE workspec ADD COLUMN `run_in_foreground`INTEGER NOT NULL DEFAULT 0")
        DatabaseUtils.dumpCursor(it.query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name LIKE 'workspec'"))

    }

sqlite_master is basically the schema, the sql column contains the SQL that was used to create the component (e.g. table, index, trigger, view), the type is the component type (so only interested in tables) name is the name of the component (only interested in the workspec table).

Using this would then output something like the following to the log:-

2022-04-21 21:13:17.631 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@9c8df94
2022-04-21 21:13:17.631 I/System.out: 0 {
2022-04-21 21:13:17.631 I/System.out:    sql=CREATE TABLE `WorkSpec` (`workSpecId` INTEGER, `another` TEXT NOT NULL, PRIMARY KEY(`workSpecId`))
2022-04-21 21:13:17.631 I/System.out: }
2022-04-21 21:13:17.631 I/System.out: <<<<<



2022-04-21 21:13:17.633 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f1e7a3d
2022-04-21 21:13:17.633 I/System.out: 0 {
2022-04-21 21:13:17.633 I/System.out:    sql=CREATE TABLE `WorkSpec` (`workSpecId` INTEGER, `another` TEXT NOT NULL, `run_in_foreground`INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`workSpecId`))
2022-04-21 21:13:17.633 I/System.out: }
2022-04-21 21:13:17.634 I/System.out: <<<<<

Note that as can be seen the first invocation does not have the run_in_foreground column. The second does, showing that you ALTER TABLE command works if the column does not exist.

Of course you could take this further and check the result to see if the column already exists and skip the ALTER if this suited.

As to the actual cause the log is insufficient to determine this, you would probably need to provide the @Entity annotated class prior to the version change and probably the @Entity annotated class after the version change.

e.g. the above used:-

@Entity
data class WorkSpec(
    @PrimaryKey
    val workSpecId: Long? = null,
    val another: String,
    /* ADDED for Version 2*/

    @ColumnInfo(defaultValue = "0")
    val run_in_foreground: Int

)

Hey @MikeT thanks for the detailed answer. I resolved this issue by removing a third party library dependency which was causing this crash. I am in talks with the concerned team to get it fixed. — Gauresh Kambli, Apr 27 '22 at 13:34

Cause of Fatal Exception: android.database.sqlite.SQLiteException duplicate column

1 Answers1