We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not work any reason for this? I have checked nothing in the log showing any error regarding to this event.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
CONCAT(' p'
, DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN
SET @stmt := CONCAT(
'ALTER TABLE testPart1 DROP PARTITION '
, ' p'
, DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
CONCAT(' p'
, DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN
SET @stmt := CONCAT(
'ALTER TABLE testPart1 ADD PARTITION ('
, 'PARTITION p'
, DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
, ' VALUES LESS THAN ('
, TO_DAYS( CURDATE() ) + 2
, '))'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;