Mysql making --secure-file-priv option to NULL
22,614
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)
UPDATE
mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/ |
+---------------------------+
1 row in set (0.00 sec)
File: /var/lib/mysql-files/myfile.csv
1,"Row 1"
2,"Row 2"
3,"Row 3"
mysql> DROP TABLE IF EXISTS `test_files`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `test_files` (
-> `col0` INT,
-> `col1` VARCHAR(20)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
-> INTO TABLE `test_files`
-> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"'
-> LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT
-> `col0`,
-> `col1`
-> FROM
-> `test_files`;
+------+-------+
| col0 | col1 |
+------+-------+
| 1 | Row 1 |
| 2 | Row 2 |
| 3 | Row 3 |
+------+-------+
3 rows in set (0.00 sec)
Author by
Prabhu Khanna Mahadevan
Updated on December 13, 2021Comments
-
Prabhu Khanna Mahadevan over 2 years
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 toNULL
.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
? -
Prabhu Khanna Mahadevan almost 8 yearsThank you for your answer. It did changed to NULL. But still I am getting the same error.
-
wchiquito almost 8 years@PrabhuKhannaMahadevan: Please, update your question and put the query that generates the error.
-
Prabhu Khanna Mahadevan almost 8 yearsUpdated the query. I believe there is no issues with the query. It works fine in other servers. It doesn't work only in my local machine.
-
Prabhu Khanna Mahadevan almost 8 yearsIt worked after restarting my apache. Great work @wchiquito tnkz a lot for your help.