Amazon Aurora MySQL 5.7 互換の文字コードをutf8mb4にする

AWS
RDS Aurora MySQL 5.7 互換で文字コード utf8mb4 を利用したくて調査した内容をメモ。調査した Aurora のエンジンのバージョンは 5.7.mysql_aurora.2.04.5。

目次

  1. character_set_server と collation_server を設定するだけでよい
  2. 調査メモ Aurora MySQL 5.7 互換の文字コードのデフォルト値
  3. 調査メモ utf8mb4 とインデックスのキーサイズ制限の問題

character_set_server と collation_server を設定するだけでよい

RDS Aurora MySQL 5.7 互換では、クラスタのパラメータグループで以下を設定するだけで良いです。他はデフォルトで大丈夫。

パラメータ
character_set_server utf8mb4
collation_server utf8mb4_bin など

設定後、以下のコマンドで Aurora に接続して確認します。


$ mysql -u [user_name] -p -h [host_name]

MySQL にログインできたら、クライアントの文字コードと照合順序を設定します。


MySQL [(none)]> set NAMES 'utf8mb4' COLLATE 'utf8mb4_bin';

文字コードを確認します。


MySQL [(none)]> show variables like '%char%';
+--------------------------+-----------------------------------------------------------------+
| Variable_name            | Value                                                           |
+--------------------------+-----------------------------------------------------------------+
| character_set_client     | utf8mb4                                                         |
| character_set_connection | utf8mb4                                                         |
| character_set_database   | utf8mb4                                                         |
| character_set_filesystem | binary                                                          |
| character_set_results    | utf8mb4                                                         |
| character_set_server     | utf8mb4                                                         |
| character_set_system     | utf8                                                            |
| character_sets_dir       | /rdsdbbin/oscar-5.7.mysql_aurora.2.04.5.0.165.0/share/charsets/ |
+--------------------------+-----------------------------------------------------------------+

照合順序を確認します。


MySQL [(none)]> show variables like '%collation%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
| collation_database   | utf8mb4_bin |
| collation_server     | utf8mb4_bin |
+----------------------+-------------+

InnoDB まわりの設定を確認します。


MySQL [(none)]> show variables where Variable_name in ('innodb_large_prefix', 'innodb_file_format', 'innodb_default_row_format');
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_default_row_format | dynamic   |
| innodb_file_format        | Barracuda |
| innodb_large_prefix       | ON        |
+---------------------------+-----------+

調査メモ Aurora MySQL 5.7 互換の文字コードのデフォルト値

以下、調査メモです。まずは未設定のパラメータグループ default.aurora-mysql5.7 を適用して、サーバ変数のデフォルト値を調べました。
データベースとサーバの文字コードのデフォルト値は latin1


MySQL [(none)]> show variables like '%char%';
+--------------------------+-----------------------------------------------------------------+
| Variable_name            | Value                                                           |
+--------------------------+-----------------------------------------------------------------+
| character_set_client     | utf8                                                            |
| character_set_connection | utf8                                                            |
| character_set_database   | latin1                                                          |
| character_set_filesystem | binary                                                          |
| character_set_results    | utf8                                                            |
| character_set_server     | latin1                                                          |
| character_set_system     | utf8                                                            |
| character_sets_dir       | /rdsdbbin/oscar-5.7.mysql_aurora.2.04.5.0.165.0/share/charsets/ |
+--------------------------+-----------------------------------------------------------------+

照合順序は latin1_swedish_ci でした。


MySQL [(none)]> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

調査メモ utf8mb4 とインデックスのキーサイズ制限の問題

サーバ全体の文字コードを変更するだけなら character_set_server を utf8mb4 に設定するだけで変更できるのですが、MySQL 5.6 のときはこれだけではダメでした。文字コードを utf8mb4 にすると VARCHAR などのカラムで utf8 に比べてサイズが大きくなり、例えば VARCHAR(255) のカラムにインデックスを張ろうとすると Index column size too large. The maximum column size is 767 bytes. というエラーが出て失敗する問題が起きていました。
この問題に対応するため、文字コードの変更に加え、以下の InnoDB の設定を変更する必要がありました。

  • innodb_large_prefix を有効にする
  • テーブルの ROW_FORMAT を DYNAMIC (または COMPRESSED) にする
  • そのために innodb_file_format を Barracuda にする必要がある

これらの対策は、公式ドキュメントに書かれています。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.7 InnoDB テーブル上の制限

デフォルトでは、単一カラムインデックスのインデックスキーを最大で 767 バイトにすることができます。インデックスキープリフィクスにも同じ長さ制限が適用されます。セクション13.1.13「CREATE INDEX 構文」を参照してください。たとえば、UTF-8 文字セットと文字ごとに最大 3 バイトを使用すると仮定すれば、TEXT または VARCHAR カラム上で 255 文字よりも長いカラムプリフィクスインデックスを使用すると、この制限に達する可能性があります。innodb_large_prefix 構成オプションを有効にすると、DYNAMIC および COMPRESSED 行フォーマットを使用する InnoDB テーブルで、この長さ制限が 3072 バイトに上昇します。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.9.3 DYNAMIC および COMPRESSED 行フォーマット

このセクションでは、InnoDB テーブルの DYNAMIC および COMPRESSED 行フォーマットについて説明します。これらの種類のテーブルは、innodb_file_format 構成オプションが Barracuda に設定されている場合にのみ作成できます。(Barracuda ファイル形式では、COMPACT および REDUNDANT 行フォーマットも許可されます。)

MySQL 5.7 では、上記の設定がデフォルトで有効になっており、Aurora MySQL 5.7 互換も同じく有効でした。このため、追加の設定は不要でした。


MySQL [(none)]> show variables where Variable_name in ('innodb_large_prefix', 'innodb_file_format', 'innodb_default_row_format');
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_default_row_format | dynamic   |
| innodb_file_format        | Barracuda |
| innodb_large_prefix       | ON        |
+---------------------------+-----------+

MySQL 5.7 のサーバ変数のデフォルト値は MySQL :: MySQL 5.7 Reference Manual :: 14.14 InnoDB Startup Options and System Variables をご確認ください。
Aurora MySQL 5.7 互換のパラメータのデフォルト値はアップデートにより変更されることがあります。Amazon Aurora MySQL 2.0 のデータベースエンジンの更新 – Amazon Aurora を併せて確認しましょう。

まとめ

RDS Aurora MySQL 5.7 互換で utf8mb4 を利用するには、クラスタのパラメータグループで character_set_servercollation_server を設定し、接続クライアントの文字コードと照合順序を設定する。

関連記事