search

tekartik / sqflite

SQLite flutter plugin
BSD 2-Clause "Simplified" License
2.86k stars 524 forks source link

DatabaseException(database_closed 1) #1122

Open Wisdom9596 opened 1 month ago

Wisdom9596 commented 1 month ago

Hi Team ! For 3 days I have been facing an error: DatabaseException(database_closed 1). Please help me!

Here is my code below :

class HomePageState extends State<HomePage> {

   Database? _database;
   List<HymnsItem> dataHymns = [];
   List<SermonsItem> dataSermons = [];
   late List data;

   @override
    void initState() {
      super.initState();
      Timer.run(() {
      _initDatabase();
      homeSync();
      autoRefresh();
    });
   }

  @override
   void dispose() {
      //homeSync();
      _refresh.cancel();
      super.dispose();
   }

  Future<void> _initDatabase() async {
    try {
      _database = await openDatabase(
          join(await getDatabasesPath(), 'app.db'), version: 1,
          onCreate: (Database db, int version) async {
            // When creating the db, create the table
            await db.execute('CREATE TABLE hymns (idHymn TEXT PRIMARY KEY, numHymn TEXT, title TEXT)');
            await db.execute('CREATE TABLE sermons (idSerm TEXT PRIMARY KEY, title TEXT)');
          }
      );
      print("Database opened.");  // Print successful
  }

   Future<void> _ensureDatabaseIsOpen() async {
      if (_database == null || !_database!.isOpen) {
        await _initDatabase();
      } 
   }

   Future<void> _updDbHymn(List<HymnsItem> dataHymns) async {
    await _ensureDatabaseIsOpen();
    try {
      print('Database state before insertion: ${_database?.isOpen}');  // Correctly displays the "True" value
      await _database!.transaction((txn) async {   // Line generating the error
        await txn.rawDelete('DELETE FROM hymns');
        for (final hymn in dataHymns) {
          await txn.rawInsert(
            'INSERT OR REPLACE INTO hymns(idHymn, numHymn) VALUES(?,?)',
            [hymn.idHymn, hymn.numHymn],
          );
        }
      });
    }  on DatabaseException catch (e) {
      print('Erreur lors de l\'insertion Hymn : $e');   // Shows the error : DatabaseException(database_closed 1).
    }
   }

   Future<void> _updDbSermon(List<SermonsItem> dataSermons) async {
    await _ensureDatabaseIsOpen();
    try{
      print('Database state before insertion: ${_database?.isOpen}'); // Correctly displays the "True" value
      await _database!.transaction((txn) async {   // Line generating the error
        await txn.rawDelete('DELETE FROM sermons');
        for (final sermon in dataSermons) {
          await txn.rawInsert(
            'INSERT OR REPLACE INTO sermons(idSerm, title,...) VALUES(?,?)',
            [sermon.idSerm, sermon.title],
          );
        }
      });
    } on DatabaseException catch (e) {
      print('Erreur lors de l\'insertion dans Sermon : $e');   // Shows the error : DatabaseException(database_closed 1).
    }
   }

   void autoRefresh() async{
     _refresh = Timer.periodic(const Duration(seconds: 10), (Timer t) {
      homeSync();
    });
   }

   Future<void> homeSync() async {

     var response = await http.post(
          Uri.parse("https://mywebite.com"),
          body: {
            "user": '4',
            "lastsync": 'first',
          }
        );

        if(response.statusCode == 200) {

          String getResponse = const Utf8Decoder().convert(response.bodyBytes);
          data = json.decode(getResponse);

          if(data[0]['return'] == "true"){
              if(mounted){
                  setState(() {

                    dataHymns.clear();
                    dataSermons.clear();

                    if (data[2]['sermons'] != null) {
                      for (Map<String, dynamic> i in data[2]['sermons']) {
                        dataSermons.add(SermonsItem.fromJson(i));
                      }
                    }
                    if (data[5]['hymns'] != null) {
                      for (Map<String, dynamic> i in data[5]['hymns']) {
                        dataHymns.add(HymnsItem.fromJson(i));
                      }
                    }
                });
              }

              // UPDATE Database
              if(dataHymns.isNotEmpty){ await _updDbHymn(dataHymns); } // Line generating the error
              if(dataSermons.isNotEmpty){ await _updDbSermon(dataSermons); }   // Line generating the error

        }
      }

     ( ... )
     ( ... )
   }

According to my observation, the database is open but when a transaction must be launched, it closes

Please help me!

alextekartik commented 1 month ago

Weird indeed, can you:

Wisdom9596 commented 1 month ago

Weird indeed, can you:

  • specify on which platform you are running (android, ios, windows, other?)
  • try to create the parent folder before opening the database (await Directory(await getDatabasesPath()).create(recursive: true))
  • make sure you never call _database.close() anywhere
  • Add extra logs to see what is going on (quick way: databaseFactory = databaseFactory.debugQuickLoggerWrapper();) - see here
  • If too many inserts happen in the transaction, you can try to split in multiple transactions (or batches).

Thank you very much for the very quick response. I'm running Android platform and I didn't call _database.close() anywhere.

I will follow your instructions and get back to you.

Wisdom9596 commented 1 month ago

Weird indeed, can you:

  • specify on which platform you are running (android, ios, windows, other?)
  • try to create the parent folder before opening the database (await Directory(await getDatabasesPath()).create(recursive: true))
  • make sure you never call _database.close() anywhere
  • Add extra logs to see what is going on (quick way: databaseFactory = databaseFactory.debugQuickLoggerWrapper();) - see here
  • If too many inserts happen in the transaction, you can try to split in multiple transactions (or batches).

I forgot to specify that when I restart the application, everything works perfectly, the data is saved in the db without any problem. The problem occurs when launching the application for the first time

After following your instructions, here is the result :

Debug service listening on ws://127.0.0.1:65405/JWM4G7x9vxc=/ws
Syncing files to device MAR LX1A...
D/ProfileInstaller(32564): Installing profile for com.smartapp.demo
W/Gralloc3(32564): mapper 3.x is not supported
I/flutter (32564): openDatabase:({path: /data/user/0/com.smartapp.demo/databases/app.db, options: {readOnly: false, singleInstance: true, version: 1}, sw: 0:00:00.171502})
I/HwViewRootImpl(32564): removeInvalidNode jank list is null
I/flutter (32564): query(query:({db: 1, sql: PRAGMA user_version, result: [{user_version: 0}], sw: 0:00:00.067551}))
I/flutter (32564): execute(execute:({db: 1, sql: BEGIN EXCLUSIVE, result: {transactionId: 1}, sw: 0:00:00.157608}))
I/flutter (32564): query(query:({db: 1, txn: 1, sql: PRAGMA user_version, result: [{user_version: 0}], sw: 0:00:00.044720}))
I/flutter (32564): execute(execute:({db: 1, txn: 1, sql: CREATE TABLE hymns (idHymn TEXT PRIMARY KEY, numHymn TEXT, title TEXT), sw: 0:00:00.187385}))
I/flutter (32564): execute(execute:({db: 1, txn: 1, sql: CREATE TABLE sermons (idSerm TEXT PRIMARY KEY, title TEXT), sw: 0:00:00.089992}))
I/flutter (32564): execute(execute:({db: 1, txn: 1, sql: PRAGMA user_version = 1, sw: 0:00:00.004138}))
I/flutter (32564): execute(execute:({db: 1, txn: 1, sql: COMMIT, sw: 0:00:00.006571}))
I/flutter (32564): Database opened.
I/flutter (32564): Inserting Hymn...
I/flutter (32564): Database state before insertion: true
I/flutter (32564): execute(execute:({db: 1, sql: BEGIN IMMEDIATE, sw: 0:00:00.380641, error: DatabaseException(database_closed 1)}))
I/flutter (32564): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #1      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:485:16)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #2      _SqfliteDatabaseFactoryLogger._wrap (package:sqflite_common/src/logger/sqflite_logger.dart:777:20)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #3      _SqfliteDatabaseLogger._txnWrapSql (package:sqflite_common/src/logger/sqflite_logger.dart:743:18)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #4      SqfliteDatabaseMixinExt.txnBeginTransaction (package:sqflite_common/src/database_mixin.dart:361:20)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #5      SqfliteDatabaseMixin.beginTransaction (package:sqflite_common/src/database_mixin.dart:746:5)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #6      SqfliteDatabaseMixinExt._txnTransaction (package:sqflite_common/src/database_mixin.dart:333:13)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #7      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #8      SqfliteDatabaseMixin.tx
I/flutter (32564): Erreur lors de l'insertion Hymn : DatabaseException(database_closed 1)
I/flutter (32564): Inserting sermons...
I/flutter (32564): Database state before insertion: true
I/flutter (32564): execute(execute:({db: 1, sql: BEGIN IMMEDIATE, sw: 0:00:00.004927, error: DatabaseException(database_closed 1)}))
I/flutter (32564): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #1      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:485:16)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #2      _SqfliteDatabaseFactoryLogger._wrap (package:sqflite_common/src/logger/sqflite_logger.dart:777:20)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #3      _SqfliteDatabaseLogger._txnWrapSql (package:sqflite_common/src/logger/sqflite_logger.dart:743:18)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #4      SqfliteDatabaseMixinExt.txnBeginTransaction (package:sqflite_common/src/database_mixin.dart:361:20)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #5      SqfliteDatabaseMixin.beginTransaction (package:sqflite_common/src/database_mixin.dart:746:5)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #6      SqfliteDatabaseMixinExt._txnTransaction (package:sqflite_common/src/database_mixin.dart:333:13)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #7      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
I/flutter (32564): <asynchronous suspension>
I/flutter (32564): #8      SqfliteDatabaseMixin.tx
I/flutter (32564): Erreur lors de l'insertion dans Sermon : DatabaseException(database_closed 1)
I/flutter (32564): execute(execute:({db: 1, sql: BEGIN IMMEDIATE, sw: 0:00:00.027399, error: DatabaseException(database_closed 1)}))
alextekartik commented 1 month ago

Still weird. It seems here that it is creating the database. When you launch a second time, if should not go through onCreate, correct? Are you still getting the same error the second time you launch the app. Are you deleting the database somewhere?(which could cause the issue as deleting, closes the database). Can you try a simple select right after opening the database?

Wisdom9596 commented 4 weeks ago

I finally solved the problem.

I thought about your message a little above and I actually remembered that I used _database.close(), but in "main.dart".

I didn't know there was a way to update the database with onUpgrade. So I set up a method to delete the database when starting the app in main.dart so that it could be recreated in "home.dart" with the new database structure.

Despite the fact that I used "await async{}" to make tasks completely complete in "main.dart" before accessing "home.dart", the bug originated from there. I really didn't expect it to happen like this. Finally I deleted my method to finally use onUpgrade.

Problem solved! Thank you so much