Docs: Requirements

This document describes which environments are supported by Skeema.

Database version and flavor

Skeema currently supports the following databases:

  • MySQL 5.5 - 9.1
  • Percona Server 5.5 - 8.4
  • MariaDB 10.1 - 11.6

Skeema primarily focuses on supporting the InnoDB storage engine, which is the default (and most popular) table storage engine in all modern versions of MySQL and MariaDB. Other storage engines are often perfectly functional in Skeema, but it depends on whether any esoteric features of the engine are used. Additionally, some uncommonly-used database features – such as subpartitioning – are not supported yet in Skeema’s diff operations, regardless of storage engine.

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 feature request issue on GitHub so that the work can be prioritized appropriately.

If you run database servers natively on a mix of different operating systems, be sure to follow our general guidelines on avoiding data and schema portability problems caused by mixing case-sensitive and case-insensitive server filesystems.

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 server than DDL statements (“automatic read/write split”); or if it manipulates the results of queries, including combining results from multiple database servers into one result-set (automatic sharding). 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 an administrative user with extensive grants on *.* for a managed cloud database 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: 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 server 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)
  • EVENT – allows management of events (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, unless you configure Docker workspaces.

When using an external 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 have 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 functionality for stripping DEFINER clauses automatically 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 (MySQL 8.0.20+), SHOW CREATE ROUTINE (MariaDB 11.3+), or SELECT ON *.* (any version of MySQL or MariaDB): grants permission to see bodies of routines that have a different definer than the current user
  • SET_USER_ID (MySQL 8.0 and 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. For example, you must explicitly grant SET USER and BINLOG ADMIN even if a user already has SUPER in MariaDB 11.0+.

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 behavior. Otherwise, certain forms of ALTERs will lock the table and/or 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 against 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, Triggers, and Events

The Community edition of the Skeema CLI only interacts with tables, stored procedures, and functions. It completely ignores views, triggers, and events; their presence in the database won’t break anything, but Skeema Community Edition 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

All editions of Skeema can use encrypted database connections, but several advanced security options are not available in the Community edition. See the SSL / TLS configuration page for more information.

Unsupported features – all editions of Skeema

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

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 directly process any other type of imperative DML. See our blog post on managing data migrations and other imperative changes to learn about ways to track and execute these operations.

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.

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, to execute a rename, you can run the appropriate RENAME TABLE or ALTER TABLE outside of Skeema (manually, or using an imperative migration tool) on all relevant databases. Alternatively, in Skeema Premium, you can use a scheduled event to track the rename operation in version control and execute it in all environments at a specific time. Regardless of approach, you can update your schema repo afterwards by running skeema pull.

In production environments, renames involve substantial deploy-order complexity: it’s impossible to deploy application code changes (to use the new name) at the exact same moment as the rename operation in the database. Many companies either prohibit renames entirely once a table is in production, or use special processes involving application downtime/maintenance windows.

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 Docker workspaces, which 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, supply command-line overrides for --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.