MySQL Bugs: #23002: In procedure comments being dropped by mysqldump, and MySQL Administrator backup

Bug #23002	In procedure comments being dropped by mysqldump, and MySQL Administrator backup
Submitted:	5 Oct 2006 5:39	Modified:	12 Nov 2009 15:02
Reporter:	Matthew Montgomery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	1.2.16	OS:	Windows (Windows/Linux)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	backup. MySQL Administrator, comments, mysqldump, stored procedure, TTREV

Description:
When user comments appear within a stored procedure body they do not persist between dump and restore using the 'mysqldump' or MySQL Administrator backup and restore commands.

I am unable to create a procedure from the MySQL command line interface where body comments are preserved in the "SHOW CREATE PROCEDURE" output.  These comments only make it into the procedure definition when added through the Query Browser.  This may be a bug of MySQL Query Browser 1.1.20 to not strip out the comments which appear within the BEGIN/END clause. Comments which appear outside of the BEGIN/END statement are stripped away.

Below is a test performed on 5.0.17c essentially the same test case output is the same for both 5.0.17 and 5.0.24 with some slight difference on 5.0.24 for the reporting of DEFINER= information.

How to repeat:
In MySQL Query Browser:

USE test; Then issue the following...

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`TESTPROC`$$
CREATE PROCEDURE `TESTPROC`(val1 INT)
BEGIN /* Some Comments */  SELECT val1; END$$
DELIMITER ;

Verify procedure definition contains comments at command line:

mysql> SHOW CREATE PROCEDURE TESTPROC\G
*************************** 1. row ***************************
       Procedure: TESTPROC
        sql_mode:
Create Procedure: CREATE PROCEDURE `TESTPROC`(val1 INT)
BEGIN /* Some Comments */  SELECT val1; END
1 row in set (0.02 sec)

Comment appears in mysqldump output but are such that they are ignored on playback.

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `TESTPROC` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE PROCEDURE `TESTPROC`(val1 INT)
BEGIN /* Some Comments */  SELECT val1; END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;

Form of TESTPROC in MySQL Administrator backup is simpler but also are such that the comments are ignored on playback.

--
-- Structure for procedure `test`.`TESTPROC`
--

DROP PROCEDURE IF EXISTS `test`.`TESTPROC`;

DELIMITER $$
CREATE PROCEDURE  `test`.`TESTPROC`(val1 INT)
BEGIN /* Some Comments */ SELECT val1; END $$
DELIMITER ;

Following restore comments are missing:

mysql> SHOW CREATE PROCEDURE TESTPROC\G
*************************** 1. row ***************************
       Procedure: TESTPROC
        sql_mode:
Create Procedure: CREATE PROCEDURE `TESTPROC`(val1 INT)
BEGIN   SELECT val1; END

Suggested fix:
If it is the case that no comments should appear in the stored procedure body, then Query Browser or MySQL server should be fixed to strip them out. This should then be documented as a known limitation of stored procedures that inline comments are not supported and that a separate COMMENT clause should always be used.

Matthew, I can't repeat the bug exactly as you described. 
In my environment comments are _always_ stripped by MySQL command line client (mysql) before even sending a query to the server. 
How did you manage to create a procedure with a comment to it?

What version of the command line client do you use?
Could you please try to repeat your finding using the latest versions of the command line client and the server (5.0 branch).

I agree that your findings need to be documented, it's just not clear yet what exactly is there to document.

I was able to create inline comments into the body of the stored procedure using the "MySQL Query Browser" GUI Tool.  These comments were always stripped out using the CLI for me as well.

To me the problem resides on Administrator Restore, which restores a commented set of Stored Procedures (generated by Backup) but without the  comment lines.
It is an new behavior of Restore. It worked fine last month.
The circumvention is easy. Backup with Administrator and Restore with MySQL Browser, running the Script generated by Backup.

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

Exact same problem here, using MySQL Administrator version 1.2.12. Leoncio's workaround worked fine, but it also shows that it must be a bug in the administrator, because the Query Browser can restore the comments without problems.

Is anyone working on this? If the comments in my procedures are gone after restore, this effectively means a data loss for me. And data loss is certainly NOT something I want to see in a backup utility.

To keep commentaries the "--comments" switch should be used with mysql client program (The default is --skip-comments (discard comments)):

$ ../client/mysql --comments test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.54-debug Source distribution

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

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `test`.`TESTPROC`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `TESTPROC`(val1 INT)
    -> BEGIN /* Some Comments */  SELECT val1; END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SHOW CREATE PROCEDURE TESTPROC\G
*************************** 1. row ***************************
       Procedure: TESTPROC
        sql_mode: 
Create Procedure: CREATE DEFINER=``@`` PROCEDURE `TESTPROC`(val1 INT)
BEGIN /* Some Comments */  SELECT val1; END
1 row in set (0.00 sec)

Hi Gleb, 

Any reason why the default can not be changed to --comments instead of --skip-comments? Having a lossy default seems backwards to me. I know personally that this has been hit by many many people, so would rather that we stored the comments by default rather than discarding them.

Cheers,

Mark

I can report the same problem.

MySQL Administrator restores procedures without comments while MySQL Query Browser (using the same script made previously by MySQL administrator) restores them correctly.
So we can outwit the bug, but it stays active.

The backup-restore procedure is not complete (safe, consistent, etc.) until this bug is resolved properly.

(Thank you predecessors for the Query Browser workaround :)

OS: Linux, Debian Etch
mysql-server v5.0.32-Debian_7etch4
mysql-client v14.12
mysql administrator v1.2.12
mysql query browser v1.2.12

Hi Leith, Gleb....

--comments? wtf?

roland@roland-laptop:~/mysql-5.1.22-dev$ bin/mysql --comments -uroot -h127.0.0.1
bin/mysql: unknown option '--comments'

Since when was that added?

Roland,

http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html:

--comments, -c

Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments). This option was added in MySQL 5.1.23.

is there any way to get MySQL Administrator to restore without stripping out in-procedure comments?

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

workbench now uses '--comments' parameter when executing dump-import.
will be included in 5.2.7

A 'change' entry has been added to the 5.2.7 changelog:

In the Administrator tab of MySQL Workbench the Data Dump (Export/Import Data) feature now uses the --comments parameter when executing an import or export operation.