MySQL Bugs: #45868: Detection of differences in synchronization

Bug #45868	Detection of differences in synchronization
Submitted:	1 Jul 2009 5:05	Modified:	5 Feb 2013 18:44
Reporter:	Pavel Dobryakov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
During the synchronization, the program determines the absence of the server stored procedure, although it is present.

How to repeat:
In the server
SHOW FUNCTION STATUS LIKE 'check_zone'
Db 	Name 	Type 	Definer 	Modified 	Created 	Security_type 	Comment 
PkdMon	check_zone	FUNCTION	pavel@%	2009-07-01 08:57:30	2009-07-01 08:57:30	DEFINER	

SHOW CREATE FUNCTION check_zone
check_zone	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER	CREATE DEFINER=`pavel`@`%` FUNCTION `check_zone`(reg bigint) RETURNS tinyint(1)
   READS SQL DATA
begin
declare intcount int;
select count(pkdm_zone.region) into intcount from pkdm_zone where  pkdm_zone.region=reg LIMIT 1;
return intcount;
end

in the program
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DELIMITER //

DROP function IF EXISTS `PkdMon`.`check_zone` //

CREATE FUNCTION `check_zone`(reg bigint) RETURNS tinyint(1)

READS SQL DATA

begin

declare intcount int;

select count(pkdm_zone.region) into intcount from pkdm_zone where  pkdm_zone.region=reg LIMIT 1;

return intcount;

end//

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Thank you for the bug report. Indeed, if you'll try to synchronize model with database two times in a row you'll see that WB tries to re-create stored function even if it had NOT changed.

The workaround is to synchronise from database.

It's even weirder for me, on MySQL Workbench 5.2.15 OSS, Revision 5053...

I have 3 stored procedures and 2 of them always update, even if there is absolutely no change to them, while one does not want to synchronize even when I edit it.

What's worse, when I force (or at least try to force) the update, by assigning the green arrow in the "Select changes to apply" section of "Synchronize model with database" dialog, for that stored procedure, it still does not incude it in the update code produced. Basically I need to drop the procedure each time on the server so I can update.

This issue still occurs in 5.2.28 and is very frustrating, as I can't reliably update my working copy of the DB's stored functions and procedures.

This issue still occurs in 5.2.31 and is impeding introduce the use of workbench on any major projects.

Closing old bug, this has been fixed previously.