RDS Aurora MySQL 5.7 互換で文字コード utf8mb4 を利用したくて調査した内容をメモ。調査した Aurora のエンジンのバージョンは 5.7.mysql_aurora.2.04.5。
目次
- character_set_server と collation_server を設定するだけでよい
- 調査メモ Aurora MySQL 5.7 互換の文字コードのデフォルト値
- 調査メモ 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_server と collation_server を設定し、接続クライアントの文字コードと照合順序を設定しましょう。