problem with "~/lib/galaxy/app.py" after code upgrade to "March 12, 2012" release ('6799:40f1816d6857')
Hi We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857'). We have encountered two issues, which I guess are related: If I restart the server, I get: galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py 36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) ...well, I don't like this fix, and I wonder if there is a better one? I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database: at step "90to91" and "91to92" I get the following: 90 -> 91... Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table. 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92... Migration script to create the migrate_tools table. 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution? I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10) and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement' mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> Has anybody seen similar issues or can shed some light on our problems? Thank you very much for your help Hans
Hi everybody I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far. Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server. Thank you very much for any help Regards, Hans On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
Hello Hans, I've stayed out of this one due to your use of mysql which I cannot personally support as I restrict my environment to sqlite and postgres. However, assuming this is not a problem restricted to mysql, can you try the following from your Galaxy installation directory (you should backup your database before attempting this)? $sh manage_db.sh downgrade 90 After this, $sh manage_db.sh upgrade Greg Von Kuster On Apr 24, 2012, at 10:09 AM, Hans-Rudolf Hotz wrote:
Hi everybody
I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far.
Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server.
Thank you very much for any help
Regards, Hans
On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Hi Greg I was hoping to avoid this....I will let you know the outcome. On a related note: are there any plans to migrate all MySQL users to postgres? It is probably easier in the longterm.....maybe a topic we can discuss in Chicago. Thank you very much for your reply Regards, Hans On 04/24/2012 04:32 PM, Greg Von Kuster wrote:
Hello Hans,
I've stayed out of this one due to your use of mysql which I cannot personally support as I restrict my environment to sqlite and postgres. However, assuming this is not a problem restricted to mysql, can you try the following from your Galaxy installation directory (you should backup your database before attempting this)?
$sh manage_db.sh downgrade 90
After this, $sh manage_db.sh upgrade
Greg Von Kuster
On Apr 24, 2012, at 10:09 AM, Hans-Rudolf Hotz wrote:
Hi everybody
I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far.
Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server.
Thank you very much for any help
Regards, Hans
On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Hi hans, On Apr 24, 2012, at 10:49 AM, Hans-Rudolf Hotz wrote:
Hi Greg
I was hoping to avoid this....I will let you know the outcome.
This really shouldn't be a problem, the migration scripts for 91, 92 simply create new tables with 91 generating new data to insert, and 92 inserting a single new row. Since it looks lie you haven't been able to upgrade in your production environment, dropping these tables and re-creating them will not lose any data.
On a related note: are there any plans to migrate all MySQL users to postgres? It is probably easier in the longterm.....maybe a topic we can discuss in Chicago.
This is completely up to you. Galaxy supports sqlite and postgres. Mysql should work, but support for it is minimal, so you are more on your own. Some of us on the core team feel that support for mysql should go away completely (myself included), while others feel support for it should continue. The latter category continues to win the debate, but again, support for mysql is not at the level we provide for postgres or sqlite.
Thank you very much for your reply
Regards, Hans
On 04/24/2012 04:32 PM, Greg Von Kuster wrote:
Hello Hans,
I've stayed out of this one due to your use of mysql which I cannot personally support as I restrict my environment to sqlite and postgres. However, assuming this is not a problem restricted to mysql, can you try the following from your Galaxy installation directory (you should backup your database before attempting this)?
$sh manage_db.sh downgrade 90
After this, $sh manage_db.sh upgrade
Greg Von Kuster
On Apr 24, 2012, at 10:09 AM, Hans-Rudolf Hotz wrote:
Hi everybody
I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far.
Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server.
Thank you very much for any help
Regards, Hans
On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Dear Hans,
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
This error is because SQLAlchemy cannot load the controlled schema version info from the DB, the result is empty, so I think its trying to load the migrate_version and migrate_tools tables. Make sure the tables look like this hopefully they have each one row of data like this: mysql> select * from migrate_version; +---------------+--------------------------+---------+ | repository_id | repository_path | version | +---------------+--------------------------+---------+ | Galaxy | lib/galaxy/model/migrate | 93 | +---------------+--------------------------+---------+ 1 row in set (0.01 sec) mysql> select * from migrate_tools; +---------------+------------------------------+---------+ | repository_id | repository_path | version | +---------------+------------------------------+---------+ | GalaxyTools | lib/galaxy/tool_shed/migrate | 1 | +---------------+------------------------------+---------+ 1 row in set (0.01 sec) hth, Leandro On Tue, Apr 24, 2012 at 4:09 PM, Hans-Rudolf Hotz <hrh@fmi.ch> wrote:
Hi everybody
I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far.
Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server.
Thank you very much for any help
Regards, Hans
On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Hi Leandro Thank you very much for your e-mail. Indeed, the 'migrate_tools' table is empty: mysql> select * from galaxy_xenon1.migrate_version; +---------------+--------------------------+---------+ | repository_id | repository_path | version | +---------------+--------------------------+---------+ | Galaxy | lib/galaxy/model/migrate | 93 | +---------------+--------------------------+---------+ 1 row in set (0.02 sec) mysql> select * from galaxy_xenon1.migrate_tools; Empty set (0.05 sec) mysql> but I guess, the table itself is correct? mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> tomorrow, I will try adding the missing line manually Thanks, Hans On 04/24/2012 05:46 PM, Leandro Hermida wrote:
Dear Hans,
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
This error is because SQLAlchemy cannot load the controlled schema version info from the DB, the result is empty, so I think its trying to load the migrate_version and migrate_tools tables. Make sure the tables look like this hopefully they have each one row of data like this:
mysql> select * from migrate_version; +---------------+--------------------------+---------+ | repository_id | repository_path | version | +---------------+--------------------------+---------+ | Galaxy | lib/galaxy/model/migrate | 93 | +---------------+--------------------------+---------+ 1 row in set (0.01 sec)
mysql> select * from migrate_tools; +---------------+------------------------------+---------+ | repository_id | repository_path | version | +---------------+------------------------------+---------+ | GalaxyTools | lib/galaxy/tool_shed/migrate | 1 | +---------------+------------------------------+---------+ 1 row in set (0.01 sec)
hth, Leandro
On Tue, Apr 24, 2012 at 4:09 PM, Hans-Rudolf Hotz<hrh@fmi.ch> wrote:
Hi everybody
I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far.
Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server.
Thank you very much for any help
Regards, Hans
On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Hi Leandro brilliant! - adding the line manually did indeed fix the problem and I could remove the re-activate line 36 and 37 in ~/lib/galaxy/app.py Thank you very much! now have to find out why this line was not inserted in the first place.... Hans On 04/24/2012 06:04 PM, Hans-Rudolf Hotz wrote:
Hi Leandro
Thank you very much for your e-mail. Indeed, the 'migrate_tools' table is empty:
mysql> select * from galaxy_xenon1.migrate_version; +---------------+--------------------------+---------+ | repository_id | repository_path | version | +---------------+--------------------------+---------+ | Galaxy | lib/galaxy/model/migrate | 93 | +---------------+--------------------------+---------+ 1 row in set (0.02 sec)
mysql> select * from galaxy_xenon1.migrate_tools; Empty set (0.05 sec)
mysql>
but I guess, the table itself is correct?
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
tomorrow, I will try adding the missing line manually
Thanks, Hans
On 04/24/2012 05:46 PM, Leandro Hermida wrote:
Dear Hans,
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py",
line 42, in _load data = list(result)[0] IndexError: list index out of range
This error is because SQLAlchemy cannot load the controlled schema version info from the DB, the result is empty, so I think its trying to load the migrate_version and migrate_tools tables. Make sure the tables look like this hopefully they have each one row of data like this:
mysql> select * from migrate_version; +---------------+--------------------------+---------+ | repository_id | repository_path | version | +---------------+--------------------------+---------+ | Galaxy | lib/galaxy/model/migrate | 93 | +---------------+--------------------------+---------+ 1 row in set (0.01 sec)
mysql> select * from migrate_tools; +---------------+------------------------------+---------+ | repository_id | repository_path | version | +---------------+------------------------------+---------+ | GalaxyTools | lib/galaxy/tool_shed/migrate | 1 | +---------------+------------------------------+---------+ 1 row in set (0.01 sec)
hth, Leandro
On Tue, Apr 24, 2012 at 4:09 PM, Hans-Rudolf Hotz<hrh@fmi.ch> wrote:
Hi everybody
I usually don't like people sending the same e-mail several times, so I apologize for just doing this, since nobody has replied to last week's email so far.
Although, we do not encounter any problems at the moment, I fear we will run into problems at a later stage (ie at the next code update). This is our production server.
Thank you very much for any help
Regards, Hans
On 04/16/2012 03:43 PM, Hans-Rudolf Hotz wrote:
Hi
We are in the process of migrating our Galaxy servers to the current "March 12, 2012" release ('6799:40f1816d6857').
We have encountered two issues, which I guess are related:
If I restart the server, I get:
galaxy.model.migrate.check INFO 2012-04-16 09:24:50,391 At database version 93 galaxy.tool_shed.migrate.check DEBUG 2012-04-16 09:24:50,407 MySQL_python egg successfully loaded for mysql dialect Traceback (most recent call last): File "/****/galaxy_dist/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/****/galaxy_dist/lib/galaxy/app.py", line 37, in __init__ verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/****/galaxy_dist/lib/galaxy/tool_shed/migrate/check.py", line 42, in verify_tools db_schema = schema.ControlledSchema( engine, migrate_repository ) File
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py",
line 24, in __init__ self._load() File
"/****/galaxy_dist/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py",
line 42, in _load data = list(result)[0] IndexError: list index out of range
I can 'temporary' fix this by commenting-out line 36 and 37 in ~/lib/galaxy/app.py
36# from galaxy.tool_shed.migrate.check import verify_tools 37# verify_tools( self, db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options )
...well, I don't like this fix, and I wonder if there is a better one?
I have a suspicion, that this is related to problems I encountered during the database schema upgrade to version 93 for our MySQL database:
at step "90to91" and "91to92" I get the following:
90 -> 91...
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,719 Creating tool_version table failed: (OperationalError) (1050, "Table 'tool_version' already exists") u'\nCREATE TABLE tool_version (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcreate_time DATETIME, \n\tupdate_time DATETIME, \n\ttool_id VARCHAR(255), \n\ttool_shed_repository_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_shed_repository_id) REFERENCES tool_shed_repository (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () 0091_add_tool_version_tables DEBUG 2012-04-16 08:55:49,720 Creating tool_version_association table failed: (OperationalError) (1050, "Table 'tool_version_association' already exists") u'\nCREATE TABLE tool_version_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttool_id INTEGER NOT NULL, \n\tparent_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(tool_id) REFERENCES tool_version (id), \n\t FOREIGN KEY(parent_id) REFERENCES tool_version (id)\n)\n\n' () Added 0 rows to the new tool_version table. done 91 -> 92...
Migration script to create the migrate_tools table.
0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () 0092_add_migrate_tools_table DEBUG 2012-04-16 08:55:49,920 Creating migrate_tools table failed: (OperationalError) (1050, "Table 'migrate_tools' already exists") u'\nCREATE TABLE migrate_tools (\n\trepository_id VARCHAR(255), \n\trepository_path TEXT, \n\tversion INTEGER\n)\n\n' () done
the bizarre thing is, all three tables: 'migrate_tools', 'tool_version_association','tool_version' are present.....well, this explains the error: 'OperationalError 1050, "Table 'migrate_tools' already exists" '....but why were the commands executed a second and third time? - what happened during the first execution?
I can execute the three create statements without problems in a dummy database on the same MySQl server (Server version: 5.1.41-3ubuntu12.10)
and if I compare the "describe migrate_tools" outcome for the real database (galaxy_xenon1) and the dummy database (galaxy_xenon1_old), I don't see a difference and it corresponds to the 'create statement'
mysql> describe galaxy_xenon1.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> describe galaxy_xenon1_old.migrate_tools; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | repository_id | varchar(255) | YES | | NULL | | | repository_path | text | YES | | NULL | | | version | int(11) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql>
Has anybody seen similar issues or can shed some light on our problems?
Thank you very much for your help
Hans
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
participants (3)
-
Greg Von Kuster -
Hans-Rudolf Hotz -
Leandro Hermida