0

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; 
1

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?

  • I suspect that the issue is you named an object starting with a space which goes against the standard naming convention. You may be able to get around this by enclosing the object name in double quotes instead of the normal single quotes, as suggested in this answer. – LowlyDBA Sep 10 '14 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.