Skeema v1.12.0 released

July 1, 2024

Skeema v1.12.0 is now available! Our newest release adds support for the latest MySQL and MariaDB LTS version series, managing scheduled events in the Premium CLI, and automatic detection of index renaming in diffs.

MySQL 8.4 and MariaDB 11.4

The first release of MySQL’s new long-term support (LTS) version series, MySQL 8.4, became available at the end of April. Skeema v1.12 adds full support for this new server version series, including several changes to SQL syntax and reserved word handling.

One surprising change in MySQL 8.4 can have a major impact on schema management: by default, when adding a new foreign key constraint, the parent side (referenced) table must now have a UNIQUE KEY which exactly matches the referenced columns in the foreign key definition. Previously, MySQL/InnoDB allowed non-unique indexes for this purpose, as well as indexes with additional columns after the referenced ones.

In order to detect this situation, a new linter rule lint-fk-parent has been added in Skeema v1.12. This rule, which is enabled by default at the “warning” annotation level, detects foreign keys where the parent table doesn’t comply with MySQL 8.4’s stricter requirements.

Meanwhile, MariaDB’s new LTS release series, MariaDB 11.4, reached GA status at the end of May. One notable improvement is its automatic use of self-signed server certificates for encrypted connections, similar to the behavior of MySQL 5.7+. Configuring Skeema to use ssl-mode=required works as expected with MariaDB 11.4 out of the box.

Event management

The event scheduler in MySQL and MariaDB allows you to use scheduled tasks directly in the database server. With this database feature, you can define recurring SQL programmatic logic (similar to a Unix crontab), as well as one-time-only SQL logic to be executed at a specific time. Skeema Premium v1.12 adds the ability to manage events declaratively, just like any other object. All Skeema commands can now introspect, dump, and diff events and CREATE EVENT statements.

The new ignore-event regex option may be used to force Skeema to ignore particular events, and the new lint-has-event linter rule option allows you to flag or prevent use of events if desired.

One-time events may also be used in clever ways for implementing push hooks or one-time data migrations/patches using Skeema Premium:

  • 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 .... Because the event is not preserved (persisted after execution), every skeema push will re-create it, and then the server’s event scheduler will execute it immediately upon creation.

  • 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 .... Because the event is preserved, the server will persist the event metadata in its data dictionary after execution, as a “disabled” event. Subsequent skeema push operations will not re-enable or re-execute it.

  • In some cases you may wish to execute an imperative schema change (such as RENAME TABLE) only on existing environments, but not on any future ones. In this case, use a timestamp string literal with a specific time in the near future, for example CREATE EVENT ... ON SCHEDULE AT '2024-07-01 18:00:00' DO .... Push this to all existing environments, and they will execute it at the specified time. After that time, pushing this event will be a no-op, so there is no risk of running the specific logic in environments that don’t need it.

Index renaming

Database servers have supported renaming an existing index using ALTER TABLE ... RENAME KEY syntax for a number of years, since MySQL 5.7 and MariaDB 10.5. However, skeema diff and skeema push previously did not support this, emitting a DROP KEY and re-ADD KEY in this situation instead.

Skeema v1.12 solves this problem by automatically detecting when a preexisting index is being renamed in the diff, and emitting RENAME KEY if the database server’s flavor supports it. The server only allows the RENAME KEY syntax to be used when no other aspect of the index’s definition is being changed, so Skeema can detect this situation unambiguously.

When using an external online schema change tool, if you’re just renaming an index and not making any other table alterations in the push operation, you may wish to circumvent the OSC tool by using --skip-alter-wrapper as a one-time command-line override. Future versions of Skeema may add the ability to bypass OSC tools automatically for these types of operations.

Documentation improvements

In recent months, Skeema’s documentation has been substantially augmented and reorganized. Some of the new dedicated pages include:

  • Recipes: Using Skeema to achieve common schema management tasks
  • Pipelines and automation: How Skeema behaves in automation contexts
  • Safety Checks: Adjusting linter options, unsafe modification detection, and diff verification
  • Online Schema Change: Altering tables without disrupting query workloads
  • Workspaces: Configuring the location of a temporary database for testing and introspecting CREATE statements
  • Ignore Regex Options: Setting options to skip database objects by name pattern
  • Seed Data: Tracking seed data INSERTs in your schema repo (Premium feature)
  • Definers: Restricting or stripping DEFINER clauses in stored objects
  • SSL/TLS Certificates: Using encrypted connections and configuring certificates
  • SSH Tunnels: Configuring SSH tunnels through a bastion host, database server, or both (Premium feature)
  • Sharding: Mapping a single schema subdirectory to multiple databases

We’re always striving to improve our documentation further, and user feedback is welcome. Please feel free to create a GitHub issue on the Skeema Community Edition repo with any documentation-related questions or requests.

For more information on Skeema v1.12.0, full release notes are available on GitHub.