DB error after upgrade

Administrators
1

This may not be the place to continue this, but I just did the steps for the upgrade from Upgrade — Indico 3.2.7 documentation

Now there is a database error when accessing the main page:

Traceback
---------
Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1905, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column labels_1.is_event_not_happening does not exist
LINE 7: ...labels_1_title, labels_1.color AS labels_1_color, labels_1.i...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.9/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/indico/.venv/lib/python3.9/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/flask/util.py", line 79, in wrapper
    return obj().process()
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 295, in process
    handle_sqlalchemy_database_error()  # this will re-raise an exception
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 285, in process
    res = self._do_process()
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 256, in _do_process
    rv = self._process()
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/display.py", line 273, in _process
    params = get_category_view_params(self.category, self.now, is_flat=self.is_flat)
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/util.py", line 130, in get_category_view_params
    json_ld_events = events = current_event_query.all()
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2773, in all
    return self._iter().all()
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter
    result = self.session.execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1948, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2129, in _handle_dbapi_exception
    util.raise_(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1905, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column labels_1.is_event_not_happening does not exist
LINE 7: ...labels_1_title, labels_1.color AS labels_1_color, labels_1.i...
                                                             ^

[SQL: SELECT anon_1.events_events_type AS anon_1_events_events_type, anon_1.events_events_venue_id AS anon_1_events_events_venue_id, anon_1.events_events_venue_name AS anon_1_events_events_venue_name, anon_1.events_events_address AS anon_1_events_events_address, anon_1.events_events_id AS anon_1_events_events_id, anon_1.events_events_category_id AS anon_1_events_events_category_id, anon_1.events_events_label_id AS anon_1_events_events_label_id, anon_1.events_events_label_message AS anon_1_events_events_label_message, anon_1.events_events_created_dt AS anon_1_events_events_created_dt, anon_1.events_events_start_dt AS anon_1_events_events_start_dt, anon_1.events_events_end_dt AS anon_1_events_events_end_dt, anon_1.events_events_timezone AS anon_1_events_events_timezone, anon_1.events_events_visibility AS anon_1_events_events_visibility, anon_1.events_events_title AS anon_1_events_events_title, anon_1.events_events_protection_mode AS anon_1_events_events_protection_mode, (SELECT anon_3.pos
FROM (SELECT events_1.id AS id, row_number() OVER (ORDER BY events_1.start_dt, events_1.id) AS pos
FROM events.events AS events_1
WHERE events_1.series_id = anon_1.events_events_series_id AND NOT events_1.is_deleted) AS anon_3
WHERE anon_3.id = anon_1.events_events_id) AS anon_2, (SELECT count(events_1.id) AS count_1
FROM events.events AS events_1
WHERE events_1.series_id = anon_1.events_events_series_id AND NOT events_1.is_deleted) AS anon_4, anon_1.events_events_series_id AS anon_1_events_events_series_id, series_1.id AS series_1_id, series_1.show_sequence_in_title AS series_1_show_sequence_in_title, series_1.show_links AS series_1_show_links, series_1.event_title_pattern AS series_1_event_title_pattern, labels_1.id AS labels_1_id, labels_1.title AS labels_1_title, labels_1.color AS labels_1_color, labels_1.is_event_not_happening AS labels_1_is_event_not_happening, event_person_links_1.first_name AS event_person_links_1_first_name, event_person_links_1.last_name AS event_person_links_1_last_name, event_person_links_1.title AS event_person_links_1_title, event_person_links_1.affiliation_id AS event_person_links_1_affiliation_id, event_person_links_1.affiliation AS event_person_links_1_affiliation, event_person_links_1.address AS event_person_links_1_address, event_person_links_1.phone AS event_person_links_1_phone, persons_1.title AS persons_1_title, affiliations_1.id AS affiliations_1_id, affiliations_1.name AS affiliations_1_name, affiliations_1.is_deleted AS affiliations_1_is_deleted, affiliations_1.street AS affiliations_1_street, affiliations_1.postcode AS affiliations_1_postcode, affiliations_1.city AS affiliations_1_city, affiliations_1.country_code AS affiliations_1_country_code, affiliations_1.meta AS affiliations_1_meta, persons_1.id AS persons_1_id, persons_1.event_id AS persons_1_event_id, persons_1.user_id AS persons_1_user_id, persons_1.first_name AS persons_1_first_name, persons_1.last_name AS persons_1_last_name, persons_1.email AS persons_1_email, persons_1.affiliation_id AS persons_1_affiliation_id, persons_1.affiliation AS persons_1_affiliation, persons_1.address AS persons_1_address, persons_1.phone AS persons_1_phone, persons_1.invited_dt AS persons_1_invited_dt, persons_1.is_untrusted AS persons_1_is_untrusted, affiliations_2.id AS affiliations_2_id, affiliations_2.name AS affiliations_2_name, affiliations_2.is_deleted AS affiliations_2_is_deleted, affiliations_2.street AS affiliations_2_street, affiliations_2.postcode AS affiliations_2_postcode, affiliations_2.city AS affiliations_2_city, affiliations_2.country_code AS affiliations_2_country_code, affiliations_2.meta AS affiliations_2_meta, event_person_links_1.event_id AS event_person_links_1_event_id, event_person_links_1.id AS event_person_links_1_id, event_person_links_1.person_id AS event_person_links_1_person_id, event_person_links_1.display_order AS event_person_links_1_display_order
FROM (SELECT events.events.type AS events_events_type, events.events.logo AS events_events_logo, events.events.stylesheet AS events_events_stylesheet, events.events.map_url AS events_events_map_url, events.events.last_friendly_registration_id AS events_events_last_friendly_registration_id, events.events.last_friendly_contribution_id AS events_events_last_friendly_contribution_id, events.events.last_friendly_session_id AS events_events_last_friendly_session_id, events.events.description AS events_events_description, events.events.room_id AS events_events_room_id, events.events.venue_id AS events_events_venue_id, events.events.venue_name AS events_events_venue_name, events.events.room_name AS events_events_room_name, events.events.address AS events_events_address, events.events.no_access_contact AS events_events_no_access_contact, events.events.id AS events_events_id, events.events.is_deleted AS events_events_is_deleted, events.events.is_locked AS events_events_is_locked, events.events.creator_id AS events_events_creator_id, events.events.category_id AS events_events_category_id, events.events.series_id AS events_events_series_id, events.events.cloned_from_id AS events_events_cloned_from_id, events.events.label_id AS events_events_label_id, events.events.label_message AS events_events_label_message, events.events.created_dt AS events_events_created_dt, events.events.start_dt AS events_events_start_dt, events.events.end_dt AS events_events_end_dt, events.events.timezone AS events_events_timezone, events.events.visibility AS events_events_visibility, events.events.keywords AS events_events_keywords, events.events.url_shortcut AS events_events_url_shortcut, events.events.logo_metadata AS events_events_logo_metadata, events.events.stylesheet_metadata AS events_events_stylesheet_metadata, events.events.default_page_id AS events_events_default_page_id, events.events.custom_boa_id AS events_events_custom_boa_id, events.events.title AS events_events_title, events.events.protection_mode AS events_events_protection_mode, events.events.access_key AS events_events_access_key
FROM events.events
WHERE NOT events.events.is_deleted AND events.events.category_id = %(category_id_1)s) AS anon_1 LEFT OUTER JOIN events.series AS series_1 ON series_1.id = anon_1.events_events_series_id LEFT OUTER JOIN events.labels AS labels_1 ON labels_1.id = anon_1.events_events_label_id LEFT OUTER JOIN events.event_person_links AS event_person_links_1 ON anon_1.events_events_id = event_person_links_1.event_id LEFT OUTER JOIN events.persons AS persons_1 ON persons_1.id = event_person_links_1.person_id LEFT OUTER JOIN indico.affiliations AS affiliations_1 ON affiliations_1.id = persons_1.affiliation_id LEFT OUTER JOIN indico.affiliations AS affiliations_2 ON affiliations_2.id = event_person_links_1.affiliation_id
WHERE anon_1.events_events_start_dt >= %(start_dt_1)s AND anon_1.events_events_start_dt < %(start_dt_2)s ORDER BY anon_1.events_events_start_dt DESC, anon_1.events_events_id DESC]
[parameters: {'category_id_1': 0, 'start_dt_1': datetime.datetime(2023, 7, 1, 7, 0), 'start_dt_2': datetime.datetime(2023, 9, 1, 7, 0)}]
(Background on this error at: https://sqlalche.me/e/14/f405)


Request Data
------------
{'data': {'get': {},
          'headers': {'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
                      'Accept-Encoding': 'gzip',
                      'Accept-Language': 'en-US,en;q=0.9,de;q=0.8',
                      'Cdn-Loop': 'cloudflare',
                      'Cf-Connecting-Ip': '76.133.225.81',
                      'Cf-Ipcountry': 'US',
                      'Cf-Ray': '7fded4f058c8ce8c-SJC',
                      'Cf-Visitor': '{"scheme":"https"}',
                      'Connection': 'Keep-Alive',
                      'Cookie': '<redacted>',
                      'Host': 'indico.physics.lbl.gov',
                      'Referer': 'https://indico.physics.lbl.gov/admin/news/',
                      'Sec-Ch-Ua': '"Chromium";v="116", "Not)A;Brand";v="24", '
                                   '"Google Chrome";v="116"',
                      'Sec-Ch-Ua-Mobile': '?0',
                      'Sec-Ch-Ua-Platform': '"Linux"',
                      'Sec-Fetch-Dest': 'document',
                      'Sec-Fetch-Mode': 'navigate',
                      'Sec-Fetch-Site': 'same-origin',
                      'Sec-Fetch-User': '?1',
                      'True-Client-Ip': '<redacted>',
                      'Upgrade-Insecure-Requests': '1',
                      'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) '
                                    'AppleWebKit/537.36 (KHTML, like Gecko) '
                                    'Chrome/116.0.0.0 Safari/537.36',
                      'X-Forwarded-For': '76.133.225.81',
                      'X-Forwarded-Proto': 'https'},
          'json': None,
          'post': {},
          'url': {'category_id': 0}},
 'endpoint': 'categories.display',
 'id': 'f9667e4e636d4ad9',
 'ip': '<redacted>',
 'method': 'GET',
 'referrer': 'https://indico.physics.lbl.gov/admin/news/',
 'rh': 'RHDisplayCategory',
 'time': '2023-08-28T12:06:29.947074',
 'url': 'https://indico.physics.lbl.gov/',
 'user': {'email': 'jdanderson@lbl.gov', 'id': 0, 'name': 'Jeffrey Anderson'},
 'user_agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, '
               'like Gecko) Chrome/116.0.0.0 Safari/537.36'}


--
Indico :: Error Report
2

You are right, it does not belong into that thread. I moved it to a new thread.

Luckily it’s an easy fix: You simply forgot to run indico db upgrade after installing the latest version.

3

Actually, I did run indico db upgrade:

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

I’m sure I did this during the original upgrade, but I’ve run it again now and restarted the server. I still get a db error.
The salient point seems to be

psycopg2.errors.UndefinedColumn: column labels_1.is_event_not_happening does not exist
4

What does indico db current -v tell you? Also, in a psql shell, run select * from alembic_version;

5 
(indico) [indico@indico3 ~]$ 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:
Rev: b45847c0e62f
Parent: b60f5c45acf7
Path: /home/indico/.venv/lib/python3.9/site-packages/indico/migrations/versions/20220815_1631_b45847c0e62f_add_event_series_title_pattern.py

    Add event series title pattern
    
    Revision ID: b45847c0e62f
    Revises: b60f5c45acf7
    Create Date: 2022-08-15 16:31:56.529409

(indico) [indico@indico3 ~]$ psql
psql (13.12)
Type "help" for help.

indico=> select * from alembic_version;
 version_num  
--------------
 b45847c0e62f
 e787389ca868
(2 rows)
6

I don’t know how you ended up with this, but delete from alembic_revision where version_num = 'e787389ca868'; should hopefully fix it. Afterwards, run indico db upgrade.

7

Thanks, that did it.