MySQL Bugs: #54740: wrong ADD / DROP INDEX pair on db sync

Bug #54740	wrong ADD / DROP INDEX pair on db sync
Submitted:	23 Jun 2010 14:23	Modified:	29 Jun 2010 12:38
Reporter:	Stas Trefilov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.24 RC	OS:	Any (Mac 10.5.8, Ubuntu)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	schema sync drop add index

Description:
when using Database / Synchronize Model function i found out that the index created manually in the database after the previous sync and also added manually to a model was trying to sync but in a very special way:

ALTER TABLE `...........`.`applications` DROP COLUMN `an_ct_id_manager`
, ADD COLUMN `an_ct_id_manager` INT(10) UNSIGNED NULL DEFAULT NULL  AFTER `an_ct_id` 
, ADD INDEX `an_ct_cons_FK` (`an_ct_id_consultant` ASC) 
, ADD INDEX `an_ct_mgr_FK` (`an_ct_id_manager` ASC) 
, DROP INDEX `an_ct_cons_FK` 
, DROP INDEX `an_ct_mgr_FK` ;

the sequence tryes to add an index and then drop it. this is only reported for the newly created columns / indexes (existing indexes do not appear in sync log, which is normal).

another issue is with the column an_ct_id_manager which is currently defined as int(10) unsigned null and i see no need to delete it to recreate it in-place. actually this means just cleaning out the column.

How to repeat:
synchronize your model.

create a new field and add an index in a model.

create the same field / index in the db.

try to synchronize model.

Suggested fix:
clean synchronize statements before executing

synchronize window with ADD / DROP INDEX statement

Attachment: db_sync.png (image/png, text), 123.88 KiB.

Many thanks for writing a bug report.

I was able to reproduce this.

STEP 1:
Use CLI

CREATE DATABASE bug54740;
USE bug54740;
CREATE TABLE t(i SERIAL, j INTEGER, v VARCHAR(100))ENGINE=innodb;

STEP 2:
Open Workbench
Reverse Engineer Database
Double click table t
Add unsigned integer column "k"
Add index i_k on column k

STEP 3:
Use CLI

USE bug54740;
ALTER TABLE t ADD COLUMN k INTEGER UNSIGNED;
CREATE INDEX i_k ON t(k);

Step 4:
Use Workbench
Sync your model
Ignore mydb and press next

You will get the following result:

ALTER TABLE `miracee`.`t` 
DROP INDEX `i` 
, ADD UNIQUE INDEX `i` (`k` ASC) 
, DROP INDEX `i_k` 
, ADD INDEX `i_k` () ;

fix confirmed in repository

An entry has been added to the 5.2.25 changelog:

When synchronizing a model with the live database, the generated SQL included erroneous DROP and ADD column statements, and also erroneous ADD and DROP index statements.