MySQL Secure File Privileges: Resolving Error 1290 When Writing to Files
MySQL's --secure-file-priv option restricts the execution of statements that write to files. This error occurs when attempting to write query results to a file using the INTO OUTFILE clause while this option is enabled.
Solution:
Find Allowed Write Paths
Check the value of the @@GLOBAL.secure_file_priv system variable to determine the directories where MySQL is allowed to write files:
<code class="sql">SELECT @@GLOBAL.secure_file_priv;</code>
Ubuntu 16.04: Write to the specified path, for example:
<code class="sql">SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';</code>
Mac OSX (MAMP):
Create .my.cnf File:
Disable Secure File Privileges (Not Recommended):
Set the @@GLOBAL.secure_file_priv system variable to NULL:
<code class="sql">SET GLOBAL secure_file_priv=NULL;</code>
Note: This method risks allowing untrusted code to write to arbitrary locations on the server. It should only be used as a temporary measure.
The above is the detailed content of How to Fix MySQL Error 1290: \"Can\'t create/write to file\" When Using INTO OUTFILE?. For more information, please follow other related articles on the PHP Chinese website!