throwingnet.sqlcipher.database.SQLiteException: cannot start a transaction within a transaction: BEGIN TRANSACTION

[OlegPrivet]

Expected Behavior

Migrate to the new version

Actual Behavior

When I try to migrate the database to a new version, I get an error.
Used android-database-sqlcipher(4.5.3) and androidx.room(2.4.3).

Steps to Reproduce

Migration code:

internal val MIGRATION_52_53: Migration = object : Migration(52, 53) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("BEGIN TRANSACTION")

        database.execSQL("ALTER TABLE `Reconciliation` ADD COLUMN `guid` TEXT NOT NULL DEFAULT ''")
        val cursor = database.query("SELECT * FROM `Reconciliation` WHERE `guid` == ''")
        while (cursor.moveToNext()) {
            val id = cursor.getLong(cursor.getColumnIndexOrThrow("id"))
            database.execSQL("UPDATE `Reconciliation` SET guid = '${UUID.randomUUID()}' WHERE id = $id")
        }

        database.execSQL("COMMIT")
    }
}

When executing the code database.execSQL("BEGIN TRANSACTION") the application crashes with an exception:

throwingnet.sqlcipher.database.SQLiteException: cannot start a transaction within a transaction: BEGIN TRANSACTION
    at net.sqlcipher.database.SQLiteDatabase.native_execSQL(Native Method)
    at net.sqlcipher.database.SQLiteDatabase.execSQL(SQLiteDatabase.java:2439)
    at ru.m4bank.directposapi.storage.db.migration.MigrationsKt$MIGRATION_53_54$1.migrate(Migrations.kt:346)
    at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:99)
    at net.sqlcipher.database.SupportHelper$1.onUpgrade(SupportHelper.java:46)
    at net.sqlcipher.database.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:182)
    at net.sqlcipher.database.SupportHelper.getWritableDatabase(SupportHelper.java:83)
    at androidx.room.RoomDatabase.inTransaction(RoomDatabase.java:706)
    at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.java:483)
    at androidx.room.RoomDatabase.query(RoomDatabase.java:526)
    at androidx.room.util.DBUtil.query(DBUtil.java:86)
    at ru.m4bank.directposapi.storage.dao.TransactionDao_Impl$31.call(TransactionDao_Impl.java:10556)
    at ru.m4bank.directposapi.storage.dao.TransactionDao_Impl$31.call(TransactionDao_Impl.java:10553)
    at androidx.room.CoroutinesRoom$Companion$execute$4$job$1.invokeSuspend(CoroutinesRoom.kt:88)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
    at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
    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:919)

Help me please

[developernotes]

Hi @OlegPrivet,

I suspect Room is running your migration in a transaction already, which would explain the error message you are seeing. Their example does not include explicit transaction calls. Try removing that first. If that does not work, try posting this over to the SQLCipher community discuss site.