Bug #9725 "disapearing query/hang" and "unknown error" with "on duplicate key update"
Submitted: 7 Apr 2005 16:36 Modified: 12 Jul 2005 5:06
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11 OS:Any (*)
Assigned to: Antony Curtis CPU Architecture:Any

[7 Apr 2005 16:36] Martin Friebe
using the queries below, the 2nd query will crash (?) the server.

In more detail, executing the 2nd query never returns a result to the client, but the processlist will say IDLE. The client is waiting forever.

The 3rd query returns an uknown error. This should be a specific error.

Also in the third query the "ignore" is sensless, as the server does not really ignore duplicate key errors, it act correctly executing the update. Should the ignore affect the update?
Maybe it should affect the update, as the error gets back to the insert, the insert should continue.

How to repeat:
create table a1 (a int not null unique); insert into a1 values (1),(2);

insert into  a1 select 1  on duplicate key update a=2 ;
# work correct / update gives error

insert ignore into  a1 select 1  on duplicate key update a=2 ;
# will dissapear, but never return a result to the client

insert ignore into  a1 select a from a1  on duplicate key update a=a+1 ;
#ERROR 1105 (HY000): Unknown error

insert into  a1 select a from a1  on duplicate key update a=a+1 ;
# correct #ERROR 1062 (23000): Duplicate entry '2' for key 1

Suggested fix:
[18 Apr 2005 22:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[4 May 2005 14:40] Antony Curtis
Approved by Sergei, Apr 27. His comment follows:

make sure, please, that IGNORE ... UPDATE combination is documented,
as IGNORE now means not only "ignore duplicate keys" but "ignore all
errors" ("ignore duplicate keys" meaning conflicts with "on duplicate
key update" clause)
[7 May 2005 22:43] Antony Curtis
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
[22 Jun 2005 0:14] Mike Hillyer
Status returned to Open because version the patch was pushed to is not listed in the developer comments. Developer: please list the MySQL version the patch was pushed to and return the status to 'Documenting'.
[26 Jun 2005 16:47] Antony Curtis
Version 5.0.6
[10 Jul 2005 16:32] Jon Stephens
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 

Additional info:

Documented fix for 5.0.6; returned to Open status due to need for developer to indicate whether or not this was fixed in 4.1.x series and if so, which version, as this issue was originally reported in a Server 4.1 release.