1--source include/have_innodb.inc 2--source include/have_debug.inc 3--source include/have_debug_sync.inc 4--source include/master-slave.inc 5 6--echo *** MDEV-7847: "Slave worker thread retried transaction 10 time(s) in vain, giving up", followed by replication hanging *** 7--echo *** MDEV-7882: Excessive transaction retry in parallel replication *** 8 9--connection server_1 10ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; 11CREATE TABLE t7 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; 12CREATE TABLE t8 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; 13--save_master_pos 14 15--connection server_2 16--sync_with_master 17--source include/stop_slave.inc 18 19 20# Using dbug error injection, we artificially create event groups with a lot of 21# conflicting transactions in each event group. The bugs were originally seen 22# "in the wild" with transactions that did not conflict on the master, and only 23# conflicted very rarely on the slave (maybe some edge case with InnoDB btree 24# page splits or something like that). The event groups here loosely reflect 25# the structure of the original failure's group commits. 26 27 28--connection server_1 29INSERT INTO t7 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); 30SET @old_dbug= @@SESSION.debug_dbug; 31SET @commit_id= 42; 32SET SESSION debug_dbug="+d,binlog_force_commit_id"; 33INSERT INTO t8 VALUES (1,1); 34INSERT INTO t8 VALUES (2,2); 35INSERT INTO t8 VALUES (3,3); 36INSERT INTO t8 VALUES (4,4); 37INSERT INTO t8 VALUES (5,5); 38INSERT INTO t8 VALUES (6,6); 39INSERT INTO t8 VALUES (7,7); 40INSERT INTO t8 VALUES (8,8); 41 42UPDATE t7 SET b=9 WHERE a=3; 43UPDATE t7 SET b=10 WHERE a=3; 44UPDATE t7 SET b=11 WHERE a=3; 45 46INSERT INTO t8 VALUES (12,12); 47INSERT INTO t8 VALUES (13,13); 48 49UPDATE t7 SET b=14 WHERE a=3; 50UPDATE t7 SET b=15 WHERE a=3; 51 52INSERT INTO t8 VALUES (16,16); 53 54UPDATE t7 SET b=17 WHERE a=3; 55 56INSERT INTO t8 VALUES (18,18); 57INSERT INTO t8 VALUES (19,19); 58 59UPDATE t7 SET b=20 WHERE a=3; 60 61INSERT INTO t8 VALUES (21,21); 62 63UPDATE t7 SET b=22 WHERE a=3; 64 65INSERT INTO t8 VALUES (23,24); 66INSERT INTO t8 VALUES (24,24); 67 68UPDATE t7 SET b=25 WHERE a=3; 69 70INSERT INTO t8 VALUES (26,26); 71 72UPDATE t7 SET b=27 WHERE a=3; 73 74BEGIN; 75INSERT INTO t8 VALUES (28,28); 76INSERT INTO t8 VALUES (29,28), (30,28); 77INSERT INTO t8 VALUES (31,28); 78INSERT INTO t8 VALUES (32,28); 79INSERT INTO t8 VALUES (33,28); 80INSERT INTO t8 VALUES (34,28); 81INSERT INTO t8 VALUES (35,28); 82INSERT INTO t8 VALUES (36,28); 83INSERT INTO t8 VALUES (37,28); 84INSERT INTO t8 VALUES (38,28); 85INSERT INTO t8 VALUES (39,28); 86INSERT INTO t8 VALUES (40,28); 87INSERT INTO t8 VALUES (41,28); 88INSERT INTO t8 VALUES (42,28); 89COMMIT; 90 91 92SET @commit_id=43; 93INSERT INTO t8 VALUES (43,43); 94INSERT INTO t8 VALUES (44,44); 95 96UPDATE t7 SET b=45 WHERE a=3; 97 98INSERT INTO t8 VALUES (46,46); 99INSERT INTO t8 VALUES (47,47); 100 101UPDATE t7 SET b=48 WHERE a=3; 102 103INSERT INTO t8 VALUES (49,49); 104INSERT INTO t8 VALUES (50,50); 105 106 107SET @commit_id=44; 108INSERT INTO t8 VALUES (51,51); 109INSERT INTO t8 VALUES (52,52); 110 111UPDATE t7 SET b=53 WHERE a=3; 112 113INSERT INTO t8 VALUES (54,54); 114INSERT INTO t8 VALUES (55,55); 115 116UPDATE t7 SET b=56 WHERE a=3; 117 118INSERT INTO t8 VALUES (57,57); 119 120UPDATE t7 SET b=58 WHERE a=3; 121 122INSERT INTO t8 VALUES (58,58); 123INSERT INTO t8 VALUES (59,59); 124INSERT INTO t8 VALUES (60,60); 125INSERT INTO t8 VALUES (61,61); 126 127UPDATE t7 SET b=62 WHERE a=3; 128 129INSERT INTO t8 VALUES (63,63); 130INSERT INTO t8 VALUES (64,64); 131INSERT INTO t8 VALUES (65,65); 132INSERT INTO t8 VALUES (66,66); 133 134UPDATE t7 SET b=67 WHERE a=3; 135 136INSERT INTO t8 VALUES (68,68); 137 138UPDATE t7 SET b=69 WHERE a=3; 139UPDATE t7 SET b=70 WHERE a=3; 140UPDATE t7 SET b=71 WHERE a=3; 141 142INSERT INTO t8 VALUES (72,72); 143 144UPDATE t7 SET b=73 WHERE a=3; 145UPDATE t7 SET b=74 WHERE a=3; 146UPDATE t7 SET b=75 WHERE a=3; 147UPDATE t7 SET b=76 WHERE a=3; 148 149INSERT INTO t8 VALUES (77,77); 150 151UPDATE t7 SET b=78 WHERE a=3; 152 153INSERT INTO t8 VALUES (79,79); 154 155UPDATE t7 SET b=80 WHERE a=3; 156 157INSERT INTO t8 VALUES (81,81); 158 159UPDATE t7 SET b=82 WHERE a=3; 160 161INSERT INTO t8 VALUES (83,83); 162 163UPDATE t7 SET b=84 WHERE a=3; 164 165 166SET @commit_id=45; 167INSERT INTO t8 VALUES (85,85); 168UPDATE t7 SET b=86 WHERE a=3; 169INSERT INTO t8 VALUES (87,87); 170 171 172SET @commit_id=46; 173INSERT INTO t8 VALUES (88,88); 174INSERT INTO t8 VALUES (89,89); 175INSERT INTO t8 VALUES (90,90); 176 177SET SESSION debug_dbug=@old_dbug; 178 179INSERT INTO t8 VALUES (91,91); 180INSERT INTO t8 VALUES (92,92); 181INSERT INTO t8 VALUES (93,93); 182INSERT INTO t8 VALUES (94,94); 183INSERT INTO t8 VALUES (95,95); 184INSERT INTO t8 VALUES (96,96); 185INSERT INTO t8 VALUES (97,97); 186INSERT INTO t8 VALUES (98,98); 187INSERT INTO t8 VALUES (99,99); 188 189 190SELECT * FROM t7 ORDER BY a; 191SELECT * FROM t8 ORDER BY a; 192--source include/save_master_gtid.inc 193 194 195--connection server_2 196--source include/start_slave.inc 197--source include/sync_with_master_gtid.inc 198SELECT * FROM t7 ORDER BY a; 199SELECT * FROM t8 ORDER BY a; 200 201--echo *** MDEV-8302: Duplicate key with parallel replication *** 202 203--connection server_2 204--source include/stop_slave.inc 205/* Inject a small sleep which makes the race easier to hit. */ 206SET @old_dbug=@@GLOBAL.debug_dbug; 207SET GLOBAL debug_dbug="+d,inject_mdev8302"; 208 209 210--connection server_1 211INSERT INTO t7 VALUES (100,1), (101,2), (102,3), (103,4), (104,5); 212 213# Artificially create a bunch of group commits with conflicting transactions. 214# The bug happened when T1 and T2 was in one group commit, and T3 was in the 215# following group commit. T2 is a DELETE of a row with same primary key as a 216# row that T3 inserts. T1 and T2 can conflict, causing T2 to be deadlock 217# killed after starting to commit. The bug was that T2 could roll back before 218# doing unmark_start_commit(); this could allow T3 to run before the retry 219# of T2, causing duplicate key violation. 220 221SET @old_dbug= @@SESSION.debug_dbug; 222SET @commit_id= 20000; 223SET SESSION debug_dbug="+d,binlog_force_commit_id"; 224 225--let $n = 100 226--disable_query_log 227while ($n) 228{ 229 eval UPDATE t7 SET b=b+1 WHERE a=100+($n MOD 5); 230 eval DELETE FROM t7 WHERE a=100+($n MOD 5); 231 232 SET @commit_id = @commit_id + 1; 233 eval INSERT INTO t7 VALUES (100+($n MOD 5), $n); 234 SET @commit_id = @commit_id + 1; 235 dec $n; 236} 237--enable_query_log 238SET SESSION debug_dbug=@old_dbug; 239 240 241SELECT * FROM t7 ORDER BY a; 242--source include/save_master_gtid.inc 243 244 245--connection server_2 246--source include/start_slave.inc 247--source include/sync_with_master_gtid.inc 248SELECT * FROM t7 ORDER BY a; 249 250--source include/stop_slave.inc 251SET GLOBAL debug_dbug=@old_dbug; 252 253# Clean up. 254--source include/start_slave.inc 255SET DEBUG_SYNC= 'RESET'; 256 257--connection server_1 258DROP TABLE t7,t8; 259SET DEBUG_SYNC= 'RESET'; 260 261--source include/rpl_end.inc 262