Bug #69181 Foreign Keys being dropped and re-added on every model sync
Submitted: 9 May 2013 11:07 Modified: 9 May 2013 15:45
Reporter: Jan de Vries Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.47 OS:Any
Assigned to: CPU Architecture:Any

[9 May 2013 11:07] Jan de Vries
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` 
  ADD CONSTRAINT `fk_Item_NewsItem1`
  FOREIGN KEY (`newsItem_id` )
  REFERENCES `NewsItem` (`id` )
  ADD CONSTRAINT `fk_Item_NewsCategory1`
  FOREIGN KEY (`newsCategory_id` )
  REFERENCES `NewsCategory` (`id` )

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.
[9 May 2013 15:45] MySQL Verification Team
Thank you for the bug report. Duplicate of http://bugs.mysql.com/bug.php?id=69153 .
[31 May 2016 9:58] Peter de Vos
Still no solution for this BUG?
[17 Mar 9:28] Alex Stoica
The problem is still persisting in 2021 :)