Aurora Serverlessの文字コードをutf8mb4にする

2019年7月17日

MySQL

Amazon Aurora MySQL 5.6 Serverlessで文字コード utf8mb4 を利用したくて調査した内容をメモ。

目次

  1. Aurora MySQL 5.6 Serverless の文字コードのデフォルト値を調べる
  2. utf8mb4 と InnoDB の制限の問題
  3. DB クラスターのパラメータグループの変更
  4. テーブルの ROW_FORMAT を変更
  5. 変更後

Aurora MySQL 5.6 Serverless の文字コードのデフォルト値を調べる

まずは未設定のパラメータグループ default.aurora5.6 を適用して、サーバの文字コードのデフォルト値を調べました。

データベースとサーバの文字コードのデフォルト値は 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.6.serverless_10a.184.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 と InnoDB の制限の問題

続いて InnoDB のシステム変数を確認します。

文字コードを utf8mb4 にすると VARCHAR などのカラムで utf8 に比べてサイズが大きくなり、例えば VARCHAR(255) のカラムにインデックスを張ろうとすると Index column size too large. The maximum column size is 767 bytes. というエラーが出て失敗します。

これは InnoDB の制限によるもので、以下の 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 行フォーマットも許可されます。)

InnoDB サーバ変数のデフォルト値を確認すると、innodb_large_prefix は無効、innodb_file_format は Antelope でした。

MySQL [(none)]> show variables where Variable_name in ('innodb_large_prefix', 'innodb_file_format');

+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_file_format  | Antelope |
| innodb_large_prefix | OFF      |
+---------------------+----------+

MySQL5.6 では、テーブルの ROW_FORMAT は CREATE TABLE か ALTER TABLE で設定します。詳細は MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.9.2 テーブルの行フォーマットの指定 をご確認ください。

ちなみに MySQL 5.7 では innodb_default_row_format で設定できるので、CREATE TABLE で ROW_FORMAT を指定する必要はなくなっています。

DB クラスターのパラメータグループの変更

これらを踏まえると、RDS Aurora MySQL 5.6 Serverlessで utf8mb4 を使うには DB クラスターのパラメータグループで以下を設定する必要があります。

パラメータ値
character_set_serverutf8mb4
collation_serverutf8mb4_bin など
innodb_large_prefix1
innodb_file_formatBarracuda

専用の DB クラスタパラメータグループを事前に作成しておき、Serverless データベース作成時に設定しましょう。

テーブルの ROW_FORMAT を変更

テーブルの行フォーマットの指定 を参考に。

CREATE TABLE t1 (f1 int unsigned) ROW_FORMAT=DYNAMIC ENGINE=INNODB;

変更後

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

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

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

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.6.serverless_10a.184.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');

+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| innodb_file_format  | Barracuda |
| innodb_large_prefix | ON        |
+---------------------+-----------+

まとめ

RDS Aurora MySQL 5.6 Serverless で utf8mb4 を利用するには

  • クラスタのパラメータグループで character_set_server, collation_server, innodb_large_prefix, innodb_file_format を設定する
  • CREATE TABLE や ALTER TABLE で ROW_FORMAT を指定する
  • クライアントの文字コードと照合順序を設定する

-技術ブログ
-