How I fixed my “data truncated for column” warning in MySQL

Recently, while working on a project, I ran into a warning telling me that my “data was truncated for” one of my columns when I was importing a CSV file into one of my SQL tables.

Concerned that I had done something wrong, I Googled for a solution. Unfortunately, I didn’t find any answers there, so I ended up having to find the source of this warning myself.

What does “data truncated” mean?

Truncated means “cut short”, and “data truncated” warnings or errors refer to a value’s data being cut off at the end during the importing process (e.g. a value of “2.9823” being imported as “2.98”). These errors are important warnings, because it notifies us that our data has not been imported accurately.

Data truncated warnings usually happen because the imported value:

1. Does not match the data type of the column they are being imported into, e.g. inserting “120e” into an INT (i.e. integer) column will cause the data to be inserted as 120, truncating the e at the back.
2. Exceeds the maximum length of the column, e.g. a string “abcdef” being imported into a VARCHAR(2) will be truncated into “ab”.

Which was why my problem was so perplexing.

The problem

I wrote an SQL query to load the CSV file on the left into my table on the right (pictured below):

This was the SQL query I used:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id,test);

All my values have been imported correctly to the table, so what exactly is being truncated?

After some research and asking around, I found the answer: the \r character at the end of “11” was being truncated.

---

Article continues after the advertisement:

---

The \r character

The \r character is part of the \r\n series of characters, used in Windows to denote a newline character. In all other operating systems, newlines are denoted using \n, but because the CSV file was generated in Windows, it uses \r\n for newlines instead.

Why \r was being read into the database

The \r character was being read into the database because of this line in my query:

LOAD DATA INFILE '/path/to/test.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id,test);

The first row in my data has the following characters:

1;11\r\n

Which was split into 1 and 11\r. As \r is not considered an integer, it cannot be entered into the test column, which is defined as an INTEGER. Hence, it got truncated.

To fix this warning, all I had to do was to change the problematic line in my SQL query:

LINES TERMINATED BY '\r\n'

And I would be fine even if I didn’t, because this is one of those rare cases where the data truncated warning is harmless!

Conclusion

In conclusion, this is an exploration of an interesting warning I had found while trying to import CSV files into a MySQL table. Remember to always be careful and check your queries before you start importing!

Leave a comment below if the article helped you!

---

Article continues after the advertisement:

---