1

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
4
  • 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.
    – Akina
    Sep 26, 2019 at 5:11
  • 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. Sep 26, 2019 at 18:19
  • Is Raspberry Pi involved?
    – Rick James
    Oct 6, 2019 at 19:23
  • Yes I am using a raspberry pi 3. Oct 8, 2019 at 12:23

1 Answer 1

0

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';
5
  • 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 Sep 27, 2019 at 21:53
  • 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. Sep 27, 2019 at 22:08
  • 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 Sep 28, 2019 at 0:48
  • 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. Sep 28, 2019 at 9:55
  • Thank you for accepting my answer. That’s why I gave both client side and server side. Once you did both, things worked. Sep 28, 2019 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.