From hrh@fmi.ch Tue Aug 26 11:38:03 2014 From: Hans-Rudolf Hotz To: galaxy-dev@lists.galaxyproject.org Subject: Re: [galaxy-dev] problems with database migration 119 -> 120 Date: Tue, 26 Aug 2014 17:37:54 +0200 Message-ID: <53FCA9D2.8020101@fmi.ch> In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1691041575390569657==" --===============1691041575390569657== Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi John Thanks for the insights Hi Iyad Yes, the user account has the ability to drop and alter tables. Hans-Rudolf On 08/26/2014 03:08 PM, John Chilton wrote: > Well it looks like the migration file has these columns listed in a > different order than the mapping Galaxy uses - and the order yours > appeared in were the ones from Galaxy's mapping file. So somehow > Galaxy is automatically creating those tables prior to running the > migration based on the code in Galaxy proper. That is odd. > > Ultimately though - I don't think it is harmful that the order is > wrong since Galaxy always references the columns by name instead of > order. I would be more concerned that you aren't getting the right > indices / foreign keys - but it looks like you are still on MyISAM so > you aren't going to get a ton of forced integrity anyway (and maybe > this is why these errors have been okay in the past?). > > -John > > On Tue, Aug 26, 2014 at 8:52 AM, Kandalaft, Iyad > wrote: >> I've ran into other mysql problems but never anything like that. >> >> This is just a hunch and not based on anything concrete, but using an outd= ated version of sqlalchemy is probably not helping things. We're talking 2 m= igrations. Are they even implementing fixes for 0.7 anymore? It is odd you = would ever get "Table already exists" since the ORM's job is to ensure the mo= del consistency in the database. Why it would try to create the table before= it checks for its existence is beyond me. >> >> You might want to make sure that the database user account has the ability= to drop and alter tables. It may be that it tried to revert a failed upgrad= e and it wasn't able to. >> >> Iyad Kandalaft >> >> >> >> -----Original Message----- >> From: galaxy-dev-bounces(a)lists.bx.psu.edu [mailto:galaxy-dev-bounces(a)l= ists.bx.psu.edu] On Behalf Of Hans-Rudolf Hotz >> Sent: Tuesday, August 26, 2014 5:22 AM >> To: >> Subject: [galaxy-dev] problems with database migration 119 -> 120 >> >> 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 . >> >> 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 >> ___________________________________________________________ >> 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: >> http://lists.bx.psu.edu/ >> >> To search Galaxy mailing lists use the unified search at: >> http://galaxyproject.org/search/mailinglists/ >> >> ___________________________________________________________ >> 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: >> http://lists.bx.psu.edu/ >> >> To search Galaxy mailing lists use the unified search at: >> http://galaxyproject.org/search/mailinglists/ --===============1691041575390569657==--