MySQL で super_read_only=on なのに GTID が割り当てられる statement などについて

2026/01/19

sejima

こんにちわ。せじまです。

2025-10-21 にリリースされた MySQL 9.5.0、個人的には久しぶりに Innovation Release だなと思わされる変更が入ってました。

MySQL :: MySQL 9.5 Release Notes :: Changes in MySQL 9.5.0 (2025-10-21, Innovation Release)

http://dev.mysql.com

To enable enhanced replication capabilities by default, the default values of gtid_mode has been changed to ON. Additionally, the default value of enforce_gtid_consistency has been changed from ON.

ついに、GTIDがデフォルトで有効化されることになりました。

みなさん、GTID使ってますか？ Bug #88720でAffects Meのボタンは押しましたか？ MySQL 8.4.1 で fix されたBug #35665076の commit log は読まれましたか？

Bug #88720もBug #35665076もご存知の方は、できればご同僚やご友人の方々にもお誘い合わせの上、Bug #88720でAffects Meをお願いいたします。

そうでない方は、これからこれらのバグなどについて解説しますので、読み終わったらできればBug #88720でAffects Meのご協力をお願いいたします。

でははじめます。

はじめに

MySQLでGTIDを使ったreplicationを行うとき、super_read_onlyオプションを有効にすることが良いとされています。（もう十年以上前の記事になりますが）Percona Database Performance Blogのsuper_read_only and GTID replicationの時点ですでに言及されています。

MySQLのreplicationが開始できないケースの一つとして、次のようなものがあります。

CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA; した際、source の gtid_purged が replica の gtid_executed のサブセットになってなかったら、ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS（あるいはER_MASTER_HAS_PURGED_REQUIRED_GTIDS）が発生する。

これを防ぐための有効な手段として、 SOURCE_AUTO_POSITION=1 で replication する際は replica で super_read_only=on にすることが望ましいと考えられます。

では、 replica で super_read_only=on にすることで何を防げるのかを見ていきましょう。

ER_SOURCE_HAS_PURGED_REQUIRED_GTIDSが発生する状況

MySQL 8.0公式ドキュメントの17.1.3.3 GTID 自動配置で、ER_MASTER_HAS_PURGED_REQUIRED_GTIDS(ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS)が発生するケースについて言及されているのですが、OSSなので生成AIに聞いたりドキュメントを読んだりするよりも、ソースコード読んだほうがより理解できるケースがしばしばあります。なのでソースコードを読んでいきます。

CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA; すると、MySQLのプロトコル的には replica から COM_BINLOG_DUMP_GTID が投げられるのですが、COM_BINLOG_DUMP_GTID 投げる際、 replica からは gtid_executed を source に送信しています。

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_replica.cc#L4188-L4215

source は replica から COM_BINLOG_DUMP_GTID を受け取ると、BinlogSenderクラスのオブジェクトを生成するのですが

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_source.cc#L985-L1043
https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_source.cc#L1054-L1059

BinlogSenderクラスのコンストラクタを見ると、replica から渡された gtid_executed は、exclude_gtids という変数名になっています。
https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.cc#L234-L241

Binlog_sender::Binlog_sender(THD *thd, const char *start_file,
                             my_off_t start_pos, Gtid_set *exclude_gtids,
                             uint32 flag)
    : m_thd(thd),
      m_packet(*thd->get_protocol_classic()->get_output_packet()),
      m_start_file(start_file),
      m_start_pos(start_pos),
      m_exclude_gtid(exclude_gtids),

234

235

236

237

238

239

240

241

Binlog_sender::Binlog_sender(THD *thd, const char *start_file,

my_off_t start_pos, Gtid_set *exclude_gtids,

uint32 flag)

: m_thd(thd),

m_packet(*thd->get_protocol_classic()->get_output_packet()),

m_start_file(start_file),

m_start_pos(start_pos),

m_exclude_gtid(exclude_gtids),

Binlog_sender::run() → Binlog_sender::init() → Binlog_sender::check_start_file() と見ていきますと

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.cc#L379-L382
https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.cc#L257-L298
https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.cc#L854

Binlog_sender::check_start_file() でどの binary log から送信するべきか判定しているのですが

i.e., throw error if slave's GTID executed set is not a superset of Master's purged GTID set.

slave(replica) の gtid_executed が master(source) の gtid_purged のスーパーセットでなければ、 master は error を返すといった意のコメントがあります。

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.cc#L899-L930

    /*
      Setting GTID_PURGED (when GTID_EXECUTED set is empty i.e., when
      previous_gtids are also empty) will make binlog rotate. That
      leaves first binary log with empty previous_gtids and second
      binary log's previous_gtids with the value of gtid_purged.
      In find_first_log_not_in_gtid_set() while we search for a binary
      log whose previous_gtid_set is subset of slave_gtid_executed,
      in this particular case, server will always find the first binary
      log with empty previous_gtids which is subset of any given
      slave_gtid_executed. Thus Master thinks that it found the first
      binary log which is actually not correct and unable to catch
      this error situation. Hence adding below extra if condition
      to check the situation. Slave should know about Master's purged GTIDs.
      If Slave's GTID executed + retrieved set does not contain Master's
      complete purged GTID list, that means Slave is requesting(expecting)
      GTIDs which were purged by Master. We should let Slave know about the
      situation. i.e., throw error if slave's GTID executed set is not
      a superset of Master's purged GTID set.
      The other case, where user deleted binary logs manually
      (without using 'PURGE BINARY LOGS' command) but gtid_purged
      is not set by the user, the following if condition cannot catch it.
      But that is not a problem because in find_first_log_not_in_gtid_set()
      while checking for subset previous_gtids binary log, the logic
      will not find one and an error ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS
      is thrown from there.
    */
    if (!gtid_state->get_lost_gtids()->is_subset(m_exclude_gtid)) {
      mysql_bin_log.report_missing_purged_gtids(m_exclude_gtid, errmsg);
      global_sid_lock->unlock();
      set_fatal_error(errmsg.c_str());
      return 1;
    }

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

Setting GTID_PURGED (when GTID_EXECUTED set is empty i.e., when

previous_gtids are also empty) will make binlog rotate. That

leaves first binary log with empty previous_gtids and second

binary log's previous_gtids with the value of gtid_purged.

In find_first_log_not_in_gtid_set() while we search for a binary

log whose previous_gtid_set is subset of slave_gtid_executed,

in this particular case, server will always find the first binary

log with empty previous_gtids which is subset of any given

slave_gtid_executed. Thus Master thinks that it found the first

binary log which is actually not correct and unable to catch

this error situation. Hence adding below extra if condition

to check the situation. Slave should know about Master's purged GTIDs.

If Slave's GTID executed + retrieved set does not contain Master's

complete purged GTID list, that means Slave is requesting(expecting)

GTIDs which were purged by Master. We should let Slave know about the

situation. i.e., throw error if slave's GTID executed set is not

a superset of Master's purged GTID set.

The other case, where user deleted binary logs manually

(without using 'PURGE BINARY LOGS' command) but gtid_purged

is not set by the user, the following if condition cannot catch it.

But that is not a problem because in find_first_log_not_in_gtid_set()

while checking for subset previous_gtids binary log, the logic

will not find one and an error ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS

is thrown from there.

if (!gtid_state->get_lost_gtids()->is_subset(m_exclude_gtid)) {

mysql_bin_log.report_missing_purged_gtids(m_exclude_gtid, errmsg);

global_sid_lock->unlock();

set_fatal_error(errmsg.c_str());

return 1;

}

実態は

    if (!gtid_state->get_lost_gtids()->is_subset(m_exclude_gtid)) {

925	if (!gtid_state->get_lost_gtids()->is_subset(m_exclude_gtid)) {

なので、サブセットでないかどうかを見ているわけですね。

replica の gtid_executed に含まれない GTID、replica が実行していない GTID の binlog event をsourceが持っていたならば、その GTID がどれくらい古くても、 replica に送る必要があります。そうしなければ、 source と replica は同期が取れていることを保証できません。（大変ざっくりした例をあげると、source上でどれほど昔に発行されたINSERTであっても、それがreplicaで実行されていなければ、sourceとreplicaのテーブルの状態は一致しないですよね）
このように考えていくと、replica の gtid_executed は、言い換えると、 source から送信すべき binlog event から除外できる GTID の集合とも言えます。（それでBinlogSenderのソースコード中では exclude_gtids という変数名が用いられているのではないかと思われます。）

最終的に、 source の gtid_executed で実行済みの GTID に紐づく binlog event を、すべて replica でも実行する必要があります。sourceからreplicaが必要とするGTID（に紐づくbinlog event）をすべて送信できるかの判定をするとき、 source は Gtid_state::get_lost_gtids() を呼んで、サブセットかどうか調べてます。 get_lost_gtids() は、 source の binlog から失われた GTID、これらの GTID を含む binlog はすでに purge されているということです。

ちなみに gtid_purged は、Gtid_state::get_lost_gtids() の値を参照しています。
https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/sys_vars.h#L2640-L2698

というわけで、source の gtid_purged が replica の gtid_executed のサブセットになってなかったらエラーになるわけです。

最終的にMYSQL_BIN_LOG::report_missing_gtids() でER_SOURCE_HAS_PURGED_REQUIRED_GTIDSの errmsg を生成して返すことになります。
https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/binlog.cc#L9213-L9253

ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS を予防するための super_read_only=on

よって、最初にあげた

CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA; した際、source の gtid_purged が replica の gtid_executed のサブセットになってなかったら、ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS（あるいはER_MASTER_HAS_PURGED_REQUIRED_GTIDS）が発生する。

となるわけで、これを防ぐためには replica で super_read_only=on を設定することが有効な手段となります。

具体的にどのような効果があるかといいますと、次のような状況を防げます。

例えば、source に対して replica が2台ぶら下がっている構成があるとします。replicaはそれぞれnode_a,node_bとします。
ある日node_aでsuper_read_only=offの状態でnode_aでのみADD INDEXなど実行した後、月日が流れADD INDEXしたときのbinlogがpurgeされたとします。
sourceのインスタンスが落ちて node_aを新しいsourceとして昇格させるため、node_bで CHANGE REPLICATION SOURCE TO ... SOURCE_HOST=node_a, SOURCE_AUTO_POSITION=1; START REPLICA; します。
node_aでのみADD INDEXしたときのbinlogがpurgeで失われ、ADD INDEXに関連したGTIDがnode_aのgtid_purgedに含まれている状態なので、新しいsource の gtid_purged が新しいreplica の gtid_executed のサブセットにならない（ADD INDEXしたときのGTIDが新しいsourceであるnode_aのgtid_purgedにのみ含まれており、新しいreplicaのnode_bのgtid_executedに存在しない）ので、ER_SOURCE_HAS_PURGED_REQUIRED_GTIDSが発生します。

replicaがsuper_read_only=onであれば、2.の時点でADD INDEXできないので、こういった状況を防げるわけですね。

ER_SOURCE_HAS_PURGED_REQUIRED_GTIDSが実際に発生した際の error message の補足事項

ER_SOURCE_HAS_PURGED_REQUIRED_GTIDSは、非常に猛烈にわかりにくい部分がありますので補足します。

具体的に試せるよう、参考までに docker-compose.yml や初期化用のscriptを用意してみます。

$ cat docker-compose.yml
services:

  mysql-source:
    image: container-registry.oracle.com/mysql/community-server:8.0
    command:
      - --enforce_gtid_consistency=ON
      - --gtid_mode=ON
      - --server_id=1
    environment: &environment
      TZ: Asia/Tokyo
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      MYSQL_DATABASE: test
      MYSQL_USER: test
      MYSQL_PASSWORD: pass
      MYSQL_REPLICATION_USER: repl
      MYSQL_REPLICATION_PASSWORD: pass
    volumes:
      - ./init_source.sh:/docker-entrypoint-initdb.d/init_source.sh:ro
    ports:
      - "13306:3306"
    networks:
      app_net:
        ipv4_address: 172.30.0.2

  mysql-replica:
    image: container-registry.oracle.com/mysql/community-server:8.0
    depends_on:
      - mysql-source
    command:
      - --enforce_gtid_consistency=ON
      - --gtid_mode=ON
      - --server_id=2
    environment:
      <<: *environment
      MYSQL_REPLICATION_HOST: mysql-source
    volumes:
      - ./init_replica.sh:/docker-entrypoint-initdb.d/init_replica.sh:ro
    ports:
      - "23306:3306"
    networks:
      app_net:
        ipv4_address: 172.30.0.3

  mysql-replica2:
    image: container-registry.oracle.com/mysql/community-server:8.0
    depends_on:
      - mysql-replica
    command:
      - --enforce_gtid_consistency=ON
      - --gtid_mode=ON
      - --server_id=3
    environment:
      <<: *environment
      MYSQL_REPLICATION_HOST: mysql-source
    volumes:
      - ./init_replica.sh:/docker-entrypoint-initdb.d/init_replica.sh:ro
    ports:
      - "33306:3306"
    networks:
      app_net:
        ipv4_address: 172.30.0.4

networks:
  app_net:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.30.0.0/24
$ cat init_source.sh
#!/bin/bash

if [ -v MYSQL_REPLICATION_USER -a -v MYSQL_REPLICATION_PASSWORD ]; then
  mysql -u root -v mysql <<SQL
    CREATE USER '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED BY '$MYSQL_REPLICATION_PASSWORD';
    GRANT REPLICATION SLAVE ON *.* TO '$MYSQL_REPLICATION_USER'@'%';
SQL
fi
$ cat init_replica.sh
#!/bin/bash

if [ -v MYSQL_REPLICATION_USER -a -v MYSQL_REPLICATION_PASSWORD -a -v MYSQL_REPLICATION_HOST ]; then
  mysql -u root -v <<SQL
    CHANGE REPLICATION SOURCE TO
      SOURCE_HOST = '$MYSQL_REPLICATION_HOST',
      SOURCE_USER = '$MYSQL_REPLICATION_USER',
      SOURCE_PASSWORD = '$MYSQL_REPLICATION_PASSWORD',
      SOURCE_SSL = 1,
      SOURCE_AUTO_POSITION = 1;
SQL
  mysqladmin ping --wait=5 -h "$MYSQL_REPLICATION_HOST"
  mysql -u root -v -e "START SLAVE;"
fi
$

$ cat docker-compose.yml

services:

mysql-source:

image: container-registry.oracle.com/mysql/community-server:8.0

command:

- --enforce_gtid_consistency=ON

- --gtid_mode=ON

- --server_id=1

environment: &environment

TZ: Asia/Tokyo

MYSQL_ALLOW_EMPTY_PASSWORD: 1

MYSQL_DATABASE: test

MYSQL_USER: test

MYSQL_PASSWORD: pass

MYSQL_REPLICATION_USER: repl

MYSQL_REPLICATION_PASSWORD: pass

volumes:

- ./init_source.sh:/docker-entrypoint-initdb.d/init_source.sh:ro

ports:

- "13306:3306"

networks:

app_net:

ipv4_address: 172.30.0.2

mysql-replica:

image: container-registry.oracle.com/mysql/community-server:8.0

depends_on:

- mysql-source

command:

- --enforce_gtid_consistency=ON

- --gtid_mode=ON

- --server_id=2

environment:

<<: *environment

MYSQL_REPLICATION_HOST: mysql-source

volumes:

- ./init_replica.sh:/docker-entrypoint-initdb.d/init_replica.sh:ro

ports:

- "23306:3306"

networks:

app_net:

ipv4_address: 172.30.0.3

mysql-replica2:

image: container-registry.oracle.com/mysql/community-server:8.0

depends_on:

- mysql-replica

command:

- --enforce_gtid_consistency=ON

- --gtid_mode=ON

- --server_id=3

environment:

<<: *environment

MYSQL_REPLICATION_HOST: mysql-source

volumes:

- ./init_replica.sh:/docker-entrypoint-initdb.d/init_replica.sh:ro

ports:

- "33306:3306"

networks:

app_net:

ipv4_address: 172.30.0.4

networks:

app_net:

driver: bridge

ipam:

driver: default

config:

- subnet: 172.30.0.0/24

$ cat init_source.sh

#!/bin/bash

if [ -v MYSQL_REPLICATION_USER -a -v MYSQL_REPLICATION_PASSWORD ]; then

mysql -u root -v mysql <<SQL

CREATE USER '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED BY '$MYSQL_REPLICATION_PASSWORD';

GRANT REPLICATION SLAVE ON *.* TO '$MYSQL_REPLICATION_USER'@'%';

SQL

$ cat init_replica.sh

#!/bin/bash

if [ -v MYSQL_REPLICATION_USER -a -v MYSQL_REPLICATION_PASSWORD -a -v MYSQL_REPLICATION_HOST ]; then

mysql -u root -v <<SQL

CHANGE REPLICATION SOURCE TO

SOURCE_HOST = '$MYSQL_REPLICATION_HOST',

SOURCE_USER = '$MYSQL_REPLICATION_USER',

SOURCE_PASSWORD = '$MYSQL_REPLICATION_PASSWORD',

SOURCE_SSL = 1,

SOURCE_AUTO_POSITION = 1;

SQL

mysqladmin ping --wait=5 -h "$MYSQL_REPLICATION_HOST"

mysql -u root -v -e "START SLAVE;"

これで

docker-compose up -d する
docker-compose exec mysql-replica mysql して USE test;CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT NOT NULL);
docker-compose exec mysql-replica mysql して FLUSH BINARY LOGS; PURGE BINARY LOGS BEFORE DATE_ADD(NOW(), INTERVAL 1 SECOND);
docker-compose exec mysql-replica2 mysql して STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST='mysql-replica', SOURCE_USER='repl', SOURCE_PASSWORD='pass', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1; START REPLICA; すると、ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS が返ってくる

といったことを確認できます。ただ

$ docker-compose exec mysql-replica2 mysql -e 'show replica status\G'| grep -i err
                   Last_Errno: 0
                   Last_Error:
                Last_IO_Errno: 13114
                Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is '624769cb-f1e0-11f0-a025-52a98c1c970c:1-2', and the missing transactions are '6273818c-f1e0-11f0-8a08-0e5e9e614d1d:1''
               Last_SQL_Errno: 0
               Last_SQL_Error:
      Last_IO_Error_Timestamp: 260115 16:07:59
     Last_SQL_Error_Timestamp:
$ perror --version
perror  Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)
$ perror 13114
MySQL error code MY-013114 (ER_SERVER_SOURCE_FATAL_ERROR_READING_BINLOG): Got fatal error %d from source when reading data from binary log: '%-.512s'
$ perror 1236
MySQL error code MY-001236 (ER_SOURCE_FATAL_ERROR_READING_BINLOG): Got fatal error %d from source when reading data from binary log: '%-.512s'
$ perror 1789
MySQL error code MY-001789 (ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS): Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. %s
$

$ docker-compose exec mysql-replica2 mysql -e 'show replica status\G'| grep -i err

Last_Errno: 0

Last_Error:

Last_IO_Errno: 13114

Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is '624769cb-f1e0-11f0-a025-52a98c1c970c:1-2', and the missing transactions are '6273818c-f1e0-11f0-8a08-0e5e9e614d1d:1''

Last_SQL_Errno: 0

Last_SQL_Error:

Last_IO_Error_Timestamp: 260115 16:07:59

Last_SQL_Error_Timestamp:

$ perror --version

perror Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)

$ perror 13114

MySQL error code MY-013114 (ER_SERVER_SOURCE_FATAL_ERROR_READING_BINLOG): Got fatal error %d from source when reading data from binary log: '%-.512s'

$ perror 1236

MySQL error code MY-001236 (ER_SOURCE_FATAL_ERROR_READING_BINLOG): Got fatal error %d from source when reading data from binary log: '%-.512s'

$ perror 1789

MySQL error code MY-001789 (ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS): Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. %s

あれ？Error number: 1789じゃなくてError number: 1236なのです？と思われるでしょうが、よくよく読んでみると

'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is 'e24b415e-f1d7-11f0-88eb-066225a28251:1-4', and the missing transactions are 'e2855d1a-f1d7-11f0-8b58-e6f3098eb441:1''

この部分がER_SOURCE_HAS_PURGED_REQUIRED_GTIDSの

Message: Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. %s

なんですね。ER_SOURCE_HAS_PURGED_REQUIRED_GTIDSはER_SOURCE_FATAL_ERROR_READING_BINLOGでラップされて返ってきているというわけですね。

具体的には

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.cc#L926-L928

      mysql_bin_log.report_missing_purged_gtids(m_exclude_gtid, errmsg);
      global_sid_lock->unlock();
      set_fatal_error(errmsg.c_str());

926

927

928

mysql_bin_log.report_missing_purged_gtids(m_exclude_gtid, errmsg);

global_sid_lock->unlock();

set_fatal_error(errmsg.c_str());

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.h#L434-L436

  inline void set_fatal_error(const char *errmsg) {
    set_error(ER_SOURCE_FATAL_ERROR_READING_BINLOG, errmsg);
  }

434

435

436

inline void set_fatal_error(const char *errmsg) {

set_error(ER_SOURCE_FATAL_ERROR_READING_BINLOG, errmsg);

}

https://github.com/mysql/mysql-server/blob/mysql-8.0.44/sql/rpl_binlog_sender.h#L423-L428

set_fatal_error()で Error number: 1236 ER_SOURCE_FATAL_ERROR_READING_BINLOG を設定しているかと。

しかしsuper_read_only=onでも生成されるGTIDがある

ここからが本題です。

例えば、gtid_mode=onかつsuper_read_only=on に設定したMySQL8.0.44のreplicaで次のSQLやコマンドを実行した場合、GTIDが発行されるものはどれでしょうか？

FLUSH ERROR LOGS;
mysqladmin flush-logs slow
mysqldump --single-transaction

答えは 1〜3 ぜんぶです。

「おいおいマジかよ？！」と思われましたか？初めて知ったとき、私はそう思いました。

read only な replicaでmysqldumpを叩くのは至極当然でしょうし、error logやslow query logをreplicaごとに個別にローテートしてもいいと思うじゃないですか。でも、これらをそのまま実行するとbinlogにFLUSHステートメントが出力され、それに伴うGTIDが生成されます。

FLUSH ステートメントには [NO_WRITE_TO_BINLOG | LOCAL] といったオプションがあるのですが、

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.8.3 FLUSH ステートメント

http://dev.mysql.com

注記
レプリカにレプリケートすると問題が発生するため、FLUSH LOGS, FLUSH BINARY LOGS, FLUSH TABLES WITH READ LOCK (テーブルリストの有無にかかわらず)、および FLUSH TABLES tbl_name ... FOR EXPORT はバイナリログに書き込まれません。

といったごく一部の例外を除き、 [NO_WRITE_TO_BINLOG | LOCAL] を指定しないとFLUSHステートメントはbinlogに吐かれてしまい、GTIDが生成されてしまうのです。

例えば MySQL 8.0.44 をローカルで docker で立ち上げて set global super_read_only=on; した後、 mysqldump --single-transaction すると FLUSH TABLES が発行され、GTIDが生成されます。

$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql80 -d container-registry.oracle.com/mysql/community-server:8.0 --gtid-mode=on --enforce-gtid-consistency=on
2b75e3ce43fd05a85523d5b3639cae5e42e927aec2d7f05a0bbf5666a926b659
$ mysql -u root -h 127.0.0.1 -P 13306 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.44 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

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.

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       180 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.44, Binlog ver: 4 |
| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table test (id int not null auto_increment primary key, val int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (NULL, 0);
Query OK, 1 row affected (0.01 sec)

mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                      |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.44, Binlog ver: 4                                                                         |
| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                                                                                           |
| binlog.000002 | 157 | Gtid           |         1 |         234 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:1'                                         |
| binlog.000002 | 234 | Query          |         1 |         342 | create database test /* xid=5 */                                                                          |
| binlog.000002 | 342 | Gtid           |         1 |         419 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:2'                                         |
| binlog.000002 | 419 | Query          |         1 |         587 | use `test`; create table test (id int not null auto_increment primary key, val int not null) /* xid=10 */ |
| binlog.000002 | 587 | Gtid           |         1 |         666 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:3'                                         |
| binlog.000002 | 666 | Query          |         1 |         741 | BEGIN                                                                                                     |
| binlog.000002 | 741 | Table_map      |         1 |         792 | table_id: 90 (test.test)                                                                                  |
| binlog.000002 | 792 | Write_rows     |         1 |         836 | table_id: 90 flags: STMT_END_F                                                                            |
| binlog.000002 | 836 | Xid            |         1 |         867 | COMMIT /* xid=11 */                                                                                       |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)

mysql> set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@super_read_only;
+-------------------+
| @@super_read_only |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@GLOBAL.gtid_executed;
+------------------------------------------+
| @@GLOBAL.gtid_executed                   |
+------------------------------------------+
| fc700b9f-f11c-11f0-91f5-a64484b688e0:1-3 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
$ mysqldump --version
mysqldump  Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)
$ mysqldump -u root -h 127.0.0.1 -P 13306 -p --single-transaction test test > test.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
$ mysql -u root -h 127.0.0.1 -P 13306 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.44 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

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.

mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                      |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.44, Binlog ver: 4                                                                         |
| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                                                                                           |
| binlog.000002 | 157 | Gtid           |         1 |         234 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:1'                                         |
| binlog.000002 | 234 | Query          |         1 |         342 | create database test /* xid=5 */                                                                          |
| binlog.000002 | 342 | Gtid           |         1 |         419 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:2'                                         |
| binlog.000002 | 419 | Query          |         1 |         587 | use `test`; create table test (id int not null auto_increment primary key, val int not null) /* xid=10 */ |
| binlog.000002 | 587 | Gtid           |         1 |         666 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:3'                                         |
| binlog.000002 | 666 | Query          |         1 |         741 | BEGIN                                                                                                     |
| binlog.000002 | 741 | Table_map      |         1 |         792 | table_id: 90 (test.test)                                                                                  |
| binlog.000002 | 792 | Write_rows     |         1 |         836 | table_id: 90 flags: STMT_END_F                                                                            |
| binlog.000002 | 836 | Xid            |         1 |         867 | COMMIT /* xid=11 */                                                                                       |
| binlog.000002 | 867 | Gtid           |         1 |         944 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:4'                                         |
| binlog.000002 | 944 | Query          |         1 |        1022 | FLUSH TABLES                                                                                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)

mysql> select @@super_read_only;
+-------------------+
| @@super_read_only |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@GLOBAL.gtid_executed;
+------------------------------------------+
| @@GLOBAL.gtid_executed                   |
+------------------------------------------+
| fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
$

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql80 -d container-registry.oracle.com/mysql/community-server:8.0 --gtid-mode=on --enforce-gtid-consistency=on

2b75e3ce43fd05a85523d5b3639cae5e42e927aec2d7f05a0bbf5666a926b659

$ mysql -u root -h 127.0.0.1 -P 13306 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.44 MySQL Community Server - GPL

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.

mysql> show binary logs;

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

| Log_name | File_size | Encrypted |

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

| binlog.000001 | 180 | No |

| binlog.000002 | 157 | No |

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

2 rows in set (0.00 sec)

mysql> show binlog events in 'binlog.000002';

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

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

| binlog.000002 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.44, Binlog ver: 4 |

| binlog.000002 | 126 | Previous_gtids | 1 | 157 | |

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

2 rows in set (0.00 sec)

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

mysql> use test

Database changed

mysql> create table test (id int not null auto_increment primary key, val int not null);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (NULL, 0);

Query OK, 1 row affected (0.01 sec)

mysql> show binlog events in 'binlog.000002';

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

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

| binlog.000002 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.44, Binlog ver: 4 |

| binlog.000002 | 126 | Previous_gtids | 1 | 157 | |

| binlog.000002 | 157 | Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:1' |

| binlog.000002 | 234 | Query | 1 | 342 | create database test /* xid=5 */ |

| binlog.000002 | 342 | Gtid | 1 | 419 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:2' |

| binlog.000002 | 419 | Query | 1 | 587 | use `test`; create table test (id int not null auto_increment primary key, val int not null) /* xid=10 */ |

| binlog.000002 | 587 | Gtid | 1 | 666 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:3' |

| binlog.000002 | 666 | Query | 1 | 741 | BEGIN |

| binlog.000002 | 741 | Table_map | 1 | 792 | table_id: 90 (test.test) |

| binlog.000002 | 792 | Write_rows | 1 | 836 | table_id: 90 flags: STMT_END_F |

| binlog.000002 | 836 | Xid | 1 | 867 | COMMIT /* xid=11 */ |

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

11 rows in set (0.00 sec)

mysql> set global super_read_only=on;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@super_read_only;

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

| @@super_read_only |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select @@GLOBAL.gtid_executed;

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

| @@GLOBAL.gtid_executed |

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

| fc700b9f-f11c-11f0-91f5-a64484b688e0:1-3 |

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

1 row in set (0.01 sec)

mysql> exit

Bye

$ mysqldump --version

mysqldump Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)

$ mysqldump -u root -h 127.0.0.1 -P 13306 -p --single-transaction test test > test.sql

Enter password:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

$ mysql -u root -h 127.0.0.1 -P 13306 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 8.0.44 MySQL Community Server - GPL

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.

mysql> show binlog events in 'binlog.000002';

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

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

| binlog.000002 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.44, Binlog ver: 4 |

| binlog.000002 | 126 | Previous_gtids | 1 | 157 | |

| binlog.000002 | 157 | Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:1' |

| binlog.000002 | 234 | Query | 1 | 342 | create database test /* xid=5 */ |

| binlog.000002 | 342 | Gtid | 1 | 419 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:2' |

| binlog.000002 | 419 | Query | 1 | 587 | use `test`; create table test (id int not null auto_increment primary key, val int not null) /* xid=10 */ |

| binlog.000002 | 587 | Gtid | 1 | 666 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:3' |

| binlog.000002 | 666 | Query | 1 | 741 | BEGIN |

| binlog.000002 | 741 | Table_map | 1 | 792 | table_id: 90 (test.test) |

| binlog.000002 | 792 | Write_rows | 1 | 836 | table_id: 90 flags: STMT_END_F |

| binlog.000002 | 836 | Xid | 1 | 867 | COMMIT /* xid=11 */ |

| binlog.000002 | 867 | Gtid | 1 | 944 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:4' |

| binlog.000002 | 944 | Query | 1 | 1022 | FLUSH TABLES |

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

13 rows in set (0.00 sec)

mysql> select @@super_read_only;

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

| @@super_read_only |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select @@GLOBAL.gtid_executed;

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

| @@GLOBAL.gtid_executed |

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

| fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 |

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

1 row in set (0.00 sec)

mysql> exit

Bye

幸いにして、mysqldumpでGTIDが生成されてしまうのは Bug #35665076 として認められたようで、MySQL 8.4.1 で fix されました。8.4.1以降のMySQLに付属するmysqldumpを使えば、この問題は回避できます。

一方、FLUSH ERROR LOGS や mysqladmin flush-logs slow でGTIDが生成される問題については、

MySQL Bugs: #88720: Inconsistent and unsafe FLUSH behavior in terms of replication

で2017年にPerconaの方がバグレポートして以来そのままらしく、未だに修正されていません。

これはもうみんなで Affects Me して、MySQLの中の人に「困ってます！」と伝えるしかないじゃないですか。

知らず知らずのうちにreplicaで発行されたGTIDを見つけるためにはどうしたら？

このような話をすると、

「super_read_only=onにして安心していたけど、ウチのMySQLにも、知らないうちにreplicaで生成されたGTIDがあるかも…」

と思う方もいらっしゃるかもしれません。私は初めて知ったときそう思って、ざっくり調査するためのSQLを考えました。

至ってシンプルです。replicaで次のSQLを実行してみてください。

SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%');

公式ドキュメントの 17.1.3.1 GTID 形式および格納にあるように、デフォルトでは

GTID は座標のペアとして表現され、次に示すように、コロン文字 (:) で区切られます。

GTID = source_id:transaction_id

source_id は発生元サーバーを識別します。通常、この目的にはソース server_uuid が使用されます。

server_uuidが含まれます。具体的には次のように

mysql> SELECT @@server_uuid, @@GLOBAL.gtid_purged, @@GLOBAL.gtid_executed;
+--------------------------------------+------------------------------------------+------------------------------------------+
| @@server_uuid                        | @@GLOBAL.gtid_purged                     | @@GLOBAL.gtid_executed                   |
+--------------------------------------+------------------------------------------+------------------------------------------+
| fc700b9f-f11c-11f0-91f5-a64484b688e0 | fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 | fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 |
+--------------------------------------+------------------------------------------+------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONCAT('%', @@server_uuid, '%');
+----------------------------------------+
| CONCAT('%', @@server_uuid, '%')        |
+----------------------------------------+
| %fc700b9f-f11c-11f0-91f5-a64484b688e0% |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%');
+-------------------------------------------------------------+
| @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%') |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

mysql> SELECT @@server_uuid, @@GLOBAL.gtid_purged, @@GLOBAL.gtid_executed;

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

| @@server_uuid | @@GLOBAL.gtid_purged | @@GLOBAL.gtid_executed |

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

| fc700b9f-f11c-11f0-91f5-a64484b688e0 | fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 | fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 |

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

1 row in set (0.01 sec)

mysql> SELECT CONCAT('%', @@server_uuid, '%');

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

| CONCAT('%', @@server_uuid, '%') |

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

| %fc700b9f-f11c-11f0-91f5-a64484b688e0% |

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

1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%');

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

| @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%') |

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

| 1 |

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

1 row in set (0.01 sec)

mysql>

例えば、MySQLは UUID()関数でUUIDを生成できますが、UUIDを生成しつつLIKEの比較演算子で評価を行っても、@@GLOBAL.gtid_executed に該当するUUIDは存在しないので 0 が返ってきます。

mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| bb7d718b-f128-11f0-98d6-a64484b688e0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('%', UUID(), '%');
+----------------------------------------+
| CONCAT('%', UUID(), '%')               |
+----------------------------------------+
| %bf872620-f128-11f0-98d6-a64484b688e0% |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', UUID(), '%');
+------------------------------------------------------+
| @@GLOBAL.gtid_executed LIKE CONCAT('%', UUID(), '%') |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

mysql> select UUID();

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

| UUID() |

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

| bb7d718b-f128-11f0-98d6-a64484b688e0 |

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

1 row in set (0.00 sec)

mysql> SELECT CONCAT('%', UUID(), '%');

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

| CONCAT('%', UUID(), '%') |

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

| %bf872620-f128-11f0-98d6-a64484b688e0% |

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

1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', UUID(), '%');

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

| @@GLOBAL.gtid_executed LIKE CONCAT('%', UUID(), '%') |

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

| 0 |

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

1 row in set (0.01 sec)

mysql>

replicaのgtid_executedの中に、source_idにreplicaのserver_uuidが含まれているものがあれば、そのGTIDはreplicaで作成されたものだろう、という話です。

おわりに

長らくMySQLのデフォルトであったGTIDを使わないreplicationであれば、このようなFLUSHステートメントなどに悩まされることもなかったかと思いますが、MySQL 9.5.0は流石Innovation Release、今までの前提が覆ってきました。MySQL 9.7.0 LTS（予定）でも引き続きgtid_mode=offやgtid_mode=off_permissiveが指定できるなら、引き続きGTIDを使わないでやっていけますが、こういったGTIDに関連する問題など今のうちから調べつつ、MySQLのデフォルトに準拠していけるよう、やっていきたいものです。