Bug #60838 on duplicate key update -- lost data
Submitted: 12 Apr 2011 2:01 Modified: 12 May 2011 3:15
Reporter: Alexander Danel Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:Ver 14.12 Distrib 5.0.91 OS:MacOS
Assigned to: CPU Architecture:Any

[12 Apr 2011 2:01] Alexander Danel
Description:
I lost a record trying to copy a record.

There is no way to say it is "normal" that the command I executed resulted in lost data. 

>insert into jobs select * from jobs where job_id = 180 on duplicate key update job_id = NULL;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

>select * from jobs where job_id = 180;
Empty set (0.00 sec)

>select * from jobs where job_id IS NULL;
Empty set (0.00 sec)

How to repeat:
For any table MyTable, for a primary key column KeyCol, for an existing record having primary key value KeyVal, do this:

insert into MyTable select * from MyTable where KeyCol = KeyVal 
  on duplcate key update KeyCol = NULL;

You will wind up with one less record in MyTable.

Suggested fix:
At a minimum, don't delete the existing record.

In an ideal case, let the syntax be useful for the purpose I intended -- to copy a record, creating a new record that is identical except for primary key.
[12 Apr 2011 3:15] Valeriy Kravchuk
Try to execute

select * from jobs where job_id IS NULL;

again. You may be surprised. 

If you are, please, read http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null.  If not, please, send the output of SHOW CREATE TABLE for the jobs table.
[12 May 2011 23:00] Bugs System
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".