Details
-
Type:
Bug
-
Status: Stalled
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.0.10
-
Fix Version/s: 10.0
-
Component/s: None
-
Labels:
-
Sprint: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.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It is problem of not storing two variants of table names with special characters (escaped and non-escaped) and one parts of server uses escaped names other non-escaped (higher level).
innodb sends escaped db name but QC operate with unescaped.
commited for review:
[Commits] Rev 4207: MDEV-6213: Query cache is not invalidated on cascade delete when database name contains special symbols. in file:///home/bell/maria/bzr/work-maria-10.0-MDEV-6213/
ok? should be close?
no, not yet. it was reviewed, but is not pushed yet.
Still reproducible on 10.0.14+.
The upstream bug was fixed in 5.6.21:
revno: 6106
revision-id: nisha.gopalakrishnan@oracle.com-20140802075108-bde46mg1j23vutp0
parent: venkata.sidagam@oracle.com-20140801114115-v50hsa6ojtvl2z22
committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
branch nick: mysql-5.6-18710853
timestamp: Sat 2014-08-02 13:21:08 +0530
message:
BUG#18710853: QUERY CACHE NOT INVALIDATED ON CASCADE DELETE
IF DB NAME HAS SPECIAL SYMBOLS
Analysis
--------
The query cache is not invalidated for a table when the CASCADE
DELETE/UPDATE referential constraint is specified and the
database name or table name contains special characters.
InnoDB triggers invalidation of the query cache while performing
the check for CASCADE DELETE/UPDATE referential constraint. InnoDB
passes the key in the format of 'dbname\0tablename' to the query cache
interface where the database name and table name are in the canonical
format(encoded-format for special characters). The key used by the query
cache interface is 'dbname\0tablename' in its non-canonical format.
The lookup performed for query cache invalidation fails for the condition
specified above due to the mismatch in the key.
Hence the records fetched with the query cache and without the query
cache differs.
Fix
---
Innodb now passes the key 'dbname\0tablename' in its non-canonical format to
the query cache interface. Thus the query cache look up succeeds and the
query is invalidated.
Assigning to Oleksandr Byelkin – even although it's an upstream but, maybe you'll want to fix it your own way.