MySQL Bugs: #55557: can not import functions and procedures with comment "/* Procedure"

Bug #55557	can not import functions and procedures with comment "/* Procedure"
Submitted:	26 Jul 2010 13:21	Modified:	26 Jul 2010 13:37
Reporter:	Seweryn Ozog	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.1.49, 5.1.50-bzr	OS:	Linux (all)
Assigned to:		CPU Architecture:	Any
Tags:	Backup, comment, functions, import, procedures

Description:
I can not import functions and procedures  with comment like "/* Procedure test dump" (first space in comment is very important). 

Problem is in dump.sql file:

In function comment without space I have:
/*Procedure test dump
* @modify
* 2010-06-08
*/

In function comment with space I have:
/**/ /*!50003 Procedure test dump
* @modify
* 2010-06-08
*/ 

I can not import dump.sql with comments like in the second example.

How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.49    |
+-----------+
1 row in set (0.00 sec) 

DELIMITER // 
drop database test_proc//
create database test_proc//
use test_proc//

CREATE TABLE `test123` (
  `abc` int(11) unsigned NOT NULL DEFAULT '0',
  `abcd` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`abc`,`abcd`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin//

insert into test123 VALUES (1,2)//
insert into test123 VALUES (2,3)//
insert into test123 VALUES (3,4)//
insert into test123 VALUES (4,5)//

CREATE DEFINER=`root`@`%` FUNCTION `test_dump_without_space`(
p_cat_id int unsigned) RETURNS varchar(15) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare v_cat_ids varchar(15);
/*Procedure test dump
* @modify
* 2010-06-08
*/
      select abcd into v_cat_ids from test123
   where abc = p_cat_id;
  RETURN v_cat_ids;
END //

CREATE DEFINER=`root`@`%` FUNCTION `test_dump_with_space`(
p_cat_id int unsigned) RETURNS varchar(15) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare v_cat_ids varchar(15);
/* Procedure test dump
* @modify
* 2010-06-08
*/
      select abcd into v_cat_ids from test123
   where abc = p_cat_id;
  RETURN v_cat_ids;
END //

mysqldump -d -R test_proc  > dump.sql

mysql -c test_proc < dump.sql

ERROR 1064 (42000) at line 78: 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 'Procedure test dump
* @modify
* 2010-06-08
*/
      select abcd into v_cat_ids f' at line 7

Verified just as described:

macbook-pro:5.1 openxs$ bin/mysql -c -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER // 
mysql> drop database test_proc//
ERROR 1008 (HY000): Can't drop database 'test_proc'; database doesn't exist
mysql> create database test_proc//
Query OK, 1 row affected (0.00 sec)

mysql> use test_proc//
Database changed
mysql> 
mysql> CREATE TABLE `test123` (
    ->   `abc` int(11) unsigned NOT NULL DEFAULT '0',
    ->   `abcd` int(11) unsigned NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`abc`,`abcd`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin//

insert into test123 VALUES (1,2)//
insert into test123 VALUES (2,3)//
insert into test123 VALUES (3,4)//
insert into test123 VALUES (4,5)//

CREATE DEFINER=`root`@`%` FUNCTION `test_dump_without_space`(
p_cat_id int unsigned) RETURNS varchar(15) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare v_cat_ids varchar(15);
/*Procedure test dump
* @modify
* 2010-06-08
*/
      select abcd into v_cat_ids from test123
   where abc = p_cat_id;
  RETURN v_cat_ids;
END //

CREATE DEFINER=`root`@`%` FUNCTION `test_dump_with_space`(
p_cat_id int unsigned) RETURNS varchar(15) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare v_cat_ids varchar(15);
/* Procedure test dump
* @modify
* 2010-06-08
*/
      select abcd into v_cat_ids from test123
   where abc = p_cat_id;
  RETURN v_cat_ids;
END //
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin//
Query OK, 0 rows affected (0.39 sec)

mysql> insert into test123 VALUES (1,2)//
Query OK, 1 row affected (0.00 sec)

mysql> insert into test123 VALUES (2,3)//
Query OK, 1 row affected (0.00 sec)

mysql> insert into test123 VALUES (3,4)//
Query OK, 1 row affected (0.00 sec)

mysql> insert into test123 VALUES (4,5)//
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DEFINER=`root`@`%` FUNCTION `test_dump_without_space`(
    -> p_cat_id int unsigned) RETURNS varchar(15) CHARSET utf8
    ->     READS SQL DATA
    ->     DETERMINISTIC
    -> BEGIN
    -> declare v_cat_ids varchar(15);
    -> /*Procedure test dump
   /*> * @modify
   /*> * 2010-06-08
   /*> */
    ->       select abcd into v_cat_ids from test123
    ->    where abc = p_cat_id;
    ->   RETURN v_cat_ids;
    -> END //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DEFINER=`root`@`%` FUNCTION `test_dump_with_space`(
    -> p_cat_id int unsigned) RETURNS varchar(15) CHARSET utf8
    ->     READS SQL DATA
    ->     DETERMINISTIC
    -> BEGIN
    -> declare v_cat_ids varchar(15);
    -> /* Procedure test dump
   /*> * @modify
   /*> * 2010-06-08
   /*> */
    ->       select abcd into v_cat_ids from test123
    ->    where abc = p_cat_id;
    ->   RETURN v_cat_ids;
    -> END //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1449
Message: The user specified as a definer ('root'@'%') does not exist
1 row in set (0.00 sec)

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysqldump -uroot -d -R test_proc  > /tmp/dump.sql
macbook-pro:5.1 openxs$ bin/mysql -c -uroot test_proc < /tmp/dump.sql
ERROR 1064 (42000) at line 74: 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 'Procedure test dump
* @modify
* 2010-06-08
*/
      select abcd into v_cat_ids f' at line 7

With comments, restore will fail

Attachment: test_dump.sql (application/octet-stream, text), 4.95 KiB.

Comments stripped, restore will succeed

Attachment: test_dump_no_comment.sql (application/octet-stream, text), 4.71 KiB.

I'm having the same problem with triggers - and have uploaded two files you can use to recreate and test. test_dump.sql - with the comments, will fail with erroneous errors, like:

    IF IF(NEW.some_nullable_string IS NULL AND OLD.some_nullable_string IS ' at line 1that corresponds to your MySQL server version for the right syntax to use near 'IF;

This is extremely serious, IMHO, because it prevents a backup/restore cycle for databases containing commented routines.

This is also a bit problem for me, except my comments are ALSO this form:

-- MySQL dump 8.23
--
etc etc

This is a show-stopper for me and is the reason why I don't upgrade the server past version 3 in some places!