Create Event Scheduler for jobs in MySQL

Event Scheduler is runs in the background and manages the execution of scheduled events. Event scheduler is used for scheduling the jobs and tasks performed in MySQL database on specified time and intervals.

Syntax for Creating Event Scheduler:

Syntax:
CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
[ON COMPLETION PRESERVE ]  --To keep the event after it is expired
DO
DO BEGIN
	-- event body
END;

Pattern for using SCHEDULE:


1. One Time Event:
AT timestamp + [INTERVAL interval]
Interval:YEAR,QUARTER,MONTH,DAY,HOUR,MINUTE,WEEK,SECOND,YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND,MINUTE_SECOND
timestamp must be a DATETIME or TIMESTAMP value
Example: for specify exact time use the combination : AT CURRENT_TIMESTAMP + INTERVAL 1 DAY

2. Reoccuring Events:
EVERY interval 
STARTS timestamp [+ INTERVAL] 
ENDS timestamp [+ INTERVAL]
Example:
EVERY 10 SECOND
EVERY 1 MINUTE
EVERY 6 MONTH
STARTS CURRENT_TIMESTAMP 
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]

Example of Creating Event Scheduler:

Example: 
-- Schedule event executed every 10 seconds
CREATE EVENT event_test01
    ON SCHEDULE EVERY 10 SECOND
    DO
      INSERT INTO test(msg, cre_Date) VALUES ('test 1',now());

--Schedule event execute at current time 	  
CREATE EVENT IF NOT EXISTS event_test_02
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
  INSERT INTO test(msg ,cre_date) VALUES('Test 2',NOW());

--Schedule event execute after 1 minutes
CREATE EVENT event_test_03
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
   INSERT INTO test(msg,cre_date)
   VALUES('Test 3',NOW());

--Schedule event execute every 1 minute start from now upto 1 hours.
CREATE EVENT event_test_04
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO test(message,created_at)
   VALUES('Test 4',NOW());


--Another example with define date
DELIMITER $$
CREATE EVENT 'event_test_05'
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-01 00:00:00'
ON COMPLETION PRESERVE
DO BEGIN
DELETE FROM theMessages WHERE datediff(now(),updateDt)>6; 
-- Code
END$$
DELIMITER ;

Check the existing Event present in MySQL database:

Select * from INFORMATION_SCHEMA.EVENTS;

OR 

SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]
Example:
mysql> SHOW EVENTS\G
MYSQL> SHOW EVENTS FROM classicmodels;

OR 

SHOW CREATE EVENT event_name
Example: SHOW CREATE EVENT myschema.job_daily \G

Example:
mysql> SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: world
                Name: event_test01
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2021-06-17 12:24:09
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

Drop the EVENT in MySQL database.

DROP EVENT [IF EXIST] event_name;
Example:
DROP EVENT event_test01
	  
Delete an event with root user 
Defination is stored in mysql.event, for delete the event from root user can be done from deleting the row from table mysql.event.

DELETE FROM mysql.event WHERE db = 'myschema'
 AND name = 'event_name';

Permission needed to create Event in MySQL:


Grant or Revoke permission for EVENT to user

GRANT EVENT ON myschema.* TO username@localhost;

GRANT EVENT ON *.* TO username@localhost;

REVOKE EVENT ON myschema.* FROM username@localhost;

Enable and disable the Event Scheduler:

-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;
-- Disable the event Scheduler
SET GLOBAL event_scheduler = OFF;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.