はじめまして、CastingONEでバックエンドエンジニアをやっておりますJakeと申します。
最近犬を飼い始めて、原神の時間が無くなりました。ありがとうございます。
はじめに
CastingONEでは当初からMySQL5.7を使用していたのですが、とある事情からMySQL8にアップデートしたのでそちらについてお話しようと思っております。
なぜMySQL8にアップデートしたのか
- MySQL5系までのバグのせいで、データの復旧が上手くいかない事象が発生していた
- MySQL8にアップデートすれば解消される事象
- MySQL8の新機能(WITH句、WINDOW関数などなど)が使える
大きく2つの理由があったのですが、今回は1つ目の理由について、何があったか・どう対応したかを中心にお話したいと思います。
MySQL8に関しての具体的な機能などについてはこの記事では割愛させていただきます。
なぜデータの復旧が上手くいかない事象が発生していたのか
当社のサービスには、一部のデータに対してデータを削除する際復旧テーブルに削除したデータを退避させて、顧客の要望があれば一定期間復旧できるような仕組みがあります。
データを削除する際、データのIDをそのまま復旧テーブルのIDにして退避しているのですが、そこで開発環境で削除が失敗する事象が発生しました。
調べてみると、退避テーブルに既に存在するIDでINSERTしようとしていて、失敗しているようでした。
なぜこの問題がおこるのかというと、MySQL5.7まではAUTO_INCREMENTがサーバー再起動でリセットされてしまう仕様が存在していて、最後にデータの削除を行った状態でインスタンスの再起動などが起こると発生していました。
具体例を説明すると、
- staffs.id:100を(物理)削除
- staffsテーブルのAUTO_INCREMENTが101にカウントアップ
- recoveryテーブルにstaff_id:100のデータが生成される
- インスタンスの再起動
- staffsテーブルのAUTO_INCREMENTが、テーブル内のidの最大値+1にセットされる
- 99+1=100で、staffs.AUTO_INCREMENT=100となる
- staffsテーブルのデータを削除する
- recoveryテーブルにINSERT時に
Duplicate entry ‘100’ for key ‘PRIMARY’が発生
このような手順を踏むと発生していた事象になります。
MySQL8からは、AUTO_INCREMENTのカウンター値はREDOログに書き込まれるようになったため、再起動してもAUTO_INCREMENTの値が変わることは無くなったので、今回のような事象は起こらなくなり、こちらが今回MySQL8にあげた一番大きなモチベーションになります。
事象が判明してから行ったこと
事象が判明した時点で、次のリリースまで時間があったので、以下の対応をまず行いました
- 既におかしくなっているデータが無いか
- originalのテーブルと、recoveryのテーブルで同じIDができていないか確認
- リカバリにIDが重複している場合は削除をできないようにhotfixで修正
- テーブルにINSERTしているところで、リカバリに同じIDが存在した場合は削除失敗させる
- 削除失敗を検知したらAUTO_INCREMENTを更新
3に関しては幸い本番環境では発生しなかったので良かったです。
一旦傷口を広げない対応を行った後、MySQL8にアップデートする検証を始めました
MySQL8へのアップデート前に行ったこと
- 公式ドキュメントを読む
- 破壊的な変更などないか一通り確認する
- アップデートの手順をまとめる
- blogなど他の方の知見を参考にする
- 開発環境にMySQL8のインスタンスを追加して検証
- インスタンスを追加したのち、開発環境のデータをコピー
- 全機能を探索的に触る
- qaテスト(システムを全体的に触るテスト)に混ぜてテスト
アップデート検証中に判明したことと対応
事前にドキュメントなど読んだ感じだと問題なさそうだと思っていたのですが、いざ検証を始めると以下2点の問題点が出てきました。
localのmysqlのテストが異様に遅くなった
M1Macの環境で、当初5分くらいで終わっていたのが、10分以上かかるようになってしまった。
mysqlを自前でビルドでしていたのが原因で時間がかかっていたのですが、公式イメージ(mysql:latest)をそのまま使用するようにし、事前にschemaを作成してそれをコンテナのエントリポイントにコピーして起動するようにして解消しました。
結果3分くらいでテストが終わるようになったので良かったです。
collationの話
MySQL5.7以前のutf8mb4のデフォルトのcollationはutf8mb4_0900_general_ciですが、MySQL8になるとデフォルトがutf8mb4_0900_ai_ciになり、それが原因で一部タイムアウトが発生するAPIが出てきてしまったのですが、 以下の理由から、CREATE TABLE時にCOLLATE=utf8mb4_general_ciを指定して回避するようにしました
- utf8mb4_0900_ai_ciだと、カタカナや小文字などが同一と判定される
- テーブル間でcollationが異なる場合、JOINのときに結合キーでインデックスが効かないためクエリが遅くなる
本番アップデート時に行ったこと
- 改めて事前にAUTO_INCREMENTがおかしくなっていないかを確認
- originalとrecoveryテーブルのAUTO_INCREMENTの値を比較し、recoveryのほうが大きくなっていたら修正する
- AUTO_INCREMENTの対応が終わったら、MySQLを8に上げる
- 本番環境で一通り触ってみて問題ないか確認
所感
恥ずかしながら今回始めてMySQL5系以前だと再起動時にAUTO_INCREMENTの値がリセットされることを知りました。
今まで開発してきたシステムが、UUIDでの実装が大半だったたのもあると思うのですが、改めてこういうこともあるんだなと勉強になりました。
はじめからMySQL8で開発 or MySQL5系以下でAUTO_INCREMENTを使っていなかったらこういうことにはならなかったと思うのですが、同じような事象に遭遇してしまった方の助けになると幸いです。
MySQL8の新機能に関しては、個人的に開発ではまだほとんど触れていないので、触れそうなタイミングでどんどん使っていきたいです。
Discussion