MySQL5.7系からMySQL8.0系へのアップグレードにおける破壊的変更への対応
はじめに
こんにちは、2021年4月にFinatextに新卒で入社し、まもなく3年目になるToshiya Matsuzakiです。サーバーサイドエンジニアとして、AWSでのインフラ構築とGoによるシステム開発を行っています。
先日、MySQL5.7系互換であるAmazon Aurora v2を使用していたリリース前のプロダクトのデータベースを、MySQL8.0系互換であるAmazon Aurora v3にアップグレードした際に、予期せぬバグが発生しました。調べたところ、MySQL5.7から8.0へのアップグレードに含まれていた破壊的変更点によるものでした。
そこで、今回のバグから得た学びと対応方法について書きたいと思います。現在稼働しているシステムに対して、MySQL5.7系から8.0系にアップグレードをすることを検討していて、外部キー制約を使っている人の参考になれば幸いです。
MySQL5.7系→8.0系の破壊的変更点
外部キー制約に違反するようなクエリを書いた時のエラーの挙動が、MySQL5.7系とMySQL8.0系とで異なります。
具体的には、以下の通りMySQL8.0系にてエラーが分岐するようになります。
NO_REFERENCED_ROW系エラーコードの挙動
レコードをINSERTする際にチェックに引っかかる場合のエラーが以下のようになります。
ROW_IS_REFERENCED系エラーコードの挙動
レコードをDELETEする際にチェックに引っかかる場合のエラーが以下のようになります。
破壊的変更の影響を受ける条件
上記のことから、エラーのハンドリングをするときに、エラーコードでハンドリングしていた場合かつ権限を絞ったDBユーザーを使用した場合に、この破壊的変更の影響を受けることになります。
後述しますが、2022年9月にAmazon AuroraでMySQL5.7系互換であるv2からMySQL8.0系互換であるv3へのインプレースアップグレードがサポートされ、既に運用しているものについても簡単にアップグレードできるようになりました。しかし、実際にアップグレードする際には、この破壊的変更点の影響を受ける実装になっていないか、気をつける必要があります。
補足：ドキュメントの比較
補足として、当該部分についてのMySQLのドキュメントを、バージョン別に比べてみました。
- MySQL5.7のマニュアル
ER_NO_REFERENCED_ROW_2(1452) and ER_ROW_IS_REFERENCED_2(1451) error messages for foreign key operations expose information about parent tables, even if the user has no parent table access privileges.
To hide information about parent tables, include the appropriate condition handlers in application code and stored programs.権限がなくても、エラーメッセージにより親テーブルの情報が見れてしまうのでアプリケーション側でちゃんとハンドリングして、と書いてあります。つまり、ER_NO_REFERENCED_ROWの場合は一律で1452, ER_ROW_IS_REFERENCEDの場合は一律で1451のエラーが返ります。
- MySQL8.0のマニュアル
If a user has table-level privileges for all parent tables, ER_NO_REFERENCED_ROW_2(1452) and ER_ROW_IS_REFERENCED_2(1451) error messages for foreign key operations expose information about parent tables.
If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead (ER_NO_REFERENCED_ROW(1216) and ER_ROW_IS_REFERENCED(1217)).親テーブルへの参照権限によってエラーメッセージの情報量が変わるため、エラー自体が変わります。つまり、権限次第で同じエラーでもエラーコードが変わり、ER_NO_REFERENCED_ROW関連の場合は1452と1216に、ER_ROW_IS_REFERENCED関連の場合は1451と1217に分岐することになります。
バグ発生の経緯
ここで、今回のバグ発生の経緯についても触れておきたいと思います。
2022/9/26に、Amazon Aurora で MySQL 5.7から8.0へのインプレースアップグレードがサポートされました。
ちょうどリリース前のサービスを開発していたところだったので、リリース前にアップグレードしてしまおうと考え、開発環境でインプレースアップグレードを利用してMySQL8.0系であるv3にアップグレードしました。
手順自体は上記のサポートページに書いてあるとおり3クリック程度で操作でき、ダウンタイムも30分くらいで入れ替えが完了し、特に問題なく動いて移行は楽々でした。
実装はGoで、mysqlerrを用いてエラーハンドリングを行っていて、ForeignKeyErrorは1452, 1451としてハンドリングをしていました。また、CI/CDのテストにおいては権限をrootユーザーで行っていました。
しかし、webアプリからAPI経由でDBを使うユーザーには権限を絞っていたため、前述した破壊的変更点の影響を受け、MySQL8.0系においてMySQL側でのエラーハンドリングが増えてエラーコードが1216, 1217と変化し、ハンドリング漏れによりバグを生んでしまいました。
破壊的変更点のデモンストレーション
以下のリポジトリに、実際にそれぞれのMySQLバージョンで挙動を比較できるよう、検証環境の構築とコマンド一覧を公開しました。
上記の中から重要な部分のみピックアップして紹介します。
ユーザー作成
restrictionユーザーはアプリ用のユーザーを想定し、最低限のDB操作のみ可能な権限を付与します。
adminユーザーは例えばmigration用に使用する想定で、GRANT ALL PRIVILEGESにより全権限を付与しています。
CREATE DATABASE restriction_test;
CREATE USER 'restrict'@'%' IDENTIFIED BY 'restrict';
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';
GRANT SELECT,UPDATE,INSERT,DELETE on `restriction_test`.* to 'restrict'@'%';
GRANT ALL PRIVILEGES on `restriction_test`.* to 'admin'@'%';検証結果の確認
MySQL5.7系では権限による影響はなく、MySQL8.0系では以下のような変化が起きました。特に権限を絞ったrestrictユーザーで、エラーコードの変化とエラーメッセージの簡素化が確認できました。
-- adminユーザー
USE restriction_test;
INSERT INTO threads(user_id, title) values("notFoundUser", "testTitle");
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`restriction_test`.`threads`, CONSTRAINT `fk_threads_user_id_users_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))
DELETE FROM users where id = "test";
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`restriction_test`.`threads`, CONSTRAINT `fk_threads_user_id_users_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))-- restrictユーザー
use restriction_test;
INSERT INTO threads(user_id, title) values("notFoundUser", "testTitle");
> ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
DELETE FROM users where id = "test";
> ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails対応方法
対応としては、実装側でエラーハンドリングを修正する方法を取りました。DBユーザーの権限を増やす方法も考えられますが、それでは本末転倒なため棄却しました。
破壊的変更の影響を事前検知するために
今回のプロダクトでは、MySQLのDocker Imageを用いてDBの自動テストを動かしており、事前にMySQL8.0のDocker Imageに差し替えてテストをクリアしていることを確認してからアップグレードを行いました。しかし、アプリを実際に使用するユーザーが持つ権限ではなく、admin権限をつけたユーザーで自動テストを稼働させていたため、テスト段階で破壊的変更の影響を事前検知できず、開発環境にてバグというかたちで検出することになりました。
開発初期段階にCI/CDの仕組みを整えて以降、DBのユーザー作成部分について再確認していなかったことが、今回のバグを生んだ根本原因と考えています。
自動テストの内容も定期的に見直してみて、ライブラリや言語のバージョンなどが実際の環境と同じにできているか、同じでない部分はいつ確認できるかを予め調査しておくことが大切です。出来る限り環境を揃えておくことが、想定外のバグや挙動の早期検出に役立ちます。
最後に
自分の所属するFinatextグループでは、エンジニアを募集しています。以下のリンクが、事業内容やエンジニアの業務内容についてのまとめになっているので、興味がある方はぜひチェックしてみてください！
▼Finatext 開発チーム 採用情報
▼募集一覧
▼カジュアル面談の応募フォーム
https://forms.gle/r69LwG3cLAjiGvY66
