Why is my data being truncated? Warning | 1265 | Data truncated for column

Question

I tried adding NULL to both date_of_birth and date_of_death, but that didn't help. Why is data being truncated? (especially because there isn't any data at the moment...)

Also, I don't understand warning 1261 at all. Row 5 is no different from rows 1-4.

Thanks!

The following script:

-- create the people table
DROP TABLE IF EXISTS people;

CREATE TABLE people (
full_name varchar (50) NOT NULL,
people_ID varchar (15) NOT NULL,
date_of_birth DATE,
date_of_death DATE,
PRIMARY KEY(people_ID))
ENGINE = INNODB;

LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  

Gives me these errors:

Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 10

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 5 |
| Warning | 1261 | Row 5 doesn't contain data for all columns         |
+---------+------+----------------------------------------------------+
10 rows in set (0.00 sec)

mysql> select * from people;
+-------------------+-----------+---------------+---------------+
| full_name         | people_ID | date_of_birth | date_of_death |
+-------------------+-----------+---------------+---------------+
| Harry Dunham      | H_Dunham  | 0000-00-00    | NULL          |
| Julien Bryan      | J_Bryan   | 0000-00-00    | 0000-00-00    |
| Jules V.D. Bucher | J_Bucher  | 0000-00-00    | 0000-00-00    |
| Miriam Bucher     | M_Bucher  | 0000-00-00    | 0000-00-00    |
| Paul Bowles       | P_Bowles  | 0000-00-00    | 0000-00-00    |
+-------------------+-----------+---------------+---------------+
5 rows in set (0.01 sec)

Here is my .csv:

FullName,People_ID,DOB,DOD
Jules V.D. Bucher,J_Bucher,,
Miriam Bucher,M_Bucher,,
Julien Bryan,J_Bryan,,
Paul Bowles,P_Bowles,,
Harry Dunham,H_Dunham,,

Solution 1

Perhaps what's happening is that Mysql is trying to force the empty string "" into your date column, but the column only accepts DATE formatted data so it's converted into the proper format. Try adjusting your CSV so one of the rows has "0000-00-00" in the date columns instead of just "" (blank), and see if you get fewer warnings.

The 1261 warning looks like more of same -- probably harmless but annoying quirks in how Mysql handles CSV import -- and I wouldn't worry about it unless you see evidence that your imported data aren't complete.

Solution 2

Putting it all together, I have divined that your CSV file contains DOS-style line endings (\r\n) whereas you have instructed MySQL to interpret the file using UNIX-style line endings (\n).

Those \r characters are thus treated as data, with \r being "truncated" to nothingness during the string-to-DATE conversion, and with the final row indeed containing no data whatsoever for that final column (because you did not write an empty line at the end of the CSV file). That's how the final row is different.

You can actually fix this by using a more appropriate LINES TERMINATED BY directive:

LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

MySQL will then allow the \r\n sequences to be treated as a line ending.

You have another problem, which is that you're telling MySQL that your fields are enclosed by ", whereas this is not at all the case. You could remove the ENCLOSED BY directive, or precede it with OPTIONALLY:

If the input values are not necessarily enclosed within quotation marks, use OPTIONALLY before the ENCLOSED BY keywords.

You should read the documentation for the technologies that you use.