Enabling LOAD DATA LOCAL INFILE in mysql

Question

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?

Answer 1

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

Answer 2

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.