1# ==== Purpose ==== 2# 3# The purpose of this test is to verify that a DELETE 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 DELETE 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 DELETE 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 DELETE 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 DELETE 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/master-slave.inc 59 60#Scenario 1 61--source include/rpl_connection_master.inc 62CREATE TABLE mem_t1 (c1 INT) ENGINE= MEMORY; 63CREATE TABLE mem_t2 (c1 INT) ENGINE= MEMORY; 64CREATE TABLE mem_t3 (c1 INT) ENGINE= MEMORY; 65CREATE TABLE mem_t4 (c1 INT) ENGINE= MEMORY; 66CREATE TABLE mem_t5 (c1 INT) ENGINE= MEMORY; 67CREATE TABLE mem_t6 (c1 INT) ENGINE= MEMORY; 68CREATE TABLE mem_t7 (c1 INT) ENGINE= MEMORY; 69CREATE TABLE mem_t8 (c1 INT) ENGINE= MEMORY; 70INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 71INSERT INTO mem_t2 VALUES (1), (2), (3), (4); 72INSERT INTO mem_t3 VALUES (1), (2), (3), (4); 73INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 74INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 75INSERT INTO mem_t6 VALUES (1), (2), (3), (4); 76INSERT INTO mem_t7 VALUES (1), (2), (3), (4); 77INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 78--source include/sync_slave_sql_with_master.inc 79 80# Restart master 81--source include/stop_slave.inc 82--let $rpl_server_number=1 83--source include/rpl_restart_server.inc 84--source include/start_slave.inc 85--source include/rpl_connection_master.inc 86--source include/save_binlog_position.inc 87 88# Scenario 1.1 89INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 90--source include/sync_slave_sql_with_master.inc 91--source include/rpl_connection_master.inc 92--let $limit= 0,9 93--let $event_sequence= #Gtid # !Begin # !Delete # !Commit #Gtid # !Begin # !Insert # !Commit 94--source include/assert_binlog_events.inc 95--source include/save_binlog_position.inc 96 97# Scenario 1.2 98ALTER TABLE mem_t2 ADD COLUMN (c2 INT); 99--source include/sync_slave_sql_with_master.inc 100--source include/rpl_connection_master.inc 101--let $limit= 0,6 102--let $event_sequence= #Gtid # !Begin # !Delete # !Commit #Gtid # !Q(ALTER.*) 103--source include/assert_binlog_events.inc 104--source include/save_binlog_position.inc 105 106# Scenario 1.3 107CREATE TABLE mem_t9 LIKE mem_t3; 108--source include/sync_slave_sql_with_master.inc 109--source include/rpl_connection_master.inc 110--let $limit= 0,6 111--let $event_sequence= #Gtid # !Begin # !Delete # !Commit #Gtid # !Q(CREATE.*) 112--source include/assert_binlog_events.inc 113--source include/save_binlog_position.inc 114 115# Scenario 1.4: 116BEGIN; 117INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 118INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 119COMMIT; 120--source include/sync_slave_sql_with_master.inc 121--source include/rpl_connection_master.inc 122--let $limit= 0,18 123--let $event_sequence= #Gtid # !Begin # !Delete # !Commit #Gtid # !Begin # !Insert # !Commit #Gtid # !Begin # !Delete # !Commit #Gtid # !Begin # !Insert # !Commit 124--source include/assert_binlog_events.inc 125--source include/save_binlog_position.inc 126 127# Scenario 1.5: 128UPDATE mem_t6, mem_t7 SET mem_t6.c1 = mem_t6.c1 + 1, mem_t7.c1 = mem_t7.c1 + 1; 129--source include/sync_slave_sql_with_master.inc 130--source include/rpl_connection_master.inc 131--let $limit= 0,8 132--let $event_sequence= #Gtid # !Begin # !Delete # !Commit #Gtid # !Begin # !Delete # !Commit 133--source include/assert_binlog_events.inc 134--source include/save_binlog_position.inc 135 136#Scenario 1.6: 137--let $explicit_gtid=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 138--eval SET @@session.gtid_next = '$explicit_gtid' 139INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 140SET @@session.gtid_next = 'AUTOMATIC'; 141--source include/sync_slave_sql_with_master.inc 142--source include/rpl_connection_master.inc 143--let $limit= 0,9 144--let $event_sequence= #Gtid # !Begin # !Delete # !Commit #Gtid/(.*'$explicit_gtid')# !Begin # !Insert # !Commit 145--source include/assert_binlog_events.inc 146 147# Cleanup 148DROP TABLE mem_t1, mem_t2, mem_t3, mem_t4, mem_t5, mem_t6, mem_t7, mem_t8, mem_t9; 149--source include/rpl_reset.inc 150 151# Scenario 2: 152--source include/rpl_connection_master.inc 153CREATE TABLE mem_t1 (c1 INT) ENGINE= MEMORY; 154CREATE TABLE mem_t2 (c1 INT) ENGINE= MEMORY; 155CREATE TABLE mem_t3 (c1 INT) ENGINE= MEMORY; 156CREATE TABLE mem_t4 (c1 INT) ENGINE= MEMORY; 157CREATE TABLE mem_t5 (c1 INT) ENGINE= MEMORY; 158CREATE TABLE mem_t6 (c1 INT) ENGINE= MEMORY; 159CREATE TABLE mem_t7 (c1 INT) ENGINE= MEMORY; 160CREATE TABLE mem_t8 (c1 INT) ENGINE= MEMORY; 161INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 162INSERT INTO mem_t2 VALUES (1), (2), (3), (4); 163INSERT INTO mem_t3 VALUES (1), (2), (3), (4); 164INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 165INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 166INSERT INTO mem_t6 VALUES (1), (2), (3), (4); 167INSERT INTO mem_t7 VALUES (1), (2), (3), (4); 168INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 169--source include/sync_slave_sql_with_master.inc 170 171# Restart slave 172--let $rpl_server_number=2 173--source include/rpl_restart_server.inc 174--source include/start_slave.inc 175--source include/save_binlog_position.inc 176 177# Save the UUID of master and slave 178--source include/rpl_connection_master.inc 179--let $master_uuid = `SELECT @@global.server_uuid` 180--source include/rpl_connection_slave.inc 181--let $slave_uuid = `SELECT @@global.server_uuid` 182--let $dont_print_pattern = 1 183 184# Scenario 2.1 185--source include/rpl_connection_master.inc 186INSERT INTO mem_t1 VALUES (1), (2), (3), (4); 187--source include/sync_slave_sql_with_master.inc 188--source include/rpl_connection_slave.inc 189--let $limit= 0,9 190--let $event_sequence= #Gtid/(.*'$slave_uuid:1') # !Begin # !Delete # !Commit #Gtid/(.*'$master_uuid:17') # !Begin # !Insert # !Commit 191--source include/assert_binlog_events.inc 192--source include/save_binlog_position.inc 193 194# Scenario 2.2 195--source include/rpl_connection_master.inc 196ALTER TABLE mem_t2 ADD COLUMN (c2 INT); 197--source include/sync_slave_sql_with_master.inc 198--source include/rpl_connection_slave.inc 199--let $limit= 0,6 200--let $event_sequence= #Gtid/(.*'$slave_uuid:2') # !Begin # !Delete # !Commit #Gtid/(.*'$master_uuid:18') # !Q(ALTER.*) 201--source include/assert_binlog_events.inc 202--source include/save_binlog_position.inc 203 204# Scenario 2.3 205--source include/rpl_connection_master.inc 206CREATE TABLE mem_t9 LIKE mem_t3; 207--source include/sync_slave_sql_with_master.inc 208--source include/rpl_connection_slave.inc 209--let $limit= 0,6 210--let $event_sequence= #Gtid/(.*'$slave_uuid:3') # !Begin # !Delete # !Commit #Gtid/(.*'$master_uuid:19') # !Q(CREATE.*) 211--source include/assert_binlog_events.inc 212--source include/save_binlog_position.inc 213 214# Scenario 2.4: 215--source include/rpl_connection_master.inc 216BEGIN; 217INSERT INTO mem_t4 VALUES (1), (2), (3), (4); 218INSERT INTO mem_t5 VALUES (1), (2), (3), (4); 219COMMIT; 220--source include/sync_slave_sql_with_master.inc 221--source include/rpl_connection_slave.inc 222--let $limit= 0,18 223--let $event_sequence= #Gtid/(.*'$slave_uuid:4') # !Begin # !Delete # !Commit #Gtid/(.*'$master_uuid:20') # !Begin # !Insert # !Commit #Gtid/(.*'$slave_uuid:5') # !Begin # !Delete # !Commit #Gtid/(.*'$master_uuid:21') # !Begin # !Insert # !Commit 224--source include/assert_binlog_events.inc 225--source include/save_binlog_position.inc 226 227# Scenario 2.5: 228--source include/rpl_connection_master.inc 229DELIMITER |; 230CREATE FUNCTION f () RETURNS INT BEGIN 231INSERT INTO mem_t6 VALUES (1); 232RETURN 2; 233END| 234DELIMITER ;| 235INSERT INTO mem_t7 VALUES (f()); 236--source include/sync_slave_sql_with_master.inc 237--source include/rpl_connection_slave.inc 238--let $limit= 2,16 239--let $event_sequence= #Gtid/(.*'$slave_uuid:6') # !Begin # !Delete # !Commit #Gtid/(.*'$slave_uuid:7') # !Begin # !Delete # !Commit #Gtid/(.*'$master_uuid:23') # !Begin # !Multi_DML # !Commit 240--source include/assert_binlog_events.inc 241--source include/save_binlog_position.inc 242 243#Scenario 2.6: 244--source include/rpl_connection_master.inc 245--eval SET @@session.gtid_next = '$explicit_gtid' 246INSERT INTO mem_t8 VALUES (1), (2), (3), (4); 247SET @@session.gtid_next = 'AUTOMATIC'; 248--source include/sync_slave_sql_with_master.inc 249--source include/rpl_connection_slave.inc 250--let $limit= 0,9 251--let $event_sequence= #Gtid/(.*'$slave_uuid:8') # !Begin # !Delete # !Commit #Gtid/(.*'$explicit_gtid')# !Begin # !Insert # !Commit 252--source include/assert_binlog_events.inc 253 254# Cleanup 255--source include/rpl_connection_master.inc 256DROP FUNCTION f; 257DROP TABLE mem_t1, mem_t2, mem_t3, mem_t4, mem_t5, mem_t6, mem_t7, mem_t8, mem_t9; 258--let $dont_print_pattern = 0 259--source include/rpl_end.inc 260