Hi Everyone,

This is follow-up information/questions to the issue I ran into with the galaxy June 2^nd, 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 2^nd 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.Kandalaft@agr.gc.ca

Telephone | Téléphone 613- 759-1228

Facsimile | Télécopieur 613-759-1701

Government of Canada | Gouvernement du Canada