MySQL Bugs: #60230: Synchronize model *always* want to alter foreign keys and index

Bug #60230	Synchronize model always want to alter foreign keys and index
Submitted:	24 Feb 2011 10:19	Modified:	30 May 2012 20:01
Reporter:	Henrik Gemal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.31 7115	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alter, sync

Description:
whenever I do a Synchronize Model it always wants me to drop all indexs and foreign keys and create them again.
even though that the database was just created before using workbench.

I'm not sure why it does this. I might suspect some ordering of the indexes.

I think workbench should ignore the ordering.

How to repeat:
create a database using workbench and then try to syncrhonize the model

Please, upload the smallest .mwb file that demonstrate the problem. What exact version of server, 5.x.y, are you working with?

In the attached testcase you will see 2 problems:

1) WorkBench keeps saying it will ALTER the table and add INDEX

2) WorkBench keeps saying that we need to change the varchar(100)

If you create the database using forward engineer and the just after do a sync model you ALWAYS get:

ALTER TABLE `wb_test`.`restaurants` 
DROP INDEX `fk_restaurants_restaurants` 
, ADD INDEX `fk_restaurants_restaurants` (`parent_id` ASC) 
, DROP INDEX `res_type` ;

ALTER TABLE `wb_test`.`sessions` CHANGE COLUMN `id` `id` VARCHAR(100) NOT NULL  ;

MySQL Server 5.5.9

Verified just as described in the last comment, using the .mwb file uploaded.

the same problem is happening with me, I'm using version 5.2.34
Is there any solution?

I'm having this same issue using:

Workbench 5.2.34
MySQL 5.5.16
Mac OS X Lion 10.7.1

I experienced the same issue prior to updates, using previous versions of Workbench and MySQL on Mac OS X 10.6.8.

I'm having similar issues. Look at the ALTER script generated:

ALTER TABLE `db_c87d5f29`.`providers` DROP COLUMN `userId` , ADD COLUMN `userId` INT(10) UNSIGNED NOT NULL  AFTER `providerId` , DROP FOREIGN KEY `fk_providers_users1` 

ALTER TABLE `db_c87d5f29`.`providers` 
  ADD CONSTRAINT `fk_providers_users1`
  FOREIGN KEY (`userId` )
  REFERENCES `db_c87d5f29`.`users` (`userId` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, DROP INDEX `fk_providers_users1` 
, ADD INDEX `fk_providers_users1` (`userId` ASC) 

It is trying to delete and recreate identical columns and indexes!

Hello,

I have the same problem on my mwb.
I don't understand the problem...
I use T.2.37 CE.

Have you some news of this bug ?

Thanks for help.

Hey guys I figured out what the problem is on this one.

The problem is in the ORDER of the UNIQUE indexes.  When you add them through the GUI it will add them in whatever order you put them in and keep them in the diagram in that order.  On the SERVER SIDE it orders the UNIQUE INDEX FIRST, and then the others.  So the Modeler thinks the foreignkey/index needs to be recreated because they're mismatched, but really they're just sorted differently.

The way I resolve this is I double/tripple check to make sure everything is correct in both places and sync a few times to make sure.  And then when I sync I tell the modeler to go ahead and update the model instead of the database.  It then flips the order of the index and stops asking me to sync that table.

Hey guys I figured out what the problem is on this one.

The problem is in the ORDER of the UNIQUE indexes.  When you add them through the GUI it will add them in whatever order you put them in and keep them in the diagram in that order.  On the SERVER SIDE it orders the UNIQUE INDEX FIRST, and then the others.  So the Modeler thinks the foreignkey/index needs to be recreated because they're mismatched, but really they're just sorted differently.

The way I resolve this is I double/tripple check to make sure everything is correct in both places and sync a few times to make sure.  And then when I sync I tell the modeler to go ahead and update the model instead of the database.  It then flips the order of the index and stops asking me to sync that table.

Great ! 
Thanks Ryan
It works fine for me !

this one-year old issue reported as fixed may help:

http://bugs.mysql.com/bug.php?id=58238

Fixed as of the soon-to-be-released 5.2.41 workbench, and here's the changelog entry:

        Synchronizing a model would drop, and then re-add, indexes and
        foreign keys if the order of these indexes and foreign keys were
        different. This could happen because Workbench would add
        UNIQUE indexes in the order added by the user, whereas the MySQL
        Server puts them first.

I'm having this issue at least with foreign keys with workbench 5.2.44
MySQL 5.5.28

I don't have any case where the order of the indexes is different between a model and a database so I can't tell.

Not all the tables in which this is happening contain unique indexes other than a primary key

I am experiencing the same issue as [25 Feb 2011 13:51] Henrik Gemal, but then when using the Forward engineer ALTER script feature.

I just upgraded to the latest workbench version: 5.2.45 and the problem persists. 

Also note the fact that the script tries to add the index/foreign key first and then delete it again, which would leave you without any indexes at all.

To make 100% sure my schema's were identical, I did a mysqldump on both servers with the following command:

mysqldump -u <username> <database> -d --skip-add-drop-table --triggers --create-options -p > ./dump.sql

I then diff'd both dumps and the only differences that are showing up are related to either usernames in comments; schema's are 100% identical. I am using InnoDB tables.

Ideally, I would like to see a feature where I could simply compare two SQL create scripts; right now when I import an SQL CREATE script which was created with phpMyAdmin (which places the foreign keys at the end of the script using ALTER TABLE statements), the ALTER TABLE statements and thus the foreign keys altogether seem to be ignored.

Still no solution for this BUG?

I have tried updating the model from the database, however in cases where a table has more than one relationship it just crashes MySQL Workbench.

If anyone has sorted this further or has determined it doesn't actually matter that the model keeps updating the database, that would be appreciated.

Still here after 10 years :)

12 years later and the bug still here