Bug #94050 reordering index elements does not change generated sql index statement order
Submitted: 24 Jan 2019 22:32 Modified: 6 Feb 2019 1:47
Reporter: Nick Radonic Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:8.0.13 OS:MacOS (macOS 10.13.x High Sierra x86_64)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[24 Jan 2019 22:32] Nick Radonic
----[For better reports, please attach the log file after submitting. You can find it in /Users/nickradonic/Library/Application Support/MySQL/Workbench/log/wb.log]

I created compound primary keys. The referring table referenced them in the incorrect order. This made the index statement sort the fields incorrectly which broke the sql creation script -- it could not apply the foreign key constraints. Editing the 1,2 order of the  foreign key references did NOT change the INDEX sql statement sort order.

How to repeat:
create two tables. in the first one create a two element string primary key. In the second table refer to the key items, but in the wrong order. See the sql statement for the referring table -- the index order is backwards. Go to the table - foreign key view, and change the element order. examine the sql creation statement again and see that the sql index sorting order is not revised..


CREATE TABLE IF NOT EXISTS `human_encounter` (
  `hash_uuid` VARCHAR(80) NOT NULL,
  `version` VARCHAR(11) NOT NULL,
  `person_id` INT NULL,
  `human_person_hashuuid` VARCHAR(80) NOT NULL,
  `human_person_version` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`hash_uuid`, `version`),
  INDEX `human_person_fk_c_idx` (`human_person_hashuuid` ASC, `human_person_version` ASC),
  CONSTRAINT `human_person_fk_c`
    FOREIGN KEY (`human_person_hashuuid` , `human_person_version`)
    REFERENCES `human_person` (`hash_uuid` , `version`)

The :  FOREIGN KEY (`human_person_hashuuid` , `human_person_version`) line was reversed originally FOREIGN KEY ( `human_person_version`,`human_person_hashuuid`) -- until I deleted and re-added the hash and uuid columns back into the table; no amount of changing the ordering in workbench foreign key view changed the sql. 

Suggested fix:
The index components for compound keys need to track the ordering selected in the foreign key view, and update as the ordering is changed. I suspect you have a stuck internal data structure - which tracks the elements, but doesn't update them with the foreign key view.
[6 Feb 2019 0:11] MySQL Verification Team
Thank you for the bug report. Please provide the create statement of the 2 tables. Thanks.
[6 Feb 2019 1:47] Nick Radonic
I no longer have the old models for reference. I cannot reproduce the problem.