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)
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)