Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05
I do :) Thank you Dannon. Fred De : Dannon Baker Envoyé : mardi 26 juin 2018 02:07:07 À : SAPET, Frederic Cc : Hans-Rudolf Hotz; galaxy-dev@lists.galaxyproject.org Objet : Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05 Frederic, you may want to follow this issue to track a resolution here: https://github.com/galaxyproject/galaxy/issues/6401 On Thu, Jun 14, 2018 at 1:24 AM SAPET, Frederic mailto:frederic.sa...@biogemma.com>> wrote: Hi Hans, We have a very nice MySQL server with a lot of RAM and associated storage (as well as Oracle for some other applications) So we would like to avoid a third system to maintain. And mysql is working well for my current 17.09 galaxy instance. Fred -Message d'origine- De : Hans-Rudolf Hotz [mailto:h...@fmi.ch<mailto:h...@fmi.ch>] Envoyé : jeudi 14 juin 2018 08:25 À : SAPET, Frederic mailto:frederic.sa...@biogemma.com>>; Galaxy-dev mailto:galaxy-dev@lists.galaxyproject.org>> Objet : Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05 Hi Fred Sorry, I don't have an answer I am just wondering: If you start from 'scratch', why don't you use PostgreSQL? Regards, Hans-Rudolf On 06/13/2018 05:41 PM, SAPET, Frederic wrote: > Hi, > > I try to install galaxy 18-05. My database backend is MysqL (5.7.11-4 > Percona Server) > > I start from scratch (drop database, new directory with galaxy files > fetched with git) > > So the database is first created when run.sh is used : : > > Creating database for URI [mysql://…. > > I see warnings about duplicated indexes and OperationalError from > SQLAlchemy. > > Some tables are then missing in my database > (workflow_invocation_output_dataset_collection_association and > workflow_invocation_output_dataset_collection_association). > > And fields are still present (ie in request table : library_id and > folder_id) > > Could you please help me ? > > Thank you. > > Fred > >>grep "^OperationalError:" FirstRun.log | cut -f 2- -d "," > > "Cannot drop index 'ix_request_library_id': needed in a foreign key > constraint") [SQL: u'\nALTER TABLE request DROP COLUMN library_id'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "Cannot drop index 'ix_request_folder_id': needed in a foreign key > constraint") [SQL: u'\nALTER TABLE request DROP COLUMN folder_id'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'annotation' used in key specification without a key > length") [SQL: u'CREATE INDEX > ix_page_annotation_association_annotation > ON page_annotation_association (annotation)'] (Background on this > error > at: http://sqlalche.me/e/e3q8) > > "Cannot drop index 'sequencer_id': needed in a foreign key > constraint") > [SQL: u'\nALTER TABLE request_type DROP COLUMN sequencer_id'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'guid' used in key specification without a key > length") [SQL: u'CREATE UNIQUE INDEX ix_tool_id_guid_map_guid ON > tool_id_guid_map (guid)'] (Background on this error at: > http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'layout' used in key specification without a key > length") [SQL: u'CREATE INDEX ix_form_definition_layout ON > form_definition (layout)'] (Background on this error at: > http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'notification' used in key specification without a > key > length") [SQL: u'CREATE INDEX ix_request_notification ON request > (notification)'] (Background on this error at: > http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'data_manager_id' used in key specification without > a key length") [SQL: u'CREATE INDEX > ix_data_manager_job_association_data_manager_id ON > data_manager_job_association (data_manager_id)'] (Background on this > error at: http://sqlalche.me/e/e3q8) > > "Identifier name > 'workflow_invocation_output_dataset_collection_association_ibfk_1' is > too long") [SQL: u'\nCREATE TABLE > workflow_invocation_output_dataset_collection_association (\n\tid > INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_id INTEGER, > \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, > \n\tworkflow_output_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN > KEY(workflow_invocation_id) REFERENCES workflow_invocation (id), > \n\tFOREIGN KEY(workflow_step_id) REFERENCES workflow_step (id), > \n\tFOREIGN KEY(dat
Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05
Frederic, you may want to follow this issue to track a resolution here: https://github.com/galaxyproject/galaxy/issues/6401 On Thu, Jun 14, 2018 at 1:24 AM SAPET, Frederic wrote: > Hi Hans, > > We have a very nice MySQL server with a lot of RAM and associated storage > (as well as Oracle for some other applications) > So we would like to avoid a third system to maintain. > And mysql is working well for my current 17.09 galaxy instance. > > Fred > > -Message d'origine- > De : Hans-Rudolf Hotz [mailto:h...@fmi.ch] > Envoyé : jeudi 14 juin 2018 08:25 > À : SAPET, Frederic ; Galaxy-dev < > galaxy-dev@lists.galaxyproject.org> > Objet : Re: [galaxy-dev] MySQL errors and warnings with a fresh install of > Galaxy 18-05 > > Hi Fred > > Sorry, I don't have an answer I am just wondering: If you start from > 'scratch', why don't you use PostgreSQL? > > Regards, Hans-Rudolf > > > > On 06/13/2018 05:41 PM, SAPET, Frederic wrote: > > Hi, > > > > I try to install galaxy 18-05. My database backend is MysqL (5.7.11-4 > > Percona Server) > > > > I start from scratch (drop database, new directory with galaxy files > > fetched with git) > > > > So the database is first created when run.sh is used : : > > > > Creating database for URI [mysql://…. > > > > I see warnings about duplicated indexes and OperationalError from > > SQLAlchemy. > > > > Some tables are then missing in my database > > (workflow_invocation_output_dataset_collection_association and > > workflow_invocation_output_dataset_collection_association). > > > > And fields are still present (ie in request table : library_id and > > folder_id) > > > > Could you please help me ? > > > > Thank you. > > > > Fred > > > >>grep "^OperationalError:" FirstRun.log | cut -f 2- -d "," > > > > "Cannot drop index 'ix_request_library_id': needed in a foreign key > > constraint") [SQL: u'\nALTER TABLE request DROP COLUMN library_id'] > > (Background on this error at: http://sqlalche.me/e/e3q8) > > > > "Cannot drop index 'ix_request_folder_id': needed in a foreign key > > constraint") [SQL: u'\nALTER TABLE request DROP COLUMN folder_id'] > > (Background on this error at: http://sqlalche.me/e/e3q8) > > > > "BLOB/TEXT column 'annotation' used in key specification without a key > > length") [SQL: u'CREATE INDEX > > ix_page_annotation_association_annotation > > ON page_annotation_association (annotation)'] (Background on this > > error > > at: http://sqlalche.me/e/e3q8) > > > > "Cannot drop index 'sequencer_id': needed in a foreign key > > constraint") > > [SQL: u'\nALTER TABLE request_type DROP COLUMN sequencer_id'] > > (Background on this error at: http://sqlalche.me/e/e3q8) > > > > "BLOB/TEXT column 'guid' used in key specification without a key > > length") [SQL: u'CREATE UNIQUE INDEX ix_tool_id_guid_map_guid ON > > tool_id_guid_map (guid)'] (Background on this error at: > > http://sqlalche.me/e/e3q8) > > > > "BLOB/TEXT column 'layout' used in key specification without a key > > length") [SQL: u'CREATE INDEX ix_form_definition_layout ON > > form_definition (layout)'] (Background on this error at: > > http://sqlalche.me/e/e3q8) > > > > "BLOB/TEXT column 'notification' used in key specification without a > > key > > length") [SQL: u'CREATE INDEX ix_request_notification ON request > > (notification)'] (Background on this error at: > > http://sqlalche.me/e/e3q8) > > > > "BLOB/TEXT column 'data_manager_id' used in key specification without > > a key length") [SQL: u'CREATE INDEX > > ix_data_manager_job_association_data_manager_id ON > > data_manager_job_association (data_manager_id)'] (Background on this > > error at: http://sqlalche.me/e/e3q8) > > > > "Identifier name > > 'workflow_invocation_output_dataset_collection_association_ibfk_1' is > > too long") [SQL: u'\nCREATE TABLE > > workflow_invocation_output_dataset_collection_association (\n\tid > > INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_id INTEGER, > > \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, > > \n\tworkflow_output_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN > > KEY(workflow_invocation_id) REFERENCES workflow_in
Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05
Hi Hans, We have a very nice MySQL server with a lot of RAM and associated storage (as well as Oracle for some other applications) So we would like to avoid a third system to maintain. And mysql is working well for my current 17.09 galaxy instance. Fred -Message d'origine- De : Hans-Rudolf Hotz [mailto:h...@fmi.ch] Envoyé : jeudi 14 juin 2018 08:25 À : SAPET, Frederic ; Galaxy-dev Objet : Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05 Hi Fred Sorry, I don't have an answer I am just wondering: If you start from 'scratch', why don't you use PostgreSQL? Regards, Hans-Rudolf On 06/13/2018 05:41 PM, SAPET, Frederic wrote: > Hi, > > I try to install galaxy 18-05. My database backend is MysqL (5.7.11-4 > Percona Server) > > I start from scratch (drop database, new directory with galaxy files > fetched with git) > > So the database is first created when run.sh is used : : > > Creating database for URI [mysql://…. > > I see warnings about duplicated indexes and OperationalError from > SQLAlchemy. > > Some tables are then missing in my database > (workflow_invocation_output_dataset_collection_association and > workflow_invocation_output_dataset_collection_association). > > And fields are still present (ie in request table : library_id and > folder_id) > > Could you please help me ? > > Thank you. > > Fred > >>grep "^OperationalError:" FirstRun.log | cut -f 2- -d "," > > "Cannot drop index 'ix_request_library_id': needed in a foreign key > constraint") [SQL: u'\nALTER TABLE request DROP COLUMN library_id'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "Cannot drop index 'ix_request_folder_id': needed in a foreign key > constraint") [SQL: u'\nALTER TABLE request DROP COLUMN folder_id'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'annotation' used in key specification without a key > length") [SQL: u'CREATE INDEX > ix_page_annotation_association_annotation > ON page_annotation_association (annotation)'] (Background on this > error > at: http://sqlalche.me/e/e3q8) > > "Cannot drop index 'sequencer_id': needed in a foreign key > constraint") > [SQL: u'\nALTER TABLE request_type DROP COLUMN sequencer_id'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'guid' used in key specification without a key > length") [SQL: u'CREATE UNIQUE INDEX ix_tool_id_guid_map_guid ON > tool_id_guid_map (guid)'] (Background on this error at: > http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'layout' used in key specification without a key > length") [SQL: u'CREATE INDEX ix_form_definition_layout ON > form_definition (layout)'] (Background on this error at: > http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'notification' used in key specification without a > key > length") [SQL: u'CREATE INDEX ix_request_notification ON request > (notification)'] (Background on this error at: > http://sqlalche.me/e/e3q8) > > "BLOB/TEXT column 'data_manager_id' used in key specification without > a key length") [SQL: u'CREATE INDEX > ix_data_manager_job_association_data_manager_id ON > data_manager_job_association (data_manager_id)'] (Background on this > error at: http://sqlalche.me/e/e3q8) > > "Identifier name > 'workflow_invocation_output_dataset_collection_association_ibfk_1' is > too long") [SQL: u'\nCREATE TABLE > workflow_invocation_output_dataset_collection_association (\n\tid > INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_id INTEGER, > \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, > \n\tworkflow_output_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN > KEY(workflow_invocation_id) REFERENCES workflow_invocation (id), > \n\tFOREIGN KEY(workflow_step_id) REFERENCES workflow_step (id), > \n\tFOREIGN KEY(dataset_collection_id) REFERENCES > history_dataset_collection_association (id), \n\tFOREIGN > KEY(workflow_output_id) REFERENCES workflow_output (id)\n)\n\n'] > (Background on this error at: http://sqlalche.me/e/e3q8) > > "Identifier name > 'workflow_invocation_step_output_dataset_collection_association_ibfk_1' > is too long") [SQL: u'\nCREATE TABLE > workflow_invocation_step_output_dataset_collection_association (\n\tid > INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_step_id > INTEGER, \n\tworkflow_step_id INTEGER, \n\tdataset
Re: [galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05
Hi Fred Sorry, I don't have an answer I am just wondering: If you start from 'scratch', why don't you use PostgreSQL? Regards, Hans-Rudolf On 06/13/2018 05:41 PM, SAPET, Frederic wrote: Hi, I try to install galaxy 18-05. My database backend is MysqL (5.7.11-4 Percona Server) I start from scratch (drop database, new directory with galaxy files fetched with git) So the database is first created when run.sh is used : : Creating database for URI [mysql://…. I see warnings about duplicated indexes and OperationalError from SQLAlchemy. Some tables are then missing in my database (workflow_invocation_output_dataset_collection_association and workflow_invocation_output_dataset_collection_association). And fields are still present (ie in request table : library_id and folder_id) Could you please help me ? Thank you. Fred grep "^OperationalError:" FirstRun.log | cut -f 2- -d "," "Cannot drop index 'ix_request_library_id': needed in a foreign key constraint") [SQL: u'\nALTER TABLE request DROP COLUMN library_id'] (Background on this error at: http://sqlalche.me/e/e3q8) "Cannot drop index 'ix_request_folder_id': needed in a foreign key constraint") [SQL: u'\nALTER TABLE request DROP COLUMN folder_id'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'annotation' used in key specification without a key length") [SQL: u'CREATE INDEX ix_page_annotation_association_annotation ON page_annotation_association (annotation)'] (Background on this error at: http://sqlalche.me/e/e3q8) "Cannot drop index 'sequencer_id': needed in a foreign key constraint") [SQL: u'\nALTER TABLE request_type DROP COLUMN sequencer_id'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'guid' used in key specification without a key length") [SQL: u'CREATE UNIQUE INDEX ix_tool_id_guid_map_guid ON tool_id_guid_map (guid)'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'layout' used in key specification without a key length") [SQL: u'CREATE INDEX ix_form_definition_layout ON form_definition (layout)'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'notification' used in key specification without a key length") [SQL: u'CREATE INDEX ix_request_notification ON request (notification)'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'data_manager_id' used in key specification without a key length") [SQL: u'CREATE INDEX ix_data_manager_job_association_data_manager_id ON data_manager_job_association (data_manager_id)'] (Background on this error at: http://sqlalche.me/e/e3q8) "Identifier name 'workflow_invocation_output_dataset_collection_association_ibfk_1' is too long") [SQL: u'\nCREATE TABLE workflow_invocation_output_dataset_collection_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_id INTEGER, \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, \n\tworkflow_output_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(workflow_invocation_id) REFERENCES workflow_invocation (id), \n\tFOREIGN KEY(workflow_step_id) REFERENCES workflow_step (id), \n\tFOREIGN KEY(dataset_collection_id) REFERENCES history_dataset_collection_association (id), \n\tFOREIGN KEY(workflow_output_id) REFERENCES workflow_output (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8) "Identifier name 'workflow_invocation_step_output_dataset_collection_association_ibfk_1' is too long") [SQL: u'\nCREATE TABLE workflow_invocation_step_output_dataset_collection_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_step_id INTEGER, \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, \n\toutput_name VARCHAR(255), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(workflow_invocation_step_id) REFERENCES workflow_invocation_step (id), \n\tFOREIGN KEY(workflow_step_id) REFERENCES workflow_step (id), \n\tFOREIGN KEY(dataset_collection_id) REFERENCES history_dataset_collection_association (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8) grep "Warning" FirstRun.log | cut -f 3,4 -d ":" Warning: Duplicate index 'ix_galaxy_user_deleted' defined on the table 'galaxydbBGAdev.galaxy_user'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_galaxy_user_purged' defined on the table 'galaxydbBGAdev.galaxy_user'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_hdadaa_history_dataset_association_id' defined on the table 'galaxydbBGAdev.history_dataset_association_display_at_authorization'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_lddaia_ldda_id' defined on the table 'galaxydbBGAdev.library_dataset_dataset_info_association'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_hda_ta_history_dataset_association_id' defined on the table 'galaxydbBGAdev.history_datas
[galaxy-dev] MySQL errors and warnings with a fresh install of Galaxy 18-05
Hi, I try to install galaxy 18-05. My database backend is MysqL (5.7.11-4 Percona Server) I start from scratch (drop database, new directory with galaxy files fetched with git) So the database is first created when run.sh is used : : Creating database for URI [mysql:// I see warnings about duplicated indexes and OperationalError from SQLAlchemy. Some tables are then missing in my database (workflow_invocation_output_dataset_collection_association and workflow_invocation_output_dataset_collection_association). And fields are still present (ie in request table : library_id and folder_id) Could you please help me ? Thank you. Fred >grep "^OperationalError:" FirstRun.log | cut -f 2- -d "," "Cannot drop index 'ix_request_library_id': needed in a foreign key constraint") [SQL: u'\nALTER TABLE request DROP COLUMN library_id'] (Background on this error at: http://sqlalche.me/e/e3q8) "Cannot drop index 'ix_request_folder_id': needed in a foreign key constraint") [SQL: u'\nALTER TABLE request DROP COLUMN folder_id'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'annotation' used in key specification without a key length") [SQL: u'CREATE INDEX ix_page_annotation_association_annotation ON page_annotation_association (annotation)'] (Background on this error at: http://sqlalche.me/e/e3q8) "Cannot drop index 'sequencer_id': needed in a foreign key constraint") [SQL: u'\nALTER TABLE request_type DROP COLUMN sequencer_id'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'guid' used in key specification without a key length") [SQL: u'CREATE UNIQUE INDEX ix_tool_id_guid_map_guid ON tool_id_guid_map (guid)'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'layout' used in key specification without a key length") [SQL: u'CREATE INDEX ix_form_definition_layout ON form_definition (layout)'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'notification' used in key specification without a key length") [SQL: u'CREATE INDEX ix_request_notification ON request (notification)'] (Background on this error at: http://sqlalche.me/e/e3q8) "BLOB/TEXT column 'data_manager_id' used in key specification without a key length") [SQL: u'CREATE INDEX ix_data_manager_job_association_data_manager_id ON data_manager_job_association (data_manager_id)'] (Background on this error at: http://sqlalche.me/e/e3q8) "Identifier name 'workflow_invocation_output_dataset_collection_association_ibfk_1' is too long") [SQL: u'\nCREATE TABLE workflow_invocation_output_dataset_collection_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_id INTEGER, \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, \n\tworkflow_output_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(workflow_invocation_id) REFERENCES workflow_invocation (id), \n\tFOREIGN KEY(workflow_step_id) REFERENCES workflow_step (id), \n\tFOREIGN KEY(dataset_collection_id) REFERENCES history_dataset_collection_association (id), \n\tFOREIGN KEY(workflow_output_id) REFERENCES workflow_output (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8) "Identifier name 'workflow_invocation_step_output_dataset_collection_association_ibfk_1' is too long") [SQL: u'\nCREATE TABLE workflow_invocation_step_output_dataset_collection_association (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tworkflow_invocation_step_id INTEGER, \n\tworkflow_step_id INTEGER, \n\tdataset_collection_id INTEGER, \n\toutput_name VARCHAR(255), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(workflow_invocation_step_id) REFERENCES workflow_invocation_step (id), \n\tFOREIGN KEY(workflow_step_id) REFERENCES workflow_step (id), \n\tFOREIGN KEY(dataset_collection_id) REFERENCES history_dataset_collection_association (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8) >grep "Warning" FirstRun.log | cut -f 3,4 -d ":" Warning: Duplicate index 'ix_galaxy_user_deleted' defined on the table 'galaxydbBGAdev.galaxy_user'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_galaxy_user_purged' defined on the table 'galaxydbBGAdev.galaxy_user'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_hdadaa_history_dataset_association_id' defined on the table 'galaxydbBGAdev.history_dataset_association_display_at_authorization'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_lddaia_ldda_id' defined on the table 'galaxydbBGAdev.library_dataset_dataset_info_association'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_hda_ta_history_dataset_association_id' defined on the table 'galaxydbBGAdev.history_dataset_association_tag_association'. This is deprecated and will be disallowed in a future release. Warning: Duplicate index 'ix_galaxy_user_username' defined on the