SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
-
Jun 27, 2007, 13:14 #1
- Join Date
- Jan 2005
- Posts
- 502
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ON DUPLICATE KEY UPDATE and query cache
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
-
Jun 27, 2007, 13:25 #2
- Join Date
- Dec 2004
- Location
- London, UK
- Posts
- 1,376
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 27, 2007, 13:51 #3
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Turn off query caching, or use the SQL_NO_CACHE hint in your SELECT.
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Jun 27, 2007, 14:04 #4
- Join Date
- Dec 2004
- Location
- London, UK
- Posts
- 1,376
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jun 27, 2007, 14:05 #5
- Join Date
- Jan 2005
- Posts
- 502
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jun 27, 2007, 14:08 #6
- Join Date
- Jan 2005
- Posts
- 502
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 27, 2007, 14:12 #7
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
That was my understanding as well, and it may well be a bug. Are you running a pretty recent version of MySQL?
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Jun 27, 2007, 14:29 #8
- Join Date
- Jan 2005
- Posts
- 502
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jun 27, 2007, 17:54 #9
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 28, 2007, 08:32 #10
- Join Date
- Jan 2005
- Posts
- 502
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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']}"
But I was able to duplicate the same problem with testing tables:
Code: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';
Bookmarks