MySQL Bugs: #53760: MySql Workbench cannot create two procedures in a row

Bug #53760	MySql Workbench cannot create two procedures in a row
Submitted:	18 May 2010 19:03	Modified:	28 May 2010 10:02
Reporter:	John Dieter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.21	OS:	Windows (2008)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	1304, create procedure, drop procedure, MySQL Workbench

Description:
drop procedure not working when run using MySql Workbench 
Drop and create procedure script works fine in the cmd line.

How to repeat:
Run this script in MySql Workbench:

DROP PROCEDURE IF EXISTS testthis;
delimiter //
CREATE PROCEDURE testthis ()
BEGIN
    select 'hello' from dual;
END//
delimiter ; 

DROP PROCEDURE IF EXISTS testthat;
delimiter //
CREATE PROCEDURE testthat ()
BEGIN
SELECT 'goodbye' from dual;
END//
delimiter ;

You will get the following error:
Error Code: 1304
PROCEDURE testthat already exists

MySql workbench 5.2.20 OSS Beta rev 5783 hitting 5.5.3-m3-community (both 32 bit) on a windows 2008 64 bit machine.

NOTE: the very first time (when the procs testthis and test that don't exist) you wont get the error. The procs have to exist for it to fail. It seems the second "drop" statement isn't running.

Please, check with a newer version, 5.2.21, and inform about the results. I do not see this problem with 5.2.21 on Mac OS X at least.

I am going to test this with 5.2.21- rc

This behavior IS still exhibited in ver 5.2.21 OSS RC Revision 5918
on Windows 2008

I figured out what causes it. On line 7 of the SQL, there is a space after the ; on the line:
delimiter ;

When that space is back spaced out of the query it starts working. 
Bad line hex: 64656C696D69746572203B200A
good line hex: 64656C696D69746572203B0A

So, apparently there is a problem parsing 'deliimiter[space];[space][CR]

Verified as described.

Open Workbench
Open SQL Editor

Take an existing Schema and type:

USE existing_schema;

Now copy the following into the editor:
DROP PROCEDURE IF EXISTS testthis;
delimiter //
CREATE PROCEDURE testthis ()
BEGIN
    select 'hello' from dual;
END//
delimiter ; 

DROP PROCEDURE IF EXISTS testthat;
delimiter //
CREATE PROCEDURE testthat ()
BEGIN
SELECT 'goodbye' from dual;
END//
delimiter ;

Execute

All is fine

Execute again

You will see that system can't create second procedure.

fix confirmed in repository

An entry has been added to the 5.2.22 changelog:

In the SQL Editor, if two procedures were defined in a script, each proceded with a suitable DROP statement, then when the script was executed and the procedures already existed a procedure already defined error occurred. It appeared that the second DROP statement was being ignored. This happened if the line preceding the second DROP statement was terminated with a space, which caused the DROP statement to be ignored, resulting in the error.