Indico database upgrade issue(upgrading Indico 3.1.1 to 3.2)

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)

Any idea, to solve this issue?

What’s your Postgres version?

Thanks for your reply @ThiefMaster
My Postgres version is 9.6

That version is end-of-life and we no longer test on it. And in fact this error you get is caused by the old Postgres version.

Please use a supported version, ideally the latest one, but Postgres 11 and and newer should be fine (however, it makes no sense to install an old version considering that 13 and 14 are both released for over a year).

1 Like

Thanks for your support : :bouquet:
Sure, we are working to upgrade the Postgres to the latest version (Postgres 14).