SqliteException(261): while executing, database is locked, database is locked (code 261) Causing statement: PRAGMA wal_checkpoint(PASSIVE);PRAGMA busy_timeout = 5000;PRAGMA journal_size_limit = 6144000;PRAGMA synchronous = normal;PRAGMA journal_mode = WAL;

[matteoscaramuzza]

On some Android devices (for example, a Xiaomi Redmi 9T), when an app is put in background and opened back after some time (2-3 hours averagely) this exception is thrown and it is impossible to access the database until you close completely the app and open it back.

On the devices that I have encountered the problems, energy saving mode was enabled. I don't know if it may be related.

This is the code that opens the connection:

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    var path = Platform.isAndroid
        ? p.join(dbFolder.parent.path, 'databases/zacapp.sqlite')
        : p.join(dbFolder.path, 'zacapp.sqlite');

    final file = File(path);

    if (Platform.isAndroid) {
      await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();
    }

    final cacheBase = (await getTemporaryDirectory()).path;
    sqlite3.tempDirectory = cacheBase;

    return NativeDatabase.createInBackground(file, setup: (db) {
      db.execute('PRAGMA wal_checkpoint(PASSIVE);'
          'PRAGMA busy_timeout = 5000;'
          'PRAGMA journal_size_limit = 6144000;'
          'PRAGMA synchronous = normal;'
          'PRAGMA journal_mode = WAL;');
    });
  });
}

Originally, in the setup I have put only the WAL mode enabling command, but after reading online some possible solutions I've added the other commands.

This error seems related only to the WAL mode, accordingly to official documentation: https://www.sqlite.org/rescode.html#busy_recovery

I instantiate the database connection using Riverpod.

Can someone help me with this problem?

[neiljaywarner]

@simolus3 can you please consider this.. i also plan to use background access and riverpod

[neiljaywarner]

@matteoscaramuzza what's this?
await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();

does your problem only occur on old devices - Android 10 or earlier?

[matteoscaramuzza]

@matteoscaramuzza what's this? await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();

does your problem only occur on old devices - Android 10 or earlier?

That part of code is referenced in the documentation here: https://drift.simonbinder.eu/docs/getting-started/#database-class.