Myリノベ 8.0.21に参加

先日「MySQL Release note でわいわい言う勉強会 8.0.21」に参加してまいりました。

今回も最新のMySQL8.0.21のリリースノートをみんなで見ながら、思いつくままに語る会で、その中で僕は相変わらずROM専ですが参加しておりました。

流れとしては坂井さんがオープニングトーク、次にまつさんがJSON_VALUE()のLTを語ってくださり、その他の機能についてはリリースノートを見ながら…という感じでした。

いずれyoutubeあたりに動画が上がると思いますので、全体が知りたい方はtwitterやslackでチェックしてるとよいかと。

8.0.21からはCREATE TABLE ... SELECT気軽に使えるぜ！

さて、その会の中でyoku0825さんがつぶやいたCREATE TABLE ... SELECTの以下の話、マイナーかもしれないけど結構驚きの変更だと思うんですよね。

On storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is now logged as one transaction in the binary log when row-based replication is in use.
Previously, it was logged as two transactions, one to create the table, and the other to insert data.
With this change, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication.
For more information, see Atomic Data Definition Statement Support.

見たことないですか？

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

GTIDを使う人は頭に叩き込まれてるはずなんです。

行ベースで
GTID使ってる場合
CREATE TABLE ... SELECTはできないって

だって、ドキュメントにもあるんだから。

みんな5.6以降、GTIDを使う人はCREATE TABLE ... SELECT使わないぞ！と心に誓ってたはずなんです。

でも、

MySQL8.0.21からはフツーにレプリカに反映されるようになったんです。

試してみた

作業概要

環境構築

僕はdbdeployer使ってます。とてもかんたんに環境作れますよ。

dbdeployerの詳しい説明については適当にググってもらうとかすればいいんですが、ざっくり書くと

MySQLのtarファイルゲットしてきて
dbdeployerインストールして
dbdeployer unpack mysql-xxxxxxxx-8.0.xx-linux-x86_64-minimal.tar.xz　みたいな感じで解凍して
以下のようなコマンドでレプリケーション環境を作れます。

dbdeployer deploy replication 8.0.xx \
    --custom-role-name=R_POWERFUL \
    --custom-role-privileges='ALL PRIVILEGES' \
    --custom-role-target='*.*' \
    --custom-role-extra='WITH GRANT OPTION' \
    --default-role=R_POWERFUL \
    --bind-address=0.0.0.0 \
    --remote-access='%' \
    --gtid \
    --db-user=admin \
    --db-password=xxxxx

こんな感じで2つのソースレプリカ形式のDBのペアを用意しました。

$ dbdeployer sandboxes
 rsandbox_8_0_20          :   master-slave           8.0.20   [21021 31021 21022 31022 21023 31023 ]
 rsandbox_8_0_21          :   master-slave           8.0.21   [21122 31122 21123 31123 21124 31124 ]

検証開始

そして、２つのバージョンで比較するんですが、やることはかんたん。

テーブル（t1）を作って
テーブルに適当にデータ入れて
一応テーブルにデータ入ったこと確認して
そのテーブルからデータコピーをCREATE TABLE ...SELECT構文でやって
レプリカ側のほうにあるか確認

というだけの作業。

GTIDの確認

こんな感じでGTIDモードはいずれもONです

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 -e "show variables like 'gtid_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 -e "show variables like 'gtid_mode'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+

MySQL8.0.20

ソース（マスター）側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> CREATE TABLE `t1` (
    ->    `id` int(11) NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.04 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
(root@127.0.0.1) [test]> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

ERROR 1786が出ますね。

当然ソース・レプリカいずれのインスタンスにもt2テーブルは作られていません。

これを8.0.21でもやります。

MySQL8.0.21

ソース（マスター）側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> CREATE TABLE `t1` (
    ->    `id` int(11) NOT NULL AUTO_INCREMENT,
    ->    PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

エラーが出ない！

んじゃ、レプリカ側はどうなってるかな？

レプリカ（スレーブ）側

$ mysql -uroot -pxxxxx -h127.0.0.1 -P21123 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

できてるぅ！ヒャッホー！

binlogの比較

GITDがONだと比較できないので、OFFにしてmysqlbinlogで覗いてみる。

MySQL8.0.20

# at 9418
#200814 18:50:04 server id 100  end_log_pos 9543 CRC32 0xccceb3e3   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398604/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0'
)
/*!*/;
# at 9543
#200814 18:50:04 server id 100  end_log_pos 9622 CRC32 0xf3e5f383   Anonymous_GTID  last_committed=38   sequence_number=39  rbr_only=yes    original_committed_timestamp=1597398604099299   immediate_commit_timestamp=1597398604099299 transaction_length=283
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST)
# immediate_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST)
/*!80001 SET @@session.original_commit_timestamp=1597398604099299*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 9622
#200814 18:50:04 server id 100  end_log_pos 9697 CRC32 0x796b6528   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398604/*!*/;
BEGIN
/*!*/;
# at 9697
#200814 18:50:04 server id 100  end_log_pos 9745 CRC32 0x1fde1b11   Table_map: `test`.`t2` mapped to number 112
# at 9745
#200814 18:50:04 server id 100  end_log_pos 9795 CRC32 0xc4bf3987   Write_rows: table id 112 flags: STMT_END_F

BINLOG '
TF42XxNkAAAAMAAAABEmAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQARG94f
TF42Xx5kAAAAMgAAAEMmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAIc5v8Q=
'/*!*/;
# at 9795
#200814 18:50:04 server id 100  end_log_pos 9826 CRC32 0xbc8b4b0a   Xid = 71
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

MySQL8.0.21

# at 9496
#200814 18:47:28 server id 100  end_log_pos 9639 CRC32 0xe24b1dc8   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1597398448/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t2` (
  `id` int NOT NULL DEFAULT '0'
) START TRANSACTION
/*!*/;
# at 9639
#200814 18:47:28 server id 100  end_log_pos 9687 CRC32 0x4972ee4a   Table_map: `test`.`t2` mapped to number 112
# at 9687
#200814 18:47:28 server id 100  end_log_pos 9737 CRC32 0x56fa2558   Write_rows: table id 112 flags: STMT_END_F

BINLOG '
sF02XxNkAAAAMAAAANclAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQBK7nJJ
sF02Xx5kAAAAMgAAAAkmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAFgl+lY=
'/*!*/;
# at 9737
#200814 18:47:28 server id 100  end_log_pos 9768 CRC32 0xa09930d6   Xid = 71
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

明らかに8.0.21のほうが短いね。

読み解いたわけじゃないけど、雰囲気は感じる。

所感

リリースノートではサラッと一行で書かれてるけど、結構な変更だと思いません？

GTIDが無効な場合でもクラッシュ時とかにレプリカにはテーブルはあるけどデータが無いとか無くなるわけだし、

GTIDが有効な場合でもエラーにならずにCREATE TABLE ... SELECTが使えるようになったわけです。

リリースノートをみんなで眺めることで、そういう知見が多くの人の目に止まったんです。

素敵な会ですね！

この話以外にも気になる機能について色々語られてました。

非公式の何かがちょろっと配布されたりして、意外と録画されていないところも楽しい勉強会ｗ

みなさんも次回は参加してみては？

（今日はHunter×Hunter アニメ版のようなタイトルの付け方してみました）

41から始めました

シンキノウ×デ×シルキノウ (CREATE TABLE ... SELECT)

Myリノベ 8.0.21に参加

8.0.21からはCREATE TABLE ... SELECT気軽に使えるぜ！

MySQL8.0.21からはフツーにレプリカに反映されるようになったんです。

試してみた

作業概要

環境構築

検証開始

GTIDの確認

MySQL8.0.20

ソース（マスター）側

MySQL8.0.21

ソース（マスター）側

レプリカ（スレーブ）側

できてるぅ！ヒャッホー！

binlogの比較

MySQL8.0.20

MySQL8.0.21

所感

素敵な会ですね！