MySQL Bugs: #64821: Query Cache not used when table name contains dollar sign and engine is innodb

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:	None
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

Description:
no SELECT's  run against InnoDB tables with a dollar sign ($) in the table's name are cacheable. 

MyISAM tables are not affected by this bug, only INNODB in my testing.

Reproducible in 5.1.52, 5.1.58 and 5.5.11 so far.

How to repeat:
#enable query cache;
SET global query_cache_size = 999424;

CREATE TABLE query_cache_$test (ID int auto_increment primary key) ENGINE=INNODB;

INSERT INTO query_cache_$test values (0);

#enable query profiling;
set profiling = 1;

#try to populate the cache:
select * from query_cache_$test;
+----+
| id |
+----+
|  1 |
+----+

#try to retrieve results from the cache:
select * from query_cache_$test;
+----+
| id |
+----+
|  1 |
+----+

# Below, notice no 'sending cached result to client' entry in results, so it's a cache miss.
 show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000019 |
| Waiting for query cache lock   | 0.000003 |
| checking query cache for query | 0.000032 |
| checking permissions           | 0.000009 |
| Opening tables                 | 0.000021 |
| System lock                    | 0.000009 |
| Waiting for query cache lock   | 0.000033 |
| init                           | 0.000012 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000010 |
| preparing                      | 0.000007 |
| executing                      | 0.000002 |
| Sending data                   | 0.000039 |
| end                            | 0.000004 |
| query end                      | 0.000004 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000023 |
| logging slow query             | 0.000007 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

#drop the dollar sign from table name;
ALTER TABLE query_cache_$test RENAME TO query_cache_test;

select * from query_cache_test;
+----+
| id |
+----+
|  1 |
+----+

select * from query_cache_test;
+----+
| id |
+----+
|  1 |
+----+

# without $ sign in table name, query is now served from cache.
mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000019 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000007 |
| sending cached result to clien | 0.000020 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000007 |
+--------------------------------+----------+

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)

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.