1################### include/grant_cache.inc #################### 2# 3# Test grants with query cache 4# 5# Last update: 6# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc 7# - Remove the disabling of the ps-protocol 8# - minor improvements like error names instead of numbers 9# - Create two toplevel tests sourcing this routine 10# 11# Running this test with and without "--ps-protocol" produces different 12# Qcache_not_cached results because of the following reason: 13# In normal protocol, a SELECT failing due to insufficient privileges 14# increments Qcache_not_cached, while in ps-protocol, no. 15# In detail: 16# - In normal protocol, 17# the "access denied" errors on SELECT are issued at (stack trace): 18# mysql_parse/mysql_execute_command/execute_sqlcom_select/handle_select/ 19# mysql_select/JOIN::prepare/setup_wild/insert_fields/ 20# check_grant_all_columns/my_error/my_message_sql, which then calls 21# push_warning/query_cache_abort: at this moment, 22# query_cache_store_query() has been called, so query exists in cache, 23# so thd->net.query_cache_query!=NULL, so query_cache_abort() removes 24# the query from cache, which causes a query_cache.refused++ (thus, 25# a Qcache_not_cached++). 26# - In ps-protocol, 27# the error is issued at prepare time; 28# for this mysql_test_select() is called, not execute_sqlcom_select() 29# (and that also leads to JOIN::prepare/etc). Thus, as 30# query_cache_store_query() has not been called, 31# thd->net.query_cache_query==NULL, so query_cache_abort() does nothing: 32# Qcache_not_cached is not incremented. 33# 34# A run of this tests with sp/cursor/view protocol does not make sense 35# because these protocols serve totally different purposes than this test. 36# 37 38--source include/add_anonymous_users.inc 39 40set @save_query_cache_size=@@global.query_cache_size; 41set @save_sql_mode=@@global.sql_mode; 42set @save_query_cache_type=@@global.query_cache_type; 43 44set GLOBAL sql_mode=""; 45set LOCAL sql_mode=""; 46 47# 48--disable_warnings 49drop table if exists test.t1,mysqltest.t1,mysqltest.t2; 50drop database if exists mysqltest; 51--enable_warnings 52 53set GLOBAL query_cache_type=ON; 54set LOCAL query_cache_type=ON; 55set GLOBAL query_cache_size=1355776; 56 57reset query cache; 58flush status; 59connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); 60connection root; 61show grants for current_user; 62show grants; 63--disable_warnings 64create database if not exists mysqltest; 65--enable_warnings 66 67create table mysqltest.t1 (a int,b int,c int); 68create table mysqltest.t2 (a int,b int,c int); 69insert into mysqltest.t1 values (1,1,1),(2,2,2); 70insert into mysqltest.t2 values (3,3,3); 71create table test.t1 (a char (10)); 72insert into test.t1 values ("test.t1"); 73select * from t1; 74connect (root2,localhost,root,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); 75connection root2; 76# put queries in cache 77select * from t1; 78select a from t1; 79select c from t1; 80select * from t2; 81select * from mysqltest.t1,test.t1; 82show status like "Qcache_queries_in_cache"; 83show status like "Qcache_hits%"; 84 85# Create the test users 86grant SELECT on mysqltest.* to mysqltest_1@localhost; 87grant SELECT on mysqltest.t1 to mysqltest_2@localhost; 88grant SELECT on test.t1 to mysqltest_2@localhost; 89grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; 90 91# The following queries should be fetched from cache 92connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); 93connection user1; 94show grants for current_user(); 95show status like "Qcache_queries_in_cache"; 96show status like "Qcache_hits"; 97show status like "Qcache_not_cached"; 98select "user1"; 99show status like "Qcache_queries_in_cache"; 100show status like "Qcache_hits"; 101show status like "Qcache_not_cached"; 102select * from t1; 103show status like "Qcache_queries_in_cache"; 104show status like "Qcache_hits"; 105show status like "Qcache_not_cached"; 106# The pre and end space are intentional 107 select a from t1 ; 108show status like "Qcache_queries_in_cache"; 109show status like "Qcache_hits"; 110show status like "Qcache_not_cached"; 111select c from t1; 112show status like "Qcache_queries_in_cache"; 113show status like "Qcache_hits"; 114show status like "Qcache_not_cached"; 115 116 117# Don't use '' as user because it will pick Unix login 118connect (unkuser,localhost,unkuser,,,$MASTER_MYPORT,$MASTER_MYSOCK); 119connection unkuser; 120show grants for current_user(); 121 122# The following queries should be fetched from cache 123connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); 124connection user2; 125select "user2"; 126select * from t1; 127select a from t1; 128select c from t1; 129select * from mysqltest.t1,test.t1; 130--replace_result 127.0.0.1 localhost 131--error ER_TABLEACCESS_DENIED_ERROR 132select * from t2; 133show status like "Qcache_queries_in_cache"; 134show status like "Qcache_hits"; 135show status like "Qcache_not_cached"; 136 137# The following queries should not be fetched from cache 138connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); 139connection user3; 140select "user3"; 141--replace_result 127.0.0.1 localhost 142--error ER_TABLEACCESS_DENIED_ERROR 143select * from t1; 144select a from t1; 145--replace_result 127.0.0.1 localhost 146--error ER_COLUMNACCESS_DENIED_ERROR 147select c from t1; 148--replace_result 127.0.0.1 localhost 149--error ER_TABLEACCESS_DENIED_ERROR 150select * from t2; 151--replace_result 127.0.0.1 localhost 152--error ER_COLUMNACCESS_DENIED_ERROR 153select mysqltest.t1.c from test.t1,mysqltest.t1; 154show status like "Qcache_queries_in_cache"; 155show status like "Qcache_hits"; 156show status like "Qcache_not_cached"; 157 158# Connect without a database 159connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); 160connection user4; 161select "user4"; 162show grants; 163--error ER_NO_DB_ERROR 164select a from t1; 165# The following query is not cached before (different database) 166select * from mysqltest.t1,test.t1; 167# Cache a query with 'no database' 168select a from mysqltest.t1; 169select a from mysqltest.t1; 170show status like "Qcache_queries_in_cache"; 171show status like "Qcache_hits"; 172show status like "Qcache_not_cached"; 173 174# Cleanup 175 176connection root; 177disconnect root; 178--source include/wait_until_disconnected.inc 179connection root2; 180disconnect root2; 181--source include/wait_until_disconnected.inc 182connection user1; 183disconnect user1; 184--source include/wait_until_disconnected.inc 185connection user2; 186disconnect user2; 187--source include/wait_until_disconnected.inc 188connection user3; 189disconnect user3; 190--source include/wait_until_disconnected.inc 191connection user4; 192disconnect user4; 193--source include/wait_until_disconnected.inc 194connection unkuser; 195disconnect unkuser; 196--source include/wait_until_disconnected.inc 197connection default; 198 199# 200# A temporary 4.1 workaround to make this test pass if 201# mysql was compiled with other than latin1 --with-charset=XXX. 202# Without "set names binary" the below queries fail with 203# "Illegal mix of collations" error. 204# In 5.0 we will change grant tables to use NCHAR(N) instead 205# of "CHAR(N) BINARY", and use cast-to-nchar: N'mysqltest_1'. 206# 207set names binary; 208delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); 209delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); 210delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); 211delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3"); 212flush privileges; 213drop table test.t1,mysqltest.t1,mysqltest.t2; 214drop database mysqltest; 215 216set GLOBAL query_cache_type=ON; 217set LOCAL query_cache_type=ON; 218 219--source include/delete_anonymous_users.inc 220set GLOBAL query_cache_size=@save_query_cache_size; 221set GLOBAL sql_mode=@save_sql_mode; 222set GLOBAL query_cache_type=@save_query_cache_type; 223