diff options
Diffstat (limited to 'mysql-test/main/query_cache_ps_no_prot.result')
-rw-r--r-- | mysql-test/main/query_cache_ps_no_prot.result | 577 |
1 files changed, 577 insertions, 0 deletions
diff --git a/mysql-test/main/query_cache_ps_no_prot.result b/mysql-test/main/query_cache_ps_no_prot.result new file mode 100644 index 00000000000..ef1f92b597d --- /dev/null +++ b/mysql-test/main/query_cache_ps_no_prot.result @@ -0,0 +1,577 @@ +set GLOBAL query_cache_type=ON; +set LOCAL query_cache_type=ON; +connect con1,localhost,root,,test,$MASTER_MYPORT,; +connection default; +set @initial_query_cache_size = @@global.query_cache_size; +set @@global.query_cache_size=102400; +flush status; +drop table if exists t1; +create table t1(c1 int); +insert into t1 values(1),(10),(100); +prepare stmt1 from "select * from t1 where c1=10"; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 1 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 2 +prepare stmt2 from "select * from t1 where c1=10"; +execute stmt2; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 3 +execute stmt2; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 4 +execute stmt2; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 5 +connection con1; +prepare stmt3 from "select * from t1 where c1=10"; +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 6 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 7 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 8 +connection default; +prepare stmt10 from "SELECT * FROM t1 WHERE c1 = 100"; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 8 +execute stmt10; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 8 +execute stmt10; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 9 +SELECT * FROM t1 WHERE c1 = 100; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 10 +connection con1; +SELECT * FROM t1 WHERE c1 = 100; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 11 +connection default; +prepare stmt11 from "SELECT * FROM t1 WHERE c1 = 1"; +connection con1; +prepare stmt12 from "SELECT * FROM t1 WHERE c1 = 1"; +connection default; +SELECT * FROM t1 WHERE c1 = 1; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 11 +SELECT * FROM t1 WHERE c1 = 1; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt11; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 13 +connection con1; +execute stmt12; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +connection default; +prepare stmt1 from "select * from t1 where c1=?"; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +set @a=1; +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 15 +connection con1; +set @a=1; +prepare stmt4 from "select * from t1 where c1=?"; +execute stmt4 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +prepare stmt4 from "select @a from t1 where c1=?"; +execute stmt4 using @a; +@a +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt4 using @a; +@a +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +connection default; +prepare stmt1 from "select * from t1 where c1=10"; +set global query_cache_size=0; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +connection con1; +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +connection default; +set global query_cache_size=102400; +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 18 +connection con1; +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 20 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +connection default; +set global query_cache_size=0; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +connection con1; +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +connection default; +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=10"; +connection con1; +prepare stmt3 from "select * from t1 where c1=10"; +connection default; +set global query_cache_size=102400; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +connection con1; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +connection default; +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=?"; +set global query_cache_size=102400; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +set @a=1; +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +set @a=100; +execute stmt1 using @a; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +set @a=10; +execute stmt1 using @a; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 21 +drop table t1; +disconnect con1; +######################################################################## +# +# BUG#25843: Changing default database between PREPARE and EXECUTE of +# statement breaks binlog. +# +######################################################################## + +# +# Check that default database and its attributes are fixed at the +# creation time. +# + +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; + +CREATE DATABASE mysqltest1 COLLATE utf8_unicode_ci; +CREATE DATABASE mysqltest2 COLLATE utf8_general_ci; + +CREATE TABLE mysqltest1.t1(msg VARCHAR(255)); +CREATE TABLE mysqltest2.t1(msg VARCHAR(255)); + +use mysqltest1; +PREPARE stmt_a_1 FROM 'INSERT INTO t1 VALUES(DATABASE())'; +PREPARE stmt_a_2 FROM 'INSERT INTO t1 VALUES(@@collation_database)'; + +EXECUTE stmt_a_1; +EXECUTE stmt_a_2; + +use mysqltest2; +EXECUTE stmt_a_1; +EXECUTE stmt_a_2; + +SELECT * FROM mysqltest1.t1; +msg +mysqltest1 +utf8_unicode_ci +mysqltest1 +utf8_unicode_ci + +SELECT * FROM mysqltest2.t1; +msg + +DROP PREPARE stmt_a_1; +DROP PREPARE stmt_a_2; + +# +# The Query Cache test case. +# + +DELETE FROM mysqltest1.t1; +DELETE FROM mysqltest2.t1; + +INSERT INTO mysqltest1.t1 VALUES('mysqltest1.t1'); +INSERT INTO mysqltest2.t1 VALUES('mysqltest2.t1'); + +use mysqltest1; +PREPARE stmt_b_1 FROM 'SELECT * FROM t1'; + +use mysqltest2; +PREPARE stmt_b_2 FROM 'SELECT * FROM t1'; + +EXECUTE stmt_b_1; +msg +mysqltest1.t1 + +EXECUTE stmt_b_2; +msg +mysqltest2.t1 + +use mysqltest1; + +EXECUTE stmt_b_1; +msg +mysqltest1.t1 + +EXECUTE stmt_b_2; +msg +mysqltest2.t1 + +DROP PREPARE stmt_b_1; +DROP PREPARE stmt_b_2; + +use test; + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; + +# +# Check that prepared statements work properly when there is no current +# database. +# + +CREATE DATABASE mysqltest1 COLLATE utf8_unicode_ci; +CREATE DATABASE mysqltest2 COLLATE utf8_general_ci; + +use mysqltest1; + +PREPARE stmt_c_1 FROM 'SELECT DATABASE(), @@collation_database'; + +use mysqltest2; + +PREPARE stmt_c_2 FROM 'SELECT DATABASE(), @@collation_database'; + +DROP DATABASE mysqltest2; + +SELECT DATABASE(), @@collation_database; +DATABASE() @@collation_database +NULL latin1_swedish_ci + +EXECUTE stmt_c_1; +DATABASE() @@collation_database +mysqltest1 utf8_unicode_ci + +SELECT DATABASE(), @@collation_database; +DATABASE() @@collation_database +NULL latin1_swedish_ci + +EXECUTE stmt_c_2; +DATABASE() @@collation_database +NULL latin1_swedish_ci +Warnings: +Note 1049 Unknown database 'mysqltest2' + +SELECT DATABASE(), @@collation_database; +DATABASE() @@collation_database +NULL latin1_swedish_ci + +PREPARE stmt_c_3 FROM 'SELECT DATABASE(), @@collation_database'; + +EXECUTE stmt_c_3; +DATABASE() @@collation_database +NULL latin1_swedish_ci + +use mysqltest1; + +EXECUTE stmt_c_2; +DATABASE() @@collation_database +NULL latin1_swedish_ci +Warnings: +Note 1049 Unknown database 'mysqltest2' + +SELECT DATABASE(), @@collation_database; +DATABASE() @@collation_database +mysqltest1 utf8_unicode_ci + +EXECUTE stmt_c_3; +DATABASE() @@collation_database +NULL latin1_swedish_ci + +SELECT DATABASE(), @@collation_database; +DATABASE() @@collation_database +mysqltest1 utf8_unicode_ci + +DROP DATABASE mysqltest1; + +use test; + +######################################################################## +# +# Bug#27430 Crash in subquery code when in PS and table DDL changed +# after PREPARE +# Check the effect of automatic reprepare on query cache +# +######################################################################## +drop table if exists t1; +create table t1 (a varchar(255)); +insert into t1 (a) values ("Pack my box with five dozen liquor jugs."); +flush status; +prepare stmt from "select a from t1"; +execute stmt; +a +Pack my box with five dozen liquor jugs. +set @@global.query_cache_size=0; +alter table t1 add column b int; +execute stmt; +a +Pack my box with five dozen liquor jugs. +set @@global.query_cache_size=102400; +execute stmt; +a +Pack my box with five dozen liquor jugs. +execute stmt; +a +Pack my box with five dozen liquor jugs. +# +# Sic: ALTER TABLE caused an automatic reprepare +# of the prepared statement. Since the query cache was disabled +# at the time of reprepare, the new prepared statement doesn't +# work with it. +# +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +show status like 'Qcache_queries_in_cache'; +Variable_name Value +Qcache_queries_in_cache 0 +# Cleanup +deallocate prepare stmt; +drop table t1; +set @@global.query_cache_size=@initial_query_cache_size; +flush status; +set GLOBAL query_cache_type=default; |