sql_mode does not work
MySQLのsql_modeを設定する必要に迫られたので設定しようとしたらハマった…。MySQLのバージョンは5.6系です。OSはCentOS6系です。
手動で設定する方法
まずは手動で設定する方法。
現在設定されているsql_modeを確認する方法は以下。
SELECT @@GLOBAL.sql_mode;
sql_modeを手動で設定する方法は以下。
- 例として
NO_ENGINE_SUBSTITUTIONを指定する場合
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
ここまでは良い。ただし、この設定はオンメモリなのでmysqldを再起動したら失われます。
なので/etc/my.cnfにsql_modeを設定したかったのですが、ここでハマりました。sql_modeの設定を書いてmysqldをrestartしても設定が反映されない…。
ハマリポイント1
MySQL5.6.27以前(Ubuntuのリポジトリの話ですが)ではsql_modeが効かない問題があったらしい。
ハマリポイント2
どうにも/etc/my.cnfに書いた設定が反映されないので、もしかして別の場所で設定が上書きされているのではないかと疑い雑にsudo find / -name "*my.cnf"してみたところ、/usr/my.cnfとやらが発掘されました。怪しい…。
中を見るとsql_nodeの設定がありました。これが原因でした。
解決方法
/usr/my.cnfのsql_modeを修正することでmysqld restart後も設定が反映されました。
原因
範馬の血の如く様々なディレクトリに点在しているmy.cnfの読み込み順は後勝ちになっており、/usr/my.cnfは/etc/my.cnfより後に読み込まれるので、/usr/my.cnfのsql_modeが反映されてしまっていました。
範馬の血的にも後から生まれた範馬刃牙の方がジャック・ハンマーより強いですね。
以下が参考になりました。
参考リンクにもあるように、/usr/bin/mysql_install_dbが/usr/my.cnfを設置しているようです。mysql_install_dbはperlスクリプト(MySQL5.5ではシェルスクリプトらしい)になっており、# Distros might move files以下の行で/usr/share/mysql/my-default.cnfをコピーして作成しているようです。
/usr/my.cnfはあくまでmysql_install_dbが設置しているファイルなので、rpm -ql mysql-community-serverコマンドでは発見できないところが曲者ですね…。
なおMySQL5.7のmysql_install_dbからは/usr/my.cnfを作らなくなるそうです。 :D