MySQL: Enable LOAD DATA LOCAL INFILE

Question

I'm running Mysql 5.5 on Ubuntu 12 LTS. How should I enable LOAD DATA LOCAL INFILE in my.cnf?

I've tried adding local-infile in my config at various places but I'm still getting the "The used command is not allowed with this MySQL version"

Spotter

Answer 1

From the MySQL 5.5 manual page:

LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

You should set the option:

local-infile=1

into your [mysql] entry of my.cnf file or call mysql client with the --local-infile option:

mysql --local-infile -uroot -pyourpwd yourdbname

You can also set the global variable at runtime with this query:

SET GLOBAL local_infile=ON;

You have to be sure that the same parameter is defined into your [mysqld] section too to enable the "local infile" feature server side.

It's a security restriction.

Enchorial

thx. btw, the my.cnf path is /etc/mysql/my.cnf in my machine (AWS EC2). – Andris

Interestingly, the my.cnf file was in the /etc directory for me. – Adena

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. – Ancona

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

The answer is in the right direction. But for those who face this problem here is another gotcha. If you have apparmor enabled on your system and the problem persists then check the syslog to see if the mysqld daemon is being blocked. I had this problem: apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/var/www/myfile.csv" pid=19488 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=106 ouid=33. The solution is very simple. Just update your /etc/apparmor.d/local/usr.sbin.mysqld and reload the apparmor service. – Nagaland

I had to add /var/www/myfile.csv r, entry to /etc/apparmor.d/local/usr.sbin.mysqld. My system is ubuntu 14 LTS . Check apparmor documentation for further info. – Nagaland

If you're using Mac OS this file is in /etc/my.cnf, which you may need to create yourself! – Munition

DO NOT DO THIS lol - this causes MySQL to be unstartable. I added local-infile=1 to /etc/mysql/my.conf and SystemD gives the below error: mysql.service: Control process exited, code=exited status=1 after commenting it back out it starts fine! – Prohibition

Answer 2

The my.cnf file you should edit is the /etc/mysql/my.cnf file. Just:

sudo nano /etc/mysql/my.cnf

Then add:

[mysqld]
local-infile 

[mysql]
local-infile 

The headers [mysqld] and [mysql] are already given, just locate them in the file and add local-infile underneath each of them.

It works for me on MySQL 5.5 on Ubuntu 12.04 LTS.

Langland

This definitely fixed it for me with Ubuntu 12.04. – Scrutineer

I can confirm Ubuntu 14.04.2 LTS not working as well. – Gwinn

Works for Mac, too. For Mac user, /mysql/my.cnf does not exist by default. Just create the directory and the file, and paste these lines into the file. – Ectoblast

If you don't want this configuration to get overwritten when mysql gets upgraded, you should put this into /etc/mysql/conf.d/enable-local-infile.cnf Also don't forget to sudo service mysql restart after making the configuration change so that it takes effect. – Smokestack

This is the proper solution. The solution highest upvoted fails to mention you have to put [mysqld] in first before the line local-infile. – Prohibition

Answer 3

I solved this problem on MySQL 8.0.11 with the mysql terminal command:

SET GLOBAL local_infile = true;

I mean I logged in first with the usual:

mysql -u user -p*

After that you can see the status with the command:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

It should be ON. I will not be writing about security issued with loading local files into database here.

Consols

I tried many other solutions, but only the command SET GLOBAL local_infile = true; works. My MySQL version is 8.0.12. Maybe this is the latest solution. Thanks very much. – Barrator

Unfortunately the variable is OFF again after restarting the server. – Heraclitean

Mohit, somehow I solved it, because now the global variable 'local_infile' is always ON, even after reboot. And it seems I did not use my.cnf file for this. Could you, please, try to log in into PhpMyAdmin, latest version, as root, go to the Variables section, search for 'local_infile', then click Edit, and change it to ON, and finally hit Enter. Did it work? – Consols

Only this answer worked for me on 8.0.12 MySQL Community Server on Windows. – Hillhouse

I have MySQL 8.0.16 installed on Windows server 2016, I executed the SET GLOBAL local_infile = true; command but I am still getting the same error ERROR 1148 (42000): The used command is not allowed with this MySQL version but connection to mysql with --load-infile=1 worked mysql --local-infile=1 -u root -p – Erwin

Answer 4

Replace the driver php5-mysql by the native driver

On debian

apt-get install php5-mysqlnd

Margotmargrave

This worked! Using stock aws 12.04 ubuntu, I needed 'local' as I am connecting to RDS. Thanks a million! – Allodium

Thanks a lot, this solution worked for me. I have asked the question #36526602 – Forceps

No success ubuntu 12.04 – Auk

another happy customer! ubuntu 14.04 mysql 5.7 – Jehovah

Answer 5

in case your flavor of mysql on ubuntu does NOT under any circumstances work and you still get the 1148 error, you can run the load data infile command via command line

open a terminal window

run mysql -u YOURUSERNAME -p --local-infile YOURDBNAME

you will be requested to insert mysqluser password

you will be running MySQLMonitor and your command prompt will be mysql>

run your load data infile command (dont forget to end with a semicolon ; )

like this:

load data local infile '/home/tony/Desktop/2013Mini.csv' into table Reading_Table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Voltcoulomb

Answer 6

See below image...

I've added --local-infile=1 to normal mysql command mysql -u root -p

So total line would be :

mysql --local-infile=1 -u root -p

Bondstone

This worked for me when SET GLOBAL .. did not. Does a 'source' command (\. file.sql) somehow start a new session or reset this? I did not try putting the setting in mmy sql script though. – Rendezvous

Yes this works for me. However it didn't insert my values. – Frumentaceous

Answer 7

Also, for other readers, if you are trying to do this in Django AND your server allows local_infile (you can check by typing SHOW VARIABLES via a mysql client) then you can add this to your settings.py file (since python MySQLdb doesn't by default read the .my.cnf file):

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydb',
        'USER': 'myname',
        'PASSWORD': 'mypass',
        'HOST': 'myserver',
        'PORT': '3306',
        'OPTIONS' : {
            'local_infile':1,
        },
    }
}

Ruel

Where did you find 'OPTIONS' : { 'local_infile':1, }, will work ? – Vookles

Answer 8

Add local_infile in both mysql and mysqld section.

[mysql]
local_infile=1
...

[mysqld]
local_infile=1
...

Tested in MySQL 8.x both in Windows and Linux.

Boscage

Answer 9

I know this is not exactly what the OP is asking, but as this thread is quite old and none of the solutions proposed here worked for me, I decided to share this.

If someone is having trouble enabling local_infile in the version 8 of MySql, this command here did the trick for me:

SET PERSIST local_infile = 1;

It persists the configuration on the "mysqld-auto.cnf" config file and then the change will be remembered after service or server restart.

Ebert

Worked for me, and persists - but cannot find "mysqld-auto.cnf" in Version 8.0.26. "auto.cnf" exists but doesn't contain the setting. – Password

@Password On Windows, the file is created by default in "C:\ProgramData\MySQL\MySQL Server 8.0\Data". – Ebert

Answer 10

You have to take care how you establish your mysqli connection. Full credit for this solution goes to Jorge Albarenque, source

In order to fix it I had to:

• Add local-infile=1 to the [mysqld] and [mysql] sections of my.cnf (as explained in the comments above)
• Use mysqli_real_connect function (PHP documentation).

The catch is that with that function you can explicitly enable the support for LOAD DATA LOCAL INFILE. For example (procedural style):

$link = mysqli_init();
mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($link, $host, $username, $password, $database);

or object oriented

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true);
$mysqli->real_connect($host, $username, $password, $database);

Mikol

@Mask Cannot confirm. My solution is running on 5.5.46 (Ubuntu) – Mikol

Answer 11

if your csv file located same with db, you need to remove LOCAL in LOAD DATA INFILE, or you will get the error

The used command is not allowed with this MySQL version

Stench

This did it for me, but then I also had to follow Nelson's answer here – Phallus

Answer 12

In case if Mysql 5.7 you can use "show global variables like "local_infile" ;" which will give the local infile status ,You can turn it on using "set global local_infile=ON ; ".

Ramify

Answer 13

Another way is to use the mysqlimport client program.

You invoke it as follows:

mysqlimport -uTheUsername -pThePassword --local yourDatabaseName tableName.txt

This generates a LOAD DATA statement which loads tableName.txt into the tableName table.

Keep in mind the following:

mysqlimport determines the table name from the file you provide; using all text from the start of the file name up to the first period as the table name. So, if you wish to load several files to the same table you could distinguish them like tableName.1.txt, tableName.2.txt,..., etc, for example.

Dynamite

Answer 14

This went a little weird for me, from one day to the next one the script that have been working since days just stop working. There wasn´t a newer version of mysql or any kind of upgrade but I was getting the same error, so I give a last try to the CSV file and notice that the end of lines were using \n instead of the expected ( per my script ) \r\n so I save it with the right EOL and run the script again without any trouble.

I think is kind of odd for mysql to tell me The used command is not allowed with this MySQL version since the reason was completely different.

My working command looks like this:

LOAD DATA LOCAL INFILE 'file-name' IGNORE INTO TABLE table-name CHARACTER SET latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES.

Buonaparte

Answer 15

I used below method, which doesn't require any change in config, tested on mysql-5.5.51-winx64 and 5.5.50-MariaDB:

put 'load data...' in .sql file (ex: LoadTableName.sql)

LOAD DATA INFILE 'D:\\Work\\TableRecords.csv' INTO TABLE tbl1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (col1,col2);

then:

mysql -uroot -pStr0ngP@ss -Ddatabasename -e "source D:\Work\LoadTableName.sql"

Scimitar

Answer 16

I am using xampp v3.2.4 and mysql server 8.0.20.

I added local-infile=1 to [mysql] and [mysqld] in the file "my.ini". The file is located at "C:\xampp\mysql\bin\my.ini".

Then I inserted the data from csv file using the following code LOAD DATA INFILE .... It is important to move LOCAL. Otherwise it won't work.

Thanks for all suggestions above. A combination finally worked out for me.

Udder

Answer 17

Please note that for newer MySQL servers, like version 8.0, this setting is not a boolean value, but ON|OFF as you can read here in the docs: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

If you set it to 1 MySQL will not recognize it and will warn this error on your logfiles:

# /var/log/mysql/mysql.err
2022-04-08T14:45:06.532132Z 0 [Warning] [MY-000076] [Server] option 'local_infile': boolean value '1;' was not recognized. Set to OFF.

Ballast

Answer 18

Ok, something odd is happening here. To make this work, do NOT need to make any configuration changes in /etc/mysql/my.cnf . All you need to do is to restart the current mysql service in terminal:

sudo service mysql restart

Then if I want to "recreate" the bug, I simply restart the apache service:

sudo service apache2 restart

Which can then be fixed again by entering the following command:

sudo service mysql restart

So, it appears that the apache2 is doing something to not allow this feature when it starts up (which is then reversed/corrected if restart the mysql service).

Valid in Debian based distributions.

service mysqld restart
service httpd restart

Valid in RedHat based distributions

Siam

Answer 19

For those of you looking for answers to make LOAD DATA LOCAL INFILE work like me, this might probably work. Well it worked for me, so here it goes. Install percona as your mysql server and client by following the steps from the link. A password will be prompted for during the installation, so provide one that you'll remember and use it later. One the installation is done, reboot your system and test if the server is up and running by going to the terminal and typing mysql -u root -p and then the password. Try running the command LOAD DATA LOCAL INFILE now.. Hope it works :)

BTW I was working on Rails 2.3 with Ruby 1.9.3 on Ubuntu 12.04.

Mucin

Answer 20

All: Evidently this is working as designed. Please see new ref man dated 2019-7-23, Section 6.1.6, Security Issues with LOAD DATA LOCAL.

Foin

Answer 21

I think that although my answer will be about working on a different software configuration (MySQL 8.0.31, Windows 11, MySQL 8.0 Command Line Client), but it will apply to different versions of MySQL based on different operating systems and on different client programs. It will also be more up-to-date. According to MySQL Security Considerations for LOAD DATA LOCAL and Loading Data into a Table documentation, it may happen that the server and client can be installed on different hosts. This topic has been partially outlined at this link How to use LOAD DATA INFILE statement when file is another location? . Therefore, the configuration for LOAD DATA LOCAL INFILE to work properly must apply separately to the client and the server.

For my client I check if it is enabled local_infile runing:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

If local_infile is set to OFF, run:

SET GLOBAL local_infile = 'ON';

Then I check again if the ON setting is enabled. This setting works right away and will also work after the client restarts. It is worth to say that I do not enter

SET GLOBAL local_infile = 1;

just type

SET GLOBAL local_infile = 'ON';

This is because in this version of the software this option is Boolean, not numeric .

For the server, you need to change the settings in the configuration file. You can do it in the file my.cnf, or my.ini (in my case). The order in which configuration files are read and overwriting settings and other information can be found in the relevant documentation Using Option Files . In the my.ini file on my MySQL server I changed: in the headers [client], [mysql] under the CLIENT section & [mysqld] under the SERVER section I added entries

local_infile=ON

After all, the settings will work after the restart of the server. More information can also be found at the link Enabling LOAD DATA LOCAL INFILE in mysql .

For me, only this configuration worked correctly when I typed (in Windows):

LOAD DATA LOCAL INFILE 'D:\\mysqldirforloadfiles\\pets.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

I think other answers might also work on a different software. Finally, please consider that configuring the software this way is risky as there are two potential security issues, as detailed in the documentation Security Considerations for LOAD DATA LOCAL.

Rocco