Bug #46909 partitioned innodb tables end up with duplicate primary key values!
Submitted: 25 Aug 2009 5:31 Modified: 4 Sep 2009 14:05
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.37, 5.1.39-debug OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[25 Aug 2009 5:31] Shane Bester
Description:
During some tests, I found a few partitioned innodb tables have duplicate primary keys, which should never be possible!

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `f` int(11) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (i)
PARTITIONS 4 */
1 row in set (0.02 sec)

mysql> select * from t;
+----+------+
| i  | f    |
+----+------+
|  1 |   30 |<-----
| -1 |  -10 |
|  1 |   10 |<-----
|  2 |   20 |
+----+------+
4 rows in set (0.02 sec)

mysql> check table t extended;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.69 sec)

this was never happening in 5.1.36..

How to repeat:
no idea. will try make a testcase later.
[26 Aug 2009 21:38] Sveta Smirnova
Thank you for the report.

I can repeat described behavior using release version with following test for our MTR:

--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(10);
insert into t values(-1,-10);

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

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

connection default;
commit;
select * from t;

While same test with debug binary crashes for me with known assertion (handler.cc:4580: int handler::ha_external_lock(THD*, int): Assertion `next_insert_id == 0' ).
[27 Aug 2009 2:22] MySQL Verification Team
sveta, I can repeat it as you described on 5.1.37:

mysql> select * from t;
+----+------+
| i  | f    |
+----+------+
|  1 |   20 |
| -1 |  -10 |
|  1 |   10 |
|  2 |   30 |
+----+------+
4 rows in set (0.00 sec)
[27 Aug 2009 5:31] Sveta Smirnova
Verified as described.

Bug is not repeatable without partitioning.
[27 Aug 2009 12:12] Mikhail Izioumtchenko
Sunny has been looking at it
[27 Aug 2009 12:19] Sergey Vojtovich
Probably related to BUG#45823.
[28 Aug 2009 7:59] Calvin Sun
see http://bugs.mysql.com/bug.php?id=43988.
[4 Sep 2009 14:05] Mattias Jonsson
This bug is a duplicate of Bug#45823.

(Cannot repeat on current mysql-5.1-bugteam, where that patch is pushed.)