Auto Migration SQLiteException: unknown error (code 0 SQLITE_OK): Queries can be performed using SQLiteDatabase query or rawQuery methods only [190113935]

Fixed

Bug

Status Update

No update yet.

Description

as...@gmail.com

created issue #1

Jun 6, 2021 05:39PM

Component used: Room

Version used: 2.4.0-alpha02

I just added foreign keys on a couple of entities and got this error when trying to use AutoMigration.

Here's the generated code:

class AppDatabase_AutoMigration_1_2_Impl extends Migration {
  public AppDatabase_AutoMigration_1_2_Impl() {
    super(1, 2);
  }

  @Override
  public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_float_hsv` (`hue` REAL NOT NULL, `saturation` REAL NOT NULL, `value` REAL NOT NULL, `alpha` REAL NOT NULL, `palette_id` TEXT NOT NULL, `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FOREIGN KEY(`palette_id`) REFERENCES `palette`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("INSERT INTO `_new_float_hsv` (saturation,alpha,palette_id,hue,id,value) SELECT saturation,alpha,palette_id,hue,id,value FROM `float_hsv`");
    database.execSQL("DROP TABLE `float_hsv`");
    database.execSQL("ALTER TABLE `_new_float_hsv` RENAME TO `float_hsv`");
--> database.execSQL("PRAGMA foreign_key_check(`float_hsv`)");
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_saturation_value` (`saturation` REAL NOT NULL, `value` REAL NOT NULL, `palette_id` TEXT NOT NULL, `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FOREIGN KEY(`palette_id`) REFERENCES `harmony_palette`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("INSERT INTO `_new_saturation_value` (saturation,palette_id,id,value) SELECT saturation,palette_id,id,value FROM `saturation_value`");
    database.execSQL("DROP TABLE `saturation_value`");
    database.execSQL("ALTER TABLE `_new_saturation_value` RENAME TO `saturation_value`");
    database.execSQL("PRAGMA foreign_key_check(`saturation_value`)");
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_hue` (`hue` REAL NOT NULL, `palette_id` TEXT NOT NULL, `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FOREIGN KEY(`palette_id`) REFERENCES `harmony_palette`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("INSERT INTO `_new_hue` (palette_id,hue,id) SELECT palette_id,hue,id FROM `hue`");
    database.execSQL("DROP TABLE `hue`");
    database.execSQL("ALTER TABLE `_new_hue` RENAME TO `hue`");
    database.execSQL("PRAGMA foreign_key_check(`hue`)");
  }
}

Stack trace:

android.database.sqlite.SQLiteException: unknown error (code 0 SQLITE_OK): Queries can be performed using SQLiteDatabase query or rawQuery methods only.
        at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:890)
        at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
        at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1920)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1841)
        at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:242)
        at com.se7en.hue.data.room.AppDatabase_AutoMigration_1_2_Impl.migrate(AppDatabase_AutoMigration_1_2_Impl.java:21)
        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:416)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:316)
        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.internalBeginTransaction(RoomDatabase.java:591)
        at androidx.room.RoomDatabase.beginTransaction(RoomDatabase.java:580)
        at com.se7en.hue.data.room.PaletteDao_Impl$21.call(PaletteDao_Impl.java:417)
        at com.se7en.hue.data.room.PaletteDao_Impl$21.call(PaletteDao_Impl.java:414)
        at androidx.room.CoroutinesRoom$Companion$createFlow$1$1$1.invokeSuspend(CoroutinesRoom.kt:125)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
        at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
        at androidx.room.TransactionExecutor$1.run(TransactionExecutor.java:47)
        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)

Comments

el...@google.com <el...@google.com> #2Jun 6, 2021 06:12PM

Reassigned to el...@google.com.

Hi there - could you verify that you do not have any foreign key violations in your database? I think this may be the reason why you are receiving this error, though we should make sure to provide a more clear error message if this is the case.

as...@gmail.com <as...@gmail.com> #3Jun 6, 2021 06:29PM

Here's my entity:

@Entity(tableName = "palette")
class ManualPaletteEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: String,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "time_millis") val timeMillis: Long,
)

@Entity(
    tableName = "float_hsv",
    foreignKeys = [
        ForeignKey(
            entity = ManualPaletteEntity::class,
            parentColumns = ["id"],
            childColumns = ["palette_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class FloatHSVEntity(
    @ColumnInfo(name = "hue") val hue: Float,
    @ColumnInfo(name = "saturation") val saturation: Float,
    @ColumnInfo(name = "value") val value: Float,
    @ColumnInfo(name = "alpha") val alpha: Float,
    @ColumnInfo(name = "palette_id") val paletteId: String,
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") val id: Int = 0,
)

yb...@google.com <yb...@google.com> #4Jun 7, 2021 03:56PM

FK violation would happen based on the data in the database. For that example, if you have anything in float_hsv where palette_id does not match an id from ManuelPaletteEntity, it would crash.

I'm also not sure sqlite is even able to detect that match after the fact, we should probably test that and figure out if we can workaround it.

as...@gmail.com <as...@gmail.com> #5Jun 7, 2021 04:01PM

Alright, I'll try to figure out what's wrong

as...@gmail.com <as...@gmail.com> #6Jun 7, 2021 04:12PM

Yep, you were right. There's a FK violation

yb...@google.com <yb...@google.com> #7Jun 7, 2021 04:15PM

Thanks for checking it. @Elif, lets use this bug to see if we can improve the message.

We could even consider checking for FKey violations ourselves by generating a query and printing a better message. Though if that happens on production, that would mean repeatedly crashing the application :/.

That is probably the right thing to do. Room used to migrate destructively by default and developers didn't like it. We might also consider providing an API to delete violating entries in a future release.

el...@google.com <el...@google.com> #8Jun 7, 2021 04:55PM

Sounds good - we have an idea on how we can handle this in the AutoMigrations API logic, and I am now working on a fix, will link to this bug when it's ready.

el...@google.com <el...@google.com> #9Jun 13, 2021 12:49PM

A follow up - would you be able to share with us what kind of FK violation was in the implementation?

as...@gmail.com <as...@gmail.com> #10Jun 14, 2021 09:24AM

float_hsv had a palette_id that was deleted before. That's why I added the foreign key and onDelete = ForeignKey.CASCADE to automatically delete float_hsv when the palette was deleted.

ap...@google.com <ap...@google.com> #11Jun 23, 2021 11:26AM

Project: platform/frameworks/support
Branch: androidx-main

commit 7917c78c2e0212794a90e46fb6db554c90eec4b9
Author: Elif Bilgin <elifbilgin@google.com>
Date: Mon Jun 07 13:58:31 2021

Handle foreign key violations during Auto Migration more gracefully.

When doing a PRAGMA foreign key check after complex changes have been performed on the database, in the case of a foreign key violation, we are now outputting a clear error message notifying the user that a violation has been found, including the name of the relevant entity.

Bug: 190113935
Test: TransactionMethodProcessorTest.kt
Change-Id: I68e256074d2aa801bb8db9fced5e0099ff51b6df

M room/integration-tests/testapp/schemas/androidx.room.integration.testapp.migration.AutoMigrationDb/1.json
M room/integration-tests/testapp/schemas/androidx.room.integration.testapp.migration.AutoMigrationDb/2.json
A room/integration-tests/testapp/schemas/androidx.room.integration.testapp.migration.AutoMigrationDb/3.json
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/migration/AutoMigrationDb.java
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/migration/AutoMigrationTest.java
M room/room-compiler/src/main/kotlin/androidx/room/writer/AutoMigrationWriter.kt
M room/room-runtime/api/restricted_current.txt
M room/room-runtime/src/main/java/androidx/room/util/DBUtil.java

https://android-review.googlesource.com/1729334

el...@google.com <el...@google.com> Jun 23, 2021 11:26AM

Marked as fixed.