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:Microsoft Windows (XP Prof. SP3 german)
Assigned to: Chad MILLER CPU Architecture:Any
Tags: mysql.exe, mysqldump, regression, stored function, stored procedure
Triage: Triaged: D2 (Serious)

[20 Sep 2008 16:29] Roland Volkmann
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
[20 Sep 2008 16:36] Roland Volkmann
Using client of mysql version 5.0.68-gpl you can read back the above dump created with version 5.1

Best regards,
Roland.
[22 Sep 2008 6:09] Sveta Smirnova
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.
[22 Sep 2008 6:36] Roland Volkmann
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.
[22 Sep 2008 8:50] Sveta Smirnova
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?
[22 Sep 2008 20:43] Roland Volkmann
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
[22 Sep 2008 20:44] Roland Volkmann
dump of database 'test' containing procedure p_test

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

[23 Sep 2008 15:25] Sveta Smirnova
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.
[23 Sep 2008 22:08] Roland Volkmann
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.
[24 Sep 2008 5:15] Sveta Smirnova
Hello Roland,

thank you for finding the cause. But, please, send exact option you use.
[24 Sep 2008 8:45] Roland Volkmann
Configuration file with the critical option (look for the '!!!!!!!!' to find it)

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

[24 Sep 2008 8:49] Roland Volkmann
The description of the option "comments" can be found in the reference manual, section  "4.5.1.1. mysql Options"

Roland.
[24 Sep 2008 9:31] Sveta Smirnova
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.
[7 Apr 2009 20:21] Jim Winstead
Changing category, since the problem is actually server-side.
[9 Apr 2009 15:18] Valeriy Kravchuk
Bug #44189 was marked as a duplicate of this one.
[9 Apr 2009 15:48] Peter Laursen
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!
[10 Apr 2009 2:23] Bugs System
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.
[10 Apr 2009 3:07] Marc Alff
The second patch is approved, ok to push
[10 Apr 2009 14:38] Chad MILLER
Queued to 5.1- and 6.0-bugteam trees.
[5 May 2009 19:41] Bugs System
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)
[6 May 2009 14:09] Bugs System
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)
[13 May 2009 1:44] Paul Dubois
Noted in 5.1.35, 6.0.12 changelogs.

A stored routine contain a C-style comment could not be dumped and reloaded.
[15 Jun 2009 8:28] Bugs System
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)
[15 Jun 2009 9:08] Bugs System
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)
[15 Jun 2009 9:49] Bugs System
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)