search

simolus3 / drift

Drift is an easy to use, reactive, typesafe persistence library for Dart & Flutter.
https://drift.simonbinder.eu/
MIT License
2.54k stars 362 forks source link

Seeing multiple instances of SqliteException(13), SqliteException(14), and SqliteException(5) in the wild #3099

Closed btrautmann closed 3 weeks ago

btrautmann commented 1 month ago

Describe the bug

I have had multiple users report serious (full reinstall requiring) issues with their local database. The most recent began manifesting as:

SqliteException(13): while selecting from statement, database or disk is full, database or disk is full (code 13) Causing statement: SELECT

After force-quitting the app, the user encountered:

SqliteException(14): while selecting from statement, unable to open database file, unable to open database file (code 14) Causing statement: SELECT

and was unable to use the app without reinstalling. Once reinstalling, they ran into the same series of events again.

Other users occasionally run into:

Fatal Exception: io.flutter.plugins.firebase.crashlytics.FlutterError
SqliteException(5): while executing, database is locked, database is locked (code 5) Causing statement: COMMIT TRANSACTION, parameters:

I don't believe I'm doing anything particularly novel. As discussed in https://github.com/simolus3/drift/issues/944#issuecomment-2184201454 I have one event in my app (initial launch/load) that inserts a potentially large number of transactions into the database, but after that only minor delta updates are made--yet the jank on each launch and the issues (for some users) above persist. Are there known cases where these types of issues can occur? The ones I can think of are:

My app is local-only right now so users who run into these issues are at risk of data loss and it's very hard to have that be a possibility.

Is there a way to have more real-time support with drift, where I can share a more holistic view of my setup and potentially get help with this? I'd be willing to pay an hourly rate if that's a possibility.

simolus3 commented 1 month ago

Is this on Android or on iOS? Or both?

This feels like it should be recoverable. Do I need to do something special here?

SQLite should automatically roll the database back into the state immediately before the transaction. There's nothing that application developers would have to do here.

If it's iOS, my best guess is that (due to another native dependency), you end up with two sqlite3's linked in your application. This can cause all kinds of bizarre issues as the functions from the library get mixed up. If this only/mostly happens on iOS that's something worth checking.

Is there a way to have more real-time support with drift, where I can share a more holistic view of my setup and potentially get help with this? I'd be willing to pay an hourly rate if that's a possibility.

Sure, feel free to reach out to oss@simonbinder.eu so that we can discuss what works best for you. I'll work on issues where public information is available either way though.

btrautmann commented 1 month ago

Is this on Android or on iOS? Or both?

The SqliteException(5) has happened on Android and iOS and is the most common varient.

The SqliteException(13) and SqliteException(14) have (to my knowledge and according to my logs) only happened to one user, an iOS user using an older device (iPhone 11) running up-to-date iOS (17.5.1). One oddity about this user is that when they reinstall the application, the same thing happens. The error above (SqliteException(5)) seems to correct itself after a reinstall. Right now I have no path forward/way to help the user facing codes 13/14.

If it's iOS, my best guess is that (due to another native dependency), you end up with two sqlite3's linked in your application. This can cause all kinds of bizarre issues as the functions from the library get mixed up

Is there any way to identify if this is a possibility?

simolus3 commented 1 month ago

The SqliteException(5) has happened on Android and iOS and is the most common varient.

I think this error is a little less worrying too since it doesn't scream data corruption - still worth fixing of course. The most likely cause is that there are indeed two threads with separate database connections to the same file. Even when using WAL, concurrent writes are not allowed and can fail with that error. Drift provides some tools like DriftIsolate to coordinate access, when that is not possible you can instruct sqlite3 to retry internally with the use of the busy timeout pragma.

Is there any way to identify if this is a possibility?

Unfortunately my native iOS knowledge is not that advanced, but I think there's a view in XCode somewhere telling you which frameworks you're including and what native libraries they are linking to.

The problem is that an (always slightly outdated) version of sqlite3 is available as part of the OS on Apple devices, and Apple happily lets libraries link to that. I think in particular some tools like the native Google Analytics libraries are making use of that. With sqlite3_flutter_libs, we include or own (newer) sqlite3 build, but since it all goes into one static bundle there can be conflicts which are hard to detect. There's this somewhat involved workaround patching build files of dependencies to no longer link to the native sqlite. That was required for sqlcipher_flutter_libs, it would be worth checking if that also solves the issue for raw sqlite3.

iampopal commented 1 month ago

I am also getting this error in windows operating system.

simolus3 commented 1 month ago

@iampopal Which of the error codes mentioned here specifically? Only 5 or 13/14 as well?

btrautmann commented 1 month ago

I'm willing to bet codes 13 and 14 in my case have the same root cause as https://github.com/simolus3/drift/issues/3110. I will verify with the user that the fix for that issue fixes their database issues (I have only one user that's seen codes 13/14). I'm not sure about code 5, though I believe this issue is usually recoverable with an app restart. I will continue to keep an eye on instances of that.

simolus3 commented 1 month ago

A database exponentially growing in size would explain the "no space remaining" issue, but it's still surprising to see data corruption even in that case.

btrautmann commented 3 weeks ago

If it were up to me, I'd close this issue--I have seen no new instances of any of these codes since fixing the bug I mentioned above. I suspect they were all related and would also theorize that nothing here was related to a bug/issue with drift. Since others have commented though, I will keep it open and defer to @simolus3 on closure.

simolus3 commented 3 weeks ago

@iampopal I suspect these have a different cause in your case, so if you're still experiencing these errors please follow up with a new issue and more details if possible.