This document describes which environments are supported by Skeema.
Database version and flavor
Skeema currently supports the following databases:
- MySQL 5.5, 5.6, 5.7, 8.0
- Percona Server 5.5, 5.6, 5.7, 8.0
- MariaDB 10.1, 10.2, 10.3, 10.4, 10.5
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.
Compatibility with AWS Aurora is not guaranteed, since Aurora is a proprietary commercial fork with substantial internal differences from corresponding MySQL versions. One known incompatibility is noted in this issue report.
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.
If possible, configure Skeema to connect via an administrative database user with SUPER privileges. This is the easiest way to get Skeema working. However, it is also possible to run with reduced privileges. This section describes the minimum requirements for Skeema’s user.
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 database being interacted with, and will be named
_skeema_tmp. The MySQL user used by Skeema will need 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
ALTER ROUTINE– if you would like to manage stored procedures and functions using 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 pushto execute CREATE TABLE statements
DROP– in order for
skeema push --allow-unsafeto 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 pushto execute ALTER TABLE statements
INDEX– in order for
skeema pushto execute ALTER TABLE statements that manipulate indexes
ALTER ROUTINE– if you would like to manage stored procedures and functions using 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.
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
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
.skeemaconfiguration 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 lintmay 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.
Some of these may be added in future open source releases. Others will be added in the upcoming commercial edition of Skeema.
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.
Ignored object types
The following object types are completely ignored by Skeema. Their presence won’t break anything, but Skeema will not interact with them. This means that
skeema init and
skeema pull won’t create file representations of them;
skeema diff and
skeema push will not detect or alter them.
- grants / users / roles
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
Implementation notes and special cases
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
DROPfollowed by a re-
ADD. Although MySQL supports
ALTER FUNCTIONfor 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 pushdo 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
DEFINERthan Skeema’s user, and/or impact binary logging,
SUPERprivileges 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.
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.