What to Check When Migrating from utf8mb3 to utf8mb4 in Aurora MySQL

この文書の適用条件

そのまま実行できるコマンド

-- 対象: Aurora MySQL 2.x / 3.x、MySQL 5.7 / 8.0
-- 権限: 接続権限（グローバル変数の参照のみ）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
    'character_set_server',
    'character_set_database',
    'character_set_client',
    'character_set_connection',
    'character_set_results',
    'collation_server',
    'collation_database',
    'collation_connection'
);

-- 対象: Aurora MySQL 2.x / 3.x、MySQL 5.7 / 8.0
-- 権限: information_schema の参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    ROW_FORMAT,
    TABLE_COLLATION,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 1) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND (TABLE_COLLATION IS NULL OR TABLE_COLLATION NOT LIKE 'utf8mb4%')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 対象: Aurora MySQL 2.x / 3.x、MySQL 5.7 / 8.0
-- 権限: information_schema の参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH AS max_chars,
    CHARACTER_OCTET_LENGTH   AS max_bytes,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4'
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

-- 対象: Aurora MySQL 2.x / 3.x、MySQL 5.7 / 8.0（InnoDB）
-- 権限: information_schema の参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
-- utf8mb4 は 1 文字最大 4 バイト。プレフィックス長指定の無い文字列列だけを対象に、
-- 変換後に必要となるキー長（バイト）を見積もる。
SELECT
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME,
    GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX)  AS index_columns,
    SUM(c.CHARACTER_MAXIMUM_LENGTH * 4)                  AS bytes_after_utf8mb4
FROM information_schema.STATISTICS s
JOIN information_schema.COLUMNS c
      ON  c.TABLE_SCHEMA = s.TABLE_SCHEMA
      AND c.TABLE_NAME   = s.TABLE_NAME
      AND c.COLUMN_NAME  = s.COLUMN_NAME
WHERE s.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
  AND s.SUB_PART IS NULL
GROUP BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
HAVING bytes_after_utf8mb4 > 767
ORDER BY bytes_after_utf8mb4 DESC;

-- 対象: Aurora MySQL 2.x / 3.x、MySQL 5.7 / 8.0
-- 権限: 対象テーブルへの ALTER 権限
-- 変更作業: あり（テーブル作り直し + 全行のデータコピー）
-- Production 実行: 大きなテーブルでは不可。所要時間を実測してから計画実行する

-- 1) 変換前に現在の定義を保存しておく（切り戻し判断の材料になる）
SHOW CREATE TABLE SampleDB.sample_table;

-- 2) テーブル既定と全文字列列をまとめて変換する
ALTER TABLE SampleDB.sample_table
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ALGORITHM = COPY,
    LOCK = SHARED;

-- 3) 変換結果を確認する
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'SampleDB'
  AND TABLE_NAME   = 'sample_table'
  AND CHARACTER_SET_NAME IS NOT NULL;

-- 対象: Aurora MySQL 2.x / 3.x、MySQL 5.7 / 8.0（セッション単位の設定）
-- 権限: 接続権限
-- 変更作業: あり（現在のセッションのみ。他セッションには影響しない）
-- Production 実行: 可能

-- クライアント・接続・結果の3つの文字セットをまとめて切り替える
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 現在のセッションの値を確認する
SHOW SESSION VARIABLES WHERE Variable_name IN (
    'character_set_client',
    'character_set_connection',
    'character_set_results',
    'collation_connection'
);

# 対象: Aurora MySQL（DBクラスターパラメータグループ）
# 権限: IAM rds:DescribeDBClusterParameters, rds:ModifyDBClusterParameterGroup
# 変更作業: あり（クラスター全体に効くパラメータ変更）
# Production 実行: 変更管理を通したうえで実施

# 1) まず現在値と反映方法（ApplyType）を確認する。static なら再起動が必要になる
aws rds describe-db-cluster-parameters \
  --db-cluster-parameter-group-name example-aurora-mysql-cluster-params \
  --query "Parameters[?starts_with(ParameterName, 'character_set') || starts_with(ParameterName, 'collation')].{Name:ParameterName,Value:ParameterValue,Apply:ApplyType}" \
  --output table

# 2) 既定の文字セットと照合順序を変更する
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name example-aurora-mysql-cluster-params \
  --parameters '[
    {"ParameterName":"character_set_server","ParameterValue":"utf8mb4","ApplyMethod":"pending-reboot"},
    {"ParameterName":"collation_server","ParameterValue":"utf8mb4_unicode_ci","ApplyMethod":"pending-reboot"}
  ]'

結果の読み方

こういう状況で使います

• Saving an emoji or certain kanji causes an `Incorrect string value` error
• Saving succeeds, but the emoji comes back as `????` or an empty string
• `ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4` fails with `Specified key was too long`
• The table is utf8mb4, but the application still shows garbled text
• Comparing the same strings gives a different result before and after the conversion (duplicate errors increased or decreased)

考えられる原因（可能性の高い順）

1. 01

   utf8 (utf8mb3) can only store up to 3 bytes

   In MySQL 5.7, `utf8` is an alias for `utf8mb3`, which uses at most 3 bytes per character. Emoji and some extended CJK kanji require 4 bytes, so they cannot be stored.

2. 02

   Hitting the index key length limit

   InnoDB index keys have a limit: 767 bytes with the legacy setting (`innodb_large_prefix` disabled, `COMPACT` / `REDUNDANT` row format), or 3072 bytes with `DYNAMIC` / `COMPRESSED`. Since utf8mb4 counts 4 bytes per character, a standalone index on `VARCHAR(255)` requires 1020 bytes and fails under the 767-byte limit.

3. 03

   The connection-side character set doesn't match

   Even if the table is utf8mb4, if the connection is utf8mb3, 4-byte characters are dropped before reaching the server. Check the driver's connection string or the `SET NAMES` setting.

4. 04

   A column-level override remains

   Changing the table default does not change a column that has an explicit character set specified. Checking at the column level is necessary.

5. 05

   A collation difference changed comparison results

   `utf8mb4_general_ci` and `utf8mb4_unicode_ci` differ in which characters they consider equivalent. Changing collation can change which set of rows trips a unique constraint.

確認手順

1. 1

   Check the current value across all four levels: server, database, table, and column

   参照のみ

   A higher-level setting is only a default — an explicit setting at a lower level wins. Check all four levels with read-only SQL.

2. 2

   List tables and columns that are not utf8mb4

   参照のみ

   Determine the targets using `information_schema.TABLES` and `information_schema.COLUMNS`.

3. 3

   Find indexes that will hit the key length limit

   参照のみ

   Always do this before running the conversion. A table that shows up here needs its index definitions reviewed before conversion.

4. 4

   Check the row format and the state of `innodb_large_prefix`

   参照のみ

   On a MySQL 5.7-compatible environment, check with `SELECT @@global.innodb_large_prefix, @@global.innodb_default_row_format;`. `innodb_large_prefix` has been removed in MySQL 8.0.

5. 5

   Convert one table in a test environment and measure the time and result

   中

   Measure with a data volume equivalent to production. The time obtained here is the only real basis for planning.

6. 6

   Check the difference in comparison results caused by the collation change

   中

   In a test environment, check whether duplicates occur after conversion on columns with a unique constraint.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Check the application's driver settings

  Check whether JDBC's `characterEncoding`, PHP's `charset`, and the defaults of each language's client library are set to `utf8mb4`.

• Check whether the collation matches across tables being joined

  Joining columns with different collations can cause runtime errors or prevent index usage.

• Check the character set of stored procedures, views, and triggers

  A routine retains the character set from when it was created. It may need to be recreated after the table conversion.

• Check whether replicas or DMS targets are converted at the same time

  Converting only the source causes inconsistency or apply errors on the replication target.

• Check whether the character set is preserved in the backup restore procedure

  Confirm beforehand that you can roll back to the pre-conversion definition.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• Saving an emoji in MySQL causes Incorrect string value
• Index creation fails after converting to utf8mb4
• Should I use utf8 or utf8mb4
• Want to change the default character set of Aurora MySQL to utf8mb4

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

Character set migration looks like a "do it once and you're done" task, but in practice the settings keep drifting after migration every time a column is added or a table is newly created. GIIP periodically runs the utf8mb4-target discovery SQL to continuously check whether any new non-utf8mb4 columns have appeared. It only notifies when a difference shows up, leaving the decision on whether to make a schema change to a human, which prevents settings from reverting after the migration is done.

関連サービス

• 開発・検証・本番環境の作り方
• データベース性能チューニング