0

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

0

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.

1
  • @Ronaldo thanks for your help, but its still not deleting from both tables
    – Ali Adil
    Mar 5 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.