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.

I'm still working on a fix, but here's the current state of OnConflictStrategy.

# REPLACE
Works as expected. Old rows are replaced.

# IGNORE
Works as expected. New rows are ignored.

# ROLLBACK
This ends the transaction on conflict. Thus, the following call of endTransaction() always fails. This is basically unusable.

# ABORT (default)
As the SQLite doc says, all the changes prior to this statement are preserved, but they are rolled back by the surrounding transaction anyway.

# FAIL
This works pretty much the same as ABORT, except that it does not roll back the current statement at first. It is, however, rolled back by the surrounding transaction along with the preceding changes.

In summary, ABORT and FAIL produce the same results, and unlike the SQLite doc says, they both roll back on conflict (because of the surrounding transaction). ROLLBACK always fails.

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

Yes, this is exactly what I observed. It seems that FAIL and ROLLBACK strategies don't make much sense for Room. If this is correct, maybe you should deprecate and later remove them to avoid confusion?

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

http://r.android.com//839274

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

Fine!
Maybe it would also be useful to mention in the docs which strategies can throw an SQLiteConstraintException (btw, is it the only possible type of exception?) and which cannot.

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

SQLiteConstraintException is the only type thrown by the platform SQLite, but if you use custom implementation [1], it might throw something else.
[1]:

https://developer.android.com/reference/android/arch/persistence/room/RoomDatabase.Builder.html#openHelperFactory(android.arch.persistence.db.SupportSQLiteOpenHelper.Factory)

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

Project: platform/frameworks/support
Branch: androidx-master-dev

commit b8b669fd8cc863ad19a5e6c912b163aab107a86f
Author: Yuichi Araki <yaraki@google.com>
Date: Mon Dec 03 11:40:46 2018

Deprecate OnConflictStrategy.ROLLBACK and FAIL

OnConflictStrategy.ROLLBACK was never working properly. On conflict, it
always fails with SQLiteException since Room tries to end the
transaction which has already been rolled back by the conflict. In
addition, framework has a bug in handling of transaction around ROLLBACK
(

b/120397728

), so it is impossible to make this work as expected.

Instead, ABORT has always worked like ROLLBACK. This is because the
INSERT throws SQLiteConstraintException on conflict, so the wrapping
transaction is ended without being marked as successful and thus rolled
back. We could change this to work more like SQLite's original ABORT
(keep preceding changes in the transaction), but it is too risky to
change the behavior of ABORT since it is the default option of @Insert
and @Update. This CL just clarifies the behavior in Javadoc.

OnConflictStrategy.FAIL is no different from ABORT because of the
wrapping transaction. We should just remove it for clarity.

REPLACE and IGNORE are working fine.

Bug: 117266738
Test: OnConflictStrategyTest
Change-Id: Id62920d531afe4644d9d37ffc823a65132b54b4f

M room/common/api/2.1.0-alpha03.txt
M room/common/api/current.txt
M room/common/src/main/java/androidx/room/Insert.java
M room/common/src/main/java/androidx/room/OnConflictStrategy.java
M room/common/src/main/java/androidx/room/Update.java
A room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/OnConflictStrategyTest.java

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

https://goto.google.com/android-sha1/b8b669fd8cc863ad19a5e6c912b163aab107a86f

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.