Share db with host Android app embedded flutter readonly/read write problems

[diederikMrAwesome]

We have an Android and iOS app that embeds flutter. Flutter uses drift to get some data from the app's db. We have disabled all migrations on drift side and recently changed db access from readWriteCreate to readOnly. (Mutex flag is null, and migration disabled here as well. closeUnderlyingOnClose set to true.)

I have created a repo where a simple subset of the above structure is used, and manifests a problem that might be related to what we are seeing in our production app.

If the Android app does lots of inserts, and some transactions in the background, then the embedded flutter sometimes gets SqliteException(11): while selecting from statement, database disk image is malformed, database disk image is malformed (code 11) in read write mode, and sometimes gets SqliteException(776): while selecting from statement, attempt to write a readonly database, attempt to write a readonly database (code 776) in read only mode.

I find the last result most surprising as the only query that is run is a select. I am not expecting any writes to be attempted from the flutter side.

As I understand how SQLite works, it would be valid to have drift access the SQlite file via the dart process in read only mode. (Even read write, if all the locks are working correctly between Android and Flutter. But this isn't as important for now, as our requirements are for Flutter to be read only for now.)

The Android app uses ORMLite, but connects to the SQLite db file using normal Android database APIs.

The emulator I've reliably reproduced this problem on is running Android API level 32

The project uses drift 2.17.0 and running flutter 3.19.6

[simolus3]

Thanks for the report! I can reproduce the issue, but I'm afraid I don't really understand how this is happening either. Interestingly it works for me after re-opening the app and navigating to the Flutter fragment - so it's not an actual database corruption but rather appears to be corrupting an in-memory cache.

One workaround may be to use drift_sqflite instead of package:drift/native.dart and sqlite3_flutter_libs - you can replace the NativeDatabase with an SqfliteQueryExecutor. sqflite uses platform channels to run sqlite3 queries, so that avoids potential conflicts there.

[diederikMrAwesome]

I made a new branch on that repo that seems to confirm your suspicions. I can't now reproduce this problem on the example app. Thanks!

(I do notice that it is much slower, but I guess that is the cost of the workaround.)

Do you think it is worth posting the original problem on the SQLite forums? I am not expecting the Android process (That accesses the SQLite db file via the system SQLite service (and uses an older version of SQLite) to conflict with the one read/written by normal drift in this way.

[simolus3]

Do you think it is worth posting the original problem on the SQLite forums?

I think the chance of this being an Android-specific issue are far higher. I know that Android is doing its own locking around sqlite3 databases when using the Java API, but I don't know if that means they're setting a flag to disable sqlite3 locks altogether. I wanted to check, but pragma compile_options; doesn't return any rows for me when running it on Android.

There has to be a proper solution here, but I could not find it. You can't be the first one sharing a sqlite3 database between Java and what's essentially an NDK thread accessing the same database via a direct C library for sqlite3.

[diederikMrAwesome]

I've seen an interesting thing: if you enable write-ahead logging on Android side, the problem goes away.

It is my understanding that the filesystem locking should allow for smooth access in this case between Android and drift. Could one of the two parties here be ignoring the locks? Or not setting them up correctly?

Patch snippet for above idea:

Index: app/src/main/java/com/skynamo/sqlitedbfun/ui/home/HomeFragment.kt
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/app/src/main/java/com/skynamo/sqlitedbfun/ui/home/HomeFragment.kt b/app/src/main/java/com/skynamo/sqlitedbfun/ui/home/HomeFragment.kt
--- a/app/src/main/java/com/skynamo/sqlitedbfun/ui/home/HomeFragment.kt	(revision fdd421dfd08dda9613476f708192df3b57eb4a9d)
+++ b/app/src/main/java/com/skynamo/sqlitedbfun/ui/home/HomeFragment.kt	(date 1715266975382)
@@ -64,6 +64,8 @@
     private fun insertRandomUserData() {
         if (_dao == null) {
             _dao = DataItemDao(this.requireContext())
+            _dao?.databaseHelper?.setWriteAheadLoggingEnabled(true)
         }
 
         // Launch a coroutine in the background

[simolus3]

Could one of the two parties here be ignoring the locks? Or not setting them up correctly?

Yeah that's what I'm thinking as well. It's possible that WAL mode just hides the symptoms (WAL mode still uses locks on the WAL file to avoid two concurrent writers - it's possible that this still has potential to break, but if one process is read-only it should be safe).

Still, drift/package:sqlite3 isn't disabling thread safety and as far as I know, Android isn't doing that either. So I still don't understand what's causing this.