Automating MariaDB Tasks with Events

You are viewing an old version of this article. View the current version here.

MariaDB has an event scheduler that can be used to automate tasks, making them run at regular intervals of time. This page is about using events for automation. For more information about events themselves, and how to work with them, see event scheduler.

Pro's and Con's of Using Events for Automation

Events can be compared to Unix cron jobs or Windows scheduled tasks. MariaDB events have at least the following benefits compared to those tools:

  • Events are system-independent.
  • Events are written in procedural SQL. There is no need for other languages.
  • Events run in MariaDB. One implication, for example, is that the results of queries remain in MariaDB itself and are not sent to a client.

Some drawbacks of using events are the following:

  • Events can only perform tasks that can be developed in SQL. So, for example, it is not possible to send alerts. Access to files or remote databases is limited.
  • The event scheduler is a single thread. While this thread is busy running an event, other events cannot start. They are just skipped, not postponed. This can be a big problem when automating a number of tasks that should run in a limited time range.
  • For more events limitations, see Event Limitations.

In many cases you may prefer to develop scripts in an external programming languages. However, you should know that simple tasks consisting of a few queries can easily be implemented as events.

Examples

Some examples of tasks that could easily be automated with events:

  • Copying data from a remote table to a local table by night, using the CONNECT storage engine. This can be a good idea if many rows need be copied, because data won't be sent to an external client.
  • Delete rows from a table, that are older than 5 years. Nothing prevents us from doing this with an external script, but probably this wouldn't add any value.
  • Add a new partition to a table and drop the oldest one.

Content initially contributed by Vettabase Ltd.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.