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 <frederic.sapet@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:hrh@fmi.ch]
Envoyé : jeudi 14 juin 2018 08:25
À : SAPET, Frederic <frederic.sapet@biogemma.com>; 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_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
> table 'galaxydbBGAdev.galaxy_user'. This is deprecated and will be
> disallowed in a future release.
>
> Warning: Duplicate index 'ix_galaxy_user_form_values_id' defined on
> the table 'galaxydbBGAdev.galaxy_user'. This is deprecated and will be
> disallowed in a future release.
>
> Warning: Duplicate index 'ix_lia_deleted' defined on the table
> 'galaxydbBGAdev.library_info_association'. This is deprecated and will
> be disallowed in a future release.
>
> Warning: Duplicate index 'ix_lfia_deleted' defined on the table
> 'galaxydbBGAdev.library_folder_info_association'. This is deprecated
> and will be disallowed in a future release.
>
> Warning: Duplicate index 'ix_lddia_deleted' 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_sample_folder_id' defined on the table
> 'galaxydbBGAdev.sample'. This is deprecated and will be disallowed in
> a future release.
>
> Warning: Duplicate index 'ix_lia_inheritable' defined on the table
> 'galaxydbBGAdev.library_info_association'. This is deprecated and will
> be disallowed in a future release.
>
> Warning: Duplicate index 'ix_lfia_inheritable' defined on the table
> 'galaxydbBGAdev.library_folder_info_association'. This is deprecated
> and will be disallowed in a future release.
>
> Warning: Duplicate index 'ix_sd_external_service_id' defined on the
> table 'galaxydbBGAdev.sample_dataset'. This is deprecated and will be
> disallowed in a future release.
>
> Warning: Duplicate index 'ix_icda_ldda_parent_id' defined on the table
> 'galaxydbBGAdev.implicitly_converted_dataset_association'. This is
> deprecated and will be disallowed in a future release.
>
> Warning: Duplicate index 'ix_icda_ldda_id' defined on the table
> 'galaxydbBGAdev.implicitly_converted_dataset_association'. This is
> deprecated and will be disallowed in a future release.
>
> Warning: Duplicate index 'ix_tsr_includes_datatypes' defined on the
> table 'galaxydbBGAdev.tool_shed_repository'. This is deprecated and
> will be disallowed in a future release.
>
>>grep ERROR FirstRun.log | grep failed
>
> 0037_samples_library ERROR 2018-06-13 12:01:45,817 [p:17852,w:0,m:0]
> [MainThread] Deleting column 'library_id' to request table failed.
>
> 0037_samples_library ERROR 2018-06-13 12:01:45,832 [p:17852,w:0,m:0]
> [MainThread] Deleting column 'folder_id' to request table failed.
>
> galaxy.model.migrate.check INFO 2018-06-13 12:01:47,129
> [p:17852,w:0,m:0] [MainThread] 0037_samples_library ERROR 2018-06-13
> 12:01:45,817 Deleting column 'library_id' to request table failed.
>
> galaxy.model.migrate.check INFO 2018-06-13 12:01:47,133
> [p:17852,w:0,m:0] [MainThread] 0037_samples_library ERROR 2018-06-13
> 12:01:45,817 Deleting column 'library_id' to request table failed.
>
> galaxy.model.migrate.check INFO 2018-06-13 12:01:47,137
> [p:17852,w:0,m:0] [MainThread] 0037_samples_library ERROR 2018-06-13
> 12:01:45,832 Deleting column 'folder_id' to request table failed.
>
> galaxy.model.migrate.check INFO 2018-06-13 12:01:47,142
> [p:17852,w:0,m:0] [MainThread] 0037_samples_library ERROR 2018-06-13
> 12:01:45,832 Deleting column 'folder_id' to request table failed.
>
> 0040_page_annotations ERROR 2018-06-13 12:01:49,598 [p:17852,w:0,m:0]
> [MainThread] Creating page_annotation_association table failed.
>
> 0068_rename_sequencer_to_external_services ERROR 2018-06-13
> 12:02:26,658 [p:17852,w:0,m:0] [MainThread] Deleting column
> 'sequencer_id' from the 'request_type' table failed.
>
> 0087_tool_id_guid_map_table ERROR 2018-06-13 12:02:48,300
> [p:17852,w:0,m:0] [MainThread] Creating tool_id_guid_map table failed.
>
> galaxy.model.migrate.check INFO 2018-06-13 12:02:48,309
> [p:17852,w:0,m:0] [MainThread] 0087_tool_id_guid_map_table ERROR
> 2018-06-13 12:02:48,300 Creating tool_id_guid_map table failed.
>
> galaxy.model.migrate.check INFO 2018-06-13 12:02:48,313
> [p:17852,w:0,m:0] [MainThread] 0087_tool_id_guid_map_table ERROR
> 2018-06-13 12:02:48,300 Creating tool_id_guid_map table failed.
>
> 0112_add_data_manager_history_association_and_data_manager_job_associa
> tion_tables ERROR 2018-06-13 12:03:22,252 [p:17852,w:0,m:0]
> [MainThread] Creating data_manager_job_association table failed.
>
> 0136_collection_and_workflow_state ERROR 2018-06-13 12:03:57,766
> [p:17852,w:0,m:0] [MainThread] Creating
> workflow_invocation_output_dataset_collection_association table failed.
>
> 0136_collection_and_workflow_state ERROR 2018-06-13 12:03:58,326
> [p:17852,w:0,m:0] [MainThread] Creating
> workflow_invocation_step_output_dataset_collection_association table failed.
>
>
>
> ___________________________________________________________
> 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:
>    https://lists.galaxyproject.org/
>
> To search Galaxy mailing lists use the unified search at:
>    http://galaxyproject.org/search/
>
___________________________________________________________
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:
  https://lists.galaxyproject.org/

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/