Bug #45081 Insertion anomaly for ON DUPLICATE KEY UPDATE
Submitted: 25 May 2009 22:10 Modified: 26 May 2009 5:56
Reporter: Michael McLaughlin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.34-community OS:Any
Assigned to: CPU Architecture:Any
Tags: ON DUPLICATE KEY UPDATE

[25 May 2009 22:10] Michael McLaughlin
Description:
If you attempt to use an override signature that excludes the auto incrementing column, the ON DUPLICATE KEY UPDATE fails, and writes a second row. The documentation doesn't qualify this as expected behavior but it may be what you intend. If this is expected behavior, perhaps updating the manual is the course of action but that and a syntax error would be better. If this isn't expected behavior, perhaps a fix is required.

How to repeat:
DROP TABLE IF EXISTS system_user;

CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(1)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);

INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY)
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY));

INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
,'Samuel'
, NULL
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY 
UPDATE first_name = 'Samuel'
,      middle_name = NULL
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = NOW();

mysql> select * from system_user\G
*************************** 1. row ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: NULL
         middle_name: NULL
           last_name: NULL
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-24
*************************** 2. row ***************************
      system_user_id: 2
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-25
     last_updated_by: 1
    last_update_date: 2009-05-25
2 rows in set (0.00 sec)

Suggested fix:
I'd suggest that one of two things happen: (1) If this is desired behavior, you should throw an error when the override signature excludes the auto incrementing column; or (2) If this is undesired behavior, you should provide a function that lets you compare the current row auto incrementing column.

My preference is a new functions that checks current row for the primary key value because realistically most developers don't have the ID values when they attempt a merge operation. My guess is that there may be a bunch of garbage data from this clause in databases. This sure looks like a potential insertion anomaly.
[25 May 2009 22:46] MySQL Verification Team
Thank you for the bug report. Sorry but this behavior isn't a bug instead is an expected behavior how AUTO_INCREMENT works when that column is omitted in the INSERT statement and also no syntax error exists (it is valid syntax). The sample how AUTO_INCREMENT works is illustrated at:

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

so how a new value of system_user_id is created (2 in your sample) ON DUPLICATE KEY UPDATE hasn't effect.
[25 May 2009 23:47] Michael McLaughlin
While the URI you've referenced does point to the auto increment behavior, it has no reference to the ON DUPLICATE KEY UPDATE behavior in this context.

It strikes me that the intent of the ON DUPLICATE KEY UPDATE performs the behavior as the Oracle MERGE statement. Oracle's MERGE statement does enforce matching the submitted key against the current key, which is how MySQL performs when the primary key column is included in the overriding signature.

I don't understand why an override signature would be allowed that excludes the auto increment column. It certainly does allow an insertion anomaly. Isn't the better documentation reference this:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

If this isn't a bug, it's certainly something to watch out for when using the ON DUPLICATE KEY UPDATE clause. It appears a change to the online document I've referenced may help users - 12.2.5.3.
[26 May 2009 5:56] Sveta Smirnova
Thank you for the feedback.

Re-closed as "Not a Bug".
[7 Feb 2013 17:26] Neike Taika-Tessaro
Sorry, I'm very late to the party, :( but I got here from a comment on the documentation and... I admittedly don't understand the fuss. Could you bear with me for a moment, not just for my benefit, but (hopefully) that of others reading the comments in the documentation?

As I understand it:

INSERT ODKU is an INSERT first and foremost. If your INSERT statement doesn't conflict with a unique key (i.e. UNIQUE or PRIMARY), there is no reason to proceed with ODKU and it naturally won't fire. No duplicate key was attempted to be inserted.

In the example you cited, you might have intended for 'system_user_name' to be UNIQUE - but... if you HAD made it so, the row would have been updated, entirely without the inclusion of the auto_increment column.

(That being said, it might indeed make sense to have ODKU fail on principle if no unique keys are accessed in INSERT, but that has nothing to do with auto_increment.)

Am I somehow completely misunderstanding the issue?
[28 Nov 2014 6:21] Henry Antona
hi. im having problems with my code, can you help me find the best solution? thanks in advance.