Analysis of MySQL Event Scheduler: Architecture, Heap Algorithms, and Bug Fixes

MySQL introduced the event mechanism starting from version 5.1; this article focuses on the implementation in versions 5.6, 5.7, and 8.0, using the 5.6 code as a reference.

1. MySQL Event Code Class Diagram

The main classes involved are:

Events – entry module for loading, unloading, creating, deleting, and altering events (files: events.h/.cc).

Event_parse_data – internal structure after SQL parsing (files: event_parse_data.h/.cc).

Event_scheduler – scheduler module (files: event_scheduler.h/.cc).

Event_queue – memory management of event tasks, implemented as a min‑heap; the head holds the next task to execute (files: event_queue.h/.cc).

Event_db_repository – operations on the mysql.event table (files: event_db_repository.h/.cc).

Event_queue_element – element operations (files: event_data_objects.h/.cc).

QUEUE – generic heap implementation used by other modules as well (files: queues.h/queues.c or priority_queue.h in newer versions).

2. MySQL Event Runtime Mechanism

The event metadata consists of an in‑memory event queue and the persistent mysql.event table. The event scheduler thread repeatedly fetches the queue head, waits until its execution time, and runs the event as a stored procedure, updating metadata after completion.

3. Main Processes

3.1 Event Creation Process

handle_one_connection → do_handle_one_connection → do_command → dispatch_command → mysql_parse → mysql_execute_command → Events::create_event → lock_object_name → db_repository->create_event → db_repository->load_named_event → event_queue->create_event → thd->add_to_binlog_accessed_dbs → write_bin_log

3.2 Event Deletion Process

handle_one_connection → … → Events::drop_event → check_access → lock_object_name → Event_db_repository::drop_event → Event_queue::drop_event → thd->add_to_binlog_accessed_dbs → write_bin_log

3.3 Event Update Process

handle_one_connection → … → Events::update_event → check_access → lock_object_name → Event_db_repository::update_event → Event_queue::update_event → thd->add_to_binlog_accessed_dbs → write_bin_log

3.4 Event Scheduler Startup and Execution

The scheduler starts when mysqld launches with event_scheduler enabled or when the variable is switched from OFF to ON at runtime. The startup sequence is:

mysqld_main → Events::init → Event_queue::init_queue → Events::load_events_from_db → Event_scheduler::start → mysql_thread_create(event_scheduler_thread)

The scheduler thread runs:

Event_scheduler::run → Event_queue::get_top_for_execution_if_time → Event_scheduler::execute_top → mysql_thread_create(event_worker_thread)

The worker thread loads the event, constructs the stored‑procedure SQL, parses it, and executes it.

4. Event Min‑Heap Insertion Algorithm

/* Insert element into queue */
void queue_insert(register QUEUE *queue, uchar *element) {
    // sentinel node
    queue->root[0] = element;
    idx = ++queue->elements;
    while ((queue->compare(queue->first_cmp_arg,
            element + queue->offset_to_key,
            queue->root[(next = idx >> 1)] + queue->offset_to_key) * queue->max_at_top) < 0) {
        queue->root[idx] = queue->root[next];
        idx = next;
    }
    queue->root[idx] = element;
}

The article illustrates the insertion steps with a concrete example inserting 0 into the heap {1,2,7,3,5,9,10,6}.

5. Event Min‑Heap Deletion Algorithm

/* Delete element at idx */
uchar *queue_remove(register QUEUE *queue, uint idx) {
    uchar *element;
    element = queue->root[++idx];          // replace with last element
    queue->root[idx] = queue->root[queue->elements--];
    _downheap(queue, idx);                 // adjust downwards only
    return element;
}

void _downheap(register QUEUE *queue, uint idx) {
    // standard down‑heap adjustment (omitted for brevity)
}

A bug is identified: when the last element is smaller than the removed element, only a down‑heap adjustment is performed, leaving the heap invalid.

6. Slow‑Log Not Recording Updates (Bug 1)

When an event executes a slow SELECT, the SELECT is logged, but subsequent INSERT/UPDATE/DELETE statements are not logged unless a prior slow query set the SERVER_QUERY_WAS_SLOW flag. The analysis shows that the flag is only set when the Diagnostics_area state is DA_EOF; DML statements use DA_OK, so the flag is never set.

Proposed fixes:

Update SERVER_QUERY_WAS_SLOW in sp_instr_stmt::execute when the status is my_ok.

Reset the slow‑query flag after each statement in the event execution flow.

7. Delayed Event Execution After DROP (Bug 2)

Some events are delayed for hours or days after a DROP and recreation. Investigation ruled out CPU load and execution errors. The root cause is the broken min‑heap after deleting an event: queue_remove performs only a down‑heap adjustment, corrupting the heap when the replacement element is smaller.

Fix:

Replace the down‑heap call with queue_fix (or implement an up‑heap adjustment when needed) in queue_remove .

Verification on MySQL 5.6, 5.7, and 8.0 shows the heap property is preserved after the fix.

8. Conclusion

The MySQL event mechanism still has shortcomings; for workloads requiring precise timing or strong task dependencies, alternative scheduling solutions are recommended. The database team continues to monitor and patch such issues to improve stability.