| Bug #55032 | Query cache sometime insert queries to cache, but doenn't find this queries. | ||
|---|---|---|---|
| Submitted: | 6 Jul 2010 16:52 | Modified: | 7 Jul 2017 9:45 |
| Reporter: | Oleg Tsarev | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
| Version: | All versions (5.1.x, 5.5.x) | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[7 Jul 2010 4:04]
Valeriy Kravchuk
Thank you for the bug report. Verified on Mac OS X: mysql> show status like 'Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 8379584 | | Qcache_hits | 7 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 681 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> ( SELECT * from t1 ); +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> ( SELECT * from t1 ); +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) mysql> show status like 'Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 8378048 | | Qcache_hits | 7 | | Qcache_inserts | 3 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 682 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) ...
[7 Jul 2010 16:55]
Oleg Tsarev
Patch fot this in single file: http://bazaar.launchpad.net/~percona-dev/percona-server/release-5.1.48-12/annotate/head%3A...
[13 Jul 2010 1:12]
Omer Barnir
triage: setting tag to SRMRTBD
[7 Jul 2017 9:45]
Erlend Dahl
MySQL will no longer invest in the query cache, see: http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
Description: Current query cache does find queryies only following forms: 1) Any number of "(" (without brackets too) 2) "SELECT" or C-style comments. Examples: "( SELECT ...)" "( SELECT ...)" "( # comment SELECT ...)" This queries would be inserted to query cache, but never found. I can write many queryies with this behavior. I want fix coverage all syntax correct form of queries. How to repeat: I have tests for this: http://bazaar.launchpad.net/~percona-dev/percona-server/release-5.1.48-12/files/head:/mysq... All files with name started by "percona_query_cache_with_comments". tsarev@main:~$ ls -1 mysql-test/percona_query_cache_with_comments* mysql-test/percona_query_cache_with_comments_begin.inc mysql-test/percona_query_cache_with_comments_clear.inc mysql-test/percona_query_cache_with_comments_crash.result mysql-test/percona_query_cache_with_comments_crash.test mysql-test/percona_query_cache_with_comments_disable.result mysql-test/percona_query_cache_with_comments_disable.test mysql-test/percona_query_cache_with_comments_end.inc mysql-test/percona_query_cache_with_comments_eval.inc mysql-test/percona_query_cache_with_comments.inc mysql-test/percona_query_cache_with_comments.inc.backup mysql-test/percona_query_cache_with_comments_prepared_statements.result mysql-test/percona_query_cache_with_comments_prepared_statements.test mysql-test/percona_query_cache_with_comments.result mysql-test/percona_query_cache_with_comments_show.inc mysql-test/percona_query_cache_with_comments.test Simple case: > show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 > show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 0 > show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 > ( SELECT * from t1 ); a 1 2 3 > ( SELECT * from t1 ); a 1 2 3 > show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 > show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 > show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 I expected in last output: > show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 > show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 > show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 Suggested fix: Apply three patches from Percona-Serverin following order: 1) http://bazaar.launchpad.net/~percona-dev/percona-server/release-5.1.48-12/annotate/head:/q... 2) http://bazaar.launchpad.net/~percona-dev/percona-server/release-5.1.48-12/annotate/head:/q... 3) http://bazaar.launchpad.net/~percona-dev/percona-server/release-5.1.48-12/annotate/head:/q... This fixes change behavior of mysql server to correct work with this trouble, and also fix bug number 40986 But this patches has GPL license... You can contact with Percona-Server managment about change license of this patches.