| Bug #60448 | Query cache always misses with InnoDB schema with '-' in theirschema name. | ||
|---|---|---|---|
| Submitted: | 13 Mar 2011 4:36 | Modified: | 13 Mar 2011 11:25 |
| Reporter: | Craig Pearson | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S2 (Serious) |
| Version: | 5.5.9, 5.5.11 | OS: | Any (Centos 5.5, Mac OS X) |
| Assigned to: | CPU Architecture: | Any | |
[13 Mar 2011 9:38]
Valeriy Kravchuk
Verified just as described with current mysql-5.5 from bzr on Mac OS X:
macbook-pro:5.5 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 5
Server version: 5.5.11-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database `test-schema`;
Query OK, 1 row affected (0.01 sec)
mysql> create database `testschema`;
Query OK, 1 row affected (0.04 sec)
mysql> CREATE TABLE `test-schema`.test_innodb ( `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.18 sec)
mysql> CREATE TABLE `test-schema`.test_myisam ( `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE `testschema`.test_innodb ( `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)
mysql> CREATE TABLE `testschema`.test_myisam ( `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO `test-schema`.test_innodb (str) VALUES
-> ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `test-schema`.test_myisam (str) VALUES
-> ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `testschema`.test_innodb (str) VALUES
-> ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `testschema`.test_myisam (str) VALUES
-> ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
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.03 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> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 10476656 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM `test-schema`.test_innodb;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.03 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 10476656 |
| 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 `test-schema`.test_myisam;
+-----+-------+
| 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 | 10475120 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM `testschema`.test_innodb;
+-----+-------+
| 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 | 10473584 |
| Qcache_hits | 0 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM `test-schema`.test_innodb;
+-----+-------+
| 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 | 10473584 |
| Qcache_hits | 0 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM `testschema`.test_innodb;
+-----+-------+
| 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 | 10473584 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+----------+
8 rows in set (0.00 sec)
[13 Mar 2011 11:25]
Davi Arnaut
Closed as a duplicate of Bug#55556.
Description: InnoDB schema with hyphens in the names. Query cache NEVER caches queries using these schema. How to repeat: mysql> CREATE DATABASE `test-schema`; Query OK, 1 row affected (0.00 sec) mysql> CREATE DATABASE `testschema`; Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE `test-schema`.test_innodb ( `uid` INT AUTO_INCREMENT PRIMARY KEY, `str` VARCHAR(255) NOT NULL ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE `test-schema`.test_myisam ( `uid` INT AUTO_INCREMENT PRIMARY KEY, `str` VARCHAR(255) NOT NULL ) ENGINE=MYISAM; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `testschema`.test_innodb ( `uid` INT AUTO_INCREMENT PRIMARY KEY, `str` VARCHAR(255) NOT NULL ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `testschema`.test_myisam ( `uid` INT AUTO_INCREMENT PRIMARY KEY, `str` VARCHAR(255) NOT NULL ) ENGINE=MYISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `test-schema`.test_innodb (str) VALUES ('one'),('two'),('three'),('four'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `test-schema`.test_myisam (str) VALUES ('one'),('two'),('three'),('four'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `testschema`.test_innodb (str) VALUES ('one'),('two'),('three'),('four'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO `testschema`.test_myisam (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 | 207977 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM `test-schema`.test_innodb; +-----+-------+ | 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 | 219929 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM `test-schema`.test_myisam; +-----+-------+ | 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 | 228500 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM `testschema`.test_innodb; +-----+-------+ | 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 | 536849752 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 250905 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 7 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM `testschema`.test_myisam; +-----+-------+ | 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 | 536848216 | | Qcache_hits | 0 | | Qcache_inserts | 3 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 261019 | | Qcache_queries_in_cache | 3 | | Qcache_total_blocks | 10 | +-------------------------+-----------+ 8 rows in set (0.00 sec) Suggested fix: Scema names could be renamed to not use hyphens. However, most existing production systems this will be difficult.