0

I am on MySQL 5.7.24 on Windows.

I am trying to export a table to csv:

SELECT 
    description, comment
FROM
    usecase
INTO OUTFILE ' c:\wamp64\tmp\usecase0.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

Error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I see there are lots of discussions on resolving the error during importing data into mysql, however, the issue I am having is on exporting data from MySQL table to CSV file.

I already run SET GLOBAL local_infile = true; and confirmed

enter image description here

I also have local-infile in both [mysql] and [mysqld] sections in my.ini

I don't know what am I missing or what else I can do?

Can anyone please help to sort it out?

Any clue is appreciated.

Thank you very much.

mdivk
  • 3,545
  • 8
  • 53
  • 91
  • use SHOW VARIABLES LIKE “securefilepriv”; to see if there is your folder c:\wamp64\tmp\ else change the variable – nbk Feb 21 '20 at 23:40
  • MYsql server only loads and writes data from that folder when you enable local_infile This is a security precaution. – nbk Feb 22 '20 at 01:44
  • Thank you. Initially the `securefilepriv` was set to `c:\wamp64\tmp\` and though I specified it as the export destination in the OUTFILE, it still throws me the error. – mdivk Feb 22 '20 at 01:50
  • I commented it out in the my.ini file, `; secure_file_priv="c:/wamp64/tmp"`, I am also running the mysql console as admin, just in case it matters. – mdivk Feb 22 '20 at 01:53
  • Check the permissions of the folder and also have a peek at the Event Viewer. – nbk Feb 22 '20 at 01:56
  • Permission is fine but event viewer shows some anomalies: `The DNS proxy agent was unable to allocate 0 bytes of memory. This may indicate that the system is low on virtual memory, or that the memory manager has encountered an internal error.` – mdivk Feb 22 '20 at 02:07
  • I tested it again, I believe the DNS proxy error in event viewer has nothing to do with the mysql export. – mdivk Feb 22 '20 at 02:31
  • Out of curiosity, have you checked out the answers in the duplicate question? – Shadow Feb 22 '20 at 08:05
  • yes I did, I've found the issue with my case, it was partly caused by the path, the path must come with double back slashes. I posted my answer here: https://stackoverflow.com/questions/33691094/mysql-exporting-into-outfile-secure-file-priv-error-when-using-set-directory/60348556#60348556 – mdivk Feb 22 '20 at 13:42

0 Answers0