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.

8

Upgrading from 3.2.4 to 3.3.2 I have what may be a related error

celery.app.trace - ERROR trace.py:270 -- Task event_reminders[ca2bf116-d95d-4513-aa94-25b0ad978732] raised unexpected: ProgrammingError('(psycopg2.errors.UndefinedColumn) column labels.is_event_not_happening does not exist\nLINE 5: ...ERE events.labels.id = events.events.label_id AND events.lab...\n                                                             ^\n')

Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column labels.is_event_not_happening does not exist
LINE 5: ...ERE events.labels.id = events.events.label_id AND events.lab...

$ indico db upgrade
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.

$ indico db show
Rev: 4e32f4d5ebe4 (head)
Parent: b37cbc4bb129
Path: /opt/indico/.venv/lib/python3.12/site-packages/indico/migrations/versions/20240314_1430_4e32f4d5ebe4_add_registration_id_to_designer_templates.py

    Add registration_form_id to designer templates
    
    Revision ID: 4e32f4d5ebe4
    Revises: b37cbc4bb129
    Create Date: 2024-03-14 10:42:45.632227
9

looks similar. please run the select query i posted above and show its output

10 
psql
psql (13.15 (Debian 13.15-1.pgdg120+1))
Type "help" for help.

indico=> select * from alembic_version;
 version_num  
--------------
 4f5bac451434
(1 row)
11

That revision does not exist in our codebase. Did you run a customized version before, possibly one where you manually added your own alembic revision?

If yes, the easiest way to recover from this would be to:

  • Check your old code (a file containing the string 4f5bac451434, most likely even in its filename) for the revision, and note the down_revision in that file
  • Run indico db stamp XXX with XXX being the revision identifier from that down_revision
  • Run indico db upgrade 4e32f4d5ebe4 to apply all our DB upgrades

Now your DB should be fine.

Assuming you want to keep your alembic revision, you can now “rebase” it on top of the latest one from the core, by setting its down_revision to 4e32f4d5ebe4. Run indico db stamp 4f5bac451434 to mark it as already applied. Or even better, get rid of that revision altogether and maintain your DB change using some other way. An alembic revision in a custom plugin could be one option. But maintaining your own core revisions is going to make every Indico upgrade that contains DB changes very painful as you can see…

12

Not as far as I know. This started as 2.x, upgraded to 3.2.4 and then 3.3.
My experience with indico upgrades are that they seem super fragile, so I try to avoid them at all costs.

13

I think you may be introducing some of that fragility by not updating frequently… However, I’d be happy to hear what problems you had before.

Anyway, considering that the revision you mentioned does not exist (and never existed AFAICT): Please provide me with a schema-only dump (ie no data) of your database. I can then most likely give you the commands to recover from it. You can create it by using pg_dump with the --schema-only option.

14

The current schema is too big to post. Please snarf it down from this link.

The last database upgrade from (as I recall) 2.4 to 3.2.4 failed. I had to go back to an earlier backup, dump it and then restore the data . My notes says I had to do
pg_dump indico -Fc -f indico.dump
and
pg_restore -d indico -O indico.dump

15

This should fix your problem:

indico db stamp 5d05eda06776
indico db upgrade

The last database upgrade from (as I recall) 2.4 to 3.2.4 failed. I had to go back to an earlier backup, dump it and then restore the data

I don’t see any post from you regarding that problem, so of course there’s no way I can tell what exactly went wrong there… But considering that all DB migrations are executed inside a transaction, it should be nearly impossible to get to a state where you need to restore a database backup during an upgrade due to something Indico (or its DB migration scripts) did…

16

After updating the stamp and the upgrade, the error changed to:

2024-06-17 11:07:23,610  0000000000000000  -       celery.app.trace - ERROR trace.py:270 -- Task static_sites_cleanup[d2d64930-0743-44cc-b614-29e281819353] raised unexpected: ProgrammingError('(psycopg2.errors.UndefinedColumn) column users_1.accepted_terms_dt does not exist\nLINE 1: ...ending, users_1.is_deleted AS users_1_is_deleted, users_1.ac...\n                                                             ^\n')

Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column users_1.accepted_terms_dt does not exist
LINE 1: ...ending, users_1.is_deleted AS users_1_is_deleted, users_1.ac...
17

What was the output when running indico db upgrade? What does indico db current -v output?

Before giving you the commands to run, I compared the database from your schema (upgraded with my commands) with a “clean” DB and the schemas were identical… Is there any chance you’re accessing different databases?

18 
$ indico db upgrade
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.

$ 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:

This is a dedicated indico machine with no other services.

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Mon 2024-06-17 11:06:52 MDT; 34min ago
    Process: 597 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 597 (code=exited, status=0/SUCCESS)
        CPU: 2ms

$ psql
psql (13.15 (Debian 13.15-1.pgdg120+1))
Type "help" for help.

indico=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 indico    | indico   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)
19

I am also get the following error every 5 minutes from what I expect is a cron job:

2024-06-17 12:10:00,284  0000000000000000  -       celery.app.trace - ERROR trace.py:270 -- Task event_reminders[634a5d5b-cf4f-450e-b764-1595c4c3b111] raised unexpected: ProgrammingError('(psycopg2.errors.UndefinedColumn) column labels.is_event_not_happening does not exist\nLINE 5: ...ERE events.labels.id = events.events.label_id AND events.lab...\n                                                             ^\n')

Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column labels.is_event_not_happening does not exist
LINE 5: ...ERE events.labels.id = events.events.label_id AND events.lab...
20

This was the output when you ran it for the first time after executing indico db stamp 5d05eda06776?!

Please share the output of select * from alembic_version; once again.

Please also post the output of ls -l /opt/indico/.venv/lib/python3.12/site-packages/indico/migrations/versions/*.py | tail

PS: Please put triple-backticks (“fenced markdown code blocks”) around log/code/shell blocks when posting them, that way they are properly formatted and I don’t need to edit your posts for this purpose all the time. :slight_smile: