Question :
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 working well any reason for this?
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;
Answer :
I have solved it the issue is here CONCAT(' p'
when I remove the space it is solved. But I don’t understand why it does not work with the space because when we created it is with space?