java.lang.Throwable: DatabaseException(no such table: cacheObject (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM cacheObject WHERE url = ?) sql 'SELECT * FROM cacheObject WHERE url = ?' args [https://dl.weshineapp.com/app_pcp/20190807/cd1f5aefd93a09136ca63278ee1fc3b4.png!150]}

[hkakutalua]

I'm having the same issue, i don't have any idea on how to reproduce this as it happens randomly.

The stacktrace:

Caused by com.kiwi.fluttercrashlytics.b: DatabaseException(no such table: cacheObject (code 1 SQLITE_ERROR): , while compiling: SELECT FROM cacheObject) sql 'SELECT FROM cacheObject' args []} at .wrapDatabaseException.wrapDatabaseException + 11(wrapDatabaseException.java:11) at SqfliteDatabaseFactoryImpl.wrapDatabaseException + 29(SqfliteDatabaseFactoryImpl.java:29) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.safeInvokeMethod + 184(_SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.java:184) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnRawQuery. + 366(txnRawQuery.java:366) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnSynchronized. + 300(txnSynchronized.java:300) at BasicLock.synchronized + 31(BasicLock.java:31) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnSynchronized + 296(_SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.java:296) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnRawQuery + 365(_SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.java:365) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.rawQuery + 115(_SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.java:115) at _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.query + 105(_SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.java:105) at CacheObjectProvider.getAllObjects + 128(CacheObjectProvider.java:128) at CacheStore.emptyCache + 151(CacheStore.java:151) at BaseCacheManager.emptyCache + 183(BaseCacheManager.java:183) at LogoutMiddleware._logoutUser + 34(LogoutMiddleware.java:34) at LogoutMiddleware.call + 24(LogoutMiddleware.java:24) at Store._createDispatchers. + 238(_createDispatchers.java:238) at NavigatorMiddleware.call + 29(NavigatorMiddleware.java:29) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at NavigatorMiddleware.call + 28(NavigatorMiddleware.java:28) at Store._createDispatchers. + 238(_createDispatchers.java:238) at ParentalControlMiddleware.call + 33(ParentalControlMiddleware.java:33) at Store._createDispatchers. + 238(_createDispatchers.java:238) at TicketMiddleware.call + 26(TicketMiddleware.java:26) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at TicketMiddleware.call + 25(TicketMiddleware.java:25) at Store._createDispatchers. + 238(_createDispatchers.java:238) at BookingMiddleware.call + 21(BookingMiddleware.java:21) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at BookingMiddleware.call + 20(BookingMiddleware.java:20) at Store._createDispatchers. + 238(_createDispatchers.java:238) at PaymentMiddleware.call + 25(PaymentMiddleware.java:25) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at PaymentMiddleware.call + 22(PaymentMiddleware.java:22) at Store._createDispatchers. + 238(_createDispatchers.java:238) at RoomMiddleware.call + 37(RoomMiddleware.java:37) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at RoomMiddleware.call + 36(RoomMiddleware.java:36) at Store._createDispatchers. + 238(_createDispatchers.java:238) at AccountExistenceVerificationMiddleware.call + 24(AccountExistenceVerificationMiddleware.java:24) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at AccountExistenceVerificationMiddleware.call + 23(AccountExistenceVerificationMiddleware.java:23) at Store._createDispatchers. + 238(_createDispatchers.java:238) at LoginMiddleware.call + 19(LoginMiddleware.java:19) at Store._createDispatchers. + 238(_createDispatchers.java:238) at EventMiddleware.call + 24(EventMiddleware.java:24) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at EventMiddleware.call + 22(EventMiddleware.java:22) at Store._createDispatchers. + 238(_createDispatchers.java:238) at TheaterMiddleware.call + 30(TheaterMiddleware.java:30) at _AsyncAwaitCompleter.start + 49(_AsyncAwaitCompleter.java:49) at TheaterMiddleware.call + 28(TheaterMiddleware.java:28) at Store._createDispatchers. + 238(_createDispatchers.java:238) at Store.dispatch + 250(Store.java:250) at SettingsViewModel.logout + 29(SettingsViewModel.java:29) at SettingsPage.build.. + 67(.java:67) at _LogoutEntry.build. + 92(build.java:92) at _InkResponseState._handleTap + 635(_InkResponseState.java:635) at _InkResponseState.build. + 711(build.java:711) at GestureRecognizer.invokeCallback + 182(GestureRecognizer.java:182) at TapGestureRecognizer._checkUp + 365(TapGestureRecognizer.java:365) at TapGestureRecognizer.handlePrimaryPointer + 275(TapGestureRecognizer.java:275) at PrimaryPointerGestureRecognizer.handleEvent + 455(PrimaryPointerGestureRecognizer.java:455) at PointerRouter._dispatch + 75(PointerRouter.java:75) at PointerRouter.route + 102(PointerRouter.java:102) at _WidgetsFlutterBinding&BindingBase&GestureBinding.handleEvent + 218(_WidgetsFlutterBinding&BindingBase&GestureBinding.java:218) at _WidgetsFlutterBinding&BindingBase&GestureBinding.dispatchEvent + 198(_WidgetsFlutterBinding&BindingBase&GestureBinding.java:198) at _WidgetsFlutterBinding&BindingBase&GestureBinding._handlePointerEvent + 156(_WidgetsFlutterBinding&BindingBase&GestureBinding.java:156) at _WidgetsFlutterBinding&BindingBase&GestureBinding._flushPointerEventQueue + 102(_WidgetsFlutterBinding&BindingBase&GestureBinding.java:102) at _WidgetsFlutterBinding&BindingBase&GestureBinding._handlePointerDataPacket + 86(_WidgetsFlutterBinding&BindingBase&GestureBinding.java:86) at ._rootRunUnary._rootRunUnary + 1136(_rootRunUnary.java:1136) at _CustomZone.runUnary + 1029(_CustomZone.java:1029) at _CustomZone.runUnaryGuarded + 931(_CustomZone.java:931) at ._invoke1._invoke1 + 250(_invoke1.java:250) at ._dispatchPointerDataPacket._dispatchPointerDataPacket + 159(_dispatchPointerDataPacket.java:159)

[hkakutalua]

I have a theory of why this is happening.

In Android, the recommendation is maintaining a single and persistent connection when using SQLite. https://github.com/tekartik/sqflite/blob/master/sqflite/doc/usage_recommendations.md https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_db.md#prevent-database-locked-issue

My app was using two plugins, flutter_cache_manager and Floor ORM. Both use the plugin sqflite, so in some cases they end up using simultaneously SQLite in the platform side, with different connections.

This is probably causing some reading lock, as an exception is thrown with the database tables being reported as not found with one side (flutter_cache_manager) or another (floor), but not both.

So, i ended up creating my own cache implementation that does not uses SQLite, instead relying on the last accessed date of a cached file to know if it will be deleted.

[zhancheng]

@Henry-Keys yes, you are right! i think this plugin should avoid i/o database with other plugin at the same time. it should not be the developer`s duty.

[petermichaux]

@Henry-Keys I was worried about multiple SQLite databases. Is your cache implementation available as open source?

[hkakutalua]

@Henry-Keys I was worried about multiple SQLite databases. Is your cache implementation available as open source?

Is not available as a plugin, but this the code i ended up writing to solve the problem:

import 'dart:async';
import 'dart:io';
import 'dart:typed_data';

import 'package:path/path.dart' as path;
import 'package:path_provider/path_provider.dart';
import 'package:crypto/crypto.dart';

import 'cache_store.dart';

class FileCacheStore implements CacheStore {
  static const String _cacheDirectoryName = "file_cache";
  static const Duration _cleanupInterval = Duration(seconds: 30);

  final Duration filesLifetime = Duration(days: 30);
  final int maxNumberOfCachedFiles = 100;

  FileCacheStore._() {
    Timer.periodic(
      _cleanupInterval, (_) async {
        try {
          print("Cleanning.......");
          await _clearDueFiles();
        } on FileSystemException catch(e) {
          print("On FileCacheStore: ${e.toString()}");
        }
      },
    );
  }

  static FileCacheStore getInstance() {
    if(_instance == null) {
      _instance = FileCacheStore._();
    }
    return _instance;
  }

  @override
  Future<Uint8List> retrieveFile(String identifier) async {
    final file = await _buildFileFromIdentifier(identifier);

    if (await file.exists()) {
      return await file.readAsBytes();
    } else {
      return null;
    }
  }

  @override
  Future<void> cacheFile(String identifier, Uint8List fileBytes) async {
    final file = await _buildFileFromIdentifier(identifier);
    await file.writeAsBytes(fileBytes);
  }

  @override
  Future<void> removeFile(String identifier) async {
    final file = await _buildFileFromIdentifier(identifier);

    if(file.existsSync()) {
      file.deleteSync();
    }
  }

  @override
  Future<void> clearCache() async {
    final cacheDirectory = await _getCacheDirectory();
    await cacheDirectory.delete(recursive: true);
  }

  Future<File> _buildFileFromIdentifier(String identifier) async {
    final cacheDirectoryPath = (await _getCacheDirectory()).path;
    final identifierConvertedToSha1 = sha1
        .convert(identifier.codeUnits).toString();
    final filePath = path.join(cacheDirectoryPath, identifierConvertedToSha1);

    return File(filePath);
  }

  Future<Directory> _getCacheDirectory() async {
    final tempDirectory = await getTemporaryDirectory();
    final cacheDirectory = Directory(path.join(tempDirectory.path, _cacheDirectoryName));

    if (!(await cacheDirectory.exists()))
      await cacheDirectory.create(recursive: true);

    return cacheDirectory;
  }

  Future<void> _clearDueFiles() async {
    final cacheDirectory = await _getCacheDirectory();
    var cachedFiles = <_FileWithAccessDate>[];

    await for (final fileSystemEntity in cacheDirectory.list()) {
      final fileStat = await fileSystemEntity.stat();
      if (fileStat.type == FileSystemEntityType.file) {
        final file = File(fileSystemEntity.path);
        final fileLastAccessDate = fileStat.accessed;

        if (DateTime.now().difference(fileLastAccessDate) > filesLifetime) {
          if (!(await file.exists()))
            return;

          await file.delete();
          print("Deleted expired file: ${file.path}");
        } else {
          cachedFiles
              .add(_FileWithAccessDate(file, fileLastAccessDate));
        }
      }
    }

    cachedFiles
        .sort((a, b) => a.lastAccessDate.compareTo(b.lastAccessDate));

    if (cachedFiles.length > maxNumberOfCachedFiles) {
      int filesToDeleteCount = cachedFiles.length - maxNumberOfCachedFiles;
      for (int i = 0; i < filesToDeleteCount; i++) {
        var cachedFile = cachedFiles[i].file;
        if (await cachedFile.exists()) {
          await cachedFile.delete();
          print("Deleted exceeded file: ${cachedFile.path}");
        }
      }
    }
  }
}

class _FileWithAccessDate {
  final File file;
  final DateTime lastAccessDate;

  _FileWithAccessDate(this.file, this.lastAccessDate);
}