Debian Codegraph @

mysql-test/suite/rpl/t/rpl_gtid_drop_mem_table.test

raw
1 # ==== Purpose ====
2 #
3 # The purpose of this test is to verify that a 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/not_rpl_gtid_only.inc
59 --source include/master-slave.inc
60
61 #Scenario 1
62 --source include/rpl_connection_master.inc
63 CREATE TABLE mem_t1 (c1 INT) ENGINE= MEMORY;
64 CREATE TABLE mem_t2 (c1 INT) ENGINE= MEMORY;
65 CREATE TABLE mem_t3 (c1 INT) ENGINE= MEMORY;
66 CREATE TABLE mem_t4 (c1 INT) ENGINE= MEMORY;
67 CREATE TABLE mem_t5 (c1 INT) ENGINE= MEMORY;
68 CREATE TABLE mem_t6 (c1 INT) ENGINE= MEMORY;
69 CREATE TABLE mem_t7 (c1 INT) ENGINE= MEMORY;
70 CREATE TABLE mem_t8 (c1 INT) ENGINE= MEMORY;
71 INSERT INTO mem_t1 VALUES (1), (2), (3), (4);
72 INSERT INTO mem_t2 VALUES (1), (2), (3), (4);
73 INSERT INTO mem_t3 VALUES (1), (2), (3), (4);
74 INSERT INTO mem_t4 VALUES (1), (2), (3), (4);
75 INSERT INTO mem_t5 VALUES (1), (2), (3), (4);
76 INSERT INTO mem_t6 VALUES (1), (2), (3), (4);
77 INSERT INTO mem_t7 VALUES (1), (2), (3), (4);
78 INSERT 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
90 INSERT 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
99 ALTER 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
108 CREATE 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:
117 BEGIN;
118 INSERT INTO mem_t4 VALUES (1), (2), (3), (4);
119 INSERT INTO mem_t5 VALUES (1), (2), (3), (4);
120 COMMIT;
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:
129 UPDATE 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'
140 INSERT INTO mem_t8 VALUES (1), (2), (3), (4);
141 SET @@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
149 DROP 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
154 CREATE TABLE mem_t1 (c1 INT) ENGINE= MEMORY;
155 CREATE TABLE mem_t2 (c1 INT) ENGINE= MEMORY;
156 CREATE TABLE mem_t3 (c1 INT) ENGINE= MEMORY;
157 CREATE TABLE mem_t4 (c1 INT) ENGINE= MEMORY;
158 CREATE TABLE mem_t5 (c1 INT) ENGINE= MEMORY;
159 CREATE TABLE mem_t6 (c1 INT) ENGINE= MEMORY;
160 CREATE TABLE mem_t7 (c1 INT) ENGINE= MEMORY;
161 CREATE TABLE mem_t8 (c1 INT) ENGINE= MEMORY;
162 INSERT INTO mem_t1 VALUES (1), (2), (3), (4);
163 INSERT INTO mem_t2 VALUES (1), (2), (3), (4);
164 INSERT INTO mem_t3 VALUES (1), (2), (3), (4);
165 INSERT INTO mem_t4 VALUES (1), (2), (3), (4);
166 INSERT INTO mem_t5 VALUES (1), (2), (3), (4);
167 INSERT INTO mem_t6 VALUES (1), (2), (3), (4);
168 INSERT INTO mem_t7 VALUES (1), (2), (3), (4);
169 INSERT 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 $replica_uuid = `SELECT @@global.server_uuid`
183 --let $dont_print_pattern = 1
184
185 # Scenario 2.1
186 --source include/rpl_connection_master.inc
187 INSERT 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/(.*'$replica_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
197 ALTER 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/(.*'$replica_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
207 CREATE 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/(.*'$replica_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
217 BEGIN;
218 INSERT INTO mem_t4 VALUES (1), (2), (3), (4);
219 INSERT INTO mem_t5 VALUES (1), (2), (3), (4);
220 COMMIT;
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/(.*'$replica_uuid:4') # !Q(TRUNCATE.*) #Gtid/(.*'$master_uuid:20') # !Begin # !Insert # !Commit #Gtid/(.*'$replica_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
230 DELIMITER |;
231 CREATE FUNCTION f () RETURNS INT BEGIN
232 INSERT INTO mem_t6 VALUES (1);
233 RETURN 2;
234 END|
235 DELIMITER ;|
236 INSERT 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/(.*'$replica_uuid:6') # !Q(TRUNCATE.*) #Gtid/(.*'$replica_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'
247 INSERT INTO mem_t8 VALUES (1), (2), (3), (4);
248 SET @@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/(.*'$replica_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
257 DROP FUNCTION f;
258 DROP 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