Search code examples
mysqlsqlcreate-tablealter-table

Mysql partitioning every month - Error Code: 1503 A PRIMARY KEY must include all columns in the table's partitioning function

There are similar cases here, but nothing worked for me. I've created table:

CREATE TABLE `message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_content` longtext,
  `recipient` varchar(255) DEFAULT NULL,
  `send_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `send_time` (`send_time`, `id`))
 ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

And I need to add partitioning - to create new partition every month:

ALTER TABLE messages.message
PARTITION BY RANGE(TO_DAYS(`send_time`))
(PARTITION p01 VALUES LESS THAN (TO_DAYS('2019-01-01')) ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN (TO_DAYS('2019-02-01')) ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN (TO_DAYS('2019-03-01')) ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN (TO_DAYS('2019-04-01')) ENGINE = InnoDB,
PARTITION p05 VALUES LESS THAN (TO_DAYS('2019-05-01')) ENGINE = InnoDB,
PARTITION p06 VALUES LESS THAN (TO_DAYS('2019-06-01')) ENGINE = InnoDB,
PARTITION p07 VALUES LESS THAN (TO_DAYS('2019-07-01')) ENGINE = InnoDB,
PARTITION p08 VALUES LESS THAN (TO_DAYS('2019-08-01')) ENGINE = InnoDB,
PARTITION p09 VALUES LESS THAN (TO_DAYS('2019-09-01')) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (TO_DAYS('2019-10-01')) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (TO_DAYS('2019-11-01')) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (TO_DAYS('2019-12-01')) ENGINE = InnoDB)

I've already tried solutions from other answers (like adding UNIQUE KEY), but nothing worked for me. Here's what I get:

  SQL State  : HY000
  Error Code : 1503
  Message    : A PRIMARY KEY must include all columns in the table's partitioning function
Solution

  • You partioning column must be part of your primary key, having a UNIQUE index is not enough. See the mysql docs :

    The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. [...] This also includes the table's primary key, since it is by definition a unique key.

    You probably want to make your unique key the primary key, like :

    PRIMARY KEY (`id`, `send_time`)