Bug #58238 index constantly recreated during sync
Submitted: 16 Nov 2010 17:30 Modified: 7 Mar 2011 17:06
Reporter: Stas Trefilov Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.29 OS:MacOS (10.5.8)
Assigned to: Alexander Musienko CPU Architecture:Any

[16 Nov 2010 17:30] Stas Trefilov
when synchronizing models workbench constantly recreates some indexes. the following statement occurs on each sync:

ALTER TABLE `...`.`missions` 
DROP INDEX `mn_code_U` 
, ADD UNIQUE INDEX `mn_code_U` (`mn_code` ASC) ;

the issue only appears for tables where one or more unique indexes are defined together with non-unique indexes AND unique indexes are lower in the list than the non-unique indexes.

when using sql editor to see table details unique indexes are always defined at the top of the list (right after the PRIMARY) even if in the model they appear lower in the list.

there are no possibility to change the order of indexes in the model.

one of the issues was corrected by deleting the non-unique index so that the unique appears right after the PRIMARY and then recreating the non-unique index so that it comes third. this way the list of indexes in the model corresponds to the same list in sql editor.

How to repeat:
1/ in the model add a unique index to the table already containing non-unique index (the index is at the bottom of the list)

2/ sync with database (new index is created in the database)

3/ check with sql editor that the unique index in the database is listed above the non-unique indexes

4/ sync again but do not execute the statements. statements to drop/add the non-unique index will now appear on each sync.

Suggested fix:
for workbench developers: on sync do not take into account the order of indexes in model/database but only their presence.

for workbench users: reorder the list of indexes in the model so that the unique indexes appear above the non-unique ones.

you will have to delete non-unique indexes and recreate them later.

if your non-unique indexes are backing foreign keys (which workbench designers incomprehensible consider as non-deletable) you will have to delete / recreate your foreign keys.
[16 Nov 2010 18:17] Valeriy Kravchuk
Thank you for the bug report.
[26 Nov 2010 16:30] Johannes Taxacher
Bug #58488 has been marked as duplicate of this one
[3 Mar 2011 13:44] Johannes Taxacher
fix confirmed in repository
[7 Mar 2011 17:06] Tony Bedford
An entry has been added to the 5.2.32 changelog: 

When synchronizing a model with a server, indexes were unnecessarily dropped 
and recreated.
[27 Mar 2011 18:06] Michael Kakuschky
I'm using Workbench 5.2.32b on Windows 7 64bit and on my projects the uneccessary recreation of indexes still occurs. I can't find a rule which produce this behaviuor. It's really annoying if the overview of the synchronisation marks always tables as unsynchronized which are already synched.
[8 Jun 2011 14:33] Kirk Larson
I am still seeing this issue with my model ... I am using Workbench 5.2.34 on Windows 7 64bit.  I tried the index reordering work-around suggested above, and it resolved most of the unnecessary recreation of indexes.  However, a few indexes are still recreated for each synch regardless of the ordering of the indexes.
[4 Aug 2017 5:52] Dong Wook Kim Kim
i finally decided to stop using mysql beacuase of those synchronization bugs
[4 Aug 2017 5:52] Dong Wook Kim Kim
i finally decided to stop using mysql beacuase of those synchronization bugs