MySQL Bugs: #39559: dump of stored procedures / functions with C-style comment can't be read back

Bug #39559	dump of stored procedures / functions with C-style comment can't be read back
Submitted:	20 Sep 2008 16:29	Modified:	13 May 2009 1:44
Reporter:	Roland Volkmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1.28-rc, 5.1, 6.0 bzr	OS:	Windows (XP Prof. SP3 german)
Assigned to:	Chad MILLER	CPU Architecture:	Any
Tags:	mysql.exe, mysqldump, regression, stored function, stored procedure

Description:
If you have a stored procedure or stored function containing C-Style comment, mysqldump adds an additional C-Style comment arround the definition for that stored procedure / function. So that dump can't be read back by mysql.exe due to nested comment, producing syntax error. Used engine is innodb.

Critical part of dump may be:

DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER="root"@"%"*/ /*!50003 PROCEDURE "p_test"(IN $in INTEGER)
BEGIN
  /* for test only */
  SELECT 2*$in;
END */;;
DELIMITER ; 

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> connect test
Connection id:    18
Current database: test

mysql> delimiter ;;
mysql> CREATE PROCEDURE "p_test"(IN $in INTEGER)
    -> BEGIN
    ->   /* for test only */
    ->   SELECT 2*$in;
    -> END;
    -> delimiter ;
Query OK, 0 rows affected (0.00 sec)

mysql>

Now do a dump from command line:
mysqldump --default-character-set=latin1 --routines --single-transaction  --user=root --password=secret --result-file=test_dump.sql -B test

mysql> drop procedure p_test;
Query OK, 0 rows affected (0.14 sec)

mysql> \. test_dump.sql
Query OK, 0 rows affected (0.00 sec)
[...]
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 '*/' a
t line 4

Using client of mysql version 5.0.68-gpl you can read back the above dump created with version 5.1

Best regards,
Roland.

Thank you for the report.

I can not copy-paste procedure provided:

$mysql bug39559
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 999
Server version: 5.1.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop procedure p_test;
ERROR 1305 (42000): PROCEDURE bug39559.p_test does not exist
mysql> delimiter ;;
mysql> CREATE PROCEDURE "p_test"(IN $in INTEGER)
    -> BEGIN
    -> /* for test only */
    ->    SELECT 2*$in;
    -> END;
    -> delimiter ;
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 '"p_test"(IN $in INTEGER)
BEGIN

   SELECT 2*$in;
END' at line 1
mysql> 

Please provide correct copy-paste from the mysql command line client.

Hello Sveta,

sorry - I have forgotten to tell that I'm using special sql-mode:
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI"

So you can either use my original example with this sql-mode, or you can use default sql-mode and remove the double quotes from procedure definition:

delimiter ;;
CREATE PROCEDURE p_test(IN $in INTEGER)
 BEGIN
 /* for test only */
    SELECT 2*$in;
 END;
delimiter ;

With best regards,
Roland.

Thank you for the feedback.

I was able to create procedure, but can not repeat error with dump reload. Could you please provide dump file created in your environment?

This time, for creating the procedure p_test, dumping the database 'test', and reloding the dump I used default sql-mode (no explicit setting, line commented out in my.ini). The dump itself is done with following command line:

mysqldump --default-character-set=latin1 --routines --single-transaction  --user=root --password=secret --result-file=test_dump.sql -B test

dump of database 'test' containing procedure p_test

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

Thank you for the feedback.

Unfortunately nor me, neither my colleague could repeat described error. So I close the report as "Can't repeat". If you are able to provide more information how to repeat the problem feel free to reopen the report.

Additionally this looks like bug #38158 which should be fixed in version 5.1.28. Please re-check if you upgraded mysql command line client.

Hello Sveta,

when using a new installation out of the box, I can't repeat the bug too. Analysing the differences between my regular installation and the test installation gives the answer:

it's the "comments"-option in my.ini

If there is no "comments"-option in my.ini then you can read in the above dump. But as soon you set this option, you get the error.

So please reopen this bug report, because setting this option is neccessary for not loosing the comments on restoring a dump.

Best regards,
Roland.

Hello Roland,

thank you for finding the cause. But, please, send exact option you use.

Configuration file with the critical option (look for the '!!!!!!!!' to find it)

Attachment: my.ini (application/octet-stream, text), 8.85 KiB.

The description of the option "comments" can be found in the reference manual, section  "4.5.1.1. mysql Options"

Roland.

Thank you for the feedback.

Thought about server options :)

Verified with following test case:

set sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI";

delimiter |;
CREATE PROCEDURE p_test(IN $in INTEGER)
 BEGIN
 /* for test only */
    SELECT 2*$in;
 END|
delimiter ;|

--exec $MYSQL_DUMP --default-character-set=latin1 --routines --single-transaction test | $MYSQL --comment test

Version 5.0 is not affected.

Changing category, since the problem is actually server-side.

Bug #44189 was marked as a duplicate of this one.

Now I am not able to understand why
http://bugs.mysql.com/bug.php?id=30631
.. was not taken seriously at the time I reported it!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71838

2841 Chad MILLER	2009-04-09
      Bug#39559: dump of stored procedures / functions with C-style \
      	comment can't be read back
      
      A change to the lexer in 5.1 caused slash-asterisk-bang-version
      sections to be terminated early if there exists a slash-asterisk-
      style comment inside it.  Nesting comments is usually illegal,
      but we rely on versioned comment blocks in mysqldump, and the
      contents of those sections must be allowed to have comments.
      
      The problem was that when encountering open-comment tokens and
      consuming -or- passing through the contents, the "in_comment"
      state at the end was clobbered with the not-in-a-comment value,
      regardless of whether we were in a comment before this or not.  
      
      So, """/*!VER one /* two */ three */""" would lose its in-comment
      state between "two" and "three".  Save the echo and in-comment
      state, and restore it at the end of the comment if we consume a 
      comment.

The second patch is approved, ok to push

Queued to 5.1- and 6.0-bugteam trees.

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:chad@mysql.com-20090410143330-88se7rvl2qd48lk1) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.1.35, 6.0.12 changelogs.

A stored routine contain a C-style comment could not be dumped and reloaded.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)