Creating Scheduled Events for Automated Database Tasks in MySQL

Course: MySQL Training

Topic: Creating Scheduled Events for Automated Database Tasks

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Scheduled Events in MySQL

MySQL provides a feature called the Event Scheduler that allows database administrators and developers to automate SQL tasks at specified times or recurring intervals. Scheduled events can perform operations such as deleting expired records, updating account statuses, generating summary reports, maintaining log tables, and archiving old data without requiring a person to execute the SQL statements manually.

A scheduled event is a named database object containing one or more SQL statements that MySQL executes according to a defined schedule. It is similar to a cron job in Linux or a scheduled task in Windows, but the task is managed by the MySQL server.

For example, an online shopping application may need to mark unpaid orders as expired after 24 hours. Instead of running the update query manually every day, a MySQL event can execute the operation automatically at a scheduled interval.

1.1 What Is a Scheduled Event?

A scheduled event is a database object that runs a specified SQL statement once at a particular time or repeatedly according to a schedule.

Example:

CREATE EVENT daily_task
ON SCHEDULE EVERY 1 DAY
DO
    UPDATE orders
    SET status = 'Expired'
    WHERE status = 'Pending'
      AND created_at < NOW() - INTERVAL 24 HOUR;

This example creates an event that runs every day and updates pending orders older than 24 hours. The example assumes that the orders table contains the columns shown.

1.2 What Is the Event Scheduler?

The Event Scheduler is the MySQL server component responsible for identifying and executing scheduled events. Creating an event defines the task and its schedule, but the Event Scheduler must be enabled for the event to run automatically.

1.3 Why Are Scheduled Events Important?

• They automate repetitive database operations.
• They reduce the need for manual execution of SQL queries.
• They help maintain data consistency and cleanliness.
• They can perform routine data archiving and cleanup.
• They support scheduled summaries and reporting tables.
• They help execute recurring maintenance tasks at predictable intervals.

2. Learning Objectives

After completing this topic, learners should be able to:

• Explain scheduled events and the MySQL Event Scheduler.
• Understand the difference between events, triggers, and stored procedures.
• Check the status of the Event Scheduler.
• Enable the Event Scheduler when permitted.
• Create one-time and recurring events using CREATE EVENT.
• Use AT, EVERY, STARTS, and ENDS.
• Write events containing single SQL statements and compound statements.
• Inspect, modify, disable, enable, and delete scheduled events.
• Identify common errors and apply safe scheduling practices.

3. Understanding the Difference Between Events, Triggers, and Stored Procedures

Although events, triggers, and stored procedures can all execute SQL statements, they are designed for different purposes.

Important distinction: A trigger responds to a database operation, whereas a scheduled event responds to the passage of time. A stored procedure defines reusable logic but does not independently provide a recurring schedule.

4. Prerequisites for Creating Scheduled Events

Before creating scheduled events, confirm that the MySQL server is running, that you have selected the correct database, that the relevant tables exist, and that your database account has the required privileges.

4.1 Select the Database

SHOW DATABASES;
USE company_db;

Replace company_db with the actual database name. The event belongs to a database schema, so selecting the correct schema helps avoid creating an event in the wrong location.

4.2 Check the Event Scheduler Status

SHOW VARIABLES LIKE 'event_scheduler';

This command displays the current value of the event_scheduler system variable.

4.3 Enable the Event Scheduler

If the scheduler is OFF and your account has the necessary system-variable privileges, you can enable it at runtime:

SET GLOBAL event_scheduler = ON;

Verify the setting again:

SHOW VARIABLES LIKE 'event_scheduler';

Note: Changing a global variable requires sufficient privileges. Runtime changes may not persist after a server restart, depending on the server configuration. To make the setting persistent, configure the server appropriately, for example by setting event_scheduler=ON in the MySQL server configuration and restarting the server when required. Follow your hosting provider's instructions if you use managed MySQL hosting.

4.4 Required Privileges

Creating a scheduled event requires the EVENT privilege for the database in which the event is created. The event also needs appropriate privileges to perform its SQL operations when it runs.

For example, an event that updates an orders table needs suitable update privileges for the account under which the event executes. Having permission to create an event does not automatically guarantee that every statement inside the event will succeed.

5. Understanding the CREATE EVENT Statement

The CREATE EVENT statement defines a new scheduled event in MySQL.

5.1 General Syntax

CREATE EVENT event_name
ON SCHEDULE schedule_definition
DO
    sql_statement;

The schedule definition specifies when and how often the event runs. The DO clause contains the SQL statement that MySQL executes.

5.2 Main Components

6. Creating a One-Time Scheduled Event

A one-time event runs once at a specified date and time. Use the AT keyword when an operation should occur only one time.

6.1 Schedule an Event One Hour from Now

CREATE EVENT one_time_task
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('One-time task', CURRENT_TIMESTAMP);

This event is scheduled to insert a log record approximately one hour after the event's schedule is established. The example assumes a task_logs table with compatible columns already exists.

6.2 Schedule an Event for a Specific Date and Time

CREATE EVENT scheduled_report
ON SCHEDULE AT '2027-01-15 09:00:00'
DO
    INSERT INTO report_logs (report_name, created_at)
    VALUES ('Scheduled report', CURRENT_TIMESTAMP);

This example schedules an event for the specified date and time. Use a future date appropriate to your environment. A past schedule may expire immediately and, by default, the event may be removed after completion.

6.3 When Should You Use One-Time Events?

• To execute a database operation at a future date and time.
• To create a scheduled one-time maintenance record.
• To perform a planned data update.
• To execute a single cleanup task at a defined time.

7. Creating Recurring Events with EVERY

The EVERY keyword is used when an event must repeat at a regular interval. The interval can be specified in seconds, minutes, hours, days, weeks, months, quarters, or years, subject to MySQL's supported interval syntax.

7.1 Run an Event Every Day

CREATE EVENT daily_log_task
ON SCHEDULE EVERY 1 DAY
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('Daily task', CURRENT_TIMESTAMP);

This event inserts a log record each time it executes on its daily schedule.

7.2 Run an Event Every Hour

CREATE EVENT hourly_log_task
ON SCHEDULE EVERY 1 HOUR
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('Hourly task', CURRENT_TIMESTAMP);

This event is appropriate for demonstrations or lightweight hourly database operations. In production, consider whether the operation needs to run every hour and whether its execution time and resource usage are acceptable.

7.3 Run an Event Every Five Minutes

CREATE EVENT five_minute_task
ON SCHEDULE EVERY 5 MINUTE
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('Five-minute task', CURRENT_TIMESTAMP);

The task is scheduled to run every five minutes. Avoid creating unnecessarily frequent events, especially when their SQL statements scan or modify large tables.

7.4 Common Scheduling Intervals

8. Using STARTS and ENDS

The STARTS and ENDS clauses provide more control over a recurring event's active schedule. STARTS specifies when the repeated schedule begins, while ENDS specifies when it stops.

8.1 Schedule an Event to Start Tomorrow

CREATE EVENT future_daily_task
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('Future daily task', CURRENT_TIMESTAMP);

This event is configured to begin its daily schedule one day after the current time.

8.2 Schedule an Event with an End Date

CREATE EVENT temporary_daily_task
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 7 DAY
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('Temporary scheduled task', CURRENT_TIMESTAMP);

This example schedules a daily task that begins in the future and has an end time approximately seven days after creation. The actual execution count depends on the start and end timestamps and the interval boundaries.

8.3 Start and End Options

• Without STARTS: The recurring schedule normally begins at the current time.
• With STARTS: The recurring schedule begins at the specified future or explicit timestamp.
• Without ENDS: The recurring schedule has no scheduled end time and continues until disabled, dropped, or otherwise stopped.
• With ENDS: The recurring schedule stops at the specified end time.

9. Writing Multiple SQL Statements Inside an Event

A scheduled event can contain a compound statement using BEGIN and END. This is useful when an automated task needs to perform several related operations.

9.1 Example: Insert a Summary and Clean Old Data

Suppose an application has an activity_logs table and a daily_activity_summary table. A scheduled event can store a summary before deleting old log records.

DELIMITER //

CREATE EVENT daily_activity_maintenance
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    INSERT INTO daily_activity_summary
        (summary_date, total_records)
    SELECT
        CURRENT_DATE,
        COUNT(*)
    FROM activity_logs
    WHERE DATE(created_at) = CURRENT_DATE - INTERVAL 1 DAY;

    DELETE FROM activity_logs
    WHERE created_at < CURRENT_TIMESTAMP - INTERVAL 90 DAY;
END//

DELIMITER ;

Explanation:

1. The event is scheduled to run every day.
2. The first statement calculates the number of activity log records from the previous calendar day and inserts a summary.
3. The second statement removes records older than 90 days.
4. The delimiter is changed temporarily so the client does not treat the first semicolon inside the compound statement as the end of the complete event definition.
5. The delimiter is restored after the event is created.

This example assumes that both tables exist and their columns match the statements. If this task is used in production, ensure the summary is not duplicated when an event is rerun or retried, and test the deletion condition carefully before enabling it.

9.2 Calling a Stored Procedure from an Event

An event can also call a stored procedure. This is helpful when the business logic is complex and needs to be reused elsewhere.

CREATE EVENT daily_processing_event
ON SCHEDULE EVERY 1 DAY
DO
    CALL process_daily_records();

The stored procedure process_daily_records() must already exist and must be executable by the account used for event execution.

10. Practical Example: Automatically Expire Old Pending Orders

One common use of scheduled events is changing the status of orders that have remained unpaid for too long.

10.1 Example Table Structure

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'Pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

10.2 Insert Sample Records

INSERT INTO orders (customer_name, status, created_at)
VALUES
('Amit', 'Pending', CURRENT_TIMESTAMP - INTERVAL 2 DAY),
('Neha', 'Pending', CURRENT_TIMESTAMP - INTERVAL 2 HOUR),
('Ravi', 'Paid', CURRENT_TIMESTAMP - INTERVAL 3 DAY);

10.3 Create the Scheduled Event

CREATE EVENT expire_pending_orders
ON SCHEDULE EVERY 1 DAY
DO
    UPDATE orders
    SET status = 'Expired'
    WHERE status = 'Pending'
      AND created_at < CURRENT_TIMESTAMP - INTERVAL 24 HOUR;

10.4 Understand the Result

• Amit's order is older than 24 hours and has a Pending status, so it qualifies for the update when the event executes.
• Neha's order is only two hours old, so it does not qualify.
• Ravi's order is already Paid, so it does not qualify.

The event runs on its configured schedule, not immediately just because it was created. If the business rule requires expiry exactly 24 hours after each order was created, a daily event may introduce a delay of up to roughly one scheduling interval. Choose the interval according to the required business behavior.

11. Practical Example: Delete Old Temporary Records

Temporary records, expired sessions, and old application logs can accumulate over time. A scheduled event can clean up records that are no longer needed.

11.1 Example Table

CREATE TABLE temporary_sessions (
    session_id INT PRIMARY KEY AUTO_INCREMENT,
    session_token VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

11.2 Create a Cleanup Event

CREATE EVENT cleanup_old_sessions
ON SCHEDULE EVERY 1 DAY
DO
    DELETE FROM temporary_sessions
    WHERE created_at < CURRENT_TIMESTAMP - INTERVAL 30 DAY;

This event is scheduled to remove session records older than 30 days each day.

11.3 Safety Recommendations

• Confirm the retention period with the application's requirements.
• Test the DELETE condition using a SELECT query first.
• Keep a backup or archive if records may be required later.
• Consider indexing the date column when the table is large.
• Monitor how many records are removed and how long the task takes.

To preview matching records before deletion, use:

SELECT *
FROM temporary_sessions
WHERE created_at < CURRENT_TIMESTAMP - INTERVAL 30 DAY;

12. Viewing and Managing Scheduled Events

After creating events, you should inspect their definitions and statuses to confirm that they are configured as intended.

12.1 List Events in the Current Database

SHOW EVENTS;

This command lists events associated with the current database, including available schedule and status information.

12.2 List Events from a Specific Database

SHOW EVENTS FROM company_db;

Replace company_db with the target schema name.

12.3 Display an Event Definition

SHOW CREATE EVENT expire_pending_orders;

This command displays the SQL definition of the event, which is useful for reviewing its schedule and action statement.

12.4 Inspect Event Metadata

SELECT
    EVENT_SCHEMA,
    EVENT_NAME,
    STATUS,
    EVENT_TYPE,
    EXECUTE_AT,
    INTERVAL_VALUE,
    INTERVAL_FIELD,
    STARTS,
    ENDS
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = DATABASE();

The INFORMATION_SCHEMA.EVENTS view provides metadata about scheduled events. Some fields are populated only for the applicable event schedule type, so one-time and recurring events may display different values.

13. Modifying a Scheduled Event with ALTER EVENT

The ALTER EVENT statement changes the schedule, status, or other supported properties of an existing event.

13.1 Change the Recurrence Interval

ALTER EVENT daily_log_task
ON SCHEDULE EVERY 12 HOUR;

This changes the event to a 12-hour recurring interval.

13.2 Disable an Event

ALTER EVENT daily_log_task DISABLE;

A disabled event remains defined in the database but is not executed by the Event Scheduler while disabled.

13.3 Enable an Event

ALTER EVENT daily_log_task ENABLE;

Enabling an event makes it eligible to execute according to its configured schedule, provided the server's Event Scheduler is also enabled.

13.4 Change the Schedule and Enable the Event

ALTER EVENT daily_log_task
ON SCHEDULE EVERY 1 DAY
ENABLE;

Use ALTER EVENT when you need to change an existing event instead of creating a second event with a similar purpose.

14. Deleting a Scheduled Event with DROP EVENT

If an event is no longer required, it can be removed with DROP EVENT.

DROP EVENT daily_log_task;

To avoid an error when the event may not exist, use:

DROP EVENT IF EXISTS daily_log_task;

Important: Dropping an event removes the scheduled event object. It does not automatically reverse the changes that the event previously made to application tables.

15. Event Completion and Persistence

MySQL supports completion options that determine what happens to an event after its schedule expires.

15.1 Default Completion Behavior

A one-time event is normally removed after it completes unless its definition specifies that it should be preserved. This behavior is useful for one-off tasks that do not need to remain in the database after execution.

15.2 Preserve a One-Time Event

CREATE EVENT preserved_one_time_task
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO
    INSERT INTO task_logs (task_name, executed_at)
    VALUES ('Preserved one-time task', CURRENT_TIMESTAMP);

The event is configured to remain after its schedule completes. Preserving an event does not mean that a one-time event will repeat; it means the event object is retained.

16. Event Timing and Time Zones

Event scheduling depends on the date and time values used when the event is created or altered. MySQL interprets the schedule using the session time zone in effect at that time and stores scheduling information accordingly.

16.1 Check the Current Time and Time Zone

SELECT
    CURRENT_TIMESTAMP AS current_time,
    @@session.time_zone AS session_time_zone,
    @@global.time_zone AS global_time_zone;

16.2 Why Time Zones Matter

• Applications may serve users in different time zones.
• Server configuration and client session settings may differ.
• Daylight saving changes can affect how local business times are interpreted.
• A schedule created in one session may not mean the same local wall-clock time expected by another system.

For production tasks, define the intended time zone clearly, confirm the session time zone before creating or altering events, and test schedules around daylight-saving transitions when relevant.

17. Common Errors When Creating Scheduled Events

18. Best Practices for Scheduled Database Tasks

18.1 Use Descriptive Event Names

Choose names that explain the event's purpose, such as daily_order_cleanup, hourly_summary_update, or weekly_archive_task.

18.2 Test SQL Statements Before Scheduling

Execute the underlying SELECT, UPDATE, or DELETE logic manually in a test environment before putting it into a recurring event. Validate the rows affected and the expected output.

18.3 Make Operations Safe to Repeat

Recurring events can execute repeatedly, so design their SQL statements to avoid unintended duplicate records or repeated business actions. Unique keys, appropriate WHERE conditions, and transactional design can help.

18.4 Prevent Overlapping Execution

If an event takes longer than its recurrence interval, multiple executions may overlap. For operations where overlap is unsafe, implement a suitable locking or coordination strategy, such as an advisory lock with GET_LOCK(), or appropriate row/table locking.

18.5 Monitor Event Results

Consider recording task start times, completion times, row counts, and error details in a dedicated logging table. A log table can help identify missed work or repeated failures.

18.6 Use Appropriate Indexes

Queries that filter by date or status may become slow as tables grow. Review indexes that support the event's WHERE conditions and test query performance with realistic data volumes.

18.7 Apply Least-Privilege Access

Ensure that the event's definer has only the privileges needed for the scheduled operation. Avoid granting broad administrative permissions simply to make an event work.

18.8 Be Careful with Destructive Tasks

Automated DELETE and UPDATE statements can affect large numbers of records without a person reviewing each execution. Verify conditions, test on sample data, maintain appropriate backups, and use a retention policy approved for the application.

19. Practical Exercises

1. Create an event that inserts a timestamp into a log table every hour.
2. Create a one-time event scheduled to execute one day in the future.
3. Create a daily event that removes temporary records older than 30 days.
4. Create a recurring event with both STARTS and ENDS clauses.
5. Use SHOW EVENTS to inspect your events.
6. Use SHOW CREATE EVENT to inspect one event's SQL definition.
7. Disable an event with ALTER EVENT, verify its status, and then enable it again.
8. Drop a test event using DROP EVENT IF EXISTS.
9. Check the Event Scheduler status and confirm that the server is configured to run events.
10. Design a cleanup event and test its WHERE clause using SELECT before executing any DELETE statement.

20. Mini Project: Automated Daily Database Maintenance

Project Objective: Create a scheduled event that records a daily maintenance summary and removes old temporary records.

Project Requirements

• Create a temporary records table with a timestamp column.
• Create a maintenance summary table.
• Insert sample temporary records with different creation dates.
• Create an event that counts records older than the retention period.
• Save the summary in the summary table.
• Delete only records that meet the approved retention rule.
• Inspect the event definition and verify the scheduler status.

Example Summary Table

CREATE TABLE maintenance_summary (
    summary_id INT PRIMARY KEY AUTO_INCREMENT,
    run_at DATETIME NOT NULL,
    eligible_records INT NOT NULL
);

Example Event Definition

DELIMITER //

CREATE EVENT daily_maintenance_summary
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    INSERT INTO maintenance_summary (run_at, eligible_records)
    SELECT
        CURRENT_TIMESTAMP,
        COUNT(*)
    FROM temporary_sessions
    WHERE created_at < CURRENT_TIMESTAMP - INTERVAL 30 DAY;

    DELETE FROM temporary_sessions
    WHERE created_at < CURRENT_TIMESTAMP - INTERVAL 30 DAY;
END//

DELIMITER ;

This example records the number of eligible records before deleting them. For a production implementation, consider what should happen if the summary insert succeeds but the deletion fails, or if the event is run again. Use appropriate transactional handling, monitoring, and duplicate prevention for the specific requirements.

21. Interview Questions and Answers

Q1. What is a scheduled event in MySQL?

A scheduled event is a named database object that executes one or more SQL statements at a specified time or recurring interval.

Q2. What is the purpose of the Event Scheduler?

The Event Scheduler manages and executes events when their schedules become due. Events will not run automatically if the scheduler is disabled.

Q3. Which statement is used to create a scheduled event?

The CREATE EVENT statement is used to create a scheduled event.

Q4. What is the difference between AT and EVERY?

AT specifies a one-time execution, whereas EVERY specifies a recurring interval.

Q5. What is the purpose of STARTS?

STARTS specifies when a recurring event's schedule should begin.

Q6. What is the purpose of ENDS?

ENDS specifies when a recurring event's schedule should stop.

Q7. How can you check whether the Event Scheduler is enabled?

SHOW VARIABLES LIKE 'event_scheduler';

Q8. How can you disable an existing event?

ALTER EVENT event_name DISABLE;

Q9. How can you enable a disabled event?

ALTER EVENT event_name ENABLE;

Q10. How can you delete a scheduled event?

DROP EVENT IF EXISTS event_name;

Q11. What is the difference between a trigger and a scheduled event?

A trigger executes in response to a supported table operation. A scheduled event executes according to a time-based schedule.

Q12. Can a scheduled event execute multiple SQL statements?

Yes. A compound event body can use BEGIN and END to contain multiple statements.

Q13. Can an event call a stored procedure?

Yes. The event body can call a stored procedure if it exists and the execution account has the required privileges.

Q14. Why might an event be created successfully but fail during execution?

The event may reference missing tables or columns, or its definer may lack privileges needed by the SQL statements inside it. Execution errors must be investigated separately from successful event creation.

Q15. What is ON COMPLETION PRESERVE?

It specifies that an event object should be retained after its schedule completes instead of being automatically removed under the default completion behavior.

22. Quick Revision Checklist

• Understand scheduled events and the Event Scheduler.
• Check the scheduler status using SHOW VARIABLES LIKE 'event_scheduler'.
• Use CREATE EVENT to define a scheduled task.
• Use AT for one-time execution.
• Use EVERY for recurring execution.
• Use STARTS and ENDS to control the recurring schedule window.
• Use BEGIN and END for multiple statements.
• Use SHOW EVENTS and SHOW CREATE EVENT to inspect events.
• Use ALTER EVENT to modify or disable an event.
• Use DROP EVENT to remove an event.
• Verify privileges, time zones, execution duration, and data safety.
• Test automated operations before enabling them in production.

23. Conclusion

Scheduled events are an important MySQL feature for automating recurring and one-time database tasks. By using the Event Scheduler and statements such as CREATE EVENT, ALTER EVENT, and DROP EVENT, developers can automate cleanup, maintenance, summaries, and routine data updates directly within the database server.

Successful event automation requires more than writing a valid SQL statement. You must also ensure that the Event Scheduler is enabled, the event has the correct schedule, the execution account has appropriate privileges, and the task is safe to run repeatedly. With careful testing and monitoring, scheduled events can make database operations more consistent and reduce manual work.

Explore the course: MySQL Training at JustAcademy

Book a course demo: Register for a Course Demo

Official documentation: MySQL CREATE EVENT Statement | MySQL Event Scheduler Overview