1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6SET @debug_save= @@GLOBAL.DEBUG; 7call mtr.add_suppression("test the suppression statement"); 8SHOW CREATE TABLE mysql.gtid_executed; 9Table Create Table 10gtid_executed CREATE TABLE `gtid_executed` ( 11 `source_uuid` char(36) NOT NULL COMMENT 'uuid of the source where the transaction was originally executed.', 12 `interval_start` bigint NOT NULL COMMENT 'First number of interval.', 13 `interval_end` bigint NOT NULL COMMENT 'Last number of interval.', 14 PRIMARY KEY (`source_uuid`,`interval_start`) 15) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC 16# 17# Verify that the specified gtid to GTID_NEXT can be reported from 18# global.gtid_executed and is stored into gtid_executed table on 19# following binlog rotation. 20# 21SET GTID_NEXT='MASTER_UUID:3'; 22BEGIN; 23COMMIT; 24include/assert.inc [committed gtids MASTER_UUID:1:3] 25SET GTID_NEXT='AUTOMATIC'; 26FLUSH LOGS; 27# 28# Store gtids MASTER_UUID:1:3 in gtid_executed table on binlog rotation 29# 30SELECT * FROM mysql.gtid_executed; 31source_uuid interval_start interval_end 32MASTER_UUID 1 1 33MASTER_UUID 3 3 34# 35# Verify that these gtids can be reported from global.gtid_executed and 36# are stored into gtid_executed table on next rotation for normal DDLs. 37# 38CREATE TABLE IF NOT EXISTS t1 (a INT) ENGINE=InnoDB; 39CREATE TABLE t2 (a INT) ENGINE=MyISAM; 40include/assert.inc [committed gtids MASTER_UUID:1-4] 41# 42# Store gtids MASTER_UUID:1-4 in gtid_executed table on binlog rotation 43# 44SET @@GLOBAL.DEBUG= '+d,compress_gtid_table'; 45FLUSH LOGS; 46SET DEBUG_SYNC='now WAIT_FOR complete_compression'; 47SELECT * FROM mysql.gtid_executed; 48source_uuid interval_start interval_end 49MASTER_UUID 1 4 50# 51# Verify that these gtids can be reported from global.gtid_executed 52# and are stored into gtid_executed table for compound statement with 53# regular and temporary tables. 54# 55CREATE TEMPORARY TABLE tmp1 (c1 INT) Engine=MyISAM; 56CREATE TABLE t3 (a INT); 57DROP TEMPORARY TABLE tmp1, t3; 58ERROR 42S02: Unknown table 'test.t3' 59DROP TEMPORARY TABLE tmp1; 60DROP TABLE t3; 61include/assert.inc [committed gtids MASTER_UUID:1-8] 62# 63# Store gtids MASTER_UUID:1-8 in gtid_executed table on binlog rotation 64# 65SET @@GLOBAL.DEBUG= '+d,compress_gtid_table'; 66FLUSH LOGS; 67SET DEBUG_SYNC='now WAIT_FOR complete_compression'; 68SELECT * FROM mysql.gtid_executed; 69source_uuid interval_start interval_end 70MASTER_UUID 1 8 71# 72# Verify that transactions' gtids can be reported from 73# global.gtid_executed correctly and are stored into 74# gtid_executed table on next binlog rotation. 75# 76BEGIN; 77INSERT INTO t2 VALUES(2); 78INSERT INTO t1 VALUES(1); 79INSERT INTO t1 VALUES(2); 80COMMIT; 81# 82# Verify that specified gtid for transaction can be reported from 83# global.gtid_executed correctly and is stored into gtid_executed 84# table on next binlog rotation. 85# 86SET @@SESSION.GTID_NEXT= 'MASTER_UUID:11'; 87BEGIN; 88INSERT INTO t1 VALUES(3); 89COMMIT; 90include/assert.inc [committed gtids MASTER_UUID:1-11] 91SET GTID_NEXT='AUTOMATIC'; 92SET @@GLOBAL.DEBUG= '+d,compress_gtid_table'; 93FLUSH LOGS; 94SET DEBUG_SYNC='now WAIT_FOR complete_compression'; 95# 96# Store gtids MASTER_UUID:1-11 in gtid_executed table on binlog rotation 97# 98SELECT * FROM mysql.gtid_executed; 99source_uuid interval_start interval_end 100MASTER_UUID 1 11 101# 102# Verify that transaction's gtid can not be reported from 103# global.gtid_executed and is not stored into gtid_executed table 104# on next binlog rotation if the transaction is rollbacked. 105# 106BEGIN; 107INSERT INTO t1 VALUES(4); 108ROLLBACK; 109include/assert.inc [committed gtids MASTER_UUID:1-11] 110# 111# Store gtids MASTER_UUID:1-11 in gtid_executed table on binlog rotation 112# 113FLUSH LOGS; 114SELECT * FROM mysql.gtid_executed; 115source_uuid interval_start interval_end 116MASTER_UUID 1 11 117SET GLOBAL DEBUG= @debug_save; 118include/sync_slave_sql_with_master.inc 119# 120# connection slave 121# 122# 123# Verify that the transaction is skiped if its specified gtid is 124# already in global.gtid_executed, although not in gtid_executed table. 125# 126SET @@SESSION.GTID_NEXT= 'MASTER_UUID:6'; 127INSERT INTO t1 VALUES(11); 128include/assert.inc [Table t1 must not contain 11] 129include/assert.inc [committed gtids MASTER_UUID:1-11] 130# 131# The gtid_executed table would only have GTID for Innodb transactions 132# 133# 134# Store gtids MASTER_UUID:1-11 in gtid_executed table on binlog rotation 135# 136SET GTID_NEXT='AUTOMATIC'; 137FLUSH LOGS; 138SELECT * FROM mysql.gtid_executed; 139source_uuid interval_start interval_end 140MASTER_UUID 1 11 141# 142# Verify that the specified gtid to GTID_NEXT is stored into 143# global.gtid_executed immediately and stored into gtid_executed table 144# on next binlog rotation. 145# 146SET @@SESSION.GTID_NEXT= 'MASTER_UUID:17'; 147COMMIT; 148include/assert.inc [committed gtids MASTER_UUID:1-11:17] 149# 150# Store gtids MASTER_UUID:1-11:17 in gtid_executed table 151# on next binlog rotation. 152# 153SET GTID_NEXT='AUTOMATIC'; 154FLUSH LOGS; 155SELECT * FROM mysql.gtid_executed; 156source_uuid interval_start interval_end 157MASTER_UUID 1 11 158MASTER_UUID 17 17 159# 160# Verify that we can get the correct set of gtid_purged 161# when purging logs. 162# 163PURGE BINARY LOGS TO 'slave-bin.000003'; 164include/assert.inc [purged gtids MASTER_UUID:1-11:17] 165# 166# Verify that transaction's gtid generated on slave is stored 167# into gtid_executed table on next binlog rotation. 168# 169BEGIN; 170INSERT INTO t1 VALUES(12); 171COMMIT; 172# 173# Store gtids MASTER_UUID:1-11:17 and SLAVE_UUID:1 174# in gtid_executed table on binlog rotation. 175# 176FLUSH LOGS; 177SELECT * FROM mysql.gtid_executed where source_uuid="MASTER_UUID"; 178source_uuid interval_start interval_end 179MASTER_UUID 1 11 180MASTER_UUID 17 17 181SELECT * FROM mysql.gtid_executed where source_uuid="SLAVE_UUID"; 182source_uuid interval_start interval_end 183SLAVE_UUID 1 1 184DROP TABLE t1, t2; 185include/rpl_end.inc 186