1 -- source include/have_query_cache.inc
2 -- source include/not_embedded.inc
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
9 # Tests with query cache
11 set GLOBAL query_cache_size=1355776;
13 # Reset query cache variables.
15 flush query cache; # This crashed in some versions
16 flush query cache; # This crashed in some versions
20 drop table if exists t1, t2, t3, t11, t21;
25 create table t1 (a int not null);
26 insert into t1 values (1),(2),(3);
27 create table t2 (a int not null);
28 insert into t2 values (1),(2),(3);
31 insert into t1 values (4);
32 show status like "Qcache_free_blocks";
34 show status like "Qcache_free_blocks";
36 # With join results...
37 create table t1 (a text not null);
38 create table t11 (a text not null);
39 create table t2 (a text not null);
40 create table t21 (a text not null);
41 create table t3 (a text not null);
42 insert into t1 values("1111111111111111111111111111111111111111111111111111");
43 insert into t2 select * from t1;
44 insert into t1 select * from t2;
45 insert into t2 select * from t1;
46 insert into t1 select * from t2;
47 insert into t2 select * from t1;
48 insert into t1 select * from t2;
49 insert into t2 select * from t1;
50 insert into t1 select * from t2;
51 insert into t2 select * from t1;
52 insert into t1 select * from t2;
53 insert into t2 select * from t1;
54 insert into t1 select * from t2;
55 insert into t2 select * from t1;
56 insert into t1 select * from t2;
57 insert into t2 select * from t1;
58 # t11 and t21 must be over 4Kb (QUERY_CACHE_MIN_RESULT_DATA_SIZE)
59 insert into t11 select * from t1;
60 insert into t21 select * from t1;
61 insert into t1 select * from t2;
62 insert into t2 select * from t1;
63 insert into t1 select * from t2;
64 #results of t3 must be > 0.5Mb
65 insert into t3 select * from t1;
66 insert into t3 select * from t2;
67 insert into t3 select * from t1;
72 show status like "Qcache_total_blocks";
73 show status like "Qcache_free_blocks";
75 insert into t11 values("");
78 show status like "Qcache_total_blocks";
79 show status like "Qcache_free_blocks";
81 show status like "Qcache_total_blocks";
82 show status like "Qcache_free_blocks";
83 drop table t1, t2, t3, t11, t21;
86 # do not use QC if tables locked (Bug#12385)
88 connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
90 CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE =
92 LOCK TABLE t1 READ LOCAL;
93 connect (root2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
95 INSERT INTO t1 VALUES (), (), ();
108 # query in QC from normal execution and SP (Bug#6897)
109 # improved to also test Bug#3583 and Bug#12990
115 create table t1 (s1 int)//
116 create procedure f1 () begin
117 select sql_cache * from t1;
118 select sql_cache * from t1;
119 select sql_cache * from t1;
121 create procedure f2 () begin
122 select sql_cache * from t1 where s1=1;
123 select sql_cache * from t1;
125 create procedure f3 () begin
126 select sql_cache * from t1;
127 select sql_cache * from t1 where s1=1;
129 create procedure f4 () begin
130 select sql_cache * from t1;
131 select sql_cache * from t1 where s1=1;
132 select sql_cache * from t1;
133 select sql_cache * from t1 where s1=1;
134 select sql_cache * from t1 where s1=1;
139 show status like "Qcache_queries_in_cache";
140 show status like "Qcache_inserts";
141 show status like "Qcache_hits";
143 show status like "Qcache_queries_in_cache";
144 show status like "Qcache_inserts";
145 show status like "Qcache_hits";
147 select sql_cache * from t1;
148 show status like "Qcache_queries_in_cache";
149 show status like "Qcache_inserts";
150 show status like "Qcache_hits";
151 insert into t1 values (1);
152 select sql_cache * from t1;
153 show status like "Qcache_queries_in_cache";
154 show status like "Qcache_inserts";
155 show status like "Qcache_hits";
158 select sql_cache * from t1;
159 show status like "Qcache_queries_in_cache";
160 show status like "Qcache_inserts";
161 show status like "Qcache_hits";
165 select sql_cache * from t1;
166 select sql_cache * from t1 where s1=1;
174 select sql_cache * from t1 where s1=1;
175 insert into t1 values (2);
177 select sql_cache * from t1 where s1=1;
178 select sql_cache * from t1;
191 # Bug#14767 INSERT in SF + concurrent SELECT with query cache
194 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
195 SET GLOBAL log_bin_trust_function_creators = 1;
199 drop function if exists f1;
201 create table t1 (id int);
203 create function f1 ()
207 set i_var = sleep(3);
208 insert into t1 values(3);
209 set i_var = sleep(3);
214 connect (con1,localhost,root,,);
215 connect (con2,localhost,root,,);
225 # This gives wrong result i.e. 't' table seems to be empty
235 set GLOBAL query_cache_size=0;
239 SET @@global.log_bin_trust_function_creators = @old_log_bin_trust_function_creators;
242 # Bug #30269 Query cache eats memory
245 DROP DATABASE IF EXISTS bug30269;
248 CREATE DATABASE bug30269;
250 CREATE TABLE test1 (id int, name varchar(23));
251 CREATE VIEW view1 AS SELECT * FROM test1;
252 INSERT INTO test1 VALUES (5, 'testit');
253 GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
254 GRANT SELECT ON TABLE bug30269.view1 TO 'bug30269'@'localhost';
255 set global query_cache_size= 81920;
256 connect (bug30269, localhost, bug30269,,);
259 show status like 'Qcache_queries_in_cache';
260 --echo # Select statement not stored in query cache because of column privileges.
261 SELECT id FROM test1 WHERE id>2;
262 show status like 'Qcache_queries_in_cache';
263 SELECT id FROM view1 WHERE id>2;
264 show status like 'Qcache_queries_in_cache';
268 DROP DATABASE bug30269;
270 DROP USER 'bug30269'@'localhost';
272 set GLOBAL query_cache_type=default;
273 set GLOBAL query_cache_limit=default;
274 set GLOBAL query_cache_min_res_unit=default;
275 set GLOBAL query_cache_size=default;
277 # Wait till we reached the initial number of concurrent sessions
278 --source include/wait_until_count_sessions.inc