Take the 2-minute tour ×
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.

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

1 Answer 1

up vote 4 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:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

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)

mysql>

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

[mysqld]
local_infile=ON

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

[mysqld]
init-file=/var/lib/mysql/init_file.sql

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

Your Answer

 
discard

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.