sql - Disable secure priv for data loading on MySQL

Question

I'm running MySQL 5.7 on a Windows 10 machine. I've read through all the SO threads on this topic and still haven't figured out how to get my data to load and get past this error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv 
option so it cannot execute this statement

I have 1) checked the settings to change them to be able to load from the directory in which I've saved my dataset, 2) opened up MySQL as administrator and checked the command line and have confirmed that the secure file does indeed point to my directory, 3) and confirmed in the init file that it's pointing to the correct directory containing my file. I tried changing the location of the dataset so it would be in a new folder and confirmed it had been moved there with the above methods, and it still did not work.

Any and all help would be welcome, thank you.

Answer 1

I can't reproduce the problem.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0,00 sec)

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

-- USE ...

mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
    -> INTO TABLE `test_files`
    -> COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '
';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement

Change file: /etc/mysql/my.cnf

[mysqld]
.
.
.
secure_file_priv=/var/lib/mysql-files/
.
.
.

Restart MySQL.

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (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 '
';
Query OK, 3 rows affected (0,00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

See 6.1.4 Server System Variables :: secure_file_priv