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
Triage: Needs Triage: D3 (Medium)

[26 Jul 2010 11:59] Thomas Hoffmann
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%';
[26 Jul 2010 12:07] Valeriy Kravchuk
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)
[13 Mar 2011 11:26] Davi Arnaut
Bug#60448 has been closed as a duplicate of this one.
[15 Jul 2011 12:54] Rob Janssen
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...
[15 Jul 2011 13:09] Rob Janssen
I can also confirm that the same problem exists when the >>table<< name contains a minus. Underscores work.
[15 Jul 2011 13:15] Rob Janssen
...and 5.5.11-log still has this bug (confirmed)
[23 Aug 2011 14:50] Olivier Doucet
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.
[23 Aug 2011 15:09] Valeriy Kravchuk
Bug #62237 was marked as a duplicate of this one.
[5 Nov 2012 12:27] Jon Olav Hauglid
Bug will be fixed by patch for Bug#64821.