List:Commits« Previous MessageNext Message »
From:kpettersson Date:August 17 2007 12:31pm
Subject:bk commit into 5.0 tree (thek:1.2480) BUG#30269
View as plain text  
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-17 14:31:35+02:00, thek@adventure.(none) +4 -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-17 14:31:34+02:00, thek@adventure.(none) +24 -0
    Added test

  mysql-test/t/query_cache.test@stripped, 2007-08-17 14:31:34+02:00, thek@adventure.(none) +29 -1
    Added test

  sql/sql_cache.cc@stripped, 2007-08-17 14:31:34+02:00, thek@adventure.(none) +21 -3
    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.

  sql/sql_cache.h@stripped, 2007-08-17 14:31:34+02:00, thek@adventure.(none) +5 -3
    - Removed 'static' attribute or class methods
    - Added THD parameter to process_and_count_tables

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-17 14:31:34 +02:00
@@ -1502,6 +1502,30 @@ 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));
+CREATE VIEW view1 AS SELECT id FROM test1;
+INSERT INTO test1 VALUES (5, 'testit');
+GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
+GRANT SELECT (id) ON TABLE bug30269.view1 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
+SELECT id FROM view1 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-17 14:31:34 +02:00
@@ -1096,9 +1096,37 @@ 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));
+CREATE VIEW view1 AS SELECT id FROM test1;
+INSERT INTO test1 VALUES (5, 'testit');
+GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
+GRANT SELECT (id) ON TABLE bug30269.view1 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;
+SELECT id FROM view1 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_cache.cc b/sql/sql_cache.cc
--- a/sql/sql_cache.cc	2007-05-10 14:54:53 +02:00
+++ b/sql/sql_cache.cc	2007-08-17 14:31:34 +02:00
@@ -2991,14 +2991,31 @@ void Query_cache::double_linked_list_joi
     >0  number of tables
 */
 
-static TABLE_COUNTER_TYPE process_and_count_tables(TABLE_LIST *tables_used,
-                                                   uint8 *tables_type)
+TABLE_COUNTER_TYPE
+Query_cache::process_and_count_tables(THD *thd, TABLE_LIST *tables_used,
+                                      uint8 *tables_type)
 {
   DBUG_ENTER("process_and_count_tables");
   TABLE_COUNTER_TYPE table_count = 0;
   for (; tables_used; tables_used= tables_used->next_global)
   {
     table_count++;
+#ifdef HAVE_QUERY_CACHE
+    /*
+      Disable any attempt to store this statement if there are
+      column level grants on any referenced tables.
+      The grant.want_privileges flag was set to 1 in the
+      check_grant() function earlier if the TABLE_LIST object
+      had any associated column privileges.
+    */
+    if (tables_used->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;
+      DBUG_RETURN(0);
+    }
+#endif
     if (tables_used->view)
     {
       DBUG_PRINT("qcache", ("view: %s  db: %s",
@@ -3071,7 +3088,8 @@ Query_cache::is_cacheable(THD *thd, uint
                           (long) lex->select_lex.options,
                           (int) thd->variables.query_cache_type));
 
-    if (!(table_count= process_and_count_tables(tables_used, tables_type)))
+    if (!(table_count= process_and_count_tables(thd, tables_used,
+                                                tables_type)))
       DBUG_RETURN(0);
 
     if ((thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) &&
diff -Nrup a/sql/sql_cache.h b/sql/sql_cache.h
--- a/sql/sql_cache.h	2007-01-22 13:10:40 +01:00
+++ b/sql/sql_cache.h	2007-08-17 14:31:34 +02:00
@@ -368,10 +368,12 @@ protected:
     If query is cacheable return number tables in query
     (query without tables not cached)
   */
-  static
   TABLE_COUNTER_TYPE is_cacheable(THD *thd, uint32 query_len, char *query,
-				  LEX *lex, TABLE_LIST *tables_used,
-				  uint8 *tables_type);
+                                  LEX *lex, TABLE_LIST *tables_used,
+                                  uint8 *tables_type);
+  TABLE_COUNTER_TYPE process_and_count_tables(THD *thd,
+                                              TABLE_LIST *tables_used,
+                                              uint8 *tables_type);
 
   static my_bool ask_handler_allowance(THD *thd, TABLE_LIST *tables_used);
  public:
Thread
bk commit into 5.0 tree (thek:1.2480) BUG#30269kpettersson17 Aug
  • Re: bk commit into 5.0 tree (thek:1.2480) BUG#30269Konstantin Osipov17 Aug