Docs: Requirements

This document describes which environments are supported by Skeema.

Database version and flavor

Skeema currently supports the following databases:

  • MySQL 5.5 - 8.2
  • Percona Server 5.5 - 8.0
  • MariaDB 10.1 - 11.2

Some uncommonly-used database features – such as subpartitioning – are not supported yet in Skeema’s diff operations. Additionally, only the InnoDB storage engine is primarily supported at this time. Other storage engines are often perfectly functional in Skeema, but it depends on whether any esoteric features of the engine are used.

In all cases, Skeema’s safety mechanisms will detect when a table is using unsupported features, and will alert you to this fact in skeema diff or skeema push. There is no risk of generating or executing an incorrect diff. If Skeema does not yet support a table/column feature that you need, please open a GitHub issue so that the work can be prioritized appropriately.

If you run database servers natively on multiple operating systems, follow our general guidelines on avoiding data and schema portability problems caused by mixing case-sensitive and case-insensitive operating systems.

The open source Community edition of the Skeema CLI does not guarantee full compatibility with AWS Aurora for MySQL, and has two minor known issues with older versions of Aurora. Our Premium edition products are fully compatible with Aurora, and include coverage for all major versions of Aurora MySQL in extensive integration test suites.

Skeema is designed to operate on specific information_schema behaviors of MySQL, MariaDB, and their direct variants. In general, other database systems are not supported, unless they are directly based on the MySQL or MariaDB codebases and provide complete compatibility.

Proxy software (also known as router or middleware software) is supported, as long as all of Skeema’s queries to each configured host are indeed routed to the same underlying database server. Skeema may not work properly if your proxy routes SELECT or SHOW queries to a different host than DDL statements (“read/write split”); or if it manipulates the results of queries, including combining results from multiple database servers into one result-set. In such environments, you must configure Skeema to bypass the proxy and connect directly to the underlying writer/primary database servers.

Privileges

When connecting to the database, Skeema’s user needs a number of privilege grants for its operations. The easiest way to get Skeema working is to use an existing administrative DB user with elevated privileges: SUPER / ALL PRIVILEGES ON *.* for a self-hosted DB, or extensive grants on *.* for a cloud DBaaS such as RDS or CloudSQL. In such situations you may skip this section.

It is possible to use finer-grained privileges instead. This section describes the minimum requirements for Skeema’s user.

Database-level privileges

Many privileges are available at a database (schema) level of granularity. For simplicity, you can grant these privileges ON *.* in order to apply to all schemas if you wish. Alternatively, you can grant these privileges on each relevant schema separately.

If granting them on each schema separately, be sure to do so for all of your relevant application schemas. You may also need to grant these same privileges for Skeema’s workspace schema: as described in the FAQ, most Skeema commands need to perform operations in a temporary schema that is created, used, and then dropped for each command invocation. By default, this database is named _skeema_tmp and is located on each DB instance that Skeema interacts with.

You don’t need to grant explicit privileges on the built-in system schemas. Although Skeema interacts extensively with information_schema, the server provides appropriate access automatically based on other privilege grants. Skeema does not interact with the performance_schema or sys schemas. Meanwhile in the mysql schema, Skeema will query mysql.proc (if available) in order to speed up introspection of stored procedures and functions, but this is not mandatory.

Relevant database-level privileges for Skeema include the following:

  • SELECT – to see tables and confirm whether or not they are empty
  • CREATE – allows skeema push to execute CREATE statements in application schemas, and for all commands to perform necessary operations in the workspace schema
  • DROP – allows skeema push --allow-unsafe to execute DROP statements in application schemas, and for all commands to perform necessary operations in the workspace schema
  • ALTER, INDEX – allows skeema push to execute ALTER TABLE statements, and for multiple commands to verify that generated DDL is correct in the workspace schema
  • CREATE ROUTINE, ALTER ROUTINE – allows management of stored procedures and functions
  • CREATE VIEW, SHOW VIEW – allows management of views (not available in the Community edition of Skeema)
  • TRIGGER – allows management of triggers (not available in the Community edition of Skeema)

When first testing out Skeema, if you do not plan on using skeema push initially, on your application schemas it is fine to omit everything but SELECT. The other privileges are still necessary on the workspace schema (e.g. _skeema_tmp) though.

If using the alter-wrapper option to execute a third-party online schema change tool, you may need to provide additional privileges as required by the tool.

Global privileges

Several useful privileges only exist at the “global” level, meaning ON *.* rather than a specific schema.

The SHOW DATABASES global privilege can be useful when trying to diagnose why Skeema can’t “see” one or more application schemas, but otherwise is not normally necessary.

If you wish to manage stored procedures / functions that use a different DEFINER than Skeema’s user, SUPER privileges may be necessary for Skeema’s user in the Community edition of Skeema. Premium Skeema products add support for the strip-definer option to improve usability with non-superusers.

In recent versions of MySQL and MariaDB, instead of granting SUPER, these new finer-grained global privileges can be useful for Skeema’s user:

  • SHOW_ROUTINE: grants permission to see bodies of routines that have a different definer than the current user (MySQL 8.0.20+)
    • Alternatively, granting global SELECT ON *.* provides the same ability, in any version of MySQL or MariaDB
  • SET_USER_ID (MySQL 8.0-8.1), SET_ANY_DEFINER (MySQL 8.2+), or SET USER (MariaDB 10.5+): permits setting a DEFINER (for procs, funcs, triggers, views) other than the current user
  • SYSTEM_USER: necessary to specify a DEFINER that is also a system user (MySQL 8.0.16+)
  • SESSION_VARIABLES_ADMIN (MySQL 8.0.14+), SYSTEM_VARIABLES_ADMIN (MySQL 8+), or BINLOG ADMIN (MariaDB 10.5+): permits skipping binary logging for workspace operations

Users of MariaDB 11.0+ should also note that the SUPER privilege no longer also provides these finer-grained privileges. You must explicitly grant SET USER and BINLOG ADMIN in addition to SUPER in MariaDB 11.0+.

SSL / TLS configuration

By default, Skeema will use an encrypted connection whenever the database server supports one, or an unencrypted connection if not. This behavior can be configured with the ssl-mode option in Skeema v1.6.0+.

All editions of Skeema are capable of connecting via a user account configured with the REQUIRE SSL attribute on the server side.

Several additional options relating to encrypted connections are only available in the Premium edition of the Skeema CLI:

  • ssl-cert and ssl-key may be specified to supply client-side public and private keys, permitting connection via a user configured with the REQUIRE X509, REQUIRE ISSUER, and/or REQUIRE SUBJECT attributes on the server side.
  • ssl-ca may be specified to supply a Certificate Authority (CA) file, which is used to verify the server, helping to prevent man-in-the-middle attacks.
  • ssl-mode=verify_identity may be enabled to perform a stricter CA check which includes verification of the server’s hostname. (For purposes of compatibility with MariaDB and older MySQL clients, this behavior can also be enabled using the equivalent ssl-verify-server-cert option.)
  • Even if the server does not support SSL/TLS, server-public-key-path can be used to permit secure password exchange under certain MySQL auth plugins.

These options are all designed to match the names and behavior of their corresponding mysql command-line client options. This means if you’ve already configured them in your ~/my.cnf file, Skeema will pick up these settings automatically by default.

Responsibilities for the user

  • If you have large tables and only ever want to permit usage of online/non-blocking ALTERs, you must configure this. Otherwise please be aware that certain forms of ALTERs will lock the table and may also cause replication lag.
  • External online schema change tools can, in theory, be buggy and cause data loss. Skeema does not endorse or guarantee any particular third-party tool.
  • Skeema does not automatically verify that there is sufficient free disk space to perform an ALTER operation.
  • There is no tracking of in-flight operations yet. This means in a large production environment where schema changes take a long time to run, it is the user’s responsibility to ensure that Skeema is only run from one location in a manner that prevents concurrent execution.
  • Accidentally running Skeema on a replica may break replication. It is the user’s responsibility to ensure that the host and port options in each .skeema configuration file do not ever point to replicas. Depending on the values of the workspace and temp-schema-binlog options, even “read-only” commands such as skeema diff or skeema lint may be detrimental to replicas that use MySQL’s GTID functionality!
  • As with the vast majority of software, Skeema is distributed without warranties of any kind. Community Edition users, see LICENSE. Premium Edition users, please refer to your commercial license agreement in the customer portal.

Unsupported features – Community edition

The following features are not supported in the Community edition of the Skeema CLI, but are supported in our Premium products.

Seed data / INSERT statements

The Community edition of the Skeema CLI does not parse or process INSERT statements. Their presence won’t break anything, but Skeema will not interact with them in any way.

Views

The Community edition of the Skeema CLI completely ignores views. Their presence won’t break anything, but Skeema will not interact with them in any way.

Triggers

The Community edition of the Skeema CLI completely ignores triggers. Their presence won’t break anything, but Skeema will not interact with them in any way.

AWS Aurora

The Community edition of the Skeema CLI has two known incompatibilities with older versions of AWS Aurora:

  • In Aurora v1 (MySQL 5.6), some tables with more than one foreign key cannot be diffed.
  • In Aurora v2 (MySQL 5.7), some tables with spatial indexes cannot be diffed.

If a table is affected by these incompatibilities, skeema diff and skeema push will log a warning and skip the portion of the operation involving the unsupported table.

In both cases, the incompatibility is caused by Aurora’s information_schema behavior diverging from that of standard MySQL. Skeema’s Premium edition has special handling for these cases.

Windows exe

A native Windows exe build is not available for the Community edition of the Skeema CLI.

Client-side SSL certs or CA verification

See the SSL / TLS configuration section above for more information.

Unsupported features – all editions of Skeema

The following features are not supported yet in any edition of Skeema.

Events

Skeema does not yet interact with events (the database’s built-in cron-like scheduler) in any way.

Grants, users, roles

Skeema does not yet allow you to manage database users or grants.

Global variables

Skeema does not yet allow you to manage global variables (database settings).

UPDATE, DELETE, REPLACE, LOAD DATA INFILE, etc

Although the Premium edition does support INSERT statements as a special-case for expressing seed data, it does not process any other type of imperative DML.

MariaDB system-versioned tables / bitemporal tables

Currently, Skeema will not interact with tables that use MariaDB’s system versioning feature (WITH SYSTEM VERSIONING clause) in any way.

MariaDB sequences

Skeema does not yet interact with MariaDB’s CREATE SEQUENCE statements.

Unsupported for ALTER TABLE

Skeema can CREATE or DROP tables using these features, but cannot generate an ALTER manipulating them. The output of skeema diff and skeema push will note that it cannot generate or run ALTER TABLE for tables involving these features, so the affected table(s) will be skipped, but the rest of the operation will proceed as normal.

  • sub-partitioning (two levels of partitioning in the same table)
  • general tablespaces (explicit TABLESPACE clauses other than innodb_system or innodb_file_per_table)
  • MariaDB’s application-time periods feature (PERIOD FOR clause)
  • some features of non-InnoDB storage engines

You can still ALTER these tables externally from Skeema (e.g., direct invocation of ALTER TABLE or pt-online-schema-change). Afterwards, you can update your schema repo using skeema pull, which will work properly even on these tables.

Beginning with Skeema v1.10.1, Skeema can often generate ALTER TABLE statements for pre-existing tables that already use unsupported features, as long as the new modification to the table does not involve adding or modifying those unsupported features.

Renaming columns or tables

Skeema cannot currently be used to rename columns within a table, or to rename entire tables. This is a shortcoming of Skeema’s declarative approach: by expressing everything as a CREATE TABLE, there is no way for Skeema to know (with absolute certainty) the difference between a column rename vs dropping an existing column and adding a new column. A similar problem exists around renaming tables.

A solution may be added in a future release. The prioritization will depend on user demand. Many companies disallow renames in production anyway, as they present substantial deploy-order complexity: it’s impossible to deploy application code changes at the exact same time as a column or table rename in the database.

Currently, Skeema will interpret attempts to rename as DROP-then-ADD operations. But since Skeema automatically flags any destructive action as unsafe, execution of these operations will be prevented unless the allow-unsafe option is used, or the table is below the size limit specified in the safe-below-size option.

Note that for empty tables as a special-case, a rename is technically equivalent to a DROP-then-ADD anyway. In Skeema, if you configure safe-below-size=1, the tool will permit this operation on tables with 0 rows. This is completely safe, and can aid in rapid development.

For tables with data, the work-around to handle renames is to run the appropriate ALTER TABLE manually (outside of Skeema) on all relevant databases. You can update your schema repo afterwards by running skeema pull.

Cross-schema references and dependency ordering

Skeema’s workspace model only operates on a single database schema at a time, and does not automatically resolve ordering dependencies between multiple schemas. In some cases, this can be problematic when objects in one schema reference other objects in a completely different database schema.

Cross-schema foreign keys: Skeema’s sessions automatically use foreign_key_checks=0 for workspace operations, which prevents nearly all problems with foreign key ordering or circular foreign key dependencies. However, one problematic edge case is an attempt to add a new cross-schema FK to the “child” side table at the same time as adding the referenced column(s) to the pre-existing “parent” side table. You may need to manually order your operations for this to work properly.

Cross-schema views: When the SELECT statement for a view references some tables/views in a different database schema, you may need to manually order your operations for this to work properly. Cross-schema views are also incompatible with workspace=docker; that option cannot be used on any subdirectory which contains views that reference tables/views in a different database.

To manually resolve ordering problems, there are several possible techniques:

  • In some situations, splitting up your changes into multiple commits (running skeema push on each commit individually) is sufficient to resolve ordering conflicts.

  • Rather than running skeema push from a parent directory, you can cd to each bottom-level schema subdirectory in the desired order, running skeema push from each subdirectory individually.

  • Run skeema push twice: on the first run, use ignore-table and/or ignore-view to skip the problematic objects that have complex cross-schema dependencies. On the second run, omit these “ignore” options to create these objects, once the other referenced objects exist.

Implementation notes and special cases

Routines

Skeema v1.2 added support for MySQL routines (stored procedures and functions). This support generally handles all common usage patterns, but there are a few edge-cases to be aware of:

  • Dropping a routine is considered a destructive action, requiring the allow-unsafe option.
  • When modifying an existing routine, Skeema uses different syntax in MySQL vs MariaDB.
    • MySQL does not support CREATE OR REPLACE for atomic modifications to existing routines, so Skeema must emit a DROP followed by a re-CREATE. This is considered an unsafe action, as there may be a split-second period where the routine does not exist.
    • MariaDB supports CREATE OR REPLACE to atomically modify an existing routine. This syntax is used automatically in Skeema Premium v1.6+ or Skeema Community v1.9+. Since this operation is atomic, it is not considered an unsafe action.
    • Skeema v1.11+ now considers changes to a routine’s parameters or return type to be unsafe, even in MariaDB.
    • When only modifying a routine’s characteristics (SQL SECURITY; CONTAINS/READS/MODIFIES SQL; COMMENT clauses), Skeema v1.11+ uses ALTER PROCEDURE / ALTER FUNCTION syntax if possible. This is a safe, atomic change in both MySQL and MariaDB.
  • If you wish to manage stored procedures / functions that use a different DEFINER than Skeema’s user, SUPER privileges may be required in the Community edition of Skeema. Premium Skeema products add support for the strip-definer option to improve usability with non-superusers.
  • If you wish to manage functions and have binary logging enabled, SUPER privileges may be necessary for Skeema’s user, unless you have enabled the global database variable log_bin_trust_function_creators.
  • The ignore-proc and ignore-func regex options may be used to force Skeema to ignore particular procs or funcs, or even all procs and funcs.
  • By default, skeema diff and skeema push do not examine the creation-time sql_mode or db_collation associated with a routine. To add these comparisons, use the compare-metadata option.
  • Skeema does not support management of native or loadable UDFs, which are written in C++ and compiled into shared libraries.
  • MariaDB 10.3+ Oracle-style routine PACKAGEs are not supported.

Refer to our comprensive blog post on versioning and deploying stored routines for additional advice and recipes.

Partitioned tables

Skeema v1.4 added support for partitioned tables. The diff/push functionality fully supports changes to partitioning status: initially partitioning a previously-unpartitioned table; removing partitioning from an already-partitioned table; changing the partitioning method or expression of an already-partitioned table. The partitioning option controls behavior of DDL involving these operations. With its default value of “keep”, tables can be initially partitioned, but won’t subsequently be de-partitioned or re-partitioned.

Skeema intentionally ignores changes to the list of partitions for an already-partitioned table using RANGE or LIST partitioning methods; the assumption is that an external partition management script/cron is responsible for handling this, outside of the scope of the schema repository. Meanwhile, for HASH or KEY partitioning methods, attempting to change the partition count causes an unsupported diff error, skipping the affected table. Future versions of Skeema may add additional options controlling these behaviors.

Whenever a RANGE or LIST partitioned table is being dropped, Skeema will generate a series of ALTER TABLE ... DROP PARTITION clauses to drop all but 1 partition prior to generating the DROP TABLE. This avoids having a single excessively-long DROP TABLE operation, which could be disruptive to other queries since it holds MySQL’s dict_sys mutex.

Sub-partitioning (two levels of partitioning in the same table) is not supported for diff operations yet, as this feature adds complexity and is infrequently used.

Skeema v1.4.3 added new partitioning-related options to other commands (init, pull, format, lint) to control modification or suppression of PARTITION BY clauses in *.sql files. See options strip-partitioning and update-partitioning for more information. These new options are intentionally distinct from the diff/push partitioning option to avoid inadvertent effects from previously-configured values in .skeema files.

Views

Our paid Premium products include the ability to manage views. A few usage notes:

  • If you have configured Skeema to connect to the DB as a non-superuser, managing views may require additional privilege grants for this user.
    • The strip-definer option (introduced in v1.6.0) is automatically enabled by default in this situation, to help avoid issues with mismatched DEFINER clauses between environments.
  • When first upgrading from the Community edition CLI to the Premium edition, be sure to run skeema pull one time to bring in any views to the filesystem.
  • The database stores views in a canonical format which places the entire query on a single long line, also stripping any inline SQL comments. Users will typically want to reformat views manually. For this reason, by default skeema pull, skeema lint, and skeema format won’t modify or reformat existing CREATE VIEW statements in the filesystem. The update-views option must be used to specifically update or reformat existing views.
    • In particular, pay close attention to that option’s documentation relating to skeema pull. By default, this command does not update definitions of existing views, even if an ALTER VIEW statement was run manually / outside of Skeema.
  • The ignore-view regex option may be used to force Skeema to ignore particular views or even all views. Additionally, since views share a namespace with tables in the database, the ignore-table option applies to both tables and views.
  • If you wish to restrict or ban views in your environment, see the lint-has-view option.
  • If a view name begins with “_seed_”, it is interpreted as a special seed view and it will not be diffed, pushed, or pulled as usual. With seed views, the filesystem representation is always considered canonical, rather than the database-side definition. See update-seed-inserts for more information.
  • To restrict which DEFINER users may be used for views, see the lint-definer and allow-definer options. Or, to strip and ignore DEFINERs entirely, see the strip-definer option.
  • Dropping a view is considered a destructive action, requiring the allow-unsafe option.
  • Views may be altered atomically (Skeema uses ALTER VIEW as needed), so altering a view is not considered an unsafe action.
  • If you receive unexpected errors when operating on views, check your Skeema config for connect-options and try removing any overrides to sql_mode. These overrides are often no longer needed since Skeema v1.5.0.
  • Skeema only operates on a single database schema at a time, and does not resolve ordering dependencies between multiple schemas. Cross-schema views (that is, views whose SELECT references tables/views in a different database) can be problematic for this reason. See the cross-schema references section above for more information and advice.

Triggers

Our paid Premium products include the ability to manage triggers. A few usage notes:

  • If you have configured Skeema to connect to the DB as a non-superuser, managing triggers may require additional privilege grants for this user.
    • The strip-definer option (introduced in v1.6.0) is automatically enabled by default in this situation, to help avoid issues with mismatched DEFINER clauses between environments.
    • If binary logging is enabled, you may need to enable the global database variable log_bin_trust_function_creators in order to create triggers with a non-superuser.
  • When first upgrading from the Community edition CLI to the Premium edition, be sure to run skeema pull one time to bring in any triggers to the filesystem.
  • By default, skeema init and skeema pull place trigger definitions into the same .sql file as their corresponding table, after the table’s definition. For database flavors that support multiple triggers on the same table/event/timing combination (MySQL 5.7+, MariaDB 10.2+), note that the order of triggers in the .sql file is relevant, and will be respected for all trigger creation and diff logic.
  • The ignore-trigger regex option may be used to force Skeema to ignore particular triggers (e.g. those managed by pt-online-schema-change) or even all triggers.
  • If you wish to restrict or ban triggers in your environment, see the lint-has-trigger option.
  • To restrict which DEFINER users may be used for triggers, see the lint-definer and allow-definer options. Or, to strip and ignore DEFINERs entirely, see the strip-definer option.
  • Take care when using external online schema change tools on tables that already have triggers. For example, pt-online-schema-change has a --preserve-triggers option that can help with this, but it has some important technical implications, and cannot be used with MySQL 5.6 or earlier.
  • Dropping a trigger is considered a destructive action, requiring use of the allow-unsafe option.
  • In Skeema v1.11+, whenever a skeema push operation involves execution of multiple trigger DDL statements in the same schema, Skeema will automatically use LOCK TABLES briefly on all parent tables of the modified triggers. This is to ensure that no writes can occur in between the various trigger-related DDL statements.
    • The tables will be unlocked immediately upon completion or failure of all trigger-related DDL. Generally this lock only needs to be held for a split-second.
    • Skeema will automatically use a very low lock_wait_timeout for this session, in order to prevent disruption from a long metadata lock wait, in case a long-running transaction is already affecting any of these tables. In this case, you should re-try the skeema push after long-running transactions have completed.
    • As always, skeema diff can be used to preview this locking behavior without actually running anything.
    • Use of ddl-wrapper disables this locking behavior, since locks are per-session and Skeema cannot share a session with the external wrapper tool.
    • If the multiple trigger DDL statements all only consist of DROP TRIGGER statements (with no CREATEs mixed in), locking is skipped, since it is rarely beneficial when only removing triggers.
  • When modifying an existing trigger, the behavior depends on the database server vendor (MySQL vs MariaDB) as well as the Skeema version:
    • MySQL does not support CREATE OR REPLACE for atomic modifications to existing triggers, so Skeema must emit a DROP followed by a re-CREATE. This means there is normally some risk of writes occurring during the brief moment between the two statements, when the trigger does not exist.
    • Skeema v1.11+ uses table locking to prevent these writes (see above), automatically making the operation safe in MySQL. In prior versions of Skeema, modification of triggers in MySQL is simply considered an unsafe action, requiring use of the allow-unsafe option to proceed.
    • MariaDB supports CREATE OR REPLACE to atomically alter an existing trigger, as long as the trigger remains associated with the same table as before. Skeema will use this whenever possible, allowing the trigger to be modified atomically, which is safe even without locking. Skeema will still use table locking in MariaDB when creating or replacing multiple triggers in the same operation, to ensure data consistency during complex multi-trigger refactors.
  • By default, skeema diff and skeema push do not examine the creation-time sql_mode or db_collation associated with a trigger. To add these comparisons, use the compare-metadata option.