MySQL Scheduled Events Explained

What is an Event in MySQL?

An event is a named object that contains SQL statements. MySQL Events are those tasks which run according to a user-defined schedule. Because of this reason, it referred to as Scheduled Event. The Event Scheduler is a type of special thread that executes the Events at a scheduled time. A MySQL Event is similar to a CRON JOB in LINUX or a Task Scheduler in Windows.

MySQL Events are helpful in the following:

• Facilitating database management and periodical database operational tasks
• Optimising database tables
• Cleaning up logs
• Archiving data
• Generating reports

How Stored Procedure, Trigger and Event are executed?

• A stored procedure is only executed when you invoke it directly.
• A trigger is executed when an event associated with a table such as INSERT, UPDATE or DELETE occurs.
• An event can be executed at once or more regular interval.

How to do Event Scheduler Configuration in MySQL?

A special thread called Event scheduler Thread that MySQL uses to execute all scheduled events. We can see the status of event scheduler thread by executing the below command:

SHOW PROCESSLIST;

By default, the event scheduler thread is not enabled. To enable and start the MySQL Event Scheduler Thread, you need to execute the following command:

SET GLOBAL event_scheduler = ON;    // ON – to enable and start, OFF – to disable and stop

How to create new Event in MySQL?

Creating an event is similar to creating other database objects such as stored procedures or triggers.

Synatx:

CREATE EVENT [IF NOT EXIST] event_name ON SCHEDULE schedule DO event_body;

• First, we specify the event name after the CREATE EVENT clause. The event name you specify must be unique within the database schema.
• Second, we put a schedule after the ON SCHEDULE clause.
• If the event is a one-time event, we use syntax: AT timestamp [+ INTERVAL].
• If the event is a recurring event, we use the EVERY clause: EVERY interval STARTS timestamp [+ INTERVAL] ENDS timestamp [+ INTERVAL].
• Third, we place the SQL statements after the DO keyword. It is important to note that we can call a stored procedure inside the body of the event. In case you have compound SQL statements, you can wrap them in a BEGIN END block.

Example:

CREATE EVENT [IF NOT EXIST] insert_message_event
ON SCHEDULE AT CURRENT_TIMESTAMP
DO 
INSERT INTO message(message_body, created_at) VALUES(‘test message’, NOW());

An event will be automatically dropped when it is expired. In above example, it is a one-time event that is executed and expires on completion. To change this behavior, you should make use of ON COMPLETION PRESERVE clause. 

Example:

CREATE EVENT [IF NOT EXIST] insert_message_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO 
INSERT INTO message(message_body, created_at) VALUES(‘test message’, NOW());

To create a recurring event that executes every five minute and expires in 1 hour from its creation time, you can do as follows: 

Example:

CREATE EVENT [IF NOT EXIST] insert_message_event
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
INSERT INTO message(message_body, created_at) VALUES(‘test message’, NOW());

How to view all events of a database schema in MySQL?

You can see all events of a database schema by executing the following command:

SHOW EVENTS FROM hrmis_database;

How to modify an event in MySQL?

We can modify an event using ALTER EVENT as follows:

Synatx:

ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
event_body

Example:

ALTER EVENT insert_message_event
ON SCHEDULE EVERY 2 MINUTE
ON COMPLETION NOT PRESERVE
RENAME TO new_insert_message_event
DISABLE
DO
INSERT INTO message(message_body, created_at) VALUES(‘test message’, NOW());

We can move an event from one database to another database as follows:

Example:

ALTER EVENT hrmis_database.insert_message_event
RENAME TO new_database.insert_message_event;

How to drop an event in MySQL?

We can drop an event as follows:

Synatx:

DROP EVENT [IF EXIST] event_name;

Example:

DROP EVENT [IF EXIST] insert_message_event;