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. ;)