MySQL load data infile ERROR 1064

MySQLmysql-5.5syntax

I am using MySQL version 5.5 and I am trying to use load data infile and I am getting errors. I've looked at the documentation and it looks like I am doing exactly what the docs specify.

Running this command in mysql 

LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt' 
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error.

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Running the following command 

LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt' 
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TER' at line 1

What is wrong with my syntax?
The data in the file is 

126,126text,moretext
127,127text,moretext

Best Answer

CLIENT SIDE

Before you run this

LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt' 
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

You need to start mysql client program with --local-file

mysql --local-file -uroot -p

If you wish to have this configured permanantly, add this to ~/.my.cnf

[mysql]
local-infile

This forces the mysql client program to execute.

SERVER SIDE

The mysqld (server side must be started with local_infile) in its my.cnf or my.ini.

Make sure to add this

[mysqld]
local_infile=1

and run

service mysql restart

If you cannot config it and restart it, login to mysql and run

mysql> SELECT @@global.local_infile;

or

mysql> SHOW GLOABL VARIABLES LIKE 'local_infile';

If you get 0, you cannot execute LOAD DATA LOCAL INFILE no matter what you do to the client side.

SUPPLEMENTAL INFO

Running the following command

LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt' 
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TER' at line 1

That's just a straight up syntax error.

Get rid of the dash between local and infile so that it reads

LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt' 
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Related Question