MySQL Bugs: #30269: Query cache eats memory

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	CPU Architecture:	Any
Tags:	qc

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

Thank you for a bug report. Verified just as described.

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.

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.

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.

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.

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.

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.

Pushed into 5.1.22-beta

Pushed into 5.0.48

Pushed to 5.1.23, not 5.1.22.

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.