MySQL のオンライン DDL は複数の条件が関係する
Posted on
オンライン DDL で実行されることを期待したカラム追加のクエリが、想定通りに実行されず障害になりかけたので原因を調べてみました。結論から言うと公式ドキュメントに書いてある通りだったのですが、複数の条件がオンライン DDL に関係するとは思っていなかったので学びがありました。
検証環境
MySQL 8.0.34 の Docker イメージを使って検証を行いました。
$ docker network create mysql
$ docker run --name mysql-8.0 --network mysql -d -e MYSQL_ROOT_PASSWORD=root mysql:8.0.34
$ docker run -it --rm --network mysql mysql:8.0.34 mysql -hmysql-8.0 -uroot -p
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0.00 sec)
オンライン DDL でカラムを追加する
検証に使うテーブルを作成します。
mysql> CREATE TABLE users (first_name VARCHAR(10), last_name VARCHAR(10), email VARCHAR(32));
Query OK, 0 rows affected (0.02 sec)
mysql> DESC users;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(10) | YES | | NULL | |
| last_name | varchar(10) | YES | | NULL | |
| email | varchar(32) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
適当なカラムを追加します。
mysql> ALTER TABLE users ADD COLUMN birth DATE, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC users;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(10) | YES | | NULL | |
| last_name | varchar(10) | YES | | NULL | |
| email | varchar(32) | YES | | NULL | |
| birth | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
オンライン DDL で実行したいので、明示的に ALGORITHM=INPLACE と LOCK=NONE を付けています。このように期待する動作を明示することで、オンライン DDL で実行できない場合はエラーになってくれます。
公式ドキュメントの "Column Operations" を確認すると、カラム追加は "In Place" と "Permits Concurrent DML" がどちらも Yes になっているので想定通りです。
複数条件でオンライン DDL が失敗するケース
先ほどのテーブルに first_name と last_name を結合した full_name を Generated Column で定義します。 Generated Column は定義した式に従って値を生成することができるカラムです。
mysql> ALTER TABLE users ADD COLUMN full_name VARCHAR(32) GENERATED ALWAYS AS (CONCAT(first_name ,' ', last_name)) VIRTUAL AFTER last_name, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC users;
+------------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------------------+
| first_name | varchar(10) | YES | | NULL | |
| last_name | varchar(10) | YES | | NULL | |
| full_name | varchar(32) | YES | | NULL | VIRTUAL GENERATED |
| email | varchar(32) | YES | | NULL | |
| birth | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------------------+
5 rows in set (0.01 sec)
この状態で full_name より前にカラム追加しようとすると失敗します。
mysql> ALTER TABLE users ADD COLUMN id INT FIRST, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
先ほど試したようにカラム追加はオンライン DDL で実行できるはずですが、なぜ失敗するのでしょうか？
エラーメッセージにも出ていますが、"Generated Column Operations" を参照すると Generated Column の順序入れ替えは "In Place" と "Permits Concurrent DML" が No になっています。この例では id を先頭に追加しようとしたことで Generated Column の full_name の位置がズレることになり、結果的にオンライン DDL では実行できなかったのです。
明示的に ALGORITHM=INPLACE と LOCK=NONE を付けたのでエラーになりましたが、付けずに実行した場合は ALGORITHM=COPY のテーブルコピーになります。テーブルコピーで実行されるとテーブルへの更新がブロックされるため、オンライン DDL が有効だろうと思って実行した場合はセッションが詰まって障害になるかもしれません。
冒頭に書いた障害になりかけたケースでは、運悪く先ほどの条件が揃ってしまいました。
- Generated Column の存在を認識していなかった
ALTER TABLEでカラムを追加しようとしたが、位置を指定したため Generated Column の順序入れ替えが発生した- オンライン DDL を期待していたが
ALGORITHM句とLOCK句を付けていなかった
まとめ
今回は Generated Column を例に取りましたが、ほかの組み合わせでも起こりえるかもしれません。事前にドキュメントを確認するのはもちろんのこと、ステージング環境などで ALGORITHM 句と LOCK 句を付けて想定している動作になるか確認するのが大切ですね。