【MySQL】非推奨の設定を使わないようにちゃんとメンテしよう

起こったこと

先日、外部のAPIを呼び出してDB（MySQL）へデータのインポートを行うバッチが以下のログをはいて突然失敗した。

### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\xA9\xB5\xE3\x81...' for column ...

調べたこと

今まで正常稼働していたバッチが突然失敗し、全く見当もつかなかったためログをそのままAIエージェントに投げてみたら次のような回答が。

\xF0\x9F\xA9\xB5はUTF-8の4バイト文字=絵文字で、Unicodeに直すとU+1FA75⇒💙(水色/ライトブルーのハート)を表します

確かに絵文字でのデータインポートは試したことがないため、絵文字が原因だろうと察したが、当時は失敗の理由が分からなかった。

実際に手元でAPIを実行してみると、確かに💙(水色/ライトブルーのハート)がレスポンスには含まれていた。

MySQLの文字セット

MySQLにおいて文字セット（charset）はDB操作時に利用される文字コードと照合順序に関する設定のこと。
使われる文字セットはその利用場面ごとに細かく設定することができ、
例えばクエリ実行時、結果の出力、テーブルやカラムごとに格納されるデータ自体などにも指定ができる（https://dev.mysql.com/doc/refman/8.0/ja/charset-connection.html）。

弊プロジェクトでは少なくとも下記の文字セットの設定がutf8mb3になっていた

• character_set_client（クエリ実行時に適用される文字セット）
• character_set_results（クエリの出力結果に適用される文字セット ）
• テーブルのデフォルト文字セット

utf8mb3とutf8mb4

MySQLで指定できる文字セットにはUTF8があるが、その中でもutf8mb3とutf8mb4に分かれる。
それぞれ何が違うかというと、1文字あたりに使われるバイト数が3もしくは4という点だ。
その名の通りutf8mb3=3バイト、utf8mb4=4バイトで1文字を表現しており、当該のDBではほとんどの設定でutf8mb3を採用していた。
これが今回の事象とどう関係するかというと、いわゆる寿司ビール問題🍣🍺というものらしい（筆者は知らなかった）。
寿司ビール問題はざっくりいうと1文字あたりのバイト数の違いによって、絵文字がうまく区別できなかったり、これに準じる問題が生じるというものだ。
詳しくは寿司ビール問題で検索してもらえればと思うが、とにかく絵文字は4バイトで表現されており、1文字を3バイトで表現しているutf8mb3では絵文字は基本的に扱えないということだった。

そもそもなぜutf8mb3だったのか

MySQL8.0からMySQLではutf8mb3の文字セットでの利用が非推奨になっている（https://dev.mysql.com/doc/refman/8.0/ja/charset-unicode-utf8mb3.html）。
では最初から絵文字を扱えるようにutf8mb4を採用していれば今回の問題は起こらなかった、と言えばそうなのだが、長年MySQLを運用している弊プロジェクトでは当たり前のようにutf8mb3が使われていた。
弊プロジェクトのDBは当時MySQL5.7でDBをクラウドにプロビジョニングされ、継続的に運用されてきた。
DB立ち上げ当初に文字セットを意図的にutf8mb3に設定したのかは分からなかったが、少なくともMySQL8.0にエンジンバージョンをアップグレードしたり、もろもろの対応を継続的に行っていたのにも関わらず文字セットは非推奨のutf8mb3のままだった。

加えて、MySQL8.0へのアップグレードはAzureのコンソール上から行ったのだが、おそらく裏側でutil.checkForServerUpgrade()のコマンドが走り、以下のようにちゃんと警告は出ていたため、これを見逃さずに対応すれば問題にはならなかった。

Usage of utf8mb3 charset

Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use 
  utf8mb4 instead, for improved Unicode support.

解決方法

結論から言うと、下記のDDLとコマンドを実行し解決した。

ALTER TABLE #{対象のテーブル名}
  MODIFY #{対象のカラム名} varchar(255)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

こうすることで特定カラムのみ文字セットを変更し絵文字を格納できるようになり、INSERTおよびSELECT時にも絵文字を扱えるようになる。
※あくまで最小限の変更にとどめているが、utf8mb3自体が非推奨なため可能であれば他の設定も修正するのが望ましい。

このことから得た教訓

これまでずっと動いていたアプリケーションが突然失敗すると、まず外的な要因を疑ってしまいがちですが、今回は元を正すと自分たちの保守の目が行き届いていなかったのだと反省しました。
なので当たり前かもしれませんが、

• 公式が非推奨にしているものには理由がある。特に理由がなければ見逃さすに対応しよう！
• 古いシステムは古い設定を使いまわしている可能性が高いため、定期的にメンテして最新化しよう！

MySQLであればエンジンをアップグレードする前にutil.checkForServerUpgrade()をして非推奨設定のチェックをした方がよさそうです(https://dev.mysql.com/doc/mysql-shell/8.0/ja/mysql-shell-utilities-upgrade.html）。

弊プロジェクトは保守の側面が大きいため、この辺りは今後も気を付けていければと思います。