Cause of Fatal Exception: android.database.sqlite.SQLiteExce

Question

在Firebase Crashlytics上获得这个崩溃转发:android.database.sqlite.SQLiteException重复列名:run_in_前台(Sqlite代码1 SQLITE_ERROR):，当编译时:ALTER TABLE workspec添加列run_in_foregroundINTEGER NOT NULL默认0，(操作系统错误- 11:重试)

无法找到这个问题的根本原因。下面是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)

需要帮助，因为这个问题导致应用程序发布卡住了。

Answer 1

如果一个列不存在，那么SQLite就不会说这个列已经存在，因此很可能是因为这个列实际上已经存在。

作为启动器和临时措施，您可以查看表是否实际上已经存在该列。例如:-

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基本上是schema，sql列包含用于创建组件(例如表、索引、触发器、视图)的SQL，类型是组件类型(因此只对表感兴趣)名称是组件的名称(只对工作规范表感兴趣)

使用此命令将输出如下内容到日志:

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: <<<<<

• 请注意，可以看到第一次调用没有run_in_foreground列。第二个可以，表明如果列不存在，ALTER TABLE命令可以工作。

当然，您可以进一步检查结果，看看该列是否已经存在，如果合适的话，可以跳过ALTER。

至于日志不足以确定这一点的实际原因，您可能需要在版本更改之前提供@Entity注释类，并可能在版本更改之后提供@Entity注释类。

。以上使用:-

@Entity
data class WorkSpec(
@PrimaryKey
val workSpecId: Long? = null,
val another: String,
/* ADDED for Version 2*/
@ColumnInfo(defaultValue = "0")
val run_in_foreground: Int
)