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<mailto: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/

Reply via email to