MySQL Bugs: #29840: A PRIMARY KEY must include all columns in the table's partitioning function

Bug #29840	A PRIMARY KEY must include all columns in the table's partitioning function
Submitted:	17 Jul 2007 8:59	Modified:	8 Aug 2014 6:16
Reporter:	Chunning Wang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.6.21	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
1. compile source code with partition
./configure \
--with-plugin-partition \
--with-plugin-innobase
2. install mysql5.1.20
3. create a partition table which partition filed is not primary key
Failure

A PRIMARY KEY must include all columns in the table's partitioning function

How to repeat:
CREATE TABLE `test` (
  `testid` int(10) unsigned NOT NULL auto_increment,
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `wgrpid` int(10) unsigned NOT NULL DEFAULT '0',
 
  primary key (`testid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB
PARTITION BY HASH(userid)
PARTITIONS 8;

This is a documented limitation now. Read the manual, http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-k...:

"In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key."

What exactly you are suggesting to change/implement in this feature request?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Is there any time plan for fixing this problem?

The catch is that this limitation can effectively lead to disarming the ability to use unique index for detection of duplicate data.

This feature actually makes partitioning pretty useless in most optimization scenarios, or am I missing something?

More here:
http://stackoverflow.com/questions/11763008/how-to-partition-mysql-table-by-column-that-is...

This is horrible.   How is any dba suppose to suggest partitioning of a table when it renders all unique and primary keys useless on the table?   Am I suppose to tell them they need to validate uniqueness with a trigger or in the app itself?

You do realize that one of the reasons people put unique constraints on a table is to PREVENT applications and users from accidentally inserting duplicates?

Still a limitation in current versions.

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(a int primary key, b int)engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> alter table t1 partition by range(b)(
    -> partition a values less than(10),
    -> partition b values less than(MAXVALUE));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql>

REPLACE changes its behaviour because of this:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');

SELECT * FROM test;

CREATE TABLE `test_ptn` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `ts`)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) (
  PARTITION p_2012 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00'))
, PARTITION p_2013 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00'))
, PARTITION p_2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00'))
, PARTITION p_max  VALUES LESS THAN (MAXVALUE)
);

REPLACE INTO test_ptn VALUES (1, 'Old', '2014-08-20 18:47:00');
REPLACE INTO test_ptn VALUES (1, 'New', '2014-08-20 18:47:42');

SELECT * FROM test_ptn;

for us it is a big problem too.

we have tables with a unique order id as primary key, 
and we want to partition this table on the basis of a request date.

But the request date can be change anytime, but the order id stays.

when we change the primary key from order_id to order_id + request_date we can not use the table properly. Some importent features of mysql are broken with this...

REPLACE
INSERT INTO ... ON DUPLICATE KEY UPDATE
...

Best Regards
Phillip

To enforce the uniqueness we only allow mapping of each unique/primary key value to one partition.

If we removed this limitation it would mean that for every insert/update we need to check in every partition to verify that it is unique. Also PK-only lookups would need to look into every partition.

What could be done is to enhance partitioning with Global Indexes, so that the unique/primary key is partitioned by those fields (or not partitioned at all) and the rest of the data are partitioned on a field not part of the unique/primary key. But this would mean that dropping a 'data' partition would need to do row-by-row deletes in the unique/primary key partitions/index. Also a PK-only lookup which accesses the whole row would need two lookups, the first on the PK index, and then on the 'data' index to retrieve the rest of the row.

What is your suggestion on how to remove the limitation of including all partitioning fields in all unique indexes?

Hi Mattias,

maybe it is posible to use a global unique key index who only points to the partition where the key i used.

Like: 

Unique Key | Partition
----------------------
AAA        | p_1
AAB        | p_2
AAC        | p_1
AAD        | p_2
AAE        | p_2
AAF        | p_2
AAG        | p_3
AAH        | p_1

i think this could be much more efficient than putting the whole PK in a global index.