EVOLUTION-MANAGER

Edit File: query_cache_notembedded.result

set @sql_mode_save=@@global.sql_mode; set @query_cache_type_save=@@global.query_cache_type; set @query_cache_size_save=@@global.query_cache_size; set GLOBAL sql_mode=""; set LOCAL sql_mode=""; flush query cache; flush query cache; reset query cache; flush status; drop table if exists t1, t2, t3, t11, t21; create table t1 (a int not null); insert into t1 values (1),(2),(3); create table t2 (a int not null); insert into t2 values (1),(2),(3); select * from t1; a 1 2 3 select * from t2; a 1 2 3 insert into t1 values (4); show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 2 flush query cache; show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 drop table t1, t2; create table t1 (a text not null); create table t11 (a text not null); create table t2 (a text not null); create table t21 (a text not null); create table t3 (a text not null); insert into t1 values("1111111111111111111111111111111111111111111111111111"); insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t11 select * from t1; insert into t21 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t3 select * from t1; insert into t3 select * from t2; insert into t3 select * from t1; select * from t11; select * from t21; show status like "Qcache_total_blocks"; Variable_name Value Qcache_total_blocks 7 show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 insert into t11 values(""); select * from t3; show status like "Qcache_total_blocks"; Variable_name Value Qcache_total_blocks 8 show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 flush query cache; show status like "Qcache_total_blocks"; Variable_name Value Qcache_total_blocks 7 show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 drop table t1, t2, t3, t11, t21; connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; connection root; CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE = MyISAM; LOCK TABLE t1 READ LOCAL; connect root2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; connection root2; INSERT INTO t1 VALUES (), (), (); connection root; SELECT * FROM t1; a connection root2; SELECT * FROM t1; a 1 2 3 connection root; SELECT * FROM t1; a UNLOCK TABLES; drop table t1; connection default; disconnect root; disconnect root2; flush query cache; reset query cache; flush status; create table t1 (s1 int)// create procedure f1 () begin select sql_cache * from t1; select sql_cache * from t1; select sql_cache * from t1; end;// create procedure f2 () begin select sql_cache * from t1 where s1=1; select sql_cache * from t1; end;// create procedure f3 () begin select sql_cache * from t1; select sql_cache * from t1 where s1=1; end;// create procedure f4 () begin select sql_cache * from t1; select sql_cache * from t1 where s1=1; select sql_cache * from t1; select sql_cache * from t1 where s1=1; select sql_cache * from t1 where s1=1; end;// call f1(); s1 s1 s1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 3 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 call f1(); s1 s1 s1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 3 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 3 call f1(); s1 s1 s1 select sql_cache * from t1; s1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 4 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 insert into t1 values (1); select sql_cache * from t1; s1 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 5 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 call f1(); s1 1 s1 1 s1 1 call f1(); s1 1 s1 1 s1 1 select sql_cache * from t1; s1 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 8 show status like "Qcache_hits"; Variable_name Value Qcache_hits 10 flush query cache; reset query cache; flush status; select sql_cache * from t1; s1 1 select sql_cache * from t1 where s1=1; s1 1 call f1(); s1 1 s1 1 s1 1 call f2(); s1 1 s1 1 call f3(); s1 1 s1 1 call f4(); s1 1 s1 1 s1 1 s1 1 s1 1 call f4(); s1 1 s1 1 s1 1 s1 1 s1 1 call f3(); s1 1 s1 1 call f2(); s1 1 s1 1 select sql_cache * from t1 where s1=1; s1 1 insert into t1 values (2); call f1(); s1 1 2 s1 1 2 s1 1 2 select sql_cache * from t1 where s1=1; s1 1 select sql_cache * from t1; s1 1 2 call f1(); s1 1 2 s1 1 2 s1 1 2 call f3(); s1 1 2 s1 1 call f3(); s1 1 2 s1 1 call f1(); s1 1 2 s1 1 2 s1 1 2 drop procedure f1; drop procedure f2; drop procedure f3; drop procedure f4; drop table t1; connection default; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; reset query cache; drop function if exists f1; create table t1 (id int); create function f1 () returns int begin declare i_var int; set i_var = sleep(3); insert into t1 values(3); set i_var = sleep(3); return 0; end;| connect con1,localhost,root,,; connect con2,localhost,root,,; connection con1; select f1(); connection con2; select sleep(4); sleep(4) 0 select * from t1; id 3 connection con1; f1() 0 connection con2; select * from t1; id 3 reset query cache; select * from t1; id 3 drop table t1; drop function f1; disconnect con1; disconnect con2; connection default; set GLOBAL query_cache_size=0; SET @@global.log_bin_trust_function_creators = @old_log_bin_trust_function_creators; DROP DATABASE IF EXISTS bug30269; FLUSH STATUS; CREATE DATABASE bug30269; USE bug30269; CREATE TABLE test1 (id int, name varchar(23)); CREATE VIEW view1 AS SELECT * FROM test1; INSERT INTO test1 VALUES (5, 'testit'); GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost'; GRANT SELECT ON TABLE bug30269.view1 TO 'bug30269'@'localhost'; set global query_cache_size= 81920; connect bug30269, localhost, bug30269,,; connection bug30269; USE bug30269; show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 # Select statement not stored in query cache because of column privileges. SELECT id FROM test1 WHERE id>2; id 5 show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 SELECT id FROM view1 WHERE id>2; id 5 show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 connection default; USE test; DROP DATABASE bug30269; disconnect bug30269; DROP USER 'bug30269'@'localhost'; set GLOBAL query_cache_type=ON; set LOCAL query_cache_type=ON; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; drop table if exists t1| create table t1 ( id char(16) not null default '', data int not null )| drop procedure if exists bug3583| drop procedure if exists bug3583| create procedure bug3583() begin declare c int; select * from t1; select count(*) into c from t1; select c; end| insert into t1 values ("x", 3), ("y", 5)| set @x = @@query_cache_size| set global query_cache_size = 10*1024*1024| flush status| flush query cache| show status like 'Qcache_hits'| Variable_name Value Qcache_hits 0 call bug3583()| id data x 3 y 5 c 2 show status like 'Qcache_hits'| Variable_name Value Qcache_hits 0 call bug3583()| id data x 3 y 5 c 2 call bug3583()| id data x 3 y 5 c 2 show status like 'Qcache_hits'| Variable_name Value Qcache_hits 2 set global query_cache_size = @x| flush status| flush query cache| delete from t1| drop procedure bug3583| drop table t1| SET GLOBAL query_cache_size=@query_cache_size_save; SET GLOBAL query_cache_type=@query_cache_type_save; set GLOBAL sql_mode=@sql_mode_save;