Introduction to MySQL events

 An event is similar to trigger. However, rather than running in response to a data change, events can be scheduled to run any number of times during specified period. In effect it's a database only cron job.

MySQL Event Scheduler configuration

MySQL uses special thread called event schedule thread to execute all scheduled events.

You can view the status of scheduler thread by executing following command.
  
     SHOW PROCESSLIST;

By default event scheduler thread is not enabled. you can enable it by running following command. 

    SET GLOBAL event_scheduler = ON;

If you need to off you can run following command,

    SET GLOBAL event_scheduler = OFF;


Creating and events is similar to create other database objects such as stored procedures and triggers.

Raw example:  

CREATE EVENT `event_name`
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO BEGIN
    -- event body
END; 

Description: 

• Run once on a specific date/time: AT ‘YYYY-MM-DD HH:MM.SS’
  e.g. AT ‘2011-06-01 02:00.00’ 
• Run once after a specific period has elapsed:
  AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]
  e.g. AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
• Run at specific intervals forever: EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]
  e.g. EVERY 1 DAY 
• Run at specific intervals during a specific period:
  EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date
  e.g. EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS ‘2012-01-01 00:00.00’ 

 Example:

DELIMITER $$

CREATE
    EVENT `archive_blogs`
    ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00'
    DO BEGIN
   
        -- copy deleted posts
        INSERT INTO blog_archive (id, title, content)
        SELECT id, title, content
        FROM blog
        WHERE deleted = 1;
      
        -- copy associated audit records
        INSERT INTO audit_archive (id, blog_id, changetype, changetime)
        SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime
        FROM audit
        JOIN blog ON audit.blog_id = blog.id
        WHERE blog.deleted = 1;
      
        -- remove deleted blogs and audit entries
        DELETE FROM blog WHERE deleted = 1;
      
    END */$$

DELIMITER ;


Example:

DELIMITER $$CREATE
   EVENT `countdown_expire_dev`
   #ON SCHEDULE EVERY 1 MINUTE
    #STARTS CURRENT_TIMESTAMP
    #ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
   
    #ON SCHEDULE EVERY 14 DAY_HOUR
   
   ON SCHEDULE
   #EVERY 1 DAY
   EVERY 1 MINUTE
   STARTS '2018-07-09 13:10:00' ON COMPLETION PRESERVE ENABLE
   
   
   DO BEGIN        #DELETE FROM sfrhubdev_test.notes WHERE id = 11;
       
       UPDATE sfrhubdev_test.listings SET status=3, active = 0
       where status = 2  AND expire_at <= curdate();
     
   END
   

   $$
DELIMITER ;

use full codes:

SHOW PROCESSLIST;

show events;

DROP event countdown_expire_dev;


SET GLOBAL event_scheduler = ON;



 

Introduction to MySQL events