ERROR 1148: The used command is not allowed with this MySQL version
Solution 1
You can specify that as an additional option when setting up your client connection:
mysql -u myuser -p --local-infile somedatabase
This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it.
Both client and server should enable the local-file option. Otherwise it doesn't work.To enable it for files on the server side server add following to the my.cnf
configuration file:
loose-local-infile = 1
Solution 2
I find the answer here.
It's because the server variable local_infile
is set to FALSE|0. Refer from the document.
You can verify by executing:
SHOW VARIABLES LIKE 'local_infile';
If you have SUPER privilege you can enable it (without restarting server with a new configuration) by executing:
SET GLOBAL local_infile = 1;
Solution 3
SpringBoot 2.1 with Default MySQL connector
To Solve this please follow the instructions
1. SET GLOBAL local_infile = 1;
to set this global variable please follow the instruction provided in MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html
2. Change the MySQL Connector String
allowLoadLocalInfile=true Example : jdbc:mysql://localhost:3306/xxxx?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowLoadLocalInfile=true
Solution 4
http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
Put this in my.cnf - the [client]
section should already be there
(if you're not too concerned about security).
[client]
loose-local-infile=1
Solution 5
All of this didn't solve it for me on my brand new Ubuntu 15.04.
I removed the LOCAL
and got this command:
LOAD DATA
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';
but then I got:
MySQL said: File 'A.txt' not found (Errcode: 13 - Permission denied)
That led me to this answer from Nelson to another question on stackoverflow which solved the issue for me!
sosytee
Updated on June 04, 2020Comments
-
sosytee about 4 years
I am trying to load data into mysql database using
LOAD DATA LOCAL INFILE A.txt INTO DB LINES TERMINATED BY '|';
the topic of this question is the response I get. I understand the local data offloading is off by default and I have to enable it using a the command
local-infile=1
but I do not know where to place this command. -
Teja over 9 yearsERROR 1045 (28000): Access denied for user ''@'localhost' (using password: NO)
-
Teja over 9 yearsThe error is as above arkascha,.. I am trying to write a small file into mysql table using LOAD INFILE command.
-
arkascha over 9 years@SOaddict Hm, looks like a plain authentication failure to me? You try to connect without password? Can you connect the same way to an interactive session, so without loading any data from a file? But anyways: you are highjacking someone elses question / answer! Please ask your question separately. You certainly will get lots of help!
-
OrwellHindenberg almost 9 yearsAny ideas on where to go if this still fails. I've added local-infile=1 to my.cnf file under [client], [mysqld], and [mysql] and used combinations of these. All with the same results. I've tried re-starting workbench but no luck. I've stopped and started mySQL workbench between each change as well.
-
OrwellHindenberg almost 9 yearsan update of mySQL to 6.2.5 solved this problem for me
-
Vérace almost 9 years@OrwellHindenberg - did you use loose-local-infile=1 - you have just local-infile.
-
OrwellHindenberg almost 9 yearsI tried what was in your original answer, before updates. I don't remember using "loose". However, updating mySQL worked for me.
-
Vérace almost 9 years@OrwellHindenberg - check the edit. It was always loose-local-infile. The edit merely corrected my (at the time) newbie presentation/dba.se-style errors
-
OrwellHindenberg almost 9 yearsThen I must have used it with loose. I had tried whatever was here first. Those that come to this page should try your solution first and if they still have problems they should try updating.
-
ebyrob about 6 yearsNote: I'm pretty sure this also moves the expected file location from the client machine to the server machine. (If they're the same machine, you may not notice.)
-
user706420 almost 6 years@arkascha "opens a security hole" means? EDIT: dev.mysql.com/doc/refman/8.0/en/load-data-local.html
-
codehelp4 almost 6 yearsAt first I wanted to +1 this because it was the only answer here that successfully set the local-infile variable to ON but that didn't fix the error.
-
SmallChess over 5 yearsThe MySQL server is running with the --secure-file-priv option so it cannot execute this statement
-
Snackoverflow over 5 yearsWorked for me! But I did not change it in the given method. I edited the configuration file my.ini of the server and set it in there
local_infile=1
, and restarted the server service. Also I ran the client with the--local-infile dbname
option, e.g.mysql -u root -p --local-infile mydatabase
. -
EatSleepCode almost 5 yearsThis is a weird case, but I continued to get the error while my local_infine = on/True. This was because I created my table in MySQL Workbench and then tried adding the local csv data through cmd. I fixed it by dropping the table and creating a new one through cmd and also adding the data through cmd.
-
James almost 5 yearsloose-local-infile=1 should go under the [client] section in your my.cnf or my.ini file not the [mysqld] section. You can also put local_infile=1 under the [mysqld] section.
-
arkascha over 4 years@SSMK Help you with what? That other question is answered, we cannot guess your question...
-
brat over 4 yearswhere is this my.cnf file located?
-
arkascha over 4 years@brat Depends on your installed package and the distribution of your system. Typically something like
/etc/mysql/...
. Simply take a look into your software package manager to see the list of file installed with the mysql_server package (or maridb_server). -
brat over 4 years@arkascha thanks. For those who use windows like me, the file has a .ini ending and its found in :C//ProgramData folder, although the file itself says it needs to be moved to the directory of the server to take effect. So, you need to move it there. Dunno why it isnt there in the first place.
-
arkascha over 4 years@brat Oh, I see, did not think of that, sorry. MS windows is a somewhat exotic choice for development these days. But diversity is good, so thanks for pointing that out. File locations are always an issue in that system due to the lack of a real software management system. Great you solved your issue!
-
Stephen Daddona about 4 yearsI just joined this community. This got my data loaded that I exported from Google contacts. Yay!
-
Chandan over 3 yearsI was using workbench and this was my issue. Now solved after setting OPT_LOCAL_INFILE=1
-
Carlos Ferreyra about 3 yearsApparently, this also affects the JDBC driver. I got this working in IntelliJ after setting allowLoadLocalInfile to true to a DB connection.