MySQL 8.4もまだ来てないのでテスト用DBの新顔 airportdb でも手元のMySQLに突っ込むメモ

1.

MySQL :: Other MySQL Documentation から “airportdb database (large dataset, intended for MySQL on OCI and HeatWave)” を探してダウンロード。HeatWave用って書いてあるけど別にVanillaでもOK。

​

   

$ wget https://downloads.mysql.com/docs/airport-db.tar.gz
--2024-04-18 00:43:20--  https://downloads.mysql.com/docs/airport-db.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.51.143.41, 2600:140b:2:99d::2e31, 2600:140b:2:99c::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.51.143.41|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 655687708 (625M) [application/x-gzip]
Saving to: 'airport-db.tar.gz'

100%[==============================================================================================================================================>] 655,687,708 11.5MB/s   in 52s

2024-04-18 00:44:12 (12.0 MB/s) - 'airport-db.tar.gz' saved [655687708/655687708]

​

2. 得られたファイルを展開します。600MBくらいあったけど展開は速かった。

$ time tar xf airport-db.tar.gz
real    0m5.134s
user    0m4.424s
sys     0m1.362s

​

3. `airport-db` ディレクトリの下に色々ぶら下がってるので拝見。

$ ll
total 641612
-rwxr-xr-x 1 yoku0825 yoku0825     2586 Apr  1 07:05 @.done.json
-rwxr-xr-x 1 yoku0825 yoku0825      859 Apr  1 07:05 @.json
-rwxr-xr-x 1 yoku0825 yoku0825    46349 Apr  1 07:05 @.manifest.json
-rwxr-xr-x 1 yoku0825 yoku0825      240 Apr  1 07:05 @.post.sql
-rwxr-xr-x 1 yoku0825 yoku0825      240 Apr  1 07:05 @.sql
-rwxr-xr-x 1 yoku0825 yoku0825     4884 Apr  1 07:05 README.txt
-rwxr-xr-x 1 yoku0825 yoku0825     1199 Apr  1 07:05 airportdb.json
-rwxr-xr-x 1 yoku0825 yoku0825      583 Apr  1 07:05 airportdb.sql

..
-rwxr-xr-x 1 yoku0825 yoku0825     1752 Apr  1 07:05 airportdb@weatherdata@0.tsv.zst.idx
-rwxr-xr-x 1 yoku0825 yoku0825  4733492 Apr  1 07:05 airportdb@weatherdata@@1.tsv.zst
-rwxr-xr-x 1 yoku0825 yoku0825      240 Apr  1 07:05 airportdb@weatherdata@@1.tsv.zst.idx

​

見るからにMySQL ShellでダンプされたやつなのでMySQL Shellでインポートが必要。

目立たないREADME.txtの最後にインポート方法が書いてある。

いかにもHeatWave用のはVanillaでは転けるから、実行しないかまたは叩いて転けさせれば良いと思う。

使う関数は util.loadDump .

 MySQL  localhost  SQL > SET GLOBAL local_infile = 1;  -- util.loadDumpはlocal_infile=1 必須
Query OK, 0 rows affected (0.0007 sec)

 MySQL  localhost  SQL > \js  -- JSモード変更(pyでもいい)
Switching to JavaScript mode...

 MySQL  localhost  JS > util.loadDump("./", {deferTableIndexes: "all", ignoreVersion: true})
Loading DDL and Data from './' using 4 threads.
Opening dump...
NOTE: Dump format has version 1.0.2 and was created by an older version of MySQL Shell. If you experience problems loading it, please recreate the dump using the current version of MySQL Shell and try again.
Target is MySQL 8.0.36. Dump was produced from MySQL 8.0.26-cloud
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
4 thds loading / 15% (317.41 MB / 2.03 GB), 9.49 MB/s, 12 / 14 tables done

..

​

util.loadDumpの第一引数はトップディレクトリへの相対パスなので、さっきもぐりこんだところがまさにそのディレクトリ = “./“ にした。他はリソースに余力があれば “threads”: 20, とか入れれば良いのだと思う（速くなるんですよこれがびっくりに）

ConoHaの2コア1GBメモリのプラン だけど割とすぐ20分もかからず終わったはず（ただし俺は今たぶん innodb_flush_log_at_trx_commit = 0, sync_binlog = 0してある）

突っ込んだ直後なのでANALYZE TABLEでもかけておきましょうか。
面倒なので mysqlcheck —analyze —all-databases で全部にかけました

​

$ mysqlcheck --analyze --all-databases -uroot -S /usr/mysql/8.0.36/data/mysql.sock
airportdb.airline                                  OK
airportdb.airplane                                 OK
airportdb.airplane_type                            OK
airportdb.airport                                  OK
airportdb.airport_geo                              OK
airportdb.airport_reachable                        OK
airportdb.booking                                  OK
airportdb.employee                                 OK
airportdb.flight                                   OK
airportdb.flight_log                               OK
airportdb.flightschedule                           OK
airportdb.passenger                                OK
airportdb.passengerdetails                         OK
airportdb.weatherdata                              OK
bench.activity                                     OK
bench.project                                      OK
bench.tag_ticket                                   OK
bench.team                                         OK
bench.team_user                                    OK
bench.ticket                                       OK
bench.user                                         OK
d1.t1                                              OK
d1.t2                                              OK
d2.child                                           OK
d2.parent                                          OK
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log
note     : The storage engine for the table doesn't support analyze
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.replication_asynchronous_connection_failover OK
mysql.replication_asynchronous_connection_failover_managed OK
mysql.replication_group_configuration_version      OK
mysql.replication_group_member_actions             OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log
note     : The storage engine for the table doesn't support analyze
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
sbtest.sbtest1                                     OK
sys.sys_config                                     OK
world.city                                         OK
world.country                                      OK
world.countrylanguage                              OK

​

折角なので一応MySQL Workbenchで出させたER図も置いておく。

これだけ見ても、どんなクエリがどんな日本語に

CREATE VIEW, ALTER VIEWのERROR 1142 (42000): ANY command denied to user 'root'@'localhost' for table ''にハマった

TL;DR

• これは5.7のバグ。8.0では起こらない

  • MySQL Bugs: #91122: Failed to create a view containing a from subquery(No selected database)

• エラーメッセージからここへ来たあなたへ。 use ステートメントを忘れています。 use してからもういちど CREATE VIEW なり ALTER VIEW なりを実行するのです

---

スキーマを何も指定せずに接続して、かつ、FROM句サブクエリがあるものを実行しようとするとエラる。ビュー名を完全修飾していても、使っているテーブルを完全修飾していてもエラる。しかも root@localhost なのに ANY command denied とか言われてビビる。

【2024/04/06 18:41】

しかも ANY command と言われて サブクエリのANY なんてどこにも使ってないよと余計混乱した

mysql57 9> SELECT DATABASE();

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

| DATABASE() |

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

| NULL       |

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

1 row in set (0.00 sec)

mysql57 9> ALTER VIEW d1.v1 AS SELECT * FROM (SELECT * FROM d1.t1) AS t;

ERROR 1142 (42000): ANY command denied to user 'root'@'localhost' for table ''

どこでも良いので use でデフォルトスキーマを指定するとエラーにならなくなる。

mysql57 9> use mysql
Database changed

mysql57 9> ALTER VIEW d1.v1 AS SELECT * FROM (SELECT * FROM d1.t1) AS t;
Query OK, 0 rows affected (0.00 sec)

なお、ビュー名または使っているテーブルが完全修飾されていない場合はちゃんと想像どおりのエラーで転ける。

mysql57 8> ALTER VIEW v1 AS SELECT * FROM (SELECT * FROM d1.t1) AS t;
ERROR 1046 (3D000): No database selected

mysql57 8> ALTER VIEW d1.v1 AS SELECT * FROM (SELECT * FROM t1) AS t;
ERROR 1046 (3D000): No database selected

WHERE句サブクエリの場合は完全修飾していれば転けない。完全修飾していなければ No database selected のエラー。

mysql57 8> ALTER VIEW d1.v1 AS SELECT * FROM d1.t1 WHERE num IN (SELECT num FROM d1.t1);
Query OK, 0 rows affected (0.00 sec)

mysql57 8> ALTER VIEW d1.v1 AS SELECT * FROM d1.t1 WHERE num IN (SELECT num FROM t1);
ERROR 1046 (3D000): No database selected

8.0ではFROM句で完全修飾していてもこのエラーは起こらずに成功する。

mysql80 18> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.01 sec)

mysql80 18> ALTER VIEW d1.v1 AS SELECT * FROM (SELECT * FROM d1.t1) AS t;
Query OK, 0 rows affected (0.02 sec)

root@localhost なのに ANY command denied とか言われるので壊したかと思った…

​

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

あー楽しかった。

​