MySQL Bugs: #36822: Query Cache not updated by INSERT ... ON DUPLICATE KEY UPDATE ... query

Bug #36822	Query Cache not updated by INSERT ... ON DUPLICATE KEY UPDATE ... query
Submitted:	20 May 2008 16:27	Modified:	24 May 2008 20:50
Reporter:	Ben Kaap	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Query Cache INSERT ON DUPLICATE KEY

Description:
I have a table of local network data, updated frequently via server task.  We found that our reporting statement "SELECT MAX(seen_at) FROM table_name;" was returning incorrect results.  I narrowed it down to the Query Cache.  With the SQL_NO_CACHE in the SELECT query, the correct value is returned.

How to repeat:
Table:

CREATE TABLE `net_table` (
   `port_id` int(10) unsigned NOT NULL default '0',
   `mac_address` varchar(20) NOT NULL default '',
   `seen_at` datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (`port_id`,`mac_address`),
   KEY (`seen_at`)
) ENGINE-MyISAM DEFAULT CHARSET=utf8;

Table updated by:

INSERT INTO net_table (port_id, mac_address, seen_at) VALUES ('1', '00112233445566', NOW()) ON DUPLICATE KEY UPDATE seen_at = NOW();

Select query:

SELECT MAX(seen_at) FROM net_table;

Suggested fix:
The ON DUPLICATE KEY query should mark the table as having been changed so that the SELECT query would be removed from the cache.

Thank you for the report.

I can not repeat described behavior.

Please indicate method you use to check if query still is in cache.

I viewed the output on the webpage for which the database is the back-end.  I also ran the queries in my phpMyAdmin installation and the result is the same.  I changed the SELECT query to include SQL_NO_CACHE and got the correct/expected result, and then removed the SQL_NO_CACHE and got the same incorrect old value.

Just to check the reproduction, I started over with this code: (copy/pasted this time to avoid the unfortunate typo of my first entry)

-- Create table [NOTE: for the comments below, this is line 1]
CREATE TABLE `net_table` (
  `port_id` int(10) unsigned NOT NULL default '0',
  `mac_address` varchar(20) NOT NULL default '',
  `seen_at` datetime NOT NULL default '0000-00-00 00:00:00',
  UNIQUE KEY `port_id` (`port_id`,`mac_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Initilaize data
INSERT INTO net_table (port_id, seen_at) VALUES ('1', NOW()) ON DUPLICATE KEY UPDATE seen_at = NOW();
INSERT INTO net_table (port_id, mac_address, seen_at) VALUES ('2', '001122334455', NOW()) ON DUPLICATE KEY UPDATE seen_at = NOW();

-- Run SELECT query, which will be cached
SELECT MAX(seen_at) FROM net_table;

-- WAIT HERE for ~10 seconds to give a different timestamp from the NOW() function
-- then run the INSERT queries again (these lines are the same as lines 10,11
INSERT INTO net_table (port_id, seen_at) VALUES ('1', NOW()) ON DUPLICATE KEY UPDATE seen_at = NOW();
INSERT INTO net_table (port_id, mac_address, seen_at) VALUES ('2', '001122334455', NOW()) ON DUPLICATE KEY UPDATE seen_at = NOW();

-- Now, this SELECT query gives the same result as the SELECT query before;
-- The cached result from the INSERTs of lines 10,11
SELECT MAX(seen_at) FROM net_table;

-- Add the SQL_NO_CACHE and I get the more recent timestamp from lines 18,19
SELECT SQL_NO_CACHE MAX(seen_at) FROM net_table;

-- But running the old SELECT query again still gives the cached timestamps
-- from lines 10,11
SELECT MAX(seen_at) FROM net_table;

I could not repeat with your last test case too. Could you please provide
your my.ini file?. Thanks in advance.

requested my.ini file

Attachment: my.ini (application/octet-stream, text), 9.08 KiB.

I've added the requested file.  Also, I tested the same set of queries on my development server running 5.0.37 and the problem did NOT occur.  I'm reluctant to reinstall quite yet since there doesn't seem to be any other problem with the installation except for this one issue.  But if there doesn't appear to be anything wrong, and you can't reproduce it on another machine, perhaps it's time to start fresh with 51b.

Thank you for the feedback.

It was probably this bug -- http://bugs.mysql.com/bug.php?id=27210