Procedure Random Partial Failure

[David Sarvai]

I have an event which kicks off a stored procedure at midnight to drop the oldest partition (180 Days old), and create a new partition (2 days into the future). This maintenance task randomly started failing last week (2/19) for 2 of the 3 tables it updates.

NOTE: I am not the author of this procedure.

MySQL Error Message:
2022-03-04T00:00:00.915128-05:00 64212 [ERROR] [MY-010045] [Server] Event Scheduler: [myuser@%][app.job_update_partitions] Error in list of partitions to DROP

The interesting thing is that the event/procedure drops the 3 partitions correctly, but only only creates the new partition on the first table. The second/third table do not get a new partition.


call_log - drops oldest partition
call_log - creates new partition

cc_agent_log - drops oldest partition
cc_agent_log - fails to create newest

cc_queue_log - drops oldest partition
cc_queue_log - fails to create newest


EVENT:
Delimiter $$
Create Event `Job_Update_Partitions`
On Schedule
Every 1 Day
Starts Curdate() + Interval '0:00' Hour_Minute
Do
Begin
call Update_Partitions('call_log');
call Update_Partitions('cc_agent_log');
call Update_Partitions('cc_queue_log');
End
$$
Delimiter ;


Procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `UPDATE_PARTITIONS`$$
CREATE PROCEDURE UPDATE_PARTITIONS(VAR_TABLE VARCHAR(25) )
BEGIN
DECLARE DAYSTOKEEP INT DEFAULT 180;
DECLARE CREATEINDAYS INT DEFAULT 2;

DECLARE MYDATE DATE;
DECLARE MYDATEPLUS1 DATE;
DECLARE MYDATEMINUS DATE;
DECLARE Y CHAR(2);
DECLARE M CHAR(2);
DECLARE D CHAR(2);
DECLARE DATE_STRING VARCHAR(100);

DECLARE PARTITION_NAME VARCHAR(100);
DECLARE NO_MORE_ROWS BOOLEAN;
DECLARE ROW_COUNT INT DEFAULT 0;
DECLARE NUM_ROWS INT DEFAULT 0;

SET MYDATE=DATE_ADD(CURDATE(), INTERVAL CREATEINDAYS DAY);
SET Y=DATE_FORMAT(MYDATE, '%y');
SET M=DATE_FORMAT(MYDATE, '%m');
SET D=DATE_FORMAT(MYDATE, '%d');
SET DATE_STRING=CONCAT('p', Y, M, D);

SET MYDATEPLUS1=DATE_ADD(CURDATE(), INTERVAL (CREATEINDAYS + 1) DAY);

SET @sql := CONCAT('ALTER TABLE ', VAR_TABLE, ' ADD PARTITION ( PARTITION ',
DATE_STRING, ' VALUES LESS THAN (',
TO_DAYS(MYDATEPLUS1),
'));
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET MYDATEMINUS=DATE_SUB(CURDATE(), INTERVAL DAYSTOKEEP DAY);
SET Y=DATE_FORMAT(MYDATEMINUS, '%y');
SET M=DATE_FORMAT(MYDATEMINUS, '%m');
SET D=DATE_FORMAT(MYDATEMINUS, '%d');
SET DATE_STRING=CONCAT('p', Y, M, D);

SET @sql := CONCAT('ALTER TABLE ', VAR_TABLE, ' DROP PARTITION ', DATE_STRING);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;


I upgraded the mysql server from 8.0.21 to 8.0.26, which still has the problem.

My open_files_limit is way higher than the number of tables on the server.

SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 10000 |
+------------------+-------+


Looking for suggestions/recommendations to troubleshoot/debug.

Thanks,
Dave