MySQL Bugs: #27672: insert on duplicate key update sometimes delayed

Bug #27672	insert on duplicate key update sometimes delayed
Submitted:	5 Apr 2007 16:35	Modified:	29 Jul 2007 6:36
Reporter:	Regi Guardialao	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.37 Community Edition	OS:	Linux (Fedora 5 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	cache, Delay, ON DUPLICATE KEY UPDATE, SQL_NO_CACHE

Description:
We have just updated our servers to version 5.0.37 community edition and sometimes when the server has a high load the query with the format "insert...on duplicate key update..." whenever the key already exists, hence the update portion, doesn't reflect the updated values right away sometimes for more than a minute. This can only be resolved using SQL_NO_CACHE directive on select statements.. Other insert and update statements doesn't have this kind of behavior, only the one with "on duplicate key". When the load on the server is low, it doesn't always happen.

Hope you can provide insight on this. Thank you and more power.

How to repeat:
On a high load server, create a table with unique columns and do an "insert on duplicate key update" on an already existing key and update other columns. Query the values (query caching enabled), sometimes the updated values doesn't reflect right away.

Thank you for a problem report. Do you use InnoDB tables? If yes then it looks like a duplicate of http://bugs.mysql.com/bug.php?id=27210, or, at least, has similar origins. Take a look at http://bugs.mysql.com/bug.php?id=27650 also.

Hi Valeriy,

We use myisam and it happened to me several times on different tables.
Haven't tried myself if the problem exists in innodb as well since we don't use innodb on relatively small tables.

Thank you for your attention and support.

Please provide output of SHOW CREATE TABLE  and SHOW TABLE STATUS for one of problem tables. Also provide your configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Please, send information Sveta requested.

We experienced this same bug on 5.0.37.  We found upgrading to community release of 5.0.41 resolved it.  Looks like it's related to issue #27210.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I experience the same problem.

Its on a godaddy server, 

#  Server version: 5.0.37-log
# MySQL client version: 5.0.27
# Protocol version: 10
# Server: 10.8.11.81 via TCP/IP

on duplicate key updating several fields, duplicate check is depending on a index (not the primary key) it does not update the  values on a duplicate for several minutes at least, in fact, now I tend to have to use php myadmins analyze table option before the updates show.

forgot to add, using myisam

Thank you for the feedback.

But we really need in information I requested before:

Please provide output of SHOW CREATE TABLE  and SHOW TABLE STATUS for one of problem tables. Also provide your configuration file.

Also please try with current version and examine if problem still exists.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".