1 SET @query_cache_type= @@global.query_cache_type;
2 SET @query_cache_limit= @@global.query_cache_limit;
3 SET @query_cache_min_res_unit= @@global.query_cache_min_res_unit;
4 SET @query_cache_size= @@global.query_cache_size;
5 # Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock
6 # Establish connections user1,user2,user3 (user=root)
7 # Switch to connection user1
8 SET GLOBAL query_cache_type=1;
9 SET GLOBAL query_cache_limit=10000;
10 SET GLOBAL query_cache_min_res_unit=0;
11 SET GLOBAL query_cache_size= 100000;
13 DROP TABLE IF EXISTS t1, t2;
14 CREATE TABLE t1 (a INT);
15 CREATE TABLE t2 (a INT);
16 INSERT INTO t1 VALUES (1),(2),(3);
17 # Switch to connection user2
19 # Switch to connection user1
20 # "send" the next select, "reap" the result later.
21 # The select will be blocked by the write lock on the t1.
22 SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
23 # Switch to connection user3
24 # Poll till the select of connection user1 is blocked by the write lock on t1.
25 SELECT user,command,state,info FROM information_schema.processlist
26 WHERE state = 'Locked'
27 AND info = 'SELECT *, (SELECT COUNT(*) FROM t2) FROM t1';
28 user command state info
29 root Query Locked SELECT *, (SELECT COUNT(*) FROM t2) FROM t1
30 INSERT INTO t1 VALUES (4);
31 # Switch to connection user2
33 # Switch to connection user1
34 # Collecting ("reap") the result from the previously blocked select.
35 # The printing of the result (varies between 3 and 4 rows) set has to be suppressed.
36 # Switch to connection user3
37 # The next select enforces that effects of "concurrent_inserts" like the
38 # record with a = 4 is missing in result sets can no more happen.
39 SELECT 1 FROM t1 WHERE a = 4;
42 # Switch to connection user1
43 # The next result set must contain 4 rows.
44 SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
45 a (SELECT COUNT(*) FROM t2)
51 SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
52 a (SELECT COUNT(*) FROM t2)
58 # Switch to connection default + close connections user1,user2,user3
59 SET GLOBAL query_cache_type= @query_cache_type;
60 SET GLOBAL query_cache_limit= @query_cache_limit;
61 SET GLOBAL query_cache_min_res_unit= @query_cache_min_res_unit;
62 SET GLOBAL query_cache_size= @query_cache_size;