MySQL Bugs: #39062: Query cache not always invalidated from slave SQL thread?

Bug #39062	Query cache not always invalidated from slave SQL thread?
Submitted:	27 Aug 2008 4:56	Modified:	6 Jul 2010 8:42
Reporter:	Arjen Lentz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S2 (Serious)
Version:	5.0.51b,5.0.67	OS:	Windows (Windows 2008 Server)
Assigned to:		CPU Architecture:	Any
Tags:	query cache, replication

Description:
SELECT ... returns different results from SELECT SQL_NO_CACHE ...
The underlying table gets updated by slave SQL thread.

The server config has a pile of replicate wildcards and so on, but this should affect qcache invalidation eh.

The slave is a Win2k8 server; don't know if that is relevant.
We'll shortly be able to try same with an Ubuntu server.

How to repeat:
Sorry haven't got an isolated case to reproduce this right now.

Suggested fix:
Verify qcache interaction with slave SQL thread

Arjen,

Please, check with slave running 5.0.67, if possible.

Hi Valeriy: also happens on 5.0.67

Thank you for the feedback.

Please try to find which query does not force query cache to invalidate. Also please provide configuration files for both master and slave: I could not repeat described behavior with simple UPDATE query.

It was an update, but there's a old replication filtering in place also. I'm getting the config, table schema and the exact update query for you to work with.

Following: snippets from my.cnf, schema, data, query.
I can also confirm that the query cache problem does NOT occur on Linux, with near-identical my.cnf (same weird replication filtering as below).

# relevant blah from the my.cnf  (it's garbage and shouldn't be used like this but so tightly integrated
# with the rest of the clients' system that we can't quite get rid of it all yet ;-)
# ===

replicate-wild-ignore-table=foo_bar.%
replicate-wild-ignore-table=blah_banana_bar.%
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=UserListLog.%
replicate-wild-ignore-table=ErrorLogs.%
replicate-wild-ignore-table=apple.tblActivityLog
replicate-wild-ignore-table=apple.tblErrors

binlog-ignore-db=blah_banana_bar
binlog-ignore-db=foo_bar

binlog-ignore-db=mysql
binlog-ignore-db=userlist
binlog-ignore-db=UserList
binlog-ignore-db=Plonk
binlog-ignore-db=CopyAll

# ===

# Database: UserList
# Table: 'tblUsers'
# 
CREATE TABLE `tblUsers` (
  `UserID` int(6) NOT NULL default '0',
  `UserName` varchar(30) NOT NULL default '',
  `UserInitials` varchar(5) NOT NULL default '',
  `DateActivated` datetime NOT NULL default '1901-01-01 00:00:00',
  `DateDeActivated` datetime default '1901-01-01 00:00:00',
  `BaseCampID` tinyint(4) default '0',
  `SecLevel` smallint(6) NOT NULL default '0',
  `Position` smallint(6) NOT NULL default '0',
  `Available` tinyint(4) NOT NULL default '0',
  `Password` varchar(20) NOT NULL default '',
  `ChangePassword` tinyint(4) default '0',
  `Comments` mediumtext,
  `DontAutoPasswordChange` tinyint(4) default '0',
  `StoreArea` int(6) NOT NULL default '0',
  `PayrollCode` int(6) default '0',
  `NonEmployee` tinyint(4) NOT NULL default '0',
  `NonEmployeeType` varchar(20) default NULL,
  `UserLocked` tinyint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`UserID`),
  KEY `Index_NameAndAvailable` (`UserName`,`Available`),
) TYPE=MyISAM COMMENT='The Users Master List'; 

Here an insert:
INSERT INTO tblUsers VALUES (1607,'Mickey Mouse','MM','2003-09-03 00:00:00',NULL,1,7,7,1,'34c8d0f46ecbb6f2',0,'yeah',0,1,47,0,NULL,0);

The update statement (on master!) that reveals the issue:
Update tblUsers Set ChangePassword = 0 Where UserId = 1607;
or
Update tblUsers Set ChangePassword = 1 Where UserId = 1607;

Then
SELECT * FROM tblUsers\G
will not show the change on the slave, whereas
SELECT SQL_NO_CACHE * FROM tblUsers\G
will.
So the data is replicated, but somehow the query cache is not invalidated.

I hope that with the above extra config and details you will now be able to recreate this problem!

Thank you for the feedback.

You indicated option:

binlog-ignore-db=UserList

And later before SHOW CREATE TABLE

# Database: UserList
# Table: 'tblUsers'

Is this option from slave or from master?

The config provided is for the slave - the master config should be completely irrelevant.
UserList is excluded from going into the binlog (log-slave-updates) because if a problem occurs a slave will get reset anyway. In fact I think currently log-slave-updates is not even active so the binlog ignore lines don't matter. But anyway. I figured you'd use the exact same replication settings as it might help recreate the problem.

Arjen,

sorry, missed hidden comment. No luck with generic tests so far.

I can not repeat described behavior with various generic tests. So closing report as "Can't repeat". If you meet same problem again please try to find circumstances problem is repeatable under.

This can be fixed in 5.1 by bug fix for bug #39253 also.