MySQL Bugs: #41206: mysqldump always replace LF in a body of routine with CRLF

Bug #41206	mysqldump always replace LF in a body of routine with CRLF - leading CRCRLFs
Submitted:	3 Dec 2008 17:12	Modified:	12 Dec 2008 7:53
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.72, 5.1.30	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump CRLF

Description:
mysql client always use LF only in routine bodies, whereas MySQL Query Browser in Windows platform use CRLF.

mysqldump utility always replace LF with CRLF in windows platform, even the char preceeding LF is CR.

Resulting CRCRLF sequence in a dump file cause some troubles in certain editors.

One example is MySQL Query Browser script editor itself. It interprets CRCRLF as two line breaks. Hence creating routine body which has empty line followed by an actual line.

How to repeat:
Run the following script in MySQL Query Browser on Windows platform.

CREATE DATABASE testmysqldump;
USE testmysqldump;
DELIMITER ;;
CREATE PROCEDURE testsp()
BEGIN
  -- this is a comment
END ;;
DELIMITER ;

Then from the command prompt :
mysqldump -u root -p --routines --databases testmysqldump > test.sql

Observe CRCRLF (0x0d 0x0d 0x0a) sequences in the dump file.

In MySQL Query Browser,
- DROP DATABASE testmysqldump;
- run the dump file
- edit testmysqldump.testsp()
- observe the phantom empty lines

Suggested fix:
a. mysqldump solution : 
  While dumping routine body, first clear all CR occurance in the memory buffer.

b. mysqld Solution : 
  Always ignore CR, hence store CRLF as LF in routine bodies.

/* category edit */

Verified just as described (one should add -R to the command line to dump routines, though). mysqldump client indeed produces dump with 0D0D0A character sequences that is incorrect.

/* edit title typo */

It was pointed by my colleagues that this may be even not a bug. There is no good way to prevent this while redirecting output on Windows, so --result-file option should be used as documented at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_result-file.