SQLITE_NOTFOUND (12) error when following the recommend "Sharing a Database"

[RuiAAPeres]

What did you do?

Setting up the AppContainer following this guide: https://github.com/groue/GRDB.swift/blob/master/Documentation/SharingADatabase.md

What did you expect to happen?

I would expect the setup to successfully work.

What happened instead?

There's a an error thrown here:

try v1Migrator.migrate(dbPool)

The v1Migrator looks like the following:

  var migrator = DatabaseMigrator()
  migrator.eraseDatabaseOnSchemaChange = true

  migrator.registerMigration("v1", migrate: { db in
    try db.create(table: "table0") { table in

    }

    try db.create(table: "table1") { table in
    }
  })

  return migrator

Calling the initial method openSharedDatabase looks like this:

    do {
      let pathAppGroup = FileManager()
        .containerURL(forSecurityApplicationGroupIdentifier: groupIdentifier)!
        .appendingPathComponent("database.sqlite")

      let pool = try openSharedDatabase(at: pathAppGroup)
      self.pool = pool
    } catch {
      fatalError(error.localizedDescription)
    }

The actual error thrown:

▿ SQLite error 12: unknown operation
  ▿ extendedResultCode : 12 (unknown operation)
    - rawValue : 12
  ▿ message : Optional<String>
    - some : "unknown operation"
  - sql : nil
  - arguments : nil

Environment

GRDB flavor(s): GRDB
GRDB version: 5.3.0
Installation method: SPM
Xcode version: Version 12.4 (12D4e)
Swift version: 5.3.2
Platform(s) running GRDB: iOS
macOS version running Xcode:

[RuiAAPeres]

Removing migrator.eraseDatabaseOnSchemaChange = true no longer throws the error. But ideally I would like to continue iterating on the DB, so cleaning up would be ideal.

[groue]

Hello @RuiAAPeres,

Thank you for the reports. I'm glad you found a workaround until the issue has been understood.

[RuiAAPeres]

@groue do you see a workaround/idea/quick-win for this situation, while keeping migrator.eraseDatabaseOnSchemaChange = true?

[groue]

I'll have to investigate why you get this SQLITE_NOTFOUND error, and why it is caused by eraseDatabaseOnSchemaChange. Meanwhile, please live without eraseDatabaseOnSchemaChange, even if this means you'll have to, say, remove the app from disk when the migrations evolve (as we used to do before this convenience flag was introduced).

[groue]

Maybe your own investigations will help finding a better workaround, or even a quick fix! 😉

[RuiAAPeres]

Thanks @groue 🙇

[groue]

Why did I close it?

[groue]

Hello @RuiAAPeres,

I can reproduce the error. It comes from this line, which creates a temporary database on disk.

When you set the eraseDatabaseOnSchemaChange migrator flag, the migration process compares the current state of your database schema with the state of a temporary database on disk, migrated up to the same migration. If the schemas of the two databases are different, the migrator concludes that some migration was modified, and erases the database before migrating it from scratch.

The problem is that creating this temporary database on disk is prevented by the NSFileCoordinator. It looks like you can't write at some random place while you are under the control of a file coordinator.

Removing the eraseDatabaseOnSchemaChange solves the issue, because no temporary database is created.

---

We'll have to learn about NSFileCoordinator in order to solve this issue. I personally don't know enough about the topic yet. Ideas and experience are welcome!

---

I saw your Twitter thread that links to this issue: https://twitter.com/peres/status/1367773339004067842

I could not recommend enough to only compile eraseDatabaseOnSchemaChange = true in the Debug configuration (#if DEBUG). You rarely want to ship in the wild code that is designed to destroy your precious users' data.

[groue]

We'll have to learn about NSFileCoordinator in order to solve this issue. I personally don't know enough about the topic yet. Ideas and experience are welcome!

Not at all. The problem is this snippet from the sample code from https://github.com/groue/GRDB.swift/blob/master/Documentation/SharingADatabase.md:

var configuration = Configuration()
configuration.prepareDatabase { db in
    // Activate the persistent WAL mode so that
    // readonly processes can access the database.
    //
    // See https://www.sqlite.org/walformat.html#operations_that_require_locks_and_which_locks_those_operations_use
    // and https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal
    if db.configuration.readonly == false {
        var flag: CInt = 1
        let code = withUnsafeMutablePointer(to: &flag) { flagP in
            sqlite3_file_control(db.sqliteConnection, nil, SQLITE_FCNTL_PERSIST_WAL, flagP)
        }
        guard code == SQLITE_OK else {
            throw DatabaseError(resultCode: ResultCode(rawValue: code))
        }
    }
}

This preparation code fails for the temporary database created by the migrator, which is not in the WAL mode.

@RuiAAPeres, your quick fix is to change this snippet to:

// QUICKFIX VERSION
var configuration = Configuration()
configuration.prepareDatabase { db in
    // Activate the persistent WAL mode so that
    // readonly processes can access the database.
    //
    // See https://www.sqlite.org/walformat.html#operations_that_require_locks_and_which_locks_those_operations_use
    // and https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal
    if db.configuration.readonly == false {
        var flag: CInt = 1
        let code = withUnsafeMutablePointer(to: &flag) { flagP in
            sqlite3_file_control(db.sqliteConnection, nil, SQLITE_FCNTL_PERSIST_WAL, flagP)
        }
        guard code == SQLITE_OK || code == SQLITE_NOTFOUND else {
            //                  ~~~~~~~~~~~~~~~~~~~~~~~~~~
            // workaround https://github.com/groue/GRDB.swift/issues/931
            throw DatabaseError(resultCode: ResultCode(rawValue: code))
        }
    }
}

I'll look for a better solution which does not pollute application code as above.

[groue]

For some reason, temporary on-disk databases opened with an empty path "" do not support the WAL mode:

• PRAGMA journal_mode = WAL returns delete
• sqlite3_file_control(..., SQLITE_FCNTL_PERSIST_WAL, ...) returns SQLITE_NOTFOUND, which means this opcode is not understood/recognized/supported.

Conclusion: the migrator has to use a different kind of temp database: a regular one that the migrator will remove from disk after use.

The fix will ship soon. Meanwhile, the workaround is described above (ignore SQLITE_NOTFOUND).

[groue]

For anyone more curious about the topic: https://sqlite.org/forum/forumpost/f4f9d58515

[groue]

The fix has shipped in v5.6.0!