サンタさんへ、MySQL5.7から8.4にアップグレードしてください
この記事は every Tech Blog Advent Calendar 2024 の 24 日目の記事です。
背景
こんにちは、開発本部 RetailHUB開発部 NetSuperグループに所属するフルスタックエンジニアをやらせていただいています、ホーク🦅アイ👁️です。
リテールハブ事業部
私の所属しているリテールハブ事業部の話をもう少し詳細にしてみようと思います。
ネットスーパーシステムは事業継承したので弊社が作ったものではなく、10年以上も前に作られたものがベースになっています。CentOSサーバ内にPHP5.3とMySQL5.6,5.7のLAMP環境で構成されており、クラウドもAWSではなくFJcloud-V(旧ニフクラ)を使っているという一癖も二癖もあるインフラ環境で保守運用がしづらい状況にあります。本チームではようやく、PHPからGoへのリプレイス計画を発進させたばかりです。
一方でGraphQL＋Pythonで動くものやREST＋Goで動くもの、REST＋Laravelで動くものもありインフラ環境としてはそれらは全てAWSで構築されているのでモダンな部分もあります。そういった複数言語・インフラ環境をエンジニア全員でメンテナンスしていくスタイルを取っているので自ずとフルスタックスキルを発揮することになリます。アプリはクロスプラットフォーム対応したFlutterアプリで開発制作しているのですがここだけは現在専属エンジニアに頼っておりC/S面でのフルスタックは発揮できていないのですが、来年から専属ではなくなるので今からチーム内でFlutter勉強会を開催してスキル習得を目指しています。
また、プロダクト向き合いが身近で声が通りやすいので企画段階から関わることもでき技術選定もできる用意があるので既存の開発環境に固執せず最適なアーキテクチャや新技術・ツールを使うチャンス があります。
私というと普段の業務は本当に幅広く、既存システムの追加開発に始まり小売様からの問い合わせ対応、障害調査＆火消し作業、アラート監視、ビジネスサイドの方々とのプロダクト改善・利益増のための施策・企画会議への参加などあります。レガシーかつ機能豊富な巨大システムであるため障害も起きてしまうことがあります。地味にトラブルの迅速対応(調査・解決など)推進力というのは経験豊富なエンジニアでないと発揮しづらい能力なのかなとは思うのでこのあたりの漠然としたスキルをうまく布教できると良いなと最近は思っています。
最近のトピックとしては、2025年4月より導入の義務化となっている3Dセキュア2.0認証にいち早く対応させました。2025年1月以降順次導入小売様よりスタート予定です。
さて、実際にリプレイスを行うにあたりいずれ必要になるであろう作業としてMySQLのアップグレードがあります。これは単純に2024年12月現在、AWSではMySQL5.7系のRDS、AuroraがEOLを迎えているからです。なのでまずは今のインフラ構成のままでMySQL8系にアップグレードしてみたらどうなるのか、案外簡単にできるのか？！を試してみたので以降で話させていただきます。
MySQL8.4について
公式MySQLが8.4をLTSに指定
AWS Aurora次期メジャーバージョン互換対象の可能性が高いのではないか
こういった点から、現在AWSがサポート中のMySQL8.0系ではなくいきなり8.4にあげてみることにしました。
MySQL5.7利用中のシステムに導入してみる
前提としていきなり本番環境に適用はできないので開発用に手元でDockerコンテナを利用したローカル開発環境があるのでそれを使ってアップグレード作業を行います。また、MySQL8.4サーバ設定ファイルは一旦デフォルトのままにします。
DBバックアップとリストア
まず、MySQL5.7側のデータを全てダンプします*1 。
# on MySQL 5.7
$ mysqldump --single-transaction --skip-lock-tables -p -h127.0.0.1 -P33306 -uappuser -B app_test
> app_test_dump20241224.sql
次に、MySQL8.4側に先ほどのデータを全てリストアします。
# on MySQL 8.4
$ mysql -p -h127.0.0.1 -P33308 -uappuser < app_test_dump20241224.sql
問題1
ここで、MySQL8.4にリストアすると以下のようなエラーメッセージが出てしまいました。
ERROR 1118 (42000) at line 1224: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
ここで解決策 を色々調べてみると先ず、MySQL8.0からinnodb_file_formatが廃止され内部的にBarracuda固定になったようなのでその設定変更は不要でしたのでこれは原因ではなかったようです。次に、ダンプファイルの中身を実際に見てみるとROW_FORMAT=COMPACTになっているCREATE文が32個あったのでROW_FORMAT=COMPRESSEDに全置換しました。そして再度リストアコマンド実行したところ今度はエラーが出ずに成功しました！
問題2
システムとデータが入ったMySQL8.4を接続してシステムが動作するかいざ試そうとすると、今度は以下のエラーが出て接続失敗となりました。
Client does not support authentication protocol requested by server; consider upgrading MySQL client
どうやら、MySQL8.0からデフォルトになった認証プラグインcaching_sha2_passwordに対応できてないエラーでした。この認証プラグインは結構大きな落とし穴でMySQL8.4では設定ファイルで有効化すれば使うことができました。また、MySQL9.0以上になると完全にmysql_native_passwordプラグインが廃止となって使えなくなります。
そこで、解決策 としてデフォルト設定だったmy.cnfに以下を追加して再起動しました。
[mysqld]
mysql_native_password=on
authentication_policy=mysql_native_password,,
再起動後に以下のSQLクエリで確認するとACTIVEに変わっており有効化に成功したのでさらに、pluginカラム値を変更します。この時、パスワード変更もしなければならないので注意です。BY以下を省略してしまうと空文字列のパスワードになってしまいます。また、FLUSH PRIVILEGESを実行しないとプラグイン変更が反映されないのでこれも注意です、このせいで私もさらに詰まっていました。
mysql> SHOW PLUGINS;
'mysql_native_password','ACTIVE','AUTHENTICATION',NULL,'GPL'
mysql> SELECT user, host, plugin FROM mysql.user WHERE user='appuser';
+---------+------+-----------------------+
| user | host | plugin |
+---------+------+-----------------------+
| appuser | % | caching_sha2_password |
+---------+------+-----------------------+
1 row in set (0.00 sec)
mysql> ALTER USER 'sha2user'@'%' identified WITH mysql_native_password BY '[新しいパスワード]';
+---------+------+-----------------------+
| user | host | plugin |
+---------+------+-----------------------+
| appuser | % | mysql_native_password |
+---------+------+-----------------------+
1 row in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
問題3
ここまででようやく接続までができたので今度こそクエリ発行をしてみようとネットスーパーユーザ向けTOPページにアクセスしてみたら今後は以下のエラーが出てページ表示ができませんでした。
General error: 1525 Incorrect DATETIME value: '0000-00-00 00:00:00'
調べていくとSQLモードのデフォルト設定を変更しなければならないことがわかりました。
The default SQL mode in MySQL 8.4 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.
上の引用文から8.4ではsql_modeのデフォルト値は6つあり厳密モードが有効化されていたので起きたエラーでした。したがって、解決策 として権限が一番緩い設定にしました*2 。
解決策としてmy.cnfに以下の設定を追加して再起動するとエラーが出なくなりました。
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
問題4
今度こそTOPページを表示させる！という意気込みでページのリロードを試みましたが、残念またもエラーが出てしまいました。
Error Code: 1525. Incorrect DATE value: '--15'
--15は、PHP側のバグでした。以下のコード例のように日付を年、月、日という変数から生成している箇所があり、そこで年と月の変数値が空文字列になっていたのでこのような文字列が生成されてその後date変数値が日付前提で後続処理していたのでそこでコケていたのでした。
$date = sprintf(“%s-%s-%s”, $year, $month, $day);
具体的には、MySQL側にSELECT文発行する際のWHERE条件句右辺にこのdate変数をそのまま渡しておりそこでコケると出るエラーでした。MySQL8.0.16以降はDATE型の比較演算対象の型チェックが厳密になったらしいです。一方でそれ以前のバージョンだったMySQL5.7の既存システムではこのバグ状態の変な文字列を右辺に渡していても正常クエリのレスポンスが返ってきていた*3 と思うとゾッとしますね...後は、PHPのバグを修正して正しいクエリを発行させたら解決です。
これでようやくTOPページが表示されました！
総括
本記事では、AWSリプレイス前段階としてMySQLサーバのバージョン5.7からバージョン8.4への挑戦について話しました。弊社提供のネットスーパーシステムは多くの機能を持つのですが、現状ではTOPページを表示させることができたに過ぎず今回のようないくつかの問題はまだまだ氷山の一角に過ぎないと予測しておりこれから長い検証を行っていかなければならないと思っています。例えば、SQL_CALC_FOUND_ROWSというクエリ発行が散在しているのでこれらを全てCOUNT()関数に置換していくというコードリファクタリングが見えていたりします*4 。なお、代替案のCOUNT()関数で処理速度が遅くなるバグが報告されており、バージョン8.0.37以降で改善されたそうです。
今後は、2024年11月21日に正式版がリリースされたAmazon RDS for MySQL8.4で動作検証できた時のお話ができればと思いつつ、これにて本記事の結びとさせていただきます。
最後に
エブリーでは、ともに働く仲間を募集しています。
テックブログを読んで少しでもエブリーに興味を持っていただけた方は、ぜひ一度カジュアル面談にお越しください！
corp.every.tv
Merry X'mas！🎄
六本木一丁目駅降りて直ぐの六本木グランドタワー駅前広場のイルミネーションツリー
