How to Solve \'Error Code: 1290: MySQL Secure File Priv Option Restricts Statement Execution\'?

Error Code: 1290: MySQL Secure File Priv Option Restricts Statement Execution

When attempting to execute a MySQL statement to export data to a file, you may encounter the following error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This error occurs when the MySQL server is configured to restrict file access for security reasons. The --secure-file-priv option limits the server's ability to execute statements that read or write files.

Solution:

To resolve this problem, you can take one of two approaches:

Option 1: Specify the Secure File Priv Directory

Locate the directory specified in the secure_file_priv variable using the following command:

SHOW VARIABLES LIKE "secure_file_priv";

Then, modify your export statement to specify this directory as the destination for the output file:

SELECT *
FROM xxxx
WHERE XXX
INTO OUTFILE '/path/to/directory/report.csv'
    FIELDS TERMINATED BY '#'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Option 2: Disable the Secure File Priv Option

Windows:

1. Navigate to the MySQL service properties.
2. Locate the my.ini file path in the "Options" tab.
3. Open the my.ini file and search for the secure-file-priv option.
4. Comment out the secure-file-priv line by adding a # at the beginning of the line.
5. Save the file and restart the MySQL service.

Linux:

1. Locate the MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf).
2. Open the configuration file and search for the secure-file-priv option.
3. Comment out the secure-file-priv line by adding a # at the beginning of the line.
4. Save the file and restart the MySQL service.

Note: Disabling the --secure-file-priv option may increase security risks. Ensure you weigh the security implications before making this change.

The above is the detailed content of How to Solve \'Error Code: 1290: MySQL Secure File Priv Option Restricts Statement Execution\'?. For more information, please follow other related articles on the PHP Chinese website!