Mysql – Partitioning with AUTO_INCREMENT primary key

MySQLmysql-5.7partitioning

I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table is just INSERTed into by a triggered process in aws.

When I try to partition by month I get an error saying it is not allowed and when I look up the error it seems that I need to include the primary key as part of the partition.

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

ALTER TABLE TABLE
    PARTITION BY RANGE(MONTH(entry_ts)) (
    PARTITION JANUARY VALUES LESS THAN (2),
    PARTITION FEBRUARY VALUES LESS THAN (3),
    PARTITION MARCH VALUES LESS THAN (4),
    PARTITION APRIL VALUES LESS THAN (5),
    PARTITION MAY VALUES LESS THAN (6),
    PARTITION JUNE VALUES LESS THAN (7),
    PARTITION JULY VALUES LESS THAN (8),
    PARTITION AUGUST VALUES LESS THAN (9),
    PARTITION SEPTEMBER VALUES LESS THAN (10),
    PARTITION OCTOBER VALUES LESS THAN (11),
    PARTITION NOVEMBER VALUES LESS THAN (12),
    PARTITION DECEMBER VALUES LESS THAN (13)
    );

CREATE TABLE `TABLE` (
  `HEADER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STATUS` int(11) NOT NULL DEFAULT '0',
  `CREATED_AT` varchar(45) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `VERSION` varchar(45) NOT NULL,
  `ID` varchar(45) NOT NULL,
  `ORIGIN` varchar(45) NOT NULL,
  `PAYLOAD` json NOT NULL,
  `ENTRY_TS` datetime NOT NULL',
  PRIMARY KEY (`HEADER_ID`)
)

Best Answer

Simply put, you need to put entry_ts in the PRIMARY KEY before you can partition the table.

The table needs to look like this:

CREATE TABLE `TABLE` (
  `HEADER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STATUS` int(11) NOT NULL DEFAULT '0',
  `CREATED_AT` varchar(45) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `VERSION` varchar(45) NOT NULL,
  `ID` varchar(45) NOT NULL,
  `ORIGIN` varchar(45) NOT NULL,
  `PAYLOAD` json NOT NULL,
  `ENTRY_TS` datetime NOT NULL',
  PRIMARY KEY (`HEADER_ID`,`ENTRY_TS`)
);

So, run this:

ALTER TABLE `TABLE`
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`HEADER_ID`,`ENTRY_TS`);

Then, you can partition the table since ENTRY_TS is part of the PRIMARY KEY.

ALTER TABLE TABLE
    PARTITION BY RANGE(MONTH(entry_ts)) (
    PARTITION JANUARY VALUES LESS THAN (2),
    PARTITION FEBRUARY VALUES LESS THAN (3),
    PARTITION MARCH VALUES LESS THAN (4),
    PARTITION APRIL VALUES LESS THAN (5),
    PARTITION MAY VALUES LESS THAN (6),
    PARTITION JUNE VALUES LESS THAN (7),
    PARTITION JULY VALUES LESS THAN (8),
    PARTITION AUGUST VALUES LESS THAN (9),
    PARTITION SEPTEMBER VALUES LESS THAN (10),
    PARTITION OCTOBER VALUES LESS THAN (11),
    PARTITION NOVEMBER VALUES LESS THAN (12),
    PARTITION DECEMBER VALUES LESS THAN (13)
    );

Please try this in Staging with an empty table.

GIVE IT A TRY !!!

Related Question