Description:
I'm suffering from a problem where MySQL Workbench attempts to drop and immediately re-add a set of foreign keys every time I do a sync from model -> database. I eventually traced the reason Workbench is attempting all these changes in the first place: for some reason the order of the indexes (not the order of columns within indexes, but the indexes themselves) on the server side became different vs. the order listed in workbench.
Essentially this bug can be split up in 3 sub-problems:
#1: Does the order of indexes matter at all? Could a difference not be safely ignored alltogether? See also #60230
#2: Workbench attempts to change the index order on the server side by removing the accompanying foreign keys and readding them in the "proper" order. However, it's only removing the foreign keys and not the accompanying indexes, causing the generated SQL to have no effect.
For example, for one of my tables the current index order on the server side is:
* fk_Item_NewsItem1_idx
* fk_Item_User1_idx
* fk_Item_NewsCategory1_idx
The order listed under "indexes" in Workbench is:
* fk_Item_User1_idx
* fk_Item_NewsItem1_idx
* fk_Item_NewsCategory1_idx
The SQL Workbench generates to synchronize this is:
ALTER TABLE `Item` DROP FOREIGN KEY `fk_Item_NewsItem1` , DROP FOREIGN KEY `fk_Item_NewsCategory1`
ALTER TABLE `Item`
ADD CONSTRAINT `fk_Item_NewsItem1`
FOREIGN KEY (`newsItem_id` )
REFERENCES `NewsItem` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_Item_NewsCategory1`
FOREIGN KEY (`newsCategory_id` )
REFERENCES `NewsCategory` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT
I imagine its reasoning is that the indexes will automatically appear in the proper/expected order if the foreign keys+indexes are removed and readded in this sequence. However, the generated SQL only removes the foreign keys and leaves the accompanying indexes intact, causing nothing to change.
#3: After letting Workbench execute the SQL statement above, at first the conflict appears to be resolved. If I re-run "Synchronize model" afterwards, Workbench detects no differences between the model & the database. However, if I restart Workbench and then run a new "Synchronize model", it once again detects the same changes, and wants to execute the same SQL (as above) all over again. This makes it really hard to trace issues like this down to the root cause.
How to repeat:
Create a table in both Workbench and a MySQL Server instance, which are completely identical except for the order in which the different indexes are created. Then run a "Synchronize Model" from within Workbench.
Suggested fix:
#1: Ignore differences in index order alltogether
#2: When trying to fix the index order, remove and re-add both the foreign key and the index
#3: Make sure Workbench does a "fresh scan"/from scratch if "Synchronize model" is ran multiple times in the same session.
