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
- Failing test for #1383 — committed to groue/GRDB.swift by groue a year ago
- Mark opportunities for snapshot creation failure (including #1383) — committed to groue/GRDB.swift by groue a year ago
- Failing test for #1383 (async version) — committed to groue/GRDB.swift by groue a year ago
- When a wal snapshot can't be created, ValueConcurrentObserver fallbacks to plain reads This commit fixes #1383 — committed to groue/GRDB.swift by groue a year ago
- Document the intent of #1383 regression tests — committed to groue/GRDB.swift by groue a year ago
- Add test for the SQLite file provided by @kustra https://github.com/groue/GRDB.swift/issues/1383#issuecomment-1572897011 — committed to groue/GRDB.swift by groue a year ago
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.
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
(seesqlite3_wal_checkpoint_v2()
orPRAGMA wal_checkpoint
).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.