Error Code: 1290. The MySQL server is running with the --secure-file-priv option

Question

This has really been annoying me. I am using MySQL on a Windows computer. The MySQL statement that I am trying to run is as follows:

SELECT tempInfo.clientName FROM tempInfo LIMIT 1 INTO OUTFILE 'test.out'

I understand that I need to change the location that the file is being saved to which I did by finding the correct location of the folder that needs to be used and changed my code to:

SELECT tempInfo.clientName FROM tempInfo LIMIT 1 INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\test.out';

This still gives the same error. So now I've tried to edit the my.ini file to be the following:

secure_file_priv=''

so that any directory can be use but then I am unable to save the my.ini file back to the folder because it says its denied.

Next I tried to change it inside the MySQL Command Line Client with the following:

SET @@secure_file_priv='';

This just gives me an error stating that 'secure_file_priv' is a read only variable.

Lastly I tried starting the MySQL server with the following parameter:

--secure_file_priv=''

but when I try viewing the variable in the MySQL Command Line once it has started, it still shows as this:

+------------------+------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------+ | secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ | +------------------+------------------------------------------------+ 1 row in set (0.00 sec)

I am really stuck now and have looked everywhere and I don't know what to do. It seems as though I need to change the variable to not be read-only but I don't know how to do that.

Answer 1

I modified the path and added \\ so it looks like the following:

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Uploads\\filename.csv'