MySQL Forums
Forum List  »  Stored Procedures

Procedure Random Partial Failure
Posted by: David Sarvai
Date: March 04, 2022 11:37AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Procedure Random Partial Failure
144
March 04, 2022 11:37AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.