1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
-- source include/have_query_cache.inc
#
# Test grants with query cache
#
drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
reset query cache;
flush status;
connect (root,localhost,root,,test,0,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,0,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,0,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,0,master.sock);
connection user2;
select "user2";
select * from t1;
select a from t1;
select c from t1;
select * from mysqltest.t1,test.t1;
--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,0,master.sock);
connection user3;
select "user3";
--error 1143
select * from t1;
select a from t1;
--error 1143
select c from t1;
--error 1142
select * from t2;
--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*,0,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;
|
