IssueTracker

Thanks for trying sqlite-bundled! Quickly looking at your linked commit, it seems you have a single connection for the app but you might be doing concurrent queries, is that right? The APIs in sqlite-bundled are not at the same level as requery/sqlite-android nor Android's android.database.SQLiteDtabase, they will not protect the connection from concurrent usages, something not allowed unless you set the thread mode to serialized (see https://www.sqlite.org/threadsafe.html).

I also want to mention the APIs currently have no open helper like APIs to manage versions and migrations and transactions have to be manually nested. I mention these things to set the expectation right since you seem to be migrating from Requery's which has some big differences. Have you consider using a higher level abstraction, like Room or SQLDelight?

Yeah, that's right, I'm using a single shared connection, which is setup in the following way:

            execSQL("PRAGMA journal_mode=WAL")
            execSQL("PRAGMA synchronous=NORMAL")

            val version = prepare("SELECT user_version FROM pragma_user_version").use {
                if (it.step()) {
                    it.getInt(0)
                } else {
                    0
                }
            }

            if (version == 0) {
                execSQL(ElementQueries.CREATE_TABLE)
                execSQL(EventQueries.CREATE_TABLE)
                execSQL(ReportQueries.CREATE_TABLE)
                execSQL(UserQueries.CREATE_TABLE)
                execSQL(AreaQueries.CREATE_TABLE)
                execSQL(ConfQueries.CREATE_TABLE)
                execSQL("PRAGMA user_version=1")
            }

Clearly, it's rough around the edges, but I expected it to work.

SQLDelight is fine, but it involves codegen and the IDE support is far from perfect, that's why I prefer a lower-level API. requery has some maintenance issues, and I don't really have a lot of queries so I don't mind doing the manual mapping.

So, the current compile flags aren't thread safe? I thought everyone defaults on thread safe flags nowadays, but if it's the issue, I don't mind adding a mutex and/or a connection pool. I'll read the docs your referenced in order to figure our the best course of action, thanks for your explanation!

The current compile flag is 'multi-thread', see the flags here. It is possible to change the thread-mode at runtime via sqlite_config which I have been meaning to expose in the BundledSQLiteDriver, I think that would work for you in terms of trying different approaches to see what works best, i.e. between serializing yourself / connection pool or letting SQLite do it. But in essence the SQLite Driver APIs is almost as using sqlite3 C APIs directly, where as Requery, Room, SQLDelight all have built-in connection pools and connection protections.

I'll leave this bug open to to expose sqlite_config or for a way to change the thread mode at runtime.

Ah, I see, thanks for clarification. I was confused by this line:

The default mode is serialized. (https://www.sqlite.org/threadsafe.html)

So I assumed that it's perfectly safe to share the connection between the IO thread pool, but it seems like it's not the case for androidx.sqlite:sqlite-bundled. Any specific reason for overriding the defaults? Using the safest option by default while also allowing runtime overrides seems like a good idea

We compile with the multi-thread flag for performance reasons as Room has an internal connection pool.

I have a change that adds an overload to open() such that open flags can be passed, including SQLITE_OPEN_FULLMUTEX which will enable serialized mode for the connection, see example here.

The following release(s) address this bug.It is possible this bug has only been partially addressed:

• androidx.sqlite:sqlite-framework-iosarm64:2.5.0-alpha04
• androidx.sqlite:sqlite-framework-iossimulatorarm64:2.5.0-alpha04
• androidx.sqlite:sqlite-framework-iosx64:2.5.0-alpha04
• androidx.sqlite:sqlite-framework-linuxx64:2.5.0-alpha04
• androidx.sqlite:sqlite-framework-macosarm64:2.5.0-alpha04
• androidx.sqlite:sqlite-framework-macosx64:2.5.0-alpha04

I just tested this new flag and it looks like the issue still persists. It's easily reproducible since the repo is open source:

https://github.com/teambtcmap/btcmap-android/commit/9fe6be3604067ae1a1bd8b9c576b014023bff6ec

So default open() passes SQLITE_OPEN_READWRITE or SQLITE_OPEN_CREATE flags, and I changed it to SQLITE_OPEN_READWRITE or SQLITE_OPEN_CREATE or SQLITE_OPEN_FULLMUTEX

The database works fine after that change, but when I tried to disable the global connection lock (connLock variable) in the same file, the app started to crash with "android.database.SQLException: Error code: 21, message: no row"

android.database.SQLException: Error code: 21, message: no row
    at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.nativeGetLong(Native Method)
    at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.access$nativeGetLong(BundledSQLiteStatement.jvmAndroid.kt:1)
    at androidx.sqlite.driver.bundled.BundledSQLiteStatement.getLong(BundledSQLiteStatement.jvmAndroid.kt:70)
    at element.ElementQueries$selectClusters$1.invoke(ElementQueries.kt:271)

The flags are working as expected, but looks like the issue is in our JNI implementation, specifically we use sqlite3_errcode to get the 'last result code' and that is dangerous since it is a connection level API and if the connection is being used concurrently the 'last result code' of a statement might be from a different statement being executed, in other words we need to localize the 'last result code' per statement. I'll work on a fix. Again, I appreciate the feedback and you trying out the driver.

Project: platform/frameworks/support
Branch: androidx-main
Author: Daniel Santiago Rivera <danysantiago@google.com>
Link:      https://android-review.googlesource.com/3282515

Use sqlite3_stmt_busy to check if there are rows in a statement.

Use sqlite3_stmt_busy to check if there are rows in a statement.

Instead of using 'sqlite3_errcode(db) == SQLITE_ROW' to check if a statement is in a row and ready to read columns, use sqlite3_stmt_busy as that API is local to the statement and avoids the risk of sqlite3_errcode that works on a database connection returning a result code that is irrelevant to the statement when the connection is being used concurrently.

Bug: 340949940
Test: BaseBundledConformanceTest.openWithFullMutexFlag
Change-Id: If02039b90c6415a7532ae94f1c99e1182b546db8

The following release(s) address this bug.It is possible this bug has only been partially addressed:

• androidx.sqlite:sqlite-bundled-iossimulatorarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-bundled-iosx64:2.5.0-alpha10
• androidx.sqlite:sqlite-bundled-jvm:2.5.0-alpha10
• androidx.sqlite:sqlite-bundled-linuxarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-bundled-linuxx64:2.5.0-alpha10
• androidx.sqlite:sqlite-bundled-macosarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-bundled-macosx64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-android:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-iosarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-iossimulatorarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-iosx64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-linuxarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-linuxx64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-macosarm64:2.5.0-alpha10
• androidx.sqlite:sqlite-framework-macosx64:2.5.0-alpha10