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.

For Kotlin 2.0 and KSP 2.0 the Cannot change attributes of configuration ':composeApp:debugFrameworkIosX64' after it has been locked for mutation really seems like a KSP issue. You should file a bug in their repository with a sample app if possible.

If you downgrade to Kotlin 1.9 then things 'should' work, there are example apps out there with such configuration, like the following one: https://github.com/realityexpander/KMP_Room/tree/main

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

Yes this does seem like a KSP issue however it only starts happening when room is added to the project. As the template project compiles just fine.

Will try to use the example provided by you to check if it fixes the issue.

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

Note that this issue happens when applying the Compose, KSP and Room Plugin together in Kotlin 2.0.x, the workaround for now is to not use the Room Gradle Plugin and instead specify the schema location vis KSP arguments:

// In the build.gradle
ksp {
    arg("room.schemaLocation", "${projectDir}/schemas")
}

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

Hi, I encountered a similar problem and was able to resolve it by updating the dependencies

room = "2.7.0-alpha08"
ksp = "2.0.20-1.0.25"

compose-plugin = "1.6.11"
kotlin = "2.0.20"

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.