MySQL Bugs: #46936: Duplicate entry for key 'PRIMARY' if used partition and innodb

Bug #46936	Duplicate entry for key 'PRIMARY' if used partition and innodb
Submitted:	26 Aug 2009 10:08	Modified:	4 Sep 2009 13:36
Reporter:	Sveta Smirnova	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1, next bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I get Duplicate entry for key 'PRIMARY' when try to insert a row without specifying value for auto_increment column

How to repeat:
--source include/have_innodb.inc

CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `f` int(11) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY HASH (i)
PARTITIONS 4
;

begin;
insert into t(f) values(30);
rollback;

connect (addconroot, localhost, root,,);
connection addconroot;

begin;
insert into t(f) values(10);
insert into t(f) values(20);
select * from t;
commit;

connection default;
insert into t(f) values(30);
insert into t values(2,10) on duplicate key update i=i-3;
insert into t values(1,10);
insert into t values(2,20);
insert into t(f) values(30);
select * from t;

Bug is not repeatable without partitioning.

Problem is repeatable with MyISAM as well

Duplicate of bug#45823.

The real problem was updating to a negative value (which partitioning wrapped to the largest possible positive number, leaving no room for further increments).

Is not repeatable in mysql-5.1-bugteam now (after the push of bug#45823).