problems with database migration 119 -> 120

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

problems with database migration 119 -> 120

Hans-Rudolf Hotz
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 <table>.

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

Re: problems with database migration 119 -> 120

Kandalaft, Iyad
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 outdated version of sqlalchemy is probably not helping things.  We're talking 2 migrations.  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 model 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 upgrade and it wasn't able to.

Iyad Kandalaft



-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Hans-Rudolf Hotz
Sent: Tuesday, August 26, 2014 5:22 AM
To: <[hidden email]>
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 <table>.

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

Re: problems with database migration 119 -> 120

John Chilton-4
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
<[hidden email]> 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 outdated version of sqlalchemy is probably not helping things.  We're talking 2 migrations.  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 model 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 upgrade and it wasn't able to.
>
> Iyad Kandalaft
>
>
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of Hans-Rudolf Hotz
> Sent: Tuesday, August 26, 2014 5:22 AM
> To: <[hidden email]>
> 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 <table>.
>
> 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/

___________________________________________________________
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: problems with database migration 119 -> 120

Hans-Rudolf Hotz
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
> <[hidden email]> 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 outdated version of sqlalchemy is probably not helping things.  We're talking 2 migrations.  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 model 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 upgrade and it wasn't able to.
>>
>> Iyad Kandalaft
>>
>>
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:[hidden email]] On Behalf Of Hans-Rudolf Hotz
>> Sent: Tuesday, August 26, 2014 5:22 AM
>> To: <[hidden email]>
>> 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 <table>.
>>
>> 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/
___________________________________________________________
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: problems with database migration 119 -> 120

Nate Coraor (nate@bx.psu.edu)
Hi Hans,

I'll add to John's statement about the ordering - a different ordering than the model is entirely possible because alters to add columns always add them at the end. The tables for Galaxy Main are so old that they are in a very different order than most newer installations. As John says, SQLAlchemy always references columns by name, so the order does not matter.

The reason we are using an old version of SQLAlchemy is due to it being the last version that can be used with sqlalchemy_migrate. Upgrading to newer versions will also require a significant amount of work to rewrite to use a new versioning system. This will be done eventually but hasn't reached the top of the pile yet.

Finally, you should switch to PostgreSQL. ;D

--nate

On Aug 26, 2014, at 11:37 AM, Hans-Rudolf Hotz <[hidden email]> wrote:

> 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
>> <[hidden email]> 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 outdated version of sqlalchemy is probably not helping things.  We're talking 2 migrations.  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 model 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 upgrade and it wasn't able to.
>>>
>>> Iyad Kandalaft
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: [hidden email] [mailto:[hidden email]] On Behalf Of Hans-Rudolf Hotz
>>> Sent: Tuesday, August 26, 2014 5:22 AM
>>> To: <[hidden email]>
>>> 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 <table>.
>>>
>>> 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/
> ___________________________________________________________
> 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/