[Android] attempt to write a readonly database

[haashem]

Hi,

I use sqflite to cache network responses, and delete the expired caches weekly.
After publishing the app, I got below crash reports in Firebase for some Android devices:

Fatal Exception: io.flutter.plugins.firebase.crashlytics.FlutterError
DatabaseException(attempt to write a readonly database (code 1032 SQLITE_READONLY_DBMOVED[1032])) sql 'INSERT OR REPLACE INTO network_cache ("key", response, timestamp) VALUES (?, ?, ?)' args [https://api.myapp.com/api/app?platform=a..., {"request":{"method":"HttpMethod.get","url":"https..., 2024-10-26T03:56:12.376333]

Here is how I create the db:

static Future<SqliteCacheDatabase> init() async {
    final dbDirectory = await getApplicationCacheDirectory();
    dbDirectory.create(recursive: true);
    final db = await openDatabase(
      join(dbDirectory.path, 'sqflite_cache.db'),
      onCreate: (db, version) {
        return db.execute('''
        CREATE TABLE $networkCacheTableName(
          key TEXT PRIMARY KEY,
          response TEXT,
          timestamp TEXT
        )
      ''');
      },
      version: 1,
    );
    return SqliteCacheDatabase._(db);
  }

I don't delete the db file after creation.

Do you think it can be result of parallel writes in the same table?

  @override
  Future<void> insert(CachedResponse cachedResponse, {required String key}) =>
      _database.instance.insert(
        SqliteCacheDatabase.networkCacheTableName,
        {
          'key': key,
          'response': jsonEncode(cachedResponse.response),
          'timestamp': cachedResponse.timestamp.toIso8601String(),
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );

[alextekartik]

sqflite on iOS and Android uses whatever SQLite bundled on the platform. It seems the UPSERT (ON CONFLICT) feature was added in SQLite 3.24 which might not be present in some Android version: See

• https://stackoverflow.com/a/4377116/563979
• https://github.com/tekartik/sqflite/blob/master/sqflite/doc/version.md

You can do a "SELECT sqlite_version()" to check the version.

I see 2 solutions:

• Do not use on CONFLICT and instead use transaction to catch the error on the insert and then perform the update
• Try sqflite_common_ffi along with sqlite3 and sqlite3 flutter libs. I have not tried it much but it should work

[alextekartik]

Forget that, I see the report is on Android 14 which should be fine. Parallel writes should be fine. sqflite serializes calls anyway. Still puzzled...my only "feeling" would be not to use the cache directory but instead use getDatabasesPath on Android and manage on your side to not make it too big. I don't know what is the Android behavior as when the cache is cleared (low storage situation, mabye your database is very big) but indeed if Android decides to delete the files, the error reported might happen. One alternative solution would be to catch this error and re-open the database when it happens. I'll try later (not very soon) to reproduce the issue by filling a db on the cache or by forcing file deletion but so far sqlite does not provide any automatic mechanism to handle this issue.

[haashem]

Surprisingly I faced the same error on iOS simulator while app was in state of launch, this is the first time I see this error during debug.

[alextekartik]

Do you confirm that you create the directory before opening the database ? (there is a missing await for dbDirectory.create in some code you posted above)

[haashem]

Wow 🤩,
great catch! I didn't notice it!
I must await dbDirectory.create(recursive: true);

I should look for a linter check to enforce explicitly use await to unawaited()!

[alextekartik]

unawaited_futures ! Defintly a good lint to add

[haashem]

Thanks for your point. I close the issue for now. if I face the error again, will let you know. but based on your point it should resolve the error.

[haashem]

Hi again,
The issue regressed again, and await dbDirectory.create(recursive: true); didn't fix.
The scenario is I cache network responses and when the app moves to background, I delete the expired caches.

When the db becomes readonly? do you think the db becomes readonly when a delete operation is in progress? this issue is only happening on Android.

[alextekartik]

as I said above, one reason could be the usage of the application cache directory that Android might cleanup this folder at any time. Can you try using getDatabasesPath() instead on Android?