1 files changed, 228 insertions, 0 deletions
diff --git a/mysql-test/include/grant_cache.inc b/mysql-test/include/grant_cache.inc
new file mode 100644
index 00000000..2d4e8345
--- /dev/null
+++ b/mysql-test/include/grant_cache.inc
@@ -0,0 +1,228 @@
+################### 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
+
+set @save_query_cache_size=@@global.query_cache_size;
+set @save_sql_mode=@@global.sql_mode;
+set @save_query_cache_type=@@global.query_cache_type;
+
+set GLOBAL sql_mode="";
+set LOCAL sql_mode="";
+
+#
+--disable_warnings
+drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
+drop database if exists mysqltest;
+--enable_warnings
+
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
+set GLOBAL query_cache_size=1355776;
+
+--disable_ps2_protocol
+
+reset query cache;
+flush status;
+connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+connection root;
+show grants for current_user;
+show grants;
+--disable_warnings
+create database if not exists mysqltest;
+--enable_warnings
+
+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_MYSOCK);
+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 test.t1 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;
+grant SELECT(a) on test.t1 to mysqltest_3@localhost;
+
+# The following queries should be fetched from cache
+connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
+connection user1;
+show grants for current_user();
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+show status like "Qcache_not_cached";
+select "user1";
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+show status like "Qcache_not_cached";
+select * from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+show status like "Qcache_not_cached";
+# The pre and end space are intentional
+ select a from t1 ;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+show status like "Qcache_not_cached";
+select c from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+show status like "Qcache_not_cached";
+
+
+# Don't use '' as user because it will pick Unix login
+connect (unkuser,localhost,unkuser,,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
+connection unkuser;
+show grants for current_user();
+
+# The following queries should be fetched from cache
+connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
+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 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
+connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
+connection user3;
+select "user3";
+--replace_result 127.0.0.1 localhost
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from t1;
+select a from t1;
+--replace_result 127.0.0.1 localhost
+--error ER_COLUMNACCESS_DENIED_ERROR
+select c from t1;
+--replace_result 127.0.0.1 localhost
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from t2;
+--replace_result 127.0.0.1 localhost
+--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
+connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
+connection user4;
+select "user4";
+show grants;
+--error ER_NO_DB_ERROR
+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";
+
+--enable_ps2_protocol
+
+# Cleanup
+
+connection root;
+disconnect root;
+--source include/wait_until_disconnected.inc
+connection root2;
+disconnect root2;
+--source include/wait_until_disconnected.inc
+connection user1;
+disconnect user1;
+--source include/wait_until_disconnected.inc
+connection user2;
+disconnect user2;
+--source include/wait_until_disconnected.inc
+connection user3;
+disconnect user3;
+--source include/wait_until_disconnected.inc
+connection user4;
+disconnect user4;
+--source include/wait_until_disconnected.inc
+connection unkuser;
+disconnect unkuser;
+--source include/wait_until_disconnected.inc
+connection default;
+
+#
+# A temporary 4.1 workaround to make this test pass if
+# mysql was compiled with other than latin1 --with-charset=XXX.
+# Without "set names binary" the below queries fail with
+# "Illegal mix of collations" error.
+# In 5.0 we will change grant tables to use NCHAR(N) instead
+# of "CHAR(N) BINARY", and use cast-to-nchar:  N'mysqltest_1'.
+#
+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_type=ON;
+set LOCAL query_cache_type=ON;
+
+--source include/delete_anonymous_users.inc
+set GLOBAL query_cache_size=@save_query_cache_size;
+set GLOBAL sql_mode=@save_sql_mode;
+set GLOBAL query_cache_type=@save_query_cache_type;