kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

pt-online-schema-changeとgh-ostの比較(データが損失するかもしれないAlterTable編)

こちらのエントリーは MySQL Advent Calendar 2021の14日目の記事になります。

今年はオンラインスキーママイグレーションツールのpt-online-schema-change(pt-osc)とgh-ostをよく調べていたので、その中から一つ。
mysqlコマンドではエラーになるけど、制約違反などでデータが損失する可能性のあるAlter Tableを実施するとそれぞれどうなるか比較したいと思います。
昨今ではMySQL8.0からのInstance Alter Tableが使えるのであれば、まずこれを使うでしょう。
それ以外だとOnlineDDL機能を使ってスキーマ変更するでしょうけど、これはレプリカの遅延が発生するため、pt-oscや gh-ostを使う機会はまだ多いと感じます。

各種ツールについて詳しく説明はしません。
gh-ostについてはがんばって書いた記事があるのでそちらを参照してください。pt-oscはググればいっぱい記事がでますのでそちらでご確認を。
* 第138回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その1]
* 第139回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その2]
* 第140回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その3]

使用するVersionはこちらです。

Tool Version
pt-osc 3.3.1
gh-ost 1.1.2

それでは、今回は以下の4つのケースを比較してみます。
* Unique keyの追加
* 既存の Unique key付きカラムの変更
* Foreign Key追加
* Check制約追加

では、見ていきましょう。以後、出力されるログは一部割愛しています。

Unique keyの追加

以下のようなテーブルとデータを用意します。
col1にUnique Keyを追加します。col1=1が2行あるためユニークではありません。

mysql> CREATE TABLE `t0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

mysql> SELECT * FROM t0;
+----+------+
| id | col1 |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |  100 |
+----+------+

mysqlコマンド

エラーになります。

mysql> ALTER TABLE t0 ADD UNIQUE KEY(col1);
ERROR 1062 (23000): Duplicate entry '1' for key 't0.col1'

pt-osc

pt-online-schema-change \
  --alter "ADD UNIQUE KEY(col1)" D=test,t=t0 \
  --defaults-file=./my.cnf \
  --execute

<snip>
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:

SELECT IF(COUNT(DISTINCT col1) = COUNT(*),
       'Yes, the desired unique index currently contains only unique values',
       'No, the desired unique index contains duplicated values. There will be data loss'
) AS IsThereUniqueness FROM `test`.`t0`;

Keep in mind that these queries could take a long time and consume a lot of resources

`test`.`t0` was not altered.

エラーメッセージがでます。
データが一意じゃないとデータ損失するから、SQLで確認してね。--check-unique-key-change のドキュメントを読んで確認してね。
というメッセージです。

デフォルトでは--check-unique-key-change が有効なため、このメッセージが表示されます。
--no-check-unique-key-changeを付けて実行すると、このチェックを無視して通るようになります。

pt-online-schema-change \
  --alter "ADD UNIQUE KEY(col1)" D=test,t=t0 \
  --defaults-file=./my.cnf \
  --no-check-unique-key-change \
  --execute

<snip>
2021-12-13T11:28:19 Creating triggers...
2021-12-13T11:28:19 Created triggers OK.
2021-12-13T11:28:19 Copying approximately 3 rows...
2021-12-13T11:28:19 Copied rows OK.
2021-12-13T11:28:19 Analyzing new table...
2021-12-13T11:28:19 Swapping tables...
2021-12-13T11:28:19 Swapped original and new tables OK.
2021-12-13T11:28:19 Dropping old table...
2021-12-13T11:28:19 Dropped old table `test`.`_t0_old` OK.
2021-12-13T11:28:19 Dropping triggers...
2021-12-13T11:28:19 Dropped triggers OK.
Successfully altered `test`.`t0`.

結果はもちろんデータがユニークではないので、id=2のデータが損失しました。

mysql> SHOW CREATE TABLE t0;
CREATE TABLE `t0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col1` (`col1`)
)

mysql> SELECT * FROM t0;
+----+------+
| id | col1 |
+----+------+
|  1 |    1 |
|  3 |  100 |
+----+------+

gh-ost

gh-ost \
  --alter="ADD UNIQUE KEY(col1)" \
  --database="test" \
  --table="t0" \
  --host=127.0.0.1 \
  --port=21224 \
  --user="msandbox" \
  --password='msandbox' \
  --execute

<snip>
.
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000001:23347; Lag: 0.02s, HeartbeatLag: 0.02s, State: migrating; ETA: due
[2021/12/13 11:14:44] [info] binlogsyncer.go:164 syncer is closing...
[2021/12/13 11:14:44] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/12/13 11:14:44] [info] binlogsyncer.go:179 syncer is closed
# Done

特にUnique Keyに関するメッセージもなく #Done がでて完了しました。
結果を見ると、Unique Keyは追加されていますが、データのid=2の行がなくなってしまいました。

mysql> SHOW CREATE TABLE t0;
CREATE TABLE `t0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` int DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `col1` (`col1`)
)

mysql> SELECT * FROM t0;
+----+------+
| id | col1 |
+----+------+
|  1 |    1 |
|  3 |  100 |
+----+------+

既存の Unique key付きカラムの変更

つづいて、以下のようなテーブルとデータを用意します。
mojiretu カラムのcollationはutf8mb4_binで Unique keyがあります。 mojiretuカラムをutf8mb4_binからutf8mb4_general_ciに変更します。
utf8mb4_bincase sensitiveなので'A'と'a'を区別しますが、utf8mb4_general_ciは区別されません。そのため、Unique key制約に違反します。

mysql> CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mojiretu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mojiretu` (`mojiretu`)
);

mysql> SELECT * FROM t1;
+----+----------+
| id | mojiretu |
+----+----------+
|  1 | a        |
|  2 | A        |
|  3 | Z        |
+----+----------+

mysqlコマンド

エラーになります。

mysql> ALTER TABLE t1 MODIFY mojiretu varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ERROR 1062 (23000): Duplicate entry 'A' for key 't1.mojiretu'

pt-osc

pt-online-schema-change \
  --alter "MODIFY mojiretu varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" D=test,t=t1 \
  --defaults-file=./my.cnf \
  --execute

2021-12-13T11:54:01 Creating triggers...
2021-12-13T11:54:01 Created triggers OK.
2021-12-13T11:54:01 Copying approximately 3 rows...
2021-12-13T11:54:01 Copied rows OK.
2021-12-13T11:54:01 Analyzing new table...
2021-12-13T11:54:01 Swapping tables...
2021-12-13T11:54:01 Swapped original and new tables OK.
2021-12-13T11:54:01 Dropping old table...
2021-12-13T11:54:01 Dropped old table `test`.`_t1_old` OK.
2021-12-13T11:54:01 Dropping triggers...
2021-12-13T11:54:01 Dropped triggers OK.
Successfully altered `test`.`t1`.

エラーなく完了しました。
結果を見ると、mojiretu カラムはutf8mb4_binからutf8mb4_general_ciに変更されています。
しかし、id=1の行が損失しました。

mysql> SHOW CREATE TABLE t1;
CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mojiretu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mojiretu` (`mojiretu`)
)

mysql> SELECT * FROM t1;
+----+----------+
| id | mojiretu |
+----+----------+
|  2 | A        |
|  3 | Z        |
+----+----------+

gh-ost

gh-ost \
  --alter="MODIFY mojiretu varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" \
  --database="test" \
  --table="t1" \
  --host=127.0.0.1 \
  --port=21224 \
  --user="msandbox" \
  --password='msandbox' \
  --execute

<snip>
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000001:58610; Lag: 0.01s, HeartbeatLag: 0.02s, State: migrating; ETA: due
[2021/12/13 11:57:10] [info] binlogsyncer.go:164 syncer is closing...
[2021/12/13 11:57:10] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/12/13 11:57:10] [info] binlogsyncer.go:179 syncer is closed
# Done

#Done がでて完了しました。
結果を見ると、、mojiretu カラムはutf8mb4_binからutf8mb4_general_ciに変更されています。
しかし、データのid=2の行が損失しました。

mysql> SHOW CREATE TABLE t1;
CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mojiretu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mojiretu` (`mojiretu`)
)

mysql> SELECT * FROM t1;
+----+----------+
| id | mojiretu |
+----+----------+
|  1 | a        |
|  3 | Z        |
+----+----------+

Foreign Key追加

parentchildテーブルを用意します。
parent.idカラムを参照するchild.parent_idにForeign Keyを作成するとします。
しかし、childテーブルにはparentテーブルが持っていないparent_id=1000というデータが存在します。

mysql> CREATE TABLE `parent` (
  `id` bigint NOT NULL,
  `col1` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

mysql> CREATE TABLE `child` (
  `id` bigint NOT NULL,
  `parent_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`)
);

mysql> SELECT * FROM parent;
+----+------+
| id | col1 |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
+----+------+

mysql> SELECT * FROM child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |      1000 |
+----+-----------+

mysqlコマンド

エラーになります

mysql> ALTER TABLE child ADD CONSTRAINT `fk_test` FOREIGN KEY (parent_id) REFERENCES `parent` (`id`);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-1a43_c`, CONSTRAINT `fk_test` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

pt-osc

pt-online-schema-change \
  --alter "ADD CONSTRAINT fk_test FOREIGN KEY (parent_id) REFERENCES parent (id)" D=test,t=child \
  --defaults-file=./my.cnf \
  --execute


<snip>
2021-12-13T20:39:04 Creating triggers...
2021-12-13T20:39:04 Created triggers OK.
2021-12-13T20:39:04 Copying approximately 3 rows...
2021-12-13T20:39:04 Dropping triggers...
2021-12-13T20:39:04 Dropped triggers OK.
2021-12-13T20:39:04 Dropping new table...
2021-12-13T20:39:04 Dropped new table OK.
`test`.`child` was not altered.
        (in cleanup) 2021-12-13T20:39:04 Error copying rows from `test`.`child` to `test`.`_child_new`: 2021-12-13T20:39:04 Copying rows caused a MySQL error 1452:
    Level: Warning
     Code: 1452
  Message: Cannot add or update a child row: a foreign key constraint fails (`test`.`_child_new`, CONSTRAINT `fk_test` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
    Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_child_new` (`id`, `parent_id`) SELECT `id`, `parent_id` FROM `test`.`child` LOCK IN SHARE MODE /*pt-online-schema-change 60956 copy table*/
2021-12-13T20:39:04 Dropping triggers...
2021-12-13T20:39:04 Dropped triggers OK.
`test`.`child` was not altered.

エラーになりました。
mysqlコマンドで実施した結果と同様のエラーメッセージが表示されています。

gh-ost

gh-ost \
  --alter="ADD CONSTRAINT fk_test FOREIGN KEY (parent_id) REFERENCES parent (id)" \
  --database="test" \
  --table="child" \
  --host=127.0.0.1 \
  --port=21224 \
  --user="msandbox" \
  --password='msandbox' \
  --execute

<snip>
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000001:85347; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
[2021/12/13 20:40:52] [info] binlogsyncer.go:164 syncer is closing...
[2021/12/13 20:40:52] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/12/13 20:40:52] [info] binlogsyncer.go:179 syncer is closed
# Done

#Done がでて完了しました。
結果を見ると、child テーブルにForeign Keyが作成されています。
しかし、データのid=3(parent_id=1000)の行が損失しました。

mysql> SHOW CREATE TABLE child;
CREATE TABLE `child` (
  `id` bigint NOT NULL,
  `parent_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `fk_test` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
)

mysql> SELECT * FROM child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
+----+-----------+

Check制約追加

最後に、8.0の新機能Check制約についても試してみます。 以下のようなテーブルとデータを用意します。
num < 10の条件でCheck制約を追加します。 しかし、num=1000が存在します。

mysql> CREATE TABLE `t2` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `num` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

mysql> SELECT * FROM t2;
+----+----------+
| id | check_no |
+----+----------+
|  1 |        1 |
|  2 |        2 |
|  3 |     1000 |
+----+----------+

mysqlコマンド

mysql> ALTER TABLE t2 ADD CONSTRAINT num_check CHECK (num < 10);
ERROR 3819 (HY000): Check constraint 'num_check' is violated.

エラーになります。

pt-osc

pt-online-schema-change \
  --alter "ADD CONSTRAINT num_check CHECK (num < 10)" D=test,t=t2 \
  --defaults-file=./my.cnf \
  --execute

<snip>
2021-12-13T20:53:31 Creating triggers...
2021-12-13T20:53:31 Created triggers OK.
2021-12-13T20:53:31 Copying approximately 3 rows...
2021-12-13T20:53:31 Dropping triggers...
2021-12-13T20:53:31 Dropped triggers OK.
2021-12-13T20:53:31 Dropping new table...
2021-12-13T20:53:31 Dropped new table OK.
`test`.`t2` was not altered.
        (in cleanup) 2021-12-13T20:53:31 Error copying rows from `test`.`t2` to `test`.`_t2_new`: 2021-12-13T20:53:31 Copying rows caused a MySQL error 3819:
    Level: Warning
     Code: 3819
  Message: Check constraint 'num_check' is violated.
    Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_t2_new` (`id`, `num`) SELECT `id`, `num` FROM `test`.`t2` LOCK IN SHARE MODE /*pt-online-schema-change 65146 copy table*/
2021-12-13T20:53:31 Dropping triggers...
2021-12-13T20:53:31 Dropped triggers OK.
`test`.`t2` was not altered.

エラーになりました。
mysqlコマンドで実施した結果と同様のエラーメッセージが表示されています。

gh-ost

gh-ost \
  --alter="ADD CONSTRAINT num_check CHECK (num < 10)" \
  --database="test" \
  --table="t2" \
  --host=127.0.0.1 \
  --port=21224 \
  --user="msandbox" \
  --password='msandbox' \
  --execute

<snip>
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000001:110085; Lag: 0.01s, HeartbeatLag: 0.02s, State: migrating; ETA: due
[2021/12/13 20:54:17] [info] binlogsyncer.go:164 syncer is closing...
[2021/12/13 20:54:17] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/12/13 20:54:17] [info] binlogsyncer.go:179 syncer is closed
# Done

#Done がでて完了しました。
結果を見ると、、t2 テーブルにCheck制約が作成されています。
しかし、データのid=3(num=1000)の行が損失しました。

mysql> SHOW CREATE TABLE t2;

CREATE TABLE `t2` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `num` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `num_check` CHECK ((`num` < 10))
)

mysql> SELECT * FROM t2;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+

まとめ

今回の結果は以下の通りです。

条件 mysqlコマンド pt-osc結果 gh-ost結果
Unique keyの追加 エラー データ損失する(Optionで制御あり) データ損失する
既存の Unique key付きカラムの変更 エラー データ損失する データ損失する
Foreign Key追加 エラー エラー データ損失する
Check制約追加 エラー エラー データ損失する

pt-oscのほうが制約違反をちゃんと拾ってエラーにしてくれていそうです。
ちなみに、pt-oscもgh-ostも既存のデータをコピーする際にINSERT IGNOREを使用します。
そこでpt-oscはSHOW WARNINGSを使って丁寧にエラーチェックをしているため、制約違反時にエラーにしてくれるとのことをyokuさんが教えてくれました!! さすがやで!

日々の覚書: INSERT IGNORE INTOがNOT NULL DEFAULTを裏切る

pt-oscもgh-ostもたいへん便利で運用者を助けてくれる強力なツールですが、闇雲に実施するのではなく、制約違反などが起こりうるAlter Tableのリスクもしっかりと把握して使っていきたいですね。