Resolving MySQL LOAD DATA Errors: Handling Date Truncation, Quotes, and Line Terminators

A colleague encountered a MySQL LOAD DATA error when importing a CSV file, receiving warnings such as Data truncated for column 'c1' at row 1 . The article reproduces the issue with a simplified CSV containing two datetime columns and one varchar column.

The target table t is defined as:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| c1    | datetime    | YES  |     | NULL    |                |
| c2    | varchar(10) | YES  |     | NULL    |                |
| c3    | datetime    | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

The initial import command was:

load data local infile '/home/mysql/online.csv' 
into table test 
fields terminated by ',' lines terminated by '\n' 
(c1, c2, c3) 
set c1=date_format(@c1,'%Y-%m-%d %H:%i:%s'), 
    c3=date_format(@c3,'%Y-%m-%d %H:%i:%s');

This produced warning 1265 (data truncated) because the datetime values were read as strings with surrounding double quotes. Additional errors such as ERROR 3948 (42000): Loading local data is disabled and ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected were also observed.

To enable local file loading, the global variable local_infile must be set to ON and the client started with --local-infile=1 :

set global local_infile=1;
mysql -ubisal -pbisal --local-infile=1

Subsequent attempts introduced the enclosed by '"' clause to strip the double quotes, and the column list was changed to use user variables ( @c1, c2, @c3 ) so that the values could be transformed in the SET clause.

Because the CSV lines ended with Windows carriage returns, the lines terminated by '\r\n' option was added. The transformation function was also switched from date_format (which formats a datetime) to str_to_date (which parses a string into a datetime).

load data local infile '/home/mysql/online.csv' 
into table t 
fields terminated by ',' enclosed by '"' lines terminated by '\r\n' 
(@c1, c2, @c3) 
set c1=str_to_date(@c1,'%Y-%m-%d %H:%i:%s'), 
    c3=str_to_date(@c3,'%Y-%m-%d %H:%i:%s');

With these adjustments, the import succeeded without warnings, and all five rows were correctly stored.

The article concludes with a checklist of concepts covered: enabling local_infile , meaning of terminated by , enclosed by , and lines terminated by , use of @‑variables and the SET clause, differences between date_format and str_to_date , and how to interpret MySQL warning messages during data import.