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 one-time data migrations/patches, as well as SQL post-push hooks.
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 docs further, and user feedback is welcome. If you have any documentation-related questions or requests, please file a documentation issue on our GitHub repo.
For more information on Skeema v1.12.0, full release notes are available on GitHub.