| Bug #120418 | unexpected Executed_Gtid_Set after util.loadDump with updateGtidSet: replace | ||
|---|---|---|---|
| Submitted: | 8 May 7:09 | Modified: | 8 May 7:16 |
| Reporter: | Tsubasa Tanaka (OCA) | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | Shell Dump & Load | Severity: | S3 (Non-critical) |
| Version: | 9.7.0 | OS: | Oracle Linux (8.10) |
| Assigned to: | CPU Architecture: | x86 | |
[8 May 7:16]
Tsubasa Tanaka
After "How to repeat", setting replication from 2nd server to 1st server, they have same data but replication will be broken.
```
$ mysql -h172.17.0.2
mysql> SET GLOBAL server_id = 999; -- To avoid `same server_id`
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE REPLICATION SOURCE TO source_host = '172.17.0.3', source_user = 'root', source_auto_position = 1;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> START REPLICA;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 172.17.0.3
Source_User: root
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000002
Read_Source_Log_Pos: 1044
Relay_Log_File: a625a2dd05a4-relay-bin.000002
Relay_Log_Pos: 369
Relay_Source_Log_File: binlog.000002
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '15791f27-4aaa-11f1-ab7c-0242ac110003:3' at source log binlog.000002, end_log_pos 1013. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Source_Log_Pos: 158
Relay_Log_Space: 1473
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: Yes
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '15791f27-4aaa-11f1-ab7c-0242ac110003:3' at source log binlog.000002, end_log_pos 1013. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 15791f27-4aaa-11f1-ab7c-0242ac110003
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 10
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 260508 07:13:02
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 15791f27-4aaa-11f1-ab7c-0242ac110003:1-3
Executed_Gtid_Set: 15791f27-4aaa-11f1-ab7c-0242ac110003:1-2,
4ac0f25f-4aa9-11f1-9fca-0242ac110002:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker WHERE last_error_message <> ''\G
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '15791f27-4aaa-11f1-ab7c-0242ac110003:3' at source log binlog.000002, end_log_pos 1013; 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 binlog.000002, end_log_pos 1013
LAST_ERROR_TIMESTAMP: 2026-05-08 07:13:02.951131
LAST_APPLIED_TRANSACTION: 15791f27-4aaa-11f1-ab7c-0242ac110003:2
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2026-05-08 06:52:50.172458
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2026-05-08 06:52:50.172458
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2026-05-08 07:13:02.949293
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2026-05-08 07:13:02.951033
APPLYING_TRANSACTION: 15791f27-4aaa-11f1-ab7c-0242ac110003:3
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2026-05-08 06:52:50.190070
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2026-05-08 06:52:50.190070
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2026-05-08 07:13:02.951060
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
```
Description: After restoring backup by util.loadDump with updateGtidSet=replace, there are both gtid_executed set by "backuped server" and generated by "restored server". I think this is expected behavior for *updateGtidSet=append* , updateGtidSet=replace should *replace* gtid_executed by "backuped server" GTIDs instead of mixed GTIDs. This leads replication problem after failover, because "restored server"'s GTIDed-transactions will send to failovered new replica -- "backuped server". How to repeat: 1. Create 1st server to generate backup ``` $ docker run -d --restart=on-failure -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" container-registry.oracle.com/mysql/community-server:9.7 $ mysql -h172.17.0.2 -uroot mysql> CREATE DATABASE d1; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE d1.t1 (num INT PRIMARY KEY); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO d1.t1 VALUES (1); Query OK, 1 row affected (0.01 sec) mysql> SHOW BINARY LOG STATUS; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000002 | 803 | | | 4ac0f25f-4aa9-11f1-9fca-0242ac110002:1-3 | +---------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) ``` 2. Generate backup by util.dumpInstance ``` $ mysqlsh --version mysqlsh Ver 9.7.0 for Linux on x86_64 - for MySQL 9.7.0 (MySQL Community Server (GPL)) $ mysqlsh mysql://root:''@172.17.0.2 --js -- util dumpInstance /tmp/test ``` 3. Create 2nd server to restore backup with updateGtidSet: replace ``` $ docker run -d --restart=on-failure -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" container-registry.oracle.com/mysql/community-server:9.7 $ mysql -h172.17.0.3 -uroot mysql> SHOW BINARY LOG STATUS; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000002 | 158 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 15791f27-4aaa-11f1-ab7c-0242ac110003 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> SET GLOBAL local_infile = 1; Query OK, 0 rows affected (0.00 sec) ``` 3. Restore backup from 1st server into 2nd server with updateGtidSet=replace ``` $ mysqlsh mysql://root:''@172.17.0.3 --js -- util loadDump '/tmp/test' { --updateGtidSet=replace } ``` 4. Check gtid_executed in 2nd server, then you'll see some GTIDs includes 2nd server's server_uuid ``` $ mysql -h172.17.0.3 -uroot mysql> SHOW BINARY LOG STATUS; +---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ | binlog.000002 | 1044 | | | 15791f27-4aaa-11f1-ab7c-0242ac110003:1-3, 4ac0f25f-4aa9-11f1-9fca-0242ac110002:1-3 | +---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 15791f27-4aaa-11f1-ab7c-0242ac110003 | +--------------------------------------+ 1 row in set (0.00 sec) ``` These GTIDed transactions are generated by CRATE DATABASE, CREATE TABLE, LOAD DATA LOCAL INFILE. $ mysqlbinlog -vv -R -h172.17.0.3 -uroot binlog.000002 Suggested fix: Adding "RESET BINARY LOGS AND GTIDS" before `SET GLOBAL gtid_purged` https://github.com/mysql/mysql-shell/blob/9.7/modules/util/load/dump_loader.cc#L2411-L2423