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.

Smarm

Answer 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

Hest

ERROR 1045 (28000): Access denied for user ''@'localhost' (using password: NO) – Valorie

The error is as above arkascha,.. I am trying to write a small file into mysql table using LOAD INFILE command. – Valorie

@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! – Hest

@Hest "opens a security hole" means? EDIT: dev.mysql.com/doc/refman/8.0/en/load-data-local.html – Khelat

loose-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. – Lachesis

@SSMK Help you with what? That other question is answered, we cannot guess your question... – Hest

where is this my.cnf file located? – Volcanology

@Volcanology 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). – Hest

@Hest 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. – Volcanology

@Volcanology 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! – Hest

I just joined this community. This got my data loaded that I exported from Google contacts. Yay! – Overword

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

Gentilesse

At 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. – Mundy

Worked 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. – Heuer

This 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. – Quietude

Answer 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

Settler

Simply updating the JDBC url fixed the issue for us, thank you! – Grammalogue

Answer 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

Federative

Any 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. – Nabalas

an update of mySQL to 6.2.5 solved this problem for me – Nabalas

@Nabalas - did you use loose-local-infile=1 - you have just local-infile. – Macleod

I tried what was in your original answer, before updates. I don't remember using "loose". However, updating mySQL worked for me. – Nabalas

@Nabalas - check the edit. It was always loose-local-infile. The edit merely corrected my (at the time) newbie presentation/dba.se-style errors – Macleod

Then 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. – Nabalas

Answer 5

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:

1. Go to the Welcome page of MYSQL which displays all your connections
2. Select Manage Server Connections (the little spanner icon)
3. Select your connection
4. Select Advanced tab
5. 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.*

Brumley

I was using workbench and this was my issue. Now solved after setting OPT_LOCAL_INFILE=1 – Sapor

Apparently, this also affects the JDBC driver. I got this working in IntelliJ after setting allowLoadLocalInfile to true to a DB connection. – Agitprop

Answer 6

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!

Pisces

Note: 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.) – Kalahari

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement – Indolent

Answer 7

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

Caceres

Answer 8

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)

Poniard

Answer 9

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.

Fritzfritze

Answer 10

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)

Charge

Answer 11

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

Colby

Answer 12

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

Phocomelia

Answer 13

I am faced same issue on MySQL Workbench Version 8.031 on Windows 10 pro machine,

And I changed the Edit Connection -->Advanced Go below Edit others

Add below line :

OPT_LOCAL_INFILE=1

And try run the query.

Alluring