Bug #15238 Partitions: duplicate primary-key values
Submitted: 24 Nov 2005 22:45 Modified: 6 Apr 2006 3:49
Reporter: Peter Gulutzan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.2-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Sergei Glukhov CPU Architecture:Any

[24 Nov 2005 22:45] Peter Gulutzan
Description:
If a table has a PRIMARY KEY column, it should be
impossible in the ordinary course of business --
even if some statements fail -- to produce duplicates.
For example, if s1 is a PRIMARY KEY, and there
are two rows with s1 = -5, and there are two rows
with s1 = 0, then something has gone wrong.

I create a partitioned table with a primary-key
BIGINT column. I then do 5 instructions:
INSERT, CREATE INDEX, INSERT ALTER TABLE, INSERT.
At this point, there are duplicates in the table.

How to repeat:
mysql> create table t108 (s1 bigint, primary key (s1)) partition by range (s1)
    -> (partition p1 values less than (1),
    ->  partition p3 values less than (3));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t108 values (0);
Query OK, 1 row affected (0.01 sec)

mysql> create index t108i on t108 (s1);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t108 values (-5);
Query OK, 1 row affected (0.01 sec)

mysql> alter table t108 modify column s1 bigint unsigned;
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-17ed_2'
mysql> insert into t108 values (-5),(0),(1),(2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t108;
+----+
| s1 |
+----+
| -5 |
| -5 |
|  0 |
|  0 |
|  1 |
|  2 |
+----+
6 rows in set (0.01 sec)
[24 Nov 2005 22:51] Jorge del Conde
I was able to reproduce this using a fresh clone from 5.1 under FC4
[4 Apr 2006 14:28] Reggie Burnett
This might be the same issue as bug # 18753
[6 Apr 2006 3:49] Sergei Glukhov
verified on latest 5.1 tree, can't repeat