SQLite ‘no such table: json_each’ error during notification check

[marvin-robot]

Opened from the Prefect Public Slack Community

niko: Hi, we just witnessed an error when running a prefect task locally on a windows 11 machine. This error is not popping up on mac. Do you know how to fix that?

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: json_each
[SQL: INSERT INTO flow_run_notification_queue (flow_run_notification_policy_id, flow_run_state_id) SELECT flow_run_notification_policy.id, CAST(? AS CHAR(36)) AS anon_1
FROM flow_run_notification_policy
WHERE flow_run_notification_policy.is_active IS 1 AND (flow_run_notification_policy.state_names = ? OR EXISTS (SELECT 1
FROM json_each(flow_run_notification_policy.state_names) AS json_each
WHERE json_each.value IN (?))) AND (flow_run_notification_policy.tags = ? OR EXISTS (SELECT 1
FROM json_each(flow_run_notification_policy.tags) AS json_each
WHERE json_each.value IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)))]
[parameters: ('8d01934d-c9c8-4f23-a3af-ec03ee1586a0', '[]', 'Pending', '[]')]
(Background on this error at: <https://sqlalche.me/e/14/e3q8>)

michael054: <@ULVA73B9P> open “SQLite ‘no such table: json_each’ error during notification check”

michael054: We’ll need to investigate this. Does it happen consistently?

Original thread can be found here.

[zanieb]

Version:             2.0b7
API version:         0.6.0
Python version:      3.8.10
Git commit:          eb9f17a8
Built:               Wed, Jun 22, 2022 12:07 PM
OS/Arch:             win32/AMD64
Profile:             default
Server type:         ephemeral
Server:
Database:          sqlite
SQLite version:    3.35.5

[sm-Fifteen]

As far as I can tell, the built-in version of SQLite used by Python on Windows, Python\PythonXX\DLLs\sqlite3.dll, doesn't have the json1 extension (which is where SQLite's json_each function comes from) statically built-in, which is why I can reproduce the same issue both with aiosqlite and pysqlite/sqlite3.

import aiosqlite
import asyncio
import sqlite3

async def async_main():
    async with aiosqlite.connect(':memory:') as my_db:
        res = await my_db.execute("""SELECT * FROM json_each('["foo", "bar", "baz"]')""")
        rows = await res.fetchall()
        print(rows)

def pysqlite_main():
    with sqlite3.connect(":memory:") as my_db:
        res = my_db.execute("""SELECT * FROM json_each('["foo", "bar", "baz"]')""")
        rows = res.fetchall()
        print(rows)

# asyncio.run(async_main())
pysqlite_main()

Both will throw sqlite3.OperationalError: no such table: json_each because the json_each function is not defined.

Which extensions are statically included at build time is not something SQLite has any way of letting you verify without just sending a query and checking if it fails. Building the json1 extension separately and loading it at runtime (which needs to be done per connection) could be an option, but I'm not sure how well that works with SQLAlchemy.

Another option would be to have Prefect bundle sqlite3.dll and ensure it gets loaded prior to the one included in the Python standard library. That can be done easily without changing the search path by just loading the DLL via ctypes first, since Python won't reload a library that it sees as "already in memory".

import ctypes
sqlite3_dll = ctypes.CDLL(r"C:\some\other\path\to\sqlite3.dll")
import sqlite3
import aiosqlite

You can tell by running something like strace python.exe -c 'import ctypes;ctypes.CDLL(r"C:\some\other\path\to\sqlite3.dll");import sqlite3' | grep sqlite.

Combining the two code samples above with the version of sqlite3.dll I happen to have laying around from DB Browser for SQLite fixes the error, both when using aiosqlite and sqlite3 directly.

[lukesdm]

Hi, I also hit this (Windows 10, Python 3.8, venv).

In other words, the JSON functions went from being opt-in with SQLite version 3.37.2 and earlier to opt-out with SQLite version 3.38.0 and later.

• https://www.sqlite.org/json1.html#compiling_in_json_support

As a workaround, I replaced C:\Python38\DLLs\sqlite3.dll (original version 3.31.1) with the latest version (3.39.1) from https://www.sqlite.org/download.html. So far it seems OK, but not sure if this will cause problems with other modules.

[zanieb]

Thanks for the details! We'll do some investigation to see what we can do here.

[sm-Fifteen]

@lukesdm: Ah, interesting that this might be connected to the Python version. From what the release notes of each version on the Python website say, the Windows and Mac installers of Python 3.9 and 3.10 include SQlite 3.37.2 since python/cpython#90083, while Python 3.8.10 and up uses SQLite 3.35.5 and Python 3.7.8 and up uses SQLite 3.32.2.

So unless I'm mistaken, none of these would include json1 by default, unless I'm missing something about how Python compiles its vendored libraries.

[zanieb]

I believe you'll also have no problems if you use conda since it bundles SQLite with each Python environment.

[lukesdm]

Thanks @sm-Fifteen and @madkinsz . Good to know. Sounds like conda is the way forward for Windows.

[M-Adrian-N]

how was above resolved? having installed prefect and run the example 'my_favotire_function" example flow I'm still getting the same error. Have tried sm-Fifteen's solution with the lastest sqlite3 dll from official website but no improvement.

[zanieb]

@M-Adrian-N I'd recommend using a conda environment to get the latest SQLite version. It's otherwise quite challenging to get it linked into Python correctly.

[sm-Fifteen]

how was above resolved? having installed prefect and run the example 'my_favotire_function" example flow I'm still getting the same error. Have tried sm-Fifteen's solution with the lastest sqlite3 dll from official website but no improvement.

You should be able to upgrade to the latest versions of Python 3.10 or 3.11, which should all ship with SQLite 3.40 SQLite 3.39.4 (so above the 3.38 threshold t have json1 included by default) to fix that problem.

[egeres]

I haven't look much into this, but I came across the error while using python 3.8 and prefect