Debian Codegraph @

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

raw
1
2 #Note:- WL6742 has been removed from 5.7 (Bug 23046302),
3 # but we are keeping this test since it tests
4 # count(*) correctness for various isolation
5 # levels.
6
7 --echo #
8 --echo # WL#6742 - Test the interaction of multiple transactions using
9 --echo # different isolation levels to make sure that the value returned
10 --echo # by count(*) always reflects the correct view of the table according
11 --echo # to the transaction's selected isolation level.
12 --echo #
13
14 --source include/have_innodb.inc
15
16 --disable_query_log
17 let $MYSQLD_DATADIR= `select @@datadir`;
18 let $initial_timeout=`select @@innodb_lock_wait_timeout`;
19 --enable_query_log
20
21 --echo #
22 --echo # Traverse various indexes to get the right counts.
23 --echo # This especially tests count(*) which is pushed down to InnoDB in WL#6742.
24 --echo #
25 CREATE TABLE t1 (
26 c1 INT AUTO_INCREMENT PRIMARY KEY,
27 c2 INT,
28 c3 INT,
29 c4 INT,
30 INDEX k2(c2)
31 ) Engine=InnoDB;
32 let $1=10;
33 while ($1 > 0) {
34 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
35 dec $1;
36 }
37
38 CREATE TABLE t2 LIKE t1;
39 --enable_info
40 INSERT INTO t2 (SELECT * FROM t1);
41 --disable_info
42
43 --source suite/innodb/include/innodb_isolation_selects.inc
44
45 --echo #
46 --echo # Do some DML in the default connection and leave the transaction pending.
47 --echo #
48 SET innodb_snapshot_isolation=OFF;
49 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
50 BEGIN;
51 --enable_info
52 UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1;
53 DELETE FROM t1 WHERE c1 = 6;
54 --disable_info
55 --source suite/innodb/include/innodb_isolation_selects.inc
56
57 --echo #
58 --echo # Start transactions of Repeatable Read, Read Committed, and Read Uncommitted
59 --echo #
60 --echo # Connection 1 REPEATABLE READ
61 --echo #
62 connect (con1,localhost,root,,);
63 SET innodb_snapshot_isolation=OFF;
64 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
65 BEGIN;
66 --source suite/innodb/include/innodb_isolation_selects.inc
67 --enable_info
68 UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2;
69 DELETE FROM t1 WHERE c1 = 7;
70 INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1);
71 --disable_info
72 --source suite/innodb/include/innodb_isolation_selects.inc
73
74 --echo #
75 --echo # Test a lock wait timeout during COUNT(*)
76 --echo #
77 SET innodb_lock_wait_timeout = 1;
78 --error ER_LOCK_WAIT_TIMEOUT
79 SELECT COUNT(*) FROM t1 FOR UPDATE;
80
81
82 --echo #
83 --echo # Connection 2 READ COMMITTED
84 --echo #
85 connect (con2,localhost,root,,);
86 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
87 BEGIN;
88 --source suite/innodb/include/innodb_isolation_selects.inc
89 --enable_info
90 UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3;
91 DELETE FROM t1 WHERE c1 = 8;
92 INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1);
93 --disable_info
94 --source suite/innodb/include/innodb_isolation_selects.inc
95
96 --echo #
97 --echo # Connection 3 READ UNCOMMITTED
98 --echo #
99 connect (con3,localhost,root,,);
100 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
101 BEGIN;
102 --source suite/innodb/include/innodb_isolation_selects.inc
103 --enable_info
104 UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4;
105 INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1);
106 DELETE FROM t1 WHERE c1 in(9);
107 --disable_info
108 --source suite/innodb/include/innodb_isolation_selects.inc
109
110 --echo #
111 --echo # Connection default REPEATABLE READ
112 --echo #
113 connection default;
114 --source suite/innodb/include/innodb_isolation_selects.inc
115
116 --echo #
117 --echo # Commit the 3 extra connections
118 --echo #
119 --echo # Connection 1 REPEATABLE READ
120 --echo #
121 connection con1;
122 --source suite/innodb/include/innodb_isolation_selects.inc
123
124 --echo #
125 --echo # Connection 2 READ COMMITTED
126 --echo #
127 connection con2;
128 --source suite/innodb/include/innodb_isolation_selects.inc
129
130 --echo #
131 --echo # Connection 3 READ UNCOMMITTED
132 --echo #
133 connection con3;
134 --source suite/innodb/include/innodb_isolation_selects.inc
135 COMMIT;
136 --source suite/innodb/include/innodb_isolation_selects.inc
137
138 --echo #
139 --echo # Connection 2 READ COMMITTED
140 --echo #
141 connection con2;
142 --source suite/innodb/include/innodb_isolation_selects.inc
143 COMMIT;
144 --source suite/innodb/include/innodb_isolation_selects.inc
145
146 --echo #
147 --echo # Connection 1 REPEATABLE READ
148 --echo #
149 connection con1;
150 --source suite/innodb/include/innodb_isolation_selects.inc
151
152 --echo #
153 --echo # Select the first 5 records FOR UPDATE using count(*) in a subquery.
154 --echo # The second record is still pending so we get a lock timeout.
155 --echo #
156 SET innodb_lock_wait_timeout = 1;
157 --error ER_LOCK_WAIT_TIMEOUT
158 SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE;
159 --error ER_LOCK_WAIT_TIMEOUT
160 SELECT COUNT(*) FROM t1 FOR UPDATE;
161 COMMIT;
162 --source suite/innodb/include/innodb_isolation_selects.inc
163
164 --echo #
165 --echo # Show The EXPLAIN output for these queries;
166 --echo #
167 # column 9 is the row count provided by handler::info(). In InnoDB, this is
168 # a statistical estimate. After the multi-transactional changes above,
169 # Solaris reports 10 rows which is correct, but other OSes report 9.
170 --replace_column 9 #
171 EXPLAIN SELECT * FROM t1;
172 --replace_column 9 #
173 EXPLAIN SELECT COUNT(*) FROM t1;
174 --replace_column 9 #
175 EXPLAIN SELECT COUNT(c1) FROM t1;
176 --replace_column 9 #
177 EXPLAIN SELECT COUNT(c2) FROM t1;
178 --replace_column 9 #
179 EXPLAIN SELECT COUNT(c3) FROM t1;
180 --replace_column 9 #
181 EXPLAIN SELECT SUM(c1) FROM t1;
182 --replace_column 9 #
183 EXPLAIN SELECT SUM(c2) FROM t1;
184 --replace_column 9 #
185 EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
186 --replace_column 9 #
187 EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
188 --replace_column 9 #
189 EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
190 --replace_column 9 #
191 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
192
193 --echo #
194 --echo # Make all indexes in t2 obsolete to the active repeatable read transaction
195 --echo # in the default connection.
196 --echo #
197 ALTER TABLE t2 row_format=redundant;
198
199 --echo #
200 --echo # Connection default REPEATABLE READ
201 --echo # Do more DML in the default REPEATABLE READ transaction in order to use recently committed records.
202 --echo #
203 connection default;
204 --source suite/innodb/include/innodb_isolation_selects.inc
205 --enable_info
206 UPDATE t1 SET c4 = c2 * 10;
207 --disable_info
208 --source suite/innodb/include/innodb_isolation_selects.inc
209
210 --echo #
211 --echo # Table t2 has been altered to a new row format.
212 --echo # The index should not be useable.
213 --echo #
214 --error ER_TABLE_DEF_CHANGED
215 SELECT COUNT(*) FROM t2;
216 --error ER_TABLE_DEF_CHANGED
217 SELECT * FROM t2;
218
219 COMMIT;
220 SELECT COUNT(*) FROM t2;
221 --source suite/innodb/include/innodb_isolation_selects.inc
222
223 --echo #
224 --echo # Connection 2
225 --echo #
226 connection con2;
227 --source suite/innodb/include/innodb_isolation_selects.inc
228
229 --echo #
230 --echo # Try COUNT(*) on a DISCARDED table.
231 --echo #
232 connection default;
233 CREATE TABLE t4 LIKE t1;
234 INSERT INTO t4 (SELECT * FROM t1);
235 SELECT COUNT(*) FROM t4;
236 ALTER TABLE t4 DISCARD TABLESPACE;
237 --error ER_TABLESPACE_DISCARDED
238 SELECT COUNT(*) FROM t4;
239
240
241 --echo #
242 --echo # Test the interaction of a repeatable read transaction
243 --echo # to changes that happen outside its view.
244 --echo #
245
246 CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
247 INSERT INTO t5(b) VALUES ("inserted by client 1");
248 INSERT INTO t5(b) VALUES ("inserted by client 1");
249 INSERT INTO t5(b) VALUES ("inserted by client 1");
250 INSERT INTO t5(b) VALUES ("inserted by client 1");
251 UPDATE t5 SET aa=a, bb=b;
252 CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
253 INSERT INTO t6(b) VALUES ("inserted by client 1");
254 INSERT INTO t6(b) VALUES ("inserted by client 1");
255 INSERT INTO t6(b) VALUES ("inserted by client 1");
256 INSERT INTO t6(b) VALUES ("inserted by client 1");
257 UPDATE t6 SET aa=a, bb=b;
258 CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
259 INSERT INTO t7(b) VALUES ("inserted by client 1");
260 INSERT INTO t7(b) VALUES ("inserted by client 1");
261 INSERT INTO t7(b) VALUES ("inserted by client 1");
262 INSERT INTO t7(b) VALUES ("inserted by client 1");
263 UPDATE t7 SET aa=a, bb=b;
264 BEGIN;
265 SELECT * FROM t5;
266 SELECT COUNT(*) FROM t5;
267 SELECT * FROM t6;
268 SELECT COUNT(*) FROM t6;
269 SELECT * FROM t7;
270 SELECT COUNT(*) FROM t7;
271
272 --echo #
273 --echo # Connection 1
274 --echo #
275 connection con1;
276 INSERT INTO t5(b) VALUES ("inserted by client 2");
277 UPDATE t5 SET a = 10 where a = 1;
278 UPDATE t5 SET b = "updated by client 2" where a = 2;
279 DELETE FROM t5 WHERE a = 3;
280 SELECT * FROM t5;
281 SELECT COUNT(*) FROM t5;
282 INSERT INTO t6(b) VALUES ("inserted by client 2");
283 UPDATE t6 SET a = 10 where a = 1;
284 UPDATE t6 SET b = "updated by client 2" where a = 2;
285 DELETE FROM t6 WHERE a = 3;
286 SELECT * FROM t6;
287 SELECT COUNT(*) FROM t6;
288 INSERT INTO t7(b) VALUES ("inserted by client 2");
289 UPDATE t7 SET a = 10 where a = 1;
290 UPDATE t7 SET b = "updated by client 2" where a = 2;
291 DELETE FROM t7 WHERE a = 3;
292 SELECT * FROM t7;
293 SELECT COUNT(*) FROM t7;
294
295 --echo #
296 --echo # Connection default
297 --echo #
298 connection default;
299 SELECT * FROM t5;
300 INSERT INTO t5(b) VALUES ("inserted by client 1");
301 SELECT * FROM t5;
302 UPDATE t5 SET a = a + 100;
303 SELECT * FROM t5;
304 SELECT COUNT(*) FROM t5;
305
306 UPDATE t6 SET b = "updated by client 2";
307 SELECT * FROM t6;
308 SELECT * FROM t6 LOCK IN SHARE MODE;
309 SELECT COUNT(*) FROM t6;
310
311 DELETE FROM t7;
312 SELECT * FROM t7;
313 SELECT COUNT(*) FROM t7;
314
315 COMMIT;
316 SELECT * FROM t5;
317 SELECT COUNT(*) FROM t5;
318 SELECT * FROM t6;
319 SELECT COUNT(*) FROM t6;
320 SELECT * FROM t7;
321 SELECT COUNT(*) FROM t7;
322
323 --echo #
324 --echo # Cleanup
325 --echo #
326 DROP TABLE t1,t2,t4,t5,t6,t7;
327 disconnect con1;
328 disconnect con2;
329 disconnect con3;
330 --disable_query_log
331 eval set global innodb_lock_wait_timeout=$initial_timeout;
332 --enable_query_log
333
334 --echo #
335 --echo # Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*)
336 --echo #
337
338 CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY,
339 c2 INT NOT NULL DEFAULT 1,
340 c3 char(20) DEFAULT '',
341 KEY c2_idx (c2)) ENGINE=InnoDB STATS_PERSISTENT=0;
342
343 INSERT INTO t1(c1) VALUES (1), (2), (3);
344 INSERT INTO t1(c1) SELECT c1 + 10 FROM t1;
345 INSERT INTO t1(c1) SELECT c1 + 100 FROM t1;
346
347 CREATE TABLE t2 STATS_PERSISTENT=0 SELECT * FROM t1;
348
349 let query1= SELECT COUNT(*) FROM t1;
350 let query2= SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx);
351 let query3= SELECT COUNT(*) FROM t1, t2;
352 let query4= SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2;
353
354 eval EXPLAIN $query1;
355 eval EXPLAIN $query2;
356 eval EXPLAIN $query3;
357 eval EXPLAIN $query4;
358
359 DROP TABLE t1, t2;