こんにちは。 KINTO テクノロジーズの DBRE チーム所属の @hoshino です。

はじめに

Aurora MySQL 2系（MySQL 5.7互換）から3系（MySQL 8.0互換）へのメジャーバージョンアップを、19クラスタ・46スキーマ規模のメインシステムで実施しました。

このバージョンアップで最も苦労したのが COLLATION の問題です。

Aurora MySQL 3系ではデフォルト COLLATION が utf8mb4_0900_ai_ci に変わりますが、既存システムでは、検索条件、ORDER BY、ユニーク制約、JOIN、帳票、バッチ処理などがutf8mb4_general_ci の比較・ソート挙動を前提に動いています。

utf8mb4_0900_ai_ci への変更は単なる DB 設定変更ではなく、アプリケーション仕様の変更に近いため、今回は互換性維持を優先し、utf8mb4_general_ci を維持したまま移行する方針を取りました。

しかし、Aurora MySQL 3系では default_collation_for_utf8mb4 が utf8mb4_0900_ai_ci 固定で、サーバー側で変更する手段が用意されておらず、明示的に指定しないとセッションのデフォルトが utf8mb4_0900_ai_ci になってしまいます。そのため、utf8mb4_general_ci を維持するために以下の対策を実施しました。

今回実施した対策

• SCHEMA / TABLE / COLUMN / VIEW / ROUTINE / TRIGGER / EVENT の COLLATION を統一
• 接続設定・SQL クエリで COLLATION を明示指定することで COLLATION を制御
• 意図しない COLLATION が設定されないように information_schema を使った Slack 自動通知によるチェック体制の整備

本記事では、これらの対策の詳細について説明します。

背景

KINTO テクノロジーズの DBRE チームでは、Aurora MySQL 2系（MySQL 5.7互換）から3系（MySQL 8.0互換）へのメジャーバージョンアップを進めてきました。

弊社では多数のクラスタを運用していますが、今回対象となったのは複数プロダクトが共有するメインシステムの DB です。

このメインシステムは少し特殊な構成になっています。

1つの環境に対して 2つの Aurora クラスタが存在しており、複数プロダクトがこの2クラスタを共有して利用しています。

両クラスタは密接に連携しているため、片方だけバージョンアップするわけにはいかず、同時に移行する必要がありました。

対象規模は dev・stg・prod などの全環境を合計して 19クラスタ・46スキーマ・56ユーザー にのぼります。

構成を図にすると以下のようになります。

この移行で最も苦労したのが COLLATION の問題でした。

Aurora MySQL 3系（MySQL 8.0）のデフォルト COLLATION は utf8mb4_0900_ai_ci です。

一方、既存のデータベースは utf8mb4_general_ci で運用されていました。

システム全体を utf8mb4_0900_ai_ci に切り替えるという選択肢もゼロではありませんでしたが、COLLATION の変更はアプリケーションの挙動に直接影響します。

utf8mb4_general_ci と utf8mb4_0900_ai_ci は、どちらも大文字・小文字を区別しない COLLATION ですが、内部のソートアルゴリズムが異なります。

utf8mb4_0900_ai_ci は Unicode Collation Algorithm（UCA 9.0.0）に準拠しており、= 演算子による比較結果や ORDER BY のソート順が utf8mb4_general_ci とは異なるケースがあります。

既存のアプリケーションが utf8mb4_general_ci の挙動を前提としている場合、COLLATION を切り替えただけで検索結果やソート順が変わり、意図しない不具合につながる可能性があります。

そうなると各プロダクト側でも影響調査や改修が必要になります。

複数プロダクトが共有しているデータベースであるため、その改修範囲は広く、プロダクト側の開発コストも大きくなります。

プロダクト側の負担を最小限にするためにも、utf8mb4_general_ci を維持したままバージョンアップするという方針を選択しました。

Illegal mix of collations に対する対応

utf8mb4_general_ci を維持する方針で進めるにあたって直面したのが、Illegal mix of collations というエラーです。

このエラーは、テーブル側の COLLATION とセッション側の COLLATION が混在した状態でクエリを実行したときに発生します。Aurora MySQL 3系では、サーバー側でデフォルト COLLATION を変更する手段がないため、何も対策しないとこのエラーが発生しやすい構造になっています。

MySQL 8.0 には default_collation_for_utf8mb4 というシステム変数があります（MySQL 公式: Server System Variables）。

これは CHARACTER SET utf8mb4 を指定して COLLATE を省略したとき、どの COLLATION がデフォルトで使われるかを決める変数で、デフォルト値は utf8mb4_0900_ai_ci です。

通常の MySQL であれば、SET PERSIST default_collation_for_utf8mb4='utf8mb4_general_ci'; を実行することでこの値を変更できますが、Aurora MySQL ではこの変数を変更する手段がありません。

理由としては SET PERSIST は Aurora では使えず、パラメータグループにもこの設定項目が存在しないためです。

この制約により、collation_connection を指定せずに接続した場合、セッションのデフォルトが utf8mb4_0900_ai_ci になってしまいます。

影響は実行するクエリだけではありませんでした。

VIEW や ROUTINE（ストアドプロシージャ・ファンクション）は、作成時のセッションの character_set_client や collation_connection が定義に依存するため、utf8mb4_0900_ai_ci のセッションで VIEW を作成すると、その VIEW 自体が utf8mb4_0900_ai_ci を持ってしまいます。

後からセッションの COLLATION を変えても、すでに作成された VIEW の定義は変わりません。

さらに、クエリの中で COLLATION が動的に決まる箇所にも影響します。

たとえば UNION や CAST 関数を含むクエリでは、TABLE 側の COLLATION（utf8mb4_general_ci）とセッション側の COLLATION（utf8mb4_0900_ai_ci）が混在してエラーが発生します。

例1：CAST 関数を使った JOIN

SELECT *
FROM table_a AS t1
JOIN table_b AS t2
  ON CAST(t1.id AS CHAR) = t2.code;
--    ^^^^^^^^^^^^^^^^^^   ^^^^^^^
--    utf8mb4_0900_ai_ci   utf8mb4_general_ci
--   （セッションのデフォルト）（テーブルの COLLATION）

CAST(t1.id AS CHAR) はセッションの collation_connection に従うため、Aurora のデフォルトである utf8mb4_0900_ai_ci になります。一方、t2.code はテーブル定義の utf8mb4_general_ci のままです。この2つを = で比較するため、COLLATION の不一致が発生します。

例2：UNION で異なる COLLATION が混在

SELECT name FROM table_a
--     ^^^^
--     utf8mb4_general_ci（テーブルの COLLATION）
UNION
SELECT CAST(id AS CHAR) FROM table_b;
--     ^^^^^^^^^^^^^^^^
--     utf8mb4_0900_ai_ci（セッションのデフォルト）

UNION は各 SELECT の COLLATION を統一する必要がありますが、上記のように一方が utf8mb4_general_ci、もう一方が utf8mb4_0900_ai_ci になると統一できず、エラーになります。

どちらのクエリも、最終的には以下のエラーになります。

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and
(utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

これを防ぐには、接続時に COLLATION を明示的に指定するか、SQL 文の中で COLLATE 句を明示する方法があります。

方法1：接続時に COLLATION を指定する

-- MySQL クライアントから接続する場合
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

# JDBC URL での指定例
jdbc:mysql://host:3306/mydb?connectionCollation=utf8mb4_general_ci

方法2：SQL 文の中で COLLATE 句を明示する

UNION や CAST など動的に COLLATION が決まる箇所に、直接 COLLATE 句を付与する方法です。

-- UNION での指定例
SELECT name COLLATE utf8mb4_general_ci FROM table_a
UNION
SELECT name COLLATE utf8mb4_general_ci FROM table_b;

-- CAST での指定例
SELECT CAST(column AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci
FROM table_a;

しかし、接続時の COLLATION 指定やクエリへの COLLATE 句付与は、あくまで移行後の運用で問題を防ぐための対策です。

移行するにあたり、移行前に Aurora 2系側の COLLATION を統一しておく必要がありました。

注意事項

SET NAMES utf8mb4（COLLATE 句を省略）を実行すると、それまでに設定していた collation_connection が破棄され、Aurora MySQL 3系のデフォルトである utf8mb4_0900_ai_ci に戻ってしまいます。

SET SESSION collation_connection = 'utf8mb4_general_ci';
-- ↑ ここで utf8mb4_general_ci になる

SET NAMES utf8mb4;
-- ↑ COLLATE 句がないため utf8mb4_0900_ai_ci に戻ってしまう

ORM やアプリケーションフレームワークが内部で SET NAMES utf8mb4 を発行する実装も存在するため、実際に発行されるクエリのログを確認し、暗黙の SET NAMES が含まれていないかを把握しておく必要があります。

SET NAMES を使う場合は、必ず COLLATE 句までセットで指定するのが確実です。

移行手順と事前準備

移行方法

今回の移行は mysqldump などで論理ダンプを取得し、それをインポートする方式を採用しました。

Aurora MySQL 2系から3系への移行方式としては、Blue/Green デプロイやインプレースアップグレードといった選択肢もありますが、今回は以下の理由からダンプ・インポートを採用しました。

• COLLATION の事前調整で DDL 変更が必要だった
  • VIEW や ROUTINE の定義を書き換えて再作成する必要がありましたが、Blue/Green デプロイでは DDL 変更が Green 環境へのレプリケーション中断を引き起こすリスクがあり、レプリケーションとの互換性検証コストが高いと判断しました
• ダンプ・インポート方式の社内実績が豊富だった
  • 弊社では全環境で数百の DB クラスタが存在しており、そのほとんどをダンプ・インポート方式で移行しました
  • そのため、今回のような複数プロダクトが共有する大規模システムの移行において、Blue/Green デプロイなどの実績のない手法を採用するリスクは取れませんでした

安全を最優先に考えた結果、確実にコントロールできるダンプ・インポート方式を選択しました。

ダンプ・インポート時の COLLATION エラー

ダンプ・インポート方式で移行を進めたところ、COLLATION の不整合によるエラーが発生しました。

Aurora 2系側の COLLATION が utf8mb4_general_ci に統一されていない状態でダンプを取ってインポートすると、VIEW の作成時に Illegal mix of collations エラーとなり、移行そのものが失敗します。

そのため、以下の手順で移行を実施しました。

1. Aurora 2系側で COLLATION を utf8mb4_general_ci に統一する
2. その状態でダンプを取得する
3. Aurora 3系にインポートする

事前作業の内容

事前作業では SCHEMA / TABLE / COLUMN / VIEW / ROUTINE のすべてに手を入れる必要がありました。

対象となるのは2クラスタ × 全環境（dev・stg・prod 等）にまたがる数十スキーマです。複数プロダクトが共有しているため、各スキーマの VIEW や ROUTINE がどのプロダクトに属するかを把握し、プロダクトチームと調整しながら進める必要がありました。

調整箇所は全環境合計で数千にのぼり、環境ごとにリストを作成し、プロダクトチームにレビューを依頼し、反映前に最終チェックを行うというサイクルを、すべての環境に対して繰り返し実施しました。

以下、具体的な調整方法をオブジェクトの種類ごとに説明します。

SCHEMA / TABLE / COLUMN の調整

SCHEMA・TABLE・COLUMN は ALTER 文で COLLATION を utf8mb4_general_ci に変更しました。

-- SCHEMA
ALTER DATABASE ${schema} CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- TABLE
ALTER TABLE ${table} CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci';

-- COLUMN
ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci';

VIEW / ROUTINE / TRIGGER / EVENT の調整

一方、VIEW・ROUTINE・TRIGGER・EVENT は ALTER では対応できないため、定義を書き換えて再作成する必要がありました。

定義内の文字コード・COLLATION を一括で置換してから CREATE OR REPLACE VIEW で再作成するアプローチを取りました。主な置換パターンは以下の通りです。

"utf8 "              → "utf8mb4 "
"utf8_general_ci"    → "utf8mb4_general_ci"
"utf8mb4_0900_ai_ci" → "utf8mb4_general_ci"
"utf8mb4_unicode_ci" → "utf8mb4_general_ci"
"charset utf8mb4) AS" → "charset utf8mb4) COLLATE utf8mb4_general_ci AS"

最後のパターンは CAST 関数の末尾に該当します。CAST(column AS CHAR) のような式では COLLATION が動的に決まるため、明示的に COLLATE を付与する必要がありました。

ただし、文字列置換だけでは対応しきれないケースも存在しました。

CAST 関数の使い方が複雑であったり、置換パターンに収まらない定義を持つ VIEW がいくつかありました。

こうした箇所は information_schema で COLLATION の状態を一つひとつ確認しながら、手動で定義を修正して再作成しました。

-- 置換漏れがないか確認するクエリ
SELECT table_schema, table_name,
       character_set_client, collation_connection
FROM information_schema.views
WHERE collation_connection != 'utf8mb4_general_ci'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

この確認を怠ると、一見すると置換が完了しているように見えても utf8mb4_0900_ai_ci が残ったままの定義が存在する場合インポート時にエラーが発生するか、移行後に Illegal mix of collations となってしまいます。

移行後に発生したインシデント

事前作業で Aurora 2系の COLLATION を統一し、Aurora 3系への移行を完了しました。

しかし移行後、プロダクトから Illegal mix of collations のエラーが発生したとの報告がありました。

発生したエラー

エラーの内容は以下の通りです。

1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and
(utf8mb4_general_ci,IMPLICIT) for operation '='

Aurora 2系では問題なく動作していた機能が、Aurora 3系への移行後にエラーとなっていました。

原因の調査

まず、エラーが発生しているクエリの調査を行いました。

問題のクエリには CAST 関数を使った JOIN が含まれていました。

以下は同様の構造を持つ例です。

-- 例：CAST 関数を使った JOIN で COLLATION の不一致が発生するケース
SELECT *
FROM table_a AS t1
LEFT JOIN table_b AS t2
  ON CAST(t1.id AS CHAR) = t2.code;

CAST(... AS CHAR) の結果にはセッションの collation_connection が適用されます。

該当のアプリケーションでは collation_connection が指定されておらず、Aurora のデフォルトである utf8mb4_0900_ai_ci が適用されていました。

その結果、CAST 関数の結果は utf8mb4_0900_ai_ci となり、テーブル側の utf8mb4_general_ci と混在して Illegal mix of collations が発生していました。

対処方法

対処として、アプリケーションの DB 接続設定に collation_connection=utf8mb4_general_ci を追加しました。

# 接続文字列に COLLATION 設定を追加
mysql+mysqlconnector://user:password@host/dbname
  ?init_command=SET SESSION collation_connection=utf8mb4_general_ci

init_command は接続確立直後に実行されるため、以降のクエリでは collation_connection が utf8mb4_general_ci の状態で処理されます。

collation_connection が utf8mb4_general_ci になることで、CAST や UNION のようにセッションの COLLATION 値で動的に COLLATION が決まる箇所も utf8mb4_general_ci に揃えられ、テーブル側との不一致を防げます。

この変更をリリースした後、エラーは解消し、現在は安定稼働しています。

COLLATION の定期チェックと自動通知

一度問題を修正しても、新しい VIEW が作成されたりアプリケーションが更新されたりすると、同様の問題が再発する可能性があります。

本番環境でエラーが発生してから気づくのではなく、開発段階で COLLATION の不一致を早期に検知するために、全環境の COLLATION の状態を定期的にチェックし、意図しない COLLATION が設定された場合に自動で通知する仕組みと、手動で現状のCOLLATIONの状態を確認できる仕組みを構築しました。

自動化の仕組み

仕組みの全体像は以下の通りです。

1. 日次で COLLATION 情報を自動取得

COLLATION をチェックするクエリを CLI コマンドとして実装しました。

このコマンドを全クラスタに対して日次で自動実行し、取得結果を JSON 形式で S3 に保存しています。

2. 期待する COLLATION との照合と Slack 通知

EventBridge で決まった時間に、S3 上の JSON データを精査します。

DynamoDB にあらかじめ登録してある「期待する COLLATION」と照合し、意図しない COLLATION が検出された場合は Slack の専用チャンネルに自動通知します。

3. CLI による手動チェック

CLI コマンドは手動でも実行できます。

新規 TABLE 作成後やトラブルシューティング時など、任意のタイミングで特定のクラスタの状態を確認したい場合に使用しています。

COLLATION チェックで実行しているクエリ

自動化の仕組みの中で各クラスタに対して実行しているクエリは、information_schema を使って utf8mb4_general_ci 以外の COLLATION が混入していないかを検出するものです。対象が SCHEMA・TABLE・COLUMN・VIEW・ROUTINE・TRIGGER の6種類です。

-- SCHEMA の COLLATION 確認
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- TABLE の COLLATION 確認
SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_collation != 'utf8mb4_general_ci'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- COLUMN の COLLATION 確認
SELECT table_schema, table_name, column_name, collation_name
FROM information_schema.columns
WHERE collation_name IS NOT NULL
  AND collation_name != 'utf8mb4_general_ci'
  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- VIEW の collation_connection 確認
SELECT table_schema, table_name,
       character_set_client, collation_connection
FROM information_schema.views
WHERE collation_connection != 'utf8mb4_general_ci';

-- ROUTINE の COLLATION 確認
SELECT routine_schema, routine_name, routine_type,
       collation_connection, database_collation
FROM information_schema.routines
WHERE collation_connection != 'utf8mb4_general_ci';

-- TRIGGER の COLLATION 確認
SELECT trigger_schema, trigger_name,
       collation_connection, database_collation
FROM information_schema.triggers
WHERE collation_connection != 'utf8mb4_general_ci';

今後のAuroraバージョンアップ（Aurora MySQL 4）に向けて

MySQL 8.4 で default_collation_for_utf8mb4 を SET PERSIST で変更すると、以下の deprecated 警告が表示されます。

mysql> SET PERSIST default_collation_for_utf8mb4='utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                |
+---------+------+--------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. |
+---------+------+--------------------------------------------------------------------------------------------------------+

「将来のリリースで read-only にする」と警告されていることから、今後この変数による COLLATION の制御はさらに難しくなる可能性があります。COLLATION を確実に制御するためには、SCHEMA・TABLE・COLUMN・VIEW・ROUTINE のすべてで明示指定し、information_schema で定期的にチェックするアプローチが引き続き有効です。

Aurora MySQL のリリースカレンダーによると、Aurora MySQL 3 のメジャーバージョン標準サポートは 2028年4月30日 までとなっています。その後は次のメジャーバージョンへの移行が必要になるため、今回整備した定期チェックの仕組みや CLI コマンドを次のバージョンアップでもそのまま活用できるようにしておくことが重要だと考えています。

まとめ

• Aurora MySQL 3系では MySQL 8.0 互換となり、デフォルト COLLATION が utf8mb4_0900_ai_ci に変わったことで、既存 DB の utf8mb4_general_ci と混在しやすくなった。これが今回の苦労の根本原因
• Aurora MySQL では SET PERSIST で default_collation_for_utf8mb4 を変更できないため、サーバー側で utf8mb4 の デフォルト COLLATION を制御できない
• 接続時に collation_connection を明示指定しないと、セッションのデフォルトが utf8mb4_0900_ai_ci となり Illegal mix of collations が発生する可能性がある
• ダンプ・インポートで移行する場合、移行前に Aurora 2系側の SCHEMA / TABLE / COLUMN / VIEW / ROUTINE の COLLATION を統一しておく必要がある
• SET NAMES utf8mb4;（COLLATE 省略）は直前の COLLATE 指定を破棄するため、接続文字列の init_command で指定するのが確実
• 移行後も information_schema を使った COLLATION の定期チェックと自動通知の仕組みが有効
• 今回整備した COLLATION チェックの仕組みや CLI コマンドは、次のバージョンアップでもそのまま活用できる

本記事の内容が、同じ課題に取り組んでいる方々の参考になれば幸いです。

参考文献

• Changes in MySQL 8.0
  • collation_server のデフォルトが utf8mb4_0900_ai_ci に変更
• Server System Variables
  • default_collation_for_utf8mb4 パラメータの補足
• 接続に関するパラメータの理解
  • character_set_* / collation_* 各パラメータの関係
• SET NAMES の補足
  • セッションの COLLATION 指定方法