MySQL LOAD DATA LOCAL INFILE only imports a single row

You say that the ID field has the AUTO_INCREMENT attribute, but there's no mention of it in the CREATE TABLE statement. This is part of the problem.

The other part is those truncation warnings. Some of the rows in the CSV file probably contain data that is too long to fit inside the columns. Increase the size of those text columns to a bigger value (let's say 200) and try again.

Are you absolutely sure that the CSV file is valid ? (a.k.a. each row has the same number of values etc.). You should probably check if those strings contain commas (,), although that shouldn't be an issue.

Why is MySQL LOAD DATA INFILE command only loading one row?

What is happening is you load the first column fine, then when you load the second and it fails because is violates a unique index constraint.

MySQL LOAD DATA LOCAL INFILE will enforce your unique indexes on columns and then skip any offending duplicate rows without doing anything about it, not even logging a warning!

How to reproduce this phenemenon:

1. Create a table with an int and varchar column:

   mysql> create table foo(id INT, mytext VARCHAR(255));
   Query OK, 0 rows affected (0.02 sec)
   

2. Add a unique constraint on the varchar column:

   mysql> alter table foo add constraint my_epic_constraint unique(mytext);
   Query OK, 0 rows affected (0.02 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   

3. Create your input text file /tmp/foo.txt, delimited by tabs, which violates the unique constraint:

   1   fred
   2   fred
   

4. Try importing it:

   mysql> load data local infile '/tmp/foo.txt' into table foo fields 
          terminated by '\t' lines terminated by '\n' (@col1,@col2) 
          set id=@col1, mytext=@col2;
   
   Query OK, 1 row affected (0.01 sec)
   Records: 2  Deleted: 0  Skipped: 1  Warnings: 0
   

BAM! There's your problem: why is it only importing only one row? Because you have a unique key constraint on it which the lines in the data file violated.

Solutions:

1. Remove the unique key constraint on your table and try again.

2. Go into the text file and remove the duplicate rows that violate the unique constraint.

I had the same problem and I solved using doble quotes "\r\n" instead '\r\n'