Has anyone run prefect_test_harness using the default "test" profile (i.e. prefect profile use test) I'm getting the following error:

tests/test_prefect.py F
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> captured stderr >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
16:53:38.572 | INFO    | prefect - Starting temporary server on <http://127.0.0.1:8211>
See <https://docs.prefect.io/3.0/manage/self-host#self-host-a-prefect-server> for more information on running a dedicated Prefect server.

snip

File "/<snip>pipeline/.nox/test/lib/python3.10/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
    step.migration_fn(**kw)
  File "/<snip>pipeline/.nox/test/lib/python3.10/site-packages/prefect/server/database/migrations/versions/sqlite/2024_09_16_162719_4ad4658cbefe_add_deployment_to_global_concurrency_.py", line 51, in upgrade
    op.execute(sql)

snip

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "FROM": syntax error
[SQL:
            WITH deployment_limit_mapping AS (
                SELECT d.id AS deployment_id, l.id AS limit_id
                FROM deployment d
                JOIN concurrency_limit_v2 l ON l.name = 'deployment:' || d.id
            )
            UPDATE deployment
            SET concurrency_limit_id = dlm.limit_id
            FROM deployment_limit_mapping dlm
            WHERE deployment.id = dlm.deployment_id;

Test fails at call to with_prefect_test_harness() as follows:

.nox/test/lib/python3.10/site-packages/prefect/testing/utilities.py:157: in prefect_test_harness
    test_server.start(

snip

>                       raise RuntimeError(error_message)
E                       RuntimeError: Timed out while attempting to connect to ephemeral Prefect API server.

.nox/test/lib/python3.10/site-packages/prefect/server/api/server.py:822: RuntimeError

Hi Janet. I was able to run it just now with my

test

profile with this as output from `prefect version`:

Version:             3.0.10
API version:         0.8.4
Python version:      3.12.2
Git commit:          3aa2d893
Built:               Tue, Oct 15, 2024 1:31 PM
OS/Arch:             darwin/arm64
Profile:             test
Server type:         ephemeral
Pydantic version:    2.8.2
Server:
  Database:          sqlite
  SQLite version:    3.45.2

prefect profile inspect

output:

PREFECT_SERVER_ALLOW_EPHEMERAL_MODE='true'
PREFECT_API_DATABASE_CONNECTION_URL='sqlite+aiosqlite:///:
memory:'

I can pin pydantic and prefect to match your output, but I can't change the sqlite installed on my os -- this is what my version looks like (still failing)

Version:             3.0.10
API version:         0.8.4
Python version:      3.10.15
Git commit:          3aa2d893
Built:               Tue, Oct 15, 2024 1:31 PM
OS/Arch:             linux/x86_64
Profile:             test
Server type:         ephemeral
Pydantic version:    2.8.2
Server:
  Database:          sqlite
  SQLite version:    3.31.1

Yeah. Your SQLite is approaching 5 years old. I don’t see an obvious reason why that version wouldn’t work, but I’m not an expert there.

test_harness

runs against a sqlite db only. Code.

DB_PATH = "sqlite+aiosqlite:///" + str(Path(temp_dir) / "prefect-test.db")

This Prefect Docker Compose example repo from @Emil Rex Christensen uses PostgreSQL, but could be modified to use sqlite if you wanted to test there.

I think sqlite is coming from my OS, which is older. (Ubuntu 20.04)

Gotcha. Glad it’s working. Looking at our test matrix, I don’t see that we test against older sqlite versions. That said, this is the first I’ve heard of an issue on this front. Python 3.9 should work with the test harness. I find it tricky to get info about sqlite with Python, but Claude tells me Python 3.9.0 shipped with 3.33.0. It could be the case that the issue you saw above wasn’t related to the sqlite version, but I think it would be nice to have some more coverage or at least info there. I’ve made an issue here if you want to follow it or add any color.

I don't believe Claude as I have python 3.10 with sqlite 3.31.1. Swapping out sqlite is kinda tricky if you're not rebuilding python but you can put a new sqllite .so in your LD_LIBRARY_PATH. It won't be reported as the version that python was built with but it will pick up the library at runtime.

It appears it’s possible a sqlite version doesn’t get upgraded when a new version of Python is installed. Tests are public. You can see all the GH Actions in the repo. Here’s a large test matrix. And a representative run on a PR on CI here.

From the second link, all client tests run on postgres?

In this PR, yes. Not sure about in other workflows. Yeah, the sqlite version gets installed automatically. That’s why I suggested testing against a minimum version in the issue I made. But it sounds like it’s a pain to switch the version for our tests and deemed not worth the maintenance cost. Sorry I don’t have something more helpful regarding the minimum version number.

Yeah, here is how you get python to use a different version of sqlite -- https://shuaib.org/technical-guide/how-to-update-or-upgrade-sqlite3-version-in-python/ -- you build sqlite then put a new dynamic library in your python's lib directory. Or, you build python itself from scratch and have the version of sqlite you want baked in from the start. Yeah, it's work. I think it's a bug that a python from a pretty standard source (deadsnakes) doesn't work, but assuming that I'm going to jump through the hoops, I'd like to know which version I need to be sure to use!