Gaps in GTID Sequence Numbers¶
Length: 421 words; Published: October 22, 2024; Updated: October 22, 2024; Category: State Transfers; Type: Troubleshooting
GTID gaps may occur if gtid_next is set to a specific value, and a transaction fails.
Scenario
When a failing DDL statement with explicit GTID value set with gtid_next is replicated by TOI, the failed DDL is not logged in the binary log and, therefore, the binlog will contain GTID event gaps.
Below is an example of a failing DDL statement:
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:3';
create table t_fail (i int primary key, j int, foreign key(j) references non_existing(i));
And an example of binlog events after the statement has been issued:
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:1'
use `test`; create table t1 (i int primary key, j int) /* xid=3 */
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:2'
use `test`; create table t2 (i int primary key, j int, foreign key(j) references t1(i)) /* xid=4 */
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:4'
use `test`; create table t3 (i int primary key, j int, foreign key(j) references t1(i)) /* xid=6 */
Here, GTID seqno 3 is allocated for the create table statement, but the related GTID event is missing from the binlog file.
Galera Cluster replication is based on the assumption that GTID numbers do not contain gaps in the binlog. In such a case, the behavior is undefined, thus it should be avoided.
Solution
The solution to mitigate GTID gaps is to re-assign the same gtid_next value to the next successful transaction after the failed one:
# Record some GTID
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:1';
CREATE TABLE t1 (i INT PRIMARY KEY, j INT) ENGINE=InnoDB;
# Now miss a GTID value by failing a transaction
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:2';
CREATE TABLE t_fail (i INT PRIMARY KEY, j INT, FOREIGN KEY(j) REFERENCES NON_EXISTING(i)) ENGINE=InnoDB;
# Write successful transaction with the same GTID
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:2';
DROP TABLE t1;
# Rotate binlog file to generate Previous_gtids_log_event
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:3';
FLUSH LOGS;
This results in a binlog with no GTID gaps:
#241021 11:49:48 server id 1 end_log_pos 197 CRC32 0x5912150c Previous-GTIDs
# a9264492-7be5-11ef-b353-ca382343a2f9:1-2
# at 197
...
SET @@SESSION.GTID_NEXT= 'a9264492-7be5-11ef-b353-ca382343a2f9:3'/*!*/;
# at 274
...
Related Documents
