'Attempt to write a readonly database' error after a hot restart

[NearHuscarl]

Description

Failed to update sqlite database. Error message says

attempt to write a readonly database

But database object configuration is not (SqfliteDatabaseBase.isOpen = true, SqfliteDatabaseBase.isClosed = false and SqfliteDatabaseBase.readonly = false)

Steps to reproduce

1. Clone & run this minimal sample (~100 lines)
2. Click the QUERY and UPDATE button. Open the DEBUG CONSOLE in VSCode, you will see it prints the queried data successfully
3. Hot restart
4. Click the QUERY button (Successfully as normal). But clicking the UPDATE button will lead to this error

Exception has occurred.
SqfliteDatabaseException (DatabaseException(attempt to write a readonly database (code 1032 SQLITE_READONLY_DBMOVED[1032])) sql 'UPDATE Test SET phone = ?' args [667136]})

Note: This bug will occur after executing a hot restart, but doing a hot reload or restart completely (Stop and run again) seems to work

Error log

E/SQLiteLog( 7966): (1) fileHasMoved : original inode of /data/user/0/com.example.test_flutter/app_flutter/data.sqlite is (0)
E/SQLiteLog( 7966): (1) Stat of fd(97) : st_mode(100600) st_uid(10235) st_gid(10235) st_ino(329279)
E/SQLiteLog( 7966): (1) File name of fd(97) : /data/data/com.example.test_flutter/app_flutter/data.sqlite (deleted)
E/SQLiteLog( 7966): (1) Stat of /data/user/0/com.example.test_flutter/app_flutter/data.sqlite : st_mode (100600) st_uid (10235) st_gid (10235) st_ino(329473)
E/SQLiteLog( 7966): (1) Stat of /data/user/0/com.example.test_flutter/app_flutter : st_mode(40771) st_uid(10235) st_gid(10235) st_ino(329421)
E/SQLiteLog( 7966): (1) Stat of /data/user/0/com.example.test_flutter : st_mode(40700) st_uid(10235) st_gid(10235) st_ino(329276)
E/SQLiteLog( 7966): (1) Stat of /data/user/0 : st_mode(40771) st_uid(1000) st_gid(1000) st_ino(589826)
E/SQLiteLog( 7966): (1) Stat of /data/user : st_mode(40711) st_uid(1000) st_gid(1000) st_ino(589830)
E/SQLiteLog( 7966): (1) Stat of /data : st_mode(40771) st_uid(1000) st_gid(1000) st_ino(2)
E/SQLiteLog( 7966): (1032) statement aborts at 10: [UPDATE Test SET phone = ?] attempt to write a readonly database
E/flutter ( 7966): [ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(attempt to write a readonly database (code 1032 SQLITE_READONLY_DBMOVED[1032])) sql 'UPDATE Test SET phone = ?' args [667136]}
E/flutter ( 7966): #0      wrapDatabaseException 
package:sqflite/src/exception_impl.dart:12
E/flutter ( 7966): <asynchronous suspension>
E/flutter ( 7966): #1      SqfliteDatabaseFactoryImpl.wrapDatabaseException 
package:sqflite/src/factory_impl.dart:25
E/flutter ( 7966): #2      SqfliteDatabaseMixin.safeInvokeMethod 
package:sqflite/src/database_mixin.dart:188
E/flutter ( 7966): #3      SqfliteDatabaseMixin.txnRawUpdate.<anonymous closure> 
package:sqflite/src/database_mixin.dart:388
E/flutter ( 7966): #4      SqfliteDatabaseMixin.txnSynchronized.<anonymous closure> 
package:sqflite/src/database_mixin.dart:307
E/flutter ( 7966): #5      BasicLock.synchronized 
package:synchronized/src/basic_lock.dart:31
E/flutter ( 7966): #6      SqfliteDatabaseMixin.txnSynchronized 
package:sqflite/src/database_mixin.dart:303
E/flutter ( 7966): #7      SqfliteDatabaseMixin.txnWriteSynchronized 
package:sqflite/src/database_mixin.dart:325
E/flutter ( 7966): #8      SqfliteDatabaseMixin.txnRawUpdate 
package:sqflite/src/database_mixin.dart:387
E/flutter ( 7966): #9      SqfliteDatabaseExecutorMixin.rawUpdate 
package:sqflite/src/database_mixin.dart:128
E/flutter ( 7966): #10     SqfliteDatabaseExecutorMixin.update 
package:sqflite/src/database_mixin.dart:153
E/flutter ( 7966): #11     _SqfliteAttemptToWriteInReadonlyDatabaseState.build.<anonymous closure> 
package:test_flutter/src/sqflite_attempt_to_write_in_readonly_database.dart:95
E/flutter ( 7966): <asynchronous suspension>
E/flutter ( 7966): #12     _InkResponseState._handleTap 
package:flutter/…/material/ink_well.dart:706
E/flutter ( 7966): #13     _InkResponseState.build.<anonymous closure> 
package:flutter/…/material/ink_well.dart:789
E/flutter ( 7966): #14     GestureRecognizer.invokeCallback 
package:flutter/…/gestures/recognizer.dart:182
E/flutter ( 7966): #15     TapGestureRecognizer.handleTapUp 
package:flutter/…/gestures/tap.dart:486
E/flutter ( 7966): #16     BaseTapGestureRecognizer._checkUp 
package:flutter/…/gestures/tap.dart:264
E/flutter ( 7966): #17     BaseTapGestureRecognizer.handlePrimaryPointer 
package:flutter/…/gestures/tap.dart:199
E/flutter ( 7966): #18     PrimaryPointerGestureRecognizer.handleEvent 
package:flutter/…/gestures/recognizer.dart:467
E/flutter ( 7966): #19     PointerRouter._dispatch 
package:flutter/…/gestures/pointer_router.dart:76
E/flutter ( 7966): #20     PointerRouter._dispatchEventToRoutes.<anonymous closure> 
package:flutter/…/gestures/pointer_router.dart:117
E/flutter ( 7966): #21     _LinkedHashMapMixin.forEach  (dart:collection-patch/compact_hash.dart:379:8)
E/flutter ( 7966): #22     PointerRouter._dispatchEventToRoutes 
package:flutter/…/gestures/pointer_router.dart:115
E/flutter ( 7966): #23     PointerRouter.route 
package:flutter/…/gestures/pointer_router.dart:101
E/flutter ( 7966): #24     GestureBinding.handleEvent 
package:flutter/…/gestures/binding.dart:218
E/flutter ( 7966): #25     GestureBinding.dispatchEvent 
package:flutter/…/gestures/binding.dart:198
E/flutter ( 7966): #26     GestureBinding._handlePointerEvent 
package:flutter/…/gestures/binding.dart:156
E/flutter ( 7966): #27     GestureBinding._flushPointerEventQueue 
package:flutter/…/gestures/binding.dart:102
E/flutter ( 7966): #28     GestureBinding._handlePointerDataPacket 
package:flutter/…/gestures/binding.dart:86
E/flutter ( 7966): #29     _rootRunUnary  (dart:async/zone.dart:1138:13)
E/flutter ( 7966): #30     _CustomZone.runUnary  (dart:async/zone.dart:1031:19)
E/flutter ( 7966): #31     _CustomZone.runUnaryGuarded  (dart:async/zone.dart:933:7)
E/flutter ( 7966): #32     _invoke1  (dart:ui/hooks.dart:273:10)
E/flutter ( 7966): #33     _dispatchPointerDataPacket  (dart:ui/hooks.dart:182:5)

Information

• OS: Window 10
• IDE: VSCode
• Device: Galaxy A10
• Flutter version

$ flutter --version
Flutter 1.13.0 • channel dev • https://github.com/flutter/flutter.git
Framework • revision 09126abb22 (2 days ago) • 2019-12-03 17:43:00 -0800
Engine • revision 6179380243
Tools • Dart 2.7.0 (build 2.7.0-dev.2.1 a4d799c402)

[alextekartik]

The issue is about hot restart and deleting the database here. The current workaround is to open the database and close it before deleting it.

Maybe deleteDatabase (instead of calling File().delete) should do the proper cleanup but it is not the case neither. Since many are running into this, I guess I should fix deleteDatabase behavior (well that is not really a fix, it is a hotload issue where the db is not closed on hotrestart).

I will work on it.

[alextekartik]

As of sqflite 1.1.8, the behavior of deleteDatabase has slightly change, for the better hopefully as I don't consider this a breaking change.

It now supports Android restart and hot restart in general. If the database was opened previously as a single instance, the database is closed first, then deleted.

This means that the trick of opening the database before deletion is no longer needed.

[caseyryan]

The bug is still present. I couldn't find where the deleteDatabase() method is

[haashem]

I face this issue recently, and I don't have delete db functionality. I just cache network responses. and delete the responses weekly. (not the whole 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);
  }

[alextekartik]

There is indeed a complex mechanism to handle hot-restart (as the db is not closed on the native side). I'm puzzled by the 'dbmoved' response. @haashem on which platform are you experiencing the issue ? Also can you confirm (and it seems so) that you are never closing the db? Also can you print the content of getApplicationCacheDirectory() to see if the path changed (could it be clear by flutter?) and maybe try another dir (such as getDatabasesPath() or application document directory?), that will help investigation. Thanks!

[haashem]

Thanks for quick reply,

Yes, I confirm that I don't close the db. I create two database when app launches, each db is used for specific reason. one Is located in cache directory (so OS can delete it anytime) and one is created in documents directory (user contents).
The issue only happened for the db in cache directory. this db is only used to cache network responses. and maybe simultaneous write call happens at a same time (can it cause issue?).

I don't close the databases at all and changing path is not possible because the app is in production.

It only happened on Android devices.

I have opened a new related issue:
#1137