MySQL Bugs: #9725: "disapearing query/hang" and "unknown error" with "on duplicate key update"

Bug #9725	"disapearing query/hang" and "unknown error" with "on duplicate key update"
Submitted:	7 Apr 2005 18:36	Modified:	12 Jul 2005 7:06
Reporter:	Martin Friebe (Gold Quality Contributor)
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Any (*)
Assigned to:	Bugs System	Target Version:

Description:
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:
-

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:

  http://lists.mysql.com/internals/24123

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)

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
    http://www.mysql.com/doc/en/Installing_source_tree.html

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

Version 5.0.6

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 
http://www.mysql.com/downloads/

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.