1# 2# ==== Purpose ==== 3# 4# WL#6559 Optimize GTIDs for passive slave - store GTIDs in table 5# 6# Verify that we can store gtids into gtid_executed table for transactions 7# on binlog rotation and report GLOBAL.GTID_EXECUTED and GLOBAL.GTID_PURGED 8# correctly on master and slave when binlog is enabled. 9# 10 11--source include/not_group_replication_plugin.inc 12--source include/have_gtid.inc 13--source include/have_debug.inc 14--source include/have_debug_sync.inc 15--source include/master-slave.inc 16 17SET @debug_save= @@GLOBAL.DEBUG; 18call mtr.add_suppression("test the suppression statement"); 19--let $master_uuid= `SELECT @@GLOBAL.SERVER_UUID` 20SHOW CREATE TABLE mysql.gtid_executed; 21 22--echo # 23--echo # Verify that the specified gtid to GTID_NEXT can be reported from 24--echo # global.gtid_executed and is stored into gtid_executed table on 25--echo # following binlog rotation. 26--echo # 27--replace_result $master_uuid MASTER_UUID 28--eval SET GTID_NEXT='$master_uuid:3' 29BEGIN; 30COMMIT; 31--let $assert_text= committed gtids MASTER_UUID:1:3 32--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1:3" 33--source include/assert.inc 34--eval SET GTID_NEXT='AUTOMATIC' 35FLUSH LOGS; 36--echo # 37--echo # Store gtids MASTER_UUID:1:3 in gtid_executed table on binlog rotation 38--echo # 39--replace_result $master_uuid MASTER_UUID 40SELECT * FROM mysql.gtid_executed; 41 42--echo # 43--echo # Verify that these gtids can be reported from global.gtid_executed and 44--echo # are stored into gtid_executed table on next rotation for normal DDLs. 45--echo # 46CREATE TABLE IF NOT EXISTS t1 (a INT) ENGINE=InnoDB; 47CREATE TABLE t2 (a INT) ENGINE=MyISAM; 48--let $assert_text= committed gtids MASTER_UUID:1-4 49--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-4" 50--source include/assert.inc 51--echo # 52--echo # Store gtids MASTER_UUID:1-4 in gtid_executed table on binlog rotation 53--echo # 54SET @@GLOBAL.DEBUG= '+d,compress_gtid_table'; 55FLUSH LOGS; 56SET DEBUG_SYNC='now WAIT_FOR complete_compression'; 57--replace_result $master_uuid MASTER_UUID 58SELECT * FROM mysql.gtid_executed; 59 60--echo # 61--echo # Verify that these gtids can be reported from global.gtid_executed 62--echo # and are stored into gtid_executed table for compound statement with 63--echo # regular and temporary tables. 64--echo # 65CREATE TEMPORARY TABLE tmp1 (c1 INT) Engine=MyISAM; 66CREATE TABLE t3 (a INT); 67if (`SELECT @@BINLOG_FORMAT = 'ROW'`) 68{ 69 # We did not generate gtid for above 'CREATE TEMPORARY TABLE' statement 70 # in row format, but we did in statement format. So let the 'INSERT' 71 # statement generate the missed gtid in row format. 72 --disable_query_log 73 INSERT INTO t3 VALUES(1); 74 --enable_query_log 75} 76--error ER_BAD_TABLE_ERROR 77DROP TEMPORARY TABLE tmp1, t3; 78DROP TABLE t3; 79--let $assert_text= committed gtids MASTER_UUID:1-8 80--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-8" 81--source include/assert.inc 82--echo # 83--echo # Store gtids MASTER_UUID:1-8 in gtid_executed table on binlog rotation 84--echo # 85SET @@GLOBAL.DEBUG= '+d,compress_gtid_table'; 86FLUSH LOGS; 87SET DEBUG_SYNC='now WAIT_FOR complete_compression'; 88--replace_result $master_uuid MASTER_UUID 89SELECT * FROM mysql.gtid_executed; 90 91--echo # 92--echo # Verify that transactions' gtids can be reported from 93--echo # global.gtid_executed correctly and are stored into 94--echo # gtid_executed table on next binlog rotation. 95--echo # 96BEGIN; 97INSERT INTO t2 VALUES(2); 98INSERT INTO t1 VALUES(1); 99INSERT INTO t1 VALUES(2); 100COMMIT; 101--echo # 102--echo # Verify that specified gtid for transaction can be reported from 103--echo # global.gtid_executed correctly and is stored into gtid_executed 104--echo # table on next binlog rotation. 105--echo # 106--replace_result $master_uuid MASTER_UUID 107--eval SET @@SESSION.GTID_NEXT= '$master_uuid:11' 108BEGIN; 109INSERT INTO t1 VALUES(3); 110COMMIT; 111--let $assert_text= committed gtids MASTER_UUID:1-11 112--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-11" 113--source include/assert.inc 114--eval SET GTID_NEXT='AUTOMATIC' 115SET @@GLOBAL.DEBUG= '+d,compress_gtid_table'; 116FLUSH LOGS; 117SET DEBUG_SYNC='now WAIT_FOR complete_compression'; 118--echo # 119--echo # Store gtids MASTER_UUID:1-11 in gtid_executed table on binlog rotation 120--echo # 121--replace_result $master_uuid MASTER_UUID 122SELECT * FROM mysql.gtid_executed; 123 124--echo # 125--echo # Verify that transaction's gtid can not be reported from 126--echo # global.gtid_executed and is not stored into gtid_executed table 127--echo # on next binlog rotation if the transaction is rollbacked. 128--echo # 129BEGIN; 130INSERT INTO t1 VALUES(4); 131ROLLBACK; 132--let $assert_text= committed gtids MASTER_UUID:1-11 133--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-11" 134--source include/assert.inc 135--echo # 136--echo # Store gtids MASTER_UUID:1-11 in gtid_executed table on binlog rotation 137--echo # 138FLUSH LOGS; 139--replace_result $master_uuid MASTER_UUID 140SELECT * FROM mysql.gtid_executed; 141SET GLOBAL DEBUG= @debug_save; 142 143 144--source include/sync_slave_sql_with_master.inc 145--echo # 146--echo # connection slave 147--echo # 148--let $slave_uuid= `SELECT @@GLOBAL.SERVER_UUID` 149 150--echo # 151--echo # Verify that the transaction is skiped if its specified gtid is 152--echo # already in global.gtid_executed, although not in gtid_executed table. 153--echo # 154--replace_result $master_uuid MASTER_UUID 155--eval SET @@SESSION.GTID_NEXT= '$master_uuid:6' 156INSERT INTO t1 VALUES(11); 157--let $assert_text= Table t1 must not contain 11 158--let $assert_cond= "[SELECT a FROM t1 WHERE a=11]" = "" 159--source include/assert.inc 160--let $assert_text= committed gtids MASTER_UUID:1-11 161--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-11" 162--source include/assert.inc 163--echo # 164--echo # The gtid_executed table is empty on slave currently 165--echo # 166--replace_result $master_uuid MASTER_UUID 167SELECT * FROM mysql.gtid_executed; 168--echo # 169--echo # Store gtids MASTER_UUID:1-11 in gtid_executed table on binlog rotation 170--echo # 171--eval SET GTID_NEXT='AUTOMATIC' 172FLUSH LOGS; 173--replace_result $master_uuid MASTER_UUID 174SELECT * FROM mysql.gtid_executed; 175 176--echo # 177--echo # Verify that the specified gtid to GTID_NEXT is stored into 178--echo # global.gtid_executed immediately and stored into gtid_executed table 179--echo # on next binlog rotation. 180--echo # 181--replace_result $master_uuid MASTER_UUID 182--eval SET @@SESSION.GTID_NEXT= '$master_uuid:17' 183COMMIT; 184--let $assert_text= committed gtids MASTER_UUID:1-11:17 185--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-11:17" 186--source include/assert.inc 187--echo # 188--echo # Store gtids MASTER_UUID:1-11:17 in gtid_executed table 189--echo # on next binlog rotation. 190--echo # 191--eval SET GTID_NEXT='AUTOMATIC' 192FLUSH LOGS; 193--replace_result $master_uuid MASTER_UUID 194SELECT * FROM mysql.gtid_executed; 195 196--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) 197--echo # 198--echo # Verify that we can get the correct set of gtid_purged 199--echo # when purging logs. 200--echo # 201--eval PURGE BINARY LOGS TO '$binlog_file' 202--let $assert_text= purged gtids MASTER_UUID:1-11:17 203--let $assert_cond= "[SELECT @@GLOBAL.GTID_PURGED]" = "$master_uuid:1-11:17" 204--source include/assert.inc 205 206--echo # 207--echo # Verify that transaction's gtid generated on slave is stored 208--echo # into gtid_executed table on next binlog rotation. 209--echo # 210BEGIN; 211INSERT INTO t1 VALUES(12); 212COMMIT; 213--echo # 214--echo # Store gtids MASTER_UUID:1-11:17 and SLAVE_UUID:1 215--echo # in gtid_executed table on binlog rotation. 216--echo # 217FLUSH LOGS; 218--replace_result $master_uuid MASTER_UUID 219--eval SELECT * FROM mysql.gtid_executed where source_uuid="$master_uuid" 220--replace_result $slave_uuid SLAVE_UUID 221--eval SELECT * FROM mysql.gtid_executed where source_uuid="$slave_uuid" 222 223--connection master 224DROP TABLE t1, t2; 225 226--source include/rpl_end.inc 227