1# ==== Purpose ==== 2# 3# The purpose of this test is to verify that TRUNCATE statement is binary 4# logged with correct GTID when a replicated memory table is fisrt updated 5# after a server crash or a server restart. 6# 7# ==== Requirements ==== 8# 9# R1. When there is a replicated memory table, and the slave crashes, the next 10# transaction that updates the memory table shall generate a TRUNCATE statement 11# in the binary log. This test verifies that this works in the below cases: 12 13# R1.1. When the update is applied on a replication channel. 14# R1.2. When the update is applied in a client session having 15# gtid_next=UUID:NUMBER. 16# 17# R2. The TRUNCATE statement shall have a new GTID generated by the slave. 18# 19# R3. There shall not be a crash. 20# 21# ==== Implementation ==== 22# 23# 1. Try to update the memory table on master after restarting the master: 24# 25# 1.1. A DML on the memory table 26# 1.2. A DDL on the memory table. 27# 1.3. A statement that reads from the memory table. 28# 1.4. A transaction containing update on the memory table along with other 29# table updates. 30# 1.5. A statement containing update on the memory table along with other 31# table updates. 32# 1.6. A DML on the memory table applied in a client session having 33# gtid_next=UUID:NUMBER. 34# 35# In each of the above cases: 36# - Verify that TRUNCATE statement is binary logged on the master. 37# 38# 2. Try to update the memory table on master after restarting the slave: 39# 40# 2.1. A DML on the memory table 41# 2.2. A DDL on the memory table. 42# 2.3. A statement that reads from the memory table 43# 2.4. A transaction containing update on the memory table along with other 44# table updates. 45# 2.5. A statement containing update on the memory table along with other 46# table updates. 47# 2.6. A DML on the memory table applied in a client session having 48# gtid_next=UUID:NUMBER. 49# 50# In each of the above cases: 51# - Verify that TRUNCATE statement is binary logged on the slave. 52# 53# ==== References ==== 54# 55# Bug#30527929 - RESTART CREATES ANONYMOUS TRANSACTION FOR MEMORY TABLE 56# 57 58--source include/have_gtid.inc 59--source include/master-slave.inc 60 61#Scenario 1 62--source include/rpl_connection_master.inc 63CREATE TABLE mem_t1 (c1 INT) ENGINE= MEMORY; 64CREATE TABLE mem_t2 (c1 INT) ENGINE= MEMORY; 65CREATE TABLE mem_t3 (c1 INT) ENGINE= MEMORY; 66CREATE TABLE mem_t4 (c1 INT) ENGINE= MEMORY; 67CREATE TABLE mem_t5 (c1 INT) ENGINE= MEMORY; 68CREATE TABLE mem_t6 (c1 INT) ENGINE= MEMORY; 69CREATE TABLE mem_t7 (c1 INT) ENGINE= MEMORY; 70CREATE TABLE mem_t8 (c1 INT) ENGINE= MEMORY; 71INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 72INSERT INTO mem_t2 VALUES (1), (2), (3), (4); 73INSERT INTO mem_t3 VALUES (1), (2), (3), (4); 74INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 75INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 76INSERT INTO mem_t6 VALUES (1), (2), (3), (4); 77INSERT INTO mem_t7 VALUES (1), (2), (3), (4); 78INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 79--source include/sync_slave_sql_with_master.inc 80 81# Restart master 82--source include/stop_slave.inc 83--let $rpl_server_number=1 84--source include/rpl_restart_server.inc 85--source include/start_slave.inc 86--source include/rpl_connection_master.inc 87--source include/save_binlog_position.inc 88 89# Scenario 1.1 90INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 91--source include/sync_slave_sql_with_master.inc 92--source include/rpl_connection_master.inc 93--let $limit= 0,7 94--let $event_sequence= #Gtid # !Q(TRUNCATE.*) #Gtid # !Begin # !Insert # !Commit 95--source include/assert_binlog_events.inc 96--source include/save_binlog_position.inc 97 98# Scenario 1.2 99ALTER TABLE mem_t2 ADD COLUMN (c2 INT); 100--source include/sync_slave_sql_with_master.inc 101--source include/rpl_connection_master.inc 102--let $limit= 0,4 103--let $event_sequence= #Gtid # !Q(TRUNCATE.*) #Gtid # !Q(ALTER.*) 104--source include/assert_binlog_events.inc 105--source include/save_binlog_position.inc 106 107# Scenario 1.3 108CREATE TABLE mem_t9 LIKE mem_t3; 109--source include/sync_slave_sql_with_master.inc 110--source include/rpl_connection_master.inc 111--let $limit= 0,4 112--let $event_sequence= #Gtid # !Q(TRUNCATE.*) #Gtid # !Q(CREATE.*) 113--source include/assert_binlog_events.inc 114--source include/save_binlog_position.inc 115 116# Scenario 1.4: 117BEGIN; 118INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 119INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 120COMMIT; 121--source include/sync_slave_sql_with_master.inc 122--source include/rpl_connection_master.inc 123--let $limit= 0,14 124--let $event_sequence= #Gtid # !Q(TRUNCATE.*) #Gtid # !Begin # !Insert # !Commit #Gtid # !Q(TRUNCATE.*) #Gtid # !Begin # !Insert # !Commit 125--source include/assert_binlog_events.inc 126--source include/save_binlog_position.inc 127 128# Scenario 1.5: 129UPDATE mem_t6, mem_t7 SET mem_t6.c1 = mem_t6.c1 + 1, mem_t7.c1 = mem_t7.c1 + 1; 130--source include/sync_slave_sql_with_master.inc 131--source include/rpl_connection_master.inc 132--let $limit= 0,4 133--let $event_sequence= #Gtid # !Q(TRUNCATE.*) #Gtid # !Q(TRUNCATE.*) 134--source include/assert_binlog_events.inc 135--source include/save_binlog_position.inc 136 137#Scenario 1.6: 138--let $explicit_gtid=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 139--eval SET @@session.gtid_next = '$explicit_gtid' 140INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 141SET @@session.gtid_next = 'AUTOMATIC'; 142--source include/sync_slave_sql_with_master.inc 143--source include/rpl_connection_master.inc 144--let $limit= 0,7 145--let $event_sequence= #Gtid # !Q(TRUNCATE.*) #Gtid/(.*'$explicit_gtid')# !Begin # !Insert # !Commit 146--source include/assert_binlog_events.inc 147 148# Cleanup 149DROP TABLE mem_t1, mem_t2, mem_t3, mem_t4, mem_t5, mem_t6, mem_t7, mem_t8, mem_t9; 150--source include/rpl_reset.inc 151 152# Scenario 2: 153--source include/rpl_connection_master.inc 154CREATE TABLE mem_t1 (c1 INT) ENGINE= MEMORY; 155CREATE TABLE mem_t2 (c1 INT) ENGINE= MEMORY; 156CREATE TABLE mem_t3 (c1 INT) ENGINE= MEMORY; 157CREATE TABLE mem_t4 (c1 INT) ENGINE= MEMORY; 158CREATE TABLE mem_t5 (c1 INT) ENGINE= MEMORY; 159CREATE TABLE mem_t6 (c1 INT) ENGINE= MEMORY; 160CREATE TABLE mem_t7 (c1 INT) ENGINE= MEMORY; 161CREATE TABLE mem_t8 (c1 INT) ENGINE= MEMORY; 162INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 163INSERT INTO mem_t2 VALUES (1), (2), (3), (4); 164INSERT INTO mem_t3 VALUES (1), (2), (3), (4); 165INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 166INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 167INSERT INTO mem_t6 VALUES (1), (2), (3), (4); 168INSERT INTO mem_t7 VALUES (1), (2), (3), (4); 169INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 170--source include/sync_slave_sql_with_master.inc 171 172# Restart slave 173--let $rpl_server_number=2 174--source include/rpl_restart_server.inc 175--source include/start_slave.inc 176--source include/save_binlog_position.inc 177 178# Save the UUID of master and slave 179--source include/rpl_connection_master.inc 180--let $master_uuid = `SELECT @@global.server_uuid` 181--source include/rpl_connection_slave.inc 182--let $slave_uuid = `SELECT @@global.server_uuid` 183--let $dont_print_pattern = 1 184 185# Scenario 2.1 186--source include/rpl_connection_master.inc 187INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 188--source include/sync_slave_sql_with_master.inc 189--source include/rpl_connection_slave.inc 190--let $limit= 0,7 191--let $event_sequence= #Gtid/(.*'$slave_uuid:1') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:17') # !Begin # !Insert # !Commit 192--source include/assert_binlog_events.inc 193--source include/save_binlog_position.inc 194 195# Scenario 2.2 196--source include/rpl_connection_master.inc 197ALTER TABLE mem_t2 ADD COLUMN (c2 INT); 198--source include/sync_slave_sql_with_master.inc 199--source include/rpl_connection_slave.inc 200--let $limit= 0,4 201--let $event_sequence= #Gtid/(.*'$slave_uuid:2') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:18') # !Q(ALTER.*) 202--source include/assert_binlog_events.inc 203--source include/save_binlog_position.inc 204 205# Scenario 2.3 206--source include/rpl_connection_master.inc 207CREATE TABLE mem_t9 LIKE mem_t3; 208--source include/sync_slave_sql_with_master.inc 209--source include/rpl_connection_slave.inc 210--let $limit= 0,4 211--let $event_sequence= #Gtid/(.*'$slave_uuid:3') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:19') # !Q(CREATE.*) 212--source include/assert_binlog_events.inc 213--source include/save_binlog_position.inc 214 215# Scenario 2.4: 216--source include/rpl_connection_master.inc 217BEGIN; 218INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 219INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 220COMMIT; 221--source include/sync_slave_sql_with_master.inc 222--source include/rpl_connection_slave.inc 223--let $limit= 0,14 224--let $event_sequence= #Gtid/(.*'$slave_uuid:4') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:20') # !Begin # !Insert # !Commit #Gtid/(.*'$slave_uuid:5') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:21') # !Begin # !Insert # !Commit 225--source include/assert_binlog_events.inc 226--source include/save_binlog_position.inc 227 228# Scenario 2.5: 229--source include/rpl_connection_master.inc 230DELIMITER |; 231CREATE FUNCTION f () RETURNS INT BEGIN 232INSERT INTO mem_t6 VALUES (1); 233RETURN 2; 234END| 235DELIMITER ;| 236INSERT INTO mem_t7 VALUES (f()); 237--source include/sync_slave_sql_with_master.inc 238--source include/rpl_connection_slave.inc 239--let $limit= 2,12 240--let $event_sequence= #Gtid/(.*'$slave_uuid:6') # !Q(TRUNCATE.*) #Gtid/(.*'$slave_uuid:7') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:23') # !Begin # !Multi_DML # !Commit 241--source include/assert_binlog_events.inc 242--source include/save_binlog_position.inc 243 244#Scenario 2.6: 245--source include/rpl_connection_master.inc 246--eval SET @@session.gtid_next = '$explicit_gtid' 247INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 248SET @@session.gtid_next = 'AUTOMATIC'; 249--source include/sync_slave_sql_with_master.inc 250--source include/rpl_connection_slave.inc 251--let $limit= 0,7 252--let $event_sequence= #Gtid/(.*'$slave_uuid:8') # !Q(TRUNCATE.*) #Gtid/(.*'$explicit_gtid')# !Begin # !Insert # !Commit 253--source include/assert_binlog_events.inc 254 255# Cleanup 256--source include/rpl_connection_master.inc 257DROP FUNCTION f; 258DROP TABLE mem_t1, mem_t2, mem_t3, mem_t4, mem_t5, mem_t6, mem_t7, mem_t8, mem_t9; 259--let $dont_print_pattern = 0 260--source include/rpl_end.inc 261