Database migration error
After a fresh galaxy-dist pull today (no update for various reasons) the instance was running ok on the default sqlight database. When I migrated back the setting to use the MySQL database it complained during startup about having found an old version of the database and I need to upgrade. Database upgrade started and the first few migrations were successful, but now it hangs every time at 99->100. Any clue what might be wrong? Here is the lengthy trace log. thanks Alex ==================================================== 99 -> 100... Migration script to alter the type of the tool_dependency.version column from TrimmedString(40) to Text. Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0100_alter_tool_dependency_table_version_column.py", line 30, in upgrade ToolDependency_table = Table( "tool_dependency", metadata, autoload=True ) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 108, in __call__ return type.__call__(self, name, metadata, *args, **kwargs) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 236, in __init__ _bind_or_error(metadata).reflecttable(self, include_columns=include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/engine/base.py", line 1265, in reflecttable self.dialect.reflecttable(conn, table, include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1664, in reflecttable sql = self._show_create_table(connection, table, charset) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1835, in _show_create_table raise exc.NoSuchTableError(full_name) sqlalchemy.exc.NoSuchTableError: tool_dependency Error in sys.excepthook: Traceback (most recent call last): File "/usr/lib/python2.7/dist-packages/apport_python_hook.py", line 66, in apport_excepthook from apport.fileutils import likely_packaged, get_recent_crashes ImportError: No module named apport.fileutils Original exception was: Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0100_alter_tool_dependency_table_version_column.py", line 30, in upgrade ToolDependency_table = Table( "tool_dependency", metadata, autoload=True ) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 108, in __call__ return type.__call__(self, name, metadata, *args, **kwargs) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 236, in __init__ _bind_or_error(metadata).reflecttable(self, include_columns=include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/engine/base.py", line 1265, in reflecttable self.dialect.reflecttable(conn, table, include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1664, in reflecttable sql = self._show_create_table(connection, table, charset) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1835, in _show_create_table raise exc.NoSuchTableError(full_name) sqlalchemy.exc.NoSuchTableError: tool_dependency ========================================================
Some additional info; Actually the errors already started at migrate version 93->94. I just restored the version to prior upgrade to check any other errors. There were some create errors. I am not sure if these come from the \n\t statements in the sysntax or whether that is just an error log output thing only. Bottom line is that some tables are not created. Some seem to already exist due to the partial pass of the upgrade the previous time. See log below; Would it be possible to just create a fresh latest version database with no data and then transfer the data of the backup I have into that? Alex ============================================= sudo sh manage_db.sh -c universe_wsgi.ini upgrade 93 -> 94... Migration script to create "handler" column in job table. (OperationalError) (1060, "Duplicate column name 'handler'") u'\nALTER TABLE job ADD handler VARCHAR(255)' () done 94 -> 95... Migration script to create table for tracking history_dataset_association subsets. (OperationalError) (1005, "Can't create table 'galaxy_dist.history_dataset_association_subset' (errno: 150)") u'\nCREATE TABLE history_dataset_association_subset (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\thistory_dataset_association_id INTEGER, \n\thistory_dataset_association_subset_id INTEGER, \n\tlocation VARCHAR(255), \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(history_dataset_association_id) REFERENCES history_dataset_association (id), \n\t FOREIGN KEY(history_dataset_association_subset_id) REFERENCES history_dataset_association (id)\n)\n\n' () (ProgrammingError) (1146, "Table 'galaxy_dist.history_dataset_association_subset' doesn't exist") 'CREATE INDEX ix_hda_id ON history_dataset_association_subset (history_dataset_association_id)' () done 95 -> 96... Migration script to add column to openid table for provider. Remove any OpenID entries with nonunique GenomeSpace Identifier Adding provider column to galaxy_user_openid table failed: (OperationalError) (1060, "Duplicate column name 'provider'") u'\nALTER TABLE galaxy_user_openid ADD provider VARCHAR(255)' () done 96 -> 97... Migration script to add the ctx_rev column to the tool_shed_repository table. Adding ctx_rev column to the tool_shed_repository table failed: (OperationalError) (1060, "Duplicate column name 'ctx_rev'") u'\nALTER TABLE tool_shed_repository ADD ctx_rev VARCHAR(10)' () done 97 -> 98... Migration script to create the genome_index_tool_data table. 0098_genome_index_tool_data_table DEBUG 2012-10-12 11:27:46,128 Creating genome_index_tool_data table failed: (OperationalError) (1005, "Can't create table 'galaxy_dist.genome_index_tool_data' (errno: 150)") u'\nCREATE TABLE genome_index_tool_data (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tjob_id INTEGER, \n\tdataset_id INTEGER, \n\tdeferred_job_id INTEGER, \n\ttransfer_job_id INTEGER, \n\tfasta_path VARCHAR(255), \n\tcreated_time DATETIME, \n\tmodified_time DATETIME, \n\tindexer VARCHAR(64), \n\tuser_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(dataset_id) REFERENCES dataset (id), \n\t FOREIGN KEY(transfer_job_id) REFERENCES transfer_job (id), \n\t FOREIGN KEY(user_id) REFERENCES galaxy_user (id), \n\t FOREIGN KEY(deferred_job_id) REFERENCES deferred_job (id), \n\t FOREIGN KEY(job_id) REFERENCES job (id)\n)\n\n' () 0098_genome_index_tool_data_table DEBUG 2012-10-12 11:27:46,128 Creating genome_index_tool_data table failed: (OperationalError) (1005, "Can't create table 'galaxy_dist.genome_index_tool_data' (errno: 150)") u'\nCREATE TABLE genome_index_tool_data (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tjob_id INTEGER, \n\tdataset_id INTEGER, \n\tdeferred_job_id INTEGER, \n\ttransfer_job_id INTEGER, \n\tfasta_path VARCHAR(255), \n\tcreated_time DATETIME, \n\tmodified_time DATETIME, \n\tindexer VARCHAR(64), \n\tuser_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(dataset_id) REFERENCES dataset (id), \n\t FOREIGN KEY(transfer_job_id) REFERENCES transfer_job (id), \n\t FOREIGN KEY(user_id) REFERENCES galaxy_user (id), \n\t FOREIGN KEY(deferred_job_id) REFERENCES deferred_job (id), \n\t FOREIGN KEY(job_id) REFERENCES job (id)\n)\n\n' () done 98 -> 99... Migration script to add the tool_dependency table. 0099_add_tool_dependency_table DEBUG 2012-10-12 11:27:46,268 Creating tool_dependency table failed: (OperationalError) (1005, "Can't create table 'galaxy_dist.tool_dependency' (errno: 150)") u'\nCREATE TABLE tool_dependency (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_shed_repository_id INTEGER NOT NULL, \n\tinstalled_changeset_revision VARCHAR(255), \n\tname VARCHAR(255), \n\tversion VARCHAR(40), \n\ttype VARCHAR(40), \n\tuninstalled BOOL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0099_add_tool_dependency_table DEBUG 2012-10-12 11:27:46,268 Creating tool_dependency table failed: (OperationalError) (1005, "Can't create table 'galaxy_dist.tool_dependency' (errno: 150)") u'\nCREATE TABLE tool_dependency (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_shed_repository_id INTEGER NOT NULL, \n\tinstalled_changeset_revision VARCHAR(255), \n\tname VARCHAR(255), \n\tversion VARCHAR(40), \n\ttype VARCHAR(40), \n\tuninstalled BOOL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () done 99 -> 100... Migration script to alter the type of the tool_dependency.version column from TrimmedString(40) to Text. Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0100_alter_tool_dependency_table_version_column.py", line 30, in upgrade ToolDependency_table = Table( "tool_dependency", metadata, autoload=True ) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 108, in __call__ return type.__call__(self, name, metadata, *args, **kwargs) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 236, in __init__ _bind_or_error(metadata).reflecttable(self, include_columns=include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/engine/base.py", line 1265, in reflecttable self.dialect.reflecttable(conn, table, include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1664, in reflecttable sql = self._show_create_table(connection, table, charset) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1835, in _show_create_table raise exc.NoSuchTableError(full_name) sqlalchemy.exc.NoSuchTableError: tool_dependency Error in sys.excepthook: Traceback (most recent call last): File "/usr/lib/python2.7/dist-packages/apport_python_hook.py", line 66, in apport_excepthook from apport.fileutils import likely_packaged, get_recent_crashes ImportError: No module named apport.fileutils Original exception was: Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/opt/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.7.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0100_alter_tool_dependency_table_version_column.py", line 30, in upgrade ToolDependency_table = Table( "tool_dependency", metadata, autoload=True ) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 108, in __call__ return type.__call__(self, name, metadata, *args, **kwargs) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/schema.py", line 236, in __init__ _bind_or_error(metadata).reflecttable(self, include_columns=include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/engine/base.py", line 1265, in reflecttable self.dialect.reflecttable(conn, table, include_columns) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1664, in reflecttable sql = self._show_create_table(connection, table, charset) File "/opt/galaxy_dist/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.7.egg/sqlalchemy/databases/mysql.py", line 1835, in _show_create_table raise exc.NoSuchTableError(full_name) sqlalchemy.exc.NoSuchTableError: tool_dependency ======================================================
participants (1)
-
Bossers, Alex