CODEWITHSUNDEEP
mysqlmysql-error-1064
MEKI
MEKI

Reputation: 11

Error 1148 (42000) using LOAD DATA LOCAL INFILE in MySQL

I'm trying to load data into a MySQL table using LOAD DATA LOCAL INFILE using:

LOAD DATA LOCAL INFILE 'f.csv' INTO TABLE tableName
            FIELDS TERMINATED BY ';' 
            LINES TERMINATED BY '\\r\\n' 
            IGNORE 1 LINES
            (C1, C2, C3, C4)
            SET cmj = REPLACE(@cmj,',','.');

OS : Ubuntu 14.04

MySQL : 5.7

In file my.cnf I have this config:

[mysqld]
local-infile=1
[mysql]
local-infile=1
[client]
loose-local-infile=1
local-infile=1

I'm getting the following error:

Message: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version, query was: LOAD DATA LOCAL INFILE 'f.csv' INTO TABLE tableName FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (C1, C2, C3, C4)

I think the problem is related to new line in MySQL

Thanks for your help

EDIT

In my file.csv I have two lines (header + values). If I update my request "IGNORE 1 LINES" to "IGNORE 0 LINES" the first line (header) is inserted.

All lines are considered as one line!!!!!

Upvotes: 1

Views: 6140

Answers (2)

Quaternion
Quaternion

Reputation: 486

According to the MySQL reference manual v5.7, the error you received can result from LOCAL file capability being disabled either on the server or client side.

Running mysql with the --local-infile option will enable LOCAL capability on the client side, but this won't help if it's disabled on the server, where it's controlled by the system variable local_infile. The easiest way to fix this is to reset the system variable during runtime with the SET command:

SET GLOBAL local_infile = 1;

Note: In later versions of MySQL you can make the variable change persist over server restarts with the command

SET PERSIST local_infile = 1;

Upvotes: 3

Faizan Younus
Faizan Younus

Reputation: 803

use --local-infile while connecting database.

mysql -hhostname -uusername -p --local-infile  test -A

Hope this helps.

Upvotes: 3

Related Questions