Indico database upgrade fail from 2.1.4->3.2

Hello Everyone,

I would like to ask for help with Indico upgrade.
I just got a server with Indico 2.1.4 installed on it. I have installed a new VM with Ubuntu 22.04 and Indico 3.2.
Everything works fine, until i try to upgrade the database. The only thing i get back is:

(indico) indico@indico:~$ indico db upgrade
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
(indico) indico@indico:~$

The database looks like this:

(indico) indico@indico:~$ psql
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type “help” for help.
indico=> \dt
List of relations
Schema | Name | Type | Owner
--------±---------------------±------±-------
indico | designer_image_files | table | indico
indico | designer_templates | table | indico
indico | ip_network_groups | table | indico
indico | ip_networks | table | indico
indico | news | table | indico
indico | reference_types | table | indico
indico | settings | table | indico
indico | settings_principals | table | indico
public | alembic_version | table | indico
(9 rows)
indico=>

Can anyone help with what am i doing wrong?

Thanks in advance

Best regards
Zoltan

What does indico db current -v tell you? Maybe the database has already been upgraded?

PS: \dt is not very helpful since most tables are in other schemas.

I get this:

(indico) indico@indico:~$ indico db current -v
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
Current revision(s) for postgresql:///indico:
(indico) indico@indico:~$

What does SELECT * FROM alembic_version; tell you (in psql)?

(indico) indico@indico:~$ psql
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type “help” for help.
indico=> SELECT * FROM alembic_version;
version_num
356ffba72559
(1 row)
indico=>

That revision doesn’t exist. Do you still have the old server? If yes, check ls ~/.venv/lib/python2.7/site-packages/indico/migrations/versions/*.py and show me the last few entries. In 2.1.4, the latest one should be 20180129_0842_813ea74ce8dc_add_attachment_folder_is_hidden.py so it seems like someone may have added a custom on on top of that. Maybe you can share the contents of the custom files coming after that revision?

A quick and dirty workaround would be updating the alembic revision table to set the latest official 2.1.4 one (ie 813ea74ce8dc) as the current revision.

This is the content of the folder:

│ 20170905_1445_f1eee7b4880a_add_event_roles_table.py
│ 20170905_1445_f1eee7b4880a_add_event_roles_table.pyc
│ 20170905_1521_640584a3987e_add_event_roles_to_acls.py
│ 20170905_1521_640584a3987e_add_event_roles_to_acls.pyc
│ 20170914_1433_790a06790309_rename_roles_to_permissions.py
│ 20170914_1433_790a06790309_rename_roles_to_permissions.pyc
│ 20170915_1023_1d512a9ebb30_add_default_session_to_tracks.py
│ 20170915_1023_1d512a9ebb30_add_default_session_to_tracks.pyc
│ 20171101_1149_566d5de4e0e5_add_is_private_column_to_contribution_types.py
│ 20171101_1149_566d5de4e0e5_add_is_private_column_to_contribution_types.pyc
│ 20171124_1138_2af245be72a6_review_questions_models.py
│ 20171124_1138_2af245be72a6_review_questions_models.pyc
│ 20171214_1059_9c4418d7a6aa_add_session_types_table.py
│ 20171214_1059_9c4418d7a6aa_add_session_types_table.pyc
│ 20180126_1130_093533d27a96_add_visibility_options_to_contribution.py
│ 20180126_1130_093533d27a96_add_visibility_options_to_contribution.pyc
│ 20180129_0706_c820455976ba_add_include_description_to_reminders.py
│ 20180129_0706_c820455976ba_add_include_description_to_reminders.pyc
│ 20180129_0842_813ea74ce8dc_add_attachment_folder_is_hidden.py
│ 20180129_0842_813ea74ce8dc_add_attachment_folder_is_hidden.pyc
│ 20190129_1333_356ffba72559_add_eu_vat_no_column.py
│ 20190129_1333_356ffba72559_add_eu_vat_no_column.pyc

I just modified the version_num to the one You wrote, and the upgrade worked :slight_smile:

Thank you very much for your time and help.

Best regards:
Zoltan

Please keep in mind that there was probably also some custom code to deal with that column. You may want to build your own 3.2 wheel with those customizations added back.

In particular because unless this column is nullable=True or has a server_default value, creating new registrations (if this was added to the registrations table) will fail. I can tell you more if you share the contents of 20190129_1333_356ffba72559_add_eu_vat_no_column.py.

This is the py file:

"""
Revision ID: 356ffba72559
Revises: 813ea74ce8dc
Create Date: 2019-01-29 13:33:49.853953
"""

import sqlalchemy as sa
from alembic import op


# revision identifiers, used by Alembic.
revision = '356ffba72559'
down_revision = '813ea74ce8dc'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(u'fk_designer_image_files_template_id_designer_templates', 'designer_image_files', type_='foreignkey')
    op.create_foreign_key(op.f('fk_designer_image_files_template_id_designer_templates'), 'designer_image_files', 'designer_templates', ['template_id'], ['id'], source_schema='indico', referent_schema='indico')
    op.drop_constraint(u'fk_designer_templates_background_image_id_designer_image_files', 'designer_templates', type_='foreignkey')
    op.drop_constraint(u'fk_designer_templates_backside_template_id_designer_templates', 'designer_templates', type_='foreignkey')
    op.drop_constraint(u'fk_designer_templates_category_id_categories', 'designer_templates', type_='foreignkey')
    op.drop_constraint(u'fk_designer_templates_event_id_events', 'designer_templates', type_='foreignkey')
    op.create_foreign_key(op.f('fk_designer_templates_event_id_events'), 'designer_templates', 'events', ['event_id'], ['id'], source_schema='indico', referent_schema='events')
    op.create_foreign_key(op.f('fk_designer_templates_category_id_categories'), 'designer_templates', 'categories', ['category_id'], ['id'], source_schema='indico', referent_schema='categories')
    op.create_foreign_key(op.f('fk_designer_templates_background_image_id_designer_image_files'), 'designer_templates', 'designer_image_files', ['background_image_id'], ['id'], source_schema='indico', referent_schema='indico')
    op.create_foreign_key(op.f('fk_designer_templates_backside_template_id_designer_templates'), 'designer_templates', 'designer_templates', ['backside_template_id'], ['id'], source_schema='indico', referent_schema='indico')
    op.drop_constraint(u'fk_ip_networks_group_id_ip_network_groups', 'ip_networks', type_='foreignkey')
    op.create_foreign_key(op.f('fk_ip_networks_group_id_ip_network_groups'), 'ip_networks', 'ip_network_groups', ['group_id'], ['id'], source_schema='indico', referent_schema='indico')
    op.drop_constraint(u'fk_settings_principals_user_id_users', 'settings_principals', type_='foreignkey')
    op.drop_constraint(u'fk_settings_principals_local_group_id_groups', 'settings_principals', type_='foreignkey')
    op.create_foreign_key(op.f('fk_settings_principals_user_id_users'), 'settings_principals', 'users', ['user_id'], ['id'], source_schema='indico', referent_schema='users')
    op.create_foreign_key(op.f('fk_settings_principals_local_group_id_groups'), 'settings_principals', 'groups', ['local_group_id'], ['id'], source_schema='indico', referent_schema='users')
    op.drop_constraint(u'fk_categories_default_ticket_template_id_designer_templates', 'categories', schema='categories', type_='foreignkey')
    op.create_foreign_key(op.f('fk_categories_default_ticket_template_id_designer_templates'), 'categories', 'designer_templates', ['default_ticket_template_id'], ['id'], source_schema='categories', referent_schema='indico')
    op.drop_constraint(u'fk_principals_ip_network_group_id_ip_network_groups', 'principals', schema='categories', type_='foreignkey')
    op.create_foreign_key(op.f('fk_principals_ip_network_group_id_ip_network_groups'), 'principals', 'ip_network_groups', ['ip_network_group_id'], ['id'], source_schema='categories', referent_schema='indico')
    op.drop_constraint(u'fk_forms_ticket_template_id_designer_templates', 'forms', schema='event_registration', type_='foreignkey')
    op.create_foreign_key(op.f('fk_forms_ticket_template_id_designer_templates'), 'forms', 'designer_templates', ['ticket_template_id'], ['id'], source_schema='event_registration', referent_schema='indico')
    op.drop_constraint(u'fk_contribution_references_reference_type_id_reference_types', 'contribution_references', schema='events', type_='foreignkey')
    op.create_foreign_key(op.f('fk_contribution_references_reference_type_id_reference_types'), 'contribution_references', 'reference_types', ['reference_type_id'], ['id'], source_schema='events', referent_schema='indico')
    op.drop_constraint(u'fk_event_references_reference_type_id_reference_types', 'event_references', schema='events', type_='foreignkey')
    op.create_foreign_key(op.f('fk_event_references_reference_type_id_reference_types'), 'event_references', 'reference_types', ['reference_type_id'], ['id'], source_schema='events', referent_schema='indico')
    op.drop_constraint(u'fk_principals_ip_network_group_id_ip_network_groups', 'principals', schema='events', type_='foreignkey')
    op.create_foreign_key(op.f('fk_principals_ip_network_group_id_ip_network_groups'), 'principals', 'ip_network_groups', ['ip_network_group_id'], ['id'], source_schema='events', referent_schema='indico')
    op.drop_constraint(u'fk_subcontribution_references_reference_type_id_reference_types', 'subcontribution_references', schema='events', type_='foreignkey')
    op.create_foreign_key(op.f('fk_subcontribution_references_reference_type_id_reference_types'), 'subcontribution_references', 'reference_types', ['reference_type_id'], ['id'], source_schema='events', referent_schema='indico')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f('fk_subcontribution_references_reference_type_id_reference_types'), 'subcontribution_references', schema='events', type_='foreignkey')
    op.create_foreign_key(u'fk_subcontribution_references_reference_type_id_reference_types', 'subcontribution_references', 'reference_types', ['reference_type_id'], ['id'], source_schema='events')
    op.drop_constraint(op.f('fk_principals_ip_network_group_id_ip_network_groups'), 'principals', schema='events', type_='foreignkey')
    op.create_foreign_key(u'fk_principals_ip_network_group_id_ip_network_groups', 'principals', 'ip_network_groups', ['ip_network_group_id'], ['id'], source_schema='events')
    op.drop_constraint(op.f('fk_event_references_reference_type_id_reference_types'), 'event_references', schema='events', type_='foreignkey')
    op.create_foreign_key(u'fk_event_references_reference_type_id_reference_types', 'event_references', 'reference_types', ['reference_type_id'], ['id'], source_schema='events')
    op.drop_constraint(op.f('fk_contribution_references_reference_type_id_reference_types'), 'contribution_references', schema='events', type_='foreignkey')
    op.create_foreign_key(u'fk_contribution_references_reference_type_id_reference_types', 'contribution_references', 'reference_types', ['reference_type_id'], ['id'], source_schema='events')
    op.drop_constraint(op.f('fk_forms_ticket_template_id_designer_templates'), 'forms', schema='event_registration', type_='foreignkey')
    op.create_foreign_key(u'fk_forms_ticket_template_id_designer_templates', 'forms', 'designer_templates', ['ticket_template_id'], ['id'], source_schema='event_registration')
    op.drop_constraint(op.f('fk_principals_ip_network_group_id_ip_network_groups'), 'principals', schema='categories', type_='foreignkey')
    op.create_foreign_key(u'fk_principals_ip_network_group_id_ip_network_groups', 'principals', 'ip_network_groups', ['ip_network_group_id'], ['id'], source_schema='categories')
    op.drop_constraint(op.f('fk_categories_default_ticket_template_id_designer_templates'), 'categories', schema='categories', type_='foreignkey')
    op.create_foreign_key(u'fk_categories_default_ticket_template_id_designer_templates', 'categories', 'designer_templates', ['default_ticket_template_id'], ['id'], source_schema='categories')
    op.drop_constraint(op.f('fk_settings_principals_local_group_id_groups'), 'settings_principals', schema='indico', type_='foreignkey')
    op.drop_constraint(op.f('fk_settings_principals_user_id_users'), 'settings_principals', schema='indico', type_='foreignkey')
    op.create_foreign_key(u'fk_settings_principals_local_group_id_groups', 'settings_principals', 'groups', ['local_group_id'], ['id'], referent_schema='users')
    op.create_foreign_key(u'fk_settings_principals_user_id_users', 'settings_principals', 'users', ['user_id'], ['id'], referent_schema='users')
    op.drop_constraint(op.f('fk_ip_networks_group_id_ip_network_groups'), 'ip_networks', schema='indico', type_='foreignkey')
    op.create_foreign_key(u'fk_ip_networks_group_id_ip_network_groups', 'ip_networks', 'ip_network_groups', ['group_id'], ['id'])
    op.drop_constraint(op.f('fk_designer_templates_backside_template_id_designer_templates'), 'designer_templates', schema='indico', type_='foreignkey')
    op.drop_constraint(op.f('fk_designer_templates_background_image_id_designer_image_files'), 'designer_templates', schema='indico', type_='foreignkey')
    op.drop_constraint(op.f('fk_designer_templates_category_id_categories'), 'designer_templates', schema='indico', type_='foreignkey')
    op.drop_constraint(op.f('fk_designer_templates_event_id_events'), 'designer_templates', schema='indico', type_='foreignkey')
    op.create_foreign_key(u'fk_designer_templates_event_id_events', 'designer_templates', 'events', ['event_id'], ['id'], referent_schema='events')
    op.create_foreign_key(u'fk_designer_templates_category_id_categories', 'designer_templates', 'categories', ['category_id'], ['id'], referent_schema='categories')
    op.create_foreign_key(u'fk_designer_templates_backside_template_id_designer_templates', 'designer_templates', 'designer_templates', ['backside_template_id'], ['id'])
    op.create_foreign_key(u'fk_designer_templates_background_image_id_designer_image_files', 'designer_templates', 'designer_image_files', ['background_image_id'], ['id'])
    op.drop_constraint(op.f('fk_designer_image_files_template_id_designer_templates'), 'designer_image_files', schema='indico', type_='foreignkey')
    op.create_foreign_key(u'fk_designer_image_files_template_id_designer_templates', 'designer_image_files', 'designer_templates', ['template_id'], ['id'])

OK, whoever created that made a bit of a mess… And it contains absolutely nothing regarding a vat_no column…

I have no idea about the state of your DB structure regarding constraints and foreign keys, but I hope it won’t break any future migrations at some point (unlikely though).

I will test it on the temporary server.
I would like to thank You, once more, your help :slight_smile:

Best regards
Zoltan