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