How to Fix MySQL Error 1290: \'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement\'?

MySQL Error 1290: Secure File Private Option Blocking Data Export

When executing a MySQL statement involving data export to a file using the INTO OUTFILE clause, users may encounter the error "Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement." This occurs when the server is configured with enhanced security measures that restrict file operations.

Explanation of Secure File Private Option

The --secure-file-priv option limits file access and privileges within the MySQL server. By default, this option is enabled and assigns a specific directory as the only permitted location for file operations. Any attempt to access or write to files outside this designated directory will result in Error 1290.

Simple Solution: Restrict Path to Permitted Directory

To resolve this error without modifying server settings, users can explicitly specify the permitted directory in the INTO OUTFILE clause:

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

Replace with the actual directory path that is allowed by the secure_file_priv option.

Alternative Solutions:

• Disable Secure File Private Option:

This requires editing the MySQL configuration file (usually my.ini or my.cnf). Comment out or remove the secure_file_priv option or set it to an empty string (``). However, this reduces server security and should only be done in controlled environments.

• Grant File Privileges to User:

If you require file access beyond the permitted directory, you can grant the FILE privilege to the executing user. This can be done using the following SQL statement:

GRANT FILE ON *.* TO <username>@'<host>';

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