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

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 2

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 3

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

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 5

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 6

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