From: Date: August 15 2007 3:48pm
Subject: bk commit into 5.0 tree (thek:1.2480) BUG#30269
List-Archive: http://lists.mysql.com/commits/32562
X-Bug: 30269
Message-Id: <20070815134810.840CD5300FF@Adventure>

Below is the list of changes that have just been committed into a local
5.0 repository of thek. When thek 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-08-15 15:48:06+02:00, thek@adventure.(none) +3 -0
  Bug #30269 Query cache eats memory
  
  Although the query cache doesn't support retrieval of statements containing
  column level access control, it was still possible to cache such statements
  thus wasting memory.
  
  This patch extends the access control check on the target tables to avoid
  caching a statement with column level restrictions. 

  mysql-test/r/query_cache.result@stripped, 2007-08-15 15:48:04+02:00, thek@adventure.(none) +19 -0
    Added test

  mysql-test/t/query_cache.test@stripped, 2007-08-15 15:48:04+02:00, thek@adventure.(none) +26 -1
    Added test

  sql/sql_parse.cc@stripped, 2007-08-15 15:48:04+02:00, thek@adventure.(none) +24 -0
    The function check_table_access leaves the artifact
    grant.want_privileges= 1, if a statement refers to tables with column level
    privileges. To avoid the statement from being stored into the query cache,
    it is enough to check this flag and set 'safe_to_cache_query' to zero.

diff -Nrup a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
--- a/mysql-test/r/query_cache.result	2007-07-12 15:30:32 +02:00
+++ b/mysql-test/r/query_cache.result	2007-08-15 15:48:04 +02:00
@@ -1502,6 +1502,25 @@ a	(select count(*) from t2)
 3	0
 4	0
 drop table t1,t2;
+DROP DATABASE IF EXISTS bug30269;
+CREATE DATABASE bug30269;
+USE bug30269;
+CREATE TABLE test1 (id int, name varchar(23));
+INSERT INTO test1 VALUES (5, 'testit');
+GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
+set global query_cache_size= 81920;
+USE bug30269;
+show status like 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	0
+SELECT id FROM test1 WHERE id>2;
+id
+5
+show status like 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	0
+DROP DATABASE bug30269;
+DROP USER 'bug30269'@'localhost';
 set GLOBAL query_cache_type=default;
 set GLOBAL query_cache_limit=default;
 set GLOBAL query_cache_min_res_unit=default;
diff -Nrup a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
--- a/mysql-test/t/query_cache.test	2007-07-12 15:30:16 +02:00
+++ b/mysql-test/t/query_cache.test	2007-08-15 15:48:04 +02:00
@@ -1096,9 +1096,34 @@ connection default;
 disconnect user1;
 disconnect user2;
 disconnect user3;
+
+#
+# Bug #30269 Query cache eats memory
+#
+--disable_warnings
+DROP DATABASE IF EXISTS bug30269;
+--enable_warnings
+CREATE DATABASE bug30269;
+USE bug30269;
+CREATE TABLE test1 (id int, name varchar(23));
+INSERT INTO test1 VALUES (5, 'testit');
+GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
+set global query_cache_size= 81920;
+connect (bug30269, localhost, bug30269,,);
+connection bug30269;
+USE bug30269;
+show status like 'Qcache_queries_in_cache';
+SELECT id FROM test1 WHERE id>2;
+show status like 'Qcache_queries_in_cache';
+
+connection default;
+DROP DATABASE bug30269;
+disconnect bug30269;
+DROP USER 'bug30269'@'localhost';
+
 set GLOBAL query_cache_type=default;
 set GLOBAL query_cache_limit=default;
 set GLOBAL query_cache_min_res_unit=default;
 set GLOBAL query_cache_size=default;
-# End of 5.0 tests
 
+# End of 5.0 tests
diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc
--- a/sql/sql_parse.cc	2007-07-16 21:40:30 +02:00
+++ b/sql/sql_parse.cc	2007-08-15 15:48:04 +02:00
@@ -2616,10 +2616,34 @@ mysql_execute_command(THD *thd)
     {
       if (lex->orig_sql_command != SQLCOM_SHOW_STATUS_PROC &&
           lex->orig_sql_command != SQLCOM_SHOW_STATUS_FUNC)
+      {
         res= check_table_access(thd,
                                 lex->exchange ? SELECT_ACL | FILE_ACL :
                                 SELECT_ACL,
                                 all_tables, 0);
+#ifdef HAVE_QUERY_CACHE
+        {
+          TABLE_LIST *table;
+          TABLE_LIST *first_not_own_table= thd->lex->first_not_own_table();
+          /*
+            Disable any attempt to store this statement if there are
+            column level grants on any referenced tables.
+          */
+          for (table= all_tables;
+                table && table != first_not_own_table;
+                table= table->next_global)
+          {
+            if (table->grant.want_privilege)
+            {
+              DBUG_PRINT("qcache", ("Don't cache statement as it refers to "
+                                    "tables with column privileges."));
+              thd->lex->safe_to_cache_query= 0;
+              break;
+            }
+          }
+        }
+#endif
+      }
     }
     else
       res= check_access(thd,