MySQL Bugs: #55556: Query Cache not used when database name contains minus and table is innodb

Bug #55556	Query Cache not used when database name contains minus and table is innodb
Submitted:	26 Jul 2010 11:59	Modified:	5 Nov 2012 12:27
Reporter:	Thomas Hoffmann	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S2 (Serious)
Version:	5.1.49, 5.1.50-bzr, 5.5.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	query cache innodb

Description:
Hello,

when the database name contains a minus-sign (db-test) and the table type
is innodb, the query cache is not used.

When database name is changed to "db" for example everything works.

How to repeat:
CREATE TABLE `test1` (
 `nr` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from test1;

SHOW STATUS LIKE '%Qcache%';

Verified just as described on Mac OS X:

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 6
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

mysql> set global query_cache_size=10*1024*1024;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'query%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 8192     |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 8192     |
+------------------------------+----------+
7 rows in set (0.00 sec)

mysql> create database db-test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test' at line 1
mysql> create database `db-test`;
Query OK, 1 row affected (0.00 sec)

mysql> use db-test;
Database changed
mysql> create table ti(c1 int primary key, c2 int) engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10476672 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from ti;
Empty set (0.00 sec)

mysql> select * from ti;
Empty set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10476672 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 3        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table ti(c1 int primary key, c2 int) engine=InnoDB;
Query OK, 0 rows affected (0.39 sec)

mysql> select * from ti;
Empty set (0.00 sec)

mysql> select * from ti;
Empty set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10475136 |
| Qcache_hits             | 1        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 4        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

Bug#60448 has been closed as a duplicate of this one.

You have _GOT_ to be kidding me. I've spent about a day figuring out why the query cache wouldn't work. It never-ever crossed my mind to switch to another database; in all my days worth of tweaking all kinds of my.cnf values, restarting, upgrading and trying hundreds of variations of queries with and without forcing SQL_CACHE nothing worked.

My database name contains several minuses (DEV-foo-bar-v3) on MySQL v5.1.58-log which I have upgraded from v5.1.44 just hoping that that would fix it. I can also reproduce this problem on v5.0.30-log. All on Gentoo.

Glad I finally found this...

I can also confirm that the same problem exists when the >>table<< name contains a minus. Underscores work.

...and 5.5.11-log still has this bug (confirmed)

Verified as described in the following versions : 
5.1.49-3 (Debian) X86_64 debian-linux-gnu
5.5.8 (Source) X86_64 Linux
5.1.58 (Percona version) X86_64 Linux
5.5.14 MySQL Community Server (GPL) x84_64
5.5.14 Percona Server (rel 20.5)

I just created a duplicate of this bug (http://bugs.mysql.com/bug.php?id=62237) and only saw this a few seconds ago ...

What happened internally : query cache asks the storage engine if the query can be cached. This one calls the function dict0dict.c::dict_table_get() but it returned NULL.

AFAIK, stack is : 
Query_cache::store_query()
 Query_cache::ask_handler_allowance()
  ha_innobase::register_query_cache_table()
    innobase_query_caching_of_table_permitted()
      row/row0sel.c: row_search_check_if_query_cache_permitted()
           [ code extract: ]
	   table = dict_table_get(norm_name, FALSE);
	   if (table == NULL) {
		return(FALSE);
	    }
      => false
     => false
   => false

I'm not a MySQL developer, but I think this is because internal innodb name seems to be test@002fname/mytable , though different from 'test-name/mytable' 
=> function dict_table_get() cannot find it and returned NULL.

This may be fixed in handler/ha_innodb.cc function innobase_query_caching_of_table_permitted(). In this function, there is a memcpy to "normalize the table name to InnoDB format" and I think this is here an error is made.

Please correct me if I'm wrong.

This bug is opened for more than a year. I hope my explanations will help someone to fix this asap.

Bug #62237 was marked as a duplicate of this one.

Bug will be fixed by patch for Bug#64821.