Debian Codegraph @

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

raw
1 #
2 # Bug #28799261 IODKU UNNECESSARILY GAP-LOCKS SECONDARY INDEXES
3 # AFTER DUPLICATE IS ALREADY FOUND
4 #
5 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
6 SHOW CREATE TABLE t1;
7 Table Create Table
8 t1 CREATE TABLE `t1` (
9 `a` int DEFAULT NULL,
10 `b` int DEFAULT NULL,
11 `c` int DEFAULT NULL,
12 UNIQUE KEY `a` (`a`),
13 UNIQUE KEY `b` (`b`)
14 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
15 # Scenario 1: Serialized transactions (trx1, trx2)
16 INSERT INTO t1 VALUES (1, 1, 1);
17 SELECT * FROM t1;
18 a b c
19 1 1 1
20 # Transaction trx1
21 START TRANSACTION;
22 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
23 Warnings:
24 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
25 COMMIT;
26 # Transaction trx2
27 START TRANSACTION;
28 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
29 Warnings:
30 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
31 INSERT INTO t1 VALUES (2, 3, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
32 Warnings:
33 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
34 COMMIT;
35 # Result:
36 SELECT * FROM t1;
37 a b c
38 1 1 1
39 2 2 4
40 # Cleanup
41 DROP TABLE t1;
42 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
43 SHOW CREATE TABLE t1;
44 Table Create Table
45 t1 CREATE TABLE `t1` (
46 `a` int DEFAULT NULL,
47 `b` int DEFAULT NULL,
48 `c` int DEFAULT NULL,
49 UNIQUE KEY `a` (`a`),
50 UNIQUE KEY `b` (`b`)
51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
52 # Scenario 2: Serialized transactions (trx2, trx1)
53 INSERT INTO t1 VALUES (1, 1, 1);
54 SELECT * FROM t1;
55 a b c
56 1 1 1
57 # Transaction trx2
58 START TRANSACTION;
59 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
60 Warnings:
61 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
62 INSERT INTO t1 VALUES (2, 3, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
63 Warnings:
64 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
65 COMMIT;
66 # Transaction trx1
67 START TRANSACTION;
68 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
69 Warnings:
70 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
71 COMMIT;
72 # Result:
73 SELECT * FROM t1;
74 a b c
75 1 1 2
76 2 3 3
77 # Cleanup
78 DROP TABLE t1;
79 #
80 # Bug #11758237 INSERT ON DUPLICATE KEY UPDATE SOMETIMES
81 # WRITES BINLOG POSITION INCORRECT
82 #
83 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
84 SHOW CREATE TABLE t1;
85 Table Create Table
86 t1 CREATE TABLE `t1` (
87 `a` int DEFAULT NULL,
88 `b` int DEFAULT NULL,
89 `c` int DEFAULT NULL,
90 UNIQUE KEY `a` (`a`),
91 UNIQUE KEY `b` (`b`)
92 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
93 CREATE TABLE tt (i INT) ENGINE = InnoDB;
94 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
95 FOR EACH ROW
96 INSERT INTO tt SELECT a as i FROM t1 WHERE a=0;
97 # Scenario 3: Interleaved transactions (trx1 conflicts on index b)
98 INSERT INTO t1 VALUES (0,0,0), (1, 1, 1);
99 SELECT * FROM t1;
100 a b c
101 0 0 0
102 1 1 1
103 # Connection default:
104 # Transaction trx1:
105 START TRANSACTION;
106 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
107 Warnings:
108 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
109 SELECT * FROM t1;
110 a b c
111 0 0 0
112 1 1 2
113 # Connection other:
114 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
115 # Connection default:
116 INSERT INTO t1 VALUES (2, 3, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
117 Warnings:
118 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
119 SELECT * FROM t1;
120 a b c
121 0 0 0
122 1 1 2
123 2 3 4
124 COMMIT;
125 # Connection other:
126 Warnings:
127 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
128 # Connection default:
129 # Result:
130 SELECT * FROM t1;
131 a b c
132 0 0 0
133 1 1 2
134 2 3 3
135 # Cleanup
136 DROP TABLE t1;
137 DROP TABLE tt;
138 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
139 SHOW CREATE TABLE t1;
140 Table Create Table
141 t1 CREATE TABLE `t1` (
142 `a` int DEFAULT NULL,
143 `b` int DEFAULT NULL,
144 `c` int DEFAULT NULL,
145 UNIQUE KEY `a` (`a`),
146 UNIQUE KEY `b` (`b`)
147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
148 # Scenario 4: Serialized transactions (trx1, trx2)
149 INSERT INTO t1 VALUES (1, 1, 1);
150 SELECT * FROM t1;
151 a b c
152 1 1 1
153 # Transaction trx1
154 START TRANSACTION;
155 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
156 Warnings:
157 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
158 COMMIT;
159 # Transaction trx2
160 START TRANSACTION;
161 INSERT INTO t1 VALUES (1, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
162 Warnings:
163 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
164 INSERT INTO t1 VALUES (3, 2, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
165 Warnings:
166 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
167 COMMIT;
168 # Result:
169 SELECT * FROM t1;
170 a b c
171 1 1 2
172 2 2 4
173 # Cleanup
174 DROP TABLE t1;
175 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
176 SHOW CREATE TABLE t1;
177 Table Create Table
178 t1 CREATE TABLE `t1` (
179 `a` int DEFAULT NULL,
180 `b` int DEFAULT NULL,
181 `c` int DEFAULT NULL,
182 UNIQUE KEY `a` (`a`),
183 UNIQUE KEY `b` (`b`)
184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
185 # Scenario 5: Serialized transactions (trx2, trx1)
186 INSERT INTO t1 VALUES (1, 1, 1);
187 SELECT * FROM t1;
188 a b c
189 1 1 1
190 # Transaction trx2
191 START TRANSACTION;
192 INSERT INTO t1 VALUES (1, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
193 Warnings:
194 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
195 INSERT INTO t1 VALUES (3, 2, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
196 Warnings:
197 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
198 COMMIT;
199 # Transaction trx1
200 START TRANSACTION;
201 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
202 Warnings:
203 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
204 COMMIT;
205 # Result:
206 SELECT * FROM t1;
207 a b c
208 1 1 2
209 3 2 3
210 # Cleanup
211 DROP TABLE t1;
212 CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY, c INT) ENGINE = InnoDB;
213 SHOW CREATE TABLE t1;
214 Table Create Table
215 t1 CREATE TABLE `t1` (
216 `a` int DEFAULT NULL,
217 `b` int DEFAULT NULL,
218 `c` int DEFAULT NULL,
219 UNIQUE KEY `a` (`a`),
220 UNIQUE KEY `b` (`b`)
221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
222 # Scenario 6: Interleaved transactions (trx1 conflicts on index a)
223 INSERT INTO t1 VALUES (1, 1, 1);
224 SELECT * FROM t1;
225 a b c
226 1 1 1
227 # Connection default:
228 # Transaction trx1:
229 START TRANSACTION;
230 INSERT INTO t1 VALUES (1, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
231 Warnings:
232 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
233 SELECT * FROM t1;
234 a b c
235 1 1 2
236 # Connection other:
237 START TRANSACTION;
238 SET innodb_lock_wait_timeout = 1;
239 INSERT INTO t1 VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
240 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
241 COMMIT;
242 # Connection default:
243 INSERT INTO t1 VALUES (3, 2, 4) ON DUPLICATE KEY UPDATE c=VALUES(c);
244 Warnings:
245 Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
246 COMMIT;
247 # Result:
248 SELECT * FROM t1;
249 a b c
250 1 1 2
251 3 2 4
252 # Cleanup
253 DROP TABLE t1;
254 CREATE TABLE t1(f1 INT PRIMARY KEY,
255 f2 INT, f3 INT, UNIQUE KEY(f2))ENGINE=INNODB;
256 SHOW CREATE TABLE t1;
257 Table Create Table
258 t1 CREATE TABLE `t1` (
259 `f1` int NOT NULL,
260 `f2` int DEFAULT NULL,
261 `f3` int DEFAULT NULL,
262 PRIMARY KEY (`f1`),
263 UNIQUE KEY `f2` (`f2`)
264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
265 INSERT INTO t1(f1, f2, f3) VALUES(1, 10, 100);
266 SELECT * FROM t1;
267 f1 f2 f3
268 1 10 100
269 # Connection other:
270 START TRANSACTION;
271 INSERT INTO t1 VALUES(2, 10, 200) ON DUPLICATE KEY UPDATE f3 = 120;
272 # Connection default
273 START TRANSACTION;
274 SET innodb_lock_wait_timeout = 1;
275 INSERT INTO t1 VALUES(2, 20, 300) ON DUPLICATE KEY UPDATE f3 = 500;
276 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
277 COMMIT;
278 # Connection other:
279 COMMIT;
280 # Connection default
281 # Verify Results:
282 SELECT * FROM t1;
283 f1 f2 f3
284 1 10 120
285 DROP TABLE t1;
286 CREATE TABLE t1(f1 INT AUTO_INCREMENT PRIMARY KEY, f2 INT, f3 INT,
287 UNIQUE KEY(f2),UNIQUE KEY(f3))ENGINE=INNODB;
288 INSERT INTO t1(f2,f3) VALUES(10, 100);
289 INSERT INTO t1(f2,f3) VALUES(20, 200);
290 UPDATE t1 SET f2=30,f3=100 WHERE f2 = 20;
291 ERROR 23000: Duplicate entry '100' for key 't1.f3'
292 SELECT * FROM t1;
293 f1 f2 f3
294 1 10 100
295 2 20 200
296 COMMIT;
297 DROP TABLE t1;