diff options
Diffstat (limited to 'mysql-test/t/grant_cache.test')
-rw-r--r-- | mysql-test/t/grant_cache.test | 112 |
1 files changed, 112 insertions, 0 deletions
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test new file mode 100644 index 00000000000..95989d1ee99 --- /dev/null +++ b/mysql-test/t/grant_cache.test @@ -0,0 +1,112 @@ +-- source include/have_query_cache.inc + +drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +drop database if exists mysqltest; + +# +# Test grants with query cache +# + +reset query cache; +flush status; +connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connection root; +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; +connect (root2,localhost,root,,mysqltest,$MASTER_MYPORT,master.sock); +connection root2; +# put queries in cache +select * from t1; +select a from t1; +select c from t1; +select * from t2; +select * from mysqltest.t1,test.t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits%"; + +# Create the test users +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; + +# The following queries should be fetched from cache +connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock); +connection user1; +select "user1"; +select * from t1; +# The pre and end space are intentional + select a from t1 ; +select c from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# The following queries should be fetched from cache +connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,master.sock); +connection user2; +select "user2"; +select * from t1; +select a from t1; +select c from t1; +select * from mysqltest.t1,test.t1; +--replace_result 127.0.0.1 localhost +--error 1142 +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 +connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,master.sock); +connection user3; +select "user3"; +--replace_result 127.0.0.1 localhost +--error 1143 +select * from t1; +select a from t1; +--replace_result 127.0.0.1 localhost +--error 1143 +select c from t1; +--replace_result 127.0.0.1 localhost +--error 1142 +select * from t2; +--replace_result 127.0.0.1 localhost +--error 1143 +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 +connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,master.sock); +connection user4; +select "user4"; +--error 1046 +select a from t1; +# The following query is not cached before (different database) +select * from mysqltest.t1,test.t1; +# Cache a query with 'no database' +select a from mysqltest.t1; +select a from mysqltest.t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Cleanup + +connection root; +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; |