Scheduled Events

Some database work has to happen again and again: delete login sessions nobody has used for a month, cancel orders that were never paid, build yesterday's sales summary before the manager arrives at 9 a.m. You could set an alarm and type the queries yourself, but MySQL can do it for you.

The Event Scheduler is a timer built into the MySQL server. An event is a named piece of SQL with a schedule: "run this once at 6 p.m." or "run this every night at 2 a.m.". In this lesson you will switch the scheduler on, create one-time and repeating events, build two realistic jobs, manage and debug events, and compare them with cron, the usual alternative. Events run only on a real MySQL server, but the SQL inside them runs in the browser too.

Turning the scheduler on

The scheduler is a background thread inside the server. Check whether it is running:

SHOW VARIABLES LIKE 'event_scheduler';

Variable_name   | Value
----------------+------
event_scheduler | ON
(1 row)

ON has been the default since MySQL 8.0. When it is on, SHOW PROCESSLIST; lists a connection whose user is event_scheduler and whose command is Daemon. If the value is OFF, an administrator can start it without restarting the server:

SET GLOBAL event_scheduler = ON;

A third value, DISABLED, can only be set in the server's configuration file at startup, and then the scheduler cannot be switched on at runtime. Events you create while it is off are stored but do not run. XAMPP's MariaDB ships with the scheduler OFF, so turn it on there, or add event_scheduler=ON under [mysqld] in my.ini to make it permanent. Creating events needs the EVENT privilege on the database, and some shared hosting plans do not allow it.

Your first events

A one-time event uses AT with a moment in time. This one ends a weekend sale on audio products in 48 hours, by putting back the ₹500 discount:

CREATE EVENT ev_end_weekend_sale
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 48 HOUR
DO
  UPDATE products SET price = price + 500 WHERE category = 'Audio';

After a one-time event runs, MySQL drops it automatically. Add ON COMPLETION PRESERVE if you want to keep it, disabled, as a record.

A repeating event uses EVERY with an interval, and optionally STARTS and ENDS. Login sessions are a good example: every visit adds a row, and old rows pile up forever unless something removes them.

CREATE EVENT IF NOT EXISTS ev_clean_sessions
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR
COMMENT 'Remove sessions idle for more than 30 days'
DO
  DELETE FROM user_sessions
  WHERE last_seen < NOW() - INTERVAL 30 DAY;

STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR means "tomorrow at 02:00", a quiet time for most Indian websites. Without STARTS, the first run happens immediately and the next one a day later. The interval can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR, and combined units such as '1:30' HOUR_MINUTE also work.

Always test the body on its own before you schedule it. Here it is with a fixed time in place of NOW() - INTERVAL 30 DAY, as if the event ran at 02:00 on 31 May 2025:

CREATE TABLE user_sessions (
  session_id VARCHAR(40) PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  last_seen DATETIME NOT NULL
);

INSERT INTO user_sessions (session_id, customer_id, last_seen) VALUES
  ('a91f', 1, '2025-03-02 10:15:00'),
  ('b72c', 2, '2025-04-28 21:40:00'),
  ('c33d', 5, '2025-05-29 08:05:00'),
  ('d48e', 8, '2025-05-30 23:55:00');

-- The event body, with a fixed time in place of NOW() - INTERVAL 30 DAY
DELETE FROM user_sessions
WHERE last_seen < '2025-05-01 02:00:00';

SELECT session_id, customer_id, last_seen
FROM user_sessions
ORDER BY last_seen;

session_id | customer_id | last_seen
-----------+-------------+--------------------
c33d       | 5           | 2025-05-29 08:05:00
d48e       | 8           | 2025-05-30 23:55:00
(2 rows)

The two sessions idle since March and April are gone; the recent ones stay.

A nightly summary table

Dashboards that add up every order on every page view get slower as the shop grows. A common fix is a summary table, filled once a night, that the dashboard reads instead. This is the summary the event will build, shown here for all of April 2025 so you can run it:

CREATE TABLE daily_sales (
  sale_date DATE PRIMARY KEY,
  orders INTEGER NOT NULL,
  revenue DECIMAL(12,2) NOT NULL
);

-- What the nightly event inserts, here for every day in April 2025
INSERT INTO daily_sales (sale_date, orders, revenue)
SELECT o.order_date,
       COUNT(DISTINCT o.order_id),
       SUM(oi.quantity * oi.unit_price)
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
  AND o.order_date >= '2025-04-01'
  AND o.order_date < '2025-05-01'
GROUP BY o.order_date;

SELECT * FROM daily_sales ORDER BY sale_date;

sale_date  | orders | revenue
-----------+--------+--------
2025-04-01 | 1      | 32797
2025-04-10 | 1      | 2199
2025-04-18 | 1      | 8695
(3 rows)

The real event summarises only yesterday, and has several statements, so its body goes inside BEGIN ... END. In the mysql client you must change the statement delimiter first, so the semicolons inside the body do not end the CREATE EVENT early (the same trick is used for stored procedures):

DELIMITER //

CREATE EVENT ev_daily_sales
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 30 MINUTE
DO
BEGIN
  DELETE FROM daily_sales WHERE sale_date = CURDATE() - INTERVAL 1 DAY;

  INSERT INTO daily_sales (sale_date, orders, revenue)
  SELECT o.order_date, COUNT(DISTINCT o.order_id), SUM(oi.quantity * oi.unit_price)
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.order_date = CURDATE() - INTERVAL 1 DAY
  GROUP BY o.order_date;

  INSERT INTO job_log (job_name, finished_at) VALUES ('daily_sales', NOW());
END //

DELIMITER ;

Three design choices make this job reliable. The DELETE first makes the event safe to run twice: a second run replaces yesterday's row instead of failing on the primary key. Running at 00:30 leaves a margin for orders placed just before midnight. And the last line writes to a small job_log table, so you can see when the job last succeeded. MySQL Workbench and phpMyAdmin handle the delimiter for you; the DELIMITER lines are only for the command-line client.

Managing events

SHOW EVENTS FROM shop;

SELECT EVENT_NAME, STATUS, INTERVAL_VALUE, INTERVAL_FIELD, LAST_EXECUTED
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = 'shop';

ALTER EVENT ev_clean_sessions DISABLE;                       -- pause it
ALTER EVENT ev_clean_sessions ENABLE;                        -- resume it
ALTER EVENT ev_clean_sessions ON SCHEDULE EVERY 6 HOUR;      -- change the timing
DROP EVENT IF EXISTS ev_end_weekend_sale;

LAST_EXECUTED tells you when an event last started. An event runs with the privileges of its definer, the account that created it. If that account is later dropped, the event stops working, so create long-lived events with a dedicated maintenance account rather than a person's own login.

When events go wrong

Events run with nobody watching, so their problems are easy to miss:

• Errors are silent. A failing event writes a message to the server's error log and nowhere else. Check the log, LAST_EXECUTED, and your own job_log table.
• Runs can overlap. If a run takes longer than the interval, the next run starts anyway, and two copies work on the same rows. Keep jobs short, or guard them with GET_LOCK().
• Missed runs are skipped. If the server is down at 02:00, that night's run does not happen later. A job that summarises "yesterday" should be able to fill a gap when you run it by hand.
• Results go nowhere. A SELECT inside an event shows nothing to anyone. Write results into a table.
• Time zones. The schedule uses the session time zone at the moment you create the event (see Date & Time Functions). Create events while connected with the time zone you intend.

Events or cron?

The main alternative is an operating-system scheduler: cron on Linux, Task Scheduler on Windows, or the "cron jobs" page of a hosting control panel. It runs a script, for example php artisan schedule:run or a PHP file, at set times.

Use events for small, pure-SQL housekeeping that belongs to the data itself, such as cleanups and summary tables. Use cron when the job sends emails, calls other services, touches several systems, or needs proper logging and alerts. Many teams keep all scheduled work in the application's scheduler so that it is versioned and tested with the rest of the code.

Key points

• The Event Scheduler runs SQL on a schedule inside MySQL; event_scheduler is ON by default since 8.0.
• ON SCHEDULE AT ... creates a one-time event; EVERY ... STARTS ... ENDS ... creates a repeating one.
• One-time events are dropped after running unless you add ON COMPLETION PRESERVE.
• Multi-statement bodies use BEGIN ... END, with DELIMITER in the command-line client.
• Make jobs safe to run twice, log their success, and keep them short.
• Manage events with SHOW EVENTS, INFORMATION_SCHEMA.EVENTS, ALTER EVENT and DROP EVENT; prefer cron for jobs that go beyond SQL.

CREATE EVENT ev_cancel_unpaid
ON SCHEDULE EVERY 1 HOUR
COMMENT 'Cancel orders pending for more than 2 days'
DO
  UPDATE orders
  SET status = 'cancelled'
  WHERE status = 'pending'
    AND order_date < CURDATE() - INTERVAL 2 DAY;

-- Test version of the body (runs in the browser)
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
  AND order_date < '2025-05-23';

SELECT order_id, order_date, status FROM orders WHERE order_id IN (111, 115);