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_bin
はcase 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追加
parent
とchild
テーブルを用意します。
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のリスクもしっかりと把握して使っていきたいですね。