MySQLのエラー『Variable ‘SQL_MODE’ Can’t be Set』の解決方法

MySQLで「Variable ‘sql_mode’ can’t be set to the value of ‘XXXX’」というエラーは、指定されたSQL_MODEの値が不正、またはそのバージョンで使用できないときに発生する。設定ファイルの記述ミスや、非推奨となったモードの利用が主な原因となる。

1. エラーメッセージの内容と意味

ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NO_ZERO_DATE'

このエラーは、指定したSQL_MODEの中にMySQLのバージョンでサポートされていないものが含まれているときに表示される。

2. 発生条件：非推奨モードの使用

MySQL 5.7.4以降では、以下のモードは削除または無視されるようになった。

• NO_AUTO_CREATE_USER（MySQL 8.0で削除）
• NO_ZERO_DATE（5.7.4以降で削除）
• NO_ZERO_IN_DATE（5.7.4以降で削除）

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE';

この設定はMySQL 5.7.4以降ではエラーになる。

3. SHOW VARIABLESで現在のsql_modeを確認

SHOW VARIABLES LIKE 'sql_mode';

現在のSQLモードがどのように設定されているかを確認し、不要または廃止されたモードを取り除く手がかりになる。

4. SET文で部分的に変更する

sql_modeを完全に上書きするのではなく、特定のモードだけを削除することでエラーを回避できる。

SET sql_mode = REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');

5. my.cnf や my.ini での設定ミス

設定ファイルに誤ったsql_modeを記述すると、MySQLの起動時や再起動時に失敗する。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE

NO_ZERO_DATEなどを削除することで回避可能。

6. sql_modeの有効な値の確認方法

MySQLのドキュメントか、以下のクエリで有効なsql_modeを確認する。

SELECT @@sql_mode;

また、以下でサーバのバージョンも確認しておく。

SELECT VERSION();

7. MySQLのバージョンごとの差異

MySQL 8.0では `NO_AUTO_CREATE_USER` を設定しようとするとエラーになる。

SET sql_mode = 'NO_AUTO_CREATE_USER';
-- ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

8. 推奨されるsql_modeの例

バージョン8.0以上での推奨設定例。

SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

9. アプリケーションやORMによる自動設定

LaravelやRails、Spring Bootなど一部のフレームワークが自動で `sql_mode` を設定することがある。サーバ設定と競合してエラーになることも。

10. SQLモードの違いによる動作影響

たとえば `STRICT_TRANS_TABLES` を有効にすると、NULLを許可しないカラムにNULLを入れようとするとINSERTは失敗する。一方、モードが無効なら代替値が使われる。

11. SQLモードを永続的に変更する

サーバ再起動後にも反映されるよう、設定ファイルに正しい値を記述する必要がある。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

12. SQLモードの初期化で強制リセット

sql_modeを空にしてリセットすることで、一時的にすべてのモードを無効化できる。ただし安全性は下がるため注意が必要。

SET sql_mode = '';