attempt to write a readonly database (code 1032)

[jakobhec]

I am mostly following the setup documentation.

Sometimes, after restarting the app, I encounter the following error. It happens each time I try to write to the database, regardless of whether any other read or write operations are active.

flutter: SqliteException(1032): while executing statement, attempt to write a readonly database, attempt to write a readonly database (code 1032)
Causing statement: INSERT OR REPLACE INTO "my_table" ...

Here is my code:

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final file = await getFileLocationDrift(); // uses getApplicationDocumentsDirectory
    if (!await file.exists()) {
      final byteData = await rootBundle.load('assets/my_database.sqlite.gzip');
      final decodedData = GZipCodec().decode(byteData.buffer.asUint8List());
      await file.writeAsBytes(decodedData);
    }

    // workarounds for android. see https://drift.simonbinder.eu/docs/getting-started/
    if (Platform.isAndroid) {
      await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();
    }
    final cachebase = (await getTemporaryDirectory()).path;
    sqlite3.tempDirectory = cachebase;

    return NativeDatabase.createInBackground(
      file,
      logStatements: false,
    );
  });
}

This issue seems to go away when I set the journal mode to WAL. Like this:

    return NativeDatabase.createInBackground(
      file,
      logStatements: false,
      setup: (rawDb) {
        rawDb.execute('PRAGMA journal_mode=WAL;');
      },
    );

I don't understand why it helps. Can I simply do that? Are there any underlying issues that need to be addressed?

[simolus3]

Has the database in my_database.sqlite.gzip been created with WAL mode? I have seen that error sometimes when trying to open a WAL database where the shm or the wal files have been missing.

[jakobhec]

No. PRAGMA journal_mode returns delete.

[simolus3]

Hm ok. Have you checked the description for the extended error code? It's pretty weird that sqlite3 claims the database file has been moved, I don't know how it comes up with that. Does this happen on some platforms only or all of them?

[jakobhec]

Before yesterday, it happened occasionally (maybe 1 in 20 restarts) on Android and not on iOS. Then since yesterday, after some point it happened at every restart.

Before that I updated to "Xcode 15.1" and "Flutter 3.16.4 • channel stable". But I am not sure if it started immediately afterwards, sorry. And it also happens on Android. I did not update my *.sqlite.gzip file and I think pub upgrade did not affect any drift related libraries.

I just tested it a bit more. Between each flutter run I completely uninstalled the app to ensure that it starts from scratch. I am using iOS 17.2 and MIUI 14 Global 14.0.4.0.

Phone OS	journal_mode=WAL	sqlite3.tempDirectory = cachebase	Results
Android	used	used	works, works
Android	commented out	used	code 1032, code 1032
Android	used	commented out	works, works
Android	commented out	commented out	code 1032, code 1032
iOS	used	used	works
iOS	commented out	used	code 1032

I thought that yesterday commenting out sqlite3.tempDirectory = cachebase; made it work. That's why I've included it in the table. But it doesn't seem to make a difference.

(The table now just shows that it works with journal_mode=WAL and that it doesn't work without it.)

[simolus3]

So looking at the only possible source of that error, on iOS and Android this both calls stat to check whether the inode of the open file has changed. The error from that function is not surfaced for WAL databases, so that explains why you're not seeing it for that journal mode, but it's still very weird that the inode of the database file would change.

I wonder if this might be some backup service messing around with files, but then way more people should be seeing this and this is the first report I've gotten about it. Does this happen on debug and release mode builds (you don't have to retry every combination, but it might be good to know whether some non-WAL configuration experiencing the error works with release mode apps)?

[jakobhec]

Thanks for investigating! It appears that the issue occurs only in debug mode.

For all entries I did the following:

• commented out journal_mode=WAL
• used sqlite3.tempDirectory = cachebase
• uninstalled the app manually before

Phone OS	build mode	Results
iOS	debug	code 1032
iOS	release	works
Android	debug	code 1032
Android	release	works

Please let me know if you need any other tests.

[simolus3]

Very interesting. I wonder if this could be due to DevFS or something moving files around. I don't know Flutter internals well enough to say for sure, but that sounds like the most likely cause if this is a debug-only problem. Do you happen to remember the Flutter version you were on before upgrading to 3.16.4 (you don't need to re-run the tests on older versions, just to see how recent this might be)?

[jakobhec]

I am on the stable flutter channel and I always keep it updated. So I would assume since at least 3 months (maybe starting around 3.13 – very very roughly) I had these occasional issues on Android but then only after 3.16.4 in 100% of the debug mode flutter runs.

[simolus3]

I've asked around in the Flutter discord but that didn't lead to any obvious cause standing out. I wonder if this also happens without the initialization from the root bundle? If you just create an empty database on fresh installs, does the problem go away? It's not that useful obviously, but if that fixes the issue we know it's related to writeAsBytes and I can look into that specifically.