Skeema v1.11.0 released

Skeema v1.11.0 has been released! This new version includes spatial index support, safe trigger DDL operations with table locking, new options to ignore some types of table differences, and additional safety guardrails.

Spatial indexes and SRIDs

Skeema v1.11 adds the ability to diff tables that have spatial indexes, and also fully supports MySQL 8’s SRID column attribute for spatial column types.

Additionally, the lint-dupe-index linter check will now flag spatial indexes which MySQL 8 won’t use due to lack of an SRID.

Safe trigger DDL with table locking

The Premium edition of Skeema includes full support for managing triggers. Skeema v1.11 improves this functionality by automatically using split-second table locks whenever skeema push needs to run multiple trigger DDL statements in the same schema, ensuring that no writes can occur in the brief moment between the trigger-related DDL statements. Even though MySQL and MariaDB do not support transactional DDL, this new Skeema functinality allows you to safely refactor your trigger definitions without worrying about data inconsistency from in-between states.

This functionality is especially important in MySQL, which lacks the CREATE OR REPLACE syntax which is present in MariaDB for making atomic modifications to existing triggers. Previously, Skeema considered any modification to an existing trigger to be unsafe in MySQL (requiring --allow-unsafe to proceed), due to risk of writes occurring in the brief moment between the DROP and the re-CREATE. Skeema v1.11 no longer considers this to be unsafe, since Skeema’s automatic locking behavior will temporarily block those writes until the trigger modification is complete.

As always, you should use skeema diff to preview the generated DDL before running skeema push. Whenever Skeema detects that locks are necessary, the output of skeema diff will show the exact LOCK statements that will be used.

LOCK TABLES `users` WRITE;
DROP TRIGGER `new_signup`;
CREATE DEFINER=`root`@`%` TRIGGER `new_signup` AFTER INSERT ON `users` FOR EACH ROW UPDATE daily_signups SET num = num + 1 WHERE day = CURDATE();
UNLOCK TABLES;

New cosmetic diff filtering options

Skeema v1.11 adds two new options for ignoring certain types of minor schema drift in skeema diff and skeema push. The lax-column-order option can be enabled to ignore differences in column order, and the lax-comments option provides a way to ignore discrepancies in COMMENT clauses.

These options are especially useful when transitioning to Skeema’s declarative approach after previously using a traditional imperative migration tool. Imperative tools are more prone to schema drift over time, because they don’t actively enforce consistency between environments, nor do they take the entire state of the environment into account. These new options can help you get the important functional aspects of your environments back in sync, without having to stress about the trivial cosmetic discrepancies.

Diff safety improvements

Safety has always been the core design concern of Skeema, and v1.11 augments our already-substantial guardrails even further by improving log output and detecting additional edge-cases.

Whenever skeema diff or skeema push detects an unsafe change, an explanation of the specific risk/concern is now logged. If multiple unsafe changes are present within a single schema, all of them are now logged at once, as well as all linter annotations for objects in the diff. This allows you to see all problematic statements at the same time, without having to perform multiple Skeema commands.

Several new edge cases are now detected as unsafe. Here we’ll highlight two in particular:

• Changing the collation of a string-type column is now considered unsafe if the column is part of a uniqueness constraint (primary key or unique index). Collations determine what characters are considered equal, so changing the collation can cause subtle problems with how the uniqueness constraint behaves.

• Modifying the parameters or return type of a stored procedure/function is now always considered unsafe – even in MariaDB, where Skeema can use CREATE OR REPLACE syntax to atomically modify a routine. This type of change is typically disruptive to call-sites in application code, as well as in other routines. For this reason, it’s often impossible to deploy such a change without risk of a brief spike of query errors.

Additional changes

Previously, Skeema bundled an internal Docker client for use in Docker workspaces. This has been removed in Skeema v1.11, which now simply shells out to an external docker CLI binary as needed. This change reduces the size of the skeema binary by a considerable 25-40%, and avoids spurious false-positive security warnings related to the dependency chain of the previous embedded Docker client library.

Last but certainly not least, the recent GA releases of MySQL 8.1 and MariaDB 11.1 are officially supported by Skeema v1.11.

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