Hi all
I am in the process of updating our galaxy servers (from
"release_2014.04.14" to "latest_2014.08.11").
when I execute
~/lib/galaxy/model/migrate/versions/0120_dataset_collections.py
as part of the 'manage_db.sh upgrade' I run into a bizarre error:
First, it produces 10 "Mysql 1050 Error 'Table already exists' ", I have
encountered this before, and usually everything is fine. The table gets
created and for whatever reason, the command get's executed a second
time - no big deal.
However, this time for two of those ten table the situation has been
different. As usual, I have checked all the tables (where I got the
errors) with the MySQL command describe <table>.
For two tables:
history_dataset_collection_association
library_dataset_collection_association
the order of the columns was wrong (ie did not correspond to the order
in the create statement) - see below for example.
I have dropped the tables and executed the create statements manually,
everything seems fine, eg
mysql> describe library_dataset_collection_association;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| collection_id | int(11) | YES | MUL | NULL | |
| folder_id | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
| deleted | tinyint(1) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> drop table library_dataset_collection_association;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE library_dataset_collection_association (
-> id INTEGER NOT NULL AUTO_INCREMENT,
-> collection_id INTEGER,
-> name VARCHAR(255),
-> deleted BOOL,
-> folder_id INTEGER,
-> PRIMARY KEY (id),
-> FOREIGN KEY(collection_id) REFERENCES dataset_collection (id),
-> CHECK (deleted IN (0, 1)),
-> FOREIGN KEY(folder_id) REFERENCES library_folder (id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> describe library_dataset_collection_association;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| collection_id | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
| deleted | tinyint(1) | YES | | NULL | |
| folder_id | int(11) | YES | MUL | NULL | |
+---------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
Has anyone else (among those few who are still using MySQL) seen
something similar?
Regards, Hans-Rudolf
PS: Please don't make any comments about the fact, that I should change
to PostgreSQL. I am more than aware of that, and eventually, I will
do it. ;)
--
Hans-Rudolf Hotz, PhD
Bioinformatics Support
Friedrich Miescher Institute for Biomedical Research
Maulbeerstrasse 66
4058 Basel/Switzerland