MySQL Bugs: #14145: mysqldump --routines creates non-reloadable output

Bug #14145	mysqldump --routines creates non-reloadable output
Submitted:	19 Oct 2005 15:39	Modified:	14 Jul 2006 19:15
Reporter:	Paul DuBois	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0.15/5.0.16 BK source	OS:	Linux (Linux)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
mysqldump --routines can create output that cannot be
reloaded. This occurs when a single-statement routine is
not defined using BEGIN/END around the routine body.

This might be a mysqldump problem, or a mysql parsing
problem.

How to repeat:
Create a routine like this:

create function f () returns int return 1;

Then dump it with mysqldump --routines.  The dump file will contain
this output:

--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `f` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE FUNCTION `f`() RETURNS int(11)
return 1 */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;

Which fails when reloaded with this error message:

ERROR 1064 (42000) at line 24: 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 2

The '/' in the error message is the one on the "return 1 */;;" line.
I suppose that the "1 *" is ambiguous because it could begin an
expression such as 1 * 2.

I tried adding a semicolon to the dump after "return 1":

/*!50003 CREATE FUNCTION `f`() RETURNS int(11)
return 1; */;;

That failed with:

ERROR 1064 (42000) at line 24: 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 'return 1; */' at line 2

Perhaps that's a mysql parser problem.

Next I added begin/end, which worked as long as I did *not* add a semicolon
after the "end" keyword (that is unfortunate, because it should be legal):

/*!50003 CREATE FUNCTION `f`() RETURNS int(11)
begin return 1; end */;;

for now in my backups, I am doing :

CREATE TABLE views SELECT * FROM information_schema.views
CREATE TABLE routines SELECT * FROM information_schema.routines
CREATE TABLE triggers SELECT * FROM information_schema.triggers

Here is the test I tried with extra output removed.
-----------------
master> create function f () returns int return 1;
Query OK, 0 rows affected (0.01 sec)

./mysqldump -u root -S ../mysql-test/var/tmp/master.sock test --routines > backup.sql

master> drop function f;
Query OK, 0 rows affected (0.00 sec)
master> show create function f;
ERROR 1305 (42000): FUNCTION f does not exist

./mysql -u root -S ../mysql-test/var/tmp/master.sock test < backup.sql

master> show create function f;
+----------+----------+---------------------------------------------------------------------------+
| Function | sql_mode | Create Function                                                           |
+----------+----------+---------------------------------------------------------------------------+
| f        |          | CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS int(11)
return 1 |
+----------+----------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
master> select @@version;
+------------------+
| @@version        |
+------------------+
| 5.0.25-debug-log |
+------------------+
1 row in set (0.00 sec)

I am also unable to reproduce with current MySQL 5.0 code.