Bug #64821 | Query Cache not used when table name contains dollar sign and engine is innodb | ||
---|---|---|---|
Submitted: | 30 Mar 2012 23:33 | Modified: | 11 Dec 2012 2:47 |
Reporter: | Ryan Streb | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Query Cache | Severity: | S2 (Serious) |
Version: | 5.1.61, 5.5.11 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | query cache innodb dollar sign |
[30 Mar 2012 23:33]
Ryan Streb
[31 Mar 2012 7:47]
Valeriy Kravchuk
Thank you for the bug report. Verified with 5.1.61 on Mac OS X also: macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.1.61-debug Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET global query_cache_size = 999424; Query OK, 0 rows affected (0.09 sec) mysql> mysql> CREATE TABLE query_cache_$test (ID int auto_increment primary key) ENGINE=INNODB; Query OK, 0 rows affected (0.18 sec) mysql> mysql> INSERT INTO query_cache_$test values (0); Query OK, 1 row affected (0.01 sec) mysql> show status like 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 990576 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+--------+ 8 rows in set (0.06 sec) mysql> select * from query_cache_$test; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.04 sec) mysql> select * from query_cache_$test; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> show status like 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 990576 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+--------+ 8 rows in set (0.01 sec) mysql> alter table query_cache_$test engine=MyISAM; Query OK, 1 row affected (0.29 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from query_cache_$test; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.02 sec) mysql> select * from query_cache_$test; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> show status like 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 989040 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+--------+ 8 rows in set (0.00 sec)
[11 Dec 2012 2:47]
Paul DuBois
Noted in 5.6.9, 5.7.0 changelogs. The server failed to use the query cache for queries in which a database or table name contained special characters and the table storage engine was InnoDB.