MySQL Bugs: #38507: Workbench wants to synchronize the same foreign keys/stored procedures

Bug #38507	Workbench wants to synchronize the same foreign keys/stored procedures
Submitted:	31 Jul 2008 21:19	Modified:	18 May 2009 10:14
Reporter:	Phil Sorber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.23	OS:	Windows
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	CHECKED, synchronize, workbench

Description:
If I reverse engineer a DB and then attempt to synchronize, nothing gets updated as expected. If I make a change to a stored procedure as simple as putting it in a group, that stored procedure will forever want to be updated.

The same happens for a foreign key.

It makes it confusing to figure out if you are only making the correct changes.

Also dropping stored procedures drops the permissions for them by default.

How to repeat:
Create or use an existing DB that has tables with foreign keys and stored procedures. Import it into Workbench with either the Reverse Engineer DB function in SE or the import CREATE script function.

Create and ALTER script or synchronize DB in SE. (Nothing should want to be changed)

Add a new foreign key or put a stored procedure in a group.

Create and ALTER script or synchronize DB in SE. (It should want to change the affected files as expected)

Create and ALTER script or synchronize DB in SE again. (It should want to change the same ones again even though they are the same in the DB as in the model)

17:36 < jtaxacher> Hi PSUdaemon (forgot to greet first time, how unpolite ;) 
                   ... can you explain what you mean by "The same happens for a 
                   foreign key." in the bugreport?
17:37 < jtaxacher> what exactly do you change on the FK to make it always update
17:38 < jtaxacher> (or did i misunterstand that)

You can change anything. Create a new one. Change the field that it references. Change the on update/on delete action.

Someone in IRC also mentioned that the stored procedures do that because it is a zero cost operation. It might be doing the keys as well because it turns off foreign key checks and turns them back on. So nothing really changes, except it might have to re-index?

I encounter the same problem with VIEWS, workbench always prompt to synchronize them again and again, even unchecking the chekcbox for view or procedure in the wizard has no effect

I was told that syncing problems with FK and RESTRICT belongs to this bug as well:

CREATE TABLE `child` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `parents` (`parent_id`),
CONSTRAINT `parents` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

See also attached issue.

Re-Verifying just showed, this bug is fixed in
Workbench 5.1.12 and 5.2.0 which you can download at

  http://www.mysql.com/downloads/