MySQL (InnoDB) のオンラインDDL(ALTER TABLE)の分かりづらい罠 〜 激重カラム追加 ＆ 一意制約違反でDDLロールバック

MySQL (InnoDB)には オンラインDDLといって、 DDL (ALTER TABLE) で変更中のテーブルにそのままDMLによる変更操作(INSERT/UPDATE/DELETE)を許可する機能 があります。

これにより、システムを稼働させたままDBマイグレーションを実施することができます。 新バージョンリリースのたびにシステムを閉塞する必要がなく、とても便利ですね！

とはいったものの、いくつか分かりづらい罠があったので、それらを紹介したいと思います。

罠その1 〜 本来瞬時に終わるカラム追加のDDLが激重になる場合がある

ALTER TABLE 文は通常下記のように書くものですが、コメントアウトしている行のように明示的にアルゴリズムを指定することも可能です。

ALTER TABLE foobar ADD COLUMN my_new_column VARCHAR(200) DEFAULT NULL
-- , ALGORITHM=INSTANT

アルゴリズムは下記の3種類から選ぶことができ、デフォルト（未指定）では上から順に使えそうなものを使います。（アルゴリズムを明示的に指定した場合は、指定したものが使えない場合は他で代替せずにエラーで終了します。）

• INSTANT: テーブルのデータは変更せず、メタデータの変更だけで済ませる。
• INPLACE: テーブルをその場で リビルド しつつ、最後にDDL中に実行されたDMLによる変更をリプレイ。
  • 詳細な動作については、この記事が参考になりました。
  • 場合によっては INSTANT と同様の動作をすることもあります。
• COPY: オンラインDDLを不可にして（INSERT文等ブロック）、旧テーブルをコピーして新テーブルを構築後、リネームして置き換える。

巨大なテーブルへのカラムの追加で顕著になるのですが、 INSTANT (メタデータ編集するだけ) が爆速 です。 0.1秒 レベルです。INPLACE (テーブルのリビルド) および COPY (テーブルのコピー) はテーブルサイズに比例して遅くなっていきます。大きなテーブルだと10分以上かかったりします。

-- 爆速
ALTER TABLE foobar ADD COLUMN my_new_column VARCHAR(200) DEFAULT NULL, ALGORITHM=INSTANT;
-- 遅い
ALTER TABLE foobar ADD COLUMN my_new_column VARCHAR(200) DEFAULT NULL, ALGORITHM=INPLACE;
-- もっと遅い ＆ SELECTしかできなくなる & 容量をやや食う
ALTER TABLE foobar ADD COLUMN my_new_column VARCHAR(200) DEFAULT NULL, ALGORITHM=COPY;

基本的にアルゴリズムを指定しなくてもメタデータ操作で済む場合は INSTANT 扱いになるのですが、 MySQL 8.0.29未満では、 ADD COLUMN のカラム位置を指定すると、 INPLACE に劣化します。

The INSTANT algorithm can add a column at any position in the table. Before MySQL 8.0.29, the INSTANT algorithm could only add a column as the last column of the table.
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

-- 8.0.29 未満だとINPLACE扱いでテーブルのリビルドが発生して遅い
ALTER TABLE foobar ADD COLUMN my_new_column VARCHAR(200) DEFAULT NULL
BEFORE other_column;

カラムを追加する場合は、 MySQL 8.0.29未満ではカラム追加の際はカラムの位置を追加しない か、もしくは INSTANT で実行されるという確信がある場合は、事故防止のために ALGORITHM=INSTANT を明示的に指定 しましょう。

あるいは INPLACE だからといって気にしないという選択肢もあります。途中のオンラインDMLが一時領域の innodb_online_alter_log_max_size を溢れなければいいでしょう。そのための機能ですからね！　

と思っていると、次の罠が……

罠その2 〜 INPLACE でオンラインDDL実行中、キーが衝突する INSERT を投げただけで ALTER TABLE が最終的に失敗に終わる

「一意制約があるカラムにとりあえずINSERTを投げて、衝突したら処理を諦める」みたいな処理パターン、たまにあると思うのですが、

-- 'hogehogeの予約' があるかどうかわからないが、とりあえず投げてみて、一意制約違反で怒られたら諦める
INSERT INTO reservation VALUES ('hogehogeの予約');

INPLACE オンラインDDL実行中にこれをやると、オンラインDDLの最後のDMLのリプレイ中にエラーで死にます。 ¹

もちろんオンラインDDL下といった特殊条件でない通常のオンライン処理の場合、一意制約に違反しない結果になりますが、オンラインDDL中は試行しただけで ALTER TABLE が失敗します。 （エラーとなり、DDLによる変更は無かったことになります。以前のテーブルに入り続けていたデータ変更は残ります。）

前述した通り、 INPLACE でのオンラインDDLは、裏で洗い替え用の一時テーブルを作りつつ、その間に旧テーブルに投げられた変更を記録しています。 そして準備ができたら新しいテーブル側に記録した変更を適用していくのですが、ここで一時的にも一意制約に反するようなDMLがあると、失敗扱いになります。

ちゃんと公式ドキュメントにもそれらしいことが書いてあります。

When running an in-place online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html

要するに、故意であれ一意制約違反を頻繁に起こすようなソフトウェアを運用している場合、INPLACEでのオンラインDDL適用は実質的に不可能になります。 該当のテーブルを更新するシステムの機能については一時的に閉塞しなければいけません。

別ドキュメントでも、「DDL後の制約に失敗するような変更をオンラインDDL中にやると失敗する」と書かれていますが、実質的に前述のものと同じオンラインDDLの制約のことを言っていると思われます。

Concurrent DML makes changes to the table that are allowed with the original table definition, but not with the new one. The operation only fails at the very end, when MySQL tries to apply all the changes from concurrent DML statements. For example, you might insert duplicate values into a column while a unique index is being created, or you might insert NULL values into a column while creating a primary key index on that column. The changes made by the concurrent DML take precedence, and the ALTER TABLE operation is effectively rolled back.

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-failure-conditions.html

一意制約違反発生を前提とした機能を作る場合はオンラインDDLの適用が難しくなる ことに気をつけておいたほうがいいでしょう。

まとめ

MySQLのオンラインDDLは便利な機能ですが、あらゆるテーブルへの変更操作を手軽に扱える魔法の機能ではなさそうです。きちんとどういったアルゴリズムで変更が反映されるか知ることがMySQL安定稼働への一歩になりそうです。

（それはそうと、Amazon RDS Blue-Greenデプロイやってみたいなあ。バイナリログを複製する仕組みなので、割りと今回の INPLACE に似てると思っています。）

1. MySQL 8.0.26 で確認済。DDLの最後に SQLエラー [1062] [23000]: Duplicate entry 'xxxxx' for key と出ます。 ↩