1--source include/have_innodb.inc 2--source include/have_debug.inc 3--source include/have_debug_sync.inc 4--let $rpl_topology=1->2 5--source include/rpl_init.inc 6 7call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); 8call mtr.add_suppression("Can't find record in 't1'"); 9call mtr.add_suppression("Can't find record in 't2'"); 10 11--connection server_1 12ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; 13CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; 14--save_master_pos 15 16--connection server_2 17--sync_with_master 18SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; 19--source include/stop_slave.inc 20SET GLOBAL slave_parallel_threads=10; 21CHANGE MASTER TO master_use_gtid=slave_pos; 22SET @old_parallel_mode=@@GLOBAL.slave_parallel_mode; 23SET GLOBAL slave_parallel_mode='optimistic'; 24 25 26--connection server_1 27 28INSERT INTO t1 VALUES(1,1); 29BEGIN; 30INSERT INTO t1 VALUES(2,1); 31INSERT INTO t1 VALUES(3,1); 32COMMIT; 33 34# Do a bunch of INSERT/DELETE on the same rows, bound to conflict. 35# We will get a lot of rollbacks, probably, but they should be handled without 36# any visible errors. 37 38DELETE FROM t1 WHERE a=2; 39INSERT INTO t1 VALUES (2,2); 40DELETE FROM t1 WHERE a=2; 41INSERT INTO t1 VALUES (2,3); 42DELETE FROM t1 WHERE a=2; 43INSERT INTO t1 VALUES (2,4); 44DELETE FROM t1 WHERE a=2; 45INSERT INTO t1 VALUES (2,5); 46 47DELETE FROM t1 WHERE a=3; 48INSERT INTO t1 VALUES(3,2); 49DELETE FROM t1 WHERE a=1; 50INSERT INTO t1 VALUES(1,2); 51DELETE FROM t1 WHERE a=3; 52INSERT INTO t1 VALUES(3,3); 53 54DELETE FROM t1 WHERE a=2; 55INSERT INTO t1 VALUES (2,6); 56--source include/save_master_gtid.inc 57SELECT * FROM t1 ORDER BY a; 58 59--connection server_2 60--source include/start_slave.inc 61--source include/sync_with_master_gtid.inc 62SELECT * FROM t1 ORDER BY a; 63#SHOW STATUS LIKE 'Slave_retried_transactions'; 64 65 66--echo *** Test a bunch of non-transactional/DDL event groups. *** 67 68--connection server_2 69--source include/stop_slave.inc 70 71--connection server_1 72 73INSERT INTO t1 VALUES (4,4); 74INSERT INTO t1 VALUES (5,5); 75CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 76INSERT INTO t2 VALUES (1); 77CREATE TABLE t3 (a INT PRIMARY KEY) ENGINE=MyISAM; 78ALTER TABLE t2 ADD b INT; 79INSERT INTO t2 VALUES (2,2); 80ALTER TABLE t2 DROP b; 81INSERT INTO t2 VALUES (3); 82ALTER TABLE t2 ADD c INT; 83INSERT INTO t2 VALUES (4,5); 84INSERT INTO t2 VALUES (5,5); 85INSERT INTO t3 VALUES (1); 86UPDATE t2 SET c=NULL WHERE a=4; 87ALTER TABLE t2 ADD UNIQUE (c); 88INSERT INTO t2 VALUES (6,6); 89UPDATE t2 SET c=c+100 WHERE a=2; 90INSERT INTO t3(a) VALUES (2); 91DELETE FROM t3 WHERE a=2; 92INSERT INTO t3(a) VALUES (2); 93DELETE FROM t3 WHERE a=2; 94ALTER TABLE t3 CHANGE a c INT NOT NULL; 95INSERT INTO t3(c) VALUES (2); 96DELETE FROM t3 WHERE c=2; 97INSERT INTO t3 SELECT a+200 FROM t2; 98DELETE FROM t3 WHERE c >= 200; 99INSERT INTO t3 SELECT a+200 FROM t2; 100--source include/save_master_gtid.inc 101SELECT * FROM t1 ORDER BY a; 102SELECT * FROM t2 ORDER BY a; 103SELECT * FROM t3 ORDER BY c; 104 105--connection server_2 106--source include/start_slave.inc 107--source include/sync_with_master_gtid.inc 108SELECT * FROM t1 ORDER BY a; 109SELECT * FROM t2 ORDER BY a; 110SELECT * FROM t3 ORDER BY c; 111#SHOW STATUS LIKE 'Slave_retried_transactions'; 112 113 114--echo *** Test @@skip_parallel_replication. *** 115 116--connection server_2 117--source include/stop_slave.inc 118--let $retry1= query_get_value(SHOW STATUS LIKE 'Slave_retried_transactions', Value, 1) 119 120--connection server_1 121# We do a bunch of conflicting transactions on the master with 122# skip_parallel_replication set to true, and check that we do not 123# get any retries on the slave. 124 125UPDATE t1 SET b=10 WHERE a=3; 126SET SESSION skip_parallel_replication=1; 127UPDATE t1 SET b=20 WHERE a=3; 128UPDATE t1 SET b=30 WHERE a=3; 129UPDATE t1 SET b=50 WHERE a=3; 130UPDATE t1 SET b=80 WHERE a=3; 131UPDATE t1 SET b=130 WHERE a=3; 132UPDATE t1 SET b=210 WHERE a=3; 133UPDATE t1 SET b=340 WHERE a=3; 134UPDATE t1 SET b=550 WHERE a=3; 135UPDATE t1 SET b=890 WHERE a=3; 136SET SESSION skip_parallel_replication=0; 137SELECT * FROM t1 ORDER BY a; 138--source include/save_master_gtid.inc 139 140--connection server_2 141--source include/start_slave.inc 142--source include/sync_with_master_gtid.inc 143SELECT * FROM t1 ORDER BY a; 144--let $retry2= query_get_value(SHOW STATUS LIKE 'Slave_retried_transactions', Value, 1) 145--disable_query_log 146eval SELECT IF($retry1=$retry2, "Ok, no retry", 147 CONCAT("ERROR: ", $retry2-$retry1, " retries during replication (was ", 148 $retry1, " now ", $retry2, ")")) AS status; 149--enable_query_log 150 151 152--echo *** Test that we do not replicate in parallel transactions that had row lock waits on the master *** 153 154--connection server_2 155--source include/stop_slave.inc 156--let $retry1= query_get_value(SHOW STATUS LIKE 'Slave_retried_transactions', Value, 1) 157 158--connection server_1 159# Setup a bunch of transactions that all needed to wait. 160--connect (m1,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 161--connect (m2,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 162--connect (m3,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 163--connect (m4,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 164--connect (m5,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 165--connect (m6,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 166--connect (m7,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 167--connect (m8,127.0.0.1,root,,test,$SERVER_MYPORT_1,) 168 169--connection default 170BEGIN; UPDATE t1 SET b=b+1 WHERE a=3; 171 172--connection m1 173SET debug_sync='thd_report_wait_for SIGNAL waiting1'; 174send UPDATE t1 SET b=1001 WHERE a=3; 175--connection default 176SET debug_sync='now WAIT_FOR waiting1'; 177 178--connection m2 179BEGIN; 180UPDATE t1 SET b=1002 WHERE a=5; 181SET debug_sync='thd_report_wait_for SIGNAL waiting2'; 182send UPDATE t1 SET b=102 WHERE a=3; 183--connection default 184SET debug_sync='now WAIT_FOR waiting2'; 185 186UPDATE t1 SET b=1000 WHERE a=1; 187--connection m3 188SET debug_sync='thd_report_wait_for SIGNAL waiting3'; 189send UPDATE t1 SET b=1003 WHERE a=5; 190--connection default 191SET debug_sync='now WAIT_FOR waiting3'; 192 193--connection m4 194SET debug_sync='thd_report_wait_for SIGNAL waiting4'; 195send UPDATE t1 SET b=1004 WHERE a=3; 196--connection default 197SET debug_sync='now WAIT_FOR waiting4'; 198 199--connection m5 200SET debug_sync='thd_report_wait_for SIGNAL waiting5'; 201send UPDATE t1 SET b=1005 WHERE a=5; 202--connection default 203SET debug_sync='now WAIT_FOR waiting5'; 204 205--connection m6 206SET debug_sync='thd_report_wait_for SIGNAL waiting6'; 207send UPDATE t1 SET b=1006 WHERE a=1; 208--connection default 209SET debug_sync='now WAIT_FOR waiting6'; 210 211--connection m7 212SET debug_sync='thd_report_wait_for SIGNAL waiting7'; 213send UPDATE t1 SET b=1007 WHERE a=5; 214--connection default 215SET debug_sync='now WAIT_FOR waiting7'; 216 217--connection m8 218SET debug_sync='thd_report_wait_for SIGNAL waiting8'; 219send UPDATE t1 SET b=1008 WHERE a=3; 220--connection default 221SET debug_sync='now WAIT_FOR waiting8'; 222 223--connection default 224COMMIT; 225--connection m1 226REAP; 227--connection m2 228REAP; 229COMMIT; 230--connection m3 231REAP; 232--connection m4 233REAP; 234--connection m5 235REAP; 236--connection m6 237REAP; 238--connection m7 239REAP; 240--connection m8 241REAP; 242--connection default 243SET debug_sync='RESET'; 244SELECT * FROM t1 ORDER BY a; 245--source include/save_master_gtid.inc 246 247--connection server_2 248--source include/start_slave.inc 249--source include/sync_with_master_gtid.inc 250SELECT * FROM t1 ORDER BY a; 251--let $retry2= query_get_value(SHOW STATUS LIKE 'Slave_retried_transactions', Value, 1) 252--disable_query_log 253eval SELECT IF($retry1=$retry2, "Ok, no retry", 254 CONCAT("ERROR: ", $retry2-$retry1, " retries during replication (was ", 255 $retry1, " now ", $retry2, ")")) AS status; 256--enable_query_log 257 258 259--echo *** Test that we replicate correctly when using READ COMMITTED and binlog_format=MIXED on the slave *** 260 261--connection server_2 262--source include/stop_slave.inc 263SET @old_format= @@GLOBAL.binlog_format; 264# Use MIXED format; we cannot binlog ROW events on slave in STATEMENT format. 265SET GLOBAL binlog_format= MIXED; 266SET @old_isolation= @@GLOBAL.tx_isolation; 267SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 268# Reset the worker threads to make the new settings take effect. 269SET GLOBAL slave_parallel_threads=0; 270SET GLOBAL slave_parallel_threads=10; 271 272--connection server_1 273DROP TABLE t1, t2; 274CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; 275CREATE TABLE t2 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; 276INSERT INTO t1 VALUES (1,0), (2,0), (3,0); 277INSERT INTO t2 VALUES (1,0), (2,0); 278INSERT INTO t1 SELECT 4, COUNT(*) FROM t2; 279INSERT INTO t2 SELECT 4, COUNT(*) FROM t1; 280 281INSERT INTO t1 SELECT 5, COUNT(*) FROM t2; 282INSERT INTO t2 SELECT 5, COUNT(*) FROM t1; 283 284INSERT INTO t2 SELECT 6, COUNT(*) FROM t1; 285INSERT INTO t1 SELECT 6, COUNT(*) FROM t2; 286 287INSERT INTO t1 SELECT 7, COUNT(*) FROM t2; 288INSERT INTO t2 SELECT 7, COUNT(*) FROM t1; 289 290INSERT INTO t2 SELECT 8, COUNT(*) FROM t1; 291INSERT INTO t1 SELECT 8, COUNT(*) FROM t2; 292 293INSERT INTO t2 SELECT 9, COUNT(*) FROM t1; 294INSERT INTO t1 SELECT 9, COUNT(*) FROM t2; 295 296INSERT INTO t1 SELECT 10, COUNT(*) FROM t2; 297INSERT INTO t2 SELECT 10, COUNT(*) FROM t1; 298 299SELECT * FROM t1 ORDER BY a; 300SELECT * FROM t2 ORDER BY a; 301--source include/save_master_gtid.inc 302 303--connection server_2 304--source include/start_slave.inc 305--source include/sync_with_master_gtid.inc 306SELECT * FROM t1 ORDER BY a; 307SELECT * FROM t2 ORDER BY a; 308 309--source include/stop_slave.inc 310SET GLOBAL binlog_format= @old_format; 311SET GLOBAL tx_isolation= @old_isolation; 312--source include/start_slave.inc 313 314 315--echo *** MDEV-7888: ANALYZE TABLE does wakeup_subsequent_commits(), causing wrong binlog order and parallel replication hang *** 316 317--connection server_1 318DROP TABLE t1, t2, t3; 319CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; 320CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; 321CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=MyISAM; 322INSERT INTO t2 VALUES (1,1), (2,1), (3,1), (4,1), (5,1); 323--source include/save_master_gtid.inc 324 325--connection server_2 326--source include/sync_with_master_gtid.inc 327--source include/stop_slave.inc 328SET @old_debug= @@GLOBAL.debug_dbug; 329SET GLOBAL debug_dbug= '+d,inject_analyze_table_sleep'; 330 331--connection server_1 332# The bug was that ANALYZE TABLE would call 333# wakeup_subsequent_commits() too early, allowing the following 334# transaction in the same group to run ahead and binlog and free the 335# GCO. Then we get wrong binlog order and later access freed GCO, 336# which causes lost wakeup of following GCO and thus replication hang. 337# We injected a small sleep in ANALYZE to make the race easier to hit (this 338# can only cause false negatives in versions with the bug, not false positives, 339# so sleep is ok here. And it's in general not possible to trigger reliably 340# the race with debug_sync, since the bugfix makes the race impossible). 341 342ALTER TABLE t2 COMMENT "123abc"; 343ANALYZE TABLE t2; 344INSERT INTO t1 VALUES (1,2); 345INSERT INTO t1 VALUES (2,2); 346INSERT INTO t1 VALUES (3,2); 347INSERT INTO t1 VALUES (4,2); 348INSERT INTO t3 VALUES (1,3); 349ALTER TABLE t2 COMMENT "hello, world"; 350BEGIN; 351INSERT INTO t1 VALUES (5,4); 352INSERT INTO t1 VALUES (6,4); 353INSERT INTO t1 VALUES (7,4); 354INSERT INTO t1 VALUES (8,4); 355INSERT INTO t1 VALUES (9,4); 356INSERT INTO t1 VALUES (10,4); 357INSERT INTO t1 VALUES (11,4); 358INSERT INTO t1 VALUES (12,4); 359INSERT INTO t1 VALUES (13,4); 360INSERT INTO t1 VALUES (14,4); 361INSERT INTO t1 VALUES (15,4); 362INSERT INTO t1 VALUES (16,4); 363INSERT INTO t1 VALUES (17,4); 364INSERT INTO t1 VALUES (18,4); 365INSERT INTO t1 VALUES (19,4); 366INSERT INTO t1 VALUES (20,4); 367COMMIT; 368INSERT INTO t1 VALUES (21,5); 369INSERT INTO t1 VALUES (22,5); 370 371SELECT * FROM t1 ORDER BY a; 372SELECT * FROM t2 ORDER BY a; 373SELECT * FROM t3 ORDER BY a; 374--source include/save_master_gtid.inc 375 376--connection server_2 377--source include/start_slave.inc 378--source include/sync_with_master_gtid.inc 379 380SELECT * FROM t1 ORDER BY a; 381SELECT * FROM t2 ORDER BY a; 382SELECT * FROM t3 ORDER BY a; 383 384--source include/stop_slave.inc 385SET GLOBAL debug_dbug= @old_debug; 386--source include/start_slave.inc 387 388--echo *** MDEV-7929: record_gtid() for non-transactional event group calls wakeup_subsequent_commits() too early, causing slave hang. *** 389 390--connection server_2 391--source include/stop_slave.inc 392SET @old_dbug= @@GLOBAL.debug_dbug; 393# The bug was that record_gtid(), when there is no existing transaction from 394# a DML event being replicated, would commit its own transaction. This wrongly 395# caused wakeup_subsequent_commits(), with similar consequences as MDEV-7888 396# above. We simulate this condition with a small sleep in record_gtid() for 397# a specific ANALYZE that we binlog with server id 100. 398SET GLOBAL debug_dbug= '+d,inject_record_gtid_serverid_100_sleep'; 399 400--connection server_1 401 402ALTER TABLE t3 COMMENT "DDL statement 1"; 403INSERT INTO t1 VALUES (30,0); 404INSERT INTO t1 VALUES (31,0); 405INSERT INTO t1 VALUES (32,0); 406INSERT INTO t1 VALUES (33,0); 407INSERT INTO t1 VALUES (34,0); 408INSERT INTO t1 VALUES (35,0); 409INSERT INTO t1 VALUES (36,0); 410SET @old_server_id= @@SESSION.server_id; 411SET SESSION server_id= 100; 412ANALYZE TABLE t2; 413SET SESSION server_id= @old_server_id; 414INSERT INTO t1 VALUES (37,0); 415ALTER TABLE t3 COMMENT "DDL statement 2"; 416INSERT INTO t1 VALUES (38,0); 417INSERT INTO t1 VALUES (39,0); 418ALTER TABLE t3 COMMENT "DDL statement 3"; 419 420SELECT * FROM t1 WHERE a >= 30 ORDER BY a; 421 422--source include/save_master_gtid.inc 423 424 425--connection server_2 426--source include/start_slave.inc 427--source include/sync_with_master_gtid.inc 428SELECT * FROM t1 WHERE a >= 30 ORDER BY a; 429 430 431--source include/stop_slave.inc 432SET GLOBAL debug_dbug= @old_debug; 433--source include/start_slave.inc 434 435 436--echo *** MDEV-8113: ALTER TABLE causes slave hang in optimistic parallel replication *** 437 438--connection server_2 439--source include/stop_slave.inc 440 441--connection server_1 442ALTER TABLE t2 ADD c INT; 443INSERT INTO t2 (a,b) VALUES (50, 0); 444INSERT INTO t2 (a,b) VALUES (51, 1); 445INSERT INTO t2 (a,b) VALUES (52, 2); 446INSERT INTO t2 (a,b) VALUES (53, 3); 447INSERT INTO t2 (a,b) VALUES (54, 4); 448INSERT INTO t2 (a,b) VALUES (55, 5); 449INSERT INTO t2 (a,b) VALUES (56, 6); 450INSERT INTO t2 (a,b) VALUES (57, 7); 451INSERT INTO t2 (a,b) VALUES (58, 8); 452INSERT INTO t2 (a,b) VALUES (59, 9); 453ALTER TABLE t2 DROP COLUMN c; 454SELECT * FROM t2 WHERE a >= 50 ORDER BY a; 455--source include/save_master_gtid.inc 456 457--connection server_2 458--source include/start_slave.inc 459--source include/sync_with_master_gtid.inc 460SELECT * FROM t2 WHERE a >= 50 ORDER BY a; 461 462 463--echo *** MDEV-8075: DROP TEMPORARY TABLE not marked as ddl, causing optimistic parallel replication to fail *** 464 465--connection server_2 466--source include/stop_slave.inc 467 468--connection server_1 469INSERT INTO t1 VALUES (40, 10); 470CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 471INSERT INTO t1 VALUES (41); 472BEGIN; 473INSERT INTO t2 SELECT a, 20 FROM t1; 474DROP TEMPORARY TABLE t1; 475COMMIT; 476INSERT INTO t1 VALUES (42, 10); 477--source include/save_master_gtid.inc 478SELECT * FROM t1 WHERE a >= 40 ORDER BY a; 479SELECT * FROM t2 WHERE a >= 40 ORDER BY a; 480 481--connection server_2 482--source include/start_slave.inc 483--source include/sync_with_master_gtid.inc 484SELECT * FROM t1 WHERE a >= 40 ORDER BY a; 485SELECT * FROM t2 WHERE a >= 40 ORDER BY a; 486 487# partial cleanup to reuse the tables by following tests 488--connection server_1 489DELETE FROM t1; 490DELETE FROM t2; 491--source include/save_master_gtid.inc 492 493--connection server_2 494--source include/sync_with_master_gtid.inc 495 496# 497# MDEV-13577 optimistic parallel slave errors out to error log unnecessary 498# 499 500# The 1st of the following two trx:s a blocker on slave 501--connection server_2 502set global log_warnings=2; 503BEGIN; 504INSERT INTO t1 SET a=1; 505 506--connection server_1 507SET @save.binlog_format=@@session.binlog_format; 508SET @@SESSION.binlog_format=row; 509 510BEGIN; 511 INSERT INTO t1 SET a=1; 512 INSERT INTO t2 SET a=1; 513COMMIT; 514 515# This transaction is going to win optimistical race with above INSERT 516# on slave while being depend on it. That means it will face a kind of temporary error 517# and then will retry to succeed. 518BEGIN; 519 DELETE FROM t2; 520COMMIT; 521 522# First make sure DELETE raced indeed to get stuck at retrying stage 523# where it runs "realistically" now. There is nomore optimistic error 524# in the errorlog, which is downgraded to the warning level (when 525# --log-warnings > 1), see above suppression. 526--connection server_2 527--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" 528--source include/wait_condition.inc 529 530# Next release the 1st trx to commit. 531--connection server_2 532ROLLBACK; 533 534# MDEV-13577 local cleanup: 535--connection server_1 536SET @@SESSION.binlog_format= @save.binlog_format; 537DELETE FROM t1; 538DELETE FROM t2; 539--source include/save_master_gtid.inc 540 541--connection server_2 542--source include/sync_with_master_gtid.inc 543 544# 545# Clean up. 546# 547--connection server_2 548--source include/stop_slave.inc 549set global log_warnings=default; 550SET GLOBAL slave_parallel_mode=@old_parallel_mode; 551SET GLOBAL slave_parallel_threads=@old_parallel_threads; 552--source include/start_slave.inc 553 554--connection server_1 555DROP TABLE t1, t2, t3; 556--source include/save_master_gtid.inc 557 558--connection server_2 559--source include/sync_with_master_gtid.inc 560# Check for left-over rows in table mysql.gtid_slave_pos (MDEV-12147). 561# 562# There was a bug when a transaction got a conflict and was rolled back. It 563# might have also handled deletion of some old rows, and these deletions would 564# then also be rolled back. And since the deletes were never re-tried, old no 565# longer needed rows would accumulate in the table without limit. 566# 567# The earlier part of this test file have plenty of transactions being rolled 568# back. But the last DROP TABLE statement runs on its own and should never 569# conflict, thus at this point the mysql.gtid_slave_pos table should be clean. 570# 571# To support @@gtid_pos_auto_engines, when a row is inserted in the table, it 572# is associated with the engine of the table at insertion time, and it will 573# only be deleted during record_gtid from a table of the same engine. Since we 574# alter the table from MyISAM to InnoDB at the start of this test, we should 575# end up with 4 rows: two left-over from when the table was MyISAM, and two 576# left-over from the InnoDB part. 577--echo Check that no more than the expected last four GTIDs are in mysql.gtid_slave_pos 578select count(4) <= 4 from mysql.gtid_slave_pos order by domain_id, sub_id; 579 580--connection server_1 581--source include/rpl_end.inc 582