SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist
    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

  2. #2
    SitePoint Wizard HarryR's Avatar
    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.

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    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.

  4. #4
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    or use the SQL_NO_CACHE hint in your SELECT.
    Yes, but it's handy having query caching in between updates

  5. #5
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by HarryR View Post
    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...

  6. #6
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    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...

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    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?

  8. #8
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    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.

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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.

  10. #10
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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:
    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']}"
    (note php code for the insert values)

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •