識別子の大文字と小文字の区別を定義する設定値のlower_case_table_namesに起因するRDSのMySQL5.7.44からMySQL8系へのブルーグリーンデプロイ失敗についての挙動と対処法についてまとめました。
MySQL5.7.43〜44の標準サポートが近々終了するため、同じようにハマっている方の一助になれれば幸いです。
そもそもlower_case_table_namesとは？
これを読んでいる方はご存知かもしれませんが念の為。
lower_case_table_namesはMySQLの設定値で、この値により、下記の挙動が変化します。
- MySQLサーバのディスク上に作成されるディレクトリ名やファイル名(データベースおよびテーブル)を大文字で作成されるか小文字で作成されるか
- データベース、テーブルを参照する際に大文字と小文字を区別するか否か
前提として、MySQLはCREATE DATABASEやCREATE TABLE等のクエリを受け付け、データベースやテーブルを作成する際に、サーバ上のディスクにディレクトリやファイルとして作成します。
その際のクエリのテーブル名をそのまま踏襲するか、あるいは小文字に統一するかを決めます。
下記は私物のMacのmysqlディレクトリになります。
sample_todoデータベースに各テーブル名のファイルが存在していることが確認できると思います。
nonodakeiji@nonodakeijisMBP test % ls -l /opt/homebrew/var/mysql/sample_todo
total 1536
-rw-r----- 1 nonodakeiji admin 131072 1 19 2022 failed_jobs.ibd
-rw-r----- 1 nonodakeiji admin 114688 1 19 2022 migrations.ibd
-rw-r----- 1 nonodakeiji admin 131072 1 19 2022 password_resets.ibd
-rw-r----- 1 nonodakeiji admin 147456 1 19 2022 personal_access_tokens.ibd
-rw-r----- 1 nonodakeiji admin 131072 1 19 2022 t_todo_tasks.ibd
-rw-r----- 1 nonodakeiji admin 131072 1 19 2022 users.ibd
データベース、テーブルを参照する際に大文字と小文字を区別するか否かについては、例えば大文字のHOGEテーブルがあった場合、大文字小文字を区別しないように定義していた場合に成功するクエリは以下になります。
SELECT * FROM HOGE;
SELECT * FROM hoge;
実際に定義する設定値は0〜2になります。
詳細については少し複雑なので、公式のドキュメントをご確認ください。
https://dev.mysql.com/doc/refman/8.0/ja/identifier-case-sensitivity.html
なぜlower_case_table_namesの値でRDS for MySQLブルーグリーンデプロイが失敗するのか？
設定値がデフォルトの0のままで、ブルーグリーンデプロイをすれば問題なかったのですが、どうやら過去に1に設定された形跡がありました。
おそらくWindows環境などで開発をしており、大文字と小文字のテーブル名が混在した状態で開発することの煩わしさを解消するために1に設定することで、大文字小文字を気にせず開発に集中する意図があったのだと思います。
実際にRDS for MySQL5.7から8系へのブルーグリーンデプロイを行った際に起きたエラーは下記になります。
lower_case_table_namesが変更できない旨のエラー
下記のエラーメッセージが表示されました。
The parameter value for lower_case_table_names can't be changed for MySQL 8.0 DB instances.
文面通りですが、MySQL8ではlower_case_table_namesを変更できませんと怒られました。
これはブルー側のlower_case_table_namesの値が1、グリーン側を0として設定した場合に表示されたエラーになります。
どうやらMySQL8ではサーバーの初期化時にのみ構成できるようで、後からの変更は禁止されていると公式ドキュメントに記載がありました。
また、AWSのドキュメントの方にもパラメータを変更出来ないことと、マスターDBと同じパラメータ値であることが言及されています。
※なぜここでマスターDBが出てくるかは後述します。
パラメータグループがバージョン 8.0 MySQL DB インスタンスに関連付けられている場合、lower_case_table_names パラメータを変更できません。
リードレプリカでは、常にマスター DB インスタンスと同じ lower_case_table_names パラメータ値を使用する必要があります。
レプリケーションエラー
グリーン側のlower_case_table_namesの値もブルー側と同じ1に設定し直して再度ブルーグリーンデプロイしたところ、次はSQLエラーとして下記のようなログが表示されました。
Read Replica Replication Error - SQLError: 1146,
reason: Error 'Table 'hoge_db.HOGA_TBL' doesn't exist' on query.
レプリケーションのクエリでHOGA_TBLは存在しないと怒られています。
なぜここでレプリケーションが出てくるのかというと、エラーメッセージを見ても分かる通り、ブルーグリーンデプロイは内部的にはブルーとグリーンの同期を取るため、グリーンに対してレプリケーションを行うことで成立しているようです。
公式ドキュメントにも記載がありますが、グリーン側はブルー側のリードレプリカ(昔で言うSlave)のため、デプロイ後は読み取り専用としてテストを行うことを推奨しています。
話を戻して、なぜレプリケーションエラーが出たのかというと、プロジェクトのソースコードを見るとテーブル名やカラム名に大文字小文字が混在しており、どうやら大文字で参照したテーブル名のクエリに対してエラーが吐かれていたようです。
この内容もAWSのドキュメントに記載がありました。
パラメータグループが 8.0 より低いバージョンの MySQL DB インスタンスに関連付けられている場合、lower_case_table_names パラメータを変更しないことをお勧めします。これを変更すると、ポイントインタイムリカバリのバックアップとリードレプリカの DB インスタンスで不整合が生じることがあります。
MySQL5.7でlower_case_table_namesを1に変更したため、リードレプリカDBインスタンス、この場合はグリーン側のMySQL8.0.37インスタンスで不整合が発生したことになります。
解決策
既にMySQL5系でlower_case_table_namesを1に変更して運用していた場合の解決策としては、ソースコードのデータベース、テーブル名をすべて小文字に統一し、グリーン側のlower_case_table_namesも1に設定して再度ブルーグリーンデプロイを行うことです。
こうすることで、1だった場合でも小文字のテーブル名に対して大文字でクエリを発行することはなくなるため、レプリケーションの不整合が発生しなくなるはずです。
ブルーグリーンデプロイが成功したとしても、稼働中も同期が取られる(レプリケーションされる)ため、安心はできません。
同様のエラーが発生した場合はまだどこかで大文字小文字の区別をせずにクエリを投げる輩が居ることになります。
アプリケーションだけでなくバッチジョブ等も確認しておいた方が良さそうです。
ブルーグリーンデプロイ後の注意点
さらっと前述しましたが、大事なことなのでもう一回大きな声で言っておこうと思います。
切り替え前のグリーン側の書き込みはやめましょう
ブルー側がMaster、グリーン側がリードレプリカ(Slave)の関係なので、不整合が発生する原因になります。
lower_case_table_namesを含む設定値も変更しない方が良さげです。
(lower_case_table_namesは変更できないとエラーが出ると思いますが)
まとめ
- lower_case_table_namesの値はデフォルトのまま運用した方が良い
- ブルー側のlower_case_table_namesの値とグリーン側のlower_case_table_namesの値を同じにする
- 内部的にはレプリケーションを行うため、切り替え前のグリーン側では書き込みを行わない
テストを行うのに書き込みを行わないのは現実的ではないので、ソースコードの修正を行ったらスナップショットを作成し、そこで時間をかけて検証するのが良さそうですね。
最後まで読んで頂きありがとうございましたm(_ _)m