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.

There is no error when I use kotlin versions: 1.6.21, but issue/error starts appearing when kotlin version is 1.7.0

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

I have the same problem, i think no stable version of room 2.4.2 with kotlin 1.7.0

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

usage implementation 'androidx.room:room-ktx:2.5.0-alpha02'

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

me too

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

Thanks for the report I was able to reproduce the issue and indeed it occurs when using Kotlin 1.7.0. Sadly I don't have a workaround yet, but will investigate further.

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

and just to clarify, we see the issue when using Room 2.4.2, but not with Room 2.5.0-alpha02.

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

it occurs when kotlin version 1.7.0 and room 2.5.0-alpha02 as well to me. I solved the problem by downgrading kotlin version to 1.6.20

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

The same if you trying this Google-Developer Project:

https://github.com/google-developer-training/android-basics-kotlin-sql-basics-app
I updated everything - It wasn't running.
With ext.kotlin_version = "1.6.21" - it is running.

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

As a workaround, you can remove the "suspend" in the dao, but you need to explicitly specify the Dispatchers.IO when launching the coroutine.

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

Hey all, looks like the issue is related to a change in Kotlin's @Metadata annotation and the way Room reads it, Room 2.4.2 is using an older version of the kotlinx-metadata-jvm library which does not support reading Kotlin 1.7 information, where as in Room 2.5.0 we updated the library.

To workaround this issue please force upgrade the dependency by adding it to your annotation processor path, i.e. add kapt "org.jetbrains.kotlinx:kotlinx-metadata-jvm:0.5.0" if you are using Room 2.4.x.

Meanwhile, I'll work on releasing a Room 2.4.3 that is compatible with Kotlin 1.7 since Room 2.5.x is still in development.

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

Marked as fixed.