MySQL alter table partition by range - Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

Question

I have a MySQL table (with data):

CREATE TABLE IF NOT EXISTS `lc6_words` (
  `jp_wkey` BIGINT NOT NULL AUTO_INCREMENT,
  `jp_word` varchar(255) NOT NULL,
  `jp_fcharascii` INT NOT NULL,
  `jp_word_occ` BIGINT NOT NULL DEFAULT 1,
  UNIQUE(`jp_word`),
  PRIMARY KEY (`jp_wkey`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I want to add partitions in it (by altering):

ALTER TABLE lc6_words PARTITION BY RANGE COLUMNS(jp_fcharascii)(
    PARTITION pw2486 VALUES LESS THAN (2486),
    PARTITION pw2487 VALUES LESS THAN (2487),
    PARTITION pw2488 VALUES LESS THAN (2488),
    PARTITION pw2489 VALUES LESS THAN (2489),
    PARTITION pwmax VALUES LESS THAN (MAXVALUE)
);

I'm getting the error:

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

MySQL version: 5.7.19 / Win 10 64bit

Any way around to implement the partition keeping the table structure intact? Thanks in advance.

Bill Karwin Bill Karwin · Accepted Answer · 2018-03-30T16:49:53

The error explains it pretty clearly.

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html also says:

All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every column in the table's partitioning expression.

That doc page goes on to show several examples of tables that may or may not be partitioned, or may be partitioned after some alteration.

Your table resembles one of the examples in the doc page that is shown as an example of an invalid partitioning request.

There are two workarounds, but both involve altering the table structure:

Add jp_fcharascii to your PRIMARY KEY and to the UNIQUE KEY on jp_word.
Remove both PRIMARY KEY and UNIQUE KEY constraints from the table (even if you keep the columns on which the constraints are defined).

But your condition in your question "keeping the table structure intact" precludes any alteration.

MySQL alter table partition by range - Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

1 Answers