ON DUPLICATE KEY UPDATE and query cache

[Mr_Money]

After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

Any suggestions?

Thanks in advance

[HarryR]

The MySQL "REPLACE INTO ...." syntax should reset the query cache for that table and performs an almost similar action to what you want to do (or it seems so to me).

Please keep me updated on this, would be nice to see what happens.

[Dan Grossman]

Turn off query caching, or use the SQL_NO_CACHE hint in your SELECT.

[HarryR]

or use the SQL_NO_CACHE hint in your SELECT.

Yes, but it's handy having query caching in between updates

[Mr_Money]

The MySQL "REPLACE INTO ...." syntax should reset the query cache for that table and performs an almost similar action to what you want to do (or it seems so to me).

Please keep me updated on this, would be nice to see what happens.

thanks for the suggestion, that is what I am going with for the time being...

[Mr_Money]

Turn off query caching, or use the SQL_NO_CACHE hint in your SELECT.

Hi Dan, thanks for the suggestions. I would still like to have the benefits of query caching though.
I was under the impression that query cache was flushed whenever there was a write to the appropriate table.
From the mysql docs:

Note: The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

Im not sure why in my case it is not being flushed...

[Dan Grossman]

That was my understanding as well, and it may well be a bug. Are you running a pretty recent version of MySQL?

[Mr_Money]

That was my understanding as well, and it may well be a bug. Are you running a pretty recent version of MySQL?

I'm running 5.0.37 on Windows XP.
I was wondering if it were just some setting in my config file, but I'm not sure what that would be.

[longneck]

can you post the query cache settings you are using, plus the CREATE TABLE statement and the UPDATE statement? i want to try and duplicate the situation over here to report a possible bug.

[Mr_Money]

can you post the query cache settings you are using, plus the CREATE TABLE statement and the UPDATE statement? i want to try and duplicate the situation over here to report a possible bug.

Here are the settings I currently have (an 'out-of-the-box' WAMP install of the latest version):
query_cache_size=8M
table_cache=510
thread_cache_size=12
innodb_buffer_pool_size=15M

The actual query:

create table `friend_request_details` (
	`friend_request_id` int(11) not null,
	`details_status` enum('confirmed','unconfirmed','skipped') not null default 'unconfirmed',
	`friendship` enum('contact','acquaintance','friend','none'),
	`professional` enum('co-worker','colleague','none'),
	`physically_met` tinyint(1) unsigned not null default '0',
	`where_met` varchar(255),
	`date_met` int(12) unsigned not null default '0',
	`website` varchar(255),
	`private_notes` text,
	`created_date` int(12) unsigned not null,
	`confirmee_id` int(11) not null,
	`last_modified_date` int(12) unsigned not null default '0',
	`modified_by_id` int(11) not null,
	primary key (`friend_request_id`),
	key `xx_friend_request_details_01` (`confirmee_id`,`details_status`),
	key `xx_friend_request_details_02` (`modified_by_id`)
);

"INSERT INTO `friend_request_details` 
 (	`friend_request_id`,
	`details_status`,
	`friendship`,
	`professional`,
	`physically_met`,
	`where_met`,
	`date_met`,
	`website`,
	`private_notes`,
	`confirmee_id`,
	`created_date`,
	`last_modified_date`,
	`modified_by_id` )
 VALUES
 (  $friendRequestId,
	{$detailsArray['details_status']},
	{$detailsArray['friendship']},
	{$detailsArray['professional']},
	{$detailsArray['physically_met']},
	{$detailsArray['where_met']},
  	{$detailsArray['date_met']},
	{$detailsArray['website']},
	{$detailsArray['private_notes']},
	{$detailsArray['confirmee_id']},
	$now,
	$now,
	{$detailsArray['modified_by_id']}  )
  ON DUPLICATE KEY UPDATE 
  	`details_status`={$detailsArray['details_status']},
  	`friendship`={$detailsArray['friendship']},
  	`professional`={$detailsArray['professional']},
  	`physically_met`={$detailsArray['physically_met']},
  	`where_met`={$detailsArray['where_met']},
  	`date_met`={$detailsArray['date_met']},
  	`website`={$detailsArray['website']},
  	`private_notes`={$detailsArray['private_notes']},
  	`confirmee_id`={$detailsArray['confirmee_id']},
  	`last_modified_date`=$now,
  	`modified_by_id`={$detailsArray['modified_by_id']}"

(note php code for the insert values)

But I was able to duplicate the same problem with testing tables:

create table foo(
  id int(11) unsigned,
  a char(1),
  b char(1),
  primary key(id)
);
insert into foo values(1,'a','b');
insert into foo values(1,'c','d') on duplicate key update a='e',b='f';

I had a co-worker try it out on there linux box (Im on Windows) and they had no problems. I have not had any troubles running that type of query before, until now...