MySQL Bugs: #59374: Bad magic from variable names

Bug #59374	Bad magic from variable names
Submitted:	9 Jan 2011 2:58	Modified:	9 Jan 2011 11:42
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
All variable names beginning with `var_go` tried ...

drop procedure if exists p;
delimiter go
create procedure p()
BEGIN
  DECLARE var_go INT;
END;
go
delimiter ;

elicit ERROR 1064 (42000): 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 'INT; END' at line 1

How to repeat:
As above

Suggested fix:
Avoid such innocuous variable names?

If you use another DELIMITER than "go" it works; 

DROP PROCEDURE IF EXISTS p;
DELIMITER $$
CREATE PROCEDURE p()
BEGIN
  DECLARE var_go INT;
END;
$$
DELIMITER ;

.. so it looks like the "go" in "var_go" delimits and the script is parsed something like this 

DROP PROCEDURE IF EXISTS p;
DELIMITER $$
CREATE PROCEDURE p()
BEGIN
  DECLARE var_ $$ INT;
END;
$$
DELIMITER ;

Additionally you should tell what client you are using.  Parsing for DELIMITER happens client-side (no 'DELIMITER statement" is ever sent to the server).

In command line client there are two errors:

mysql> use test
Database changed
mysql> drop procedure if exists p;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter go
mysql> create procedure p()
    -> BEGIN
    ->   DECLARE var_go INT;
ERROR 1064 (42000): 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 '' at
line 3
    -> END;
    -> go
ERROR 1064 (42000): 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 'INT;
END' at line 1
mysql> delimiter ;

Also this fails

mysql> DELIMITER ;
mysql> DROP PROCEDURE IF EXISTS p;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE p()
    -> BEGIN
    ->   DECLARE var_$$ INT;
ERROR 1064 (42000): 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 '' at
line 3
    -> END;
    -> $$
ERROR 1064 (42000): 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 'INT;
END' at line 1
mysql> DELIMITER ;

.. so it seems the problem occurs if the user-defined DELIMITER is a substring of user-defined variable.

If you happen to be a SQLyog user I have added this issue to our issue tracker here: http://code.google.com/p/sqlyog/issues/detail?id=1591

(but note that also command line has problems - though errors are not exactly the same)

So, what exact client do you use?

This is IMHO a client bug. But not sure if it is a bug in the client API (libmysql) or or if it is just a bug with *client applications* (including 'mysql' command line client) tested.

Not sure about any other client, but mysql command line client in this case works as designed and described at http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html.

Since I have no option to reopen here I created a new report:
http://bugs.mysql.com/bug.php?id=59379

+ http://bugs.mysql.com/bug.php?id=59381