Hi everyone, I have the following issue when I tried to upgrade indico from 3.11 to 2.3. This issue occurred during running the following indico command to upgrade the database schema :
indico db upgrade
Any idea, to solve this issue?
Thank you in advance for any suggestions,
(venv) indico@indico-1:/opt/indico$ indico db upgrade
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade b36825c7869e -> 8993132179d3, Ensure single_choice fields have item_type
INFO [alembic.runtime.migration] Running upgrade 8993132179d3 -> 84874a17eaf3, Cleanup registration fields is_billable
INFO [alembic.runtime.migration] Running upgrade 84874a17eaf3 -> 3dafee32ba7d, Remove is_billable from registration fields
INFO [alembic.runtime.migration] Running upgrade 3dafee32ba7d -> 82fb6c6ac6db, Add publish registrations with consent
INFO [alembic.runtime.migration] Running upgrade 82fb6c6ac6db -> ef7a8b2e6737, Add access column to menu entries
INFO [alembic.runtime.migration] Running upgrade ef7a8b2e6737 -> 5123f24eb41e, Add retention period to RegistrationFormItem
INFO [alembic.runtime.migration] Running upgrade 5123f24eb41e -> 57696d76f9b0, Add registration visibility override
INFO [alembic.runtime.migration] Running upgrade 57696d76f9b0 -> a61ce4bd7549, Add publish registrations duration to registration forms
INFO [alembic.runtime.migration] Running upgrade a61ce4bd7549 -> 88eb87ee0d3e, Add retention period to RegistrationForm
INFO [alembic.runtime.migration] Running upgrade 88eb87ee0d3e -> a707753d16e2, Add 'is_purged' flag to registration forms
INFO [alembic.runtime.migration] Running upgrade a707753d16e2 -> 812aa90a3660, Add favorite events table
INFO [alembic.runtime.migration] Running upgrade 812aa90a3660 -> c39db219f85a, Add predefined affiliations
INFO [alembic.runtime.migration] Running upgrade c39db219f85a -> 1950e5d12ab5, Add predefined affiliations to persons
INFO [alembic.runtime.migration] Running upgrade 1950e5d12ab5 -> 0c4bb2973536, Move affiliation into users table
INFO [alembic.runtime.migration] Running upgrade 0c4bb2973536 -> 33c3ab67d729, Add registration created_by_manager column
Traceback (most recent call last):
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.p y", line 732, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.CannotCoerce: cannot cast type jsonb to integer
LINE 10: reg.id = (lo.meta -> 'registration_id')::int;
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/indico/venv/bin/indico", line 8, in <module>
sys.exit(cli())
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
return self.main(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/flask/cli.py", line 567, in main
return super().main(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 1055, in main
rv = self.invoke(ctx)
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/indico/venv/lib/python3.9/site-packages/indico/cli/util.py", line 1 08, in invoke
return self._impl.invoke(ctx)
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 760, i n invoke
return __callback(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/flask/cli.py", line 407, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 760, i n invoke
return __callback(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/indico/cli/database.py", li ne 137, in _call_with_plugins
func(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/flask/cli.py", line 407, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/click/core.py", line 760, i n invoke
return __callback(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/flask_migrate/cli.py", line 149, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/opt/indico/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 98, in wrapped
f(*args, **kwargs)
File "/opt/indico/venv/lib/python3.9/site-packages/flask_migrate/__init__.py", line 185, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/command.py", line 3 22, in upgrade
script.run_env()
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/script/base.py", li ne 569, in run_env
util.load_python_file(self.dir, "env.py")
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", l ine 94, in load_python_file
module = load_module_py(module_id, path)
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", l ine 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/indico/venv/lib/python3.9/site-packages/indico/migrations/env.py", line 91, in <module>
run_migrations_online()
File "/opt/indico/venv/lib/python3.9/site-packages/indico/migrations/env.py", line 83, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/runtime/environment .py", line 853, in run_migrations
self.get_context().run_migrations(**kw)
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/runtime/migration.p y", line 623, in run_migrations
step.migration_fn(**kw)
File "/opt/indico/venv/lib/python3.9/site-packages/indico/migrations/versions/ 20220718_1534_33c3ab67d729_add_registration_created_by_manager_.py", line 23, in upgrade
op.execute('''
File "<string>", line 8, in execute
File "<string>", line 3, in execute
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 2414, in execute
return operations.invoke(op)
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/operations/base.py" , line 399, in invoke
return fn(self, operation)
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/operations/toimpl.p y", line 207, in execute_sql
operations.migration_context.impl.execute(
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 202, in execute
self._exec(sql, execution_options)
File "/opt/indico/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 195, in _exec
return conn.execute(construct, multiparams)
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py" , line 333, in _execute_on_connection
return connection._execute_clauseelement(
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/opt/indico/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.p y", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.CannotCoerce) cannot cast type jsonb to integer
LINE 10: reg.id = (lo.meta -> 'registration_id')::int;
^
[SQL:
UPDATE event_registration.registrations reg
SET created_by_manager = true
FROM events.logs lo
WHERE
lo.realm = 2 AND
lo.module = 'Registration' AND
lo.summary LIKE 'New registration: %%' AND
lo.meta ? 'registration_id' AND
reg.id = (lo.meta -> 'registration_id')::int;
]
(Background on this error at: https://sqlalche.me/e/14/f405)