Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # Verifies that empty transactions are generated when they are
4 # supposed to. There are three cases:
5 #
6 # 1. Transaction is filtered out on slave
7 #
8 # 2. Transaction does not have any effect, gets logged and assigned a
9 # GTID on master because master uses binlog_format=statement, but
10 # does not get logged on slave because slave uses binlog_format='row'.
11 #
12 # 3. Transaction is explicitly made empty on master by setting
13 # gtid_next and executing a COMMIT.
14 #
15 # Moreover, verify that no empty transaction is generated for rolled
16 # back transactions.
17 #
18 # ==== Implementation ====
19 #
20 # For case 1, we use a filter (specified in the .cnf file) to filter
21 # out table t_ignore, and verify that empty transaction is generated
22 # on slave. Do this both for (1A) base tables and for (1B) temporary
23 # tables.
24 #
25 # For case 2, we use binlog_format=statement on master and
26 # binlog_format='row' on the first slave. We test two cases of
27 # transactions that are not logged in row format: (2A) temporary table
28 # DDL and DML and (2B) an UPDATE statement that matches no row. We
29 # use a second slave where the effect of the statement would be seen,
30 # if it was not logged as an empty statement. In case (2A) we check
31 # that replica_open_temp_tables is still 0 on the second slave. In case
32 # (2B) we do an UPDATE on master that matches no rows on master, but
33 # that would match a row on the second slave.
34 #
35 # For case 3, we just execute an explicit empty transaction
36 # inside/outside stored procedure on master and verify that it gets
37 # assigned a GTID and gets replicated to the slaves.
38 #
39 # To verify nothing is logged for rollback transactions, we execute a
40 # DDL statement that generates an error, a DML statement that
41 # generates an error, and an explicit ROLLBACK transaction, and verify
42 # that nothing got logged for the three statements.
43 #
44 # ==== References ====
45 #
46 # WL#3584 - Global Transaction Identifiers (GTIDs)
47 # - The base worklog implementing empty transactions.
48 # BUG#18145032 - NO EMPTY TRANSACTION IS CREATED FOR A FILTERED CREATE TEMPORARY TABLE WITH GTIDS
49 # - Addressing the special cases for CREATE/ALTER/DROP TEMPORARY.
50 # BUG#18095502 - RESTART OF SLAVE INSTANCE FAIL IN GTID REPLICATION IF WE USE REPLICATE-IGNORE-DB
51 # - Addressing database filters in the applier thread.
52 # BUG#19774317 GTID_NEXT WITH EMPTY TRANSACTIONS DOES NOT WORK INSIDE STORED PROCEDURES
53 # - Addressing gtid_next with empty transaction inside stored procedure.
54
55 --let $rpl_topology= 1->2->3
56 --let $rpl_gtid_utils= 1
57 --source include/rpl_init.inc
58 --source include/rpl_default_connections.inc
59 --source include/have_binlog_format_mixed.inc
60
61 --source include/rpl_reset.inc
62
63 --echo ==== Case 1A: Transaction filtered out ====
64
65 --source include/gtid_step_reset.inc
66
67 CREATE TABLE t_ignore(id INT);
68 INSERT INTO t_ignore VALUES (1);
69 DROP TABLE t_ignore;
70
71 CREATE TABLE t_ignore_wild(id INT);
72 INSERT INTO t_ignore_wild VALUES (1);
73 DROP TABLE t_ignore_wild;
74
75 # Verify that 6 GTIDs were generated.
76 --let $gtid_step_count= 6
77 --source include/gtid_step_assert.inc
78
79 # Verify that the GTIDs make it to every slave
80 --source include/rpl_sync.inc
81
82 --echo ==== Case 1B: CREATE/ALTER/DROP TEMPORARY filtered out ====
83
84 CREATE TEMPORARY TABLE t_ignore(a INT);
85 ALTER TABLE t_ignore ADD COLUMN b INT;
86 INSERT INTO t_ignore VALUES (1, 2);
87 DROP TEMPORARY TABLE t_ignore;
88
89 CREATE TEMPORARY TABLE t_ignore_wild(a INT);
90 ALTER TABLE t_ignore_wild ADD COLUMN b INT;
91 INSERT INTO t_ignore_wild VALUES (1, 2);
92 DROP TEMPORARY TABLE t_ignore_wild;
93
94 # Verify that 0 GTIDs were generated.
95 --let $gtid_step_count= 0
96 --source include/gtid_step_assert.inc
97
98 # Verify that the GTIDs make it to every slave.
99 --source include/rpl_sync.inc
100
101 --echo ==== Case 1C: database filters on slave applier ====
102
103 --source include/gtid_step_reset.inc
104
105 # These two will be ignored on first slave.
106 CREATE DATABASE db_ignore;
107 USE db_ignore;
108 CREATE TABLE t1 (a INT);
109 INSERT INTO t1 VALUES (1);
110 USE test;
111
112 # Verify 2 GTIDs were generated
113 --let $gtid_step_count= 3
114 --source include/gtid_step_assert.inc
115
116 --source include/rpl_sync.inc
117
118 --let $assert_text= db_ignore should not be created on slave
119 --let $assert_cond= "[slave:SHOW DATABASES LIKE "db_ignore"]" = ""
120 --source include/assert.inc
121
122 --let $assert_text= db_ignore should not be created on second slave
123 --let $assert_cond= "[server_3:SHOW DATABASES LIKE "db_ignore"]" = ""
124 --source include/assert.inc
125
126 DROP DATABASE db_ignore;
127 --source include/rpl_sync.inc
128
129 --echo ==== Case 1D: database filters on slave binary log ====
130
131 --source include/gtid_step_reset.inc
132
133 # These two will be ignored on first slave.
134 CREATE DATABASE db_binlog_ignore;
135 USE db_binlog_ignore;
136 CREATE TABLE t1 (a INT);
137 INSERT INTO t1 VALUES (1);
138 USE test;
139
140 # Verify 2 GTIDs were generated
141 --let $gtid_step_count= 3
142 --source include/gtid_step_assert.inc
143
144 --source include/rpl_sync.inc
145
146 --let $assert_text= db_binlog_ignore should not be created on slave
147 --let $assert_cond= "[slave:SHOW DATABASES LIKE "db_binlog_ignore"]" = "db_binlog_ignore"
148 --source include/assert.inc
149
150 --let $assert_text= db_binlog_ignore should not be created on second slave
151 --let $assert_cond= "[server_3:SHOW DATABASES LIKE "db_binlog_ignore"]" = ""
152 --source include/assert.inc
153
154 DROP DATABASE db_binlog_ignore;
155 --source include/rpl_sync.inc
156
157 --echo ==== Case 2A: temp table transaction not logged in row format ====
158
159 --echo ---- Initialize ----
160
161 --connection slave
162 SET @save.binlog_format= @@global.binlog_format;
163 # To avoid an error 'ER_RUNNING_APPLIER_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT',
164 # which will be caused by the following include/end_replace_combination.inc
165 --source include/stop_slave_sql.inc
166 SET @@global.binlog_format= 'row';
167 source include/start_slave_sql.inc;
168
169 --echo ---- Test ----
170
171 --source include/gtid_step_reset.inc
172
173
174 # - Master will log these statements and assign them GTIDs, since
175 # master uses binlog_format=statement.
176 #
177 # - First slave will not log any change since it uses row format, but
178 # it should log empty transactions with GTIDs.
179 #
180 # - Second slave should also not log any change, since it receives an
181 # empty transaction from the first slave. Thus
182 # Replica_open_temp_tables should remain 0 on the second slave.
183 CREATE TEMPORARY TABLE t2 (a INT);
184 ALTER TABLE t2 ADD COLUMN b INT;
185 INSERT INTO t2 VALUES (1, 2);
186
187 # assert exactly 0 GTIDs were generated
188 --let $gtid_step_count= 0
189 --source include/gtid_step_assert.inc
190
191 # Verify that the GTID gets replicated everywhere.
192 --let $use_gtids= 1
193 --source include/rpl_sync.inc
194
195 # Verify temp tables on first slave but not on second slave
196 --let $assert_text= First slave should not have created any temp table
197 --let $assert_cond= [slave:SHOW GLOBAL STATUS LIKE 'Replica_open_temp_tables'] = 1
198
199 --let $assert_text= Second slave should not have created any temp table
200 --let $assert_cond= [server_3:SHOW GLOBAL STATUS LIKE 'Replica_open_temp_tables'] = 0
201
202 --echo ---- Clean up ----
203
204 DROP TEMPORARY TABLE t2;
205 --source include/rpl_sync.inc
206
207 --connection slave
208 # To avoid an error 'ER_RUNNING_APPLIER_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT',
209 # which will be caused by the following include/end_replace_combination.inc
210 --source include/stop_slave_sql.inc
211 SET @@global.binlog_format= @save.binlog_format;
212 --connection master
213
214 --echo ==== Case 2B: transaction with no effect not logged in row format ====
215
216 --echo ---- Initialize ----
217
218 --connection slave
219 SET @save.binlog_format= @@global.binlog_format;
220 SET @@global.binlog_format= 'row';
221 source include/start_slave_sql.inc;
222
223 --connection master
224 SET @@session.binlog_format= statement;
225 CREATE TABLE t1(id INT) ENGINE = InnoDB;
226 --source include/rpl_sync.inc
227 --connection server_3
228 INSERT INTO t1 VALUES (0);
229 --connection master
230
231 --echo ---- Test ----
232
233 # - Master will log this and assign it a GTID despite it has no
234 # effect, since master uses binlog_format=statement.
235 #
236 # - First slave will not log any change since it uses row format, but
237 # it should log an empty transaction with a GTID.
238 #
239 # - Second slave should also not log any change, since it receives an
240 # empty transaction from the first slave. It should also not update
241 # the table despite the original SQL statement would match a row on
242 # the second slave, since it only receives the empty transaction
243 # from the first slave.
244 UPDATE t1 SET id= 1 WHERE id = 0;
245
246 # Verify that the GTID gets replicated everywhere.
247 --let $use_gtids= 1
248 --source include/rpl_sync.inc
249
250 --let $assert_text= Second slave should not have done any update
251 --let $assert_cond= [server_3:SELECT * FROM t1] = 0
252
253 -- echo ---- Clean up ----
254
255 DROP TABLE t1;
256 --source include/rpl_sync.inc
257
258 --connection slave
259 # To avoid an error 'ER_RUNNING_APPLIER_PREVENTS_SWITCH_GLOBAL_BINLOG_FORMAT',
260 # which will be caused by the following include/end_replace_combination.inc
261 --source include/stop_slave_sql.inc
262 SET @@global.binlog_format= @save.binlog_format;
263 --source include/start_slave_sql.inc
264 --connection master
265
266 --echo ==== Case 3: explicit empty transaction on master ====
267
268 SET @@SESSION.binlog_format= 'row';
269 --source include/gtid_step_reset.inc
270 eval SET @@SESSION.GTID_NEXT = '$uuida:1';
271 COMMIT;
272 SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
273
274 # Verify exactly one GTID was generated
275 --let $gtid_step_count= 1
276 --let $gtid_step_only_count= 1
277 --source include/gtid_step_assert.inc
278
279 # ==== Purpose ====
280 #
281 # Verify that committing an empty transaction with specified gtid works fine
282 # inside stored procedure when binlog is enabled.
283 #
284 # ==== References ====
285 #
286 # BUG#19774317 GTID_NEXT WITH EMPTY TRANSACTIONS DOES NOT WORK INSIDE STORED PROCEDURES
287 # mysql-test/t/no_binlog_gtid_empty_transaction.test
288
289 CREATE TABLE t1 (a INT);
290
291 delimiter |;
292
293 CREATE PROCEDURE p1()
294 BEGIN
295 SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
296 START TRANSACTION;
297 COMMIT;
298 SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
299 START TRANSACTION;
300 COMMIT;
301 END|
302
303 delimiter ;|
304
305 # Verify exactly two GTIDs were generated
306 --let $gtid_step_count= 2
307 --let $gtid_step_only_count= 1
308 --source include/gtid_step_assert.inc
309
310 CALL p1();
311
312 SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
313 # Verify exactly four GTIDs were generated
314 --let $gtid_step_count= 2
315 --let $gtid_step_only_count= 1
316 --source include/gtid_step_assert.inc
317 --let $gtid_step_only_count= 0
318
319 DROP TABLE t1;
320
321 # Verify the GTID makes it to every slave.
322 --source include/rpl_sync.inc
323
324 --echo ==== Case 4: Nothing logged for ROLLBACK transactions ====
325
326 # Test that ROLLBACK does not generate any GTID
327
328 --echo ---- Initialize ----
329
330 CREATE TABLE t1 (a INT) ENGINE = InnoDB;
331
332 --echo ---- Test ----
333
334 --source include/gtid_step_reset.inc
335
336 --error ER_BAD_TABLE_ERROR
337 DROP TABLE t2;
338
339 --error ER_WRONG_VALUE_COUNT_ON_ROW
340 INSERT INTO t1 VALUES (1, 1);
341
342 BEGIN;
343 INSERT INTO t1 VALUES (2);
344 ROLLBACK;
345
346 --let $gtid_step_count= 0
347 --source include/gtid_step_assert.inc
348
349 --echo ---- Clean Up ----
350
351 DROP TABLE t1;
352 DROP PROCEDURE p1;
353
354 --source include/rpl_end.inc