Prabhu Khanna Mahadevan Prabhu Khanna Mahadevan - 6 months ago 803
MySQL Question

Mysql making --secure-file-priv option to NULL

I am running MySQL in Ubuntu. I getting this error while running specific set of queries.

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

When I did SELECT @@secure_file_priv; in my mysql server I got /var/lib/mysql-files/. I think I need to make this to null.

This is the query I am running LOAD DATA INFILE :file
INTO TABLE test_files
COLUMNS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'"


Now the question is how to make this NULL ?

Answer

Try:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.12-0  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

Change file: /etc/mysql/my.cnf

[mysqld]
.
.
.
secure_file_priv=NULL
.
.
.

Restart MySQL.

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)