Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.10
-
10.0.20
Description
Originally reported by leo.unglaub on IRC.
Also reproducible on MySQL 5.6, 5.7.
Test case:
--source include/have_innodb.inc
|
--source include/have_query_cache.inc
|
--enable_connect_log
|
|
SET @query_cache_size.saved = @@query_cache_size; |
SET GLOBAL query_cache_size = 1024*1024; |
|
DROP DATABASE IF EXISTS `db-db`; |
CREATE DATABASE `db-db`; |
USE `db-db`; |
|
--connect (con2,localhost,root,,db-db)
|
|
create table t1 (id int primary key) ENGINE=InnoDB; |
create table t2 ( |
id int primary key, |
t1_id int, |
constraint t2_fk foreign key (t1_id) references t1 (id) on delete cascade |
) ENGINE=InnoDB;
|
|
insert into t1 values (100); |
insert into t2 values (1,100); |
|
select * from t2; |
|
--connection default
|
|
delete from t1 where id=100; |
select * from t2; |
select SQL_NO_CACHE * from t2; |
|
DROP DATABASE `db-db`; |
SET GLOBAL query_cache_size = @query_cache_size.saved; |
Result (the row is returned while in fact the table is already empty):
delete from t1 where id=100;
|
select * from t2;
|
id t1_id
|
1 100
|
select SQL_NO_CACHE * from t2;
|
id t1_id
|
The problem appeared in MySQL 5.6 with the following revision:
revno: 4514
|
revision-id: nisha.gopalakrishnan@oracle.com-20121031062656-4advnrgfltkxsu4y
|
parent: nuno.carvalho@oracle.com-20121030162613-vvq9dnc1hbh3pmlq
|
committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
|
branch nick: mysql-5.6-13919851
|
timestamp: Wed 2012-10-31 11:56:56 +0530
|
message:
|
BUG#13919851:QUERY CACHE NOT USED WHEN TABLE NAME CONTAINS
|
DOLLAR SIGN AND ENGINE IS INNODB
|
|
Analysis
|
--------
|
|
The query cache is not used when the database name or the table
|
name contains special characters and the engine used is INNODB.
|
Ex: CREATE TABLE 'db-test'.'query$table'(fld int) ENGINE=INNODB;
|
|
While storing the query, the query cache interface checks with
|
the storage engine module to determine whether the query can be
|
cached. INNODB uses the canonical format
|
('db@002dtest/query@0024table') as the key to find the
|
corresponding table object.
|
|
The query cache interface passes the key which is a string of
|
the format 'databasename/0tablename' in a non-canonical format
|
('db-test/0query$table'). Hence the hash value generated is
|
different and the table object is not found.
|
|
So when the query cache interface checks with storage engine
|
to determine whether the query can be cached, the storage
|
engine INNODB does not find the corresponding table object.
|
Hence the query is not cached.
|
|
Fix
|
---
|
|
a) The query cache interface now passes the normalized path to
|
the table in its canonical form to the storage engines.
|
|
b) The piece of code in the routine
|
'innobase_query_caching_of_table_permitted' used to normalize the
|
table name is replaced with an existing macro 'normalize_table_name'
|
which does the same.
|
|
c) THE NDB handler code which used to perform the conversion of the
|
key to canonical format has been removed. This is because the query
|
cache interface now passes the canonical format of the key to the
|
storage engines.
|