GRDB.swift: SQL logic error when starting a ValueObservation immediately after opening a DatabasePool

TL;DR

GRDB v6.15.0 fixes this issue. If you can’t upgrade, and your app doesn’t perform SQLite checkpoints, here’s a workaround - to be run right after creating the DatabasePool:

let dbPool: DatabasePool = ... // your init code here

// https://github.com/groue/GRDB.swift/issues/1383
// FIXME: remove this after upgrading to GRDB v6.15.0
try dbPool.write { db in
    try db.execute(sql: """
        CREATE TABLE grdb_issue_1383 (id INTEGER PRIMARY KEY);
        DROP TABLE grdb_issue_1383;
        """)
}

What did you do?

I created a new DatabasePool with an existing .sqlite database file that did not have -wal or -shm files next to it. I noticed that by the time the DatabasePool was created, there was already a zero-length -wal file and an -shm file next to the .sqlite file.

I debugged into it, and executing this line creates the -wal and -shm files: https://github.com/groue/GRDB.swift/blob/v6.13.0/GRDB/Core/Database.swift#L420 Notably, this if condition evaluated to false, because a zero-length -wal file existed at this point: https://github.com/groue/GRDB.swift/blob/v6.13.0/GRDB/Core/DatabasePool.swift#L87

Then, I tried to start a ValueObservation with the DatabasePool, which failed on this line: https://github.com/groue/GRDB.swift/blob/v6.13.0/GRDB/Core/WALSnapshot.swift#L45

I suspect it fails because of this requirement:

One or more transactions must have been written to the current wal file since it was created on disk (by any connection). This means that a snapshot cannot be taken on a wal mode database with no wal file immediately after it is first opened. At least one transaction must be written to it first.

If I place a breakpoint at the end of the DatabasePool init, and execute this command from the debugger, the ValueObservation seems to work as expected: https://github.com/groue/GRDB.swift/blob/v6.13.0/GRDB/Core/DatabasePool.swift#L93

I’m not sure if this is important, but the .sqlite file was first created using GRDB 5.12.0, then I tried opening it with GRDB 6.13.0.

What did you expect to happen?

I expect a ValueObservation to work even if I start it right after opening a DatabasePool.

Environment

GRDB flavor(s): GRDB GRDB version: 6.13.0 Installation method: CocoaPods Xcode version: 14.2 Swift version: 5.7.2 Platform(s) running GRDB: iOS macOS version running Xcode: 12.6.5

About this issue

  • Original URL
  • State: closed
  • Created a year ago
  • Comments: 21 (13 by maintainers)

Commits related to this issue

Most upvoted comments

You have been a super user, too, very precise and challenging! It was a pleasure to deal with this issue with you, and quickly find a fix that will help all other users 🤝 I hope you’ll find other issues to chew on 😉

#1384 should be merged and shipped soon.

It is not necessary. I could reproduce the error with this test:

I stand corrected.

So this code creates an empty wal file?! 😮

You certainly shake my intuitions [^1]. And now I really understand your question about the frequency of this error. 🤯

[^1]: Am I crazy, or does SQLite exhibit different behavior, depending on its version and the operating system?

Hello @kustra,

I can reproduce your error (code 1, “SQL logic error”). Thank you for the report 👍

I think your interpretation is correct, although the SQLite documentation is slightly incomplete: missing wal files make it impossible to create a snapshot (and start an observation), but empty wal files as well.

The wal file becomes empty after a wal checkpoint has been performed with mode SQLITE_CHECKPOINT_TRUNCATE (see sqlite3_wal_checkpoint_v2() or PRAGMA wal_checkpoint).

I expect a ValueObservation to work even if I start it right after opening a DatabasePool.

Yes, this is expected to work. And starting an observation after a truncating checkpoint has been performed, too.

As far as I know, truncating checkpoints are not performed automatically by SQLite or GRDB, but I might be wrong. If your app is performing truncating checkpoints with the pragma or Database.checkpoint(_:on:), then maybe consider using another checkpointing mode, one that does not truncate the wal file, until the fix ships in GRDB.