MySQL Bugs: #64164: 5.5.16 waits for the query cache even with SQL_NO

Bug #64164	5.5.16 waits for the query cache even with SQL_NO_CACHE hint
Submitted:	30 Jan 2012 14:54	Modified:	6 Mar 2013 16:50
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.5.16 GPL	OS:	Linux (CentOS 5.4 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	windmill parser query cache

Description:
With the query cache enabled on a box we see that a query that explicitly says SQL_NO_CACHE still waits on the cache. That seems silly.

How to repeat:
Sample SHOW PROCESSLIST output (edited) to remove sensitive info:

| 5962833 | user2 | some-host:38467 | my_db   | Sleep   |     54 |                                | NULL                            |
| 5962905 | user1 | some-host:44169 | my_db   | Sleep   |      0 |                                | NULL                            |
| 5962908 | user1 | some-host:44171 | my_db   | Query   |      0 | storing result in query cache  | SELECT ...                      |
| 5962909 | user1 | some-host:44173 | my_db   | Query   |      0 | Waiting for query cache lock   | SELECT SQL_NO_CACHE ...         |
| 5962911 | user1 | some-host:44175 | my_db   | Query   |      0 | Waiting for query cache lock   | SELECT id FROM table2 WHERE ... |
| 5962912 | user1 | some-host:44177 | my_db   | Query   |      0 | Waiting for query cache lock   | SELECT id FROM table1 ...       |

Suggested fix:
Do not wait on query cache if SQL_NO_CACHE is given as a hint.

Looks very similar to bug #37416. I wonder what last test case from James Day presented there shows in your environment. 

It would be nice to see my.cnf content also and storage engine used for that table(s) accessed by SELECT.

I provided further input to Oracle privately and it seems the issue has been identified.

The problem was reproduced using this test case under 5.5.20 :

with query_cache_type = 1, query_cache_size = 32M, max_connections=1000

create database qc;
use qc;
create table t(id int auto_increment, c1 int, primary key(id)) engine=InnoDB;
insert into t(id, c1) values(0,1);

then 

./bin/mysqlslap -uroot -p --delimiter=$ --create-schema=qc --query=slap.sql -c 900 --iterations=100000

where slap.sql contains 100 statements as :

select SQL_NO_CACHE
* from t where 1=1$

Notice the carriage return after the SQL_NO_CACHE keyword.

In this case :

************************** 898. row ***************************
     Id: 13961818
   User: root
   Host: localhost
     db: qc
Command: Query
   Time: 0
  State: Waiting for query cache lock
   Info: select SQL_NO_CACHE
* from t where 1=1
898 rows in set (0.00 sec)

If you add a space after the SQL_NO_CACHE, there is no more lock and less connections (900 to 22) :

*************************** 22. row ***************************
     Id: 13974245
   User: root
   Host: localhost
     db: qc
Command: Query
   Time: 0
  State: Opening tables
   Info: select SQL_NO_CACHE 
* from t where 1=1
22 rows in set (0.00 sec)

the slap.sql file used to reproduce the bug

Attachment: slap.sql (application/octet-stream, text), 3.95 KiB.

Same file as slap.sql with a space after SQL_NO_CACHE

Attachment: slap_ok.sql (application/octet-stream, text), 4.04 KiB.

Yes, but also note with the http://lists.mysql.com/commits/68592 patch that an explicit space is also checked for _before_ the SQL_NO_CACHE keyword, so both these checks probably need to use something like isspace() or something functionally equivalent.

Noted in 5.7.1 changelog.

The SQL_NO_CACHE keyword is supposed to prevent the server from 
checking the query cache to see whether the query result is already
cached, and to prevent it from caching the query result. However, the
query cache check was suppressed only if SQL_NO_CACHE was preceded
and followed by space characters. (For example, the server checked
the cache if the keyword was followed by a newline.) Now the parser
requires that the preceding and following characters be whitespace