Invalid SQL: "no such table: pragma_database_list" on database page

[justinallen]

Don't think this has been covered here yet. I'm a little stumped with this one and can't tell if it's a bug or I have something misconfigured.

Oddly, when running locally the usual list of tables populates (i.e. at /charts a list of tables in charts.db). But when on the web server it throws an Invalid SQL error and "no such table: pragma_database_list" below.

All the url endpoints seem to work fine aside from this - individual tables (/charts/chart_one), as well as stored queries (/charts/query_one).

Not sure if this has anything to do with upgrading to Datasette 0.55, or something to do with our setup, which uses a metadata build script similar to the one for the 538 server, or something else.

[simonw]

That's really odd. What version of SQLite are you using on the server? You can tell by visiting https://your-site/-/versions

[simonw]

I just managed to replicate this bug on Glitch: https://nicar-2020.glitch.me/data

Invalid SQL
no such table: pragma_database_list

https://nicar-2020.glitch.me/-/versions says:

{
    "python": {
        "version": "3.7.10",
        "full": "3.7.10 (default, Feb 20 2021, 21:21:24) \n[GCC 5.4.0 20160609]"
    },
    "datasette": {
        "version": "0.55"
    },
    "asgi": "3.0",
    "uvicorn": "0.13.4",
    "sqlite": {
        "version": "3.11.0",
        "fts_versions": [
            "FTS4",
            "FTS3"
        ],
        "extensions": {
            "json1": null
        },
        "compile_options": [
            "ENABLE_COLUMN_METADATA",
            "ENABLE_DBSTAT_VTAB",
            "ENABLE_FTS3",
            "ENABLE_FTS3_PARENTHESIS",
            "ENABLE_JSON1",
            "ENABLE_LOAD_EXTENSION",
            "ENABLE_RTREE",
            "ENABLE_UNLOCK_NOTIFY",
            "ENABLE_UPDATE_DELETE_LIMIT",
            "HAVE_ISNAN",
            "LIKE_DOESNT_MATCH_BLOBS",
            "MAX_SCHEMA_RETRY=25",
            "OMIT_LOOKASIDE",
            "SECURE_DELETE",
            "SOUNDEX",
            "SYSTEM_MALLOC",
            "TEMP_STORE=1",
            "THREADSAFE=1"
        ]
    }
}

That's SQLite 3.11.0 from 2016-02-15 with no FTS5.

[simonw]

The Glitch server logs showed:

ERROR: conn=<sqlite3.Connection object at 0x7f88ebadd8f0>, sql = 'select seq, name, file from pragma_database_list() where seq > 0', params = None: no such table: pragma_database_list

[simonw]

Here's where I run that:

datasette/datasette/database.py

Lines 249 to 253 in 6f41c8a

	async def attached_databases(self):
	results = await self.execute(
	"select seq, name, file from pragma_database_list() where seq > 0"
	)
	return [AttachedDatabase(*row) for row in results.rows]

That's from when I added the --crossdb option in #1232: 6f41c8a#diff-4e20309c969326a0008dc9237f6807f48d55783315fbfc1e7dfa480b550e16f9R249

[simonw]

Aha! https://www.sqlite.org/pragma.html says:

The table-valued functions for PRAGMA feature was added in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite cannot use this feature.

[simonw]

Testing this on Glitch by adding https://github.com/simonw/datasette/archive/48d5e0e6ac8975cfd869d4e8c69c64ca0c65e29e.zip as a dependency. That fixed it.

[justinallen]

Fantastic. Thank you!