MySQL Bugs: #61232: Could not Execute mysql script using MySQL workbench

Bug #61232	Could not Execute mysql script using MySQL workbench
Submitted:	19 May 2011 19:14	Modified:	23 May 2011 21:23
Reporter:	Heena Shah	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.33b	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	script

Description:
Platform: MySQL Workbench on Windows

I have created a script and saved on local windows machine. I wanted to use and run this script file on Mysql Workbench...while doing so - using the Script tab.
It reports error while Execute command.

** I am able to run/execute on MySQL server - without workbench application.

How to repeat:
Same as description.

Thank you for the bug report. Could you please provide the mentioned script. Thanks.

Here is the script I tried on mysql workbench.

drop procedure if exists test.has_addcol;

delimiter '//'
CREATE PROCEDURE test.has_addcol()
BEGIN

-- table column move
alter table test.hastest1 change state state varchar(45) default NULL after addr;

-- add new column, if not exists
IF EXISTS(
        SELECT * FROM information_schema.COLUMNS
        WHERE COLUMN_NAME='zipCode' AND TABLE_NAME='hastest1' AND TABLE_SCHEMA='test'
        )
        THEN
                ALTER TABLE `test`.`hastest1`
                DROP COLUMN `zipCode` ;

END IF;
                ALTER TABLE `test`.`hastest1`
                ADD COLUMN `zipCode` mediumint(4);

END;
//

delimiter ';'

CALL test.has_addcol();

DROP PROCEDURE test.has_addcol;

What exact version of MySQL Workbench, 5.x.y, do you use?

MySQL workbench version: 5.2.33 CE Revision 7508

Are you using the SQL Editor? Or the Scripting Shell? The Scripting Shell is not intended to execute MySQL scripts.

I used SQL editor.

With MySQL workbench > What should I use to execute MySQL script, I described above? It generates error:

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//

Try removing the ; after the last END from your code

I tried as you suggested - no success >> same result shows up.
Just wondering why the code successfully works using SQL command line > run scripting file... and not run using workbench.

following URL helped me ....
http://forums.mysql.com/read.php?99,375530,375845#msg-375845

I changed my code like this >> and it worked.

DELIMITER $$
CREATE PROCEDURE test.has_addcol()
BEGIN
select 1;
alter table test.hastest1 change state state varchar(45) default NULL after addr;

-- add new column, if not exists
IF EXISTS(
        SELECT * FROM information_schema.COLUMNS
        WHERE COLUMN_NAME='zipCode' AND TABLE_NAME='hastest1' AND TABLE_SCHEMA='test'
        )
        THEN
                ALTER TABLE `test`.`hastest1`
                DROP COLUMN `zipCode` ;

END IF;
                ALTER TABLE `test`.`hastest1`
                ADD COLUMN `zipCode` mediumint(4);

END$$
DELIMITER ;

CALL test.has_addcol();

drop procedure test.has_addcol;