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`) )