| Bug #45081 | Insertion anomaly for ON DUPLICATE KEY UPDATE | ||
|---|---|---|---|
| Submitted: | 26 May 0:10 | Modified: | 26 May 7:56 |
| Reporter: | Michael McLaughlin | ||
| Status: | Not a Bug | ||
| Category: | Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.34-community | OS: | Any |
| Assigned to: | Target Version: | ||
| Tags: | ON DUPLICATE KEY UPDATE | ||
[26 May 0:46]
Miguel Solorzano
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.
[26 May 1: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 7:56]
Sveta Smirnova
Thank you for the feedback. Re-closed as "Not a Bug".
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.