MySQL Bugs: #72547: Query cache not invalidated on cascade delete if db name has special symbols

Bug #72547	Query cache not invalidated on cascade delete if db name has special symbols
Submitted:	6 May 2014 10:49	Modified:	28 Aug 2014 17:48
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S2 (Serious)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://mariadb.atlassian.net/browse/MDEV-6213

Results of the test case from 'how to repeat' (a row is returned when the table is in fact 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 5.6 tree 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
  
...

How to repeat:
# Run with --mysqld=--query-cache-type=1

--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;

Hello Elena,

Thank you for the bug report and test case.
Verified as described on latest builds 5.6.18/19.

Thanks,
Umesh

// 5.6.19 - affected

MySQL Version 5.6.19
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/data/ushastry/server/mysql-advanced-5.6.19/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
SET @query_cache_size.saved = @@query_cache_size;
SET GLOBAL query_cache_size = 1024*1024;
DROP DATABASE IF EXISTS `db-db`;
Warnings:
Note    1008    Can't drop database 'db-db'; database doesn't exist
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;
id      t1_id
1       100
connection default;
delete from t1 where id=100;
select * from t2;
id      t1_id
1       100
select SQL_NO_CACHE * from t2;
id      t1_id

Noted in 5.6.21, 5.7.5 changelogs.

The query cache was not invalidated for a table when a CASCADE DELETE
or CASCADE UPDATE referential constraint was specified and the
database name or table name contained special characters.

revno: 6106
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.