How to Fix MySQL Error 1290: \'Can\'t create/write to file\' When Using INTO OUTFILE?

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:

1. Open ~/.my.cnf.
2. Add the line: secure_file_priv="/Users/[your username]/" within the [mysqld] section.

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!