Hello privacyidea community!
I have been tasked upgrading my companies privacyidea which has been abandoned for some time. I started at 3.5 and worked my way up to 3.8 but now I’ve het a wall with 3.9.
My dev environment is currently running on Alma Linux 9, python 3.9 with postgres Engine 16 for the database. The database and privacyidea configuration are imported from production which started at privacyidea 3.5. I’ve got a database backup for every version.
Installing the application via pip has shown no issues but the schema upgrade fails with the following message:
Running online
CurrentID in Table authcache: 1
+++ Creating Sequence: authcache_seq
CurrentID in Table caconnector: 1
+++ Creating Sequence: caconnector_seq
Traceback (most recent call last):
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/privacyidea/bin/pi-manage", line 1739, in <module>
manager.run()
File "/opt/privacyidea/lib/python3.9/site-packages/flask_script/__init__.py", line 417, in run
result = self.handle(argv[0], argv[1:])
File "/opt/privacyidea/lib/python3.9/site-packages/flask_script/__init__.py", line 386, in handle
res = handle(*args, **config)
File "/opt/privacyidea/lib/python3.9/site-packages/flask_script/commands.py", line 216, in __call__
return self.run(*args, **kwargs)
File "/opt/privacyidea/lib/python3.9/site-packages/flask_migrate/__init__.py", line 96, in wrapped
f(*args, **kwargs)
File "/opt/privacyidea/lib/python3.9/site-packages/flask_migrate/__init__.py", line 271, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/opt/privacyidea/lib/python3.9/site-packages/alembic/command.py", line 378, in upgrade
script.run_env()
File "/opt/privacyidea/lib/python3.9/site-packages/alembic/script/base.py", line 576, in run_env
util.load_python_file(self.dir, "env.py")
File "/opt/privacyidea/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
module = load_module_py(module_id, path)
File "/opt/privacyidea/lib/python3.9/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 850, in exec_module
File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
File "/opt/privacyidea/lib/privacyidea/migrations/env.py", line 110, in <module>
run_migrations_online()
File "/opt/privacyidea/lib/privacyidea/migrations/env.py", line 100, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/opt/privacyidea/lib/python3.9/site-packages/alembic/runtime/environment.py", line 867, in run_migrations
self.get_context().run_migrations(**kw)
File "/opt/privacyidea/lib/python3.9/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
step.migration_fn(**kw)
File "/opt/privacyidea/lib/privacyidea/migrations/versions/5cb310101a1f_.py", line 51, in upgrade
current_id = session.query(func.max(tbl.c.id)).one()[0] or 1
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2870, in one
return self._iter().one()
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter
result = self.session.execute(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/opt/privacyidea/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block
[SQL: SELECT max(challenge.id) AS max_1
FROM challenge]
(Background on this error at: https://sqlalche.me/e/14/2j85)
The assumption is that the schema upgrade fails at the query SELECT max(challenge.id) AS max_1 FROM challenge. When running the query manually I get the following result:
FROM challenge;
max_1
-------
(1 row)
When doing a simple select * from challenge; It returns an empty table. So I am not surprised by this result.
The command used to initiate the schema upgrade is:
privacyidea-schema-upgrade /opt/privacyidea/lib/privacyidea/migrations
I am at a loss on what is going wrong here and what I can do to resolve this. Any suggestions on how to troubleshoot this are welcome.