Enabling LOAD DATA LOCAL INFILE in mysql
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 !!!