SqliteException(5): database is locked, database is locked (code 5) - DriftRemoteException

[manhtran-itr]

Here is my code to create a database connection.

class DatabaseApp extends _$DatabaseApp {
  DatabaseApp() : super(_openConnection());

  @override
  int get schemaVersion => 4;
  Future<void> deleteAll() async {

  }

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onUpgrade: (Migrator m, int from, int to) async {
        if (from < 4) {
          m.createTable(bioheartTrackingEntity);
        }
      },
    );
  }
}

LazyDatabase _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    // The database saves into the support directory, doesn't expose to user
    // Review carefully before modifying it
    final dbFolder = await getApplicationSupportDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return NativeDatabase.createInBackground(file);
  });
}

Here are the logs:

communication.dart in DriftCommunication.request at line 109 within drift
client_impl.dart in _BaseExecutor._runRequest at line 85 within drift
client_impl.dart in _BaseExecutor.runSelect at line 118 within drift
lazy_database.dart in LazyDatabase.runSelect at line 83 within drift
1
select.dart in SimpleSelectStatement._getRaw.<fn> at line 68 within drift
connection_user.dart in DatabaseConnectionUser.doWhenOpened.<fn> at line 160 within drift

[simolus3]

Thanks for the report. Are you opening multiple instances of DatabaseApp at the same time or just one? Are you using the same database file in another place too (e.g. natively via platform-specific code)?

[manhtran-itr]

Thanks for your response! in native code we create another instance, please review the code below.

// creating object of database
// to perform query
private var db: SQLiteDatabase? = null

private class DatabaseHelper(context: Context?) : SQLiteOpenHelper(
    context,
    DATABASE_NAME,
    null,
    DATABASE_VERSION
) {
    // creating a table in the database
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_DB_TABLE)
    }

    override fun onUpgrade(
        db: SQLiteDatabase,
        oldVersion: Int,
        newVersion: Int
    ) {
        // sql query to drop a table
        // having similar name
        if (oldVersion != newVersion) {
            // Simplest implementation is to drop all old tables and recreate them
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }
    }
}

Does it cause the error? how to fix it?

[simolus3]

So the way I see it, you have two potential problems here:

1. Both the Android code and your Drift database are defining a migration strategy with onUpgrade. Depending on who opens the database first, only one onUpgrade will be called. And if the schemaVersion defined in Dart is not the same value as the DATABASE_VERSION in Kotlin, each database library will attempt to change it every time you open the database. If you want to share the database between Dart and native code, the migration strategies need to be the same: Drift is now responsible for creating tables used by native code, and your DatabaseHelper is responsible for performing the migration steps that your Dart database class expects. This is generally pretty hard to get right - if your app's architecture allows it, is there a way which allows drift to always open the database first and run the right migrations? Then you can avoid the onUpgrade and onCreate in Android.
2. Additionally, sqlite3 uses locks so that two writers don't corrupt the database if they're using it at the same time. The locked exception could occur both in Drift and in your native code. You can enable WAL mode by putting a setup: (db) => db.execute('PRAGMA journal_mode=WAL;') parameter on the NativeDatabase factory. In Android, your open helper would have to override onConfigure to call enableWriteAheadLogging().

[dickermoshe]

There has been no activity in this issue for over a year and no reproducible bug exists.