How to Fix \"Error Code: 1290. The MySQL server is running with the --secure-file-priv option...\"?

Error Encountered: MySQL Server Secure File Privileges Restricting Statement Execution

You've encountered the error "Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement" while attempting to execute a MySQL statement that involves using the INTO OUTFILE command.

Cause of the Error:

The secure-file-priv option limits the directories where MySQL can write files to improve security. By default, this option restricts MySQL from writing to external directories.

Immediate Solution (Non-Configuration Change):

A quick workaround to resolve this issue is to identify the directory where MySQL is permitted to write files:

<code class="sql">mysql> SHOW VARIABLES LIKE "secure_file_priv";</code>

Once you've identified the allowed directory, modify your INTO OUTFILE statement to use this directory as the target:

<code class="sql">SELECT *
FROM xxxx
WHERE XXX
INTO OUTFILE '/var/lib/mysql-files/report.csv'
    FIELDS TERMINATED BY '#'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'</code>

Permanent Solution (Configuration Change):

Windows:

1. Navigate to the MySQL service settings in Windows Services.
2. Open the "my.ini" file specified in the "Service Settings" section.

3. Locate the "[mysqld]" group and add or modify the "secure-file-priv" option to specify the allowed directory. For example:

   <code class="ini">[mysqld]
   secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.6/Uploads"</code>

4. Restart the MySQL service.

Linux:

1. Open the MySQL configuration file, typically located at "/etc/my.cnf" or "/etc/mysql/my.cnf".

2. Locate the "[mysqld]" group and add or modify the "secure-file-priv" option to specify the allowed directory. For example:

   <code class="ini">[mysqld]
   secure-file-priv="/var/lib/mysql-files/"</code>

3. Restart the MySQL service.

The above is the detailed content of How to Fix \"Error Code: 1290. The MySQL server is running with the --secure-file-priv option...\"?. For more information, please follow other related articles on the PHP Chinese website!