Hey Iyad, I just want to second (and add slightly) to what John said here. I had also failed to reproduce this locally, but that makes perfect sense now -- I'm glad you figured it out, and thanks for looking into this so closely and reporting back.
Like John said, I wouldn't modify the migrations to force or assume a particular engine, but if you wanted to make a contribution to the code base it would probably be worth improving the detection and reporting of this particular error condition to help anyone else who might run into the issue -- if you wanted to take a stab at that. On Wed, Jun 11, 2014 at 10:08 AM, John Chilton <jmchil...@gmail.com> wrote: > Spent a couple hours yesterday trying to track down this - I was not > getting anywhere though and I see why now. > > Well this definitely an unfortunate situation - but I think > documentation improvements are the right fix not enforcing the engine > type in sqlalchemy migrations. For one, I don't think we can assume a > particular engine type because different deployers may already going > to have both kinds out there right? > > I think it would be better to update the wiki to encourage InnoDB for > all new installations and warn this can happen for MySQL upgrades. > > Is this okay? > > Sorry about this. > > -John > > > > On Wed, Jun 11, 2014 at 8:48 AM, Kandalaft, Iyad > <iyad.kandal...@agr.gc.ca> wrote: > > This is a follow up for those that are interested with regards to my > failed > > schema upgrade. > > > > > > > > I believe I have determined why all the tables are set to use the MyISAM > > engine. When I initialized galaxy on our enterprise servers, they were > > running a dated version of CentOS. Hence, the OS defaults to a dated > MySQL > > version. MySQL only switched to using InnoDB (over MyISAM) as the > default > > engine in version 5.5. If I’m not mistaken, I initialized galaxy in > MySQL > > 5.1 without changing the default engine to InnoDB (big mistake). Due to > my > > ignorance, I will now have to try to compare v118 of our database with a > new > > install of galaxy running schema v118. Then, I will try “migrating” to > the > > true schema state without destroying the data. > > > > > > > > I do feel that I should still modify the galaxy schema to set the MySQL > > engine to InnoDB to thwart problems like this for other unsuspecting > users. > > If anyone can point me at some documentation about how Galaxy schema > changes > > should occur in this case, that would be great. > > > > As per my previous comment, I’m not certain whether I would edit all > schema > > versions to ensure table definitions include the mysql_engine=InnoDB > > attribute or whether using a DDL event in SQLAlchemy. I suspect that > > setting this option globally would mean that future developers don’t > need to > > remember to define mysql_engine on every new table. > > > > > > > > > > > > Iyad Kandalaft > > > > Microbial Biodiversity Bioinformatics > > > > Agriculture and Agri-Food Canada | Agriculture et Agroalimentaire Canada > > 960 Carling Ave.| 960 Ave. Carling > > > > Ottawa, ON| Ottawa (ON) K1A 0C6 > > > > E-mail Address / Adresse courriel iyad.kandal...@agr.gc.ca > > Telephone | Téléphone 613-759-1228 > > Facsimile | Télécopieur 613-759-1701 > > Teletypewriter | Téléimprimeur 613-773-2600 > > > > Government of Canada | Gouvernement du Canada > > > > > > > > > > > > > > > > From: Kandalaft, Iyad > > Sent: Tuesday, June 10, 2014 1:39 PM > > To: 'galaxy-...@bx.psu.edu' > > Subject: Re: Galaxy updated botched? > > > > > > > > Hi Everyone, > > > > > > > > This is follow-up information/questions to the issue I ran into with the > > galaxy June 2nd, 2014 update. I hope to receive feedback on how to > proceed. > > > > > > > > Background: > > > > - Running Galaxy (DB Schema 118) with a MySQL 5.5 back-end > > > > - When updating galaxy to the june 2nd release, the v120 DB > schema > > has referential integrity constraints, which produced errors during the > > upgrade. > > > > - Completed two galaxy updates in the past 4 months without > > encountering this before (schema changes included) > > > > > > > > Discussion: > > > > In the past, referential integrity in the DB schema was never an issue. > I > > checked backups and the current database to find that the database tables > > are using the MyISAM engine. MyISAM = no referential integrity > support, no > > transactions. > > > > I reviewed galaxy’s SQLAlchemy templates and determined that > > mysql_engine='InnoDB' isn’t set on tables. This explains why all tables > > were created with the MyISAM engine. If the mysql_engine is not innodb, > SQL > > Alchemy is supposed to drop any referential integrity constraints > defined in > > the schema. What I don’t understand is why SQL Alchemy is no longer > > ignoring the referential integrity constraints. > > > > > > > > Going forward, can anyone propose how I can salvage the database or > continue > > ignoring referential integrity for now? > > > > Assuming that my limited understanding of SQLAlchemy holds water, I was > > looking at fixing the galaxy code base but I need some clarification on > the > > DB schema versioning. Do I edit schema v1 and add the appropriate table > > args to make every table an innodb engine table or do I add a new schema > and > > modify all tables to use the innodb engine? Alternatively, I can use DDL > > events > > > > def after_create(target, connection, **kw): > > > > connection.execute("ALTER TABLE %s ENGINE=InnoDB; > > > > (target.name, target.name)) > > > > > > > > Thank you for your help. > > > > > > > > Regards, > > > > Iyad Kandalaft > > > > > > > > Bioinformatics Application Developer > > > > Agriculture and Agri-Food Canada | Agriculture et Agroalimentaire Canada > > > > KW Neatby Bldg | éd. KW Neatby > > > > 960 Carling Ave| 960, avenue Carling > > > > Ottawa, ON | Ottawa (ON) K1A 0C6 > > > > E-mail Address / Adresse courriel: iyad.kandal...@agr.gc.ca > > > > Telephone | Téléphone 613- 759-1228 > > > > Facsimile | Télécopieur 613-759-1701 > > > > Government of Canada | Gouvernement du Canada > > > > > > > > > > ___________________________________________________________ > > 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/ >
___________________________________________________________ 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/