Db upgrade fails from 2.3.5 to 3.0

1

after restoring db from 2.3.5 version of indico the db upgrade fails:

indico db upgrade
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> f1eee7b4880a, Add event roles table
Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidSchemaName: schema "events" does not exist
LINE 2: CREATE TABLE events.roles (

although 
postgres=# \dn
         List of schemas
         Name          |  Owner   
-----------------------+----------
 attachments           | indico
 categories            | indico
 event_abstracts       | indico
 event_editing         | indico
 event_paper_reviewing | indico
 event_registration    | indico
 event_surveys         | indico
 events                | indico
 indico                | indico
 oauth                 | indico
 public                | postgres
 roombooking           | indico
 users                 | indico

any help would be appreciated
Ben

2

Are you sure indico.conf points to the correct database?

3

that is most likely the problem, but I am using the same connection string as in the previous version
SQLALCHEMY_DATABASE_URI = ‘postgresql:///indico’

4

I think the problem lies in the restore, the database has been restored in the wrong schema

5

How did you restore the DB? Did you do that as root or postgres by any chance without specifying the target DB? postgres=# \dn in your command line indicates that you restored to a database named postgres… If yes, you need to createdb -O indico indico using the postgres user and then add the -d indico argument to the pg_restore command.

6

yes, that did the trick, thanks a lot

7

Hi,
I’ve a similar problem.

I created a new fresh installation of Indico 3.0 on CentOS 7.
But I’ve some issues importing the database.

I report the procedure I used here below.

On VM A (Indico 2.3.5 - CentOS 7):

After stopping the services:

su - postgres
pg_dump indico -Fc -f indico_sql_bkp_AAAA_MM_DD.dump
scp /var/lib/pgsql/indico_sql_bkp_AAAA_MM_DD.dump root@VM_B_IP_ADDRESS:/opt/indico/

On VM B (Indico 3.0 - CentOS 7):

su - postgres
dropdb indico
createdb -O indico indico
psql indico -c "CREATE EXTENSION unaccent; CREATE EXTENSION pg_trgm;"
Ctrl+D

chown indico:users /opt/indico/indico_sql_bkp_AAAA_MM_DD.dump

su - indico
pg_restore -d indico -O indico_sql_bkp_AAAA_MM_DD.dump 
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;


pg_restore: from TOC entry 5950; 0 0 COMMENT SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  must be owner of schema public
Command was: COMMENT ON SCHEMA public IS 'standard public schema';


pg_restore: from TOC entry 5951; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: error: could not execute query: ERROR:  must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


pg_restore: from TOC entry 5952; 0 0 COMMENT EXTENSION pg_trgm 
pg_restore: error: could not execute query: ERROR:  must be owner of extension pg_trgm
Command was: COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


pg_restore: from TOC entry 5953; 0 0 COMMENT EXTENSION unaccent 
pg_restore: error: could not execute query: ERROR:  must be owner of extension unaccent
Command was: COMMENT ON EXTENSION unaccent IS 'text search dictionary that removes accents';


pg_restore: warning: errors ignored on restore: 5

The I restart the services.
At this point if I point the browser to the IP address of VM B, I can see the main page of Indico, but it tells me:

Something went wrong
There was a database error while processing your request.
Report Error
Go to the main page

Please, could you give me some hints on this?

Thank you in advance.

8

The warnings are safe to ignore.

Check indico.log for the actual error.

9

Hi,
thank you, just seen and it seems that it is complaining about an undefined column:

2021-08-02 15:45:46,137  INFO     e7ca542d4083421e  -       indico.rh                 GET / [IP=10.105.72.1] [PID=11303]
2021-08-02 15:45:46,160  ERROR    e7ca542d4083421e  -       indico.flask              (psycopg2.errors.UndefinedColumn) column categories.is_flat_view_enabled does not exist
LINE 1: ...anagers AS categories_categories_notify_managers, categories...
                                                             ^

[SQL: SELECT categories.categories.event_message AS categories_categories_event_message, categories.categories.description AS categories_categories_description, categories.categories.no_access_contact AS categories_categories_no_access_contact, categories.categories.id AS categories_categories_id, categories.categories.parent_id AS categories_categories_parent_id, categories.categories.is_deleted AS categories_categories_is_deleted, categories.categories.position AS categories_categories_position, categories.categories.visibility AS categories_categories_visibility, categories.categories.icon_metadata AS categories_categories_icon_metadata, categories.categories.logo_metadata AS categories_categories_logo_metadata, categories.categories.timezone AS categories_categories_timezone, categories.categories.default_event_themes AS categories_categories_default_event_themes, categories.categories.event_creation_restricted AS categories_categories_event_creation_restricted, categories.categories.event_creation_notification_emails AS categories_categories_event_creation_notification_emails, categories.categories.event_message_mode AS categories_categories_event_message_mode, categories.categories.suggestions_disabled AS categories_categories_suggestions_disabled, categories.categories.notify_managers AS categories_categories_notify_managers, categories.categories.is_flat_view_enabled AS categories_categories_is_flat_view_enabled, categories.categories.default_ticket_template_id AS categories_categories_default_ticket_template_id, categories.categories.default_badge_template_id AS categories_categories_default_badge_template_id, categories.categories.title AS categories_categories_title, categories.categories.protection_mode AS categories_categories_protection_mode, (SELECT count(attachments.attachments.id) AS count_1
FROM attachments.attachments JOIN attachments.folders ON attachments.attachments.folder_id = attachments.folders.id
WHERE NOT attachments.folders.is_deleted AND NOT attachments.attachments.is_deleted AND attachments.folders.category_id = categories.categories.id) AS anon_1, EXISTS (SELECT 1
FROM events.events
WHERE events.events.category_id = categories.categories.id AND NOT events.events.is_deleted) AS anon_2, categories_1.id AS categories_1_id, categories_1.title AS categories_1_title, categories_1.protection_mode AS categories_1_protection_mode
FROM categories.categories LEFT OUTER JOIN categories.categories AS categories_1 ON categories_1.parent_id = categories.categories.id AND NOT categories_1.is_deleted
WHERE categories.categories.id = %(id_1)s AND categories.categories.is_deleted = false ORDER BY categories_1.position]
[parameters: {'id_1': 0}]
(Background on this error at: https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column categories.is_flat_view_enabled does not exist
LINE 1: ...anagers AS categories_categories_notify_managers, categories...
                                                             ^


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 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/indico/.venv/lib/python3.9/site-packages/flask/app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.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 291, 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 281, in process
    res = self._do_process()
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 225, in _do_process
    args_result = self._process_args()
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/display.py", line 223, in _process_args
    RHDisplayCategoryBase._process_args(self)
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/base.py", line 34, in _process_args
    self.category = self._get_category(category_id)
  File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/base.py", line 27, in _get_category
    category = self._category_query.filter_by(id=category_id, is_deleted=False).one_or_none()
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2787, in one_or_none
    return self._iter().one_or_none()
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2845, in _iter
    result = self.session.execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, 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 1583, 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 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
      self._handle_dbapi_exception(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
    util.raise_(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
    self.dialect.do_execute(
  File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column categories.is_flat_view_enabled does not exist
LINE 1: ...anagers AS categories_categories_notify_managers, categories...
                                                             ^

[SQL: SELECT categories.categories.event_message AS categories_categories_event_message, categories.categories.description AS categories_categories_description, categories.categories.no_access_contact AS categories_categories_no_access_contact, categories.categories.id AS categories_categories_id, categories.categories.parent_id AS categories_categories_parent_id, categories.categories.is_deleted AS categories_categories_is_deleted, categories.categories.position AS categories_categories_position, categories.categories.visibility AS categories_categories_visibility, categories.categories.icon_metadata AS categories_categories_icon_metadata, categories.categories.logo_metadata AS categories_categories_logo_metadata, categories.categories.timezone AS categories_categories_timezone, categories.categories.default_event_themes AS categories_categories_default_event_themes, categories.categories.event_creation_restricted AS categories_categories_event_creation_restricted, categories.categories.event_creation_notification_emails AS categories_categories_event_creation_notification_emails, categories.categories.event_message_mode AS categories_categories_event_message_mode, categories.categories.suggestions_disabled AS categories_categories_suggestions_disabled, categories.categories.notify_managers AS categories_categories_notify_managers, categories.categories.is_flat_view_enabled AS categories_categories_is_flat_view_enabled, categories.categories.default_ticket_template_id AS categories_categories_default_ticket_template_id, categories.categories.default_badge_template_id AS categories_categories_default_badge_template_id, categories.categories.title AS categories_categories_title, categories.categories.protection_mode AS categories_categories_protection_mode, (SELECT count(attachments.attachments.id) AS count_1
FROM attachments.attachments JOIN attachments.folders ON attachments.attachments.folder_id = attachments.folders.id
WHERE NOT attachments.folders.is_deleted AND NOT attachments.attachments.is_deleted AND attachments.folders.category_id = categories.categories.id) AS anon_1, EXISTS (SELECT 1
FROM events.events
WHERE events.events.category_id = categories.categories.id AND NOT events.events.is_deleted) AS anon_2, categories_1.id AS categories_1_id, categories_1.title AS categories_1_title, categories_1.protection_mode AS categories_1_protection_mode
FROM categories.categories LEFT OUTER JOIN categories.categories AS categories_1 ON categories_1.parent_id = categories.categories.id AND NOT categories_1.is_deleted
WHERE categories.categories.id = %(id_1)s AND categories.categories.is_deleted = false ORDER BY categories_1.position]
[parameters: {'id_1': 0}]
(Background on this error at: https://sqlalche.me/e/14/f405)
10

Did you forget to run indico db upgrade?

11

Thank you, yes, I forgot that instruction :slight_smile: