SELECT query on standard database with geopoly virtual table fails

[captn3m0]

Database: https://dbhub.io/captn3m0/bbmp-2023.db

The database is public, and uses the GEOPOLY extension. The table was created using CREATE VIRTUAL TABLE wards USING geopoly(No,Name).

Query: SELECT No FROM wards LIMIT 0,10

Running the query as a Visualization fails:

error executing query at 2:48:52 PM: SQL that modifies a database can only be used on Live databases

Running it over the API (/v1/query) results in the same response:

{
	"error": "SQL that modifies a database can only be used on Live databases"
}

[justinclift]

Interesting. I'll do some experimentation with that database on my local development setup, and figure out what's going wrong.

Hopefully it won't be too hard to fix. 😄

[justinclift]

Debugging this now, it looks like that query is (somehow) causing a bunch of INSERT, UPDATE, and DELETE statements to run. Debugging output from our SQLite Authorizer function (while running in my local dev desktop):

2023/12/18 15:16:19 SQLite Authorizer function - action 'Insert', table 'wards_node', function ''
2023/12/18 15:16:19 SQLite Authorizer function - action 'Delete', table 'wards_node', function ''
2023/12/18 15:16:19 SQLite Authorizer function - action 'Insert', table 'wards_rowid', function ''
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'nodeno'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Delete', table 'wards_rowid', function ''
2023/12/18 15:16:19 SQLite Authorizer function - action 'Insert', table 'wards_parent', function ''
2023/12/18 15:16:19 SQLite Authorizer function - action 'Delete', table 'wards_parent', function ''
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a0'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a1'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a2'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a3'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a4'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a5'
2023/12/18 15:16:19 SQLite Authorizer function - action 'Update', table 'wards_rowid', function 'a6'
2023/12/18 15:16:19 Error when running query by 'default' for database (default/bbmp-2023.db): 'no such column: No (SELECT No FROM wards LIMIT 0,10;) (SQL logic error)'

Naturally, INSERT/UPDATE/DELETE aren't super great in what's supposed to be a read only database.

I'm not (yet) sure how that SELECT is turning into a bunch of statements that modify the database. Will probably need to check out the Geopoly stuff next, as that'll probably clue me in.

Will spend some more time on this today, and hopefully figure things out. It might be as simple as just needing to add the geopoly extension to the server or something, which hopefully will change things to not try altering the database (for read only databases).

Will find out... 😄

[justinclift]

Oh hang on, we already compile SQLite with the geopoly extension (sorry, I forgot that). So it's not going to be completely that simple.

[justinclift]

Attacking this problem from a different direction... the query seems to execute as expected when the database is uploaded as a live database though.

Maybe go with that approach instead? 😄

[captn3m0]

I might have tried that. Not 100% sure, will try again, but I remember being stuck with /query: Do live databases support the /query endpoint? I wanted to use that to avoid hitting /execute (with the possibility of writes) entirely.

[justinclift]

Yep, they do support the query end point. With that time it got stuck, how long ago was that?

We were having backend problems with RabbitMQ a while ago, which completely wedged everything when it occurred. We replaced RabbitMQ (about a week ago), so if that's what you hit then you're probably ok to use Query calls again. 😄

[justinclift]

Oh, why the avoidance of the Execute end point btw? Was that also getting stuck when you used it?

[justinclift]

@captn3m0 When you have a chance to try this stuff again, let me know if you come across any of the end points not working as expected.

And I just realised that when you said "remember being stuck" that you were probably meaning "conceptually" rather than server problems. Heh. 😉