akippaのインフラを改善していく物語の3回目です。前回はアラート通知を強化した、というお話でした。

今回のテーマ

前回の冒頭でも触れていた、Auroraのバージョンアップがようやく完了したので、今回はこれがテーマです。

akippaではMySQL5.7互換からMySQL8.0互換へのバージョンアップでしたが、同じパターンでバージョンアップはまだこれから、というところもあると思いますので、事例が1つでも参考になれば幸いです。

バージョンアップ準備

事前調査

何はなくとも、まずはMySQLの公式情報をチェックしました。

• https://dev.mysql.com/doc/refman/8.0/ja/upgrading-from-previous-series.html

• https://dev.mysql.com/doc/refman/8.0/ja/mysql-nutshell.html

非常に多岐に亘っており、すべてを事前に確認・検証するのは現実的でないと判断し、明らかにクリティカルなものにフォーカスして確認・調査を行いました。

mysql_native_password に代わり、caching_sha2_password がデフォルトとなっています。

ローカルの開発環境で利用しているMySQL8.0では、my.cnfで明示的にデフォルトの認証プラグインを指定する必要がありましたが、Aurora側ではあらかじめこの設定が為されていました。嬉しい配慮ですね。

予約語が増えたため、既存のクエリがエラーになってしまう可能性があります。akippaでは基本的にORMやクエリビルダーを利用しているので、文字列でSQLを組み立てるケースは皆無ですが、古いコードベースでは引っかかる可能性があります。

使っていそうな予約語はgrepで確認のうえ概ね問題なさそうだと判断し、後は検証であぶり出せると期待しました。

akippaのDBは既に utf8mb4 に対応済みですので、ここは大きな問題ではありません。(と考えていましたが、後で躓くことになります…)

テスト環境Auroraのバージョンアップ

今回は、稼動中のAuroraクラスターの設定変更でバージョンアップを行う「インプレースアップグレード」で臨むため、まずは複数あるテスト環境のうち、チームや他部署への影響が最も少ない環境のAuroraをバージョンアップしてみましたが、当然のように一筋縄では行きません。

バージョンアップの試行錯誤

インプレースアップグレードでは、実際のバージョンアップ実施前に必要な条件をクリアできているか？をチェックしてくれます。このチェックに失敗するとJSON形式で問題箇所を指摘してくれるため、1つずつ対処していきます。

既に存在しないテーブルを参照するVIEW

{
    "level": "Error",
    "dbObject": "********",
    "description": "Corrupt"
}

10年続くサービスですので、その過程で発生した様々な試行錯誤の余韻が残っていたようです。フレームワークのmigration導入前に定義されたものと思われたので、古参メンバーの知見も借りて不要なVIEWはすべて削除しました。

特定テーブルのINDEX長

{
    "level": "Error",
    "dbObject": "********",
    "description": "Index `bigram` from `********` has Index length 12884901885 > 767 bytes, which will cause error after upgradation. Consider changing the row_format of the tables to dynamic and restart the upgrade."
}

過去に実施されたutf8 -> utf8mb4対応時、以下の制限に引っかかり特定カラムの文字セットをutf8のままとしていたためでした。現行DBバージョンではこの制限に引っかからないことが確認できたため、事前に対象カラムもutf8mb4へ変更しました。

これで事前チェックはすべてパスするはずなので、あらためてバージョンアップを実行したところ、バージョンアップ中にエラーを吐いて失敗してしまいました。

空間データ型に対するマルチカラムインデックス

2023-09-06T05:05:20.032989Z 2 [ERROR] [MY-013140] [Server] Too many key parts specified; max 1 parts allowed

調査にかなりの時間を費やしたものの、原因は特定テーブルの「空間インデックス」であることが分かりました。空間データ型に対してマルチカラムインデックスを定義しているテーブルがあったのですが、MySQL8.0以降ではこれがNGになりました。

akippaのサービス特性上、空間データ型を利用することもままあるため、これに引っかかったようです。こちらもチームのエンジニアに知見を借りて、テーブル定義を変更することで解消されました。

バージョンアップ後の検証

無事、テスト環境のAuroraがバージョンアップ完了したので、さっそく検証へ移っていきます。

こういうシーンではE2Eが完備されていればスムーズなのですが、まだまだそこまで辿りつけていないため、まずはモンキーテストのアプローチでコアな機能や挙動に問題が無いかザッと確認しました。

DB接続エラー

最初に検知されたのは、特定のアプリケーションからDBへ接続できないという問題です。アプリケーションログには以下が記録されており、どうやら文字セットに関する問題のようです。

[2054] Server sent charset unknown to the client. Please, report to the developers

akippaでは、複数のPHPバージョンと複数のフレームワークが稼動しているのですが、このエラーはその中でも特定のPHPバージョンで稼動しているアプリケーションのみで発生していました。

バージョン詳細などは伏せますが、このPHPバージョンと前掲の文字セット utf8mb4 がトリガーとなり、接続エラーが発生するようです。

アプリケーションに手を入れるか、DB側で吸収する方法を模索するかで悩みましたが、以下の観点から後者のアプローチで対処することとしました。

1. かなり古いアプリケーションでテストも無いため、コアな箇所に手を入れた場合の影響範囲が測り難い

2. 現行バージョンのAuroraはクラスターパラメータの文字セットが utf8 であり、この設定での運用実績がある

3. 2.の設定をバージョンアップ後の状態で検証し、DB/アプリケーションともに4byte文字が正しく扱われることを確認できた

Auroraのパラメータグループ設定で utf8mb4 -> utf8 に変更し、各アプリケーションから問題なくDB接続できるようになりました。

照合順序不一致のクエリエラー

次に検知されたのは、最も懸念していたクエリレベルでのエラーで、ログには以下が記録されていました。

General error: 1267 Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

アプリケーションが発行しているクエリを手元のDBクライアントツールで試すなど試行錯誤した結果、以下が原因であると特定できました。

• 照合順序 utf8mb4_0900_ai_ci と utf8mb4_general_ci で不一致を起こしている

MySQL8.0からデフォルトの照合順序が utf8mb4_0900_ai_ci へ変更されたようで、これと複数の要因が組み合わさったことでクエリエラーとなっていました。

期待する照合順序は utf8mb4_general_ci だったので、文字セット同様にAuroraのパラメータグループでデフォルトを設定しようとしましたが、以下のエラーで設定することができません。

collation_server 'utf8mb4_general_ci' is not valid for character_set 'utf8'

特定アプリケーションからのDB接続を可能にするため変更した設定とバッティングしてしまうようです。設定で回避することは諦めて、対象のクエリを修正するアプローチに切り替えました。問題のクエリは以下のような内容です。

SELECT
  some_column, ...
FROM
  table_a
  LEFT JOIN table_b
    -- ここで照合不一致が発生している
    ON CAST(table_a.column1 AS CHAR(100)) = table_b.column2

諸事情によりデータ型の異なるカラムで結合する必要があるため、左辺を右辺の型に合わせてCASTしています。

table_a.column1 はINT型なので、これをCASTした際にDBデフォルトの utf8mb4_0900_ai_ci が利用されますが、table_b.column2ではカラム単位のcollationに utf8mb4_general_ci を明示しているため、不一致となることが原因です。

左辺でも utf8mb4_general_ci が利用されるよう、CAST時に明示したりCONVERT関数で代替してみたりと色々試してみたものの、どれもうまく行きません。最終的に、右辺側もCASTしてやればエラーが解消され、取得結果も変わらないことが分かったので、この対処としました。

SELECT
  some_column, ...
FROM
  table_a
  LEFT JOIN table_b
    -- 両方CASTすることで解消
    ON CAST(table_a.column1 AS CHAR(100)) = CAST(table_b.column2 AS CHAR(100))

暗黙キャストの仕様変更によるクエリエラー

ひと息ついたのも束の間、またもやクエリ絡みと思われるエラーが検知されました。ログは以下の内容です。

Error Code: 1525. Incorrect DATE value: ''

こちらは先ほどのクエリエラーほど厄介ではなく、以下を参考に原因を特定することができました。インターネットの共有知に感謝です。

アプリケーションから発行されていたクエリは以下のようなものです。コードベースもかなり古く、本来 column1 = '' は不要なはずですが、おそらく当時在籍されていたエンジニアは、安全弁・保険のようなイメージで記述されたのだろうと思います。

SELECT
  some_column, ...
FROM
  table_a
WHERE
  -- column1はDATE型
  column1 = '' OR column1 IS NULL

不要な条件を削除することでエラーは解消されたのですが、懸念したのは「こういった条件が、テストのないコードベースにどの程度記述されているか？」です。grepで拾い切れる性質でもないため、別途進める検証で炙り出していくこととしました。(幸い、同じ問題が検出されたのはもう1箇所だけでした)

上記以外のトラブルにぶつかる事もなく、検証は順調に進みました。互換性以外で懸念していたのは予期せぬパフォーマンスダウンですが、重めのバッチ処理を本番同等のデータ量で計測し、現行バージョンとほぼ同等のパフォーマンスが維持できていることも確認できました。

影響範囲の大きいバージョンアップでは、細かい点を気にしだすと終わりが無いので、検証結果を以てバージョンアップに問題なしと判断、本番での実施当日を迎えることとしました。

バージョンアップ当日

メンテナンス作業

DBバージョンアップという性質上、実施作業中はサービスをメンテナンス状態にする必要があります。深夜帯とはいえダウンタイムは短いに越したことは無いので、当たり前のことを当たり前にやる、を念頭に準備しました。

• 当日作業シミュレーションで作業時間計測

• 作業手順書の作成・レビュー

• 関係各所への事前周知

事前準備よりもAWSサービスの利便性・安定性のお陰だと思いますが、当日作業は大きな問題なく完了。と思いきや、最後の最後に問題が発生しました。

BIツールからDBに接続できない

akippaは職種を問わずSQLに明るいメンバーが多いので、データ資産を気軽に利活用するためにBIツールの Re:dash を利用しています。このRe:dashからバージョンアップ後のDBへクエリを実行すると、DB接続エラーになってしまいます。

今回のバージョンアップにあわせて、Auroraで利用されるSSL認証機関の更新も実施したため、それが原因と判断して切り戻してみるも解消せず。Re:dashが稼動するEC2インスタンスやコンテナを再起動してみても解消せずでした。

この時点で、DBバージョンに起因する問題と判断し、Re:dashの接続先とアプリケーションからの接続先はカスタムエンドポイントでisolationされている点を踏まえ、メンテナンス状態は解除しました。

Re:dashの分析結果を元に動いている業務や仕組みも多々あるため、内心冷や汗ものだったのですが、深夜作業に立ち会ってくれた akippa_okuyama がRe:dash側の設定を変更すれば接続できることに気づいてくれました。

ファインプレーに助けられ、無事接続エラーも解消、バージョンアップ当日作業はすべて完了しました。

BIツールという特性上、Re:dashは本番環境のみしか構築しておらず、課題になるような古いバージョンを使っているわけでもないため問題ないだろう、と考えたのが甘かったので、これを学習機会と捉えて次回以降に活かしていきます。

バージョンアップ後

当日に検知された問題も無事解消したものの、それだけでは終わらないのがDBバージョンアップです。翌日の業務開始後、さっそく他部門から以下の相談がやってきました。

クエリを実行するとエラーになる

前日から変わったことといえばDBバージョンアップだけですので、ほぼ間違いなくDBバージョンアップの影響です。戦々恐々としていたところに、これまた他部門のメンバーから非常に有益な情報を共有いただきました。

結論から言うと、この情報がそのものズバリで、記事にあるとおりの原因を見事に踏み抜いていました。

幸い、設定変更が必要なパラメータはどちらもDynamicだったため、クラスターパラメータの設定を変更し、必要十分なメモリ・ディスク領域を確保することで解消しました。

エラーの細かいところは分からないので、後はよろしく、というのが部門間連携では一般的だと思いますが、他部門のメンバーがエラーメッセージを頼りに原因調査までやってくれたのは、とても有り難かったですし驚きでもありました。

こういった振る舞いに、akippaが大事にしているホスピタリティの精神が現れていると思います。あらためて、この場を借りて感謝です。

また、事後調査でこのエラーはAuroraのエラーログに記録されていることが分かったため、以下を参考にCloudWatchのメトリクスフィルターを作成し、既存のアラート通知パイプラインに組み込みました。

これで、次回以降は即座に検知して、初動をとることが出来るようになりました。

まとめ

MySQL5.7→MySQL8.0自体が多くの変更を伴うものだったようで、互換エンジンのAuroraもバージョンアップはなかなかに大変だと思います。

とはいえ、MySQL/Auroraともにドキュメントはとても充実しており、先行者事例もどんどん公開されていますから、事前準備をしっかりすれば問題なく乗り切れるというのが、実際に対応してみての感想です。

また、MySQL8.0になるとWITH句が使えたり、WINDOW関数がサポートされたりと、欲しかったものがやっと揃ってきた感がありますから、こういったベネフィットもモチベーションにできるのではないでしょうか。(個人的にはマテビューも欲しかったです…)

これからバージョンアップを頑張る予定の方にエールを送りつつ、次回はAWSインフラの改善などを取り上げられればと思います。