When MySQL LOAD DATA Loads 0 Rows: Check Your Line Endings

Nana Zedginidze

— 1 min read
Share

While working on a data migration, I ran into a simple but easy-to-miss issue with MySQL LOAD DATA FROM S3.

The import statement looked correct, but the result was unexpected:

Query OK, 0 rows affected
Records: 0

The script contained:

LINES TERMINATED BY '\r\n'

The problem was not the data itself. It was the line-ending format of the source file.

\r\n vs \n

Text files use special characters to indicate where one line ends and the next begins.

\r\n represents the traditional Windows-style line ending, while \n is the standard Unix/Linux-style line ending.

My source CSV used:

row1\n
row2\n
row3\n

but MySQL had been instructed to look for:

row1\r\n
row2\r\n
row3\r\n

Because the configured row delimiter did not match the actual file format, MySQL could not interpret the records as expected.

The fix was simply:

LINES TERMINATED BY '\n'

After changing the line terminator, the same file loaded successfully

Does this modify the data?

No.

LINES TERMINATED BY does not modify the values stored inside the file. It only tells MySQL how to recognize the end of each record.

For example:

uuid1;INTERNET;123;50.00;din
uuid2;FB;124;20.00;din

With:

FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'

MySQL interprets ; as the separator between columns, while \n identifies the boundary between rows.

Takeaway

When LOAD DATA unexpectedly imports zero rows—or parses a CSV incorrectly—don't only inspect the table definition and column mapping.

Check the file format too.

A tiny difference between:

LINES TERMINATED BY '\r\n'

and:

LINES TERMINATED BY '\n'

can be the difference between 0 imported rows and a successful bulk load.

Read more