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:
- Password Hashing in MySQL 8.0: Authentication Plugins
- Best practices for optimizing MySQL database performance.
- Automating Your MySQL Database with Triggers.
- How to use ON DELETE CASCADE in MySQL.
- MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
- How to implement One-To-One association in MySQL/Node.js API using async/await.
- How to implement One-To-Many association in MySQL/Node.js API.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API.
- How to interact with MySQL database using async/await promises in node.js ?
- How to create MySQL database using node.js.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API
- How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
- Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.
- How to store Session in MySQL Database using express-mysql-session.
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 |
- 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. |
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. |
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.
You might also like:
Password Hashing in MySQL 8.0: Authentication Plugins
Best practices for optimizing MySQL database performance.
Automating Your MySQL Database with Triggers.
How to use ON DELETE CASCADE in MySQL.
MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
Using MySQL Event Scheduler: A Complete Guide.
Complete JWT Authentication and Authorization System for MySQL/Node.js API.
How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.
Nodemailer + Gmail: How to Send Emails from Node.js API using Gmail.
How to store Session in MySQL Database using express-mysql-session.
How to interact with MySQL database using async/await promises in node.js ?
How to use Sequelize async/await to interact with MySQL database in Node.js.
MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
How to add Routes to insert data into MySQL database-related tables in Node.js API?
Example How to use initialize() Function in Node.js/Express API .
Why is Connection Pooling better than Single Connection?.