-- source include/have_query_cache.inc -- source include/not_embedded.inc # # Tests with query cache # set GLOBAL query_cache_size=1355776; # Reset query cache variables. flush query cache; # This crashed in some versions flush query cache; # This crashed in some versions reset query cache; flush status; --disable_warnings drop table if exists t1, t2, t3, t11, t21; --enable_warnings # # FLUSH QUERY CACHE # 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; select * from t2; insert into t1 values (4); show status like "Qcache_free_blocks"; flush query cache; show status like "Qcache_free_blocks"; drop table t1, t2; # With join results... 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; # t11 and t21 must be over 4Kb (QUERY_CACHE_MIN_RESULT_DATA_SIZE) 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; #results of t3 must be > 0.5Mb insert into t3 select * from t1; insert into t3 select * from t2; insert into t3 select * from t1; disable_result_log; select * from t11; select * from t21; enable_result_log; show status like "Qcache_total_blocks"; show status like "Qcache_free_blocks"; disable_result_log; insert into t11 values(""); select * from t3; enable_result_log; show status like "Qcache_total_blocks"; show status like "Qcache_free_blocks"; flush query cache; show status like "Qcache_total_blocks"; show status like "Qcache_free_blocks"; drop table t1, t2, t3, t11, t21; # # do not use QC if tables locked (BUG#12385) # 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; connection root2; SELECT * FROM t1; connection root; SELECT * FROM t1; drop table t1; # # query in QC from normal execution and SP (BUG#6897) # improved to also test BUG#3583 and BUG#12990 # flush query cache; reset query cache; flush status; delimiter //; 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;// delimiter ;// call f1(); --replace_result 1 3 show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; call f1(); show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; call f1(); select sql_cache * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; insert into t1 values (1); select sql_cache * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; call f1(); call f1(); select sql_cache * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; flush query cache; reset query cache; flush status; select sql_cache * from t1; select sql_cache * from t1 where s1=1; call f1(); call f2(); call f3(); call f4(); call f4(); call f3(); call f2(); select sql_cache * from t1 where s1=1; insert into t1 values (2); call f1(); select sql_cache * from t1 where s1=1; select sql_cache * from t1; call f1(); call f3(); call f3(); call f1(); drop procedure f1; drop procedure f2; drop procedure f3; drop procedure f4; drop table t1; # # bug#14767: INSERT in SF + concurrent SELECT with query cache # SET GLOBAL log_bin_trust_function_creators = 1; reset query cache; --disable_warnings drop function if exists f1; --enable_warnings create table t1 (id int); delimiter |; 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;| delimiter ;| connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; send select f1(); connection con2; select sleep(4); select * from t1; connection con1; reap; connection con2; # This gives wrong result i.e. 't' table seems to be empty select * from t1; reset query cache; select * from t1; drop table t1; drop function f1; disconnect con1; disconnect con2; connection default; set GLOBAL query_cache_size=0; # End of 5.0 tests SET GLOBAL log_bin_trust_function_creators = 0; # # Bug #30269 Query cache eats memory # --disable_warnings DROP DATABASE IF EXISTS bug30269; --enable_warnings 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'; --echo # Select statement not stored in query cache because of column privileges. SELECT id FROM test1 WHERE id>2; show status like 'Qcache_queries_in_cache'; SELECT id FROM view1 WHERE id>2; show status like 'Qcache_queries_in_cache'; connection default; DROP DATABASE bug30269; disconnect bug30269; DROP USER 'bug30269'@'localhost'; set GLOBAL query_cache_type=default; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default;