MYSQL Delete event is not running

Question

I have below database tables,

Tables Definition

A - order_dateils :

id
order_bar
description
payment_status
created_at

B - invoices :

id
invoice_id
status
created_at

and created below event to delete data older than 2 months

DELIMITER //
CREATE EVENT 
IF NOT EXISTS daily_delete 
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00' 
DO 
BEGIN 
    DELETE 
    FROM bariq_DB.invoice 
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2; 
    delete from bariq_DB.order_details 
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3; 
END;//
DELIMITER ;

and I checked : SHOW PROCESSLIST

+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
| Id    | User            | Host      | db        | Command | Time  | State                       | Info             |
+-------+-----------------+-----------+-----------+---------+-------+-----------------------------+------------------+
|     5 | event_scheduler | localhost | NULL      | Daemon  | 61763 | Waiting for next activation | NULL             |
| 34948 | root            | localhost | baridi_DB | Sleep   |   984 |                             | NULL             |
| 34949 | root            | localhost | baridi_DB | Query   |     0 | starting                    | SHOW PROCESSLIST |
+-------+-----------------+-----------+-----------+---------+-------+---------------------------

But the events is not deleting any thing that matches the conditions, what Im missing here?

Any help will be much appreciated

RolandoMySQLDBA · Answer 1 · 2021-03-03T20:16:58.487

You have to make the event stay put using ON COMPLETION PRESERVE

DELIMITER //
DROP EVENT IF EXISTS daily_delete //
CREATE EVENT 
IF NOT EXISTS daily_delete 
ON SCHEDULE EVERY 1 DAY STARTS (CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND)
ON COMPLETION PRESERVE
DO 
BEGIN 
    DELETE 
    FROM bariq_DB.invoice 
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2; 
    delete from bariq_DB.order_details 
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3; 
END;//
DELIMITER ;

See my examples in the following posts

Jun 08, 2015 : Use event schedule and stored procedure to automatically average every hour MySQL
Jun 08, 2015 : Choosing what table fields to partition on
Sep 19, 2014 : Creating Event that execute at end of each month
Aug 28, 2014 : Adding time to an event that runs on a weekly basis

This will kickoff the event starting midnight tomorrow

You never start an event in the past. You must always start an event in the future.

MYSQL Delete event is not running

1 Answers1