Aurora MySQL でテーブルロックなしで列のデータ型を変更できるか

結論から言うとできない。

MySQL のドキュメント「オンライン DDL の概要」から抜粋すると、
https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html

「並列クエリーを許可?」カラムは、どの DDL 操作の場合に、その操作の進行中にテーブルに対するクエリーが許可されるかを示しています。推奨される値は「はい」です。並列クエリーは、すべてのオンライン DDL 操作中に許可されます。これは、参考のために、すべてのセルに表示されている「はい」で示されています。DDL 中に並列クエリーが許可されることを表明するために LOCK=SHARED を指定できますが、MySQL は、可能な場合は自動的にこのレベルの並列性を許可します。

とあり、列のデータ型変更は下表のように示されている。

操作	インプレース?	テーブルをコピー?	並列 DML を許可?	並列クエリーを許可?
カラムのデータ型を変更する	いいえ	はい	いいえ	はい

検証

検証というよりはコンテナの MySQL クライアントから ssh port forward 経由で Aurora に接続、
というのが個人的に学びがあったのでその記録。

環境

データベースエンジン : Aurora MySQL 5.6.10a
- リージョン : us-east-1
クライアント(コンテナを使用) : mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

アーキテクチャ

簡単だが以下のような環境で確認した。
※リードレプリカは割愛しています。
MySQL クライアントはコンテナ上で起動し、Aurora へは ssh の port forward を使用した踏み台（bastion）を経由して接続する。

アーキテクチャ図

環境の準備

データベース側

作成方法は「標準作成」、テンプレートは「本番稼働用」を使用して作成した。
インスタンスが「利用可能」になったら書き込み可能なエンドポイントを控えておく。
エンドポイントを CLI で確認したい場合は、少し長いが下記のコマンドで確認が可能。
filters で指定している XXX は「DB クラスター識別子」。

$ aws rds describe-db-cluster-endpoints \
  --query "DBClusterEndpoints[*].Endpoint" \
  --filters \
    "Name=db-cluster-endpoint-status,Values=available" 
    "Name=db-cluster-endpoint-type,Values=WRITER" 
    "Name=db-cluster-endpoint-id,Values=XXX"

以下のような結果が返ってくる。

[
    "XXX.cluster-YYY.REGION.rds.amazonaws.com"
]

クライアント側

Aurora のインスタンスは ec2 の踏み台（bastion）経由して接続するため、ssh で port forward を設定して踏み台に接続しておく。
こうしておくことで、クライアントの localhost:3306 に接続すると、ssh を介して Aurora のエンドポイントに接続できる。

$ ssh -L 3306:XXX.cluster-YYY.REGION.rds.amazonaws.com:3306 ec2-user@BASTION

MySQL クライアントは手元の Laptop にはインストールされていなかった。
わざわざインストールするのもどうかなと思ったので、docker コンテナを使用する。
以下は別のターミナルにて

$ docker pull mysql

mysql の接続先は ssh の port forward で設定した localhost:3306 になるが、コンテナからは localhost（もしくは 127.0.0.1）は参照できない。以下のようなエラーになってしまう。

$ docker run -it --rm mysql mysql -h localhost --ssl-mode=DISABLED -u admin -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

$ docker run -it --rm mysql mysql -h 127.0.0.1 --ssl-mode=DISABLED -u admin -p
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

どうやら host.docker.internal というホスト名を使用するとコンテナからホストに接続することができるようだ。

$ docker run -it --rm mysql mysql -h host.docker.internal -u admin -p
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol

SSL のコネクションエラーが出てしまったので、とりあえず今回は無効にして接続

$ docker run -it --rm mysql mysql -h host.docker.internal --ssl-mode=DISABLED -u admin -p
  :
  :
mysql>

検証用 Table 作成

ここから、テスト用のテーブルを作成し、LOCK=NONE を指定した alter table ... で列のデータ型を変更できるか確認。
まずテーブル作成。

mysql> use test
Database changed

mysql> create table a (id1 int primary key, text1 text);
Query OK, 0 rows affected (0.22 sec)

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   | PRI | NULL    |       |
| text1 | text    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (1.34 sec)

1件だけデータを挿入しておく。

mysql> insert into a values(1, 'test');
Query OK, 1 row affected (0.21 sec)

mysql> select * from a;
+-----+-------+
| id1 | text1 |
+-----+-------+
|   1 | test  |
+-----+-------+
1 row in set (0.18 sec)

列変更の検証

ここからが本番。列「text1」のデータ型 text を mediumtext に lock=none で変更する。

mysql> alter table a modify text1 mediumtext, lock=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

となり、やっぱりできない。

lock=none の指定を無し、すなわち lock=default の状態で実行すると成功する。

mysql> alter table a modify text1 mediumtext;
Query OK, 1 row affected (0.30 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc a;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id1   | int(11)    | NO   | PRI | NULL    |       |
| text1 | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.17 sec)

ちなみに alter table ... の実行中はテーブルが共有ロック状態となるため、他からの書き込みができなくなる。

ではどうするか

長時間テーブルがロックされるのが許容できない場合、どうするか？
一つの案であるが、新しいテーブルを変更後のデータ型で作成し、AWS DMS (Database Migration Service) を使って継続的なレプリケーションを取りながらあるタイミングで切り替える。切り替えはアプリ側でテーブル名を変更するか、alter table ... rename ...; でテーブル名を変更するか。alter table ... によるテーブル名の変更であればロック時間は短く済む（残念ながら lock=none は指定できず、lock=exclusive になる）。
注意点としては、Aurora で DMS を使用する場合は、Aurora でバイナリロギングを有効にする必要があり、インスタンスの再起動を伴う。
DBクラスターパラメータで binlog_format を ROW に設定する。詳しくは以下サイトを参照。
https://aws.amazon.com/jp/premiumsupport/knowledge-center/enable-binary-logging-aurora/

最後に・・・

この投稿は個人的なものであり、所属組織を代表するものではありません。ご了承ください。

検証

環境