Bug #14793 auto_increment pointer is not updated when auto_increment field is updated.
Submitted: 9 Nov 2005 16:50 Modified: 13 May 2010 16:04
Reporter: Nizamettin OZPOLAT Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0-BK, 5.0.15 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[9 Nov 2005 16:50] Nizamettin OZPOLAT 
Description:
auto_increment field in innoDB tables has problem. when inserting new data the last insert ID changes and become the biggest value +1 but updating the auto_increment field does not change last insert id.

How to repeat:
do the following.

CREATE TABLE `table2` (
  `f1` int(11) NOT NULL auto_increment,
  `f2` varchar(30) default NULL,
  PRIMARY KEY  (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table2` (`f1`, `f2`) VALUES
  (1,'erwe'),
  (2,'rewer'),
  (3,'kuhıu'),
  (4,'ewr234'),
  (5,'werwerw'),
  (6,'dfdfs'),
  (7,'erwe');
commit;
update table2 set f1 = 8 where f1 = 1;
commit;
INSERT INTO `table2` (`f2`) VALUES ('fdsfsd');
commit;

then error comes,

Error : "Duplicate entry '8' for key 1"

myISAM tables does not have this problem, when updating auto_increment field auto_increment pointer updated but working with innoDB it is not updated
[9 Nov 2005 18:23] Valeriy Kravchuk 
Thank you for a bug report. 

Verified just as described on 5.0.16-BK (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com).
[9 Nov 2005 20:33] Heikki Tuuri 
Hi!

I am changing this to a feature request. InnoDB's manual says that only INSERT and REPLACE affect the auto-inc counter. Not an UPDATE. MyISAM has a different behavior.

You can work around the problem by using DELETE + INSERT.

Regards,

Heikki