truncate - Scheduled truncation of MySQL Partitions

Question

I have a MySQL (version 5.6) database with an audit table to store all transactions related to the inventory. I have the table partitioned based on the month of the transaction date of the event. So the table looks like this.

ENGINE=InnoDB AUTO_INCREMENT=13726377500 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( month(creationDate))
(PARTITION p0 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (12) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (13) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

My use case is that I want to truncate the older partitions every month (on the date of 15th since I need the last 15 days transactions). Is there a way to achieve this by creating an event? Can somebody help me with the same?

question from:https://stackoverflow.com/questions/65867870/scheduled-truncation-of-mysql-partitions

Answer 1

Waitting for answers