NoSuchTableError: migration_tmp during setup fresh installation of stable release_18.09

[karolszk]

Hi All,

I am trying to setup galaxy server on my debian laptop.
I follow steps from https://galaxyproject.org/admin/get-galaxy/ and
after cloning a fresh copy of release_18.09 of galaxy and executing sh run.sh I got the following exception:

DEBUG:galaxy.containers:config file './config/containers_conf.yml' does not exist, running with default config
galaxy.queue_worker INFO 2018-12-29 00:45:15,158 [p:18220,w:0,m:0] [MainThread] Initializing main Galaxy Queue Worker on sqlalchemy+sqlite:///./database/control.sqlite?isolation_level=IMMEDIATE
galaxy.model.migrate.check INFO 2018-12-29 00:45:15,340 [p:18220,w:0,m:0] [MainThread] Creating database for URI [sqlite:///./database/universe.sqlite?isolation_level=IMMEDIATE]
galaxy.model.migrate.check INFO 2018-12-29 00:45:15,397 [p:18220,w:0,m:0] [MainThread] Migrating 0 -> 1...
galaxy.model.migrate.check INFO 2018-12-29 00:45:16,293 [p:18220,w:0,m:0] [MainThread]
galaxy.model.migrate.check INFO 2018-12-29 00:45:16,293 [p:18220,w:0,m:0] [MainThread] Migrating 1 -> 2...
galaxy.model.migrate.check INFO 2018-12-29 00:45:16,462 [p:18220,w:0,m:0] [MainThread]
galaxy.model.migrate.check INFO 2018-12-29 00:45:16,463 [p:18220,w:0,m:0] [MainThread] Migrating 2 -> 3...
galaxy.model.migrate.check INFO 2018-12-29 00:45:18,557 [p:18220,w:0,m:0] [MainThread]
galaxy.model.migrate.check INFO 2018-12-29 00:45:18,558 [p:18220,w:0,m:0] [MainThread] Migrating 3 -> 4...
galaxy.model.migrate.check INFO 2018-12-29 00:45:18,609 [p:18220,w:0,m:0] [MainThread]
Traceback (most recent call last):
File "lib/galaxy/webapps/galaxy/buildapp.py", line 49, in app_factory
app = galaxy.app.UniverseApplication(global_conf=global_conf, **kwargs)
File "lib/galaxy/app.py", line 86, in init
self._configure_models(check_migrate_databases=True, check_migrate_tools=check_migrate_tools, config_file=config_file)
File "lib/galaxy/config.py", line 1131, in _configure_models
create_or_verify_database(db_url, config_file, self.config.database_engine_options, app=self)
File "lib/galaxy/model/migrate/check.py", line 64, in create_or_verify_database
migrate()
File "lib/galaxy/model/migrate/check.py", line 60, in migrate
migrate_to_current_version(engine, db_schema)
File "lib/galaxy/model/migrate/check.py", line 149, in migrate_to_current_version
schema.runchange(ver, change, changeset.step)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange
change.run(self.engine, step)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 148, in run
script_func(engine)
File "lib/galaxy/model/migrate/versions/0004_indexes_and_defaults.py", line 29, in upgrade
HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 457, in new
metadata._remove_table(name, schema)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 452, in new
table._init(name, metadata, *args, **kw)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 534, in _init
include_columns, _extend_on=_extend_on)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 560, in _autoload
extend_on=extend_on
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2056, in run_callable
return conn.run_callable(callable, *args, **kwargs)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1545, in run_callable
return callable(self, *args, **kwargs)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 391, in reflecttable
table, include_columns, exclude_columns, **opts)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 633, in reflecttable
exclude_columns, _extend_on, reflection_options)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 761, in _reflect_fk
**reflection_options
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 457, in new
metadata._remove_table(name, schema)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 452, in new
table._init(name, metadata, *args, **kw)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 534, in _init
include_columns, _extend_on=_extend_on)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 547, in _autoload
_extend_on=extend_on
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1545, in run_callable
return callable(self, *args, **kwargs)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 391, in reflecttable
table, include_columns, exclude_columns, **opts)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 633, in reflecttable
exclude_columns, _extend_on, reflection_options)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 761, in _reflect_fk
**reflection_options
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 457, in new
metadata._remove_table(name, schema)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 452, in new
table._init(name, metadata, *args, **kw)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 534, in _init
include_columns, _extend_on=_extend_on)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 547, in _autoload
_extend_on=extend_on
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1545, in run_callable
return callable(self, *args, **kwargs)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 391, in reflecttable
table, include_columns, exclude_columns, **opts)
File "/home/karol/tmp/galaxy-python/galaxy/.venv/local/lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 626, in reflecttable
raise exc.NoSuchTableError(table.name)
NoSuchTableError: migration_tmp

the problem is repeatable.
The command: sh manage_db.sh upgrade also ends with the same stack trace.

[mvdbeek]

Hmm, this might be an issue with sqlalchemy-migrate in combination with sqlite >= 3.26 (https://bugs.launchpad.net/ubuntu/+source/nova/+bug/1807262 / https://review.openstack.org/#/c/623564/5/migrate/changeset/databases/sqlite.py)

I don't think we can do much here except for waiting for a new release, you could try patching up sqlalchemy-migrate with the patch here: https://review.openstack.org/#/c/623564/5/migrate/changeset/databases/sqlite.py

The other, and better, alternative is to use postgresql, which is very much preferred over the default sqlite database.

[karolszk]

Hi @mvdbeek thank you. How to switch from sqlalchemy to postgresql?

[mvdbeek]

This is described here: https://docs.galaxyproject.org/en/latest/admin/production.html#switching-to-a-database-server (it'll still use sqlalchemy, just with a different database backend.)

[nsoranzo]

@karolszk If you are still stuck and want to keep using SQLite, you can download https://github.com/jmchilton/galaxy-downloads/raw/master/db_gx_rev_0141.sqlite and use it to overwrite database/universe.sqlite . This will give you an empty Galaxy database at version 141. sh manage_db.sh upgrade should then bring you at the latest version.

[karolszk]

Bingo! It's work! :-) @nsoranzo thank you very much. Now I obtained what I want:

galaxy.web.stack INFO 2019-01-02 21:18:12,429 [p:14531,w:1,m:0] [MainThread] Galaxy server instance 'main.web.1' is running Starting server in PID 14531. serving on http://localhost:8080

ps.
in the meantime I was thinking about use docker container for example: https://github.com/bgruening/docker-galaxy-stable/releases instead of building galaxy from the scratch.

[nsoranzo]

@karolszk Happy it's solved!

@mvdbeek @dannon sqlalchemy-migrate seems unmaintained, time to move to Alembic ?

[dannon]

@nsoranzo Yeah. I looked into swapping to Alembic way back, but it was still fairly green at the time. Revisited it a while later, but there was a brief time period when it looked like someone else under the openstack umbrella was really going to pick sqlalchemy-migrate back up and maintain it, but it seems dead now. Alembic on the other hand is now, finally, past a 1.0 formal release and is still well maintained.

I dug up a relevant TODO that I wrote 6 years ago, here: https://github.com/galaxyproject/galaxy/blame/ed6f5d45a9ecbf0cf8a85341e0213b82a9132a46/lib/galaxy/model/migrate/versions/0102_add_tool_dependency_status_columns.py#L48

+1 on swapping over, would love to kill that TODO :)