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

Question

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?

By the way, I saw this [dev.mysql.com/doc/refman/5.7/en/… but I am not aware how to make this NULL. — Prabhu Khanna Mahadevan
– Prabhu Khanna Mahadevan, Commented May 31, 2016 at 10:35

wchiquito · Accepted Answer · 2016-05-31 12:00:04Z

4

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)

edited May 31, 2016 at 12:00

answered May 31, 2016 at 11:02

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prabhu Khanna Mahadevan Over a year ago

Thank you for your answer. It did changed to NULL. But still I am getting the same error.

wchiquito Over a year ago

@PrabhuKhannaMahadevan: Please, update your question and put the query that generates the error.

Prabhu Khanna Mahadevan Over a year ago

Updated 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 Over a year ago

It worked after restarting my apache. Great work @wchiquito tnkz a lot for your help.

slp · Accepted Answer · 2021-12-12 18:35:49Z

Thank you wchiquito for your solution, which has led me in the right direction.

I would like to add a solution for MySQL 8 under Windows 10. In which a few points differ.

The my.ini file can be found in a different location:

C:\ProgramData\MySQL\MySQL Server 8.x\my.ini

A longer description can be found here: https://stackoverflow.com/a/20136523/1316649

The variable to be changed is called secure-file-priv and it must be unset.

[mysqld]
...
secure-file-priv=
...

After restarting MySQL under services you can check with

SELECT LOAD_FILE ('C:\\path\\to\\file\\content.txt');
SELECT LOAD_FILE ('C:/path/to/file/content.txt');

whether the file is loaded. The content should be visible here, not NULL. A simple backslash doesn't work for me.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related