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.