MySQL Database creation error

[joneswac]

Set up

• Debian 10
• Galaxy Release 19.05 (clone via git)
• MySQL Ver 14.14 Distrib 5.7.27

Problem
During initial set up of the galaxy server I am encountering the following error:

0145_add_workflow_step_input ERROR 2019-08-01 13:17:19,984
[p:14134,w:0,m:0] [MainThread] Dropping index
'Index('ix_workflow_step_connection_output_step_id',
Column('output_step_id', INTEGER(display_width=11),
ForeignKey(u'workflow_step.id'), table=<workflow_step_connection>))'
from table 'workflow_step_connection' failed

OperationalError: (MySQLdb._exceptions.OperationalError) (1553, "Cannot
drop index 'ix_workflow_step_connection_output_step_id': needed in a
foreign key constraint") [SQL: u'\nDROP INDEX
ix_workflow_step_connection_output_step_id ON workflow_step_connection']
(Background on this error at: http://sqlalche.me/e/e3q8)
0145_add_workflow_step_input ERROR 2019-08-01 13:17:19,988
[p:14134,w:0,m:0] [MainThread] Dropping index
'Index('ix_workflow_step_connection_input_step_id',
Column('input_step_id', INTEGER(display_width=11),
ForeignKey(u'workflow_step.id'), table=<workflow_step_connection>))'
from table 'workflow_step_connection' failed

OperationalError: (MySQLdb._exceptions.OperationalError) (1553, "Cannot
drop index 'ix_workflow_step_connection_input_step_id': needed in a
foreign key constraint") [SQL: u'\nDROP INDEX
ix_workflow_step_connection_input_step_id ON workflow_step_connection']
(Background on this error at: http://sqlalche.me/e/e3q8)
galaxy.model.migrate.versions.util ERROR 2019-08-01 13:17:20,005
[p:14134,w:0,m:0] [MainThread] Creating table 'workflow_step_input'
failed.

OperationalError: (MySQLdb._exceptions.OperationalError) (1170,
"BLOB/TEXT column 'name' used in key specification without a key
length") [SQL: u'\nCREATE TABLE workflow_step_input (\n\tid INTEGER NOT
NULL AUTO_INCREMENT, \n\tworkflow_step_id INTEGER, \n\tname TEXT,
\n\tmerge_type TEXT, \n\tscatter_type TEXT, \n\tvalue_from MEDIUMBLOB,
\n\tvalue_from_type TEXT, \n\tdefault_value MEDIUMBLOB,
\n\tdefault_value_set BOOL, \n\truntime_value BOOL, \n\tPRIMARY KEY
(id), \n\tUNIQUE (workflow_step_id, name), \n\tFOREIGN
KEY(workflow_step_id) REFERENCES workflow_step (id), \n\tCHECK
(default_value_set IN (0, 1)), \n\tCHECK (runtime_value IN (0,
1))\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8)
galaxy.model.migrate.versions.util ERROR 2019-08-01 13:17:20,022
[p:14134,w:0,m:0] [MainThread] Creating table 'workflow_step_connection'
failed.

IntegrityError: (MySQLdb._exceptions.IntegrityError) (1215, 'Cannot add
foreign key constraint') [SQL: u'\nCREATE TABLE workflow_step_connection
(\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\toutput_step_id INTEGER,
\n\tinput_step_input_id INTEGER, \n\toutput_name TEXT,
\n\tinput_subworkflow_step_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN
KEY(output_step_id) REFERENCES workflow_step (id), \n\tFOREIGN
KEY(input_step_input_id) REFERENCES workflow_step_input (id),
\n\tFOREIGN KEY(input_subworkflow_step_id) REFERENCES workflow_step
(id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/gkpj)

The galaxy.yml config line for this connection is as follows:

database_connection: mysql://user:password@localhost/galaxy?unix_socket=/var/run/mysqld/mysqld.sock

[martenson]

@joneswac if you have the option I would really recommend using postgres instead. MySQL is lately getting minimal attention within the project and I don't see that changing any time soon.

[joneswac]

That's no trouble. I don't care what you do with this issue, but you should note that MySQL isn't supported on your administration guide page: https://docs.galaxyproject.org/en/master/admin/production.html#switching-to-a-database-server

It says that Postgres is preferred -- but maybe a !warning would be better.

[hexylena]

fixed in #8407

[nsoranzo]

So the issue for MySQL is in this part of the log:

[p:14134,w:0,m:0] [MainThread] Creating table 'workflow_step_input'
failed.

OperationalError: (MySQLdb._exceptions.OperationalError) (1170,
"BLOB/TEXT column 'name' used in key specification without a key
length") [SQL: u'\nCREATE TABLE workflow_step_input (\n\tid INTEGER NOT
NULL AUTO_INCREMENT, \n\tworkflow_step_id INTEGER, \n\tname TEXT,
\n\tmerge_type TEXT, \n\tscatter_type TEXT, \n\tvalue_from MEDIUMBLOB,
\n\tvalue_from_type TEXT, \n\tdefault_value MEDIUMBLOB,
\n\tdefault_value_set BOOL, \n\truntime_value BOOL, \n\tPRIMARY KEY
(id), \n\tUNIQUE (workflow_step_id, name), \n\tFOREIGN
KEY(workflow_step_id) REFERENCES workflow_step (id), \n\tCHECK
(default_value_set IN (0, 1)), \n\tCHECK (runtime_value IN (0,
1))\n)\n\n']

due to the fact that in migration lib/galaxy/model/migrate/versions/0145_add_workflow_step_input.py we create the table workflow_step_input which has a column name of type TEXT which is then mentioned in a UNIQUE constraint: https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

To fix this, we can choose either of the following options:

• change the type of name column to TrimmedString(255)
• add the UniqueConstraint only for PostgreSQL/SQLite

[DanBerrios]

We are also using mysql and having the same issue...we cannot migrate up from 135->136 due to mysql-specific constraints on length of identifiers created during the migration. Is there a work around for this? We are trying to get our 17.01 instance up to 19.....

[joneswac]

I switched to PostgreSQL — that seemed the only viable solution to me.

On Oct 18, 2019, at 12:53 PM, Dan Berrios ***@***.***> wrote: We are also using mysql and having the same issue...we cannot migrate up from 135->136 due to mysql-specific constraints on length of identifiers created during the migration. Is there a work around for this? We are trying to get our 17.01 instance up to 19..... — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#8400?email_source=notifications&email_token=AFOUMOYVCGV4FOALLZVE7NLQPHZ27A5CNFSM4IITJVJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBVKPPY#issuecomment-543860671>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFOUMOZGD6U3FOYWH5Z5QCLQPHZ27ANCNFSM4IITJVJQ>.

Wesley Jones Systems/Network Administrator University of Northern Iowa Information Technology Network & Infrastructure Services 15 Curris Business Building Cedar Falls, IA 50614-0121 Phone: 319-273-2541 <tel:319-273-2541> Email: wesley.jones@uni.edu <mailto:wesley.jones@uni.edu>

[nsoranzo]

The migrations have been fixed in #8473, which is part of the upcoming 19.09 release.