某レンタルサーバ(○CN)でMySQLのバージョンを5.5から5.7にアップデートしたらハマった時の対応について
connectできない
サーバ会社から割り当てられたビルトインアカウントでphpMyAdminにはログインできるものの、他のアカウントではログインできない。当然、Webアプリからもアクセスできない。
pluginフィールドを更新
そのままだと、set等のパスワード変更DDLも通らない。
そこで、mysql.userテーブルのpluginフィールドを更新してしまう。
もともとの値はセットされていなかった。
影響範囲がわからないので、ログインするユーザーのみ変更実行。
update mysql.user set plugin='mysql_native_password' where user='ユーザー名';
権限の更新
flush privilegesで権限の更新。
flush privileges;
パスワードの変更
パスワードハッシュをpassword関数をつかって書き換えてしまう。ちなみにold_password関数は5.7で廃止された。
set password for 'ユーザー名'@'localhost' = password('新しいパスワード');
これで解決。
潜在バグが顕在化
デフォルトのSQLモードが変更になり、GROUP BYでの指定がややこしくなる。
例えば
SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_name.table_name.id' which is not functionally dependent on columns in GROUP BY clause
等のエラーが発生する。
本来はバグ潰しが本筋なんだけど、今動いている物に手を加えて検証する時間もなくて動くのを優先する場合。SQLモードを旧バージョン相当にして無理やり動かしてしまう。
そのレンタルサーバでは、my.cnfをユーザーが変更することはできなかった。しかし、5.7からユーザーディレクトリのmy.cnfも読み込むようになったので、このファイルを作成して修正する。
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
5.7のデフォルトのSQLモードは
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
そもそもは突然「OUTFILEでCSVが出力できなくなった」のが原因なんですけどね...