Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian 7, but not Debian 7 minimal, though both installations come from the same precompiled deb package. Both on OpenVZ VPS.

How to debug why local-infile=1 does not work for an installation, and how to safely activate LOAD DATA LOCAL INFILE?

share|improve this question
up vote 6 down vote accepted

If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7.

Before taking that kind of time, please run the following:

SET GLOBAL local_infile = 'ON';

It should echo the following:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
| Variable_name | Value |
| local_infile  | OFF   |
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile = 'ON';
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
| Variable_name | Value |
| local_infile  | ON    |
1 row in set (0.00 sec)


If it still says 'OFF', then look deep within the compiler settings to enable it.

If it sets to 'ON', you are OK.

Please note I did not say

SET GLOBAL local_infile = 1;

I said use

SET GLOBAL local_infile = 'ON';

The option local_infile is Boolean, not numeric.

If setting this in my.cnf


and restarting mysql does not work either, you will have to start up mysql with something like this:

echo "SET GLOBAL local_infile = 'ON';" > /var/lib/mysql/init_file.sql
chown mysql:mysql /var/lib/mysql/init_file.sql
service mysql stop
service mysql start --init-file=/var/lib/mysql/init_file.sql
rm -f /var/lib/mysql/init_file.sql

or perhaps adding this to my.cnf


then restart mysql.

Give it a Try !!!

share|improve this answer
Thanks for your practical instruction for debugging, what I really needed. In the first step, it revealed, I am on wrong track, as showed local_infile | ON . It seems the problem is not mysql, but PHP or probably the mysql-PHP connector/driver (mysqli). – All Aug 27 '13 at 5:59
i had to use this in php to make it work with msqli: $con = mysqli_init(); $con->options(MYSQLI_OPT_LOCAL_INFILE, true); – Radu Bogdan Aug 26 '15 at 12:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.