MySQL Bugs: #65394: Invalid MySQL comment syntax nested within MySQL exports

Bug #65394	Invalid MySQL comment syntax nested within MySQL exports
Submitted:	22 May 2012 15:41	Modified:	30 Jan 2013 14:45
Reporter:	Vincent Cardillo	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.5.30	OS:	Any
Assigned to:	Matthew Lord	CPU Architecture:	Any
Tags:	comment, export, import, PROCEDURE, routine, stored procedure, syntax

Description:
When performing a full export of a database, MySQL often automatically includes the MySQL specific comment syntax of: /*!yyy */ This syntax is designed to be read by only MySQL (details: http://dev.mysql.com/doc/refman/5.1/en/comments.html).

The issue is that MySQL is including this syntax within Procedure definitions, and in this instance, the engine is not parsing the comment syntax upon import. Thus, the comment syntax is making it into the Procedure definition, causing the procedure to fail. Here is an example of what the MySQL export produces in the .sql file:

++++++++++++

DELIMITER ;
USE MyDB;
/*!50003 DROP PROCEDURE IF EXISTS `myProc` */;

DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`foo`@`localhost` PROCEDURE `myProc`( IN whereClause TEXT, IN orderClause TEXT )

BEGIN
IF LENGTH(whereClause) THEN SET @kwWhere = " AND ";
ELSE SET @kwWhere = "";
END IF;
SET @query = CONCAT("
select
Table.col AS Foo,
Table.col2 AS*/ /*!50003 BusinessUnit,
Table2.col3 AS something
from OtherDB.Project P
",
@kwWhere, whereClause,
"group by P.pk ",
@kwOrder, orderClause);
PREPARE stmt FROM @query;
EXECUTE stmt;
END */;;

++++++++++++

Now once the .sql dump file is executed, within the Procedure, this line will exist verbatim:
Table.col2 AS*/ /*!50003 BusinessUnit,

This obviously throws an error, and renders the Procedure as unusable. It appears as though the mysql engine is not able to properly parse the comment syntax in this particular context.

How to repeat:
Perform full export on a MySQL database that contains a stored procedure of the nature provided in the description.

Examine the .sql dump file to observe that the */ /*! close and open comment syntax exists within the stored procedure definition.

Attempt to import the .sql file from the command line. There should be no errors. However attempting to run the Procedure will then throw an error. Attempting to Alter the Procedure using MySQL Workbench will also produce an error message from MySQL Workbench.

Suggested fix:
Do not close and then reopen the MySQL /*! */ comment syntax in the middle of a Procedure definition. Or wrap the entire procedure in a comment, rather than opening and closing comments within the middle of the procedure.

Please, check if the problem still happens with a newer version, 5.5.24. I can not repeat it with current mysql-5.5:

macbook-pro:5.5 openxs$ bin/mysqldump --routines -uroot b
-- MySQL dump 10.13  Distrib 5.5.26, for osx10.5 (i386)
--
-- Host: localhost    Database: b
-- ------------------------------------------------------
-- Server version	5.5.26-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping routines for database 'b'
--
/*!50003 DROP PROCEDURE IF EXISTS `myProc` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `myProc`( IN whereClause TEXT, IN orderClause TEXT )
BEGIN
  IF LENGTH(whereClause) THEN SET @kwWhere = " AND ";
  ELSE SET @kwWhere = "";
  END IF;
  SET @query = CONCAT("
select
        Table.col AS Foo,
        Table.col2 AS BusinessUnit,
Table2.col3 AS something
from OtherDB.Project P
",
@kwWhere, whereClause,
"group by P.pk ",
@kwOrder, orderClause);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

If 5.5.24 still produces this kind of weird comments, please, upload the entire dump and exact command line to produce it.

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".

I have a similar experience. A stored procedure contains the line:

SET i = l_loop_max;	/*  Leave loop before next function call */

After exporting with mysqldump the line is changed to:

SET i = l_loop_max;	/*  Leave loop before next*/ /*!50003 function call */

So, Mysql created a version-specific-comment!

Restoring results in syntax errors.

Thank you for the bug report, Vincent!

I was NOT able to repeat the issue this way with 5.5.30:

use test;

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProc`( IN whereClause TEXT, IN orderClause TEXT )

BEGIN
  IF LENGTH(whereClause) THEN SET @kwWhere = " AND ";
  ELSE SET @kwWhere = "";
  END IF;
  SET @query = CONCAT("
select
        Table.col AS Foo,
        Table.col2 AS BusinessUnit,
	Table2.col3 AS something
from OtherDB.Project P
",
@kwWhere, whereClause,
"group by P.pk ",
@kwOrder, orderClause);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
END ;;

Then:
mysqldump --routines test  > test.sql

I then simply cut and paste the results from the dump file back into 5.5.30:

mysql> /*!50003 DROP PROCEDURE IF EXISTS `myProc` */;
Query OK, 0 rows affected (0.01 sec)

mysql> /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET @saved_col_connection = @@collation_connection */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET character_set_client  = utf8 */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET character_set_results = utf8 */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET collation_connection  = utf8_general_ci */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!50003 SET sql_mode              = '' */ ;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;;
mysql> /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `myProc`( IN whereClause TEXT, IN orderClause TEXT )
    -> BEGIN
    ->   IF LENGTH(whereClause) THEN SET @kwWhere = " AND ";
    ->   ELSE SET @kwWhere = "";
    ->   END IF;
    ->   SET @query = CONCAT("
    "> select
    ">         Table.col AS Foo,
    ">         Table.col2 AS BusinessUnit,
    ">         Table2.col3 AS something
    "> from OtherDB.Project P
    "> ",
    -> @kwWhere, whereClause,
    -> "group by P.pk ",
    -> @kwOrder, orderClause);
    ->   PREPARE stmt FROM @query;
    ->   EXECUTE stmt;
    -> END */;;
Query OK, 0 rows affected (0.00 sec)

As you can see, I got no syntax error.

Do you feel that my test case was inadequate? You provided no actual test case, so I tried to create one from what you wrote.

There *is* one related bug fix in 5.5.30, so that is why I tested this using that version.