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:

• 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:

1. 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:

1. 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;

The following table resumes all the information that we can get from the 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

1. 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:

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?.

How to create MySQL database using node.js.

How to Get Current Date and Time in JavaScript.