Error When Upgrading Database

1

I have been asked to upgrade our Galaxy application and I have run into an error when doing so.

I have been following these directions. I get stuck on Step 4, where it says to run the manage_db.sh script. I run the command exactly as written, and I receive an error:

$ sh manage_db.sh upgrade
Activating virtualenv at .venv
INFO:alembic.runtime.migration:Context impl PostgresqlImpl.
INFO:alembic.runtime.migration:Will assume transactional DDL.
INFO:alembic.runtime.migration:Running upgrade 8a19186a6ee7 -> 2dc3386d091f, add_indexes_for_workflow_comment_foreign_keys
Traceback (most recent call last):
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateTable: relation "ix_workflow_comment_workflow_id" already exists


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

Traceback (most recent call last):
  File "/opt/galaxy/galaxy/./scripts/db.py", line 37, in <module>
    main()
  File "/opt/galaxy/galaxy/./scripts/db.py", line 33, in main
    args.func(args)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/base.py", line 265, in upgrade
    self._exec_command("upgrade", args)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/base.py", line 289, in _exec_command
    getattr(dbscript, command)(args)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/base.py", line 202, in upgrade
    self._upgrade_to_head(args.sql)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/dbscript.py", line 90, in _upgrade_to_head
    self._upgrade_to_revision("gxy@head", is_sql_mode)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/base.py", line 234, in _upgrade_to_revision
    command.upgrade(self.alembic_config, rev, is_sql_mode)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/command.py", line 406, in upgrade
    script.run_env()
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/script/base.py", line 586, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/util/pyfiles.py", line 113, 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/galaxy/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 158, in <module>
    run_migrations_online()
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 41, in run_migrations_online
    _configure_and_run_migrations_online(url)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 122, in _configure_and_run_migrations_online
    _run_migrations()
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 129, in _run_migrations
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/alembic/versions_gxy/2dc3386d091f_add_indexes_for_workflow_comment_.py", line 35, in upgrade
    create_index(workflow_comment_workflow_id_index_name, workflow_comment_table_name, [workflow_id_column_name])
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/util.py", line 322, in create_index
    CreateIndex(index_name, table_name, columns, **kw).run()
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/util.py", line 29, in run
    return self.execute()
  File "/opt/galaxy/galaxy/lib/galaxy/model/migrations/util.py", line 131, in execute
    op.create_index(self.index_name, self.table_name, self.columns, **self.kw)
  File "<string>", line 8, in create_index
  File "<string>", line 3, in create_index
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/operations/ops.py", line 999, in create_index
    return operations.invoke(op)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/operations/base.py", line 442, in invoke
    return fn(self, operation)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/operations/toimpl.py", line 114, in create_index
    operations.impl.create_index(idx, **kw)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/ddl/postgresql.py", line 95, in create_index
    self._exec(CreateIndex(index, **kw))
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/alembic/ddl/impl.py", line 247, in _exec
    return conn.execute(construct, params)
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/galaxy/galaxy/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "ix_workflow_comment_workflow_id" already exists

[SQL: CREATE INDEX ix_workflow_comment_workflow_id ON workflow_comment (workflow_id)]
(Background on this error at: https://sqlalche.me/e/20/f405)

It appears that “ix_workflow_comment_workflow_id” already exists and the script is not able to handle that situation. I did not attempt to continue the upgrade after encountering this issue.

Does anyone have any idea about the cause of or the solution to that error message?

We were previously running on branch release_23.2 (commit 93eb541e21) and are trying to upgrade to branch release_24.2 (commit 16e7818249). The server is running RHEL 9.5 and Galaxy is configured to use a Postgres database. I would be glad to provide any additional information that may be needed.

From searching, I found this topic, which is a similar situation, but the error is not the same.

1 Like
2

Hi @NCATmax

I’ve cross-posted your question over to the Admin chat for help. You're invited to talk on Matrix

And, below are our links to resources and tutorials as a reference.

Let’s start there, thanks! :slight_smile:

1 Like
3

Hi @NCATmax,
Thank you for the detailed context - this is very helpful. I’ve been able to reproduce the error and can confirm this is a bug. I’ll post an update and/or a fix by Monday.

4

Fix is in [24.0] Check if index exists before creating by jdavcs · Pull Request #19873 · galaxyproject/galaxy · GitHub. Most likely, this will be merged over the weekend, after which the migration should run fine.
Again, thanks for the detailed report!

2 Likes
5

Thank you @jennaj for sharing this in the right location and for the links!

And a huge thank you to @jdavcs! It is very relieving to know that you were able to reproduce this, we were very stuck and trying to figure out where we went wrong.

We checked that your commit had been merged, pulled the latest commits, and after that, the manage_db.sh ran without issues.

We did run into another issue that prevented Galaxy from starting. Long story short, we also had to remove fastapi and fastapi-utils python packages and then reinstall the fastapi-slim package. (Looks like this change happened in Release 24.1.)

After that, our Galaxy application started without any issues. Thank you again!

1 Like