Why isn't MySQL inserting rows from CSV file? I've been using LOAD DATA INFILE wrong?

Question

I have a CSV file containing rows just like the following one:

2,1,abc123,1,2,"Hello World"
2,1,abc123,1,2,"Hello World2"
2,1,abc123,1,2,"Hello World3"

I'm running the following query:

LOAD DATA LOCAL INFILE :path INTO TABLE errors
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(import_id, type, code, row, cell, message);

It does not insert any of my rows into the database. Here's the structure for the errors table:

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| import_id | int(10) unsigned | NO   | MUL | NULL    |                |
| type      | int(10) unsigned | NO   |     | NULL    |                |
| code      | varchar(128)     | YES  |     | NULL    |                |
| row       | int(10) unsigned | YES  |     | NULL    |                |
| cell      | varchar(32)      | YES  |     | NULL    |                |
| message   | varchar(128)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

I've noticed that if I change the order of the columns, it works.

For example, in my CSV file

1,abc123,1,2,"Hello World",2
1,abc123,1,2,"Hello World2",2
1,abc123,1,2,"Hello World3",2

Also changed the query:

LOAD DATA LOCAL INFILE :path INTO TABLE errors
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(type, code, row, cell, message, import_id);

Why is it working with a different order for columns?

@Alexandru You're sure? You added this GRANT specifically? Please explain in detail your environment and provide this sort of information. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Oct 16, 2018 at 9:35

SadmirD · Accepted Answer · 2018-10-12 15:18:48Z

1

Please verify the version of your mysql and whether this option (load data infile local) is enabled by your tool that you are using, along with it being enabled on server side. It is considered a security risk and is disabled by default by newer versions of mysql server.

Here is more info on security issues about utilizing load infile local MYSQL OFFICIAL DOCS

Also pay attention that you probably need to have local_infile=1 and you can check it with the following command:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

To enable it, use the following command:

SET GLOBAL local_infile = 1;

Also verify that the lines are terminated by '\n' and not by '\r\n' (this is for windows environment)

Hope that helps!

edited Oct 12, 2018 at 15:18

answered Oct 12, 2018 at 15:12

SadmirD

6724 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexandru Over a year ago

local_infile variable is turned on. The query works if I switch the order of the columns.

SadmirD Over a year ago

Please verify if "switching columns" played any role in changing your 'newline' ( '\n' vs '\r\n'). You can verify this if you switch the columns again, after you confirm that it's working (not by 'undoing' changes!)

Collectives™ on Stack Overflow

Why isn't MySQL inserting rows from CSV file? I've been using LOAD DATA INFILE wrong?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related