MySQL 5.5.9 Query Cache not working when schemas have hyphens ("-") in their name

Question

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)

Please provide an exact example of a query that you believe should be cached, but is not. Also include the `SHOW CREATE TABLE ...` output for the tables used in that query. Please also provide the output of `SHOW GRANTS FOR user@host` for the user that is performing these queries. — hobodave, Mar 13 '11 at 02:30
Updated. The only difference between the two schemas and tables is the charset. — thepearson, Mar 13 '11 at 03:08
It is seems to be limited to InnoDB tables, when the schema has a hyphen in the name. MyISAM work fine when hyphen is in the schema name. I am using innodb_file_per_table. I have registered a bug with MySQL [here](http://bugs.mysql.com/bug.php?id=60448) Someone else verified it on MacOSX, I'll wait and see what the fix is and post an updated answer soon. — thepearson, Mar 13 '11 at 09:42

score 6 · Accepted Answer · answered Mar 13 '11 at 20:13

6

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.

answered Mar 13 '11 at 20:13

thepearson

870
10
18

score 0 · Answer 2 · answered Mar 12 '11 at 23:56

0

Your query cache settings look fine. See How the Query Cache Operates for details on what types of SELECT queries won't be cached and see if anything applies to you case. Also check the MySQL error log for any relevant messages.

What you can also do is issue a very simple SELECT query that you know should be cached and check the Qcache_hits both before and after the query. Try creating a small test database/table to exclude any possible issues with your existing tables. If that doesn't work you know something more subtle is wrong with MySQL.

answered Mar 12 '11 at 23:56

uesp

3,414
1
18
16

Thanks. So created a new test db and innod table. Queries on this test table are hitting the Qcache. SO what would cause MySQL to ignore queries on other existing Schema and/or tables. I have recently done an upgrade from 5.0.32 to 5.5.9, using mysqldump files. – thepearson Mar 13 '11 at 00:11
Sorry, some more info on how the upgrade was performed. We provisioned new server hardware, installed MySQL 5.5.9, Dumped (mysqldump)existing databases and tables (excluding information_schema, mysql) ezported user grants, then imported these SQL into the new server. SO more of a "migration" than an Upgrade. – thepearson Mar 13 '11 at 00:26
Do simple test queries on the existing tables use the query cache or just the new test table? Only thing I can think of at the moment is is the existing tables are being constantly updated/written to. Try dumping or restoring a backup of an existing table to a new database/table and see if that works or doesn't. – uesp Mar 13 '11 at 01:01
OK creating a new table in one of the existing schema, populating it and querying it results in Qcache misses. Creating a new schema and the same table (this time as part of the new schema), pupulating it again and then querying this table does actually get picked up and cached in the Qcache. Is there certian schema based settings of types that prevent Qcache to work? – thepearson Mar 13 '11 at 02:19
Not that I know of, although this is somewhat outside of my knowledge/experience. Try more tests and see exactly what schemas work and don't work for you. Once you get more info you can try another SF question or try on the MySQL forums. – uesp Mar 13 '11 at 13:41

MySQL 5.5.9 Query Cache not working when schemas have hyphens ("-") in their name

2 Answers2