How to Fix MySQL Error 1290 (HY000): The --secure-file-priv Issue

Error 1290 (HY000): Solving the --secure-file-priv Issue

MySQL error 1290 (HY000), often encountered when attempting to write query results to a text file, is caused by MySQL's --secure-file-priv option. This option restricts the server from writing files to arbitrary locations.

Ubuntu 16.04:

To resolve this issue in Ubuntu 16.04, determine the allowed write directory using the following command:

mysql> SELECT @@GLOBAL.secure_file_priv;

Write to the specified directory as follows:

mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';

Mac OSX (MAMP Installation):

1. Check the allowed write directory:

mysql> SELECT @@GLOBAL.secure_file_priv;

If the result is NULL, create a file named '~/.my.cnf' and add the following lines:

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/username/"

2. Replace 'username' with your user directory.
3. Restart the MySQL server via MAMP.
4. Verify the change:

mysql> SELECT @@GLOBAL.secure_file_priv;

5. Export the table:

mysql> SELECT * FROM train INTO OUTFILE '/Users/username/test.csv' FIELDS TERMINATED BY ',';

By following these steps, you can disable the --secure-file-priv restriction and write query results to specified text files on your system.

The above is the detailed content of How to Fix MySQL Error 1290 (HY000): The --secure-file-priv Issue. For more information, please follow other related articles on the PHP Chinese website!