I am trying to partition a mysql table by the month of the timestamp column entry (time at which the record was inserted). The table in question uses an auto-increment column as the primary key because this table is simply inserted into a triggered process.
When I try to partition by month, I get an error saying that it is not allowed and when I check the error, it seems to me necessary to include the primary key in the score.
All columns used in the partitioning expression for a partitioned table must be part of each unique key that the table can have.
ALTER TABLE TABLE PARTITION BY RANGE (MONTH (entry_ts)) ( SHARING JANUARY VALUES LOWER THAN (2), PARTITION FEBRUARY VALUES LESS THAN (3), VALUES BELOW (4) VALUE PARTITION APRIL LESS THAN (5), PARTITION MAY VALUE LESS THAN (6), SHARING JUNE VALUES LOWER THAN (7), SHARING JULY VALUES LOWER THAN (8), VALUE OF AUGUST SHARING LESS THAN (9), PARTITION SEPTEMBER VALUES LOWER THAN (10), VALUE OF SHARING OCTOBER LESS THAN (11), PARTITION OF NOVEMBER VALUES LOWER THAN (12), PARTITION DECEMBER VALUES LOWER THAN (13) ) CREATE TABLE `TABLE` ( `HEADER_ID` int (11) NOT NULL AUTO_INCREMENT, `STATUS` int (11) NOT NULL DEFAULT & # 39; 0 & # 39; `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 & # 39; PRIMARY KEY (`HEADER_ID`) )