Debian Codegraph @

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

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