Mysql Event Scheduler

WHAT IS MYSQL EVENT SCHEDULER

MySQL events are stored objects designed to execute based on a predefined schedule. Unlike triggers, which respond to actions on a specific table, events execute statements in response to a specified time interval. The Event Scheduler acts as the database's internal equivalent to a Linux crontab.

WHY BOTHER ABOUT MYSQL EVENT SCHEDULER

As engineers, we often need to perform cleanup operations on ephemeral data stored in databases. While using a worker service for these tasks is common, the MySQL Event Scheduler offers a powerful built-in feature that allows your database to self-maintain and automate critical, repetitive SQL tasks without relying on external services.

THINGS TO NOTE WHEN USING MYSQL EVENT SCHEDULER

While using the MySQL Event Scheduler is straightforward, its powerful, background nature requires careful consideration to avoid performance issues and unexpected behaviors.

• If a recurring event does not terminate within its scheduling interval, multiple instances of the same event may execute simultaneously. This can be prevented using the GET_LOCK function or low locking.

• If an event fails (e.g., due to a syntax error or a deadlock), it does not stop the event from being scheduled again at the next interval, leading to cascading failure.

ENABLING THE EVENT SCHEDULER

The event scheduler is usually disabled by default. You can check its status by running the query below.

It should show a result like this:

show variables like '%event_scheduler%';

-- OR 

SELECT @@event_scheduler;

If the value is OFF, enable it using the query below (NOTE: This can only be enabled by users with SUPER or SYSTEM_VARIABLES_ADMIN privileges).

SET GLOBAL event_scheduler = ON;

USAGE

For the cause of this article, this would be our product story:

Product Story: We operate a system that tracks unique user search queries, similar to a browser's search history, stored in a table called unique_searches. Over time, this ephemeral data accumulates rapidly, leading to table bloat. We need an automated process to run nightly to clear any search queries in the unique_searches table that are older than 30 days.

1. Create the database table.

    create table unique_searches (
        id int auto_increment primary key,
        query_text VARCHAR(255) NOT NULL,
        md5_hash VARCHAR(255) NOT NULL,
        user_id INT,
        created_at datetime DEFAULT CURRENT_TIMESTAMP
    );
   

2. Create a stored procedure to handle the cleanup logic.

    DELIMITER $$
    create procedure purge_old_user_searches()
    begin
        if (get_lock('search_cleanup_lock', 0) = 1) then
            delete FROM unique_searches
            WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
   
            select release_lock('search_cleanup_lock');
        end if;
    end;
    DELIMITER ;
   

3. Create a scheduled event.

    create event cleanup_old_searches on schedule every 1 day starts '2025-11-14 00:00:00'
    do call purge_old_user_searches();
   

OTHER SQL COMMANDS

1. Deleting an event.

    drop event cleanup_old_searches;
   

2. Altering an event.

    alter event cleanup_old_searches ON SCHEDULE every 2 day ends '2025-11-18 13:55:00';
   

CLOSING REMARKS

While the Event Scheduler excels at internal tasks, it's important to understand its limitations.

Rule of Thumb: If the task requires interaction with anything outside of the MySQL server, use an external scheduler (like Cron or Task Scheduler).

1. The event scheduler cannot be used for database backups.

2. The event scheduler cannot initiate network requests.