MySQL load data infile ERROR 1064

Question

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

• Akina: Why dows you use LOCAL? does your client works with remote MySQL, and source file is local for client and remote for server really? Check does LOAD FILE LOCAL allowed both on server and on cilent side either by proper settings in INI file or by proper command line options.
• newdeveloper: for security reasons I am trying to use the above command. I could set load local in the ini file but that could lead it issues.
• Rick James: Is Raspberry Pi involved?
• newdeveloper: Yes I am using a raspberry pi 3.

Answer 1

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';

• newdeveloper: The ` @@global.local_infile` is 1. Tried LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';. I am getting the same error ERROR 1148 (42000): The used command is not allowed with this MySQL version. If i use local-infile I get 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
• RolandoMySQLDBA: I mentioned already at the bottom of my post : That's just a straight up syntax error.. Please Get rid of the dash between local and infile.
• newdeveloper: In my original post I did. I just tried again and I get the same error. ERROR 1148 (42000): The used command is not allowed with this MySQL version
• newdeveloper: Not sure but now it is working. I added local-infile=1 to my .my.cnf file restarted mysql but notihng. Added it back to .my.cnf then rebooted the machine and it worked.
• RolandoMySQLDBA: Thank you for accepting my answer. That’s why I gave both client side and server side. Once you did both, things worked.