Description:
A simple query does not get into cache, based on the database name : if the db name has a '-' (minus) it does not work. This problem seems to happen only if engine is InnoDB.
This has been tested on 3 different servers with different config files :
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
I tried to debug this behaviour : it seems the function dict0dict.c::dict_table_get() returned NULL. 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.
How to repeat:
CREATE DATABASE `test-name`;
USE `test-name`;
CREATE TABLE `test-name`.`mytable`( `id` INT NOT NULL , `name` VARCHAR(128) , PRIMARY KEY (`id`) ) engine=InnoDB;
INSERT INTO `test-name`.`mytable`(`id`,`name`) VALUES ( '1','hello');
INSERT INTO `test-name`.`mytable`(`id`,`name`) VALUES ( '2','world');
reset query cache;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 0
SELECT SQL_CACHE * FROM mytable WHERE id=2;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 0
ALTER TABLE `test-name`.`mytable` ENGINE = MYISAM;
SELECT SQL_CACHE * FROM mytable WHERE id=2 LIMIT 0, 1000;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 1
ALTER TABLE `test-name`.`mytable` ENGINE = INNODB;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 0
SELECT * FROM mytable WHERE id=1;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 0
-----------------------------------------------------------------------
Here is what happen with different database name :
CREATE DATABASE `testname`;
USE `testname`;
CREATE TABLE `testname`.`mytable`( `id` INT NOT NULL , `name` VARCHAR(128) , PRIMARY KEY (`id`) ) Engine=InnoDB;
INSERT INTO `testname`.`mytable`(`id`,`name`) VALUES ( '1','hello');
INSERT INTO `testname`.`mytable`(`id`,`name`) VALUES ( '2','world');
reset query cache;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 0
SELECT SQL_CACHE * FROM mytable WHERE id=2;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 1
ALTER TABLE `testname`.`mytable` ENGINE = MYISAM;
SELECT SQL_CACHE * FROM mytable WHERE id=2 LIMIT 0, 1000;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 1
ALTER TABLE `testname`.`mytable` ENGINE = INNODB;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 0
SELECT * FROM mytable WHERE id=1;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
=> 1
