【MySQL】エラーメッセージ「The MySQL server is running with the --secure-file-priv option so it cannot execute this statement」

ファイルの入出力に関する制限によりエラーが発生している。MySQLのファイルへのエクスポートは、あらかじめ決められらディレクトリに置かれファイルにしか行うことができない。以下はデータベースprincessのテーブルumamusumeの内容を、D:\にファイルtemp.tsvとしてエクスポートしようとして失敗している。

mysql> use princess;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_princess |
+--------------------+
| umamusume          |
| walkure            |
+--------------------+
2 rows in set (0.01 sec)
mysql> select * into outfile 'd:\\temp.tsv' from umamusume;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

エクスポート可能なディレクトリはMySQL 8.0であればデフォルトは「C:\ProgramData\MySQL\MySQL Server 8.0\Uploads」。サーバーシステム変数secure_file_privに格納されている。

mysql> show variables like 'secure_file_priv';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/temp.tsv' from umamusume;
Query OK, 4 rows affected (0.01 sec)

これを変更するには、設定ファイルmy.iniを修正する。設定ファイルは「C:\ProgramData\MySQL\MySQL Server 8.0」にあり、これをテキストエディタで開くと、165～168行目辺りに、以下のような記述があるはず。

# This variable is used to limit the effect of data import and export operations, such as 
# those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the 
# LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

secure-file-privを次のように修正する。

secure-file-priv=""

MySQLを終了し、MySQLのサービスを再起動して、MySQLに再度サインインする。サーバーシステム変数secure_file_privを確認すると、空であることがわかる。改めてエクスポートしてみると、成功するはず。なお、ファイルを上書き保存することはできず、最後の例のとおり、出力しようとするファイルが存在する場合は、エクスポートに失敗する。

mysql> use princess;
Database changed
mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> select * into outfile 'd:\\temp.tsv' from umamusume;
Query OK, 4 rows affected (0.01 sec)
mysql> select * into outfile 'd:\\temp.tsv' from umamusume;
ERROR 1086 (HY000): File 'd:\temp.tsv' already exists

なお、「C:\ProgramData\MySQL\MySQL Server 8.0\my.ini」はテキストエディタで開くと、読み取り専用で開かれて保存できないはず。そのため、my.iniを違うディレクトリにコピーしてそこで編集をして、その編集したmy.iniをC:\ProgramData\MySQL\MySQL Server 8.0に上書き保存する必要があるので注意。