1

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.

Daelin Campleman
  • 11
  • 1
  • Try to comment out this line in my.ini and restart server. Also try to screen backslashes like \\ and spaces in a directory name in the `select into file` command. – Michael Nov 02 '18 at 08:38
  • Does the current user you are using have the FILE privilege? – J2112O Nov 02 '18 at 09:31

1 Answers1

0

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'
SherylHohman
  • 16,580
  • 17
  • 88
  • 94
Ron Lewis
  • 1
  • While this code may solve the OP's issue, it is better to add context, or an explanation as to why this solves the OP's problem, so future visitors can learn from your post, and apply this knowledge to their own issues. High Quality Answers are much more likely to be upvoted, and contribute to the value and quality of SO as a platform. You can also add a link to documentation, if that helps. – SherylHohman May 07 '20 at 17:50