How to ensure database not in use when using erase

[bellebethcooper]

Hello! I think I'm probably misunderstanding something, or missing a step, but I'm stuck trying to erase my GRDB database when a user logs out of my app, as the database seems to be in use. I haven't found any clues in the code or docs as to what I'm doing wrong, so I'd appreciate any pointers! Thanks!

What did you do?

When the user logs out of my app, I tried to erase the database like so:

if let dbQueue = dbQueue {
            do {
                try dbQueue.erase()
            } catch {
                print("error erasing db: \(error)")
            }
        }

What did you expect to happen?

All data would be removed from the database, so it's clean for the next user logging in

What happened instead?

I got the following error:

SQLite error 1: destination database is in use

Environment

GRDB flavor(s): GRDB
GRDB version: 3.2
Installation method: CocoaPods
Xcode version: 10.0
Swift version: 4.2
Platform(s) running GRDB: iOS
macOS version running Xcode: 10.13.6

[groue]

Hello @bellebethcooper,

The erase method has been introduced recently, after your much welcomed suggestion. It is tested, and I happily put DatabaseMigrator.eraseDatabaseOnSchemaChange to some good use. We generally know that it works.

Well, we know that erasing a database works when it happens during migration, very early in the database lifetime.

Now your application erases the database after it has been used for a while. That's an untested setup. Let's find out what's wrong!

The "destination database is in use" snippet appears once in SQLite source code:

/*
** Check that there is no open read-transaction on the b-tree passed as the
** second argument. If there is not, return SQLITE_OK. Otherwise, if there
** is an open read-transaction, return SQLITE_ERROR and leave an error 
** message in database handle db.
*/
static int checkReadTransaction(sqlite3 *db, Btree *p){
  if( sqlite3BtreeIsInReadTrans(p) ){
    sqlite3ErrorWithMsg(db, SQLITE_ERROR, "destination database is in use");
    return SQLITE_ERROR;
  }
  return SQLITE_OK;
}

So it looks like your app has an "open read-transaction" somewhere, whatever it means.

Does it ring a bell to you?

Is there any other connection (instance of DatabaseQueue or DatabasePool) for the same database?

Could you please run the following code, and tell if it prints anything?

import SQLite3

do {
    try dbQueue.erase()
} catch {
    dbQueue.inDatabase { db in
        var stmt: SQLiteStatement? = sqlite3_next_stmt(db.sqliteConnection, nil)
        while stmt != nil {
            print(String(cString: sqlite3_sql(stmt)))
            stmt = sqlite3_next_stmt(db.sqliteConnection, stmt)
        }
    }
}

[bellebethcooper]

Thanks very much for the quick response! I'd forgotten that erase was introduced with DatabaseMigrator.eraseDatabaseOnSchemaChange, but I remember seeing that in the PR now. (I use DatabaseMigrator.eraseDatabaseOnSchemaChange often, and find it so convenient—thanks again!)

I ran the code you suggested and it did print four lines. I'm not sure if they'll mean anything to you, since they're obviously specific to my code, but here they are just in case:

UPDATE "folder" SET "name"=?, "parent"=?, "isChild"=?, "count"=?, "colour"=?, "indentLevel"=? WHERE "id"=?
INSERT INTO "folder" ("isChild", "indentLevel", "name", "colour", "id", "count", "parent") VALUES (?,?,?,?,?,?,?)
DELETE FROM "folder" WHERE "id"=?
SELECT 1 FROM "folder" WHERE "id"=?

I declared my dbQueue variable in my AppDatabase file, as you recommend for iOS apps, and use the same dbQueue variable everywhere, and never use a DatabasePool so I don't think I'm opening another connection, but is there something I'm missing that I need to do to close a connection after reading from the database, perhaps?

Thanks again for your help!

[groue]

Thanks for your detailed answer.

Will you run another experiment towards an eventual resolution of the issue?

The printed statements are statements that are not finalized (cleaned up after use). They are cached by GRDB, as part of its regular operations. They help optimize record operations (in our case, update, insert, delete, and check for existence).

I wonder whether those cached statements could hold some lock of any kind on the database, preventing the erase to complete successfully.

To check this hypothesis, let's try to clean the database cache before erasing:

do {
    dbQueue.inDatabase { db in
        db.clearSchemaCache()
    }
    try dbQueue.erase()
} catch {
    print("error erasing db: \(error)")
}

If it works, we'll have to update GRDB so that it automatically performs this cleanup in the erase method. And meanwhile, you'll have a workaround. But let's run the experiment first!

[groue]

is there something I'm missing that I need to do to close a connection after reading from the database, perhaps?

Generally, no: eventual cleanup is the job of the library, not of the application. GRDB is supposed to do the "correct thing" whenever possible, in order to free the app developer from thinking too much about it (unless really special needs). It's a "toolkit for SQLite databases, with a focus on application development", not a DIY SQLite wrapper :-)

[bellebethcooper]

@groue Thanks very much for your help with this! When doing the next test you requested, I found that some of my code does seem to be accessing the database after the log out method is called. I'm still working on fixing this so I can determine whether that will fix the issue or I still need this extra clearSchemaCache call as well (which did stop the erase error, I believe). I'll update ASAP.

[bellebethcooper]

Okay, so even after removing the code that was potentially trying to access the database after I'd started the log out process, I'm still getting an error when using erase. Adding clearSchemaCache before erase stops the error from occurring. Thanks again for your help on this!

[groue]

Good news! All right, I'll ship a fix soon. Thanks for asking, and also for your debugging help :-)

[bellebethcooper]

Thanks very much!

[groue]

Follow up in #425