Galaxy updated botched?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Galaxy updated botched?

Kandalaft, Iyad
Hi everyone

I was looking to upgrade galaxy to the latest verison.  I did the usual hg pull; hg update;.  I start up galaxy, it fetches some eggs, and then it tells me that I need a DB schema update.  Fine, I ran sh db_manage.sh upgrade.

All I see is a list of stack traces similar to the following.  I'm using mysql and all the tables that galaxy had created are running under the myisam engine....  Hence, no foreign key constraints allowed.  Why did this happen and can I simply convert all my tables to InnoDB to complete the database upgrade?  Should I restore the DB from a backup?

IntegrityError: (IntegrityError) (1215, 'Cannot add foreign key constraint') '\nCREATE TABLE history_dataset_collection_rating_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\thistory_dataset_collection_id INTEGER, \n\tuser_id INTEGER, \n\trating INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(history_dataset_collection_id) REFERENCES history_dataset_collection_association (id), \n\tFOREIGN KEY(user_id) REFERENCES galaxy_user (id)\n)\n\n' ()
(IntegrityError) (1215, 'Cannot add foreign key constraint') '\nCREATE TABLE history_dataset_collection_tag_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\thistory_dataset_collection_id INTEGER, \n\ttag_id INTEGER, \n\tuser_id INTEGER, \n\tuser_tname VARCHAR(255), \n\tvalue VARCHAR(255), \n\tuser_value VARCHAR(255), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(history_dataset_collection_id) REFERENCES history_dataset_collection_association (id), \n\tFOREIGN KEY(tag_id) REFERENCES tag (id), \n\tFOREIGN KEY(user_id) REFERENCES galaxy_user (id)\n)\n\n' ()
Creating history_dataset_collection_tag_association table failed: (IntegrityError) (1215, 'Cannot add foreign key constraint') '\nCREATE TABLE history_dataset_collection_tag_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\thistory_dataset_collection_id INTEGER, \n\ttag_id INTEGER, \n\tuser_id INTEGER, \n\tuser_tname VARCHAR(255), \n\tvalue VARCHAR(255), \n\tuser_value VARCHAR(255), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(history_dataset_collection_id) REFERENCES history_dataset_collection_association (id), \n\tFOREIGN KEY(tag_id) REFERENCES tag (id), \n\tFOREIGN KEY(user_id) REFERENCES galaxy_user (id)\n)\n\n' ()
Traceback (most recent call last):
  File "lib/galaxy/model/migrate/versions/0120_dataset_collections.py", line 179, in __create
    table.create()
  File "build/bdist.linux-x86_64/egg/sqlalchemy/schema.py", line 593, in create
    checkfirst=checkfirst)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 2302, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1972, in _run_visitor
    **kwargs).traverse_single(element)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/ddl.py", line 86, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1542, in _execute_ddl
    compiled
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
  File "build/bdist.linux-x86_64-ucs4/egg/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "build/bdist.linux-x86_64-ucs4/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue


Regards,

Iyad Kandalaft
Bioinformatics Programmer
Microbial Biodiversity Bioinformatics
Science & Technology Branch
Agriculture & Agri-Food Canada
[hidden email] | (613) 759-1228

___________________________________________________________
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 | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Kandalaft, Iyad

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: [hidden email]

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 | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Kandalaft, Iyad
In reply to this post by Kandalaft, Iyad

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  [hidden email]
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: '[hidden email]'
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: [hidden email]

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 | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

John Chilton-4
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
<[hidden email]> 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  [hidden email]
> 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: '[hidden email]'
> 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: [hidden email]
>
> 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/
Reply | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Dannon Baker-2
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 <[hidden email]> 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
<[hidden email]> 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  [hidden email]
> Telephone | Téléphone <a href="tel:613-759-1228" value="+16137591228">613-759-1228
> Facsimile | Télécopieur <a href="tel:613-759-1701" value="+16137591701">613-759-1701
> Teletypewriter | Téléimprimeur <a href="tel:613-773-2600" value="+16137732600">613-773-2600
>
> Government of Canada | Gouvernement du Canada
>
>
>
>
>
>
>
> From: Kandalaft, Iyad
> Sent: Tuesday, June 10, 2014 1:39 PM
> To: '[hidden email]'
> 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: [hidden email]
>
> Telephone | Téléphone <a href="tel:613-%20759-1228" value="+16137591228">613- 759-1228
>
> Facsimile | Télécopieur <a href="tel:613-759-1701" value="+16137591701">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/
Reply | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Kandalaft, Iyad

Dannon & John,

 

Thank you for your feedback.  I agree that documentation is a good start.

 

I’m still in the process of figuring out what actually happens to the database schema.  It may be completely fine with no referential integrity constraints, which I’m okay with to some degree (we’re still in the infancy stages with Galaxy).  I find it odd that these referential integrity errors popped up “all of a sudden” because I don’t recall noticing these errors when I first installed galaxy/initialized the database.  I did move the database to the new version of MySQL, where InnoDB is the default and referencing primary keys on a MyISAM table caused the problem.

 

As a side note, I would be interested to know your reasons for avoiding a hardcoded mysql engine.  If galaxy depends on referential integrity (not that I am assuming it does), then setting the MySQL engine to Memory or MyISAM would be disastrous.  Also, do you see any distinct advantages to using MyISAM for galaxy?

 

Regards,

 

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  [hidden email]
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: Dannon Baker [mailto:[hidden email]]
Sent: Wednesday, June 11, 2014 10:23 AM
To: John Chilton
Cc: Kandalaft, Iyad; [hidden email]
Subject: Re: [galaxy-dev] Galaxy updated botched?

 

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 <[hidden email]> 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
<[hidden email]> 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  [hidden email]
> Telephone | Téléphone <a href="tel:613-759-1228">613-759-1228
> Facsimile | Télécopieur <a href="tel:613-759-1701">613-759-1701
> Teletypewriter | Téléimprimeur <a href="tel:613-773-2600">613-773-2600
>
> Government of Canada | Gouvernement du Canada
>
>
>
>
>
>
>
> From: Kandalaft, Iyad
> Sent: Tuesday, June 10, 2014 1:39 PM
> To: '[hidden email]'
> 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: [hidden email]
>
> Telephone | Téléphone <a href="tel:613-%20759-1228">613- 759-1228
>
> Facsimile | Télécopieur <a href="tel:613-759-1701">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/
Reply | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

John Chilton-4
On Wed, Jun 11, 2014 at 3:26 PM, Kandalaft, Iyad
<[hidden email]> wrote:

> Dannon & John,
>
>
>
> Thank you for your feedback.  I agree that documentation is a good start.
>
>
>
> I’m still in the process of figuring out what actually happens to the
> database schema.  It may be completely fine with no referential integrity
> constraints, which I’m okay with to some degree (we’re still in the infancy
> stages with Galaxy).  I find it odd that these referential integrity errors
> popped up “all of a sudden” because I don’t recall noticing these errors
> when I first installed galaxy/initialized the database.  I did move the
> database to the new version of MySQL, where InnoDB is the default and
> referencing primary keys on a MyISAM table caused the problem.
>
>
>
> As a side note, I would be interested to know your reasons for avoiding a
> hardcoded mysql engine.  If galaxy depends on referential integrity (not
> that I am assuming it does), then setting the MySQL engine to Memory or
> MyISAM would be disastrous.  Also, do you see any distinct advantages to
> using MyISAM for galaxy?
>

Well there have never been any reports of anyone having problems with
MyISAM in particular - its the switch here that seems to be the
problem as far as I can tell. If I had to venture a guess as to why no
one has complained about non-InnoDB engines - I would say the
referential integrity constraints are never violated by the statements
that the ORM generates - so they are a nice to have backup primary
protection - but are not strictly required.

Certainly, if one has to use MySQL and has a choice I would recommend
InnoDB for most interesting applications and Galaxy in particular, but
*IF* we forced it we would break existing Galaxy installations using
MyISAM right? If one did not have to worry about breaking Galaxy
installations - I think a majority of the devteam would like drop
support for MySQL entirely (and I think our recommendation of Postgres
is unanimous).

If anyone out there has tired using Galaxy with strictly MyISAM tables
and found it untenable - by all means please chime in.

-John

>
>
> Regards,
>
>
>
> 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  [hidden email]
> 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: Dannon Baker [mailto:[hidden email]]
> Sent: Wednesday, June 11, 2014 10:23 AM
> To: John Chilton
> Cc: Kandalaft, Iyad; [hidden email]
> Subject: Re: [galaxy-dev] Galaxy updated botched?
>
>
>
> 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 <[hidden email]> 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
> <[hidden email]> 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  [hidden email]
>> 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: '[hidden email]'
>> 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: [hidden email]
>>
>> 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/
Reply | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Hans-Rudolf Hotz


On 06/12/2014 12:06 AM, John Chilton wrote:

>
> If anyone out there has tired using Galaxy with strictly MyISAM tables
> and found it untenable - by all means please chime in.
>

Well, due to several reasons we are (still) using  MySQL (version:
5.1.69) with only MyISAM tables. We have been running into troubles but
they were never related to running MyISAM.

However, I haven't done the latest Galaxy code upgrade, hence we are
still on database version '118'. So, I am curious to see what will
happen when I do the next upgrade (after GCC).

Also, on my list of things to do for this year is actually changing all
our Galaxy servers from MySQL to PostgreSQL. With my current workload, I
doubt I will manage to do this and have to postpone this 'adventure' to
next year.


Regards, Hans-Rudolf
___________________________________________________________
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 | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Dannon Baker-2
Hey Hans (and anyone else using MySQL),

Just to clarify -- MySQL doesn't have this (particular) error by default and as long as your server default table type has not and does not change you should be fine.  I've just successfully tested MySQL using MyISAM all the way to the current tip revision and you should be able to upgrade safely when the time comes.

-Dannon


On Thu, Jun 12, 2014 at 2:01 AM, Hans-Rudolf Hotz <[hidden email]> wrote:


On 06/12/2014 12:06 AM, John Chilton wrote:


If anyone out there has tired using Galaxy with strictly MyISAM tables
and found it untenable - by all means please chime in.


Well, due to several reasons we are (still) using  MySQL (version: 5.1.69) with only MyISAM tables. We have been running into troubles but they were never related to running MyISAM.

However, I haven't done the latest Galaxy code upgrade, hence we are still on database version '118'. So, I am curious to see what will happen when I do the next upgrade (after GCC).

Also, on my list of things to do for this year is actually changing all our Galaxy servers from MySQL to PostgreSQL. With my current workload, I doubt I will manage to do this and have to postpone this 'adventure' to next year.


Regards, Hans-Rudolf

___________________________________________________________
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/
Reply | Threaded
Open this post in threaded view
|

Re: Galaxy updated botched?

Kandalaft, Iyad

Thanks to everyone for their input.

 

John, your explanation was excellent.  I completely agree with the implementation of galaxy as it stands now (with a minor documentation warning about default mysql engine switch in v5.5).  Personally, I either place trust in the ORM to maintain referential integrity or I offload it to the DB Engine.  Hence, I will keep the MyISAM tables going for now after I do some validation against an InnoDB database (please no MyISAM engine hate-mail LOL).

 

With respect to dropping MySQL support, I will let that one go by for now LOL.

Thanks again for your contributions and maintenance of this great platform.  I feel that it will be a game changer in the bioinformatics community.

 

Regards,

 

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  [hidden email]
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: Dannon Baker [mailto:[hidden email]]
Sent: Thursday, June 12, 2014 9:16 AM
To: Hans-Rudolf Hotz
Cc: John Chilton; Kandalaft, Iyad; [hidden email]
Subject: Re: [galaxy-dev] Galaxy updated botched?

 

Hey Hans (and anyone else using MySQL),

 

Just to clarify -- MySQL doesn't have this (particular) error by default and as long as your server default table type has not and does not change you should be fine.  I've just successfully tested MySQL using MyISAM all the way to the current tip revision and you should be able to upgrade safely when the time comes.

 

-Dannon

 

On Thu, Jun 12, 2014 at 2:01 AM, Hans-Rudolf Hotz <[hidden email]> wrote:



On 06/12/2014 12:06 AM, John Chilton wrote:


If anyone out there has tired using Galaxy with strictly MyISAM tables
and found it untenable - by all means please chime in.

 

Well, due to several reasons we are (still) using  MySQL (version: 5.1.69) with only MyISAM tables. We have been running into troubles but they were never related to running MyISAM.

However, I haven't done the latest Galaxy code upgrade, hence we are still on database version '118'. So, I am curious to see what will happen when I do the next upgrade (after GCC).

Also, on my list of things to do for this year is actually changing all our Galaxy servers from MySQL to PostgreSQL. With my current workload, I doubt I will manage to do this and have to postpone this 'adventure' to next year.


Regards, Hans-Rudolf


___________________________________________________________
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/