MySql Event Scheduler

What is MySql Event Scheduler?

MySQL Events are tasks that execute on a specified time and can be scheduled hence also knowns as Scheduled events. Scheduler events are set off one or more than one SQL statement that executes on single or multiple times at specified intervals.

MySQL Events are also known as “temporal triggers” since they are triggered by time, not by any specific operation on the table. It works like a windows scheduler. It is mainly used for cleaning logs or generate complex reports during off-peak time.

Let's see about MySQL event scheduler configuration :

MySQL uses a special thread called event scheduler thread to execute all scheduled events. We can check the status of the event scheduler thread by executing the following command.

SHOW PROCESSLIST;

If the event scheduler event is not enabled, you can set the event_scheduler system variable to enable and start it using below Command.

SET GLOBAL event_scheduler = ON;

Now again execute “SHOW PROCESSLIST” Command. And you will get an output.

To disable and stop the event scheduler thread, you can set event_scheduler variable to OFF.

SET GLOBAL event_scheduler = OFF;

Create new MySQL Events:

Using the below command. “CREATE EVENT” statement creates a new event(It must be unique in its database).

CREATE EVENT [IF NOT EXIST] event_name 
ON SCHEDULE schedule 
DO 
Event_body

In this first line, we have to specify the name of the event and it must be unique. In the second line, we have to specify a schedule after “ON SCHEDULE”.

There are mainly two types of schedule:

1. One-time event

2. Recurring event

One-time event

This type of event occurs and executed only one time. For creating this type of events we have to specify timestamp after “ON SCHEDULE”.

AT timestamp [+ INTERVAL]

Let’s see it with an example.

1. First, we create a table named “Demo” using the below command.

   CREATE TABLE Demo (
       id INT PRIMARY KEY AUTO_INCREMENT,
       message VARCHAR(255) NOT NULL,
       created_on DATETIME NOT NULL
   );

2. Now we will create an event using the below command.

   CREATE EVENT IF NOT EXISTS one_time_event
   ON SCHEDULE AT CURRENT_TIMESTAMP
   DO
     INSERT INTO Demo(message,created_on)
     VALUES('Test MySQL Event 1',NOW());
   );

3. Now we will check the Demo table in the database there is one row that got inserted using the event scheduler because we specify the time as the current timestamp as the scheduled time

   SELECT * FROM Demo;

4. Now when you use the following command to display all the events from the database, the above-created event will not show in that list as its a one-time event so it is expired after its execution and event will automatically drop.

   SHOW EVENTS FROM database_name;

5. Now to keep the event after it is expired we can use ON COMPLETION PRESERVE clause.

   CREATE EVENT one_time_event
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
   ON COMPLETION PRESERVE
   DO
      INSERT INTO Demo(message,created_at)
      VALUES('Test MySQL Event 2',NOW());

6. Now using the below command we can check the events list

   SHOW EVENTS FROM database_name;

Recurring event

This type of event occurs after every certain interval of time. For creating this type of event we can use below command after “ON SCHEDULE”.

EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

This event occurs every minute from the current timestamp to the next interval hours. We can specify the start time after the STARTS keyword and end time after the ENDS keyword along with interval hours.

Let's see it with an example.

1. First, we create a table named “Demo” using below command

   CREATE TABLE IF NOT EXISTS  Demo (
       id INT PRIMARY KEY AUTO_INCREMENT,
       message VARCHAR(255) NOT NULL,
       created_on DATETIME NOT NULL
   );

2. Now we will create an event “Recurring Time Event” using the below command.

   CREATE EVENT IF NOT EXISTS reurring_event
   ON SCHEDULE EVERY 1 MINUTE
   STARTS CURRENT_TIMESTAMP
   ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
   DO
   INSERT INTO Demo (message,created_on) 
   VALUES  ('RecurringTimeEvent',NOW());

3. Now check the Demo table here every minute event will trigger insert action until the event got expired.

Drop Event

We can drop the event using the below command.

DROP EVENT IF EXISTS event_name;

That’s it, using the above steps you can create one time and recurring event scheduler in the database.

For professional paid support, you may contact us at [email protected] .