Event scheduler in MySQL

Events are used to execute some SQL code at specified time just like windows scheduler or cron jobs in Linux.

Here are the steps to create events.

  1. Start the scheduler
  2. Create Event
  3. View the active events from the database.

To start the event scheduler process, you need to update the event_scheduler variable to ON.

SET GLOBAL event_scheduler = ON;

Once you do that, you will see that event_scheduler process is started using below statement.

SHOW PROCESSLIST;

Next you need to create the event as shown in below example. Here we are first dropping the event and then creating new one with name MyEvent.
This event starts now and ends after 1 minute repeating each 15 seconds. After every 15 seconds, it will insert a record in Table – dup.

Drop event if exists MyEvent;

CREATE EVENT MyEvent
ON SCHEDULE EVERY 15 second
STARTS now()
ENDS now() + INTERVAL 1 minute
DO
INSERT INTO dup VALUES(‘Testing Event’,now());

You can view all active events using below query.
SHOW EVENTS FROM Deals;

In case, your events are not working as expected, ensure that event_scheduler is ON. You can also check the server logs for any errors.

You may also like...