MYSQL Delete event is not running

MySQLmysql-event

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

Best Answer

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

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.