List:Commits« Previous MessageNext Message »
From:mleich Date:May 24 2007 8:13pm
Subject:bk commit into 5.1 tree (mleich:1.2514) BUG#735
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of mleich. When mleich does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-05-24 22:13:49+02:00, mleich@stripped +11 -0
  This changeset belongs to the fix of Bug#735 Prepared Statements: there is no support for Query Cache
    
  - Create "--ps-protocol" and no "--<whatever>-protocol" variants of the former tests
    t/grant_cache.test and t/query_cache_sql_prepare.test.
  - Some additional subtest and fixes of bugs
  - Minor improvements 

  mysql-test/include/grant_cache.inc@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +59 -41
    - Rename mysql-test/t/grant_cache.test to include/grant_cache.inc
    - Toplevel scripts running variants of this test are
         t/grant_cache_ps_prot.test (runs only with "--ps-protocol")
         t/grant_cache_no_prot.test (skipped if any protocol is assigned)
    - Modifications to include/grant_cache.inc:
      - Minor improvements like replace --error <number> by --error <name>
      - enable that some subtests are run with "--ps-protocol"

  mysql-test/include/grant_cache.inc@stripped, 2007-05-24 16:51:06+02:00, mleich@stripped +0 -0
    Rename: include/grant_cache.inc -> mysql-test/include/grant_cache.inc

  include/grant_cache.inc@stripped, 2007-05-24 16:49:53+02:00, mleich@stripped +0 -0
    Rename: mysql-test/t/grant_cache.test -> include/grant_cache.inc

  mysql-test/include/query_cache_sql_prepare.inc@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +147 -22
    - Rename t/query_cache_sql_prepare.test to include/query_cache_sql_prepare.inc
    - Toplevel scripts running variants of this test are
         query_cache_ps_ps_prot.test (skipped if other protocol than --ps-protocol is used.)
         query_cache_ps_no_prot.test (skipped if --<whatever>-protocol is used)   
    - Modifications to include/query_cache_sql_prepare.inc:
    - Minor improvements like add drop table
    - 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

  mysql-test/include/query_cache_sql_prepare.inc@stripped, 2007-05-24 17:05:40+02:00, mleich@stripped +0 -0
    Rename: mysql-test/t/query_cache_sql_prepare.test -> mysql-test/include/query_cache_sql_prepare.inc

  mysql-test/r/grant_cache_no_prot.result@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +8 -0
    Updated result

  mysql-test/r/grant_cache_no_prot.result@stripped, 2007-05-24 16:56:39+02:00, mleich@stripped +0 -0
    Rename: mysql-test/r/grant_cache.result -> mysql-test/r/grant_cache_no_prot.result

  mysql-test/r/grant_cache_ps_prot.result@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +218 -0
    New BitKeeper file ``mysql-test/r/grant_cache_ps_prot.result''

  mysql-test/r/grant_cache_ps_prot.result@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +0 -0

  mysql-test/r/query_cache_ps_no_prot.result@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +192 -34
    Updated result

  mysql-test/r/query_cache_ps_no_prot.result@stripped, 2007-05-24 17:51:23+02:00, mleich@stripped +0 -0
    Rename: mysql-test/r/query_cache_sql_prepare.result -> mysql-test/r/query_cache_ps_no_prot.result

  mysql-test/r/query_cache_ps_ps_prot.result@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +362 -0
    New BitKeeper file ``mysql-test/r/query_cache_ps_ps_prot.result''

  mysql-test/r/query_cache_ps_ps_prot.result@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +0 -0

  mysql-test/t/grant_cache_no_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +25 -0
    Variant of the test grant_cache to be run without any "--<whatever>-protocol"

  mysql-test/t/grant_cache_no_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +0 -0

  mysql-test/t/grant_cache_ps_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +24 -0
    Variant of the test grant_cache to be run with "--ps-protocol" only

  mysql-test/t/grant_cache_ps_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +0 -0

  mysql-test/t/query_cache_ps_no_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +26 -0
    Variant of the test query_cache_sql_prepare to be run without any "--<whatever>-protocol"

  mysql-test/t/query_cache_ps_no_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +0 -0

  mysql-test/t/query_cache_ps_ps_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +25 -0
    Variant of the test query_cache_sql_prepare to be run with "--ps-protocol" only

  mysql-test/t/query_cache_ps_ps_prot.test@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +0 -0

  tests/mysql_client_test.c@stripped, 2007-05-24 22:13:47+02:00, mleich@stripped +4 -2
    - correct wrong sized "for" loop
    - add some missing tests of query cache hit numbers

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	mleich
# Host:	four.local.lan
# Root:	/WORK/mysql-5.1-runtime/src-1

--- 1.1/mysql-test/r/query_cache_sql_prepare.result	2007-03-09 18:09:54 +01:00
+++ 1.3/mysql-test/r/query_cache_ps_no_prot.result	2007-05-24 22:13:47 +02:00
@@ -1,5 +1,9 @@
-set global query_cache_size=100000;
+---- 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";
@@ -43,6 +47,7 @@
 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
@@ -62,143 +67,296 @@
 show status like 'Qcache_hits';
 Variable_name	Value
 Qcache_hits	8
-select * from t1 where c1=10;
+---- 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
-10
+100
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	9
-flush tables;
-execute stmt1;
+Qcache_hits	8
+execute stmt10;
 c1
-10
+100
 show status like 'Qcache_hits';
 Variable_name	Value
 Qcache_hits	9
-select * from t1 where c1=10;
+SELECT * FROM t1 WHERE c1 = 100;
 c1
-10
+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	10
+Qcache_hits	14
 set @a=1;
 execute stmt1 using @a;
 c1
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	10
-set @a=100;
+Qcache_hits	14
 execute stmt1 using @a;
 c1
-100
+1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	10
-set @a=10;
-execute stmt1 using @a;
+Qcache_hits	14
+---- switch to connection con1 ----
+set @a=1;
+prepare stmt4 from "select * from t1 where c1=?";
+execute stmt4 using @a;
 c1
-10
+1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	10
+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	10
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	10
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	10
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	10
+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	10
+Qcache_hits	14
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	11
+Qcache_hits	15
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+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	12
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	19
 execute stmt1;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+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	12
+Qcache_hits	19
 set @a=1;
 execute stmt1 using @a;
 c1
 1
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	19
 set @a=100;
 execute stmt1 using @a;
 c1
 100
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	19
 set @a=10;
 execute stmt1 using @a;
 c1
 10
 show status like 'Qcache_hits';
 Variable_name	Value
-Qcache_hits	12
+Qcache_hits	19
 drop table t1;
-set global query_cache_size=0;
+---- disconnect connection con1 ----
+set @@global.query_cache_size=@initial_query_cache_size;
 flush status;

--- 1.3/mysql-test/t/query_cache_sql_prepare.test	2007-03-27 12:06:07 +02:00
+++ 1.5/mysql-test/include/query_cache_sql_prepare.inc	2007-05-24 22:13:47 +02:00
@@ -1,17 +1,35 @@
+############### 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
+--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 global query_cache_size=100000;
+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);
 
@@ -33,6 +51,7 @@
 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;
@@ -41,25 +60,57 @@
 show status like 'Qcache_hits';
 execute stmt3;
 show status like 'Qcache_hits';
+--echo ---- switch to connection default ----
 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;
+
+# 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';
- # 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;
+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;
-select * from t1 where c1=10;
+eval $my_stmt;
 show status like 'Qcache_hits';
--- enable_ps_protocol
+--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
@@ -68,12 +119,16 @@
 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;
+--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
@@ -90,6 +145,7 @@
 # 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;
@@ -98,9 +154,24 @@
 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;
-# Note that this execution will not hit results from the
+# 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;
@@ -109,10 +180,50 @@
 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';
@@ -122,7 +233,19 @@
 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=?";
@@ -141,6 +264,8 @@
 
 
 drop table t1;
+--echo ---- disconnect connection con1 ----
+disconnect con1;
 
-set global query_cache_size=0;
+set @@global.query_cache_size=@initial_query_cache_size;
 flush status; # reset Qcache status variables for next tests
--- New file ---
+++ mysql-test/r/grant_cache_ps_prot.result	07/05/24 22:13:47
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;

--- New file ---
+++ mysql-test/r/query_cache_ps_ps_prot.result	07/05/24 22:13:47
---- 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;

--- New file ---
+++ mysql-test/t/grant_cache_no_prot.test	07/05/24 22:13:47
#################### 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

--- New file ---
+++ mysql-test/t/grant_cache_ps_prot.test	07/05/24 22:13:47
#################### 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

--- New file ---
+++ mysql-test/t/query_cache_ps_no_prot.test	07/05/24 22:13:47
#################### 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

--- New file ---
+++ mysql-test/t/query_cache_ps_ps_prot.test	07/05/24 22:13:47
#################### 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


--- 1.17/mysql-test/r/grant_cache.result	2006-06-20 12:20:27 +02:00
+++ 1.19/mysql-test/r/grant_cache_no_prot.result	2007-05-24 22:13:47 +02:00
@@ -3,6 +3,7 @@
 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 @@
 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.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 @@
 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 @@
 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 @@
 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 @@
 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");

--- 1.19/mysql-test/t/grant_cache.test	2007-03-19 22:37:25 +01:00
+++ 1.22/mysql-test/include/grant_cache.inc	2007-05-24 22:13:47 +02:00
@@ -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 @@
 
 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 @@
 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(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_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 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 @@
 
 # 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 @@
 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

--- 1.238/tests/mysql_client_test.c	2007-05-23 00:19:32 +02:00
+++ 1.239/tests/mysql_client_test.c	2007-05-24 22:13:47 +02:00
@@ -2471,7 +2471,7 @@
                           "(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 @@
     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;
     }
Thread
bk commit into 5.1 tree (mleich:1.2514) BUG#735mleich24 May