MySQL Scheduled Events

MySQL Scheduled Events

Last Updated: 17 Sep, 2022
What is an Event in MySQL?

An event is a named object that contains SQL statements. MySQL Events are tasks that run according to a user-defined schedule. Because of this reason, it referred to as Scheduled Event. The Event Scheduler is a 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
  • Optimizing database tables
  • Cleaning up logs
  • Archiving data
  • Generating reports
How Stored Procedure, Trigger and Event are executed?
  • A stored procedure is only executed when it is invoked 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?

MySQL uses a special thread called event schedule thread to execute all scheduled events. You can see the status of event scheduler thread by executing the following command:

SHOW PROCESSLIST;

By default, the event scheduler thread is not enabled. To enable and start the 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 must be unique within a 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 is 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 can use the 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;

 

Thank You, Please Share.