MySQL Bugs: #50343: mysqldump breaks on restore if last line of stored procedure is a comment

Bug #50343	mysqldump breaks on restore if last line of stored procedure is a comment
Submitted:	14 Jan 2010 18:18	Modified:	26 Feb 2010 8:07
Reporter:	Ariel Antebi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.31, 5.1.42	OS:	Linux (Ubuntu)
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, MySQL, mysqldump

Description:
If the last line of a function is a comment and you mysqldump with the --routines argument, the last line of the function declaration (closing the declaration so it can start a new one) will be totally commented out.

Example of sql from dumpfile:

/*!50003 CREATE*/ /*!50020 DEFINER=`user`@`localhost`*/ /*!50003 PROCEDURE `SelectData`()
select data from table

-- This function selects some data */;;

As you can see, the */;; is commented out when replaying this SQL back into the database such as by using: `mysql -p < mysqldumpfile.sql` and causes a syntax error when trying to continue with the rest of the dump.

How to repeat:
Create a function, make the last line a comment e.g.

-- Comment

Then back up your db and restore:

mysqldump --routines --all-databases > dump.sql
mysql < dump.sql

Thank you for the problem report. Please, check with a newer version, 5.1.42, and inform about the results.

Yep, still happens.  It breaks anything that comes after the function.

./mysqldump --version
mysqldump  Ver 10.13 Distrib 5.1.42, for unknown-linux-gnu (x86_64)

Thank you for the report.

Please provide full statement you used to create stored procedure. Or output of SELECT * FROM mysql.proc before execution of mysqldump.

I made a db `test` with a table `local` with 1 row, then made a function `test`:

SELECT * FROM test.local;
-- Comment

At this point I did a dump and loaded the db from dump as described above and it worked.  I then created `test2` function so there would be some SQL after `test`:

SELECT * FROM test.local;

And this time reloading from the dumpfile produced an error.

Thank you for the feedback.

I can not repeat described behavior with following test:

$mysql51 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 1061
Server version: 5.1.44-debug Source distribution

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

mysql> drop procedure p1;
ERROR 1305 (42000): PROCEDURE test.p1 does not exist
mysql> drop procedure p2;
ERROR 1305 (42000): PROCEDURE test.p2 does not exist
mysql> delimiter |
mysql> create procedure p1()
    -> begin
    -> select 1;
    -> -- This function selects some data
    -> end
    -> |
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> create procedure p2()
    -> begin
    -> select 2;
    -> end
    -> |
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> delimiter ;
mysql> \q
Bye

$mysqldump51 --routines test | mysql511 test

mysql> select * from mysql.proc where db='test' and specific_name like 'p%'\G
*************************** 1. row ***************************
                  db: test
                name: p1
                type: PROCEDURE
       specific_name: p1
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list: 
             returns: 
                body: begin
select 1;

end
             definer: root@localhost
             created: 2010-01-26 11:04:14
            modified: 2010-01-26 11:04:14
            sql_mode: 
             comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
        db_collation: utf8_general_ci
           body_utf8: begin
select 1;

end
*************************** 2. row ***************************
                  db: test
                name: p2
                type: PROCEDURE
       specific_name: p2
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list: 
             returns: 
                body: begin
select 2;
end
             definer: root@localhost
             created: 2010-01-26 11:04:14
            modified: 2010-01-26 11:04:14
            sql_mode: 
             comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
        db_collation: utf8_general_ci
           body_utf8: begin
select 2;
end
2 rows in set (0.50 sec)

mysql> call p1();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p2();
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Please provide terminal session showing the problem like I did.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".