Any reason "PRAGMA schema_version;" might return "attempt to write a readonly database"

(1) By Simon Willison (simonw) on 2023-04-11 23:37:03 [source]

I have a user who reported the following error running my Datasette application on their own server - they got a "attempt to write a readonly database" error, which appears to have been triggered by running the read operation "PRAGMA schema_version" to read the current version of the schema.

Here's the full log:

File "/usr/local/lib/python3.11/site-packages/datasette/views/base.py", line 89, in dispatch_request
await self.ds.refresh_schemas()
File "/usr/local/lib/python3.11/site-packages/datasette/app.py", line 371, in refresh_schemas
await self._refresh_schemas()
File "/usr/local/lib/python3.11/site-packages/datasette/app.py", line 386, in _refresh_schemas
schema_version = (await db.execute("PRAGMA schema_version")).first()[0]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/datasette/database.py", line 267, in execute
results = await self.execute_fn(sql_operation_in_thread)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/datasette/database.py", line 213, in execute_fn
return await asyncio.get_event_loop().run_in_executor(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/datasette/database.py", line 211, in in_thread
return fn(conn)
^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/datasette/database.py", line 237, in sql_operation_in_thread
cursor.execute(sql, params if params is not None else {})
sqlite3.OperationalError: attempt to write a readonly database

Since "PRAGMA schema_version" is a read and not a write operation I'm surprised to see this error message. Any ideas what could be going wrong here?

(2.1) By Simon Willison (simonw) on 2023-04-12 01:56:25 edited from 2.0 in reply to 1 [link] [source]

If it turns out I can't rely on PRAGMA schema_version to always give me an indication if a schema has changed without risk of it throwing this error in some environments, I'm going to need an alternative schema-change-detection mechanism.

Top contender at the moment is this:

schema = db.execute(
    "select group_concat(sql) from sqlite_master"
).fetchall()[0]
hash = hashlib.md5(schema).hexdigest()

Then compare that hash with the previous value to see if it's changed.

I ran a micro-benchmark (actually got ChatGPT to run a micro-benchmark) on this and found that the performance isn't an order of magnitude worse. Does anyone else have a better alternative?

Here's my write-up of the benchmark: https://simonwillison.net/2023/Apr/12/code-interpreter/

(3) By mgr (mgrmgr) on 2023-04-12 14:22:48 in reply to 1 [link] [source]

Did you try a select from the table valued function pragma_schema_version instead? Like

select * from pragma_schema_version;

Maybe the "traditional style call"

PRAGMA schema_version;

sometimes checks for writing, as it can be used like PRAGMA schema_version=xxx according to pragma_schema_version

... untested, just an idea ...

(4) By Richard Hipp (drh) on 2023-04-12 14:39:30 in reply to 1 [link] [source]

My guess: There is a hot journal that needs to be rolled back. Write privilege is needed to roll back the hot journal before the read operation can proceed.