Debian Codegraph @

mysql-test/suite/innodb/r/innodb_replace.result

raw
1 #
2 #Bug#11759688 52020: InnoDB can still deadlock
3 #on just INSERT...ON DUPLICATE KEY
4 #a.k.a. Bug#7975 deadlock without any locking, simple select and update
5 #
6 CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
7 INSERT INTO t1 VALUES(3,1);
8 BEGIN;
9 SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1';
10 INSERT INTO t1 VALUES(3,2);
11 SET DEBUG_SYNC='now WAIT_FOR insert1';
12 SELECT * FROM t1 LOCK IN SHARE MODE;
13 a b
14 3 1
15 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
16 SELECT * FROM t1 FOR UPDATE;
17 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
18 SET DEBUG_SYNC='now SIGNAL select1';
19 ERROR 23000: Duplicate entry '3' for key 't1.PRIMARY'
20 INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10;
21 COMMIT;
22 a b
23 3 11
24 SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2';
25 REPLACE INTO t1 VALUES(3,4);
26 SET DEBUG_SYNC='now WAIT_FOR insert2';
27 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
28 SELECT * FROM t1;
29 a b
30 3 11
31 SELECT * FROM t1 LOCK IN SHARE MODE;
32 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
33 SET DEBUG_SYNC='now SIGNAL select2';
34 SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3';
35 INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20;
36 a b
37 3 4
38 SET DEBUG_SYNC='now WAIT_FOR insert3';
39 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
40 SELECT b FROM t1 LOCK IN SHARE MODE;
41 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
42 SET DEBUG_SYNC='now SIGNAL select3';
43 b
44 24
45 SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4';
46 LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
47 SET DEBUG_SYNC='now WAIT_FOR insert4';
48 SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE;
49 b
50 24
51 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
52 SELECT b FROM t1 WHERE a=3 FOR UPDATE;
53 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
54 SET DEBUG_SYNC='now SIGNAL select4';
55 b
56 24
57 ERROR 23000: Duplicate entry '3' for key 't1.PRIMARY'
58 SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5';
59 LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
60 SET DEBUG_SYNC='now WAIT_FOR insert5';
61 SELECT * FROM t1;
62 a b
63 3 24
64 SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE;
65 a b
66 3 24
67 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
68 SELECT * FROM t1 WHERE a=3 FOR UPDATE;
69 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
70 SET DEBUG_SYNC='now SIGNAL select5';
71 Warnings:
72 Warning 1062 Duplicate entry '3' for key 't1.PRIMARY'
73 a b
74 3 24
75 SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6';
76 LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b);
77 SET DEBUG_SYNC='now WAIT_FOR insert6';
78 SELECT * FROM t1;
79 a b
80 1 2
81 3 24
82 5 6
83 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL default_will_wait';
84 SELECT a,b FROM t1 LOCK IN SHARE MODE;
85 SET DEBUG_SYNC='now WAIT_FOR default_will_wait';
86 SET DEBUG_SYNC='now SIGNAL select6';
87 a b
88 1 2
89 3 4
90 5 6
91 SET DEBUG_SYNC='RESET';
92 DROP TABLE t1;
93 START TRANSACTION;
94 CREATE TEMPORARY TABLE t1 (a INT, b CHAR(10), d VARCHAR(100), c INT, UNIQUE INDEX(a), UNIQUE INDEX(b), UNIQUE INDEX(d)) ENGINE = InnoDB;
95 INSERT INTO t1 VALUES(2, 'abcde', 'qwerty', 200);
96 REPLACE INTO t1 VALUES(2, 'QWERTY', 'ZXCVBB', 300);
97 SELECT * FROM t1;
98 a b d c
99 2 QWERTY ZXCVBB 300
100 COMMIT;
101 DROP TABLE t1;
102 #
103 # Bug#22884631 FAILING ASSERTION: TRX->DUPLICATES == 0
104 #
105 SELECT @@table_open_cache_instances;
106 @@table_open_cache_instances
107 1
108 CREATE TABLE t1(a INT);
109 CREATE TABLE t2(a INT);
110 INSERT INTO t1 VALUES(1);
111 INSERT INTO t2 VALUES(2);
112 # Connection con1
113 INSERT INTO t1 VALUES(3);
114 SET DEBUG_SYNC = 'RESET';
115 SET DEBUG_SYNC = 'ib_after_row_insert WAIT_FOR go_ahead';
116 REPLACE INTO t2 VALUES(3);
117 # Connection con2
118 INSERT INTO t1 VALUES(70, 80);
119 ERROR 21S01: Column count doesn't match value count at row 1
120 SET DEBUG_SYNC= 'now SIGNAL go_ahead';
121 # Connection con1
122 DROP TABLE t1;
123 DROP TABLE t2;
124 SET DEBUG_SYNC= 'RESET';