MySQL 5.5.9 Query Cache not working when schemas have hyphens ("-") in their name
I am running MySQL 5.5.9 x86_64 RPM as downloaded from mysql.com. Running on CentOS 5.5 Xen DomU.
I have enabled the Query_cache however MySQL NEVER uses it. All of my tables are InnoDB. Why is the Qcache never hit?
UPDATE 2: I have found this is limited to schemas with - in the name. Creating a new Schema eg new-db, query cache fails. Unfortunatly I have 148 existing Schama, all with '-' in their names.
UPDATE this appears to be limited to schemas that were dumped and imported from a pervious version of MySQL (5.0.32) Creating a new schema and querying tables in this query cache works as expected.
Here are my settings and examples of Qc working and not working.
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
6 rows in set (0.00 sec)
mysql> USE `existing-schema`;
Database changed
mysql> CREATE TABLE test (
-> `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536852824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 56725 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536852824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 89824 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> CREATE DATABASE new;
Query OK, 1 row affected (0.00 sec)
mysql> USE new;
Database changed
mysql> CREATE TABLE test (
-> `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536852824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 89824 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536851288 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 109528 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536851288 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 126100 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SHOW CREATE DATABASE new;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| new | CREATE DATABASE `new` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SHOW CREATE DATABASE `existing-schema`;
+------------------+---------------------------------------------------------------------------+
| Database | Create Database |
+------------------+---------------------------------------------------------------------------+
| ezlead-live-data | CREATE DATABASE `existing-schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[REMOVED]' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Solution 1:
This is a bug in MySQL. I guess the only solution currently is to rename my database schema, so that they don't contain hyphens.