MySQL Bugs: #61162: Sync fails if SP or Trigger already exists

Bug #61162	Sync fails if SP or Trigger already exists
Submitted:	13 May 2011 8:23	Modified:	15 Jun 2011 14:18
Reporter:	Michael G. Zinner	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.33	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When entering the following statement in the Stored Routine Editor and then running a sync with the live database, the DROP statement gets lost.

http://pastebin.com/Jq2A5Y6E

This is the content of the sync-script that WB generates.

http://pastebin.com/U0QSYyQC

How to repeat:
1.) Create a routine with the SQL from http://pastebin.com/Jq2A5Y6E

2.) Sync with DB

3.) Check the sync script, it looks like this: http://pastebin.com/U0QSYyQC

Suggested fix:
Do not drop SQL statements.

This also happens in the Trigger Editor.

Thank you for the bug report. Verified on Windows XP.

According to reporter, the actual problem that was seen is that synchronization fails for SPs and triggers because the old one is not dropped before creating them. 

SQL statements unrelated to the SP or Trigger are not maintained and is not a bug and will not be changed anytime soon.

Updating summary and reopening bug. From a quick test I did, I cannot repeat the problem. The SP is properly dropped before creating it.

Hey,

 I am the bug reporter. It does happen each and every time I do that. Tested only on Windows versions of the Workbench and specifically on Windows 7 Pro and Ult both in 64 bit.

Can you upload the model and the resulting script?

Here is the model with some stored procedures and triggers in it; this happens for each and every one of them so it's not really a particular pick:

http://bit.ly/jNfehx

try to add to a sp or a trigger the following statement, above the initiation of the create trigger/sp statements and you'll see what i am talking about.

Let me know if there is something else

Hi

Can you provide step by step instructions to repeat what you're seeing? Because I synchronized your model to my DB and it worked OK. The 2nd time I tried to sync, it properly gave an empty script. Then I modified the definition of one of the triggers and got the stuff below. As you see, there's a DROP statement as expected. 
<snip>

DELIMITER $$

USE `mydb`$$
DROP TRIGGER IF EXISTS `mydb`.`prescription_creditcheck_insert` $$

DELIMITER ;

DELIMITER $$

USE `mydb`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `mydb`.`prescription_creditcheck_insert`
BEFORE INSERT ON `mydb`.`prescription`
FOR EACH ROW
begin
        -- declare our variables
        declare current_credit decimal(10,2) default 0;
        declare tpriceXXX decimal(10,2) default 0;

<snip>

Hi,

 Thanks for the reply, well the steps are:

1) write first the script with the drop if exists above the create statement
2) Then sync; which is done properly. 
3) Save the model (mwb file)
4) Change something in the script
5) Sync

The drop statement should be gone. At least that's I experience...

Ok, so you expect the behaviour that was initially reported by Mike...
Drop statements are automatically added if you're sycnrhonizing against a model that already
has the same object, why do you want to keep them?

In any case, adding extra statements besides the CREATE itself is currently not supported.
It is not properly a bug, it simply was never meant to be maintained in the first place.
We'll internally discuss whether this should be treated as a feature request; but anyway, 
why do you want to add the DROPs manually if they get auto-added if required during sync?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".