10

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?

25

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

3
  • 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).
    – Googlebot
    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); Aug 26 '15 at 12:32
  • Enabling in PHP mysqli.allow_local_infile = On was good for me because GLOBAL local_infile = 'ON'; was already on.
    – Rolands.EU
    Jun 17 '19 at 21:22
0

For default installation of MySQL 8.0.20 on Windows:

  1. Look for the initialization file located at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.
  2. Open my.ini file with a text editor.
  3. Look for the headers [client], [mysql] under the CLIENT section & [mysqld] under the SERVER section.
  4. Add the following statement under each of these headers:

    local_infile=ON

  5. Save the file and restart MySQL80 service under the Windows local services.

It should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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