日々の覚書: lefredのMySQL Quizがあまりに素敵だったので日本語で解説

@lefred はMySQL Community Teamの中の人。彼が出したクイズが素晴らしい。

MySQLがﾁｮｯﾄﾜｶﾙ人（特にDBAを生業にしているとこの問題はハマって面白いと思う）であれば、できればこの解説を読む前に映像を見て考えた方が面白い。回答編ももう出ている。

Hey @MySQL experts, are you ready for a small quiz? Looking forward to your replies! #MySQL #MySQLCommunity #MySQLQuiz 🐬❓ pic.twitter.com/YJzQmCc77V
— lefred (@lefred) March 20, 2024

なお環境はVanilla MySQL(ソースコード改変を行っていない、配布されているMySQL)で、 MySQLは5.7でも8.0でも8.3でも可能。

まずは設問。

ちょっと順番を変えてまずはテーブル定義から（これは @lefred のオリジナルでは動画の最後にある）


mysql80 9> SHOW CREATE TABLE t1\G

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `real_id` int NOT NULL AUTO_INCREMENT,

  `id` int DEFAULT NULL,

  PRIMARY KEY (`real_id`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

real_id がAuto_incrementのPrimary Key、idはユニークでもなんでもない単なるINT型のカラムである。InnoDB。

テーブルに入っている初期データはこんな感じ。 real_id はauto_incrementで歯抜けのない連番、1～16。idは単なるINT型なのでダブってるのもある（ここがオリジナルの動画の最初の部分）


mysql80 9> SELECT * FROM t1;

+---------+------+

| real_id | id   |

+---------+------+

|       1 |    1 |

|       2 |    2 |

|       3 |    3 |

|       4 |    4 |

|       5 |    5 |

|       6 |    5 |

|       7 |    6 |

|       8 |    8 |

|       9 |    2 |

|      10 |    3 |

|      11 |    2 |

|      12 |    1 |

|      13 |    2 |

|      14 |    3 |

|      15 |    4 |

|      16 |    5 |

+---------+------+

16 rows in set (0.00 sec)

ここに1行INSERTする。


mysql80 9> INSERT INTO t1 VALUES (0, 6);

Query OK, 0 rows affected (0.01 sec)

さてちょっとMySQLに詳しい人なら、( sql_mode に NO_AUTO_VALUE_ON_ZERO が指定されていない限り） auto_incrementなカラムに0を入れようとすると次の連番が払い出されることを知っているだろう。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用

というわけで、MySQLﾁｮｯﾄﾃﾞｷﾙ人はこれで (17, 6) の行が追加されることを期待する。

が。


mysql80 9> SELECT * FROM t1;

+---------+------+

| real_id | id   |

+---------+------+

|       1 |    1 |

|       2 |    2 |

|       3 |    3 |

|       4 |    4 |

|       5 |    5 |

|       6 |    5 |

|       7 |    6 |

|       8 |    8 |

|       9 |    2 |

|      10 |    3 |

|      11 |    2 |

|      12 |    1 |

|      13 |    2 |

|      14 |    3 |

|      15 |    4 |

|      16 |    5 |

+---------+------+

16 rows in set (0.00 sec)

増えないのである（動画じゃないからびっくりみが少ない…ちゃんと手元で再現させています）

このあとDELETEしてみても

mysql80 9> DELETE FROM t1 WHERE real_id > 10;
Query OK, 0 rows affected (0.00 sec)

mysql80 9> SELECT * FROM t1;
+---------+------+
| real_id | id   |
+---------+------+
|       1 |    1 |
|       2 |    2 |
|       3 |    3 |
|       4 |    4 |
|       5 |    5 |
|       6 |    5 |
|       7 |    6 |
|       8 |    8 |
|       9 |    2 |
|      10 |    3 |
|      11 |    2 |
|      12 |    1 |
|      13 |    2 |
|      14 |    3 |
|      15 |    4 |
|      16 |    5 |
+---------+------+
16 rows in set (0.00 sec)

減らないのである。

念のため sql_mode と read_only を確認してみても、特に変な設定をしているようには見えない。

mysql80 9> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 9> SELECT @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

さて、なぜでしょう？
というのがクイズの設問。

ここから少し俺がハマったところを説明する。

まず、この動画で違和感を感じた出力がINSERTとDELETEの直後。

mysql80 9> INSERT INTO t1 VALUES (0, 6);
Query OK, 0 rows affected (0.01 sec)

mysql80 9> DELETE FROM t1 WHERE real_id > 10;
Query OK, 0 rows affected (0.00 sec)

0 rows affected なのである。INSERTが成立、DELETEが成立していればここに自然数が入るはず。

INSERTのことは置いておいて、DELETEが0行削除ということはWHERE句で存在しない条件を指定すればありえる出力…たとえば、 WHERE real_id > 999 とか。

WHERE real_id > 10 と入力しているのに違うWHERE句を指定させる……そう、クエリリライトプラグインをまず思いついた。

見た目がINSERTでも UPDATE .. WHERE real_id > 999 とかに書き換えてしまえば 0 rows affected も実現できる。いけるんじゃね？

実際これは試す前に欠陥に気が付いたんだけど折角だから試してみる。8.0.12とそれ以降はINSERTやDELETEもリライトできるようになったらしい。

These statements are subject to rewriting:
- As of MySQL 8.0.12: SELECT, INSERT, REPLACE, UPDATE, and DELETE.
- Prior to MySQL 8.0.12: SELECT only.

https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-usage.html

$ mysql80 < /usr/mysql/8.0.36/share/install_rewriter.sql

mysql80 15> SELECT @@rewriter_enabled;
+--------------------+
| @@rewriter_enabled |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql80 15> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database) VALUES ('DELETE FROM t1 WHERE real_id > ?', 'DELETE FROM t1 WHERE real_id > ? + 999', 'd1');
Query OK, 1 row affected (0.00 sec)

mysql80 15> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database) VALUES ('INSERT INTO t1 VALUES (?, ?)', 'DELETE FROM t1 WHERE real_id > 999', 'd1');
Query OK, 1 row affected (0.00 sec)

mysql80 15> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)

これで

mysql80 15> INSERT INTO t1 VALUES (0, 6);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 15> DELETE FROM t1 WHERE real_id > 10;
Query OK, 0 rows affected, 1 warning (0.00 sec)

両方とも 0 rows affected の出力を出させられる。
しかし見ての通りワーニングが出てしまって

mysql80 15> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                    |
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'DELETE FROM t1 WHERE real_id > 10' rewritten to 'DELETE FROM t1 WHERE real_id > 10 + 999' by a query rewrite plugin |
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

リライトしたことがバレる。lefredの動画にはそんなものはなかったのでこれではない。

次に思いついたのが MySQL Router 8.3の新機能 Read-Write splitting と BLACKHOLEストレージエンジンを組み合わせて「レプリケーションソースではBLACKHOLE」「 binlog_format=ROW 」「レプリカではInnoDB」にすると、

Read-Write splittingによって SHOW CREATE TABLE と SELECT だけがレプリカに行く
INSERT と DELETE はレプリケーションソースに行くけれどBLACKHOLEストレージエンジンでbinlog_format=ROWだとバイナリログに吐かない
よってソースでいくら書いてもレプリカのデータは変わらない
でいけるかと思った。

しかしページを開いた瞬間これでもなかった。

Each client session can communicate with one read_write and one read_only destination.

再接続しない限りは同じ方を読み続けるならこれは成立しない。でも悔しいので ProxySQL を使ってやるだけやっておく。

$ sudo dnf install -y https://github.com/sysown/proxysql/releases/download/v2.6.2/proxysql-2.6.2-1-centos8.x86_64.rpm

$ sudo systemctl start proxysql

ProxySQLを起動して

$ mysql -h127.0.0.1 -P6032 -uadmin -padmin    ### ProxySQLの管理画面である

mysql> INSERT INTO  mysql_servers (hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 26244), (2, '127.0.0.2', 26245);
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO mysql_users (username, password) VALUES ('msandbox', 'msandbox');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*FROM.*t1', 2, 1), (1, '^(DELETE|INSERT)', 1, 1), (1, 'SELECT.*@@', 2, 1), (1, 'SHOW.*CREATE', 2, 1);
Query OK, 4 rows affected (0.00 sec)

mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

MySQL側もレプリケーション構成を組んだものを用意した。

master [localhost] {msandbox} ((none)) > use d1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

master [localhost] {msandbox} (d1) >  INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (5), (6), (8), (2), (3), (2), (1), (2), (3), (4), (5);
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} (d1) > SET sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} (d1) > ALTER TABLE t1 Engine = BLACKHOLE;
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0

$ ./use_all "SHOW CREATE TABLE d1.t1"
# master
Table   Create Table
t1      CREATE TABLE `t1` (\n  `real_id` int NOT NULL AUTO_INCREMENT,\n  `id` int DEFAULT NULL,\n  PRIMARY KEY (`real_id`)\n) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# server: 1:
Table   Create Table
t1      CREATE TABLE `t1` (\n  `real_id` int NOT NULL AUTO_INCREMENT,\n  `id` int DEFAULT NULL,\n  PRIMARY KEY (`real_id`)\n) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

$ ./use_all "SELECT COUNT(*) FROM d1.t1"
# master
COUNT(*)
0
# server: 1:
COUNT(*)
16

ではいこう。ProxySQLのトラフィックポートを使ってアクセス。

$ mysql -h127.0.0.1 -P6033 -umsandbox -pmsandbox d1

mysql> SELECT * FROM t1;
+---------+------+
| real_id | id   |
+---------+------+
|       1 |    1 |
|       2 |    2 |
|       3 |    3 |
|       4 |    4 |
|       5 |    5 |
|       6 |    5 |
|       7 |    6 |
|       8 |    8 |
|       9 |    2 |
|      10 |    3 |
|      11 |    2 |
|      12 |    1 |
|      13 |    2 |
|      14 |    3 |
|      15 |    4 |
|      16 |    5 |
+---------+------+
16 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `real_id` int NOT NULL AUTO_INCREMENT,
  `id` int DEFAULT NULL,
  PRIMARY KEY (`real_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

SELECT..FROM t1 と SHOW CREATE TABLE はちゃんとレプリカにルーティングされていて

mysql> SELECT @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

SELECT @@ はソースにルーティングされている。

mysql> DELETE FROM t1 WHERE real_id > 10;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Warning | 1870 | Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 't1.' |
+---------+------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+---------+------+
| real_id | id   |
+---------+------+
|       1 |    1 |
|       2 |    2 |
|       3 |    3 |
|       4 |    4 |
|       5 |    5 |
|       6 |    5 |
|       7 |    6 |
|       8 |    8 |
|       9 |    2 |
|      10 |    3 |
|      11 |    2 |
|      12 |    1 |
|      13 |    2 |
|      14 |    3 |
|      15 |    4 |
|      16 |    5 |
+---------+------+
16 rows in set (0.00 sec)

DELETE はそれっぽく見えたけど、 binlog_format=ROW だとバイナリログに吐かれないよというワーニングが出てしまったのでやっぱり違う。

あと、

mysql> INSERT INTO t1 VALUES (0, 6);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+---------+------+
| real_id | id   |
+---------+------+
|       1 |    1 |
|       2 |    2 |
|       3 |    3 |
|       4 |    4 |
|       5 |    5 |
|       6 |    5 |
|       7 |    6 |
|       8 |    8 |
|       9 |    2 |
|      10 |    3 |
|      11 |    2 |
|      12 |    1 |
|      13 |    2 |
|      14 |    3 |
|      15 |    4 |
|      16 |    5 |
+---------+------+
16 rows in set (0.00 sec)

INSERT はワーニング出ないなと思ったらしっかりバイナリログには吐いていて、

# at 356
#240404  7:06:02 server id 1  end_log_pos 400 CRC32 0x8d0ada3d  Write_rows: table id 83 flags: STMT_END_F

BINLOG '
WlEOZhMBAAAALwAAAGQBAAAAAFMAAAAAAAEAAmQxAAJ0MQACAwMAAgEBAF+zuTg=
WlEOZh4BAAAALAAAAJABAAAAAFMAAAAAAAEAAgAC/wABAAAABgAAAD3aCo0=
'/*!*/;
### INSERT INTO `d1`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=6 /* INT meta=0 nullable=1 is_null=0 */

レプリケーション止まってた…INSERTだけはちゃんとバイナリログに吐くのかよ。何その仕様。。

2024-04-04T07:06:02.751818Z 10 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bi
n.000003, end_log_pos 400; Could not execute Write_rows event on table d1.t1; Duplicate entry '1' for key 't1.PRIMARY', Error_code: 1062; handler error HA_ERR
_FOUND_DUPP_KEY; the event's source log mysql-bin.000003, end_log_pos 400, Error_code: MY-001062

というわけでこれも失敗(´･ω･｀)

答え。

gtid_mode がOFF以外である必要がある。それ以外はデフォルトのままでOK。(OFFだとgitd_nextを指定しようとした時にエラーになる)

Here is the solution, thank you to all who gave a reply! #MySQLCommunity #MySQLQuiz pic.twitter.com/ZCRvhsjDnZ
— lefred (@lefred) March 21, 2024

mysql80 8> SELECT @@gtid_next;
+----------------------------------------------+
| @@gtid_next                                  |
+----------------------------------------------+
| 77ebe912-be70-11ee-92eb-02001703a64e:1-10000 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql80 8> SET gtid_next = '77ebe912-be70-11ee-92eb-02001703a64e:100';  -- 1でも2でも構わない
Query OK, 0 rows affected (0.00 sec)

mysql80 8> DELETE FROM t1 WHERE real_id > 10;
Query OK, 0 rows affected (0.00 sec)

mysql80 8> INSERT INTO t1 VALUES (0, 6);
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT * FROM t1;
+---------+------+
| real_id | id   |
+---------+------+
|       1 |    1 |
|       2 |    2 |
|       3 |    3 |
|       4 |    4 |
|       5 |    5 |
|       6 |    5 |
|       7 |    6 |
|       8 |    8 |
|       9 |    2 |
|      10 |    3 |
|      11 |    2 |
|      12 |    1 |
|      13 |    2 |
|      14 |    3 |
|      15 |    4 |
|      16 |    5 |
+---------+------+
16 rows in set (0.01 sec)

「 gtid_next をなんでもいいから既に実行済の ( gtid_executed に含まれている）ものにセットする」
「既にgtid_executedに含まれているgtid_nextを持ったトランザクションはその内容によらず常にスキップされる」
というもの（今探してみたんだけどレプリケーション以外でもこれが成立するって明示してるドキュメントが見当たらない…？）

これはもともとはGTID環境下でのリレーログの二重受信（レプリカクラッシュだったり、メッシュ型のマルチソースレプリケーションを組んでいて複数のマシンから同じ内容のバイナリログが送られてくる環境だったり）をしても後から受信した方を空振りさせてレプリケーションを続けるための仕組み（GTIDのレプリケーションで特定のステートメントをスキップさせたい時にgitd_nextを指定して空COMMITするのはこれ。空COMMITが先行した同じgtidになるため後ろから来たSQL Thread由来のトランザクションがスキップされる）

https://dev.mysql.com/doc/refman/8.0/en/replication-administration-skip.html#replication-administration-skip-gtid

あー楽しかった。

日々の覚書

GA

2024/04/04

lefredのMySQL Quizがあまりに素敵だったので日本語で解説

0 件のコメント :

コメントを投稿