概要
MySQL5.7のDBからDUMPを取って、Mysql8.0へレストアした際に以下のエラーになりました。
ERROR 1231 (42000) at line 3775: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
こちらの原因と解決策がわかったので紹介します。
原因と対策
NO_AUTO_CREATE_USERモードとは、無効な場合はGRANT文を使用して新しいユーザーアカウントを自動的に作成することができるようになり、有効な場合は自動的に作成することができなくなる、というSQLモードです。
今回のエラーの原因は、MySQL 8.0ではこのNO_AUTO_CREATE_USERが廃止されているためでした。
MySQL 8.0以降ではsql_modeにNO_AUTO_CREATE_USERが含まれていると発生します。
DUMPファイル生成時に記述されている以下の部分から「NO_AUTO_CREATE_USER」を削除すると、エラーが表示されなくなりました。
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */
参考
以下、Mysqlの公式ドキュメントです。
MySQL 8.0.11 では、ユーザーアカウントの非権限特性を変更するための GRANT ステートメントの使用、NO_AUTO_CREATE_USER SQL モード、PASSWORD() 関数、old_passwords システム変数など、アカウント管理に関連するいくつかの非推奨機能が削除されました。
ストアドプログラム定義に NO_AUTO_CREATE_USER SQL モードを含むダンプファイルを MySQL 8.0 サーバーにロードすると、障害が発生します。 MySQL 5.7.24 および MySQL 8.0.13 の時点で、mysqldump はストアドプログラム定義から NO_AUTO_CREATE_USER を削除します。 以前のバージョンの mysqldump で作成されたダンプファイルは、NO_AUTO_CREATE_USER のインスタンスを削除するために手動で変更する必要があります。