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"

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.

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.

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.

Answer 4

Replace the driver php5-mysql by the native driver

On debian

apt-get install php5-mysqlnd

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

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

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

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.

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.

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

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

Answer 12

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.

Answer 13

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.

Answer 14

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"

Answer 15

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

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.

Answer 17

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

Answer 18

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.

Answer 19

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.

Answer 20

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.