Using MySQL Event Scheduler: A Complete Guide

MySQL Events Scheduler (or MySQL Scheduled events) allow us to schedule specific tasks to run automatically according to a time and interval schedule. Creating an event Scheduler means that you create a database object that contains one or more SQL statements which are executed in response to the passage of a specified time interval. This article will show you how to create and use MySQL Event Scheduler to automatize your database management.

Using the MySQL Event Scheduler: A Complete Guide

Related Articles:

This article will cover:

What is EVENT Privilege in MySQL?
MySQL Event Scheduler Configuration
MySQL Event Scheduler Syntax
How to create a MySQL Event Scheduler?
How to ALTER MySQL Event Scheduler?
How to Drop MySQL Event Scheduler?
Conclusion:
You might also like:

What is EVENT Privilege in MySQL?

The Event privilege controls the creation, modification, and deletion of events which means that an event is executed with the privileges of its definer (user), and it cannot perform any actions (such as SELECT, INSERT, DELETE … in SQL statement) for which its definer does not have the requisite privileges.

You can grant this privilege to a specific user by using the GRANT statement as follows:

The coming statement gives the EVENT privilege for the schema named myschemaName to the user userAccount.


 GRANT EVENT ON myschemaName.* TO userAccount;

2. To give the userAccount user the EVENT privilege on all Schemas, use the following statement:


 GRANT EVENT ON *.* TO userAccount;

3. Note that the EVENT privilege has a global or schema-level scope, meaning we can not grant an EVENT privilege to a user on a single table.

MySQL Event Scheduler Configuration

In MySQL, a special event schedule thread is in charge of the execution of the events. So when we speak of the events scheduler, we genuinely mean this thread. Users with the PROCESS privilege can check the status of the event scheduler thread by executing the following command:

 SHOW PROCESSLIST;

Whether the Event Scheduler is on and operating on the server is shown by the global event_scheduler system variable. The value of the event_scheduler variable influences the event schedule as the following:

event_schedule value	Event Scheduler status	event schedule thread	Output of SHOW PROCESSLIST
OFF (default value)	Stopped	Not running	Not shown in the output
ON	Started	Executes all scheduled events	the event scheduler thread is listed in the output

MySQL event_scheduler values

You can set the event_scheduler system variable to enable and start it using the below Command:

 SET GLOBAL event_scheduler = ON;

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

 SET GLOBAL event_scheduler = OFF;

MySQL Event Scheduler Syntax

As we already said, an Event schedule performs actions or tasks that are written in SQL statements, and it can be a one-time Event which means the tasks will execute only one, or It can be a recurrent event that repeats its action at a regular interval.

By default, an event’s schedule begins as soon as it is created and continues running until it is disabled or dropped, but we can change that by assigning a specific start (date and time) and end (date and time).

We can create, modify, and drop scheduled events using the several SQL statements provided by MySQL. In the following section, you will find out how to do that the right:

How to create a MySQL Event Scheduler?

To create a New Event scheduler in MySQL using the CREATE EVENT statement, you need to set up the minimum requirements such as name, an ON SCHEDULE clause to specify when and how often the event will execute, and a DO clause which contains an SQL statement to be executed by the event.

 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_body;

Clauses	Description and Values
schedule	In the ON SCHEDULE clause, you may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values it contains. Still, you can not use stored or loadable functions in such expressions and using them will fail with an error message. { AT timestamp [+ INTERVAL interval] … \| EVERY interval [STARTS timestamp [+ INTERVAL interval] …] [ENDS timestamp [+ INTERVAL interval] …] }
interval	You can combine intervals. For example, `AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY` is equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with `+ INTERVAL`. quantity {YEAR \| QUARTER \| MONTH \| DAY \| HOUR \| MINUTE \| WEEK \| SECOND \| YEAR_MONTH \| DAY_HOUR \| DAY_MINUTE \| DAY_SECOND \| HOUR_MINUTE \| HOUR_SECOND \| MINUTE_SECOND} To repeat actions at regular intervals, use EVERY clause. The EVERY keyword is followed by an interval as described in the previous clause. Although + INTERVAL clauses are not permitted in an EVERY clause, you can use the same complex time units allowed in a + INTERVAL.
EVERY	An EVERY clause may contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value that tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS. Not using ENDS means that the event continues to execute indefinitely. ENDS supports the same syntax for complex time units as STARTS does. You may use STARTS, ENDS, both, or neither in EVERY clause. If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously.
event_body	Contains the SQL statements of the DO clause that specifies an action executed by the event. Any valid MySQL statement that can be used in a stored routine can also be used as the action statement for a scheduled event.
event_name	The name of the event.
DEFINER	We set the DEFINER to the user account that created the event in ‘user_name‘@’host_name‘ format. This clause specifies the MySQL user account to be used when checking access privileges at event execution time. If the DEFINER clause is present, the user value should be a MySQL account specified as *‘user_name‘@’host_name‘, CURRENT_USER, or CURRENT_USER(). If the DEFINER clause is omitted, the default definer is the user who executes the CREATE EVENT* statement, and this is the same as specifying DEFINER = CURRENT_USER explicitly.
`IF NOT EXISTS`	IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named eventName already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.)
AT timestamp	AT timestamp is used for a one-time event. It specifies that the event executes only once at the date and time are given by the timestamp, which must include both the date and time or must be an expression that resolves to a datetime value. For this purpose, you may use a value of either the DATETIME orTIMESTAMP type. If the date is in the past, a warning occurs.
COMMENT	A COMMENT clause is used to supply a comment for an event. It is a string of up to 64 characters that describe the event. The comment text, being a string literal, must be surrounded by quotation marks.

MySQL Event Scheduler Syntax

The following table resumes all the information that we can get from the INFORMATION_SCHEMA EVENTS Table

Information about EVENT	Description
EVENT_NAME	The name of the event.
EVENT_SCHEMA	The name of the database to which the event belongs.
DEFINER	The user account that created the event in ‘user_name‘@’host_name‘ format.
TIME_ZONE	The event time zone is the time zone used for scheduling the event, which is in effect within the event as it executes. The default value is SYSTEM.
EVENT_BODY	The language used for the statements in the event’s DO clause. The value is always set to `SQL`.
EVENT_DEFINITION	Is the SQL statement executed by the event in the event’s DO clause.
EVENT_TYPE	The event can be either ONE TIME or a RECURRING (repeating) event.
`EXECUTE_AT`	For a one-time event, this is the DATETIME value specified in the AT clause of the CREATE EVENT statement used to create the event or of the last ALTER EVENT statement that modified the event. The value in this column reflects the addition or subtraction of any INTERVAL value included in the event’s AT clause. If the event’s timing is determined by an EVERY clause instead of an AT clause (that is, if the event is recurring), the value of this column is NULL.
INTERVAL_VALUE	For a recurring (repeating) event, the number of intervals to wait between event executions. For a transient event, the value is always `NULL`.
INTERVAL_FIELD	The time units are used for the interval in which a recurring event waits before repeating. For a transient event, the value is always NULL.
SQL_MODE	The SQL mode was in effect when the event was created or altered and under which the event executes. For the permitted values.
STARTS	The start date and time for a recurring event. This is displayed as a DATETIME value and is NULL if no start date and time are defined for the event. For a transient event, this column is always NULL. For a recurring event whose definition includes a STARTS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used. If there is no STARTS clause affecting the timing of the event, this column is NULL.
ENDS	For a recurring event whose definition includes an ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used. If no ENDS clause affects the event’s timing, this column is NULL.
STATUS	The value of the event status is one of ENABLED, DISABLED, or SLAVESIDE_DISABLED. The status SLAVESIDE_DISABLED indicates that the event’s creation occurred on another MySQL server acting as a replication source and replicated to the current MySQL server, which is acting as a replica. Still, the event is not presently being executed on the replica.
ON_COMPLETION	Takes one of the two values `PRESERVE` or `NOT PRESERVE`.
CREATED	It contains the date and time when the event was created. It is a TIMESTAMP value.
LAST_ALTERED	The date and time when the event was last modified. It is a TIMESTAMP value. If the event has not been modified since its creation, this value is the same as the CREATED value.
LAST_EXECUTED	The date and time when the event was last executed. This is also a DATETIME value. If the event has never been performed, this column is NULL. LAST_EXECUTED indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED.
EVENT_COMMENT	The text of the comment, if the event has one. If not, this value is empty.
ORIGINATOR	The server ID of the MySQL server on which the event was created; is used in replication. This value may be updated by ALTER EVENT to the server ID on which that statement occurs if executed on a replication source. The default value is 0.
CHARACTER_SET_CLIENT	The session value of the `character_set_client` system variable when the event was created.
COLLATION_CONNECTION	The session value of the `collation_connection` system variable when the event was created.
DATABASE_COLLATION	The collation of the database with which the event is associated.

MySQL INFORMATION_SCHEMA EVENTS Table

Now it’s time to give you a simple example of how to use the CREATE EVENT statement:
In this example, we create an event scheduler named eventName that is scheduled to run two hours after its creation and will increment the values of the column called col1 in schemaName.tableName by 3.

 CREATE EVENT eventName
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR
    DO
      UPDATE schemaName.tableName SET col1 = col1 + 3;

How to ALTER MySQL Event Scheduler?

To change one or more of an existing Event scheduler’s proprieties ( information), MySQL provides a specific statement to do that without dropping and recreating the Event. This statement is the ALTER EVENT statement.

Syntax of an ALTER EVENT statement:

Before you start using the statement, it is good to know that:

An ALTER EVENT statement works only with an event that exists already.
The DEFINER, ON SCHEDULE, ON COMPLETION, COMMENT ENABLE/DISABLE, and DO clauses are the same as the ones used with CREATE EVENT statement. For more details, go back to the section above.
A user can alter an event defined on a database only if he has the EVENT privilege. After a user executes a successful ALTER EVENT statement, he becomes the definer for the result event.
It is possible to change multiple information and clauses of an event in a single ALTER EVENT statement.
In an ALTER EVENT statement, you only need to specify the options you want to change; the omitted options will keep their existing values.

  ALTER
    [DEFINER = user]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]

Examples of ALTER EVENT statement on an existing event

In this example, we use the ALTER EVENT to change the existing event scheduler named eventName, which we have already created with the CREATE EVENT statement in the section above, to run one hour after its creation instead of two hours and increment the values of the column named col1 in schemaName.tableName by 1. In this statement, we modify only the ON SCHEDULE and DO clauses.

 ALTER EVENT eventName
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE schemaName.tableName SET col1 = col1 + 1;

2. Disable an Event Scheduler using ALTER EVENT statement:

 ALTER EVENT eventName
  DISABLE;

3. Move an Event Scheduler to a different database using ALTER EVENT statement:

 
ALTER EVENT schemaName.eventName
    RENAME TO newSchemaName.eventName;

How to Drop MySQL Event Scheduler?

An event Scheduler can be deleted entirely from the database server by its definer user( event privilege) using the DROP EVENT statement. It is immediately dropped if it has expired and the ON COMPLETION is set on NOT PRESERVE. So to keep an event scheduler in the server after its scheduled expiration date, you need to specify the clause ON COMPLETION PRESERVE when creating the event.

The following DROP EVENT statement drops the event named event_name. This will completely delete the event from the server.

We used the IF EXISTS in the DROP EVENT statement to prevent the statement from generating the error ERROR 1517 (HY000): Unknown event ‘event_name‘ results in case the event does not exist.

 
  DROP EVENT [IF EXISTS] event_name

Conclusion:

MySQL events schedulers are very useful in database management automatization, and this article showed you how to configure, create, alter and drop events scheduler in depth. I hope this was helpful to you.