某レンタルサーバ(○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が出力できなくなった」のが原因なんですけどね...