SqliteException(11): database disk image is malformed

[stx]

First, thank you for this lovely software.

Rarely, we'll see this issue on production via Crashlytics:

SqliteException(11): database disk image is malformed Causing statement: DROP TABLE IF EXISTS <table name>;. Error thrown null.

Non-fatal Exception: FlutterError
0  ???                            0x0 DatabaseImpl.execute + 129 (database.dart:129)
1  ???                            0x0 _VmDelegate._runWithArgs + 204 (database.dart:204)
2  ???                            0x0 _VmDelegate.runCustom + 214 (database.dart:214)
3  ???                            0x0 _BaseExecutor.runCustom.<fn> + 109 (engines.dart:109)
4  ???                            0x0 _BaseExecutor._synchronized + 55 (engines.dart:55)
5  ???                            0x0 _BaseExecutor.runCustom + 105 (engines.dart:105)
6  ???                            0x0 ServerImplementation._runQuery + 130 (server_impl.dart:130)

We're not issuing any DROP TABLE statements except in our migration:

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onUpgrade: (Migrator m, int from, int to) async {
          for (TableInfo<Table, dynamic> table in allTables) {
            await m.deleteTable(table.actualTableName);
            await m.createTable(table);
          }
        },
      );

Is there a better way to do this, or a way to fix or prevent this error?

[simolus3]

Thanks for the report. Are all of those "database disk image is malformed" errors coming from the migration? Or are there any others that with a similar error message but a different trace? For further analysis (since unfortunately this sounds like a bug that will be hard to trace down), it could be helpful try and catch this SqliteException and add the results of running pragma integrity_check to the Crashlytics error.

There's a list of things commonly causing database corruption, but most of these shouldn't be relevant with drift. Are you opening the same database file on multiple isolates?

Is there a better way to do this, or a way to fix or prevent this error?

Is it your intention to clear the entire database on each schema upgrade? Because if so, it might be easier to just use the sqlite3 package to open the raw database, check and compare the schema version and delete the database file when needed.

[stx]

Dug into the Crashlytics, and it's iOS only and only during migrations. Traces are always the same.

The database is opened once, on one isolate.

Yes, the intention is to clear out the data and recreate the schemas on each upgrade. I like how Drift handles the migration process automatically and it works extremely well except in these incredibly rare cases. How would you propose doing it better/easier?

[simolus3]

You could use something like this to delete the entire database file if it has a lower schema version:

import 'package:sqlite3/sqlite3.dart';

// This can be passed to the database's constructor
LazyDatabase open(int targetVersion) {
  return LazyDatabase(() async {
    // proper logic to determine which file to open...
    final file = File('/tmp/database.db');

    if (await file.exists()) {
      try {
        final db = sqlite3.open(file.path);
        if (db.userVersion < targetVersion) {
          db.dispose();
          await file.delete();
        }
      } on SqliteException {
        // This exception really shouldn't be thrown, but if the database file
        // is corrupted already, deleting it may be the best action.
        await file.delete();
      }
    }

    return NativeDatabase(file);
  });
}

Unfortunately I don't have an idea on where that error might be coming from or what's corrupting the database yet.

[abdelaziz-mahdy]

I have the same error on the web it only happens when I store a very large text

When I try to read the same table after that insert it shows up the database is malformed error

[simolus3]

@zezo357 Are you using a WebDatabase or a WasmDatabase? In either way, if you have a way to reproduce this (just storing a large string? if so, how large?), can you share that?

[abdelaziz-mahdy]

@zezo357 Are you using a WebDatabase or a WasmDatabase? In either way, if you have a way to reproduce this (just storing a large string? if so, how large?), can you share that?

WasmDatabase

How large it's a JSON file containing 10000 objects

Sadly I can't share it , but I will try to make a code to reproduce it

[abdelaziz-mahdy]

@simolus3 I was storing all of objects in a single text cell and just found out that their is a limit in text cell

The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.

from my understanding I broke the limit :)

looks like i will need to separate it to rows of objects in the table :(

[stx]

@simolus3

Using WasmSqlite3.loadFromUrl() is causing this to happen more frequently. Some users are experiencing this, and both my debug and production builds just became malformed, so we're rolling back to the old method.

[lomirus]

I have the same error when storing a large amount of data, too. To reproduce, you can:

1. Download the lexicon.json.
2. Go to the https://yuorb.github.io/enthrirhs/
3. Click the "Import lexicon from local files" and select "lexicon.json" (It may get stuck for a few seconds)
4. Refresh the page.

It can be reproduced stably in my environment.

Related code: https://github.com/yuorb/enthrirhs/blob/main/lib/database/shared.dart

[simolus3]

Thanks for the report - I'll try to check it out soon. In the meantime, do you see on which VFS implementation this happens (opfs shared or opfs locked, shared indexeddb?)

[lomirus]

do you see on which VFS implementation this happens (opfs shared or opfs locked, shared indexeddb?)

lib/database/web.dart:

final db = LazyDatabase(() async {
  // Create virtual filesystem for sqlite3 implemented over blobs stored in an
  // IndexedDB database (named `my_app` here).
  final fs = await IndexedDbFileSystem.open(dbName: 'my_app');

  final sqlite3 = await WasmSqlite3.loadFromUrl(
    Uri.parse('sqlite3.wasm'),
    environment: SqliteEnvironment(fileSystem: fs),
  );

  // Then, open a database:
  return WasmDatabase(sqlite3: sqlite3, path: '/app.db');
});

[lomirus]

🤔 Is there any progress now about this problem?

[simolus3]

Sorry for the slow response - unfortunately I couldn't reproduce this after upgrading to the latest version of the sqlite3 package and using the latest sqlite3.wasm file. The only things I've changed are the sqlite3/drift versions in the pubspec and

diff --git a/lib/database/web.dart b/lib/database/web.dart
index 9f3187e..9bd90e3 100644
--- a/lib/database/web.dart
+++ b/lib/database/web.dart
@@ -9,10 +9,8 @@ Database constructDb() {
     // IndexedDB database (named `my_app` here).
     final fs = await IndexedDbFileSystem.open(dbName: 'my_app');

-    final sqlite3 = await WasmSqlite3.loadFromUrl(
-      Uri.parse('sqlite3.wasm'),
-      environment: SqliteEnvironment(fileSystem: fs),
-    );
+    final sqlite3 = await WasmSqlite3.loadFromUrl(Uri.parse('sqlite3.wasm'));
+    sqlite3.registerVirtualFileSystem(fs, makeDefault: true);

     // Then, open a database:
     return WasmDatabase(sqlite3: sqlite3, path: '/app.db');

[lomirus]

You are right. I upgraded the drift and packages as you said, and found that the problem has already been fixed in the previous versions. Thanks for you work👍

[jakobhec]

I encountered the same issue: SqliteException(11): database disk image is malformed when installing a new app version requiring an updated database.

I manually deleted the existing SQLite database, then copied a new version in its place and opened it in WAL mode.

Deleting the -wal and -shm files seems to fix the issue. Here's how I now remove the database before copying over a new one:

final file = await getFileLocationDrift();
if (await file.exists()) {
  try {
    // https://github.com/simolus3/drift/issues/1773#issuecomment-1079767310
    final db = sqlite3.open(file.path);
    db.dispose();

    await file.delete();
  } on SqliteException {
    // This exception really shouldn't be thrown, but if the database file
    // is corrupted already, deleting it may be the best action.
    await file.delete();
  }

  final walFile = File('${file.path}-wal');
  if (await walFile.exists()) {
    await walFile.delete();
  }
  final shmFile = File('${file.path}-shm');
  if (await shmFile.exists()) {
    await shmFile.delete();
  }
}

Hope it is helpful for some. Alway happy to hear improvement ideas!

[jakobhec]

@simolus3 If the above behavior is expected, it might make sense to add it to the documentation.

[simolus3]

I agree, is there a particular place where you'd expect this information? Perhaps there should be a dedicated page about backups.

[jakobhec]

• https://drift.simonbinder.eu/docs/platforms/vm/#using-moor-ffi-with-an-existing-database
• https://drift.simonbinder.eu/docs/examples/existing_databases/#extracting-the-database
• and potentially under "Migrations" https://drift.simonbinder.eu/docs/migrations/

Or with a dedicated page as you've said and then you could link to it "See more about migrations/backups/existing-dbs" in the above places.

[simolus3]

@jakobhec To confirm, you're running into this with existing database files that have originally been created through an asset or another package and that you now want to use with drift?

If these databases are supposed to use the WAL journaling mode, I think deleting the -wal file might delete some recent modifications to the database.

[jakobhec]

I create the .sqlite database files on a server (using sqlalchemy / pandas / python).

Approximately every 2 months I want to completely replace the drift database on the mobile device with an updated version from the server. So I simply put a newly generated sqlite file from the server in the assets folder of my flutter application and replace the existing db on first app open after an udpate.

Deleting recent modifications is not an issue in this case.

[bendangelo]

I encountered the same issue: SqliteException(11): database disk image is malformed when installing a new app version requiring an updated database.

I manually deleted the existing SQLite database, then copied a new version in its place and opened it in WAL mode.

Deleting the -wal and -shm files seems to fix the issue. Here's how I now remove the database before copying over a new one:

final file = await getFileLocationDrift();
if (await file.exists()) {
  try {
    // https://github.com/simolus3/drift/issues/1773#issuecomment-1079767310
    final db = sqlite3.open(file.path);
    db.dispose();

    await file.delete();
  } on SqliteException {
    // This exception really shouldn't be thrown, but if the database file
    // is corrupted already, deleting it may be the best action.
    await file.delete();
  }

  final walFile = File('${file.path}-wal');
  if (await walFile.exists()) {
    await walFile.delete();
  }
  final shmFile = File('${file.path}-shm');
  if (await shmFile.exists()) {
    await shmFile.delete();
  }
}

Hope it is helpful for some. Alway happy to hear improvement ideas!

This should be added to the docs. I had issues deleting the old db and replacing it because of these files hanging around. Every time I loaded the replaced db I would get malformed error or other weird issues.

[rizaldi-wiratama]

Hello @simolus3 i have similar issue but maybe different case, in my case the issue is exist only when try to get data from db, but when saving the data it works without an issue..

this is stacktrace about the issue when try to get the data

SqliteException(11): while selecting from statement, database disk image is malformed, database disk image is malformed (code 11)
  Causing statement: SELECT * FROM "orders" WHERE "status" = ? OR "status" = ? OR "status" = ? ;, parameters: 0, 1, 2
package:sqlite3/src/implementation/exception.dart 75                              throwException
package:sqlite3/src/implementation/statement.dart 140                             StatementImplementation._selectResults
package:sqlite3/src/implementation/statement.dart 280                             StatementImplementation.selectWith
package:sqlite3/src/statement.dart 95                                             CommonPreparedStatement.select
package:drift/src/sqlite3/database.dart 161                                       Sqlite3Delegate.runSelect
package:drift/src/runtime/executor/helpers/engines.dart 77                        _BaseExecutor.runSelect.<fn>
package:drift/src/runtime/executor/helpers/engines.dart 61                        _BaseExecutor._synchronized
package:drift/src/runtime/executor/helpers/engines.dart 74                        _BaseExecutor.runSelect
package:drift/src/runtime/api/connection.dart 111                                 DatabaseConnection.runSelect
package:drift/src/remote/server_impl.dart 157                                     ServerImplementation._runQuery
package:drift/src/remote/server_impl.dart 118                                     ServerImplementation._handleRequest.<fn>
package:drift/src/remote/communication.dart 165                                   DriftCommunication.setRequestHandler.<fn>
===== asynchronous gap ===========================
package:drift/src/remote/communication.dart 113                                   DriftCommunication.request
package:drift/src/remote/client_impl.dart 97                                      _BaseExecutor._runRequest
package:drift/src/remote/client_impl.dart 130                                     _BaseExecutor.runSelect
package:drift/src/utils/lazy_database.dart 86                                     LazyDatabase.runSelect
package:drift/src/runtime/api/connection.dart 111                                 DatabaseConnection.runSelect
package:drift/src/utils/lazy_database.dart 86                                     LazyDatabase.runSelect
package:drift/src/runtime/query_builder/statements/select/select.dart 81          SimpleSelectStatement._getRaw.<fn>
package:drift/src/runtime/api/connection_user.dart 171                            DatabaseConnectionUser.doWhenOpened.<fn>
dart:async/zone.dart 1407                                                         _rootRunUnary
package:drift/src/runtime/query_builder/statements/select/select.dart 90          SimpleSelectStatement._mapResponse
package:xxx/service/database/database_service.dart 31                    DatabaseService.getOrdersByFilter

My code for function DatabaseService.getOrdersByFilter is just like this :

Future<List<Order>> getOrdersByFilter(OrderFilter filter) async => await database.managers.orders.filter(filter).get();

For now this only happen for just 1 Android device user who reporting this, and honestly i can't reproduce this issue

any insight how to fix this?

FYI i use this code to open connection to database

  static QueryExecutor _openConnection(){
    return LazyDatabase(() async {
      Directory? directory = await (Platform.isAndroid ? getExternalStorageDirectory() : getApplicationDocumentsDirectory());
      String fileName = "runchise_pos_db.sqlite";
      File dbFile = File("${directory?.path}${Platform.isWindows ? "\\" : "/"}$fileName");

      return NativeDatabase.createInBackground(dbFile);
    });
  }

should i use try catch on there to catch SqliteException and delete db, -wal & -shm like mentioned before?

btw i use these version

dependencies:
  drift: ^2.19.1+1
  sqlite3_flutter_libs: ^0.5.24
dev_dependencies:
  drift_dev: ^2.19.1

thankyou in advance, hope there's answer about this :)

[simolus3]

any insight how to fix this?

Nothing in the snippets stands out to me.

Does the error happen when inserting into the same table? This sounds like a data corruption issue. The database should never reach that state, even when the app process crashes during a write. It's unlikely that we'll be able to reproduce this though.

should i use try catch on there to catch SqliteException and delete db, -wal & -shm like mentioned before?

These files are only active when using the write-ahead log which is not enabled by default. If you want to catch and automatically delete corrupt databases, I suggest something like this:

QueryExecutor _openConnection() {
  return LazyDatabase(() async {
    Directory? directory = await (Platform.isAndroid
        ? getExternalStorageDirectory()
        : getApplicationDocumentsDirectory());
    String fileName = "runchise_pos_db.sqlite";
    File dbFile =
        File("${directory?.path}${Platform.isWindows ? "\\" : "/"}$fileName");

    return _openIsolateFor(dbFile.path);
  });
}

Future<QueryExecutor> _openIsolateFor(String path) async {
  final isolate = await DriftIsolate.spawn(
    () {
      final file = File(path);
      if (file.existsSync()) {
        final temp = sqlite3.open(path);
        var corrupt = false;

        try {
          final rows = temp.select('pragma quick_check');

          corrupt = rows.length != 1 || rows[0].columnAt(0) != 'ok';
        } on SqliteException {
          corrupt = true;
        } finally {
          temp.dispose();
        }

        if (corrupt) {
          print('Database appears to be corrupt, deleting...');

          for (final suffix in ['', '-shm', '-wal', '-journal']) {
            final file = File('$path$suffix');
            if (file.existsSync()) {
              file.deleteSync();
            }
          }
        }
      }

      return NativeDatabase(File(path));
    },
  );
  return await isolate.connect(singleClientMode: true);
}

[rizaldi-wiratama]

Does the error happen when inserting into the same table

no it doesn't happen when inserting, that's the strange part.. only when get data it will throw that error, and based on log it does happens after the user re-open app, the last log before re-open app there's no issue on get or insert data

Thankyou for the suggestion, I appreciate it :) so in that code from my understanding, we can check if the DB is corrupt or not based on pragma quick_check and then delete the db if there's SqliteException right?

But in my case the database should not be lost, because we can insert data on offline mode.. so i think we need like a checkpoint where the db is not malformed (maybe we can tolerate only 1 / few data lost), so do we need backup the db by exporting every 5 minutes? or do you have any other suggestion?

[simolus3]

so in that code from my understanding, we can check if the DB is corrupt or not based on pragma quick_check and then delete the db if there's SqliteException right?

Yeah.

so i think we need like a checkpoint where the db is not malformed (maybe we can tolerate only 1 / few data lost), so do we need backup the db by exporting every 5 minutes? or do you have any other suggestion?

This sounds like a solution for a problem that really shouldn't exist. The original issue was about corruption issues in the web file system implementation (which also shouldn't exist, but at least that's my fault alone). If you're seeing this corruption with native databases, that's really weird. The most likely explanation for this might even be a broken device or the user manually editing the database file if it's accessible to them. Have you checked if any of this might apply to you?

[rizaldi-wiratama]

Yea I agree maybe this is specific device issue when the db is somehow malformed / user may trigger some clean up memory / data in the device..

I already check this & it seems maybe it refer to no. 6.3. Filesystem Corruption. but honestly i don't have a clue/evidence about it too

For now the problem solved by reinstall the app and we already have checkpoint data after re-login , thankyou @simolus3