This page describes Skeema’s support for various table-related features. Unless otherwise noted, all table-related functionality is identical between Skeema Community Edition and Skeema Premium Edition.
Storage engines
Skeema is primarily designed to work with the InnoDB storage engine, which is the default 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. We welcome community code contributions for support of alternative storage engines.
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.
Skeema’s linter can be configured to limit the list of permissible storage engines, and warn or error on any violations. Refer to options allow-engine and lint-engine.
Column types
Several linter options allow you to control what data types can be used for columns:
- Limit which data types can be used for primary key columns with allow-pk-type and lint-pk-type.
- Limit which data types can be used for auto-increment columns (to avoid smaller int types, which can max out) with allow-auto-inc and lint-auto-inc.
- Restrict which character sets may be used with string types by using allow-charset and lint-charset.
- Prevent use of nonstandard integer display widths by using lint-display-width.
- Flag use of floating-point types (typically in favor of using decimal types instead) with lint-has-float.
- Flag use of time-related types with lint-has-time.
- Flag use of enum and set types with lint-has-enum.
Online ALTER TABLE
For information on non-disruptive schema changes, see online schema change. This includes use of external online schema change tools (e.g. pt-online-schema-change, gh-ost, spirit), as well as configuring built-in database server functionality for online or instant ALTER TABLE.
Selectively ignoring table differences
Several Skeema options provide a mechanism for disregarding certain types of table differences in skeema diff and skeema push:
- Enable lax-column-order to suppress differences in column ordering.
- Enable lax-comments to suppress differences in comment clauses.
- Cosmetic differences in index ordering (relative ordering among multiple index definitions within a table) and foreign key naming are normally suppressed by default, but you can override this behavior by enabling exact-match.
- ignore-table can be used to completely ignore tables whose names match a regular expression. See also Ignore Regex Options.
To permanently suppress these types of differences, set these options in a .skeema file. Or if you wish to suppress them ad hoc in particular situations, set them on the command-line instead only when needed.
Auto-increment
By default, Skeema will strip the next auto_increment counter (e.g. AUTO_INCREMENT=12345 clause towards the end of the CREATE TABLE) when dumping a table to the filesystem in skeema init or skeema pull. Because this value changes often and usually differs between dev/stage/prod environments, most users do not wish to track this value in version control. However, if you wish to track it, see option include-auto-inc.
If you have hundreds (or more) of auto-increment tables, we recommend using Skeema v1.11.1+, which greatly improves performance of skeema diff and skeema push in this situation.
You can limit which data types are permissible for auto-increment columns (to avoid smaller int types, which can max out) with the allow-auto-inc and lint-auto-inc options.
Generated columns
Skeema fully supports use of generated columns – both VIRTUAL and STORED – in MySQL 5.7+ and MariaDB 10.2+.
In MySQL, when modifying a table to add or modify a virtual column, by default the per-row validity of the generated column expression is not confirmed. To force virtual column validation, enable Skeema’s alter-validate-virtual option, which adds a WITH VALIDATION clause to any generated ALTER TABLE. However, this causes the database server to perform the alter operation using a table copy algorithm, which is slow.
In MariaDB, virtual column validation depends solely on the selected alter algorithm, which means Skeema’s alter-algorithm option can be used to influence it.
Foreign key constraints
Skeema supports use of foreign key contraints, but there are some important caveats to be aware of.
By default, skeema push executes DDL in a session with foreign key checks disabled. This means that whenever a new foreign key is added to an existing table, no immediate integrity check is performed on existing data. This results in faster ALTER TABLE execution, and eliminates one possible failure vector for the DDL. To override this behavior and force integrity checks for ALTER TABLE, enable Skeema’s foreign-key-checks option.
Regardless of that option, skeema push executes CREATE TABLE and DROP TABLE in a session with foreign key checks disabled, in order to avoid order-of-operation / dependency chain conflicts. This design allows circular foreign key contraints to be fully supported.
When foreign key checks are disabled, the database server doesn’t confirm that the parent (referenced) table in a foreign key constraint actually exists. The lint-fk-parent option, introduced in Skeema v1.12, helps to catch this situation. It also confirms that the parent table has an appropriate UNIQUE KEY on the specific referenced columns. MySQL and MariaDB have historically been lax about this (allowing non-unique indexes, and indexes containing additional columns), but MySQL 8.4 has deprecated that behavior, and a future MySQL version will strictly require an exactly matching UNIQUE KEY on the parent side table.
Some problematic edge cases exist with foreign keys that cross schema boundaries – that is, the parent and child tables are in different databases. See cross-schema references and dependency ordering.
Even though Skeema itself supports foreign key constraints, they can be problematic with external online schema change tools:
pt-online-schema-changecan operate on foreign keys, albeit with extra complexity and riskgh-ost,spirit,fb-osc, and LHM cannot operate on tables with foreign keys
By default, Skeema ignores discrepancies in the names of otherwise-equivalent foreign key definitions, for reasons of compatibility with pt-online-schema-change’s behavior. For more information, refer to our OSC tool documentation.
If you wish to limit or ban use of foreign keys altogether, see the lint-has-fk option.
Check constraints
Check constraints are fully supported since Skeema v1.5.
In MySQL, individual check constraints can be disabled or re-enabled by adding or removing a NOT ENFORCED clause in the constraint’s definition. The appropriate ALTER TABLE ... ALTER CHECK clause will be emitted by skeema diff or skeema push.
MariaDB does not support disabling individual check constraints, but it does allow you to disable enforcement for all checks on either a session or global basis with the check_constraint_checks server variable. If desired, you can enable this only for Skeema’s sessions by using connect-options.
As of Skeema v1.12.1+, diffs that only affect the name of a check constraint are suppressed by default. This behavior prevents spurious diffs from online schema change tools, which must rename check constraints as part of their operation. For more information, refer to our OSC tool documentation.
Indexes
Skeema can handle all major index variants and features for InnoDB tables, including fulltext indexes, spatial indexes, functional index parts, descending index parts, and invisible / ignored indexes.
To flag tables that lack a primary key, use option lint-pk. You can also restrict which column data types are permissible for use in primary keys with options lint-pk-type and allow-pk-type.
The lint-dupe-index option can be used to flag duplicate or redundant secondary indexes.
By default, when a table has multiple secondary indexes, Skeema ignores differences in the relative ordering of them within CREATE TABLE statements in most situations, because this difference typically does not affect the database’s behavior. See option exact-match to control this functionality.
Fulltext indexes may optionally use alternative parsers (WITH PARSER clause). However, when using an alternative parser that isn’t built-in to the server, Skeema’s Docker workspaces feature cannot be used.
Skeema v1.12+ adds support for automatically detecting index renames, and will generate RENAME KEY accordingly if the database server flavor supports it (MySQL 5.7+ or MariaDB 10.5+). This syntax can only be used when no other aspect of the index’s definition is being changed. In all other cases, Skeema will emit a DROP KEY on the old index name, and ADD KEY to recreate the index with its new name and modified definition.
Dropping, modifying, or renaming an index is not considered an unsafe operation. However, if you attempt to change the collation of a string-type column which is part of a uniqueness constraint (primary key or unique index), this is considered unsafe. Collations determine what characters are considered equal, so changing the collation can cause subtle adjustments to the behavior of the uniqueness constraint.
Compressed tables
Skeema supports use of traditional InnoDB table compression, as well as InnoDB transparent page compression. Additionally, the column compression features in MariaDB and Percona Server are supported; MySQL has no equivalent feature yet.
Skeema’s linter can be configured to restrict which types of compression are allowed, or require use of compression, or prevent use of compression. See options allow-compression and lint-compression.
Partitioned tables
Partitioned tables are partially supported, with some important caveats.
Skeema’s partitioned table support is primarily designed to support a specific time-based data retention workflow. This approach to partitioning is favored by many large MySQL users for truncating expired/unneeded data, because dropping a partition is substantially more performant than equivalent row-based DELETE queries. This partitioning workflow has the following characteristics:
- Partitioned tables use the
RANGEorLISTpartitioning methods (or theRANGE COLUMNSorLIST COLUMNSvariants) - Each partition stores data for a specific time interval
- An external cron / scheduled task is responsible for managing partition rotation (outside of Skeema) – that is, dropping old partitions when their data is no longer needed, and creating new empty partitions to handle upcoming time intervals
- In the schema repo, partitioned tables are included with their full
PARTITION BYclauses and some initial list of partitions, but that partition list is not kept up-to-date in the repo - Changes to the partition list (performed by the external cron) are generally not tracked in version control, because partition rotation is orthogonal to schema management (similar to how auto_increment counters are typically not tracked either)
- Some environments omit partitioning altogether; for example, tables in a dev database are created as normal unpartitioned tables, and no partition rotation cron is needed there
For this reason, skeema diff and skeema push completely ignore changes to the list of partitions for tables using the RANGE, RANGE COLUMNS, LIST, or LIST COLUMNS partitioning methods.
Additionally, by default skeema diff and skeema push won’t ever de-partition an already-partitioned table, nor alter the existing partitioning method or expression. This functionality is controlled by the partitioning option, which provides a mechanism for selectively limiting which environments use partitioning, as per the unpartitioned dev database example above.
Similarly, by default skeema pull won’t update the partitioning clause of tables in the schema repo, unless the update-partitioning option is enabled.
If you wish to exclude all partitioning clauses from your schema repo altogether, enable the strip-partitioning option when running skeema init or skeema format.
Whenever a RANGE, RANGE COLUMNS, LIST, or LIST COLUMNS 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 internal data dictionary locks.
For the HASH, LINEAR HASH, or KEY partitioning methods, Skeema is not yet able to modify the number of partitions. With these methods, attempting to change the partition count causes an unsupported diff warning, skipping the affected table.
Sub-partitioning (two levels of partitioning in the same table) is not supported for skeema diff or skeema push yet, and will cause an unsupported diff warning to be emitted, skipping the table.
As a workaround for these unsupported situations, you can alter the table outside of Skeema, and then use skeema pull with the update-partitioning option to update your schema repo.
MySQL general tablespaces
The skeema diff and skeema push commands cannot currently generate ALTER TABLE clauses relating to MySQL’s general tablespaces feature (explicit TABLESPACE clauses, other than innodb_system or innodb_file_per_table). When attempting to do so, the affected table(s) will be skipped with a warning logged.
General tablespaces are not commonly used. Although this functionality is normally present in MySQL 5.7+, most managed database platforms do not support it. MariaDB has no equivalent feature.
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. The database server treats metadata for these tables slightly differently than other tables. Community code contributions for support of this feature would be gladly welcomed.
As of Skeema v1.11.1+, system-versioned tables are automatically ignored in a way similar to ignore-table. In prior versions of Skeema, dump-functionality commands such as skeema pull would handle these tables incorrectly due to their differences in information_schema representation.
MariaDB application-time periods
The skeema diff and skeema push commands cannot currently generate ALTER TABLE clauses relating to MariaDB’s application-time periods feature (PERIOD FOR clause). When attempting to do so, the affected table(s) will be skipped with a warning logged. Community code contributions for support of this feature would be gladly welcomed.