Working with MariaDB Events: SQL Statements vs. Alternatives

• mysql.event Table
  This table stores details about these scheduled events. It contains information like the event name, the SQL statement to be executed, scheduling information (when to run), and other details about the event's creation and execution.
• Event Scheduler
  This is a built-in feature in MariaDB that allows you to define and schedule SQL statements to run automatically at specific times or intervals.

Alternatives to Accessing mysql.event

While the mysql.event table exists, you typically wouldn't interact with it directly using SQL statements. Here are preferred methods for working with MariaDB events:

1. • CREATE EVENT
     This statement is used to define a new event, specifying the SQL statement to run, scheduling details, and other options.
   • ALTER EVENT
     This statement allows you to modify an existing event.
   • DROP EVENT
     This statement removes a scheduled event.
   • SHOW EVENTS
     This statement displays information about existing events.
   • SHOW CREATE EVENT
     This statement shows the original CREATE EVENT statement used to create a specific event.

2. Information Schema
   

   • INFORMATION_SCHEMA.EVENTS
     This is a virtual table that provides information about events similar to the mysql.event table. It's generally recommended to use this table instead of mysql.event for portability reasons.

In summary

---

1. Creating a One-Time Event
   

CREATE EVENT cleanup_old_data
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  DELETE FROM user_data WHERE last_active < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

1. Creating a Recurring Event
   

This example creates an event named "update_stats" that runs every day at midnight (12:00 AM) to update user statistics:

CREATE EVENT update_stats
ON SCHEDULE EVERY 1 DAY
DO
  CALL update_user_stats_procedure();

This assumes you have a stored procedure named "update_user_stats_procedure" that performs the actual statistics update logic.

1. Viewing Event Details
   

You can use the SHOW CREATE EVENT statement to view the details of an existing event:

SHOW CREATE EVENT update_stats;

This will display the original CREATE EVENT statement used to define the "update_stats" event.

1. Listing All Events
   

Use the SHOW EVENTS statement to see a list of all currently defined events:

SHOW EVENTS;

---

1. MariaDB Event Scheduler Statements
   

These are dedicated statements for creating, modifying, and interacting with events. They offer a more secure and controlled way to manage events compared to manipulating the internal mysql.event table. Here are the key statements:

• SHOW CREATE EVENT
  Shows the original CREATE EVENT statement used to define a specific event. (e.g., SHOW CREATE EVENT update_stats)
• SHOW EVENTS
  Displays information about existing events, including name, status, and schedule. (e.g., SHOW EVENTS)
• DROP EVENT
  Removes a scheduled event. (e.g., DROP EVENT cleanup_data)
• ALTER EVENT
  Modifies an existing event's details, like its schedule or the SQL statement it executes. (e.g., ALTER EVENT cleanup_data ...)
• CREATE EVENT
  Defines a new event, specifying the SQL statement to run, scheduling details, and other options. (e.g., CREATE EVENT cleanup_data ...)

1. Information Schema
   

MariaDB provides a virtual table named INFORMATION_SCHEMA.EVENTS. This table offers information about scheduled events similar to the mysql.event table. It's generally recommended to use this table instead of mysql.event for several reasons:

• Security
  Direct access to system tables like mysql.event might require higher privileges and can be less secure.
• Portability
  INFORMATION_SCHEMA is a standard schema across various database systems, making your code more portable if you switch from MariaDB in the future.

In summary

For managing and viewing MariaDB events, it's recommended to use:

• INFORMATION_SCHEMA.EVENTS
  This virtual table offers a standard and secure way to access event information.
• MariaDB Event Scheduler Statements
  These provide dedicated, secure methods for event management.