1source include/have_innodb.inc; 2source include/have_debug.inc; 3source include/have_debug_sync.inc; 4 5SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 6 7connect (con1, localhost, root,,); 8SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 9 10connection default; 11 12let $conn0_id= `SELECT CONNECTION_ID()`; 13 14CREATE TABLE t1(c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) NOT NULL, c3 VARCHAR(10) NOT NULL); 15INSERT INTO t1(c1, c2, c3) VALUES('A1','B1','IT1'), ('A2','B2','IT1'), ('A3','B3','IT1'), ('A4','B4','IT1'), ('A5','B5','IT1'), ('A6','B6','IT1'), ('A7','B7','IT1'); 16 17CREATE TABLE t2(c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) NOT NULL, c3 VARCHAR(10) NOT NULL); 18INSERT INTO t2(c1, c2, c3) VALUES ('A3','B3','IT2'), ('A2','B2','IT2'), ('A4','B4','IT2'), ('A5','B5','II2'); 19 20CREATE TABLE result(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10), 21c3 VARCHAR(10), update_count INT DEFAULT 0, UNIQUE KEY uniq_idx (c1,c2), PRIMARY KEY (id)) ENGINE = innodb; 22 23# Insert one row from 't1' into the 'result' table and wait on a debug sync 24# point. The next insert statement from an session 2 inserts values that would 25# lead to unique key clash, when this insert resumes. 26# The subsequent inserts of this statement(after resume) will fail because of a 27# clash with the unique index, and are expected to update the row which clashes 28# with the unique key. 29# Without the fix for bug#30194841 a stale auto increment value, would cause a 30# collision with existing auto increment column value and ends up updating that 31# colliding row, instead of the row colliding with the unique index. 32SET DEBUG_SYNC = "ha_write_row_end WAIT_FOR flushed EXECUTE 1"; 33send INSERT INTO result(c1, c2, c3) SELECT * FROM t1 ON DUPLICATE KEY UPDATE c2=t1.c2, c3='UT1', update_count=update_count+1; 34 35# While session 1 is waiting (after one insert), insert rows that will cause a clash 36# with the inserts of session 1 on the unique key. 37connection con1; 38 39# Wait for the session 1 to hit the debug sync point. 40let $wait_condition=SELECT 1 FROM information_schema.processlist WHERE id = $conn0_id AND state LIKE '%ha_write_row_end%'; 41--source include/wait_condition.inc 42 43INSERT INTO result(c1, c2, c3) SELECT * FROM t2 ON DUPLICATE KEY UPDATE c2=t2.c2, c3='UT2', update_count=update_count+1; 44 45# Signal to resume the insert statement in session 1 46SET DEBUG_SYNC = "now SIGNAL flushed"; 47connection default; 48reap; 49SELECT * FROM result; 50 51DROP TABLE t1; 52DROP TABLE t2; 53DROP TABLE result; 54SET DEBUG_SYNC = "RESET"; 55