Bug #68446 insert on duplicate key update with null autoincremented PK uses wrong pk value

• Submitted: 20 Feb 2013 23:36
• Modified: 22 Feb 2013 14:56
• Reporter: Steve Shucker
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.29
• OS: Any
• CPU Architecture: Any

[20 Feb 2013 23:36] Steve Shucker

Description:
When doing an insert on duplicate key update including an autoincremented primary key column that is sometimes null, any insert value tuple (null value for the pk column) that follow an update tuple (existing pk value to update) will have the wrong primary key and may corrupt data.

It appears that once an update tuple is encountered, the last_insert_id of zero is returned from the update and that is used as the PK value for the following insert.  If an update is followed by two inserts, those inserts gets assigned pk values of 0 and 1.

Workaround: order statement so the insert tuples all come before the update tuples

How to repeat:
CREATE TABLE test ( test_id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(30) NOT NULL, PRIMARY KEY (test_id) ) ENGINE=InnoDB

insert into test (test_id, name) values (null, 'name1'), (null, 'name2') on duplicate key update name=values(name)

insert into test (test_id, name) values (null, 'name3'), (2, 'name2b'), (null, 'name4'), (null, 'name5') on duplicate key update name=values(name)

Will yield:
0	name4
1	name5
2	name2b
3	name3

[21 Feb 2013 13:23] MySQL Verification Team

Could you please try version 5.5.30. Thanks.

[21 Feb 2013 21:41] Steve Shucker

Verified this bug doesn't occur on 5.5.30.  Sorry about not checking this before filing

[22 Feb 2013 14:56] MySQL Verification Team

Thank you for the feedback. Closing now.