Docs: Features: Events

Skeema Premium includes the ability to manage events (scheduled tasks in the database server), beginning with Skeema v1.12. When first upgrading from the Community edition CLI to the Premium edition, or from a pre-1.12 Premium version to 1.12+, be sure to run skeema pull one time to export any preexisting events to the filesystem.

The database server will only execute events if the event_scheduler global server variable is enabled. Skeema does not currently manipulate this variable or check/confirm it is enabled. This variable is enabled by default in MySQL 8.0+, but not MySQL 5.x, nor any version of MariaDB. Additionally, some managed database-as-a-service platforms override the normal default. For example, “serverless” platforms tend to disable it by default, even in MySQL 8.

If the event scheduler is disabled, events can still be created, but they will silently not be executed. Enabling the event scheduler directly in the server using SET GLOBAL event_scheduler = ON requires elevated privileges. Managed database platforms typically have a web or API-driven alternative for manipulating global server variables.

Options

The ignore-event regex option may be used to force Skeema to ignore particular events, or even all events.

If you wish to restrict or ban events in your environment, see lint-has-event.

Several options relate to DEFINER user clauses. These options affect events, as well as other types of stored objects. See definer management for more information.

Privileges

In order to manage events, Skeema’s database user needs the EVENT privilege on all relevant databases. See the privileges section of the requirements documentation.

If you wish to manage events that have a different DEFINER than Skeema’s database user, be sure to read about DEFINER stripping in the definer management documentation.

Outside of Skeema, keep in mind these privilege considerations regarding management and automation of events:

  • Events are only executed by the server if the event_scheduler global server variable is enabled. Enabling or disasbling the event scheduler requires elevated privileges.
  • Events can encounter runtime errors, for example if a query in the event body refers to a table or column that does not exist. If the server cannot successfully execute an event, any failure log messages are only written to the database server error log. If you do not have access to this log, there is no other way to view these log messages.

Recipes

One-time events may be used in clever ways for implementing imperative data migrations (INSERT, UPDATE, DELETE), one-time DDL patches (e.g. RENAME), or SQL push hooks.

To execute arbitrary SQL logic once per server/environment only, create a .sql file which contains CREATE EVENT ... ON SCHEDULE AT CURRENT_TIMESTAMP ON COMPLETION PRESERVE DO .... Or use a timestamp string literal to only apply the event to environments that exist at the specified time. To learn more, see our blog post on handling imperative changes.

To execute arbitrary SQL hook logic on every push, create a .sql file which contains CREATE EVENT ... ON SCHEDULE AT CURRENT_TIMESTAMP ON COMPLETION NOT PRESERVE DO .... To learn more, see the recipe for push hooks.

Manipulating events

Events differ from other types of stored programs in severals ways, which greatly affect how Skeema interacts with them.

No workspace interactions

Because events are automatically executed based on their schedule, Skeema does not use its normal workspace design pattern to introspect them. There is too much risk that the event will be executed, which can have side-effects outside of the workspace schema. Instead, event definitions in your .sql files are handled entirely through textual parsing of their schedule and attributes from the CREATE EVENT SQL statement.

However, Skeema does not implement a full SQL parser, and it cannot fully validate the body of a CREATE EVENT statement. This means that if an event definition contains SQL syntax errors, these errors are not caught during the workspace step of Skeema’s commands, which normally fails early for syntax errors in other database object types. Instead, event body syntax errors will only become evident during the execution step of skeema push.

When skeema push executes, any event-related DDL is always ordered to run after DDL for other object types. This means a syntax error in an event won’t disrupt execution of other non-event-related DDL in the same directory.

To further minimize problems with SQL syntax errors in event bodies, you can move each event’s logic into a separate stored procedure, so that the event body just consists of a single CALL statement. This way, you can synchronize or modify the business logic just by adjusting stored procedures, without having to alter any events. Stored procedures are evaluated in a workspace, which catches any SQL syntax errors at the beginning of the push process.

Dropping an event

Dropping an enabled event is considered a destructive action, requiring use of the allow-unsafe option. This helps to prevent accidental/unintended event drops. Although events can be re-created quickly in this situation, there might be consequences from “missing” some scheduled executions during the time period while the event was dropped.

If an event is disabled for any reason, Skeema permits a drop to proceed without requiring allow-unsafe. This includes all of these scenarios:

  • An event that was intentionally set to DISABLE previously
  • A recurring event that reached its ENDS time
  • A non-recurring event that has already executed due to reaching its AT time
  • An event that was disabled due to replication failover and was never re-enabled

Event handling special cases

Unlike other database object types, event definitions are inherently more dynamic and self-modifying:

  • The STARTS, ENDS, or AT clauses of an event schedule can include expressions using CURRENT_TIMESTAMP, which is evaluated and then “frozen” upon event creation (or subsequent schedule alteration) on each database server. The server’s information_schema metadata only records the resulting timestamp, not the original expression. This frozen value will often differ between dev/stage/prod environments.
  • The status of a preserved recurring event will automatically change to DISABLE upon reaching its ENDS time, if one is set. Likewise for the status of a preserved one-time event upon being executed at its AT time.
  • Non-preserved events are automatically dropped by the database server upon reaching their ENDS or AT time.
  • With binlog replication, the status of an enabled event is automatically set to DISABLE ON REPLICA (MySQL 8.2+) or DISABLE ON SLAVE (MySQL < 8.2, or any version of MariaDB), to ensure the event is not redundantly executed on replicas. However, if you perform a failover, the event is not automatically re-enabled on the new primary server.

In order to reduce friction with these event properties, Skeema commands implement special logic when dumping events to the filesystem, as well as when diffing event state between the filesystem and a live database. This is designed to prevent spurious diffs, similar to how Skeema ignores modifications to auto_increment counters or partition list changes.

For filesystem dump logic, these special behaviors include the following:

  • If a recurring event has already started in the database, all Skeema commands will omit its STARTS time when dumping the event to the filesystem. (The database always stores a static STARTS time for all recurring events, even ones which omitted the STARTS clause; however this static time will differ between environments/shards, and generally isn’t useful for an event that has already started.)
  • When skeema pull is updating an existing event definition in the filesystem, it leaves some event properties as-is, regardless of the current database state:
    • Dynamic AT, STARTS, or ENDS expressions in the filesystem are left alone, because the server only stores a frozen static value which may be undesirable to dump.
    • If an event is expired (reached its ENDS or AT time), its filesystem side status is left alone, to avoid extraneous git diffs which replace ENABLE with DISABLE.
  • Because the canonical format of an event (shown by SHOW CREATE EVENT) places the entire definition on a single line, Skeema’s dump commands avoid reformatting events.
    • skeema pull only rewrites a filesystem CREATE EVENT statement if the database side has seemingly-intentional differences from the filesystem side, regardless of whether the format option is enabled or disabled.
    • skeema format generally does not ever reformat events in the filesystem.
    • skeema lint won’t reformat events in the filesystem, even if its format option is enabled.
  • If the filesystem definition lacks a specific DEFINER user, skeema pull won’t dump one, even though the database server always stores a notion of the DEFINER.
  • If the filesystem contains a definition for a one-time non-preserved event with a dynamic AT time, skeema pull won’t delete the CREATE EVENT statement from the filesystem if the event does not exist on the live database. This allows such events to be used for arbitrary SQL logic which is executed on every skeema push.

Meanwhile, for event diff logic, these special behaviors include the following:

  • If the filesystem definition omits a STARTS time, or uses a dynamic expression for STARTS or AT, then these fields will not be diffed at all.
  • If the filesystem definition has a dynamic expression for ENDS, and the database server side already specifies some value for ENDS, this field will not be diffed at all.
  • Differences between DISABLE, DISABLE ON SLAVE, and DISABLE ON REPLICA are ignored.
  • If the database-side event is disabled due to reaching its ENDS or AT time, Skeema won’t re-enable it (even if the filesystem definition includes ENABLE) unless its ENDS or AT time is also being changed.

If some of Skeema’s event-handling behaviors are problematic for your company’s workflow, please e-mail feedback@skeema.io to describe the situation. Additional event-related options may be introduced in the future to provide more fine-grained control over these behaviors.