MySQL Bugs: #20918: Documentation for LOAD DATA, LINES TERMINATE BY is incorrect

Bug #20918	Documentation for LOAD DATA, LINES TERMINATE BY is incorrect
Submitted:	8 Jul 2006 19:53	Modified:	11 Jul 2006 19:54
Reporter:	Jerry Krinock	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	MacOS (Mac OS 10.4.7)
Assigned to:		CPU Architecture:	Any
Tags:	Apple, LINES TERMINATED BY, LOAD DATA, Mac OS X

Description:
On web page,

http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html

It says:

   -> LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

This is apparently wrong.

How to repeat:
Get yourself an Apple Mac running OS X with Microsoft Office:mac Excel.
Put some data in a spreadsheet.
Export data in tab-return text format.
Verify with a hex editor that, indeed the line endings in this file arre 0x0d, which is usually referred to as '\r".
Upload file from Macintosh to dreamhost.com.  Supposedly they are running My SQL5.0.18 under Linux, but we interact with it through MySQL Monitor.
Log in to MySQL Monitor.
LOAD DATA using the LINES TERMINATED BY '\r' as suggested in the documentation

Expected Result: Should work.
Actual Result: Syntax error at end of first line.

Repeat the LOAD data but this time _omit_ the LINES TERMINATED BY '\r' clause.

Expected Result: Syntax error at end of first line.
Actual Result: Works!

Suggested fix:
I don't know.  The documentation makes sense, but it is apparently wrong.  Beats me!

Depending on the transfer method, the line endings could
easily have gotten changed to '\n'. The report does not verify
that the '\r' line endings were indeed preserved across the
transfer operation, so it's unclear if this was indeed the case.

I just confirmed that Paul is correct; the line endings on the server are 0x0a.  Apparently, Dreamhost saw that it was a .txt file and translated line endings upon uploading

If you use a binary transfer method (for example, "image" mode
in ftp), you should find the '\r' line endings preserved and then
you'll need LINES TERMINATED BY '\r' as you expected.

Your problem is not the result of a bug in MySQL code. I do not think that any more clarifications are needed in the documentation either.