[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.