MySql Event Scheduler

blog-banner

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.

FAQ

MySQL database operation is done with the help of scheduled events. This means that to create an event you create a database object containing some SQL statements that are to be executed at regular intervals.
In place of the event_body syntax, you should specify the SQL for the statements to be executed on the schedule you have created. The necessary syntax is to be supplied after the DO keyword
To disable myevent, use this ALTER EVENT statement: ALTER EVENT myevent DISABLE; If you use an ON SCHEDULE clause in DDL statements, then you need to use a user variable to obtain the timestamp value instead of using a built-in MySQL function
Contact us

For Your Business Requirements

Text to Identify Refresh CAPTCHA
Background Image Close Button

2 - 4 October 2024

Hall: 10, Booth: #B8 Brussels, Belgium