Bug #14487 Comments are being ripped out of SP's during restore.
Submitted: 30 Oct 2005 8:37 Modified: 31 Oct 2005 7:45
Reporter: Peter Hamilton-Scott Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:5.0.16-BK OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[30 Oct 2005 8:37] Peter Hamilton-Scott
Description:
Here is an extract from a backup of a SP I created.

--
-- Dumping routines for database 'cachelistener'
--
DELIMITER ;;
/*!50003 DROP PROCEDURE IF EXISTS `AddFile` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE PROCEDURE `AddFile`(in filename varchar(100))
BEGIN

    -- One day I will grow up to be a procedure.

END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteFileById` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE PROCEDURE `DeleteFileById`(in fileid integer)
proc : BEGIN

    -- 2005/10/29 PHS. Original coding.

    -- Delete a record using the unique file id parameter.

    delete from files where id = fileid;

END proc */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!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 */;

As you see, there are comments in my SP's. I then restored this backup into my database which I dropped and recreated. This is what my SP looks like after I backup the database:

--
-- Dumping routines for database 'cachelistener'
--
DELIMITER ;;
/*!50003 DROP PROCEDURE IF EXISTS `AddFile` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE PROCEDURE `AddFile`(in filename varchar(100))
BEGIN
    
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteFileById` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE PROCEDURE `DeleteFileById`(in fileid integer)
proc : BEGIN
    
    
    delete from files where id = fileid;
END proc */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!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 */;

As you can see the comments have been ripped out by MySql. I looked at the SP in the query browser and they are missing there as well so it looks like they are simply thrown away during the restore. It's almost like it wanted to do something positive with them as MySql has replaced the comment lines with blank lines!

How to repeat:
The attached extracts from a before and after backup show the problem clearly enough.

Suggested fix:
It is arguable that comments are superflous. Admittedly, they contribute nothing to the execution at runtime but I consider comments to be a crucial part of the development process. Why they are being trashed on restore is not something I can explain. Comments consume minimal overheads during the parsing process but I find this bug irritating to the extent that if I can't trust MySql to handle something as dumb and trivial as a comment, then God alone knows what it is doing with the code of the SP itself. Please fix this ASAP, as it is discouraging my use of SP's as I'm not sure I can actually trust them to do anything correctly. :-(

Thanks.

Peter.
[30 Oct 2005 8:39] Peter Hamilton-Scott
A mysqldump generated backup file.

Attachment: CacheListener.backup (application/octet-stream, text), 7.26 KiB.

[30 Oct 2005 11:30] Vasily Kishkin
Could you please write here procedure definition and mysqldump options line ? Thanks
[30 Oct 2005 11:30] Valeriy Kravchuk
Thank you for a problem report. I want to call it a "feature request", but simply can't. Even if it is an intended behaviour, somehow, it should be documented in prominent place, and it is weird.

The problem on 5.0.16-BK on Linux is not with mysqldump only (mysqldump test -R -uroot > /tmp/dump.sql on 5.0.16 give me the same blank lines instead of comments in the dump, by the way):

mysql> delimiter //
mysql> create procedure p_comm()
    -> begin
    ->   -- my useful comment
    ->   select 1;
    ->   /* yet another one */
    -> end//
Query OK, 0 rows affected (0,04 sec)

mysql> call p_comm()//
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,01 sec)

mysql> show create procedure p_comm//
+-----------+----------+---------------------------------------------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+---------------------------------------------------------+
| P_COMM    |          | CREATE PROCEDURE `P_COMM`()
begin

  select 1;

end |
+-----------+----------+---------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select body from mysql.proc where name = 'p_comm'//
+-----------------------------+
| body                        |
+-----------------------------+
| begin

  select 1;

end |
+-----------------------------+
1 row in set (0,02 sec)

mysql> select routine_definition from information_schema.routines where routine_name = 'p_comm'//
+-----------------------------+
| routine_definition          |
+-----------------------------+
| begin

  select 1;

end |
+-----------------------------+
1 row in set (0,01 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0,01 sec)

So, looks like comments are not stored at all anywhere now. I think, it is a bug. Verified on ChangeSet@1.1949, 2005-10-28 13:02:26+04:00, petr@mysql.com.
[31 Oct 2005 7:45] Peter Hamilton-Scott
Vasily, my apologies for not making that known, but I'm running on W2K/XP and not Linux as you have listed as the OS. In any case, here is the mysqldump options you request. I've not really used it before so my command line is somewhat long and probably too long as I'm sure there is a shorter command I can use to do the same thing. Here it is:

mysqldump --extended-insert=FALSE --add-drop-database=TRUE --add-drop-table=TRUE --disable-keys=TRUE --order-by-primary=TRUE --routines=TRUE --triggers=TRUE --user=phs --verbose=FALSE cachelistener > CacheListener.backup

I no longer have comments in my SP's as I was spending a lot of time trying to find out why they don't import on restore but it looks like you've been able to recreate it anyway. Thanks. Peter.
[2 Jul 2006 22:01] Marc Alff
For comments in stored procedures,
see related Bug#11230
[4 Oct 2008 9:53] Konstantin Osipov
This is not a server issue, it's a client issue.
MySQL command line client strips comments before sending the query to the server.

mysql> create procedure p1() begin
    -> -- comment
    -> end|

Breakpoint 1, sp_create_routine (thd=0x9a20210, type=2, sp=0x9a8f800)
    at sp.cc:720
720	sp_create_routine(THD *thd, int type, sp_head *sp)
(gdb) p thd->query
$1 = 0x9a79d58 "create procedure p1() begin\nend"