Bug #30269 Query cache eats memory
Submitted: 7 Aug 2007 7:09 Modified: 31 Aug 2007 15:38
Reporter: Vadim TKACHENKO Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Kristofer Pettersson
Tags: qc

[7 Aug 2007 7:09] Vadim TKACHENKO
Description:
Query against table with column level privileges can't be used from query_cache, but it is stored in qcache and therefore waste memory of query_cache.

How to repeat:
Under root
CREATE TABLE xp.test1 (id int, name varchar(23));
INSERT INTO xp.test1 VALUES (5, 'testit');
GRANT SELECT (id) ON TABLE xp.test1 TO 'vadim'@'localhost';

Under user vadim
Before query execution:
mysql> SHOW global STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name		   | Value
+-------------------------+----------+
| Qcache_free_memory	  | 67091176 
| Qcache_hits			 | 0
| Qcache_inserts		  | 0
| Qcache_queries_in_cache | 0
+-------------------------+----------+
8 rows IN SET (0.01 sec)
Query:
mysql> SELECT id FROM xp.test1 WHERE id> 1;
+------+
| id   
+------+
|	5 
+------+
1 row IN SET (0.00 sec)
After query execution:
mysql> SHOW global STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name		   | Value
+-------------------------+----------+
| Qcache_free_memory	  | 67089640 
| Qcache_hits			 | 0
| Qcache_inserts		  | 1
| Qcache_queries_in_cache | 1
+-------------------------+----------+

Suggested fix:
Add additional check on table with column level privileges in code
of function storing result in query cache .
The same as in function query_cache_send_result_to_client
[7 Aug 2007 7:16] Valerii Kravchuk
Thank you for a bug report. Verified just as described.
[8 Aug 2007 20:54] Martin Friebe
I am not sure, but I don't see the Problem with caching this query.

It is true the user vadim can not read it back from the cache, due to his usage of column level privileges.

But the query cache is global, all users are sharing it.

Log in as root after Vadim executed the select, and execute the same select as root (or any user which isn't restricted by his privileges).
Executing the query as root, will hit the query cache. So the stored result was no loss at all.
[9 Aug 2007 10:35] Peter Zaitsev
Martin,

If other users use this query  they would populate the query cache quickly by themselves so it still looks like most likely waste to me. 

I think  the consistent way would be either not store the query in case of column level privileges or make them work.
[15 Aug 2007 13:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32562

ChangeSet@1.2480, 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.
[17 Aug 2007 12:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32696

ChangeSet@1.2480, 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.
[17 Aug 2007 14:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32700

ChangeSet@1.2480, 2007-08-17 16:55:20+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.
[21 Aug 2007 11:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32805

ChangeSet@1.2481, 2007-08-21 13:43:09+02:00, thek@adventure.(none) +5 -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. 
  
  Views are excepted and can be cached but only retrieved by super user account.
[21 Aug 2007 23:21] Bugs System
Pushed into 5.1.22-beta
[21 Aug 2007 23:22] Bugs System
Pushed into 5.0.48
[27 Aug 2007 13:07] Paul Dubois
Pushed to 5.1.23, not 5.1.22.
[31 Aug 2007 15:38] Paul Dubois
Noted in 5.0.48, 5.1.23 changelogs.

The query cache does not support retrieval of statements for which 
column level access control applies, but the server was still caching
such statements, thus wasting memory.