Fresh installation yield sql error

GregoirePelegrin · October 19, 2022, 7:30am

Configuration

I am using Anaconda to manage my Python environments.
Conda version: 22.9.0
Platform: linux-64 (debian 11 “bullseye”)
Python version: 3.8

Installation

conda create --name prefect-env-3_8 python=3.8
conda activate prefect-env-3_8
conda install -c conda-forge prefect

Verification

prefect version
        
Version:             2.6.3
API version:         0.8.2
Python version:      3.8.13
Git commit:          9e7da96e
Built:               Tue, Oct 18, 2022 1:55 PM
OS/Arch:             linux/x86_64
Profile:             default
Server type:         <client error>

Here we already have something different than what we’re expecting by following the Installation Guide

Error

When trying to activate Orion:

prefect orion start

Error: sqlite3.OperationalError: table flow already exists

___ ___ ___ ___ ___ ___ _____    ___  ___ ___ ___  _  _
| _ \ _ \ __| __| __/ __|_   _|  / _ \| _ \_ _/ _ \| \| |
|  _/   / _|| _|| _| (__  | |   | (_) |   /| | (_) | .` |
|_| |_|_\___|_| |___\___| |_|    \___/|_|_\___\___/|_|\_|

Configure Prefect to communicate with the server with:

    prefect config set PREFECT_API_URL=http://127.0.0.1:4200/api

View the API reference documentation at http://127.0.0.1:4200/docs

Check out the dashboard at http://127.0.0.1:4200



INFO:     Started server process [8025]
INFO:     Waiting for application startup.
ERROR:    Traceback (most recent call last):
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 108, in execute
    self._adapt_connection._handle_exception(error)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 236, in _handle_exception
    raise error
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 90, in execute
    self.await_(_cursor.execute(operation, parameters))
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
    value = await result
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.OperationalError: table flow already exists

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/starlette/routing.py", line 645, in lifespan
    async with self.lifespan_context(app):
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/starlette/routing.py", line 540, in __aenter__
    await self._router.startup()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/starlette/routing.py", line 622, in startup
    await handler()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/api/server.py", line 341, in run_migrations
    await db.create_db()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/interface.py", line 55, in create_db
    await self.run_migrations_upgrade()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/interface.py", line 63, in run_migrations_upgrade
    await run_sync_in_worker_thread(alembic_upgrade)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/utilities/asyncutils.py", line 68, in run_sync_in_worker_thread
    return await anyio.to_thread.run_sync(call, cancellable=True)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/anyio/to_thread.py", line 31, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
    return await future
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 867, in run
    result = context.run(func, *args)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/alembic_commands.py", line 29, in alembic_upgrade
    alembic.command.upgrade(alembic_config(), revision, sql=dry_run)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/command.py", line 322, in upgrade
    script.run_env()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/migrations/env.py", line 147, in <module>
    apply_migrations()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/utilities/asyncutils.py", line 197, in coroutine_wrapper
    return run_async_from_worker_thread(async_fn, *args, **kwargs)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/utilities/asyncutils.py", line 148, in run_async_from_worker_thread
    return anyio.from_thread.run(call)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/anyio/from_thread.py", line 49, in run
    return asynclib.run_async_from_thread(func, *args)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 970, in run_async_from_thread
    return f.result()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/concurrent/futures/_base.py", line 444, in result
    return self.__get_result()
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/concurrent/futures/_base.py", line 389, in __get_result
    raise self._exception
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/migrations/env.py", line 141, in apply_migrations
    await connection.run_sync(do_run_migrations)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/ext/asyncio/engine.py", line 548, in run_sync
    return await greenlet_spawn(fn, conn, *arg, **kw)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 128, in greenlet_spawn
    result = context.switch(value)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/migrations/env.py", line 129, in do_run_migrations
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/prefect/orion/database/migrations/versions/sqlite/2022_01_20_115236_9725c1cbee35_initial_migration.py", line 37, in upgrade
    op.create_table(
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/operations/ops.py", line 1254, in create_table
    return operations.invoke(op)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/operations/base.py", line 399, in invoke
    return fn(self, operation)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/operations/toimpl.py", line 114, in create_table
    operations.impl.create_table(table)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/ddl/impl.py", line 354, in create_table
    self._exec(schema.CreateTable(table))
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/future/engine.py", line 280, in execute
    return self._execute_20(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 108, in execute
    self._adapt_connection._handle_exception(error)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 236, in _handle_exception
    raise error
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 90, in execute
    self.await_(_cursor.execute(operation, parameters))
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
    value = await result
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/gregoirepelegrinadmin/anaconda3/envs/prefect-env/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table flow already exists
[SQL: 
CREATE TABLE flow (
	id CHAR(36) DEFAULT (
    (
        lower(hex(randomblob(4))) 
        || '-' 
        || lower(hex(randomblob(2))) 
        || '-4' 
        || substr(lower(hex(randomblob(2))),2) 
        || '-' 
        || substr('89ab',abs(random()) % 4 + 1, 1) 
        || substr(lower(hex(randomblob(2))),2) 
        || '-' 
        || lower(hex(randomblob(6)))
    )
    ) NOT NULL, 
	created DATETIME DEFAULT (strftime('%Y-%m-%d %H:%M:%f000', 'now')) NOT NULL, 
	updated DATETIME DEFAULT (strftime('%Y-%m-%d %H:%M:%f000', 'now')) NOT NULL, 
	name VARCHAR NOT NULL, 
	tags JSON DEFAULT '[]' NOT NULL, 
	CONSTRAINT pk_flow PRIMARY KEY (id), 
	CONSTRAINT uq_flow__name UNIQUE (name)
)

]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

ERROR:    Application startup failed. Exiting.
Orion stopped!

This error was already mentioned in this post, but the answer hasn’t been helpful to me. Thanks to everyone who will read this post!

Edit

I’ve just checked the prefect package version which is version 2.6.3

anna_geller · October 19, 2022, 11:47am

Can you try pip instead?

pip install prefect

GregoirePelegrin · October 19, 2022, 12:07pm

I did try installing prefect through pip (I was in Python 3.9 though). It didn’t change anything to the error message (and I haven’t been able to observe any change whatsoever, not even the package version)

anna_geller · October 19, 2022, 1:25pm

Conda you try upgrading your Conda first?

conda update conda
conda update conda-build
conda install -c conda-forge prefect

anna_geller · October 19, 2022, 1:44pm

Also, try removing the DB file and reinstalling in a fresh new Conda environment

I was able to reproduce the issue on Conda, but on pip everything works flawlessly

GregoirePelegrin · October 19, 2022, 1:53pm

I figured there might be a file, but I haven’t been able to locate it, do you have any idea regarding its whereabouts?

anna_geller · October 19, 2022, 2:07pm

Looks like it’s because this happens during the prefect install:

openssl pkgs/main::openssl-1.1.1q-h1a28f6b_0 --> conda-forge::openssl-3.0.5-h03a7124_2 None

The cryptography package is looking for the libssl 1.1.1 dylib but conda removed it. Running this should fix it on Python 10:

conda install openssl=1.1.1

GregoirePelegrin · October 19, 2022, 2:29pm

I am unfortunately on Python 3.9 at most.
Are you saying that I should simply recreate my environment and install openssl before prefect for it to work?
Additionally, do you have any idea on the location of the file?

anna_geller · October 19, 2022, 2:45pm

3.9 or 3.10 both should work

after installing prefect, yes

GregoirePelegrin · October 19, 2022, 2:47pm

I’ve just done it, and nothing has changed here…

anna_geller · October 19, 2022, 6:51pm

interesting, have you tried Prefect Cloud?

definitely, installation and starting Orion should work, but if you can’t get it to work, you may try:

Reboot your machine
Remove the virtual environment and start a new one from scratch – if for some reason, conda doesn’t work for you, maybe in the worst case you can install it directly on your machine without the virtual environment
If this doesn’t help as well, you may try Prefect Cloud instead, which doesn’t require you to spin up the Orion server. Or try installing it on a different machine e.g. on AWS Cloud9 IDE to check if this is working

If none of those works for you, you can describe all steps you have taken during installation and open a GitHub issue.

Don’t get discouraged, you can definitely get it working, Orion is pretty lightweight, must be some small simple package version mismatch, such as with OpenSSL.

GregoirePelegrin · October 20, 2022, 8:05am

Rebooting and reinstalling the virtual environment has not worked, and I have to use a virtual environment. I will open an issue on Github. Thanks!

anna_geller · October 20, 2022, 11:12am

Can you link it here? Really puzzled why pip install prefect would not work in a fresh Conda environment

GregoirePelegrin · October 20, 2022, 11:24am

Here is the GitHub issue.
I too a puzzled by this error, as some colleagues of mine have not experienced it.

anna_geller · October 20, 2022, 11:51am

Did you remove the orion.db file? this traceback looks like not because table flow already exists (it wouldn’t if the database was removed)

rm ~/.prefect/orion.db

GregoirePelegrin · October 20, 2022, 11:55am

I didn’t do it, I wasn’t able to locate this file, as I mentioned in my answer to you at that time. It is now working correctly!

Topic		Replies	Views
When I try to start Orion, I get "sqlite3.OperationalError: table flow already exists" - how to troubleshoot this? Archive prefect-2-0 , troubleshooting , database-migrations	0	2020	November 19, 2022
Installation issues Archive prefect-2-0	6	1616	August 31, 2022
Prefect 2.14.4 : sqlalchemy.exc.OperationalError Help prefect-2-0	2	508	December 1, 2023
How to install Prefect 2.0 on a Linux VM Archive prefect-2-0 , troubleshooting , getting-started , installation-issue , linux	1	2634	October 19, 2022
Sqlite error right after the instalation Archive	1	951	May 21, 2022