MySQL Bugs: #58238: index constantly recreated during sync

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:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.29	OS:	MacOS (10.5.8)
Assigned to:	Alexander Musienko	CPU Architecture:	Any

Description:
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.

Thank you for the bug report.

Bug #58488 has been marked as duplicate of this one

fix confirmed in repository

An entry has been added to the 5.2.32 changelog: 

When synchronizing a model with a server, indexes were unnecessarily dropped 
and recreated.

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.

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.

i finally decided to stop using mysql beacuase of those synchronization bugs

i finally decided to stop using mysql beacuase of those synchronization bugs