MySQL/MariaDB events

Overview

Events are database objects that contain SQL statements executed later either regularly or once. Essentially, it’s like a crontab executed directly on the database server. This is useful, for example, for archiving older data or deleting logs.

Event privilege

Before adding a new event, it’s necessary to check if the database user has the Event privilege. If the Event privilege exists, the Events menu item will appear in phpMyAdmin. If the menu item is missing, the user does not have Event privileges.

Adding an Event

The easiest way to add events is by using the phpMyAdmin application. Select the “Add Event” link under the Events tab. In the window that opens, you need to fill in:

In the example above, a recurring event (Event type: RECURRING) is added, which triggers once a week. If you select the type ONE TIME, then the query will only be executed once at the specified Start time.

You can optionally set a time range for the event to be executed. To do this, fill in both the Start and End time.

Checking the “On completion preserve” checkbox means that after the event’s tasks are completed, the query remains intact, allowing it to be edited and reused in the future.

Disabling an event.

If you no longer want a periodic query to be executed, you can change its status to DISABLED or delete the entire event.

Creating a deletion query

To compose a correct deletion query, you need to analyze the table structure to see how the insertion time is stored. Typically, UNIX TIMESTAMP or DATETIME type fields are used. The example above works for deleting rows with a DATETIME type. If you want to delete rows with a UNIX TIMESTAMP type, the simplest way is to multiply the seconds by the desired number of deletion days and subtract it from the UNIX timestamp number.

For example, rows that are 30 days or older can be deleted like this:

SELECT * FROM `errors` WHERE `unix_t` < UNIX_TIMESTAMP() - (60 * 60 * 24 * 30);

Before executing a DELETE query, it’s always advisable to use a SELECT query to see which rows are selected by the WHERE clause. If the correct rows are selected, then replace SELECT with DELETE.

More information can be found in the official MariaDB documentation.