Introduction to MySQL Scheduled Events

By /
Introduction To Mysql Scheduled Events

In this tutorial, we will learn about the MySQL event scheduler and what are scheduled events in MySQL. This is an introductory article about MySQL events and we will cover everything about it that you should know. If you have already read this article or you know the basics about events, check our detailed tutorial on creating MySQL scheduled events (link of the working with MySQL scheduled events. article available next to this article).

Introduction to Scheduled Events

The MySQL events, as the name suggests, are tasks or set of instructions that execute periodically as specified by the scheduled time. Therefore, you can also call it scheduled events because they are already scheduled.

If you have handled the hosting of a website or a Linux OS, you may know about the cron job. The scheduled events are exactly the same as cron jobs but these events are directly associated with the DBMS.

The MySQL event is an object which consists of one or more SQL statements and is stored in the database. You can schedule the event to execute once or multiple times.

The scheduled events can not be called or triggered explicitly. They are invoked automatically over a specific period of time. The events are also called “temporal triggers” because they are invoked by time instead of DML commands.

Examples of MySQL Events

A simple example of an event is – you can create an event to remove the products with zero stock. That event will execute every hour and check the table to remove the product information.

Apart from this, the MySQL events are very helpful in multiple cases such as optimization of the database tables, clearing the logs, archiving the data, and generating reports.

However, the scope of MySQL events is broad. You can make use of this great feature in your project to increase app productivity and efficiency.

Before Creating the Events

Before creating events in MySQL, make sure that you have started the MySQL Event Scheduler in your system. The Event Scheduler is a daemon process or a thread that runs in the background and manages the scheduled events.

Without turning on the event scheduler, you can create the events but those won’t execute.

Let’s see how you can configure the event scheduler now.

Configuring Event Scheduler

In MySQL, the global event_scheduler system variable decides if the event scheduler is working and running on the server. You can turn the event scheduler on, off, or make it disabled as well. The event scheduler can have one of the below three values-

  • ON – Starts the event scheduler. By default, the event scheduler is set to ON. When the event scheduler is on, it is shown on the output screen as a daemon process when the SHOW PROCESSLIST command is entered.
  • OFF – Stops the event scheduler. When you turn off the event scheduler, it won’t be shown on the screen when the SHOW PROCESSLIST command is entered.
  • DISABLED – When the event scheduler is disabled, the event scheduler thread does not run.

You can use 0 for ON and 1 for OFF when setting the variable.

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)
Show Processlist
Show Processlist

As you can see, the event scheduler is currently empty and running in the background as a daemon process.

Let’s turn it off and check if it is visible on the screen or not when we run the SHOW PROCESSLIST command.

SET GLOBAL EVENT_SCHEDULER = off;
SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)
Turn Off Event Scheduler
Turn Off Event Scheduler

Perfect! The event scheduler is off now so it is not listed in the process list. Let’s turn it on again.

SET GLOBAL EVENT_SCHEDULER = 1;
SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)

We have used 1 as an alternative to the ON value. Let’s see if it works.

Turn On Event Scheduler
Turn On Event Scheduler

MySQL Scheduled Events Syntax

The CREATE EVENT statement is used to create an event in MySQL. If you check the official documentation of the events on the MySQL website, you will see the below syntax which looks pretty complicated.

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_bodyCode language: SQL (Structured Query Language) (sql)

Where,

  • CREATE EVENT – Statement to create an event in the database.
  • DEFINER – The user in MySQL.
  • IF NOT EXISTS – Avoids warning if you try to create a event that already exists and stops further execution.
  • event_name – Any name for the event. No two events of the same name can reside in the same database.
  • schedule – The period/time you want the event to execute/envoke. You can choose the event to execute only once or over and over again.
  • DO event_body – The set of SQL statements.

The events can be one-time or recurring.

One Time Event

The one-time events are executed only once. You can schedule them using the below-listed intervals. Before that, let’s see the syntax to create a one-time event.

In the above syntax of creating events, you can use the following statement after the ON SCHEDULE clause.

AT timestamp + [INTERVAL interval];Code language: SQL (Structured Query Language) (sql)

Interval can be-

  • YEAR
  • QUARTER
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • WEEK
  • SECOND
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • MINUTE_SECOND

The timestamp above mentioned should be valid DATETIME or TIMESTAMP in the future.

Recurring Event

The recurring events are executed multiple times, over and over again at the specified time. Following is the syntax to create a recurring event.

Write the following statements after the ON SCHEDULE clause.

EVERY interval
[STARTS timestamp [+ INTERVAL]...]
[ENDS timestamp [+ INTERVAL]...]Code language: SQL (Structured Query Language) (sql)

The STARTS specifies when to start the execution of the event. On the other side, ENDS specifies when to stop the execution of the event.

If only STARTS timestamp is specified, the event will start from a specified time and execute as long as the server is up. If both timestamps (STARTS and ENDS) are not specified, the event will start immediately and execute until the server is running.

Display Scheduled Events

You can view the information about scheduled events using the SHOW EVENTS statement.

SHOW EVENTS FROM database_name;Code language: SQL (Structured Query Language) (sql)

We have created an event in our database already. Let’s display it using the above syntax.

SHOW EVENTS FROM journaldev \G;Code language: SQL (Structured Query Language) (sql)
Show Events In Mysql
Show Events In Mysql

Modify Events

You can modify already created events using the ALTER EVENT statement.

ALTER EVENT event_name
[ ON SCHEDULE schedule ]
[ RENAME TO new_event_name ]
[ ON COMPLETION [ NOT ] PRESERVE ]
[ COMMENT 'comment' ]
[ ENABLED | DISABLED ]
[ DO sql_statement ]Code language: SQL (Structured Query Language) (sql)

The syntax for modifying an existing event is exactly the same as creating an event except for the ALTER EVENT statement.

Delete Events

You can remove the existing event using the DROP EVENT statement.

DROP EVENT [IF EXISTS] event_name;Code language: SQL (Structured Query Language) (sql)

We will now delete the event that we have already created.

DROP EVENT IF EXISTS event_demo;Code language: SQL (Structured Query Language) (sql)
Drop Event
Drop Event

The IF EXISTS statement helps us to avoid the error if you try to delete the event that does not exist in the system. However, you will see a warning when the query executes.

Conclusion

In this tutorial, we have tried to cover all the important aspects of the MySQL scheduled events that you must know. In the next tutorial, we will see how to create different types of scheduled events with simple and effective examples. We will also learn to modify the existing events in the database.