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

Testing is performed with the database server running on Linux only. Other database-side operating systems should work fine if all table and schema identifiers are entirely lowercase. Due to case-insensitive filesystems, there is a known incompatibility when the database server is running on Windows or MacOS, if schema names or table names contain any uppercase characters.

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

The open source Community edition of the Skeema CLI does not guarantee full compatibility with AWS Aurora, and has some known issues involving foreign keys. Only our Premium edition products are fully compatible with Aurora.

Skeema has not been tested yet on 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 on Vitess is not supported. Skeema’s native sharding logic is designed to connect to each MySQL instance directly, and Vitess’s operation model does not permit this.

Privileges

When connecting to the database, Skeema needs a number of privilege grants for its operations. The easiest way to get Skeema working is to use an administrative DB user with SUPER privileges, but it is possible to use more fine-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:

  • 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
  • SELECT – to verify that tables are still empty prior to dropping them
  • 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.

If you wish to manage stored procedures / functions that use a different DEFINER than Skeema’s user, and/or impact binary logging, SUPER privileges may be necessary for Skeema’s user. Consult the manual for your database version for more information.

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.

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. This will be improved in future releases.
  • 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 open source software, Skeema is distributed without warranties of any kind. See LICENSE.

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 some versions of AWS Aurora when foreign keys are in use.

Windows exe

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

Unsupported features – all editions of Skeema

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

Client-side SSL certs

At this time, Skeema does not support configuring a specific client-side SSL cert or CA when connecting to MySQL. As a work-around, you may be able to use an SSL-aware proxy such as Cloud SQL Proxy or ProxySQL.

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)
  • 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 will use a DROP followed by a re-ADD. Although MySQL supports ALTER PROCEDURE / ALTER FUNCTION for metadata-only changes, Skeema does not use this yet.
  • Because modifying a routine involves a DROP, it is still considered a destructive action, as there may be a split-second period where the routine does not exist.
  • 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.
  • If you wish to manage stored procedures / functions that use a different DEFINER than Skeema’s user, and/or impact binary logging, SUPER privileges may be necessary for Skeema’s user. Consult the manual for your database version for more information.
  • Skeema does not support management of native UDFs, which are typically written in C or C++ and compiled into shared libraries.
  • MariaDB 10.3’s Oracle-style routine PACKAGEs are not supported.

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.
    • In this case, Skeema will be unable to operate on views that have a different DEFINER than Skeema’s user. Manually removing DEFINER clauses from your *.sql files may help in some scenarios; an automated strip-definers option will be added soon to aid with this.
  • 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.
  • Since views share a namespace with tables in the database, there is no separate ignore-view option in Skeema. Instead, the existing 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.
  • 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.
    • In this case, Skeema will be unable to operate on triggers that have a different DEFINER than Skeema’s user. Manually removing DEFINER clauses from your *.sql files may help in some scenarios; an automated strip-definers option will be added soon to aid with this.
  • 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.
  • 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, Percona Server, and older versions of MariaDB, 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 10.2+, CREATE OR REPLACE TRIGGER is available and used by Skeema. This allows trigger definitions 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.