GRDB.DatabaseError: SQLite error 1: no such table: {table name} - while executing `SELECT ...

[pschuette22]

Through test flight, debug builds, and general testing, I am able to use SQLiteData and it works beautifully.

In the production app, we see errors like:

GRDB.DatabaseError: SQLite error 1: no such table: sessions - while executing `SELECT "sessions"."start" AS "start", coalesce("sessions"."end", "sessions"."start") AS "end" FROM "sessions" WHERE (("sessions"."end") IS NOT (NULL)) AND ((((("sessions"."start") >= (?)) AND (("sessions"."start") <= (?))) OR (((coalesce("sessions"."end", "sessions"."start")) >= (?)) AND ((coalesce("sessions"."end", "sessions"."start")) <= (?)))) OR ((("sessions"."start") < (?)) AND ((coalesce("sessions"."end", "sessions"."start")) > (?)))) ORDER BY "sessions"."start" ASC` (Code: 1)

This is not isolated to our "sessions" table. We have so far been unable to reproduce the issue.

We are also leveraging swift dependencies and start the app like:

    init() {
        prepareDependencies {
            do {
                $0.defaultDatabase = try DatabaseBuilder.initialize()
            } catch {
                databaseError = error
            }
        }

Where the error is then logged after bootstrapping our monitors. No error has been logged here.

Has anyone seen this issue?

It seems to happen at a rate of about 0.5% of our users and seem to generally work itself out on subsequent launches.

sqlite-data version 1.3.0
swift-dependencies version 1.9.2

[stephencelis]

What does DatabaseBuilder.initialize() look like?

Are the devices logging errors low on memory or disk space, or are there other logged attributes that stand out?

We don't really do much in SQLiteData as far as the database connection is concerned than call down to GRDB's APIs, so I wonder if there are related discussions on GRDB that may help you?

[pschuette22]

DatabaseBuilder.initialize() loosely looks like:

    public static func initialize(
        named name: String = "db"
    ) throws -> any DatabaseWriter {
        var migrator = DatabaseMigrator()

        // -- Model1 migrations --
        migrator.registerMigration("Create Model1 tables") { db in
            try #sql(Model1.createTable).execute(db)
        }
        migrator.registerMigration("Some Model1 migrations") { db in
            try #sql(Model1.namedMigration).execute(db)
            ..
        }

        // -- Model2 migrations --
        migrator.registerMigration("Create Model2 table") { db in
            try #sql(Model2.createTable).execute(db)
        }
        migrator.registerMigration("Some Model2 migrations") { db in
            try #sql(Model2.namedMigration).execute(db)
            ...
        }
        ...

I am fairly certain this works as designed. There are several migrations other migrations, but since the app is generally quite stable, I don't believe the issue is here.

What I am exploring now is if my app extensions (which are sharing this database) may be creating a SQL lock issue on startup. I am able to repro a similar issue by initializing the database at the same time across 20 Tasks in a test.

I am experimenting with a file coordinator to try to lock the migration phase. I'll follow up if that works

[mbrandonw]

What I am exploring now is if my app extensions (which are sharing this database) may be creating a SQL lock issue on startup. I am able to repro a similar issue by initializing the database at the same time across 20 Tasks in a test.

Hi @pschuette22, are you aware of the complexities of putting SQLite databases in app groups for the purpose of using from multiple extensions? It sounds like that is what you are running into, and that article contains a lot of good information.

[pschuette22]

This was not something I was aware of. Thank you for the resource, I will dig into it more

[pschuette22]

Perhaps not my best work, but I was able to modify the database initialize function to pass 1000 simultaneous calling tasks. Without the locking I fail at about 5 consistently

public static func initialize(
        named name: String = "db"
    ) throws -> any DatabaseWriter {
        @Dependency(\.context) var context

        var configuration = Configuration()
        configuration.foreignKeysEnabled = true

#if DEBUG
        configuration.prepareDatabase { db in
            db.trace(options: .profile) {
                if context == .preview {
                    print("\($0.expandedDescription)")
                } else {
                    logger.debug("\($0.expandedDescription)")
                }
            }
        }
#endif
        var database: (any DatabaseWriter)?
        
        // Acquire migration lock to prevent race conditions across multiple processes
        let lockFileURL: URL? = if context == .live {
            Self.sharedGroupURL.appending(component: "\(name).migration.lock")
        } else if context == .test {
            URL.temporaryDirectory.appending(component: "test-\(name).migration.lock")
        } else {
            nil as URL?
        }
        
        if let lockFileURL {
            // Create lock file if it doesn't exist
            if !FileManager.default.fileExists(atPath: lockFileURL.path()) {
                FileManager.default.createFile(atPath: lockFileURL.path(), contents: nil)
            }
        }
                
        func initializeDatabase(configuration: Configuration) throws -> any DatabaseWriter {
            if context == .live {
                let path = Self.sharedGroupURL.appending(component: "\(name).sqlite").path()
                logger.info("open \(path)")
                return try DatabasePool(path: path, configuration: configuration)
            } else if context == .test {
                let path = URL.temporaryDirectory.appending(component: "test-\(name).sqlite").path()
                return try DatabasePool(path: path, configuration: configuration)
            } else {
                // In memory database for preview
                return try DatabaseQueue(configuration: configuration)
            }
        }

        // Coordinate migration using NSFileCoordinator for cross-process safety
        if let lockFileURL {
            let coordinator = NSFileCoordinator(filePresenter: nil)
            var coordinationError: NSError?
            var migrationError: Error?
            
            for _ in 0..<10 {
                coordinator.coordinate(writingItemAt: lockFileURL, options: .forMerging, error: &coordinationError) { url in
                    do {
                        database = try initializeDatabase(configuration: configuration)
                        logger.info("Acquired migration coordination lock via NSFileCoordinator")
                        try migrate(database!)
                    } catch {
                        logger.error("Coordinated init failed: \(error)")
                        migrationError = error
                    }
                }
                
                if let error = coordinationError {
                    logger.info("ERROR: Failed to acquire migration coordination: \(error.localizedDescription)")
                    coordinationError = nil
                } else if let error = migrationError {
                    logger.info("ERROR: Coordinated migrate failed: \(error)")
                    migrationError = nil
                } else {
                    coordinationError = nil
                    migrationError = nil
                    break
                }
                
                Thread.sleep(forTimeInterval: 0.05)
            }
            // None of the attempts succeeded
            // Throw the underlying error
            if let migrationError {
                throw migrationError
            } else if let coordinationError {
                throw coordinationError
            }

        } else {
            database = try initializeDatabase(configuration: configuration)
            // No coordination needed for preview contexts
            try migrate(database!)
        }
        
        if let database {
            return database
        } else {
            throw DatabaseError.coordinatedInitFailed
        }
    }

[pschuette22]

Quick follow up here.

As Brandon pointed out, the root issue was absolutely sharing the database file in an app group and accessing it directly from multiple processes. This is discussed in GRDBs documentation.

The root issue has been about 99% mitigated with a locking structure similar to the one I posted above, but it highlights the need to move this storage location and rethink / prioritize our data sharing strategy. In the meantime, we have traded runtime usage failures for a slight uptick in ANR. Not debilitating, but also clearly needs fixing.

Thank you Brandon and Stephen for the timely responses, help, and resources. I urge anyone else who is starting a project and using SQLiteData to not put the sqlite file in a shared container folder