MySQL8.0から5.7へのレプリケーションがutf8mb4_0900_ai_ciのエラーで停止してしまう原因と対策
技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
なにかの役に立てば幸いです。
概要事象回避方法skip_character_set_client_handshakeを指定するダメだった方法とその理由スキーマやテーブル作成時にCollationを指定するdefault_collation_for_utf8mb4を指定する参考URL終わりに
概要
RDSのMySQL5.7から8.0にアップグレードする過程で、切り戻し環境用にMySQL5.7のレプリカを作成しておきたいが、レプリケーションエラーが発生して停止してしまうという相談がありました。
下記の2つの記事でも事例を紹介していますが、他にも回避する手段がないか確認します。
事象
| インスタンス名 | MySQL Version | 役割 |
| DB1 | MySQL8.0 | ソース |
| DB2 | MySQL5.7 | レプリカ |
変更しているパラメータ
ソースがMySQL8.0でレプリカがMySQL5.7という構成を組むと、下記のようなエラーがでてレプリケーションが停止します。
これはMySQL8.0からのデフォルト値がに変更になったためで、MySQL5.7には存在しない値のためエラーになってしまうというものです。
回避方法
skip_character_set_client_handshakeを指定する
上記ブログ記事にて紹介した方法がこちらです。
DB1(MySQL8.0)のパラメータグループで下記を有効化します。
collation_serverでutf8mb4_general_ciを指定しているから大丈夫だと思わせぶりですが、MySQL8.0のClientはスキーマ作成時やテーブル作成の実行時、Collationの指定がないとデフォルトでを指定してきます。
ですので、そのClientからの指定を無視するを有効にする必要があるというものでした。
Collationを指定すれば良いのかというとそういうことでもなく、それについては下記で解説します。
ダメだった方法とその理由
スキーマやテーブル作成時にCollationを指定する
デフォルトの指定がまずいならCollation指定したら行けるだろうというと思いますが、
なんとだめなのです。
COLLATEを指定してスキーマを作成してみます。
DB2側のslave statusを確認してみると、レプリケーションがエラーで停止しています。
DB1側のスキーマはちゃんと指定したCOLLATEで作成されています。
default_collation_for_utf8mb4を指定する
下位互換のためにという設定項目があります。
my.cnfでは設定できず、SET GLOBALかパラメータ永続化のSET PERSISTで設定する必要があります。
【2023年10月追記】下記のRDS環境でのSET PERSISTはいつからか実行できなくなったかもしれません。確認がとれないので格納させました。
2023年10月現在RDSでは使えないと思われる
RDSのパラメータグループの項目にあるの値を変更すると、SET GLOBALで設定したのと同じ状態になりRDSインスタンスを再起動すると値は初期化されます。
パラメータ永続化のSET PERSISTで設定してもRDSインスタンスを再起動すると初期値のにリセットされるRDSの仕様は注意が必要です。
RDSでこの値を永続化に近いかたちで運用するには、RDSのDBパラメータグループの
init_connectに下記設定を入れ、クライアントセッション開始時に設定を反映する方法があります。
この2つの方法両方ともいえることなのですが、
DB1(MySQL8.0)側のbinlogの中身を見てみると下記のようになっています。
CREATE DATABASEが実行される前に注目してみます。
ここでMySQL5.7に存在しないID 255のものが指定されており、レプリカがエラーで停止してしまう原因となっています。
このID 255が何者かは下記で確認できます。ですね。
ちなみに成功しているの場合のbinlogの中身は以下の通りです。
CREATE DATABASE実行前に注目してみると255が指定されておらず、45になっています。
ID 45を確認してみますと、です。
参考URL
終わりに
切り戻し用にMySQL5.7をレプリカとする構成にするとハマる罠について解説しました。
今後MySQL5.7がEOLを迎える2023年10月に向けてMySQL8.0バージョンアップの話が増えてくると思いますので、この記事がお役にたてば幸いです。
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。