An app that sends login OTPs ends up with a table full of codes that expired minutes after they were created. Somebody has to delete them, every few minutes, forever. You could write a cron job on a Linux server, but MySQL can do it by itself: an event is a named SQL task that the server's Event Scheduler runs at a set time or on a repeating interval. Think of it as cron that lives inside the database.
Step 1: check that the scheduler is on
SHOW VARIABLES LIKE 'event_scheduler';
| Variable_name | Value |
|---|---|
| event_scheduler | ON |
In MySQL 8.0 the default is ON. If you see OFF, run SET GLOBAL event_scheduler = ON; (needs an admin privilege) and add event_scheduler=ON under [mysqld] in my.cnf / my.ini so it survives a restart. If it shows DISABLED, the server was started with the scheduler disabled and it cannot be switched on at runtime. To create events you also need the EVENT privilege on the schema.
Step 2: a recurring event
CREATE TABLE otp_codes (
id INT AUTO_INCREMENT PRIMARY KEY,
phone VARCHAR(15),
code CHAR(6),
expires_at DATETIME NOT NULL
);
INSERT INTO otp_codes (phone, code, expires_at) VALUES
('9876500001', '482913', NOW() - INTERVAL 20 MINUTE),
('9876500002', '730155', NOW() - INTERVAL 2 MINUTE),
('9876500003', '118204', NOW() + INTERVAL 5 MINUTE);
CREATE EVENT ev_purge_expired_otp
ON SCHEDULE EVERY 10 MINUTE
COMMENT 'Delete OTP codes that have expired'
DO
DELETE FROM otp_codes WHERE expires_at < NOW();
After the event has fired once (we tested it with EVERY 2 SECOND so we did not have to wait), only the unexpired code remains:
SELECT id, phone, code FROM otp_codes;
| id | phone | code |
|---|---|---|
| 3 | 9876500003 | 118204 |
A recurring event without STARTS fires for the first time as soon as it is created, then once every interval.
Step 3: a multi-statement event at a fixed time
A common real job: every night at 01:00, write yesterday's order totals into a summary table so the morning dashboard does not scan the whole orders table. A body with several statements needs BEGIN ... END, and in the mysql client you change the delimiter so the inner semicolons do not end the CREATE EVENT early (Workbench handles this the same way in its SQL editor).
CREATE TABLE daily_sales_summary (
summary_date DATE PRIMARY KEY,
orders INT,
revenue DECIMAL(12,2)
);
DELIMITER $$
CREATE EVENT ev_daily_sales_summary
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 1 HOUR) -- tomorrow 01:00
ON COMPLETION PRESERVE
DO
BEGIN
INSERT INTO daily_sales_summary (summary_date, orders, revenue)
SELECT * FROM (
SELECT DATE(order_date) AS d, COUNT(*) AS n, SUM(amount) AS total
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL 1 DAY
AND order_date < CURRENT_DATE
GROUP BY DATE(order_date)
) AS s
ON DUPLICATE KEY UPDATE orders = s.n, revenue = s.total;
END$$
DELIMITER ;
Running the body by hand on 16 November 2026, with two orders (₹1,200 and ₹800) placed the previous day and one placed today, produced:
| summary_date | orders | revenue |
|---|---|---|
| 2026-11-15 | 2 | 2000.00 |
The ON DUPLICATE KEY UPDATE makes the job safe to re-run: running it twice on the same night updates the row instead of failing on the primary key. (The derived table s is used instead of the older VALUES() function, which is deprecated from MySQL 8.0.20.)
One-time events
CREATE EVENT ev_end_festive_sale
ON SCHEDULE AT '2026-11-20 23:59:59'
DO UPDATE products SET discount_pct = 0 WHERE campaign = 'festive';
A one-time (AT) event is dropped automatically after it runs, because the default is ON COMPLETION NOT PRESERVE. Add ON COMPLETION PRESERVE if you want to keep its definition for reference. The same rule applies to a recurring event with an ENDS time.
Managing events
SHOW EVENTS; -- events in the current schema
SELECT EVENT_NAME, EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STATUS, LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();
ALTER EVENT ev_purge_expired_otp DISABLE; -- pause
ALTER EVENT ev_purge_expired_otp ENABLE; -- resume
ALTER EVENT ev_purge_expired_otp ON SCHEDULE EVERY 5 MINUTE;
DROP EVENT IF EXISTS ev_purge_expired_otp;
The information_schema query on our test server (2-second test interval, LAST_EXECUTED column left out here):
| EVENT_NAME | EVENT_TYPE | INTERVAL_VALUE | INTERVAL_FIELD | STATUS |
|---|---|---|---|---|
| ev_purge_expired_otp | RECURRING | 2 | SECOND | ENABLED |
LAST_EXECUTED is the quickest way to confirm that an event is actually running.
Common mistakes
- Scheduler off. The event is created without any error but never runs. Some managed and shared hosting plans keep it off; check the variable first.
- Silent failures. An event has no client to report errors to. Failures go to the MySQL error log, so test the body as a normal query first, or have the event call a stored procedure that writes to a log table.
- Time zone surprises. Schedules follow the event's time zone, which is the session time zone when it was created. A server on UTC runs "01:00" at 06:30 IST.
- Overlapping runs. If a job can take longer than its interval, a new run can start before the last one finishes. Keep heavy jobs on longer intervals or guard them with
GET_LOCK(). - Replicas. Events created on a source server are copied to replicas in a disabled state, so the job runs only once (on the source).
- Long deletes. Deleting millions of rows in one event locks a lot. Delete in batches with
LIMIT, or look at partitioning and drop old partitions instead.
Event or trigger?
A trigger fires because a row changed; an event fires because the clock says so. Use an event for clean-up, summaries and periodic maintenance; use a trigger to react to a specific INSERT, UPDATE or DELETE. For the date arithmetic used in schedules, see MySQL date and time functions.