0

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`)
) 
  • Right, and what is your question? – mustaccio Apr 29 at 23:39
  • What do you expect in the 13th month of deployment? Two year's data in the same partition? Will you have queries that use a date range? If so, you will be disappointed to see that it will look in all partitions. Let's see your the SELECTs. – Rick James May 2 at 0:49
0

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

  • I tried that this morning on development and I got the same issue. Error Code: 1506. Foreign keys are not yet supported in conjunction with partitioning and googling that is what lead me to what I posted before about the primary key. – Joe W Apr 30 at 12:24
  • I am sorry, but you did not mention the table had foreign keys. – RolandoMySQLDBA Apr 30 at 14:12
  • Yes that was a mistake on my end as I got confused when I googled the error, my apologies for the inaccurate information. – Joe W Apr 30 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.