Bug #42327 MySQLDump + Stored Proc bug
Submitted: 25 Jan 2009 14:55 Modified: 25 Jan 2009 18:02
Reporter: Cal Leeming [Simplicity Media Ltd] Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.67, 5.0 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: commented, crlf, mysqldump, routines, stored procedures

[25 Jan 2009 14:55] Cal Leeming [Simplicity Media Ltd]
Description:
If exporting stored procedures/routines with MySQLDump, if the last line is a 'comment' or has been commented out, it will break the MySQLDump termination.

The reason being is because it doesn't add a CRLF onto the '*/;;'

I recommend forcing MySQLDump to put a CRLF before the termination characters, so that if comments are in there, developers aren't left scratching their heads for 30 minutes (Like i was! ;p)

How to repeat:
Create a stored procedure, and comment out the last line (maybe put a 'GROUP BY' in there) so it looks like "# GROUP BY `FieldName`".

Export the database using:
mysqldump --routines --no-data > dump.sql

Reimport the database dump.sql

It will then throw an error like:
 1064 (42000) at line 1646: 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 'SELECT

Suggested fix:
Force MySQLDump to add a CRLF onto the end of every stored procedure during processing, so that the termination isn't commented out.
[25 Jan 2009 15:54] Valeriy Kravchuk
Thank you for the problem report. Please, send a smaple dump that demonstrates the behaviour described.
[25 Jan 2009 16:03] Cal Leeming [Simplicity Media Ltd]
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `testProcedure` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `testProcedure`()
SELECT
    *
FROM
    `mysql`.`user`
#GROUP BY 
#`User` */;;

Notice how the */;; will be commented out due to the lack of CRLF.

If you put that in a dump, then try to execute it, the import will fail. To replicate this fault, you'll need to create the stored proc first with your favourite GUI (I used Navicat), then dump it out using mysqldump.
[25 Jan 2009 18:02] Sveta Smirnova
Thank you for the report.

Verified as described. Version 5.1 and 6.0 are not affected.
[25 Jan 2009 18:03] Sveta Smirnova
Regarding to the last comment I mean 5.1 and 6.0 versions from the development source tree. Wait new release anyway.