diff options
| -rw-r--r-- | mysql-test/include/grant_cache.inc (renamed from mysql-test/t/grant_cache.test) | 100 | ||||
| -rw-r--r-- | mysql-test/include/query_cache_sql_prepare.inc | 271 | ||||
| -rw-r--r-- | mysql-test/r/grant_cache_no_prot.result (renamed from mysql-test/r/grant_cache.result) | 8 | ||||
| -rw-r--r-- | mysql-test/r/grant_cache_ps_prot.result | 218 | ||||
| -rw-r--r-- | mysql-test/r/query_cache_ps_no_prot.result | 362 | ||||
| -rw-r--r-- | mysql-test/r/query_cache_ps_ps_prot.result | 362 | ||||
| -rw-r--r-- | mysql-test/r/query_cache_sql_prepare.result | 204 | ||||
| -rw-r--r-- | mysql-test/t/grant_cache_no_prot.test | 25 | ||||
| -rw-r--r-- | mysql-test/t/grant_cache_ps_prot.test | 24 | ||||
| -rw-r--r-- | mysql-test/t/query_cache_ps_no_prot.test | 26 | ||||
| -rw-r--r-- | mysql-test/t/query_cache_ps_ps_prot.test | 25 | ||||
| -rw-r--r-- | mysql-test/t/query_cache_sql_prepare.test | 146 | ||||
| -rw-r--r-- | tests/mysql_client_test.c | 6 |
13 files changed, 1384 insertions, 393 deletions
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/include/grant_cache.inc index 10e571fc5f5..dd65d1ed726 100644 --- a/mysql-test/t/grant_cache.test +++ b/mysql-test/include/grant_cache.inc @@ -1,14 +1,43 @@ -# Grant tests not performed with embedded server --- source include/not_embedded.inc --- source include/have_query_cache.inc -# See at the end of the test why we disable the ps protocol (*) --- disable_ps_protocol +################### include/grant_cache.inc #################### +# +# Test grants with query cache +# +# Last update: +# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc +# - Remove the disabling of the ps-protocol +# - minor improvements like error names instead of numbers +# - Create two toplevel tests sourcing this routine +# +# Running this test with and without "--ps-protocol" produces different +# Qcache_not_cached results because of the following reason: +# In normal protocol, a SELECT failing due to insufficient privileges +# increments Qcache_not_cached, while in ps-protocol, no. +# In detail: +# - In normal protocol, +# the "access denied" errors on SELECT are issued at (stack trace): +# mysql_parse/mysql_execute_command/execute_sqlcom_select/handle_select/ +# mysql_select/JOIN::prepare/setup_wild/insert_fields/ +# check_grant_all_columns/my_error/my_message_sql, which then calls +# push_warning/query_cache_abort: at this moment, +# query_cache_store_query() has been called, so query exists in cache, +# so thd->net.query_cache_query!=NULL, so query_cache_abort() removes +# the query from cache, which causes a query_cache.refused++ (thus, +# a Qcache_not_cached++). +# - In ps-protocol, +# the error is issued at prepare time; +# for this mysql_test_select() is called, not execute_sqlcom_select() +# (and that also leads to JOIN::prepare/etc). Thus, as +# query_cache_store_query() has not been called, +# thd->net.query_cache_query==NULL, so query_cache_abort() does nothing: +# Qcache_not_cached is not incremented. +# +# A run of this tests with sp/cursor/view protocol does not make sense +# because these protocols serve totally different purposes than this test. +# --source include/add_anonymous_users.inc # -# Test grants with query cache -# --disable_warnings drop table if exists test.t1,mysqltest.t1,mysqltest.t2; drop database if exists mysqltest; @@ -18,6 +47,7 @@ set GLOBAL query_cache_size=1355776; reset query cache; flush status; +--echo ----- establish connection root ----- connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; show grants for current_user; @@ -33,6 +63,7 @@ insert into mysqltest.t2 values (3,3,3); create table test.t1 (a char (10)); insert into test.t1 values ("test.t1"); select * from t1; +--echo ----- establish connection root2 ----- connect (root2,localhost,root,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection root2; # put queries in cache @@ -51,6 +82,7 @@ grant SELECT on test.t1 to mysqltest_2@localhost; grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; # The following queries should be fetched from cache +--echo ----- establish connection user1 (user=mysqltest_1) ----- connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user1; show grants for current_user(); @@ -76,12 +108,14 @@ show status like "Qcache_hits"; show status like "Qcache_not_cached"; +--echo ----- establish connection unkuser (user=unkuser) ----- # Don't use '' as user because it will pick Unix login connect (unkuser,localhost,unkuser,,,$MASTER_MYPORT,$MASTER_MYSOCK); connection unkuser; show grants for current_user(); # The following queries should be fetched from cache +--echo ----- establish connection user2 (user=mysqltest_2) ----- connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user2; select "user2"; @@ -90,39 +124,41 @@ select a from t1; select c from t1; select * from mysqltest.t1,test.t1; --replace_result 127.0.0.1 localhost ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from t2; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; show status like "Qcache_not_cached"; # The following queries should not be fetched from cache +--echo ----- establish connection user3 (user=mysqltest_3) ----- connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user3; select "user3"; --replace_result 127.0.0.1 localhost ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR select * from t1; select a from t1; --replace_result 127.0.0.1 localhost ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR select c from t1; --replace_result 127.0.0.1 localhost ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from t2; --replace_result 127.0.0.1 localhost ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR select mysqltest.t1.c from test.t1,mysqltest.t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; show status like "Qcache_not_cached"; # Connect without a database +--echo ----- establish connection user4 (user=mysqltest_1) ----- connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); connection user4; select "user4"; show grants; ---error 1046 +--error ER_NO_DB_ERROR select a from t1; # The following query is not cached before (different database) select * from mysqltest.t1,test.t1; @@ -135,7 +171,16 @@ show status like "Qcache_not_cached"; # Cleanup -connection root; +--echo ----- switch to connection default and close connections ----- +connection default; +disconnect root; +disconnect root2; +disconnect user1; +disconnect user2; +disconnect user3; +disconnect user4; +disconnect unkuser; + # # A temporary 4.1 workaround to make this test pass if # mysql was compiled with other than latin1 --with-charset=XXX. @@ -156,30 +201,3 @@ drop database mysqltest; set GLOBAL query_cache_size=default; --source include/delete_anonymous_users.inc - - -# End of 4.1 tests - -# (*) Why we disable the ps protocol: because in normal protocol, -# a SELECT failing due to insufficient privileges increments -# Qcache_not_cached, while in ps-protocol, no. -# In detail: in normal protocol, -# the "access denied" errors on SELECT are issued at (stack trace): -# mysql_parse/mysql_execute_command/execute_sqlcom_select/handle_select/ -# mysql_select/JOIN::prepare/setup_wild/insert_fields/ -# check_grant_all_columns/my_error/my_message_sql, which then calls -# push_warning/query_cache_abort: at this moment, -# query_cache_store_query() has been called, so query exists in cache, -# so thd->net.query_cache_query!=NULL, so query_cache_abort() removes -# the query from cache, which causes a query_cache.refused++ (thus, -# a Qcache_not_cached++). -# While in ps-protocol, the error is issued at prepare time; -# for this mysql_test_select() is called, not execute_sqlcom_select() -# (and that also leads to JOIN::prepare/etc). Thus, as -# query_cache_store_query() has not been called, -# thd->net.query_cache_query==NULL, so query_cache_abort() does nothing: -# Qcache_not_cached is not incremented. -# As this test prints Qcache_not_cached after SELECT failures, -# we cannot enable this test in ps-protocol. - ---enable_ps_protocol diff --git a/mysql-test/include/query_cache_sql_prepare.inc b/mysql-test/include/query_cache_sql_prepare.inc new file mode 100644 index 00000000000..cdb3bd586e7 --- /dev/null +++ b/mysql-test/include/query_cache_sql_prepare.inc @@ -0,0 +1,271 @@ +############### include/query_cache_sql_prepare.inc ################ +# +# This is to see how statements prepared via the PREPARE SQL command +# go into the query cache: if using parameters they cannot; if not +# using parameters they can. +# Query cache is abbreviated as "QC" +# +# Last update: +# 2007-05-03 ML - Move t/query_cache_sql_prepare.test +# to include/query_cache_sql_prepare.inc +# - Create two toplevel tests sourcing this routine +# - Add tests checking that +# - another connection gets the same amount of QC hits +# - statements running via ps-protocol do not hit QC results +# of preceding sql EXECUTEs +# + +--source include/have_query_cache.inc +# embedded can't make more than one connection, which this test needs +-- source include/not_embedded.inc + +--echo ---- establish connection con1 (root) ---- +connect (con1,localhost,root,,test,$MASTER_MYPORT,); +--echo ---- switch to connection default ---- +connection default; + +set @initial_query_cache_size = @@global.query_cache_size; +set @@global.query_cache_size=100000; +flush status; +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1(c1 int); +insert into t1 values(1),(10),(100); + +# Prepared statements has no parameters, query caching should happen +prepare stmt1 from "select * from t1 where c1=10"; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +# Another prepared statement (same text, same connection), should hit the QC +prepare stmt2 from "select * from t1 where c1=10"; +execute stmt2; +show status like 'Qcache_hits'; +execute stmt2; +show status like 'Qcache_hits'; +execute stmt2; +show status like 'Qcache_hits'; +# Another prepared statement (same text, other connection), should hit the QC +--echo ---- switch to connection con1 ---- +connection con1; +prepare stmt3 from "select * from t1 where c1=10"; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +--echo ---- switch to connection default ---- +connection default; + +# Mixup tests, where statements without PREPARE.../EXECUTE.... meet statements +# with PREPARE.../EXECUTE.... (text protocol). Both statements have the +# same text. QC hits occur only when both statements use the same protocol. +# The outcome of the test depends on the mysqltest startup options +# - with "--ps-protocol" +# Statements without PREPARE.../EXECUTE.... run as prepared statements +# with binary protocol. Expect to get no QC hits. +# - without any "--<whatever>-protocol" +# Statements without PREPARE.../EXECUTE run as non prepared statements +# with text protocol. Expect to get QC hits. +############################################################################ +# +# Statement with PREPARE.../EXECUTE.... first +let $my_stmt= SELECT * FROM t1 WHERE c1 = 100; +eval prepare stmt10 from "$my_stmt"; +show status like 'Qcache_hits'; +execute stmt10; +show status like 'Qcache_hits'; +execute stmt10; +show status like 'Qcache_hits'; +eval $my_stmt; +show status like 'Qcache_hits'; +--echo ---- switch to connection con1 ---- +connection con1; +eval $my_stmt; +show status like 'Qcache_hits'; +--echo ---- switch to connection default ---- +connection default; +# +# Statement without PREPARE.../EXECUTE.... first +let $my_stmt= SELECT * FROM t1 WHERE c1 = 1; +eval prepare stmt11 from "$my_stmt"; +--echo ---- switch to connection con1 ---- +connection con1; +eval prepare stmt12 from "$my_stmt"; +--echo ---- switch to connection default ---- +connection default; +eval $my_stmt; +show status like 'Qcache_hits'; +eval $my_stmt; +show status like 'Qcache_hits'; +execute stmt11; +show status like 'Qcache_hits'; +--echo ---- switch to connection con1 ---- +connection con1; +execute stmt12; +show status like 'Qcache_hits'; +--echo ---- switch to connection default ---- +connection default; + +# Prepared statement has parameters, query caching should not happen +prepare stmt1 from "select * from t1 where c1=?"; +show status like 'Qcache_hits'; +set @a=1; +execute stmt1 using @a; +show status like 'Qcache_hits'; +execute stmt1 using @a; +show status like 'Qcache_hits'; +--echo ---- switch to connection con1 ---- +connection con1; +set @a=1; +prepare stmt4 from "select * from t1 where c1=?"; +execute stmt4 using @a; +show status like 'Qcache_hits'; +--echo ---- switch to connection default ---- +connection default; + +# See if enabling/disabling the query cache between PREPARE and +# EXECUTE is an issue; the expected result is that the query cache +# will not be used. +# Indeed, decision to read/write the query cache is taken at PREPARE +# time, so if the query cache was disabled at PREPARE time then no +# execution of the statement will read/write the query cache. +# If the query cache was enabled at PREPARE time, but disabled at +# EXECUTE time, at EXECUTE time the query cache internal functions do +# nothing so again the query cache is not read/written. But if the +# query cache is re-enabled before another execution then that +# execution will read/write the query cache. + +# QC is enabled at PREPARE +prepare stmt1 from "select * from t1 where c1=10"; +# then QC is disabled at EXECUTE +# Expect to see no additional Qcache_hits. +set global query_cache_size=0; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +# The QC is global = affects also other connections. +# Expect to see no additional Qcache_hits. +--echo ---- switch to connection con1 ---- +connection con1; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +# +# then QC is re-enabled for more EXECUTE. +--echo ---- switch to connection default ---- +connection default; +set global query_cache_size=100000; +# Expect to see additional Qcache_hits. +# The fact that the QC was temporary disabled should have no affect +# except that the first execute will not hit results from the +# beginning of the test (because QC has been emptied meanwhile by +# setting its size to 0). +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +# The QC is global = affects also other connections. +--echo ---- switch to connection con1 ---- +connection con1; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +--echo ---- switch to connection default ---- +connection default; +# +# then QC is re-disabled for more EXECUTE. +# Expect to see no additional Qcache_hits. +# The fact that the QC was temporary enabled should have no affect. +set global query_cache_size=0; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +# The QC is global = affects also other connections. +--echo ---- switch to connection con1 ---- +connection con1; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +# + +--echo ---- switch to connection default ---- +connection default; +# QC is disabled at PREPARE +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=10"; +--echo ---- switch to connection con1 ---- +connection con1; +prepare stmt3 from "select * from t1 where c1=10"; +--echo ---- switch to connection default ---- +connection default; +# then QC is enabled at EXECUTE +set global query_cache_size=100000; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +execute stmt1; +show status like 'Qcache_hits'; +# The QC is global = affects also other connections. +--echo ---- switch to connection con1 ---- +connection con1; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +execute stmt3; +show status like 'Qcache_hits'; +--echo ---- switch to connection default ---- +connection default; +# +# QC is disabled at PREPARE +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=?"; +# then QC is enabled at EXECUTE +set global query_cache_size=100000; +show status like 'Qcache_hits'; +set @a=1; +execute stmt1 using @a; +show status like 'Qcache_hits'; +set @a=100; +execute stmt1 using @a; +show status like 'Qcache_hits'; +set @a=10; +execute stmt1 using @a; +show status like 'Qcache_hits'; + + +drop table t1; +--echo ---- disconnect connection con1 ---- +disconnect con1; + +set @@global.query_cache_size=@initial_query_cache_size; +flush status; # reset Qcache status variables for next tests diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache_no_prot.result index 2c6840d77d0..02360c4c325 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache_no_prot.result @@ -3,6 +3,7 @@ drop database if exists mysqltest; set GLOBAL query_cache_size=1355776; reset query cache; flush status; +----- establish connection root ----- show grants for current_user; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION @@ -19,6 +20,7 @@ insert into test.t1 values ("test.t1"); select * from t1; a test.t1 +----- establish connection root2 ----- select * from t1; a b c 1 1 1 @@ -48,6 +50,7 @@ grant SELECT on mysqltest.* to mysqltest_1@localhost; grant SELECT on mysqltest.t1 to mysqltest_2@localhost; grant SELECT on test.t1 to mysqltest_2@localhost; grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; +----- establish connection user1 (user=mysqltest_1) ----- show grants for current_user(); Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' @@ -112,9 +115,11 @@ Qcache_hits 3 show status like "Qcache_not_cached"; Variable_name Value Qcache_not_cached 1 +----- establish connection unkuser (user=unkuser) ----- show grants for current_user(); Grants for @localhost GRANT USAGE ON *.* TO ''@'localhost' +----- establish connection user2 (user=mysqltest_2) ----- select "user2"; user2 user2 @@ -145,6 +150,7 @@ Qcache_hits 7 show status like "Qcache_not_cached"; Variable_name Value Qcache_not_cached 2 +----- establish connection user3 (user=mysqltest_3) ----- select "user3"; user3 user3 @@ -169,6 +175,7 @@ Qcache_hits 7 show status like "Qcache_not_cached"; Variable_name Value Qcache_not_cached 7 +----- establish connection user4 (user=mysqltest_1) ----- select "user4"; user4 user4 @@ -199,6 +206,7 @@ Qcache_hits 8 show status like "Qcache_not_cached"; Variable_name Value Qcache_not_cached 8 +----- switch to connection default and close connections ----- set names binary; delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); diff --git a/mysql-test/r/grant_cache_ps_prot.result b/mysql-test/r/grant_cache_ps_prot.result new file mode 100644 index 00000000000..1e2cd1baa3a --- /dev/null +++ b/mysql-test/r/grant_cache_ps_prot.result @@ -0,0 +1,218 @@ +drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +drop database if exists mysqltest; +set GLOBAL query_cache_size=1355776; +reset query cache; +flush status; +----- establish connection root ----- +show grants for current_user; +Grants for root@localhost +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +show grants; +Grants for root@localhost +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +create database if not exists mysqltest; +create table mysqltest.t1 (a int,b int,c int); +create table mysqltest.t2 (a int,b int,c int); +insert into mysqltest.t1 values (1,1,1),(2,2,2); +insert into mysqltest.t2 values (3,3,3); +create table test.t1 (a char (10)); +insert into test.t1 values ("test.t1"); +select * from t1; +a +test.t1 +----- establish connection root2 ----- +select * from t1; +a b c +1 1 1 +2 2 2 +select a from t1; +a +1 +2 +select c from t1; +c +1 +2 +select * from t2; +a b c +3 3 3 +select * from mysqltest.t1,test.t1; +a b c a +1 1 1 test.t1 +2 2 2 test.t1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits%"; +Variable_name Value +Qcache_hits 0 +grant SELECT on mysqltest.* to mysqltest_1@localhost; +grant SELECT on mysqltest.t1 to mysqltest_2@localhost; +grant SELECT on test.t1 to mysqltest_2@localhost; +grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; +----- establish connection user1 (user=mysqltest_1) ----- +show grants for current_user(); +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select "user1"; +user1 +user1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 +select * from t1; +a b c +1 1 1 +2 2 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 +select a from t1 ; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 +select c from t1; +c +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 1 +----- establish connection unkuser (user=unkuser) ----- +show grants for current_user(); +Grants for @localhost +GRANT USAGE ON *.* TO ''@'localhost' +----- establish connection user2 (user=mysqltest_2) ----- +select "user2"; +user2 +user2 +select * from t1; +a b c +1 1 1 +2 2 2 +select a from t1; +a +1 +2 +select c from t1; +c +1 +2 +select * from mysqltest.t1,test.t1; +a b c a +1 1 1 test.t1 +2 2 2 test.t1 +select * from t2; +ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2' +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 7 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 2 +----- establish connection user3 (user=mysqltest_3) ----- +select "user3"; +user3 +user3 +select * from t1; +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'b' in table 't1' +select a from t1; +a +1 +2 +select c from t1; +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1' +select * from t2; +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't2' +select mysqltest.t1.c from test.t1,mysqltest.t1; +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1' +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 7 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 4 +----- establish connection user4 (user=mysqltest_1) ----- +select "user4"; +user4 +user4 +show grants; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +select a from t1; +ERROR 3D000: No database selected +select * from mysqltest.t1,test.t1; +a b c a +1 1 1 test.t1 +2 2 2 test.t1 +select a from mysqltest.t1; +a +1 +2 +select a from mysqltest.t1; +a +1 +2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 8 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 8 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 5 +----- switch to connection default and close connections ----- +set names binary; +delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); +flush privileges; +drop table test.t1,mysqltest.t1,mysqltest.t2; +drop database mysqltest; +set GLOBAL query_cache_size=default; diff --git a/mysql-test/r/query_cache_ps_no_prot.result b/mysql-test/r/query_cache_ps_no_prot.result new file mode 100644 index 00000000000..bf162439918 --- /dev/null +++ b/mysql-test/r/query_cache_ps_no_prot.result @@ -0,0 +1,362 @@ +---- establish connection con1 (root) ---- +---- switch to connection default ---- +set @initial_query_cache_size = @@global.query_cache_size; +set @@global.query_cache_size=100000; +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 +---- switch to 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 +---- switch to 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 +---- switch to connection con1 ---- +SELECT * FROM t1 WHERE c1 = 100; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 11 +---- switch to connection default ---- +prepare stmt11 from "SELECT * FROM t1 WHERE c1 = 1"; +---- switch to connection con1 ---- +prepare stmt12 from "SELECT * FROM t1 WHERE c1 = 1"; +---- switch to 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 +---- switch to connection con1 ---- +execute stmt12; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +---- switch to 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 14 +---- switch to 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 14 +---- switch to 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 14 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +---- switch to connection con1 ---- +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +---- switch to connection default ---- +set global query_cache_size=100000; +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 15 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +---- switch to connection con1 ---- +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 18 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +---- switch to connection default ---- +set global query_cache_size=0; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +---- switch to 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 19 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +---- switch to connection default ---- +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=10"; +---- switch to connection con1 ---- +prepare stmt3 from "select * from t1 where c1=10"; +---- switch to connection default ---- +set global query_cache_size=100000; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +---- switch to connection con1 ---- +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 19 +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 19 +---- switch to connection default ---- +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=?"; +set global query_cache_size=100000; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +set @a=1; +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +set @a=100; +execute stmt1 using @a; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +set @a=10; +execute stmt1 using @a; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 19 +drop table t1; +---- disconnect connection con1 ---- +set @@global.query_cache_size=@initial_query_cache_size; +flush status; diff --git a/mysql-test/r/query_cache_ps_ps_prot.result b/mysql-test/r/query_cache_ps_ps_prot.result new file mode 100644 index 00000000000..56aeda4a253 --- /dev/null +++ b/mysql-test/r/query_cache_ps_ps_prot.result @@ -0,0 +1,362 @@ +---- establish connection con1 (root) ---- +---- switch to connection default ---- +set @initial_query_cache_size = @@global.query_cache_size; +set @@global.query_cache_size=100000; +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 +---- switch to 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 +---- switch to 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 9 +---- switch to connection con1 ---- +SELECT * FROM t1 WHERE c1 = 100; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 10 +---- switch to connection default ---- +prepare stmt11 from "SELECT * FROM t1 WHERE c1 = 1"; +---- switch to connection con1 ---- +prepare stmt12 from "SELECT * FROM t1 WHERE c1 = 1"; +---- switch to connection default ---- +SELECT * FROM t1 WHERE c1 = 1; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 10 +SELECT * FROM t1 WHERE c1 = 1; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 11 +execute stmt11; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 11 +---- switch to connection con1 ---- +execute stmt12; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +---- switch to connection default ---- +prepare stmt1 from "select * from t1 where c1=?"; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +set @a=1; +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +---- switch to 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 12 +---- switch to 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 12 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +---- switch to connection con1 ---- +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +---- switch to connection default ---- +set global query_cache_size=100000; +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 12 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 13 +execute stmt1; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +---- switch to connection con1 ---- +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 15 +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 17 +---- switch to connection default ---- +set global query_cache_size=0; +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 17 +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 17 +---- switch to connection con1 ---- +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +---- switch to connection default ---- +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=10"; +---- switch to connection con1 ---- +prepare stmt3 from "select * from t1 where c1=10"; +---- switch to connection default ---- +set global query_cache_size=100000; +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 17 +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 17 +---- switch to connection con1 ---- +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +execute stmt3; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +---- switch to connection default ---- +set global query_cache_size=0; +prepare stmt1 from "select * from t1 where c1=?"; +set global query_cache_size=100000; +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +set @a=1; +execute stmt1 using @a; +c1 +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +set @a=100; +execute stmt1 using @a; +c1 +100 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +set @a=10; +execute stmt1 using @a; +c1 +10 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 17 +drop table t1; +---- disconnect connection con1 ---- +set @@global.query_cache_size=@initial_query_cache_size; +flush status; diff --git a/mysql-test/r/query_cache_sql_prepare.result b/mysql-test/r/query_cache_sql_prepare.result deleted file mode 100644 index 64af5bc4ec2..00000000000 --- a/mysql-test/r/query_cache_sql_prepare.result +++ /dev/null @@ -1,204 +0,0 @@ -set global query_cache_size=100000; -flush status; -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 -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 -select * from t1 where c1=10; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 9 -flush tables; -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 9 -select * from t1 where c1=10; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -prepare stmt1 from "select * from t1 where c1=?"; -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -set @a=1; -execute stmt1 using @a; -c1 -1 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -set @a=100; -execute stmt1 using @a; -c1 -100 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -set @a=10; -execute stmt1 using @a; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -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 10 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -set global query_cache_size=100000; -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 10 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 11 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -set global query_cache_size=0; -prepare stmt1 from "select * from t1 where c1=10"; -set global query_cache_size=100000; -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -execute stmt1; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -set global query_cache_size=0; -prepare stmt1 from "select * from t1 where c1=?"; -set global query_cache_size=100000; -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -set @a=1; -execute stmt1 using @a; -c1 -1 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -set @a=100; -execute stmt1 using @a; -c1 -100 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -set @a=10; -execute stmt1 using @a; -c1 -10 -show status like 'Qcache_hits'; -Variable_name Value -Qcache_hits 12 -drop table t1; -set global query_cache_size=0; -flush status; diff --git a/mysql-test/t/grant_cache_no_prot.test b/mysql-test/t/grant_cache_no_prot.test new file mode 100644 index 00000000000..1f6a9aeb9ed --- /dev/null +++ b/mysql-test/t/grant_cache_no_prot.test @@ -0,0 +1,25 @@ +#################### t/grant_cache_no_prot.test ################### +# +# Test grants with query cache to be run when mysqltest was started +# without any "--<whatever>-protocol". +# +# Last update: +# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc +# - Create this test as non "--<whatever>-protocol" variant. +# + +# We cannot run on embedded server because we use multiple sessions. +--source include/not_embedded.inc + +--source include/have_query_cache.inc + +# The file with expected results fits only to a run without +# ps-protocol/sp-protocol/cursor-protocol/view-protocol. +if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL + + $VIEW_PROTOCOL > 0`) +{ + --skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled +} + +# The main testing script +--source include/grant_cache.inc diff --git a/mysql-test/t/grant_cache_ps_prot.test b/mysql-test/t/grant_cache_ps_prot.test new file mode 100644 index 00000000000..7b579f869e9 --- /dev/null +++ b/mysql-test/t/grant_cache_ps_prot.test @@ -0,0 +1,24 @@ +#################### t/grant_cache_ps_prot.test ################## +# +# Test grants with query cache to be run when mysqltest was +# started with the option "--ps-protocol". +# +# Last update: +# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc +# - Create this test as "--ps-protocol" only variant. +# + +# We cannot run on embedded server because we use multiple sessions. +--source include/not_embedded.inc + +--source include/have_query_cache.inc + +# The file with expected results fits only to a run with "--ps-protocol". +if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0 + OR $PS_PROTOCOL = 0`) +{ + --skip Test requires: ps-protocol enabled, other protocols disabled +} + +# The main testing script +--source include/grant_cache.inc diff --git a/mysql-test/t/query_cache_ps_no_prot.test b/mysql-test/t/query_cache_ps_no_prot.test new file mode 100644 index 00000000000..6f4263eeef9 --- /dev/null +++ b/mysql-test/t/query_cache_ps_no_prot.test @@ -0,0 +1,26 @@ +#################### t/query_cache_ps_no_prot.test ##################### +# +# Test grants with query cache to be run when mysqltest was started +# without any "--<whatever>-protocol". +# +# Last update: +# 2007-05-03 ML - Move t/query_cache_sql_prepare.test to +# include/query_cache_sql_prepare.inc +# - Create this test as non "--<whatever>-protocol" variant. +# + +# We cannot run on embedded server because we use multiple sessions. +--source include/not_embedded.inc + +--source include/have_query_cache.inc + +# The file with expected results fits only to a run without +# ps-protocol/sp-protocol/cursor-protocol/view-protocol. +if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL + + $VIEW_PROTOCOL > 0`) +{ + --skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled +} + +# The main testing script +--source include/query_cache_sql_prepare.inc diff --git a/mysql-test/t/query_cache_ps_ps_prot.test b/mysql-test/t/query_cache_ps_ps_prot.test new file mode 100644 index 00000000000..dc49624788a --- /dev/null +++ b/mysql-test/t/query_cache_ps_ps_prot.test @@ -0,0 +1,25 @@ +#################### t/query_cache_ps_ps_prot.test ##################### +# +# Test grants with query cache to be run when mysqltest was started +# without any "--<whatever>-protocol". +# +# Last update: +# 2007-05-03 ML - Move t/query_cache_sql_prepare.test to +# include/query_cache_sql_prepare.inc +# - Create this test as "--ps-protocol" only variant. +# + +# We cannot run on embedded server because we use multiple sessions. +--source include/not_embedded.inc + +--source include/have_query_cache.inc + +# The file with expected results fits only to a run with "--ps-protocol". +if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0 + OR $PS_PROTOCOL = 0`) +{ + --skip Test requires: ps-protocol enabled, other protocols disabled +} + +# The main testing script +--source include/query_cache_sql_prepare.inc diff --git a/mysql-test/t/query_cache_sql_prepare.test b/mysql-test/t/query_cache_sql_prepare.test deleted file mode 100644 index a02388b2ae5..00000000000 --- a/mysql-test/t/query_cache_sql_prepare.test +++ /dev/null @@ -1,146 +0,0 @@ -# This is to see how statements prepared via the PREPARE SQL command -# go into the query cache: if using parameters they cannot; if not -# using parameters they can. -# Query cache is abbreviated as "QC" - --- source include/have_query_cache.inc -# embedded can't make more than one connection, which this test needs --- source include/not_embedded.inc - -connect (con1,localhost,root,,test,$MASTER_MYPORT,); -connection default; - -set global query_cache_size=100000; -flush status; -create table t1(c1 int); -insert into t1 values(1),(10),(100); - -# Prepared statements has no parameters, query caching should happen -prepare stmt1 from "select * from t1 where c1=10"; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -# Another prepared statement (same text, same connection), should hit the QC -prepare stmt2 from "select * from t1 where c1=10"; -execute stmt2; -show status like 'Qcache_hits'; -execute stmt2; -show status like 'Qcache_hits'; -execute stmt2; -show status like 'Qcache_hits'; -# Another prepared statement (same text, other connection), should hit the QC -connection con1; -prepare stmt3 from "select * from t1 where c1=10"; -execute stmt3; -show status like 'Qcache_hits'; -execute stmt3; -show status like 'Qcache_hits'; -execute stmt3; -show status like 'Qcache_hits'; -connection default; -# A non-prepared statement (same text, same connection), should hit -# the QC (as it uses the text protocol like SQL EXECUTE). -# But if it uses the binary protocol, it will not hit. So we make sure -# that it uses the text protocol: --- disable_ps_protocol -select * from t1 where c1=10; -show status like 'Qcache_hits'; - # A non-prepared statement (same text, other connection), should hit -# the QC. To test that it hits the result of SQL EXECUTE, we need to -# empty/repopulate the QC (to remove the result from the non-prepared -# SELECT just above). -flush tables; -execute stmt1; -show status like 'Qcache_hits'; -connection con1; -select * from t1 where c1=10; -show status like 'Qcache_hits'; --- enable_ps_protocol -connection default; - -# Prepared statement has parameters, query caching should not happen -prepare stmt1 from "select * from t1 where c1=?"; -show status like 'Qcache_hits'; -set @a=1; -execute stmt1 using @a; -show status like 'Qcache_hits'; -set @a=100; -execute stmt1 using @a; -show status like 'Qcache_hits'; -set @a=10; -execute stmt1 using @a; -show status like 'Qcache_hits'; - -# See if enabling/disabling the query cache between PREPARE and -# EXECUTE is an issue; the expected result is that the query cache -# will not be used. -# Indeed, decision to read/write the query cache is taken at PREPARE -# time, so if the query cache was disabled at PREPARE time then no -# execution of the statement will read/write the query cache. -# If the query cache was enabled at PREPARE time, but disabled at -# EXECUTE time, at EXECUTE time the query cache internal functions do -# nothing so again the query cache is not read/written. But if the -# query cache is re-enabled before another execution then that -# execution will read/write the query cache. - -# QC is enabled at PREPARE -prepare stmt1 from "select * from t1 where c1=10"; -# then QC is disabled at EXECUTE -set global query_cache_size=0; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -# then QC is re-enabled for more EXECUTE. -set global query_cache_size=100000; -# Note that this execution will not hit results from the -# beginning of the test (because QC has been emptied meanwhile by -# setting its size to 0). -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; - -# QC is disabled at PREPARE -set global query_cache_size=0; -prepare stmt1 from "select * from t1 where c1=10"; -# then QC is enabled at EXECUTE -set global query_cache_size=100000; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; -execute stmt1; -show status like 'Qcache_hits'; - -# QC is disabled at PREPARE -set global query_cache_size=0; -prepare stmt1 from "select * from t1 where c1=?"; -# then QC is enabled at EXECUTE -set global query_cache_size=100000; -show status like 'Qcache_hits'; -set @a=1; -execute stmt1 using @a; -show status like 'Qcache_hits'; -set @a=100; -execute stmt1 using @a; -show status like 'Qcache_hits'; -set @a=10; -execute stmt1 using @a; -show status like 'Qcache_hits'; - - -drop table t1; - -set global query_cache_size=0; -flush status; # reset Qcache status variables for next tests diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 53224cccc2d..7af90480e2a 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -2471,7 +2471,7 @@ static void test_ps_query_cache() "(2, 'hh', 'hh'), (1, 'ii', 'ii'), (2, 'ii', 'ii')"); myquery(rc); - for (iteration= TEST_QCACHE_ON; iteration < TEST_QCACHE_ON_OFF; iteration++) + for (iteration= TEST_QCACHE_ON; iteration <= TEST_QCACHE_ON_OFF; iteration++) { switch (iteration) @@ -2610,7 +2610,9 @@ static void test_ps_query_cache() case TEST_QCACHE_ON_OFF: /* should not have hit */ DIE_UNLESS(hits2-hits1 == 0); break; - case TEST_QCACHE_ON_WITH_OTHER_CONN: + case TEST_QCACHE_ON_WITH_OTHER_CONN: /* should have hit */ + DIE_UNLESS(hits2-hits1 == 1); + break; mysql_close(lmysql); mysql= org_mysql; } |