1

I have created a following table partitioning maintenance procedure thats working on unixtime date column for partition table....but it's not creating new partition for non-partitioned table ..

when I debug this procedure then I found the reason PARTITION_DESCRIPTION that is showing null for non-partition/ new table.

DELIMITER $$

CREATE DEFINER=`developer`@`%` PROCEDURE `ankit_UpdatePartitions`(dbname varchar(100), 
tblname varchar(100), today_date DATE, add_week INT)
BEGIN

        DECLARE max_date date;
        DECLARE droppart_sql date;
        DECLARE new_date date;

        -- max partititon date
    --  SELECT STR_TO_DATE(MAX(from_unixtime(PARTITION_DESCRIPTION)), '''%Y-%m-%d''') 
        SELECT MAX(date(from_unixtime(PARTITION_DESCRIPTION)))
        INTO max_date
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_NAME=tblname AND TABLE_SCHEMA=dbname;

        -- create partitions for next future week
        if (max_date < today_date +INTERVAL add_week week)
        Then
            SET new_date := max_date + INTERVAL 1 DAY;
            SET @partition_query := CONCAT('ALTER TABLE ', tblname, ' ADD PARTITION 
                                            (
                                                PARTITION p',CAST(((new_date - INTERVAL 1 DAY)+0) as char(8)),
                                                ' VALUES LESS THAN (', unix_timestamp(new_date) , ')
                                            );'
                                         );
            PREPARE stmt FROM @partition_query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

    --      SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
            SELECT MAX(date(from_unixtime(PARTITION_DESCRIPTION)))
            INTO max_date
            FROM INFORMATION_SCHEMA.PARTITIONS
            WHERE TABLE_NAME=tblname
            AND TABLE_SCHEMA=dbname;

            SET max_date := new_date;
        END if;
END

what should i update in my code to create new dynamic partitions for non_partition and already partition table both.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Ankit Agrawal
  • 2,426
  • 1
  • 13
  • 27
  • Not wise. You should deliberately add partitions to any new table -- it is _not_ always wise to partition tables, nor is it always wise to partition them the same way. – Rick James Mar 23 '16 at 23:04
  • `ADD PARTITION` is risky -- if the script fails to run when it should, seems like it would already have data in the wrong partition. See my discussion of using `REORGANIZE` instead in [my blog](http://mysql.rjweb.org/doc.php/partitionmaint). – Rick James Mar 23 '16 at 23:08

0 Answers0