Prefect and aiosqlite dependency problem - sqlalchemy.exc.OperationalError: (sqlite3.OperationalError)

Question

I'm running a script with prefect using some tasks and flows. This script has run without problems before, but i'm experiencing problems with it without having changed it.

script

I just call full_data_ingestion in order to test it in my local machine, otherwise it is deployed to a lambda and I don't include the function call

The full error statement below:

Traceback (most recent call last):
  File "c:\repos\people-synergy\serverless-apps\synergy_ingestion_service\app\benefits-service\handler.py", line 138, in <module>
    full_data_ingestion()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\flows.py", line 1097, in __call__
    return enter_flow_run_engine_from_flow_call(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\engine.py", line 286, in enter_flow_run_engine_from_flow_call
    retval = from_sync.wait_for_call_in_loop_thread(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_internal\concurrency\api.py", line 243, in wait_for_call_in_loop_thread
    return call.result()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_internal\concurrency\calls.py", line 284, in result
    return self.future.result(timeout=timeout)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_internal\concurrency\calls.py", line 168, in result
    return self.__get_result()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\concurrent\futures\_base.py", line 389, in __get_result
    raise self._exception
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_internal\concurrency\calls.py", line 355, in _run_async
    result = await coro
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\client\utilities.py", line 49, in with_injected_client
    async with client_context as new_client:
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\client\orchestration.py", line 2717, in __aenter__
    self._ephemeral_lifespan = await self._exit_stack.enter_async_context(
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 556, in enter_async_context
    result = await _cm_type.__aenter__(cm)
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 175, in __aenter__
    return await self.gen.__anext__()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\client\base.py", line 124, in app_lifespan_context
    await context.__aenter__()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_manager.py", line 102, in __aenter__
    await self._exit_stack.aclose()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 593, in aclose
    await self.__aexit__(None, None, None)
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 651, in __aexit__
    raise exc_details[1]
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 634, in __aexit__
    cb_suppress = await cb(*exc_details)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_concurrency\asyncio.py", line 80, in __aexit__
    await self.task
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_manager.py", line 99, in __aenter__
    await self.startup()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_manager.py", line 45, in startup
    raise self._app_exception
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_concurrency\asyncio.py", line 63, in run_and_silence_cancelled
    await self.coroutine()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_manager.py", line 73, in run_app
    await self.app(scope, self.receive, self.send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\asgi_lifespan\_manager.py", line 13, in app_with_state
    await app(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_vendor\fastapi\applications.py", line 293, in __call__
    await super().__call__(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\starlette\applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\starlette\middleware\errors.py", line 149, in __call__
    await self.app(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\api\server.py", line 123, in __call__
    await self.app(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\starlette\middleware\cors.py", line 75, in __call__
    await self.app(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\starlette\middleware\exceptions.py", line 55, in __call__
    await self.app(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_vendor\fastapi\middleware\asyncexitstack.py", line 20, in __call__
    raise e
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\_vendor\fastapi\middleware\asyncexitstack.py", line 17, in __call__
    await self.app(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\starlette\routing.py", line 707, in __call__
    await self.lifespan(scope, receive, send)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\starlette\routing.py", line 677, in lifespan
    async with self.lifespan_context(app) as maybe_state:
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 175, in __aenter__
    return await self.gen.__anext__()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\api\server.py", line 509, in lifespan
    await run_migrations()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\api\server.py", line 435, in run_migrations
    await db.create_db()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\interface.py", line 55, in create_db
    await self.run_migrations_upgrade()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\interface.py", line 63, in run_migrations_upgrade
    await run_sync_in_worker_thread(alembic_upgrade)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\utilities\asyncutils.py", line 91, in run_sync_in_worker_thread
    return await anyio.to_thread.run_sync(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\anyio\to_thread.py", line 33, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\anyio\_backends\_asyncio.py", line 877, in run_sync_in_worker_thread
    return await future
  File "c:\Venvs\ingestao_synergy\lib\site-packages\anyio\_backends\_asyncio.py", line 807, in run
    result = context.run(func, *args)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\alembic_commands.py", line 24, in wrapper
    return fn(*args, **kwargs)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\alembic_commands.py", line 53, in alembic_upgrade
    alembic.command.upgrade(alembic_config(), revision, sql=dry_run)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\command.py", line 385, in upgrade
    script.run_env()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\script\base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\util\pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "c:\Venvs\ingestao_synergy\lib\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 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\migrations\env.py", line 175, in <module>
    apply_migrations()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\utilities\asyncutils.py", line 243, in coroutine_wrapper
    return run_async_from_worker_thread(async_fn, *args, **kwargs)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\utilities\asyncutils.py", line 177, in run_async_from_worker_thread
    return anyio.from_thread.run(call)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\anyio\from_thread.py", line 47, in run
    return asynclib.run_async_from_thread(func, *args)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\anyio\_backends\_asyncio.py", line 906, in run_async_from_thread
    return f.result()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\concurrent\futures\_base.py", line 440, in result
    return self.__get_result()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\concurrent\futures\_base.py", line 389, in __get_result
    raise self._exception
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\migrations\env.py", line 169, in apply_migrations
    await connection.run_sync(do_run_migrations)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\ext\asyncio\engine.py", line 548, in run_sync
    return await greenlet_spawn(fn, conn, *arg, **kw)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 128, in greenlet_spawn
    result = context.switch(value)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\prefect\server\database\migrations\env.py", line 137, in do_run_migrations
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\runtime\environment.py", line 928, in run_migrations
    self.get_context().run_migrations(**kw)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\runtime\migration.py", line 628, in run_migrations
    step.migration_fn(**kw)
  File "C:\Venvs\ingestao_synergy\Lib\site-packages\prefect\server\database\migrations\versions\sqlite\2023_10_30_103720_22ef3915ccd8_index_and_backfill_block_type_name.py", line 20, in upgrade
    batch_op.create_index(
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\contextlib.py", line 124, in __exit__
    next(self.gen)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\operations\base.py", line 377, in batch_alter_table
    impl.flush()
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\operations\batch.py", line 113, in flush
    fn(*arg, **kw)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\ddl\impl.py", line 383, in create_index
    self._exec(schema.CreateIndex(index))
  File "c:\Venvs\ingestao_synergy\lib\site-packages\alembic\ddl\impl.py", line 193, in _exec
    return conn.execute(  # type: ignore[call-overload]
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\future\engine.py", line 280, in execute
    return self._execute_20(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\engine\base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\sql\ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\engine\base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\engine\base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\engine\base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\engine\base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\engine\default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 108, in execute
    self._adapt_connection._handle_exception(error)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 236, in _handle_exception
    raise error
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 90, in execute
    self.await_(_cursor.execute(operation, parameters))
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 121, in greenlet_spawn
    value = await result
  File "c:\Venvs\ingestao_synergy\lib\site-packages\aiosqlite\cursor.py", line 48, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\aiosqlite\cursor.py", line 40, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "c:\Venvs\ingestao_synergy\lib\site-packages\aiosqlite\core.py", line 133, in _execute
    return await future
  File "c:\Venvs\ingestao_synergy\lib\site-packages\aiosqlite\core.py", line 106, in run
    result = function()
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) index ix_block_document__block_type_name_name already exists
[SQL: CREATE INDEX ix_block_document__block_type_name_name ON block_document (block_type_name, name)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

I'm using a virtual environment with python 3.9 and prefect==2.14.10 and aiosqlite==0.19.0

Has someone experienced something like this and can tell me how to work around it? I haven't got much experience with prefect.

Thank you

Answer 1

index ix_block_document__block_type_name_name already exists. you could enter sqlite shell to drop the index ix_block_document__block_type_name_name and re run the code.

DROP INDEX index_name;