Hi,
When I run the following code:
let bbAlias=TableAlias()
let request = Element.select(min(bbAlias[ElementBoundingBox.Columns.minX]),
max(bbAlias[ElementBoundingBox.Columns.maxX]),
min(bbAlias[ElementBoundingBox.Columns.minY]),
max(bbAlias[ElementBoundingBox.Columns.maxY]))
.joining(required: Element.boundingBox.aliased(bbAlias))
.filter(images: CollectionOfOne(image.id!))
if let row = try Row.fetchOne(db, request) { }
I occasionally get the following error:
SQLite error 21: Can't run statement that requires a customized authorizer from a cursor - while executing PRAGMA main.table_xinfo("elementBoundingBox")
If I run the same code a second time it always (up until now) executes successfully.
The odd thing is that when the error is not thrown there is also no PRAGMA SQL executed, just the SQL that the above query generates.
How should I proceed from here to find the cause of this problem?
GRDB: 5.12.0
Xcode: 13.2.1
Thanks in advance.
Kind regards,
Remco Poelstra
Hello @remcopoelstra,
You found a GRDB bug, thank you! This error is not supposed to be thrown while running such a request.
How should I proceed from here to find the cause of this problem?
It is unclear how the bug is triggered. Will you please provide a minimal reproducible example?
Hi @gwendal.roue,
I'll try to do my best, but the application is huge and it seems it relies on something else in my application (the bug is only triggered on first run with my app in a certain state).
In the meantime, I did find out some other pieces:
If it goes wrong the trace is:
PRAGMA main.foreign_key_list("element")
SELECT length(data) FROM 'main'.'elementBoundingBox_node' WHERE nodeno = 1
ROLLBACK TRANSACTION
While if it goes fine the trace is:
PRAGMA main.foreign_key_list("element")
PRAGMA main.table_xinfo("elementBoundingBox")
PRAGMA main.table_xinfo("element")
SELECT .......
I'll try to condense the code....
It seems I was wrong about this part.... Must have mis-read.
Yes, please. Thanks for your help!
Can I send you the condensed code in private?
Sure! You can send a private message right from this site: click my username, and then the "Message" button.
Are yous sure? I only get 'This user's public profile is hidden.'
? This is not intended... Let me check my settings...
Thanks for your sample code, @remcopoelstra.
The problem is the following:
- You run a request that involves a virtual R*Tree table with an association.
- This association is defined with a
ForeignKey that does not specify the column in the referenced table. GRDB thus uses the primary key of the referenced table. To grab this primary key, GRDB has to inspect the database schema, with the table_xinfo pragma.
- Because the table is a virtual table, the
table_xinfo pragma requires a lot of authorizations (when it requires none for regular tables).
- Those authorizations are presented to the
TransactionObserver in your app (your Document class), which returns true for all inputs in its observes(eventsOfKind:) method.
- One of those authorizations is
SQLITE_DELETE, meaning that your observer is interested in database deletions.
- Because your observer is interested in database deletions, GRDB has to make sure deletions are notified to your observer. To do so, it disables the Truncate Optimization. Disabling this optimization requires running the
table_xinfo statement with a specific authorization callback.
There lies the error: GRDB does not support iterating statement results with an authorization callback (execute can, but fetchCursor and derived methods such as fetchAll currently can't).
This explains the error. The reason why the error is not triggered the second time is that the same table_xinfo pragma does not require authorizations on the second run (maybe because of some internal SQLite cache). Consequently, your transaction observer is not triggered, and GRDB does not have to disable the truncate optimization. It becomes possible to run the pragma to completion, find the primary key of the associated table, and proceed with your request.
As you can see, this is a rather complex scenario, in a very particular setup. Maybe it can be solved at the GRDB level. Nevertheless, I suggest your TransactionObserver stops returning true for all database events (aka authorizations). Not only this will solve your error, but you'll enhance the performances of your app.
EDIT
This association is defined with a ForeignKey that does not specify the column in the referenced table. GRDB thus uses the primary key of the referenced table. To grab this primary key, GRDB has to inspect the database schema, with the table_xinfo pragma.
When I specify the referenced column, we get an error later. Requests that involve the R*Tree table behave as if they were not read-only, trigger the transaction observer, require truncate optimization prevention, and eventually have all fetches fail.
That's why I really think the best solution is at the level of the transaction observer.
Thanks for looking into this problem @gwendal.roue!
In the real code there are checks for the events in the observer to see whether I'm interested in them. Unfortunately, deletes from the R*Tree table belong to them.
I also tried adding the referenced column, but came to the same conclusion 
.
I check for the R*Tree table in the observer like this:
if tableName.hasPrefix("elementBoundingBox") { return true }
Do you know about a general fix that might help here? Or do I have to track deletions from the R*Tree table myself?
I get it.
You can "warm up" your SQLite connection very early in its lifetime, as below:
// Workaround https://forums.swift.org/t/54371
// Warm up SQLite and GRDB caches before database
// observation has started.
try database.write { db in
_ = try db.columns(in: "elementBoundingBox")
}
database.add(transactionObserver: ...)
Meanwhile, I really don't know yet what to do at the GRDB level.
I can confirm that this works very well!
Many thanks!
1 Like
Hello @remcopoelstra, I have the greatest difficulties reproducing the problem - only your particular SQLite file triggers this issue. May I ask you to join SQLite error 21: Can't run statement that requires a customized authorizer from a cursor - while executing `PRAGMA table_xinfo` · Issue #1124 · groue/GRDB.swift · GitHub, so that you explain with more details how this database file is generated? Thank you in advance.