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.0
  • Percona Server 5.5 - 8.0
  • MariaDB 10.1 - 10.8

Some rare MySQL features – such as spatial indexes and 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, use Skeema v1.8.0+ to ensure compatibility, and 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, and has a known issue involving foreign keys in older versions of Aurora. Our Premium edition products are fully compatible with Aurora, and include coverage for Aurora MySQL 5.6, 5.7, and 8.0 in extensive integration test suites.

Skeema has not been tested against Galera Cluster / Percona XtraDB Cluster. If special execution of DDL statements is required in this environment, Skeema’s alter-wrapper and ddl-wrapper options may provide a possible solution.

Use of Skeema with Vitess is not supported in any form.

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.

Workspace usage

As described in the FAQ, most Skeema commands need to perform operations in a temporary “workspace” schema that is created, used, and then dropped for each command invocation. With default settings, the temporary schema is located on each DB instance that Skeema interacts with, and will be named _skeema_tmp. Skeema’s DB user needs these privileges for the temporary schema:

  • SELECT – to verify that workspace tables are still empty prior to dropping them
  • CREATE – to create the temporary schema, and create tables in it
  • DROP – to drop tables in the temporary schema, as well as the temporary schema itself when no longer in use
  • ALTER – to verify that generated DDL is correct
  • INDEX – to verify that generated DDL is correct with respect to manipulating indexes
  • CREATE ROUTINE, ALTER ROUTINE – if you would like to manage stored procedures and functions using Skeema
  • CREATE VIEW, SHOW VIEW – if you would like to manage views (not available in the Community edition of Skeema)
  • TRIGGER – if you would like to manage triggers (not available in the Community edition of Skeema)

Alternatively, you can configure Skeema to use a workspace on a local ephemeral Docker instance via the workspace=docker option. This removes the need for privileges for the temporary schema on your live databases. Skeema can automatically manage the lifecycle of containerized databases.

Your application schemas

In order for all functionality in Skeema to work, the following privileges are needed on your application schemas (i.e., all databases aside from system schemas and the workspace schema):

  • SELECT – in order to see tables and confirm whether or not they are empty
  • CREATE – in order for skeema push to execute CREATE TABLE statements
  • DROP – in order for skeema push --allow-unsafe to execute DROP TABLE statements; omit this privilege on application schemas if you do not plan to ever drop tables via Skeema
  • ALTER – in order for skeema push to execute ALTER TABLE statements
  • INDEX – in order for skeema push to execute ALTER TABLE statements that manipulate indexes
  • CREATE ROUTINE, ALTER ROUTINE – if you would like to manage stored procedures and functions using Skeema
  • CREATE VIEW, SHOW VIEW – if you would like to manage views (not available in the Community edition of Skeema)
  • TRIGGER – if you would like to manage triggers (not available in the Community edition of Skeema)

When first testing out Skeema, if you do not plan on using skeema push initially, it is fine to omit everything but SELECT on your application schemas.

If using the alter-wrapper option to execute a third-party online schema change tool, you will likely need to provide additional privileges as required by the tool; or you may configure the third-party tool to connect to the database using a different user than Skeema does.

System schemas

Skeema interacts extensively with information_schema, but MySQL grants appropriate access automatically based on other privileges provided.

Skeema does not require access to the mysql system schema, but will conditionally query the mysql.proc table if it is available and exists in your server version. This improves Skeema’s performance on databases that have a large number of stored procedures and functions, since it avoids the need to run individual SHOW CREATE queries.

Skeema does not interact with the performance_schema or sys schemas.

Global privileges

The SHOW DATABASES global privilege is recommended. Technically it should be redundant with the privileges granted on each application schema. However by granting SHOW DATABASES, other privilege problems become more obvious, e.g. when trying to diagnose why Skeema can’t “see” one or more application schemas.

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+)
    • In older versions of MySQL or any version of MariaDB, granting global SELECT ON *.* may be used instead.
  • SET_USER_ID (MySQL 8+) or SET USER (MariaDB 10.5+): permits management of definers (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

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.

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 a known incompatibility with older versions of AWS Aurora: tables with multiple foreign keys may not be diffed, causing skeema diff and skeema push to skip them. A warning is logged in this situation.

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).

Row data / seed data / data migrations

Skeema’s focus is currently on DDL. You may place DML (e.g. INSERT statements) in .sql files manually, but Skeema will not parse them.

Unsupported for ALTER TABLE

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

  • spatial indexes
  • 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 system versioned tables / bitemporal tables / application time periods
  • 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.

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.

Implementation notes and special cases

Routines

Skeema v1.2.0 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 Community Edition will always use a DROP followed by a re-ADD. This is considered an unsafe action, as there may be a split-second period where the routine does not exist.
    • Although MySQL and MariaDB support ALTER PROCEDURE / ALTER FUNCTION for routine characteristic changes, Skeema does not use these, since such changes are rare. (The body of a routine cannot be changed using ALTER syntax.)
    • MariaDB supports CREATE OR REPLACE to atomically alter an existing routine. See next section regarding Skeema v1.6.0 Premium features.
  • 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.
  • 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 UDFs, which are typically written in C or C++ and compiled into shared libraries.
  • MariaDB 10.3+ Oracle-style routine PACKAGEs are not supported.

Beginning with Skeema v1.6.0, our Premium products add some enhanced functionality for stored procedures and functions:

  • The strip-definer option provides the ability to remove DEFINER clauses from *.sql files, as well as ignoring DEFINERs in diff operations. By default it is enabled whenever Skeema’s user does not have sufficiently elevated privileges to set users.
  • 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.
  • When modifying an existing routine in MariaDB, CREATE OR REPLACE will be emitted automatically. Since this operation permits modifying a routine in a single statement, it is not considered an unsafe action.

Partitioned tables

Skeema v1.4.0 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.
  • 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.

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.
  • Triggers are placed 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 MySQL and Percona Server, CREATE OR REPLACE TRIGGER syntax is not available. This means Skeema must handle any changes to an existing trigger’s definition by DROP TRIGGER followed by CREATE TRIGGER. This operation is not atomic (there will be a brief moment where the trigger does not exist), so it is considered an unsafe action, requiring use of the allow-unsafe option.
    • In MariaDB, CREATE OR REPLACE TRIGGER is available as long as the trigger remains associated with the same table as before. Skeema will use this when possible, allowing the trigger to be modified atomically, so this is not considered an unsafe action.
  • 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.