ERROR 1148 The used command is not allowed with this MySQL version

Question

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

User · Answer

If you are using Java8 or   version  JDBC and MySql8 and facing this issue then try this   Add parameter to connection string   jdbc mysql   localhost 3306 tempDB allowLoadLocalInfile true   Also  set      local infile   1   in my cnf file    The latest version of mysql-java-connector might be wont allow directly to connect to local file  So with this parameter  you can able to enable it  This works for me

User · Answer

The top answers are correct  Please check them direct in MySQL CLI first  If this fixes the problem there  you may want to have it working in Python3 just pass it to the MySQLdb connectas parameter  self connection   MySQLdb connect                      host host  user settings DB db config  USER                        port port  passwd settings DB db config  PASSWORD                         db settings DB db config  NAME                        local infile True

User · Answer

I had the same issue while importing the CSV file in AWS MySQL 8 0  RDS      mysql   LOAD DATA LOCAL INFILE   tmp your csv  INTO TABLE test demo2 FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n  IGNORE 1 LINES       ERROR 1148  42000   The used command is not allowed with this MySQL version   1  Check for local infile parameter  mysql gt  SHOW VARIABLES LIKE  local infile     2  Log out from client and re-login using below parameter   mysql -u root -p -h rdsendpoint --local-infile 1   3  Run the same command  mysql gt  LOAD DATA LOCAL INFILE   tmp your csv  INTO TABLE test demo2 FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n  IGNORE 1 LINES    Query OK  300 rows affected  0 01 sec   Records  300  Deleted  0  Skipped  0  Warnings  0

User · Answer

Refer to MySQL 8 0 Reference Manual -- 6 1 6 Security Issues with LOAD DATA LOCAL  On the server side  run  mysql server start --local-infile   On the client side  run  mysql --local-infile database name -u username -p

User · Answer

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

User · Answer

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

User · Answer

Just little addition   Found another reincarnation in mysql connector ver 8 0 16 It now requires allow local infile True or you will see the above error  Worked in prior versions   conn   mysql connector connect host host  user user  passwd passwd  database database  allow local infile True

User · Answer

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 amp useUnicode yes amp characterEncoding UTF-8 amp allowLoadLocalInfile true

User · Answer

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

User · Answer

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

User · Answer

Also struggled with this issue  trying to upload  csv data into AWS RDS instance from my local machine using MySQL Workbench on Windows   The addition I needed was adding OPT LOCAL INFILE 1 in  Connection   Advanced   Others  Note CAPS was required   I found this answer by PeterMag in AWS Developer Forums     For further info   SHOW VARIABLES LIKE  local infile   already returned ON and the query was   LOAD DATA LOCAL INFILE  filepath file csv       INTO TABLE  table name      FIELDS TERMINATED BY          ENCLOSED BY         LINES TERMINATED BY   n      IGNORE 1 ROWS    Copying from the answer source referenced above      Apparently this is a bug in MYSQL Workbench V8 X  In addition to the   configurations shown earlier in this thread  you also need to change   the MYSQL Connection in Workbench as follows          Go to the Welcome page of MYSQL which displays all your connections   Select Manage Server Connections  the little spanner icon    Select your connection   Select Advanced tab   In the Others box  add OPT LOCAL INFILE 1         Now I can use the LOAD DATA LOCAL INFILE query on MYSQL RDS  It seems   that the File priv permission is not required

User · Answer

I got this error while loading data when using docker 1   The solution worked after I followed these next steps  Initially  I created the database and table datavault and fdata  When I tried to import the data 2   I got the error 3   Then I did    SET GLOBAL local infile   1  Confirm using SHOW VARIABLES LIKE  local infile   Then I restarted my mysql session  mysql -P 3306 -u required --local-infile 1 -p  see  4  for user creation  I recreated my table as this solved my problem     use datavault   drop table fdata  CREATE TABLE fdata  fID INT  NAME VARCHAR 64   LASTNAME VARCHAR 64   EMAIL VARCHAR 128   GENDER VARCHAR 12   IPADDRESS VARCHAR 40     Finally I imported the data using  2      For completeness  I would add I was running the mysql version inside the container via docker exec -it testdb sh  The mysql version was mysql  Ver 8 0 17 for Linux on x86 64  MySQL Community Server - GPL   This was also tested with mysql exe  Ver 14 14 Distrib 5 7 14  for Win64  x86 64  which was another version of mysql from WAMP64  The associated commands used are listed in  5       1  docker run --name testdb -v   c Users C Downloads data csv-data   var data -p 3306 -e MYSQL ROOT PASSWORD password -d mysql latest   2  load data local infile   var data mockdata csv  into table fdata fields terminated by     enclosed by    lines terminated by   n  IGNORE 1 ROWS    3  ERROR 1148  42000   The used command is not allowed with this MySQL version   4  The required client was created using    CREATE USER  required      IDENTIFIED BY  password   GRANT ALL PRIVILEGES ON       TO  required       FLUSH PRIVILEGES  You might need this line ALTER USER  required      IDENTIFIED WITH mysql native password BY  password   if you run into this error  Authentication plugin    caching sha2 password    cannot be loaded    5  Commands using mysql from WAMP64    mysql -urequired -ppassword -P 32775 -h 192 168 99 100 --local-infile 1 where the port is thee mapped port into the host as described by docker ps -a and the host ip was optained using docker-machine ip  This depends on OS and possibly Docker version   Create database datavault2 and table fdata as described above load data local infile  c  Users C Downloads data csv-data mockdata csv  into table fdata fields terminated by     enclosed by    lines terminated by   n   For my record  this other alternative to load the file worked after I have previously created datavault3 and fdata  mysql -urequired -ppassword -P 32775 -h 192 168 99 100 --local-infile datavault3 -e  LOAD DATA LOCAL INFILE  c  Users C Downloads data csv-data mockdata csv   REPLACE INTO TABLE fdata  FIELDS TERMINATED BY     LINES TERMINATED BY   n  IGNORE 1 ROWS  and it successfully loaded the data easily checked after running select   from fdata limit 10

[mysql] ERROR 1148: The used command is not allowed with this MySQL version

Examples related to mysql

Examples related to sql

Examples related to import