MySQL Bugs: #45830: Forward Engineer SQL ALTER Script... recreates unmodified unique indexes

Bug #45830	Forward Engineer SQL ALTER Script... recreates unmodified unique indexes
Submitted:	29 Jun 2009 16:30	Modified:	7 Aug 2009 14:49
Reporter:	Stas Trefilov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.15 RC	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	CHECKED

Description:
forward engineering the alter script will generate DROP INDEX/ADD UNIQUE INDEX statements on any unique index (even unmodified). regular index, fulltext and spatial index types do not have this behaviour (there are no DROP/CREATE statements for these).

How to repeat:
1/ create new table with a single column on which set the unique index

2/ run Export / Forward Engineer SQL CREATE Script... (by the way, if you are overwriting the existing file the second dialog (i don't know already why we have two dialogs here) with Replace / Cancel buttons has a very funny behavior -- the buttons are actually reversed, so that if you press Replace you cannot go to the next page. it only advances when you give up and press Cancel. by i can live with this one :). so, the generated create script will contain:

[skip]
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `name` VARCHAR(45) NOT NULL ,
  UNIQUE INDEX `name` (`name` ASC) )
ENGINE = MyISAM;
[skip]

3/ run Export / Forward Engineer SQL ALTER Script... using the created file as an input. the output file will have this:

[skip]
ALTER TABLE `mydb`.`table1` 
DROP INDEX `name` 
, ADD UNIQUE INDEX `name` (`name` ASC) ;
[skip]

Suggested fix:
if you are using OSS version of WB and trying to synchronize your schema using Forward Engineer SQL ALTER Script feature then you have to manually review the generated alter statements and remove the lines corresponding to the recreation of unique indexes.

Thank you for the bug report. Verified just as described also on Windows XP.

this has been corrected. fix will be included in 5.1.17

An entry was added to the 5.1.17 changelog:

The Forward Engineering SQL ALTER Script wizard generated DROP INDEX and ADD UNIQUE INDEX statements for any unique index, even for one that had not been modified. These unnecessary statements had to be manually deleted from the script.