Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # Test timestamps generated by binlog-transaction-dependency-tracking
4 # modes WRITESET, WRITESET_SESSION and COMMIT_ORDER.
5 #
6 # The following properties are tested:
7 # - With binlog-transaction-dependency-tracking=WRITESET, the logical timestamps
8 # are generated as expected, marking each transaction as conflicting with
9 # transactions having intersecting writesets.
10 # (type = 0)
11 # - With binlog-transaction-dependency-tracking=WRITESET_SESSION, transactions
12 # are additionally marked as conflicting with all previous transactions in the
13 # same session.
14 # (type = 1)
15 # - Additionally, transactions are marked as conflicting with any DDL.
16 # (step = 2)
17 # - Additionally, transactions are marked as conflicting if one appears before
18 # and the other after a binlog rotation.
19 # (step = 3)
20 # - Additionally, transactions are marked as conflicting if the writeset history
21 # was cleared between the transactions.
22 # (step = 4)
23 #
24 # - It tests this for INSERT, UPDATE and DELETE statements.
25 #
26 # ==== Implementation ====
27 #
28 # 1. Apply 8 transactions with different data dependencies and check the binary
29 # log generated (step 1). Do the same with DDL in the middle (step 2), with
30 # rotation (step 3) and when writeset history becomes full (step 4).
31 #
32 # 2. Test Bug#2561672: POST RESET MASTER/FLUSH LOGS SETTING BTDT=WRITESET
33 # BEHAVES LIKE COMMIT_ORDER
34 #
35 # 3. Test ROLLBACK TO SAVEPOINT.
36 #
37 # ==== References ====
38 #
39 # WL#9556: Writeset-based MTS dependency tracking on master
40 #
41
42 --source include/master-slave.inc
43 --source include/have_binlog_format_row.inc
44 --source include/have_transaction_write_set_extraction.inc
45
46 --echo #### INITIALIZE ####
47
48 --connection slave
49 call mtr.add_suppression("Transaction is tagged with inconsistent logical timestamps");
50 call mtr.add_suppression("Cannot execute the current event group in the parallel mode.");
51
52 --source include/stop_slave.inc
53
54 SET @save_replica_parallel_type = @@GLOBAL.replica_parallel_type;
55 SET @save_replica_parallel_workers = @@GLOBAL.replica_parallel_workers;
56 SET @save_replica_transaction_retries = @@GLOBAL.replica_transaction_retries;
57
58 SET GLOBAL replica_parallel_type = LOGICAL_CLOCK;
59 SET GLOBAL replica_parallel_workers = 3;
60 SET GLOBAL replica_transaction_retries = 0;
61
62 --connection master
63
64 SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking;
65 SET @save_binlog_transaction_dependency_history_size = @@GLOBAL.binlog_transaction_dependency_history_size;
66
67 # for step 4
68 SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
69
70
71 CREATE TABLE tests (`id` INT NOT NULL, `description` VARCHAR(50), `results` VARCHAR(100), PRIMARY KEY(`id`));
72 INSERT INTO `tests` (`id`, `description`, `results`) VALUES
73 (1, 'Writeset', '0 1;1 2;1 3;1 4;3 5;3 6;4 7;7 8;6 9;9 10'),
74 (2, 'Writeset+DDL', '0 1;1 2;1 3;1 4;4 5;5 6;6 7;7 8;4 9;7 10;10 11;9 12;12 13'),
75 (3, 'Writeset+rotation', '0 1;0 2;1 3;3 4;2 5;5 6'),
76 (4, 'Writeset+history', '0 1;1 2;1 3;1 4;4 5;4 6;6 7;7 8;6 9;9 10'),
77 (5, 'Writeset_session', '0 1;1 2;1 3;2 4;3 5;3 6;5 7;7 8;7 9;9 10'),
78 (6, 'Writeset_session+DDL', '0 1;1 2;1 3;2 4;4 5;5 6;6 7;7 8;4 9;8 10;10 11;10 12;12 13'),
79 (7, 'Writeset_session+rotation', '0 1;0 2;1 3;3 4;3 5;5 6'),
80 (8, 'Writeset_session+history', '0 1;1 2;1 3;2 4;4 5;4 6;6 7;7 8;7 9;9 10'),
81 (9, 'Commit_order', '0 1;1 2;1 3;3 4;4 5;4 6;6 7;7 8;7 9;9 10'),
82 (10, 'Commit_order+DDL', '0 1;1 2;1 3;3 4;4 5;5 6;6 7;7 8;4 9;9 10;10 11;10 12;12 13'),
83 (11, 'Commit_order+rotation', '0 1;0 2;2 3;3 4;3 5;5 6'),
84 (12, 'Commit_order+history', '0 1;1 2;1 3;3 4;4 5;4 6;6 7;7 8;7 9;9 10');
85
86 # flush logs to clear previous transactions
87 FLUSH LOGS;
88
89 --let $idx= 0
90
91 --echo #### TEST ####
92
93 # loop between WRITESET and WRITESET_SESSION
94 --let $type= 0
95 while ($type < 3)
96 {
97 --inc $type
98
99 --connection master
100 if ($type == 1)
101 {
102 SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
103 --echo ######## 1. WRITESET ########
104 }
105 if ($type == 2)
106 {
107 SET GLOBAL binlog_transaction_dependency_tracking = WRITESET_SESSION;
108 --echo ######## 2. WRITESET_SESSION ########
109 }
110 if ($type == 3)
111 {
112 SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
113 --echo ######## 3. COMMIT_ORDER ########
114 }
115
116 # loop between steps
117 --let $step= 0
118 while ($step < 4)
119 {
120 --inc $step
121 --inc $idx
122
123 --let $testname=`SELECT description FROM tests WHERE id=$idx`
124 --echo #### STEP $type.$step TEST $testname ####
125
126 SET GLOBAL binlog_transaction_dependency_history_size=25000;
127 if ($step == 4)
128 {
129 --echo History size reduced to 5 to test when it becomes full
130 SET GLOBAL binlog_transaction_dependency_history_size=5;
131 }
132
133 SELECT @@binlog_transaction_dependency_tracking,
134 @@binlog_transaction_dependency_history_size;
135
136 CREATE TABLE `t1` (`id` int(11) NOT NULL, `val` int(11) NOT NULL,
137 PRIMARY KEY (`id`)) ENGINE=InnoDB;
138
139 --connection master
140 --echo #TRX2 (session A, independent)
141 BEGIN;
142 INSERT INTO t1(id, val) VALUES (1, 0),(2, 0),(3, 0);
143
144 --connection master1
145 --echo #TRX1 (session B, independent)
146 BEGIN;
147 INSERT INTO t1(id, val) VALUES (4, 0),(5, 0);
148 COMMIT;
149
150 --connection master
151 --echo finish TRX2 after TRX1
152 COMMIT;
153
154 --connection master1
155 --echo #TRX3 (session B, no data dependency, depends on TRX1 (session))
156 BEGIN;
157 INSERT INTO t1(id, val) VALUES (6, 0),(7, 0);
158 COMMIT;
159
160 --connection master
161 --echo #TRX5 (session A, depends on TRX2 (session and data) and TRX1(data))
162 BEGIN;
163 INSERT INTO t1(id, val) VALUES (8, 1);
164 UPDATE t1 SET val=val+1 WHERE id=1;
165 UPDATE t1 SET val=val+1 WHERE id=4;
166
167 if ($step == 2)
168 {
169 --connection master1
170 --echo Inserted a DDL after TRX5 and check that they run in parallel due to commit_order
171 CREATE TABLE `t2` (`id` int(11) NOT NULL, `val` int(11) NOT NULL) ENGINE=InnoDB;
172 ALTER TABLE t2 ADD COLUMN b INT;
173 DROP TABLE `t2`;
174 }
175
176 # flush logs in the middle
177 if ($step == 3)
178 {
179 --connection master1
180 --echo Flushing logs in the middle of the set
181 FLUSH LOGS;
182 }
183
184 --connection server_1
185 --echo #TRX4 (session C, depends on TRX2 (data))
186 BEGIN;
187 UPDATE t1 SET val=val+1 WHERE id=3;
188 INSERT INTO t1(id, val) VALUES (9, 1);
189 COMMIT;
190
191 --connection master
192 --echo finish TRX5 after TRX4
193 COMMIT;
194
195 --connection server_1
196 --echo #TRX6 (session C, depends on TRX4 (session) and TRX3(data))
197 BEGIN;
198 DELETE FROM t1 WHERE id=6;
199 COMMIT;
200
201 --connection server_1
202 --echo #TRX8 (session C, depends on TRX5 (data) and TRX6 (session))
203 BEGIN;
204 UPDATE t1 SET val=val+1 WHERE id=4;
205
206 --connection master1
207 --echo #TRX7 (session B, depends on TRX1 and TRX6 (data) and TRX3 (session))
208 BEGIN;
209 UPDATE t1 SET val=val+1 WHERE id=5;
210 INSERT INTO t1(id, val) VALUES (6, 0);
211 COMMIT;
212
213 --connection server_1
214 --echo finish TRX8 after TRX7
215 COMMIT;
216
217 --connection master
218 --echo Drop table and flush logs to force binlog to rotate
219 DROP TABLE t1;
220
221 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
222 --echo Processing binlog $binlog_file
223 FLUSH LOGS;
224
225 --let $source_file= $server_1_datadir/$binlog_file
226 --let $logical_timestamps=`SELECT results FROM tests WHERE id=$idx`
227 --source include/assert_logical_timestamps.inc
228 }
229 }
230
231 --echo
232 --echo #### Verify that replication was correct ####
233
234 --connection slave
235 --source include/start_slave.inc
236
237 --connection master
238 --source include/sync_slave_sql_with_master.inc
239
240 --let $diff_tables= master:test.tests, slave:test.tests
241 --source include/diff_tables.inc
242
243 --connection master
244 DROP TABLE `tests`;
245 --source include/sync_slave_sql_with_master.inc
246
247 --connection master
248 SET GLOBAL binlog_transaction_dependency_tracking= WRITESET;
249 SET GLOBAL binlog_transaction_dependency_history_size= DEFAULT;
250 FLUSH LOGS;
251
252 --echo
253 --echo #### Bug#25616372, PART1 ####
254 CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT); # 0 1
255 INSERT INTO t1 VALUES (null); # 1 2
256 INSERT INTO t1 VALUES (null); # 1 3
257 set global binlog_transaction_dependency_tracking=WRITESET;
258 INSERT INTO t1 VALUES (null); # 3 4
259 INSERT INTO t1 VALUES (null); # 3 5
260 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
261 -- echo Processing binlog $binlog_file
262 --let $source_file= $server_1_datadir/$binlog_file
263 --let $logical_timestamps=0 1;1 2;1 3;3 4;3 5
264 --source include/assert_logical_timestamps.inc
265 FLUSH LOGS;
266
267 --echo
268 --echo #### Bug#25616372, PART2 ####
269 INSERT INTO t1 VALUES (null); # 0 1
270 INSERT INTO t1 VALUES (null); # 0 2
271 set global binlog_transaction_dependency_tracking=WRITESET;
272 INSERT INTO t1 VALUES (null); # 2 3
273 INSERT INTO t1 VALUES (null); # 3 4. # It should be 2 4
274 INSERT INTO t1 VALUES (null); # 4 5. # It should be 2 5
275 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
276 -- echo Processing binlog $binlog_file
277 --let $source_file= $server_1_datadir/$binlog_file
278 --let $logical_timestamps=0 1;1 2;2 3;2 4;2 5
279 --source include/assert_logical_timestamps.inc
280 DROP TABLE t1;
281 FLUSH LOGS;
282
283
284 --echo
285 --echo #### Bug#26277771, PART1 ####
286 # Bug#26277771 original issue due to missing collation in generating hashes
287 CREATE TABLE t1 (c1 int(10) NOT NULL, c2 varchar(20) NOT NULL, PRIMARY KEY (c1),
288 UNIQUE KEY (c1, c2)) ENGINE=InnoDB DEFAULT CHARSET=latin1; # 0 1
289 INSERT INTO t1 VALUES (1, 'a'); # 1 2
290 DELETE FROM t1 WHERE c1=1 AND c2='a'; # 2 3
291 INSERT INTO t1 VALUES (1, 'A'); # 3 4
292 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
293 -- echo Processing binlog $binlog_file
294 --let $source_file= $server_1_datadir/$binlog_file
295 --let $logical_timestamps=0 1;1 2;2 3;3 4
296 --source include/assert_logical_timestamps.inc
297 DROP TABLE t1;
298 FLUSH LOGS;
299
300 --echo
301 --echo #### Bug#26277771, PART2 ####
302 # Bug#26277771 issue due to wrong string length calculation
303 CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL PRIMARY KEY); # 0 1
304 INSERT INTO t1 VALUES ('a'); # 1 2
305 INSERT INTO t1 VALUES ('A'); # 1 3
306 INSERT INTO t1 VALUES ('b'); # 1 4
307 INSERT INTO t1 VALUES ('B'); # 1 5
308 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
309 -- echo Processing binlog $binlog_file
310 --let $source_file= $server_1_datadir/$binlog_file
311 --let $logical_timestamps=0 1;1 2;1 3;1 4;1 5
312 --source include/assert_logical_timestamps.inc
313 DROP TABLE t1;
314 FLUSH LOGS;
315
316 --echo
317 --echo #### Bug#26277771, PART3 ####
318 # test for unset charset, padding and null terminaton
319 CREATE TABLE t1 (c1 VARCHAR(20) NOT NULL PRIMARY KEY); # 0 1
320 INSERT INTO t1 VALUES ('a'); # 1 2
321 INSERT INTO t1 VALUES ('b'); # 1 3
322
323 CREATE TABLE t2 (c1 BINARY(3) NOT NULL PRIMARY KEY); # 3 4
324 INSERT INTO t2 VALUES ('a'); # 4 5
325 # copy using strmake will generate same hash for '\0a' and '\0'
326 # as it was copying till null termination character and binary
327 # doesn't consider null as temination character.
328 INSERT INTO t2 VALUES ('\0a'); # 4 6
329 INSERT INTO t2 VALUES ('\0'); # 4 7
330 INSERT INTO t2 VALUES ('a '); # 4 8
331
332 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
333 -- echo Processing binlog $binlog_file
334 --let $source_file= $server_1_datadir/$binlog_file
335 --let $logical_timestamps=0 1;1 2;1 3;3 4;4 5;4 6;4 7;4 8
336 --source include/assert_logical_timestamps.inc
337 DROP TABLE t1;
338 DROP TABLE t2;
339 FLUSH LOGS;
340
341 --echo
342 --echo #### Bug#26277771, PART4 ####
343 CREATE TABLE t1 (c1 varchar(20) NOT NULL PRIMARY KEY)
344 ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; # 0 1
345 INSERT INTO t1 VALUES ('a'); # 1 2
346 INSERT INTO t1 VALUES ('รก'); # 1 3
347 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
348 --echo Processing binlog $binlog_file
349 --let $source_file= $server_1_datadir/$binlog_file
350 --let $logical_timestamps=0 1;1 2;1 3
351 --source include/assert_logical_timestamps.inc
352 DROP TABLE t1;
353 FLUSH LOGS;
354
355
356 #
357 # Test ROLLBACK TO SAVEPOINT. The rolled-back rows will still have their
358 # hashes in the writeset and may cause spurious conflicts.
359 #
360 --echo
361 -- echo #### Test ROLLBACK TO SAVEPOINT ####
362 CREATE TABLE t1 (a INT PRIMARY KEY);
363 CREATE TABLE nopk (a INT);
364 CREATE TABLE parent (a INT PRIMARY KEY);
365 CREATE TABLE child (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES parent(a));
366
367 --echo # 1. Hashes of rolled-back rows are removed from writeset
368 FLUSH LOGS;
369 INSERT INTO t1 VALUES (1);
370 BEGIN;
371 SAVEPOINT sp;
372 UPDATE t1 SET a = 2;
373 ROLLBACK TO sp;
374 INSERT INTO t1 VALUES (2);
375 COMMIT;
376 INSERT INTO t1 VALUES (3);
377
378 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
379 --echo Processing binlog $binlog_file
380 --let $source_file= $server_1_datadir/$binlog_file
381 --let $logical_timestamps=0 1;1 2;1 3
382 --source include/assert_logical_timestamps.inc
383
384 --echo # 2. If transaction is flagged as updating a non-index table, the flag is not rolled-back
385 FLUSH LOGS;
386 INSERT INTO t1 VALUES (4);
387 BEGIN;
388 SAVEPOINT sp;
389 INSERT INTO nopk VALUES (1);
390 ROLLBACK TO sp;
391 INSERT INTO t1 VALUES (5);
392 COMMIT;
393 INSERT INTO t1 VALUES (6);
394
395 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
396 --echo Processing binlog $binlog_file
397 --let $source_file= $server_1_datadir/$binlog_file
398 --let $logical_timestamps=0 1;1 2;1 3
399 --source include/assert_logical_timestamps.inc
400
401 --echo # 3. If transaction is flagged as updating a foreign key parent table, the flag is not rolled-back
402 FLUSH LOGS;
403 INSERT INTO t1 VALUES (7);
404 BEGIN;
405 SAVEPOINT sp;
406 INSERT INTO parent VALUES (1);
407 ROLLBACK TO sp;
408 INSERT INTO t1 VALUES (8);
409 COMMIT;
410 INSERT INTO t1 VALUES (9);
411
412 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
413 --echo Processing binlog $binlog_file
414 --let $source_file= $server_1_datadir/$binlog_file
415 --let $logical_timestamps=0 1;1 2;2 3
416 --source include/assert_logical_timestamps.inc
417
418 --echo # Clean up SAVEPOINT tests
419 DROP TABLE t1;
420 DROP TABLE nopk;
421 DROP TABLE child;
422 DROP TABLE parent;
423
424 #
425 # Cleanup
426 #
427 --echo
428 --echo #### CLEANUP ####
429
430 --source include/sync_slave_sql_with_master.inc
431 --source include/stop_slave.inc
432
433 SET @@GLOBAL.replica_parallel_type= @save_replica_parallel_type;
434 --disable_warnings
435 SET @@GLOBAL.replica_parallel_workers= @save_replica_parallel_workers;
436 --enable_warnings
437 SET @@GLOBAL.replica_transaction_retries= @save_replica_transaction_retries;
438
439 --source include/start_slave.inc
440
441 --connection master
442 SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
443 SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking;
444 SET @@GLOBAL.binlog_transaction_dependency_history_size= @save_binlog_transaction_dependency_history_size;
445
446 --source include/rpl_end.inc
447