1# 2# Bug#25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK 3# 4CREATE TABLE t1(f1 int primary key, 5f2 int, f3 int, unique key(f2))engine=innodb; 6SHOW CREATE TABLE t1; 7Table Create Table 8t1 CREATE TABLE `t1` ( 9 `f1` int(11) NOT NULL, 10 `f2` int(11) DEFAULT NULL, 11 `f3` int(11) DEFAULT NULL, 12 PRIMARY KEY (`f1`), 13 UNIQUE KEY `f2` (`f2`) 14) ENGINE=InnoDB DEFAULT CHARSET=latin1 15INSERT INTO t1(f1, f2, f3) VALUES(1, 10, 100); 16# Connection default 17BEGIN; 18INSERT INTO t1 VALUES(2, 20, 300) ON DUPLICATE KEY UPDATE f3 = 500; 19# Connection other: 20BEGIN; 21SET DEBUG_SYNC = 'ib_after_row_insert_step SIGNAL default_commit'; 22INSERT INTO t1 VALUES(2, 10, 200) ON DUPLICATE KEY UPDATE f3 = 120; 23# Connection default 24SET DEBUG_SYNC = 'now WAIT_FOR default_commit'; 25COMMIT; 26# Connection other 27COMMIT; 28# Verify Results: 29SELECT * FROM t1; 30f1 f2 f3 311 10 100 322 20 120 33SET DEBUG_SYNC ='RESET'; 34DROP TABLE t1; 35# 36# Bug #29195848 ASSERTION "!OTHER_LOCK" IN LOCK_REC_ADD_TO_QUEUE 37# 38SET GLOBAL innodb_purge_stop_now = ON; 39CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT, UNIQUE KEY(b)); 40INSERT INTO t1 VALUES(2, 300); 41DELETE FROM t1; 42INSERT INTO t1 VALUES(3, 300); 43SELECT * FROM t1; 44id b 453 300 46SET DEBUG_SYNC='ib_after_row_insert_step SIGNAL after_insert WAIT_FOR 47rollback'; 48INSERT INTO t1 VALUES(2, 300);; 49SET DEBUG_SYNC='now WAIT_FOR after_insert'; 50START TRANSACTION; 51INSERT INTO t1 VALUES(1, 300); 52ERROR 23000: Duplicate entry '300' for key 'b' 53SET GLOBAL innodb_purge_run_now=ON; 54SET DEBUG_SYNC='now SIGNAL rollback'; 55ERROR 23000: Duplicate entry '300' for key 'b' 56COMMIT; 57DROP TABLE t1; 58SET GLOBAL innodb_purge_stop_now = ON; 59CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE KEY(b)); 60INSERT INTO t1 VALUES(1,10); 61DELETE FROM t1; 62INSERT INTO t1 VALUES(2,10); 63SET DEBUG_SYNC='ib_undo_mod_before_remove_clust SIGNAL during_rollback 64WAIT_FOR rollback'; 65INSERT INTO t1 VALUES(1, 10) ;; 66SET DEBUG_SYNC='now WAIT_FOR during_rollback'; 67SELECT * FROM t1 WHERE a = 1 LOCK IN SHARE MODE; 68ERROR HY000: Lock wait timeout exceeded; try restarting transaction 69SET GLOBAL innodb_purge_run_now=ON; 70SET DEBUG_SYNC='now SIGNAL rollback'; 71ERROR 23000: Duplicate entry '10' for key 'b' 72SELECT * FROM t1; 73a b 742 10 75DROP TABLE t1; 76# 77# Bug #29718243 MYQL SERVER CRASHING 78# 79CREATE TEMPORARY TABLE tmpTest(tmpField INT , UNIQUE KEY uq_tmpField (tmpField)); 80CREATE TEMPORARY TABLE tmpTest1(tmpField INT , UNIQUE KEY uq_tmpField (tmpField)); 81CREATE FUNCTION ZZtest() RETURNS int(11) 82BEGIN 83DECLARE l_total INTEGER; 84SET l_total = 0; 85INSERT INTO tmpTest SET tmpField = 40; 86INSERT IGNORE INTO tmpTest SET tmpField = 40; 87INSERT IGNORE INTO tmpTest1 SET tmpField = 40; 88DROP TEMPORARY TABLE IF EXISTS tmpTest; 89DROP TEMPORARY TABLE IF EXISTS tmpTest1; 90RETURN l_total; 91END| 92SELECT ZZtest() AS test; 93test 940 95DROP FUNCTION ZZtest; 96