MySQL Bugs: #67922: queries with "comments" may prevents query

Bug #67922	queries with "comments" may prevents query_cache hits
Submitted:	17 Dec 2012 13:01	Modified:	7 Jul 2017 9:08
Reporter:	xiaobin lin (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.1.68, 5.5.30, 5.7.1, 5.0.97	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	comments, query_cache

Description:
in comment syntax, mysql allow this format /*!VERSION xxxx */

if the query_cache is on, and when the VERSION is large than he version server. The statement can be inserted into query_cache, but can not be used.

How to repeat:

mysql> show status like '%qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 31417680 |
| Qcache_hits             | 31       |
| Qcache_inserts          | 21       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 132      |
| Qcache_queries_in_cache | 21       |
| Qcache_total_blocks     | 44       |
+-------------------------+----------+

select * from tb /*!121221 limit 1*/

mysql> show status like '%qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 31416656 |
| Qcache_hits             | 31       |
| Qcache_inserts          | 22       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 133      |
| Qcache_queries_in_cache | 22       |
| Qcache_total_blocks     | 46       |
+-------------------------+----------+

select * from tb /*!121221 limit 1*/

mysql> show status like '%qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 31416656 |
| Qcache_hits             | 31       |
| Qcache_inserts          | 22       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 134      |
| Qcache_queries_in_cache | 22       |
| Qcache_total_blocks     | 46       |
+-------------------------+----------+

Qcache_inserts increases, but subsequent same queries lead to increasing of Qcache_not_cached but not Qcache_hits.

Suggested fix:

The reason is when the search the result in the query_cache, the origial is used. But when parse the statement, the '!' is replaced with a ' '. So the subsequent same queies can not match the new inserted item.

have not think of good method yet..

Thank you for the report.

Verified as described.

In version 5.7.1 Qcache_inserts never increases.

fyi, the reason  the '!' is replaced with a ' ' is so that the version comment has no meaning on a slave...

Shane 
Yes, it can be used to deal with the /*!VERSION */,when the VERSION is bigger than master-version, but smaller than slave-version.

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/