CODEWITHSUNDEEP
mysql
rahul
rahul

Reputation: 1122

Event Scheduler in mysql not working

I worte the below mention scheduler which delete some data every minute whose date_created is four days back to current date but delete data once when the file is import in mysql but after that doesnt work for me. Kindly tell me what the problem with the scheduler.

use mydatabase;
DROP EVENT IF EXISTS  noti_event ;
SET GLOBAL event_scheduler = OFF;
SET GLOBAL event_scheduler = ON;
create EVENT IF NOT EXISTS noti_event ON SCHEDULE EVERY 1 MINUTE STARTS CURDATE() + '12:00:00'

DO
   SET FOREIGN_KEY_CHECKS=0;
   DELETE FROM user_noti WHERE noti_id in (Select id from noti where date_created <= DATE_SUB(NOW(), INTERVAL 4 DAY));
   DELETE FROM noti_read_by WHERE noti_id in (Select id from noti where date_created <= DATE_SUB(NOW(), INTERVAL 4 DAY));
   DELETE FROM associated_add WHERE id  in (Select id from noti where date_created <= DATE_SUB(NOW(), INTERVAL 4 DAY));
   DELETE FROM noti WHERE date_created <= DATE_SUB(NOW(), INTERVAL 4 DAY) ;
   SET FOREIGN_KEY_CHECKS=1;
;

Upvotes: 0

Views: 2573

Answers (1)

Devart
Devart

Reputation: 122032

You need to set ON COMPLETION PRESERVE option, e.g. -

CREATE EVENT event1
  ON SCHEDULE EVERY '1' MINUTE
  STARTS CURRENT_TIMESTAMP
  ON COMPLETION PRESERVE
  DO 
BEGIN
END

Upvotes: 1

Related Questions