Share db with host Android app embedded flutter readonly/read write problems about drift HOT 5 OPEN

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.

from drift.

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.

from drift.

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.

from drift.

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

from drift.

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.

from drift.