1

Im new to MySql events and I never used them before so please to bear with me. With My DB I have order_details and invoices tables which I need to create an Event to delete all the records older than 2 months and where order_details payment_status = 3 and invoices status = 2 and I want the event to make the deleting every day at 3AM How I may accomplish this Any help will be much appreciated

Both tables have timestamp column named created_at

Tables Definition

A - order_dateils :

  • id
  • order_bar
  • description
  • payment_status
  • created_at

B - invoices :

  • id
  • invoice_id
  • status
  • created_at

I tried below command but Im getting errors,

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

Errors :

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
  • And remember to turn on the Event Scheduler. – Rick James Sep 13 at 15:08
1

When you have multiple statements you need not only a BEGIN and END

But also a DELIMITER

One error is also dates have to be in quotes as you can see in the code

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 ;
| improve this answer | |
  • Not Sargeable. Change to created_at > NOW() - INTERVAL 2 MONTH. Also add the composite INDEX(payment_status, created_at). – Rick James Sep 13 at 15:10

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