Debian Codegraph @

mysql-test/suite/innodb/t/iodku.test

raw
1 --echo #
2 --echo # Bug #28799261 IODKU UNNECESSARILY GAP-LOCKS SECONDARY INDEXES
3 --echo # AFTER DUPLICATE IS ALREADY FOUND
4 --echo #
5
6 --source include/count_sessions.inc
7
8 #Test 1
9 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
10 SHOW CREATE TABLE t1;
11
12 --echo # Scenario 1: Serialized transactions (trx1, trx2)
13 INSERT INTO t1 VALUES (1, 1, 1);
14 SELECT * FROM t1;
15
16 --echo # Transaction trx1
17 START TRANSACTION;
18 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
19 COMMIT;
20
21 --echo # Transaction trx2
22 START TRANSACTION;
23 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
24 INSERT INTO t1 VALUES (2, 3, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
25 COMMIT;
26
27 --echo # Result:
28 SELECT * FROM t1;
29
30 --echo # Cleanup
31 DROP TABLE t1;
32
33 #Test 2
34
35 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
36 SHOW CREATE TABLE t1;
37
38 --echo # Scenario 2: Serialized transactions (trx2, trx1)
39 INSERT INTO t1 VALUES (1, 1, 1);
40 SELECT * FROM t1;
41
42 --echo # Transaction trx2
43 START TRANSACTION;
44 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
45 INSERT INTO t1 VALUES (2, 3, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
46 COMMIT;
47
48 --echo # Transaction trx1
49 START TRANSACTION;
50 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
51 COMMIT;
52
53 --echo # Result:
54 SELECT * FROM t1;
55
56 --echo # Cleanup
57 DROP TABLE t1;
58
59 --echo #
60 --echo # Bug #11758237 INSERT ON DUPLICATE KEY UPDATE SOMETIMES
61 --echo # WRITES BINLOG POSITION INCORRECT
62 --echo #
63
64 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
65 SHOW CREATE TABLE t1;
66
67 # Following two lines are added because of Bug#28523025
68 # We don't really care about tt's content, nor about the exact body
69 # of the TRIGGER. What we care is that the mere fact of processing the
70 # TRIGGER before each inserted row, can impact the state of flags set
71 # for the transaction and table t1.
72 CREATE TABLE tt (i INT) ENGINE = InnoDB;
73 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
74 FOR EACH ROW
75 INSERT INTO tt SELECT a as i FROM t1 WHERE a=0;
76
77 --echo # Scenario 3: Interleaved transactions (trx1 conflicts on index b)
78 INSERT INTO t1 VALUES (0,0,0), (1, 1, 1);
79 SELECT * FROM t1;
80
81 --echo # Connection default:
82
83 --echo # Transaction trx1:
84 START TRANSACTION;
85 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
86 SELECT * FROM t1;
87
88 --echo # Connection other:
89 --connect (other,localhost,root,,test,,)
90 --send
91 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
92
93 --echo # Connection default:
94 --connection default
95 let $wait_condition=
96 SELECT COUNT(*) = 1 FROM information_schema.innodb_trx
97 WHERE trx_query =
98 'INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c)' AND
99 trx_operation_state = 'inserting' AND
100 trx_state = 'LOCK WAIT';
101
102 --source include/wait_condition.inc
103
104 INSERT INTO t1 VALUES (2, 3, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
105 SELECT * FROM t1;
106 COMMIT;
107
108 --echo # Connection other:
109 --connection other
110 reap;
111
112 --echo # Connection default:
113 connection default;
114
115 --echo # Result:
116 SELECT * FROM t1;
117
118 --echo # Cleanup
119 DROP TABLE t1;
120 DROP TABLE tt;
121
122 #Test 4
123 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
124 SHOW CREATE TABLE t1;
125
126 --echo # Scenario 4: Serialized transactions (trx1, trx2)
127 INSERT INTO t1 VALUES (1, 1, 1);
128 SELECT * FROM t1;
129
130 --echo # Transaction trx1
131 START TRANSACTION;
132 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
133 COMMIT;
134
135 --echo # Transaction trx2
136 START TRANSACTION;
137 INSERT INTO t1 VALUES (1, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
138 INSERT INTO t1 VALUES (3, 2, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
139 COMMIT;
140
141 --echo # Result:
142 SELECT * FROM t1;
143
144 --echo # Cleanup
145 DROP TABLE t1;
146
147 #Test 5
148 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
149 SHOW CREATE TABLE t1;
150
151 --echo # Scenario 5: Serialized transactions (trx2, trx1)
152 INSERT INTO t1 VALUES (1, 1, 1);
153 SELECT * FROM t1;
154
155 --echo # Transaction trx2
156 START TRANSACTION;
157 INSERT INTO t1 VALUES (1, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
158 INSERT INTO t1 VALUES (3, 2, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
159 COMMIT;
160
161 --echo # Transaction trx1
162 START TRANSACTION;
163 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
164 COMMIT;
165
166 --echo # Result:
167 SELECT * FROM t1;
168
169 --echo # Cleanup
170 DROP TABLE t1;
171
172 #Test 6
173 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
174 SHOW CREATE TABLE t1;
175
176 --echo # Scenario 6: Interleaved transactions (trx1 conflicts on index a)
177 INSERT INTO t1 VALUES (1, 1, 1);
178 SELECT * FROM t1;
179
180 --echo # Connection default:
181
182 --echo # Transaction trx1:
183 START TRANSACTION;
184 INSERT INTO t1 VALUES (1, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
185 SELECT * FROM t1;
186
187 --echo # Connection other:
188 connection other;
189 START TRANSACTION;
190 SET innodb_lock_wait_timeout = 1;
191 --error ER_LOCK_WAIT_TIMEOUT
192 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
193 COMMIT;
194
195 --echo # Connection default:
196 --connection default
197 INSERT INTO t1 VALUES (3, 2, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
198 COMMIT;
199
200 --echo # Result:
201 SELECT * FROM t1;
202
203 --echo # Cleanup
204 DROP TABLE t1;
205
206 #test 7
207 CREATE TABLE t1(f1 INT PRIMARY KEY,
208 f2 INT, f3 INT, UNIQUE KEY(f2))ENGINE=INNODB;
209
210 SHOW CREATE TABLE t1;
211
212 INSERT INTO t1(f1, f2, f3) VALUES(1, 10, 100);
213 SELECT * FROM t1;
214
215 --echo # Connection other:
216 connection other;
217 START TRANSACTION;
218 INSERT INTO t1 VALUES(2, 10, 200) ON DUPLICATE KEY UPDATE f3 = 120;
219
220 --echo # Connection default
221 connection default;
222 START TRANSACTION;
223 SET innodb_lock_wait_timeout = 1;
224 --error ER_LOCK_WAIT_TIMEOUT
225 INSERT INTO t1 VALUES(2, 20, 300) ON DUPLICATE KEY UPDATE f3 = 500;
226 COMMIT;
227
228 --echo # Connection other:
229 --connection other
230 COMMIT;
231
232 --echo # Connection default
233 connection default;
234
235 --echo # Verify Results:
236 SELECT * FROM t1;
237
238 DROP TABLE t1;
239
240 disconnect other;
241
242 #Test 8
243 #Test added to cover the undo update path
244 CREATE TABLE t1(f1 INT AUTO_INCREMENT PRIMARY KEY, f2 INT, f3 INT,
245 UNIQUE KEY(f2),UNIQUE KEY(f3))ENGINE=INNODB;
246 INSERT INTO t1(f2,f3) VALUES(10, 100);
247 INSERT INTO t1(f2,f3) VALUES(20, 200);
248
249 --error ER_DUP_ENTRY
250 UPDATE t1 SET f2=30,f3=100 WHERE f2 = 20;
251 SELECT * FROM t1;
252 COMMIT;
253 DROP TABLE t1;
254
255 --source include/wait_until_count_sessions.inc