MySQL Bugs: #42102: INSERT fail 'Duplicated entry' after UPDATE set auto

Bug #42102	INSERT fail 'Duplicated entry' after UPDATE set auto_inc field with InnoDB
Submitted:	14 Jan 2009 9:34	Modified:	14 Jan 2009 10:08
Reporter:	Zhenxing He	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	4.1, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The following test will pass with MyISAM, but fail with InnoDB:

------------------------------------------------------------------
source include/have_innodb.inc;

#let $engine_type= MyISAM;
let $engine_type= InnoDB;
replace_result $engine_type engine_type;
eval CREATE TABLE t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type;

INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
UPDATE t1 set id=4 where id=3;

# this works fine with MyISAM, but will cause the following error with InnoDB:
# query 'INSERT INTO t1 VALUES (NULL)' failed: 1062: Duplicate entry '5' for key 'PRIMARY'
INSERT INTO t1 VALUES (NULL);
--------------------------------------------------------------------

How to repeat:
See description.

Suggested fix:
Here some findings related to this:
1) table->next_number_field is not set for UPDATEs
2) InnoDB does not update table's auto_increment value for UPDATEs

Thank you for the report.

Verified as described.

Duplicate of bug #38839 really.