The MariaDB Event Scheduler is often misunderstood as just a cron-like utility for running SQL statements on a schedule, but its true power lies in its tight integration with the database engine itself, allowing for complex, transaction-aware operations that are impossible with external schedulers.
Let’s see it in action. Imagine you have a table orders and you want to automatically clean up old, completed orders older than 30 days.
-- First, create a sample table and some data
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATETIME,
status VARCHAR(50),
details TEXT
);
INSERT INTO orders (order_date, status, details) VALUES
(NOW() - INTERVAL 40 DAY, 'COMPLETED', 'Order A'),
(NOW() - INTERVAL 20 DAY, 'PENDING', 'Order B'),
(NOW() - INTERVAL 50 DAY, 'COMPLETED', 'Order C'),
(NOW() - INTERVAL 10 DAY, 'COMPLETED', 'Order D');
-- Now, let's create the event to clean up old orders
DELIMITER //
CREATE EVENT cleanup_old_orders
ON SCHEDULE EVERY 1 DAY STARTS NOW() + INTERVAL 1 MINUTE
DO
BEGIN
DELETE FROM orders WHERE status = 'COMPLETED' AND order_date < NOW() - INTERVAL 30 DAY;
END //
DELIMITER ;
-- Check that the event was created and is enabled
SHOW EVENTS;
This SHOW EVENTS; output would reveal cleanup_old_orders, its schedule, and its status. The DO block is where the SQL logic lives.
The Event Scheduler solves the problem of automating repetitive database tasks directly within the database. Unlike external cron jobs that might execute SQL scripts, the Event Scheduler runs inside MariaDB. This means it benefits from the database’s transaction isolation, concurrency control, and immediate access to data. You don’t need to worry about external dependencies, network issues between your scheduler and the database, or passing credentials securely. The scheduler is a feature of the mysqld process itself.
Internally, the Event Scheduler is managed by a dedicated thread within the MariaDB server. When you create an event, its definition is stored in the mysql.event table. The scheduler thread periodically scans this table, checks the next_event timestamp for each event, and executes the event_body (the SQL code within the DO block) when the next_event time is reached. The event_body is executed as a separate SQL statement or block, respecting the user privileges defined for the event creator.
You have several levers to control events:
CREATE EVENT: To define a new scheduled task.ALTER EVENT: To modify an existing event’s schedule or body.DROP EVENT: To remove an event.SHOW EVENTS: To list all defined events, their schedules, and statuses.SET GLOBAL event_scheduler = ON/OFF;: This is crucial. The scheduler itself must be enabled at the server level for any events to run. You can check its status withSELECT @@global.event_scheduler;.
The STARTS clause is particularly interesting. You can specify an exact timestamp ('2023-10-27 03:00:00') or a relative time (NOW() + INTERVAL 5 MINUTE). This allows for precise control over when an event first becomes active. The EVERY clause defines the recurrence interval, which can be as granular as seconds.
One aspect that often surprises people is how events handle errors. If an event’s event_body fails, by default, the event simply stops executing and will not run again until manually re-enabled or fixed. However, you can add error handling within the DO block using standard SQL DECLARE CONTINUE HANDLER statements to gracefully manage failures, log them, or retry operations without the entire event thread halting. This robust error management is a significant advantage over simpler scheduling mechanisms.
The next step in mastering MariaDB automation is understanding how to use stored procedures and functions within your events to encapsulate complex logic and improve maintainability.