Docs: Options

This document is a reference, describing all options supported by Skeema. To learn how to use options in general, please see the configuration guide.

Index


allow-auto-inc

Commandsdiff, push, lint, Cloud Linter
Default“int unsigned, bigint unsigned”
Typestring
RestrictionsTo specify multiple values, use a comma-separated list

This option specifies which data types are permissible for auto_increment columns. This option only has an effect if lint-auto-inc is set to “warning” (the default) or “error”. If so, a warning or error (respectively) will be emitted for any auto_increment column using a data type not included in this list.

When specifying values in this list, always omit the display width. For example, specify “int unsigned”, never “int(10) unsigned”.

The purpose of this option is to avoid various auto_increment pain points:

  • Integer overflow / ID exhaustion: it is extremely problematic when an auto_increment column reaches the maximum value for its column type. To avoid this situation, only allow larger int types for this option.
  • Signed types: The behavior of auto_increment columns is undefined when negative numbers are present. To avoid this situation, only permit unsigned types for this option.
  • Float types: MySQL and MariaDB permit float and double columns to be auto_increment, but due to inexact precision the behavior is undesirable and nonsensical, and now deprecated in MySQL 8.0.17.

Some companies ban use of auto_increment entirely. This can be enforced in Skeema by setting this option to a blank string (e.g. allow-auto-inc='') while also setting lint-auto-inc to “error”.

allow-charset

Commandsdiff, push, lint, Cloud Linter
Default“latin1,utf8mb4”
Typestring
RestrictionsTo specify multiple values, use a comma-separated list

This option specifies which character sets are permitted by Skeema’s linter. This option only has an effect if lint-charset is set to “warning” (the default) or “error”. If so, a warning or error (respectively) will be emitted for any table using a character set not included in this list.

This option checks column character sets as well as table default character sets. It does not currently check any other object type besides tables.

If you wish to intentionally allow the legacy 3-byte utf8 character set, note that the names “utf8” and “utf8mb3” are treated as equivalent in allow-charset since Skeema v1.5.1. However, future releases of MySQL and MariaDB may eventually change “utf8” to be an alias for “utf8mb4” instead, at which point Skeema’s behavior will need to change as well. For this reason, the more-specific “utf8mb3” name may be more future-proof.

allow-compression

Commandsdiff, push, lint, Cloud Linter
Default“none,4kb,8kb”
Typestring
RestrictionsTo specify multiple values, use a comma-separated list

This option specifies which InnoDB table compression settings/modes are permitted by Skeema’s linter. This option only has an effect if lint-compression is set to “warning” (the default) or “error”. If so, a warning or error (respectively) will be emitted for any InnoDB table using a compression setting not included in this list.

The value of this option should be set to a comma-separated list of one or more of the following values:

  • none: Allow uncompressed tables
  • 1kb: Allow tables using KEY_BLOCK_SIZE=1
  • 2kb: Allow tables using KEY_BLOCK_SIZE=2
  • 4kb: Allow tables using KEY_BLOCK_SIZE=4
  • 8kb: Allow tables using KEY_BLOCK_SIZE=8
  • 16kb: Allow tables using KEY_BLOCK_SIZE=16
  • page: Allow tables using transparent page compression (e.g. COMPRESSION='zlib' in MySQL, or PAGE_COMPRESSED=1 in MariaDB)

To prevent use of compressed tables, set only allow-compression=none. Conversely, to require compression, set allow-compression to a list of values that excludes none.

For the purposes of this option, InnoDB tables which specify ROW_FORMAT=COMPRESSED without a KEY_BLOCK_SIZE are treated by Skeema’s linter as having KEY_BLOCK_SIZE=8, which is correct assuming the innodb_page_size on the server has not been modified from its default.

allow-definer

Commandsdiff, push, lint, Cloud Linter
Default“%@%”
Typestring
RestrictionsTo specify multiple values, use a comma-separated list

This option specifies which DEFINER users are permitted by Skeema’s linter for stored procedures, functions, views, and triggers. This option only has an effect if lint-definer is set to “error” (the default) or “warning”. If so, an error or warning (respectively) will be emitted for any definition using a DEFINER not matched by any value in this list.

The value of this option should be a comma-separated list of MySQL-style user@host values, optionally using SQL LIKE-style wildcards of % and _. For example, allow-definer=root@%,procdef@192.168.% will permit a definer of root with any hostname, or procdef with any IP beginning with 192.168.

The default value for this option is intentionally permissive of all possible DEFINER users. You must override this option if you wish to restrict what DEFINER users are permissible. This is useful for limiting privileges of routines, views, and triggers. It also protects against accidentally creating orphaned objects when running Skeema with a privileged database user. Otherwise, if you simply have a typo in a DEFINER clause, you can inadvertently create an orphaned object if your user happens to have the SET_USER_ID (MySQL 8.0+), ALLOW_NONEXISTENT_DEFINER (MySQL 8.2+), or SET USER (MariaDB 10.5+) privilege.

In the Community edition of Skeema, views and triggers are not supported, so this option only affects stored procedures and functions.

In premium Skeema products, if the strip-definer option is enabled, all DEFINER clauses are completely ignored, and the allow-definer and lint-definer options have no effect. Note that strip-definer is enabled by default in the Premium edition of the Skeema CLI whenever the user does not have sufficient privileges to set users, e.g. SUPER.

allow-engine

Commandsdiff, push, lint, Cloud Linter
Default“innodb”
Typestring
RestrictionsTo specify multiple values, use a comma-separated list

This option specifies which storage engines are permitted by Skeema’s linter. This option only has an effect if lint-engine is set to “warning” (the default) or “error”. If so, a warning or error (respectively) will be emitted for any table using a storage engine not included in this list.

allow-pk-type

Commandsdiff, push, lint, Cloud Linter
Defaultempty string
Typestring
RestrictionsTo specify multiple values, use a comma-separated list

This option specifies which data types are permissible for use in primary key columns. This option only has an effect if lint-pk-type is set to “warning” or “error”. If so, a warning or error (respectively) will be emitted for any primary key column using a data type not included in this list.

By default, lint-pk-type defaults to “ignore” (linter check disabled) while allow-pk-type defaults to an empty string. If you enable lint-pk-type, you must also set allow-pk-type to at least one column type.

When specifying values in this list, always omit lengths, display widths, character sets, collations, and modifiers such as “unsigned”. For example, specify “int” or “varchar”, but never “int(10) unsigned” or “varchar(30) character set latin1”.

This option is particularly useful when combining Skeema with the Spirit online schema change tool, which contains significant optimizations for tables that have memory-comparable primary keys. To encourage or require these optimizations, set lint-pk-type to either “warning” or “error”, and allow-pk-type to “tinyint,smallint,mediumint,int,bigint,decimal,varbinary,binary,date,datetime,timestamp,time,year” or any desired subset of those types.

allow-unsafe

Commandsdiff, push
Defaultfalse
Typeboolean
Restrictionsnone

If set to the default of false, skeema push refuses to run any DDL on a database if any of the operations are “unsafe” – that is, they have the potential to destroy data. Similarly, skeema diff also refuses to function in this case; even though skeema diff never executes DDL anyway, it serves as an accurate “dry run” for skeema push and therefore aborts in the same fashion.

The following operations are considered unsafe, as of the most recent version of Skeema:

  • Dropping a table
  • Altering a table to drop a normal column or stored (non-virtual) generated column
  • Altering a table to modify an existing column in a way that potentially causes data loss, truncation, corruption, or reduction in precision
  • Altering a table to modify the character set of an existing column
  • Altering a table to modify the collation of an existing column, if that column is part of a uniqueness constraint
  • Altering a table to change its storage engine
  • Dropping a stored procedure or function
  • Dropping and re-creating a stored procedure or function to change its parameters or return type
  • Dropping and re-creating a MySQL stored procedure or function to change its body, DEFINER, DETERMINISTIC property, or creation-time metadata
    • This is only unsafe in MySQL, which lacks atomic CREATE OR REPLACE syntax. In MariaDB, this operation safe.
  • Dropping a view (only supported in premium Skeema products)
  • Dropping a trigger (only supported in premium Skeema products)

If allow-unsafe is set to true, these operations are fully permitted, for all objects. It is not recommended to enable this setting in an option file, especially in the production environment. It is safer to require users to supply it manually on the command-line on an as-needed basis, to serve as a confirmation step for unsafe operations.

To conditionally control execution of unsafe operations for tables based on their size, see the safe-below-size option.

alter-algorithm

Commandsdiff, push
Defaultempty string
Typeenum
RestrictionsRequires one of these values: “copy”, “inplace”, “nocopy”, “instant”, “default”, ""

Adds an ALGORITHM clause to any generated ALTER TABLE statement, in order to control how the database server internally performs the alteration. When used in skeema push, executing the statement will fail if any generated ALTER clause does not support the specified algorithm. See your database server’s manual for more information on the effect of the ALGORITHM clause.

The explicit value “default” is supported, and will add an “ALGORITHM=DEFAULT” clause to all ALTER TABLEs, but this has no effect versus simply omitting alter-algorithm entirely.

Some algorithm values are only available in certain database flavors/versions:

  • The “instant” algorithm is only available in MySQL 8.0+ and MariaDB 10.3+.
  • The “nocopy” algorithm is only available in MariaDB 10.3+. It does not exist in MySQL.
  • MySQL 5.5 does not support the ALGORITHM clause at all.

The selected algorithm value is passed through as-is to the database server. An error will be thrown if your server flavor/version does not support the selected value.

The behavior of “inplace” differs between MySQL and MariaDB in situations where the alteration operation actually supports use of the faster INSTANT algorithm. In MySQL, if you explicitly specify “inplace”, then the operation will not use the INSTANT algorithm. However, in MariaDB, specifying “inplace” will automatically upgrade to the faster INSTANT algorithm whenever the operation supports it.

alter-lock

Commandsdiff, push
Defaultempty string
Typeenum
RestrictionsRequires one of these values: “none”, “shared”, “exclusive”, “default”, ""

Adds a LOCK clause to any generated ALTER TABLE statement, in order to force enabling/disabling MySQL 5.6+ or MariaDB 10.0+ support for online DDL. When used in skeema push, executing the statement will fail if any generated ALTER clause does not support the specified lock method. See your database server’s manual for more information on the effect of this clause.

The explicit value “default” is supported, and will add a “LOCK=DEFAULT” clause to all ALTER TABLEs, but this has no real effect vs simply omitting alter-lock entirely.

MySQL 5.5 does not support the LOCK clause of ALTER TABLE, so use of this option will cause an error in that version.

alter-validate-virtual

Commandsdiff, push
Defaultfalse
Typebool
Restrictionsnone

This option adds a WITH VALIDATION clause to any generated ALTER TABLEs which affect virtual columns. This clause tells the database server to confirm that the calculated values fit into the designated data type of the new or modified virtual column(s), for all existing rows of the table. However, this validation can cause significantly slower ALTER TABLE performance. Refer to the MySQL manual for more information.

Skeema will only ever apply the WITH VALIDATION clause to a generated ALTER TABLE which adds or modifies a virtual generated column. This option has no effect in all other situations, meaning it is safe to enable permanently in a .skeema file if desired. This option also has no effect in versions of MySQL prior to 5.7, when generated column support was added.

When an external online schema change tool is being used, such tools rebuild an entire table, and the database server will inherently perform virtual column validation as rows are bulk-copied into the new table. This happens regardless of whether the WITH VALIDATION clause is present.

In MariaDB, enabling this option will cause a syntax error. Even though MariaDB 10.2+ supports MySQL’s syntax for generated columns, MariaDB does not support the WITH VALIDATION clause. Instead, to force validation of virtual columns in MariaDB, you must use alter-algorithm=copy.

alter-wrapper

Commandsdiff, push
Defaultempty string
Typestring
Restrictionsnone

This option causes skeema push to shell out to an external process, such as pt-online-schema-change, to handle execution of ALTER TABLE statements.

For each ALTER TABLE statement in the diff, rather than executing the ALTER directly, skeema push will instead shell out to the external process. Meanwhile the output of skeema diff will display what command-line would be executed, but it won’t actually be run.

Use of an absolute path is recommended. Relative paths may not work as expected.

This option supports use of special variables, which you can pass to the external script on its command-line. Skeema will dynamically replace these with an appropriate value when building the final command-line. See options with variable interpolation for more information. The following variables are supported by alter-wrapper:

  • {HOST} – hostname (or IP) that this ALTER TABLE targets
  • {PORT} – port number for the host that this ALTER TABLE targets
  • {SCHEMA} – schema name containing the table that this ALTER TABLE targets
  • {USER} – MySQL username defined by the user option either via command-line or option file
  • {PASSWORD} – MySQL password defined by the password option either via command-line or option file
  • {PASSWORDX} – Behaves like {PASSWORD} when the command-line is executed, but only displays X’s whenever the command-line is displayed on STDOUT
  • {ENVIRONMENT} – environment name from the first positional arg on Skeema’s command-line, or “production” if none specified
  • {DDL} – Full ALTER TABLE statement, including all clauses
  • {NAME} – table name that this ALTER TABLE targets
  • {TABLE} – table name that this ALTER TABLE targets (identical to {NAME})
  • {SIZE} – size of table that this ALTER TABLE targets, in bytes. For tables with no rows, this will be 0, regardless of actual size of the empty table on disk.
  • {CLAUSES} – Body of the ALTER TABLE statement, i.e. everything after ALTER TABLE <name> . This is what pt-online-schema-change’s –alter option expects.
  • {TYPE} – always the word “ALTER” in all caps.
  • {CLASS} – always the word “TABLE” in all caps.
  • {CONNOPTS} – Session variables passed through from the connect-options option
  • {DIRNAME} – The base name (last path element) of the directory being processed.
  • {DIRPATH} – The full (absolute) path of the directory being processed.

This option can be used for integration with an online schema change tool, logging system, CI workflow, or any other tool (or combination of tools via a custom script) that you wish. An example alter-wrapper for executing pt-online-schema-change is included in the FAQ.

If an ssh or ssh-to-db SSH tunnel is being used, the wrapper script is executed on the remote SSH host, instead of locally. In this situation, be sure to configure alter-wrapper as a valid absolute path on the remote host. This functionality can be used to solve problems of security (always running wrappers from a single audited location), locking (preventing concurrent execution), uniformity (ensuring the same version of pt-osc or gh-ost), and portability (removing the need to get pt-osc working on Windows or MacOS for example). Refer to the documentation for the ssh option for more information, as well as guidance regarding use of terminal multiplexers such as screen or tmux.

This option does not affect CREATE TABLE or DROP TABLE statements; nor does it affect non-table DDL such as CREATE DATABASE or ALTER DATABASE. To execute all DDL (regardless of operation type or object class) through an external script, see ddl-wrapper.

alter-wrapper-min-size

Commandsdiff, push
Default0
Typesize
RestrictionsHas no effect unless alter-wrapper also set

Any table smaller than this size (in bytes) will ignore the alter-wrapper option. This permits skipping the overhead of external OSC tools when altering small tables.

The size comparison is a strict less-than. This means that with the default value of 0, alter-wrapper is always applied if set, as no table can be less than 0 bytes.

To only skip alter-wrapper on empty tables (ones without any rows), set alter-wrapper-min-size to 1. Skeema always treats empty tables as size 0 bytes as a special-case.

If alter-wrapper-min-size is set to a value greater than 0, whenever the alter-wrapper is applied to a table (any table >= the supplied size value), the alter-algorithm and alter-lock options are both ignored automatically. This prevents sending an ALTER statement containing ALGORITHM or LOCK clauses to an external OSC tool. This permits a configuration that uses built-in online DDL for small tables, and an external OSC tool for larger tables.

If this option is supplied along with both alter-wrapper and ddl-wrapper, ALTERs on tables below the specified size will still have ddl-wrapper applied. This configuration is not recommended due to its complexity.

brief

Commandsdiff
Defaultfalse
Typeboolean
RestrictionsShould only appear on command-line

Ordinarily, skeema diff outputs DDL statements to STDOUT. With brief, skeema diff will instead only output a newline-delimited list of unique instances (host:port) that had at least one difference. This can be useful in any environment with multiple database hosts, to see which instances/shards have “drifted” or are not up-to-date with the latest schema changes.

Since the purpose of the brief option is to see which instances contain schema differences, enabling this option always automatically enables the allow-unsafe option, and disables the verify, lint, and inserts options. It also automatically hides all INFO-level logging, unless debug is enabled.

The naming of this option was chosen to resemble UNIX’s diff --brief, which outputs names of differing files instead of full line-by-line differences. It can be invoked with the shortcut option -q as per GNU diff and BSD diff.

compare-metadata

Commandsdiff, push
Defaultfalse
Typeboolean
Restrictionsnone

In MySQL and MariaDB, whenever a procedure, function, or trigger is created, two pieces of environmental metadata are automatically stored and associated with the object: the session sql_mode in effect at the time, and the default collation of the database containing the object. This metadata affects execution of the stored program in subtle ways, and even if the global sql_mode or database default collation changes at a later time, existing objects are not updated automatically by the database server itself. The compare-metadata option controls whether or not Skeema should include this metadata in its diff logic.

By default, skeema diff and skeema push ignore this creation-time metadata when comparing objects, because it exists outside of the SQL CREATE statement entirely.

Enabling the compare-metadata option will cause skeema diff and skeema push to include two extra comparisons for relevant object types:

  • Compare the object’s original creation-time sql_mode to the current global sql_mode
  • Compare the object’s original creation-time db_collation to its database’s current default collation

If any differences are found in those comparisons, the generated SQL DDL will include statements to drop and recreate the object. This output can be somewhat counter-intuitive, however, since the relevant change is outside of the SQL statement itself.

In the Community edition of Skeema, triggers are not supported, so this option only impacts procedures and functions.

concurrent-instances

Commandsdiff, push
Default1
Typeint
RestrictionsMust be a positive integer

By default, skeema diff and skeema push only operate on one database server instance (mysqld process) at a time. To operate on multiple instances simultaneously, set concurrent-instances to the number of database instances to run on concurrently. This is useful in an environment with multiple shards or clusters.

On each individual database instance, only one DDL operation will be run at a time by skeema push, regardless of concurrent-instances. Concurrency within an instance may be configurable in a future version of Skeema.

connect-options

Commandsall
Defaultempty string (see below)
Typestring
Restrictionsnone

This option stores a comma-separated list of session variables to set upon connecting to the database. For example, a value of wait_timeout=86400,innodb_lock_wait_timeout=1,lock_wait_timeout=60 would set these three MySQL variables, at the session level, for connections made by Skeema.

Any string-valued variables must have their values wrapped in single-quotes. Take extra care to nest or escape quotes properly in your shell if supplying connect-options on the command-line. For example, --connect-options="lock_wait_timeout=60,sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES'"

The following MySQL session variables cannot be set by this option, since it would interfere with Skeema’s internal operations:

  • autocommit – cannot be disabled in Skeema
  • foreign_key_checks – see Skeema’s own foreign-key-checks option to manipulate this
  • default_storage_engine – always set to InnoDB for Skeema’s sessions

Aside from the above list, any legal MySQL session variable may be set. Note that some session variables require special privileges, depending on your server version. For example, in MySQL 8.0.26+, setting innodb_strict_mode requires either SUPER, SESSION_VARIABLES_ADMIN, or SYSTEM_VARIABLES_ADMIN.

This option only affects connections made directly by Skeema. If you are using an external tool via alter-wrapper or ddl-wrapper, you will also need to configure that tool to set options appropriately. Skeema’s {CONNOPTS} variable can help avoid redundancy here; for example, if configuring pt-online-schema-change, you could include --set-vars {CONNOPTS} on its command-line to pass the same configured options dynamically.

Prior to v1.5, Skeema automatically defaulted to using a strict sql_mode and innodb_strict_mode for its sessions. This is no longer the case, and these variables now work like any other: the server’s global values are used by default, but may be overridden using connect-options if desired.

In addition to setting MySQL session variables, you may also set any of these special variables which affect client-side behavior at the internal driver/protocol level:

  • charset=string – Character set used for client-server interaction
  • collation=string – Collation used for client-server interaction
  • maxAllowedPacket=int – Max allowed packet size, in bytes
  • allowCleartextPasswords=bool – Allow use of cleartext auth methods such as auth_pam_compat or AWSAuthenticationPlugin; default false
  • rejectReadOnly=bool – Reconnect upon a DBaaS returning read-only errors; default false
  • readTimeout=duration – Query timeout; the value must be a float with a unit suffix (“ms” or “s”); default 20s
  • timeout=duration – Connection timeout; the value must be a float with a unit suffix (“ms” or “s”); default 5s
  • writeTimeout=duration – Socket write timeout; the value must be a float with a unit suffix (“ms” or “s”); default 5s

All of these special driver variables are case-sensitive. Unlike session variables, their values should never be wrapped in quotes. These special non-MySQL variables are automatically stripped from {CONNOPTS}, so they won’t be passed through to tools that don’t understand them.

The value of readTimeout applies to all queries made directly by Skeema, except for ALTER TABLE, DROP TABLE, and INSERT statements, which are all exempted from query timeouts entirely.

If an SSH tunnel is being used (ssh or ssh-to-db options), readTimeout and writeTimeout have no effect, and no query timeout or socket write timeout is applied.

ddl-wrapper

Commandsdiff, push
Defaultempty string
Typestring
Restrictionsnone

This option works exactly like alter-wrapper, except that it applies to all DDL statements regardless of operation type (ALTER, DROP, CREATE) or object class (TABLE, DATABASE, etc) – not just ALTER TABLE statements. This is intended for use in situations where all DDL statements need to be sent through a common script or system for execution.

For each DDL statement in the diff, rather than executing the DDL directly, skeema push will instead shell out to the external process. Meanwhile the output of skeema diff will display what command-line would be executed, but it won’t actually be run.

If both of alter-wrapper and ddl-wrapper are set, then alter-wrapper will be applied to ALTER TABLE statements, and ddl-wrapper will be applied to all other DDL.

If only ddl-wrapper is set, then it will be applied to all DDL.

For even more fine-grained control, such as different behavior for CREATE vs DROP, set ddl-wrapper to a custom script which performs a different action based on {TYPE} and/or {CLASS}.

Use of an absolute path is recommended. Relative paths may not work as expected.

This command supports use of special variables. Skeema will dynamically replace these with an appropriate value when building the final command-line. See options with variable interpolation for more information. The following variables are supported by ddl-wrapper:

  • {HOST} – hostname (or IP) that this DDL statement targets
  • {PORT} – port number for the host that this DDL statement targets
  • {SCHEMA} – default database name (schema name) that the DDL statement should be executed in. Blank if {CLASS} is DATABASE.
  • {USER} – MySQL username defined by the user option either via command-line or option file
  • {PASSWORD} – MySQL password defined by the password option either via command-line or option file
  • {PASSWORDX} – Behaves like {PASSWORD} when the command-line is executed, but only displays X’s whenever the command-line is displayed on STDOUT
  • {ENVIRONMENT} – environment name from the first positional arg on Skeema’s command-line, or “production” if none specified
  • {DDL} – Full DDL statement, including all clauses
  • {NAME} – object name that this DDL statement targets
  • {TABLE} – if the object is a table, identical to {NAME}; blank for non-tables
  • {SIZE} – size of table that this DDL statement targets, in bytes. For tables with no rows, this will be 0, regardless of actual size of the empty table on disk. It will also be 0 for CREATE TABLE statements. It will be 0 if {CLASS} isn’t TABLE.
  • {CLAUSES} – Body of the DDL statement, i.e. everything after ALTER TABLE <name> or CREATE TABLE <name> . This is blank for DROP TABLE statements, and blank if {CLASS} isn’t TABLE.
  • {TYPE} – the operation type: the word “CREATE”, “DROP”, or “ALTER” in all caps.
  • {CLASS} – the object class: the word “TABLE”, “DATABASE”, “PROCEDURE”, “FUNCTION”, “VIEW”, or “TRIGGER” in all caps. Additional object classes (e.g. “EVENT”) may be supported in the future.
  • {CONNOPTS} – Session variables passed through from the connect-options option
  • {DIRNAME} – The base name (last path element) of the directory being processed.
  • {DIRPATH} – The full (absolute) path of the directory being processed.

If an ssh or ssh-to-db SSH tunnel is being used, the wrapper script is executed on the remote SSH host, instead of locally. In this situation, be sure to configure ddl-wrapper as a valid absolute path on the remote host. This functionality can be used to solve problems of security (always running wrappers from a single audited location), locking (preventing concurrent execution), uniformity (ensuring the same version of pt-osc or gh-ost), and portability (removing the need to get pt-osc working on Windows or MacOS for example). Refer to the documentation for the ssh option for more information, as well as guidance regarding use of terminal multiplexers such as screen or tmux.

If the ddl-wrapper option is used, the automatic locking behavior for operations affecting multiple triggers is skipped. Table locks are per-session, and Skeema cannot share a session with the external wrapper tool. If you typically need to modify existing triggers in MySQL (which lacks atomic CREATE OR REPLACE syntax), or perform operations creating/modifying multiple triggers in a coordinated fashion, your wrapper/systems must account for this in a custom way.

This option does not affect execution of non-DDL statements, such as DML from the inserts option.

debug

Commandsall
Defaultfalse
Typeboolean
RestrictionsShould only appear on command-line or in a global option file

This option enables debug logging in all commands. The extra output is sent to STDERR and includes the following:

  • When skeema diff or skeema push encounters tables that cannot be ALTERed due to use of features not yet supported by Skeema, the debug log will indicate which specific line(s) of the CREATE TABLE statement are using such features.
  • If a panic occurs in Skeema’s main thread, a full stack trace will be logged.
  • Options that control conditional logic based on table sizes, such as safe-below-size and alter-wrapper-min-size, provide debug output with size information whenever their condition is triggered.
  • Upon exiting, the numeric exit code will be logged.

default-character-set

Commandsall
Defaultempty string
Typestring
RestrictionsShould only appear in a .skeema option file that also contains schema

This option specifies the default character set to use for a particular schema. In .skeema files, it is populated automatically by skeema init and updated automatically by skeema pull.

If a new schema is being created for the first time via skeema push, and default-character-set has been set, it will be included as part of the CREATE DATABASE statement. If it has not been set, the instance’s default server-level character set is used instead.

If a schema already exists when skeema diff or skeema push is run, and default-character-set has been set, and its value differs from what the schema currently uses on the instance, an appropriate ALTER DATABASE statement will be generated.

default-collation

Commandsall
Defaultempty string
Typestring
RestrictionsShould only appear in a .skeema option file that also contains schema

This option specifies the default collation to use for a particular schema. In .skeema files, it is populated automatically by skeema init and updated automatically by skeema pull.

If a new schema is being created for the first time via skeema push, and default-collation has been set, it will be included as part of the CREATE DATABASE statement. If it has not been set, the instance’s default server-level collation is used instead.

If a schema already exists when skeema diff or skeema push is run, and default-collation has been set, and its value differs from what the schema currently uses on the instance, an appropriate ALTER DATABASE statement will be generated.

dir

Commandsinit, add-environment
Defaultsee below
Typestring
Restrictionsnone

For skeema init, specifies what directory to populate with table files (or, if multiple schemas present, schema subdirectories that then contain the table files). If unspecified, the default dir for skeema init is based on the hostname (and port, if non-3306). Either a relative or absolute path may be supplied. The directory will be created if it does not already exist. If it does already exist, it must not already contain a .skeema option file.

For skeema add-environment, specifies which directory’s .skeema file to add the environment to. The directory must already exist (having been created by a prior call to skeema init), and must already contain a .skeema file, but the new environment name must not already be defined in that file. If unspecified, the default dir for skeema add-environment is the current directory, “.”.

Please note that all other Skeema commands intentionally do not support the dir option. Most commands operate recursively from the current working directory, meaning you should generally cd to the desired location before invoking other commands.

docker-cleanup

Commandsdiff, push, pull, lint, format
Default“none”
Typeenum
RestrictionsRequires one of these values: “none”, “stop”, “destroy”

When using workspace=docker, the docker-cleanup option controls cleanup behavior of dynamically-managed Docker containers right before Skeema exits.

With the default value of “none”, no cleanup occurs, meaning that any dynamically-managed container(s) are left in the running state. This allows subsequent Skeema invocations to perform well, since no time is wasted recreating or restarting local database containers. However, the running containers may consume some resources on your local machine.

With a value of “stop”, containers are stopped, but not destroyed completely. Subsequent invocations of Skeema will need to restart the containers, which can take a few seconds, but is still faster than completely recreating the containers from scratch. The stopped containers won’t consume CPU or memory on your local machine, but they may consume disk space.

With a value of “destroy”, containers (and their volumes) are deleted upon Skeema shutdown. Each invocation of Skeema will need to recreate the containers, which can take 10-20 seconds. This option avoids most resource consumption on your local machine, aside from image storage.

When using Skeema v1.11.1+ on a Linux system, setting this option to “destroy” also automatically enables use of an in-memory tmpfs volume for the containerized database’s data directory. This improves performance by preventing disk writes.

Regardless of the option used here, Skeema does not automatically delete or un-tag images. You may need to periodically perform prune operations in Docker itself to completely avoid any storage impact.

dry-run

Commandspush
Defaultfalse
Typeboolean
RestrictionsShould only appear on command-line

Running skeema push --dry-run is exactly equivalent to running skeema diff: the DDL will be generated and printed, but not executed. The same code path is used in both cases. The only difference is that skeema diff has its own help/usage text, but otherwise the command logic is the same as skeema push --dry-run.

exact-match

Commandsdiff, push, Cloud Linter
Defaultfalse
Typeboolean
Restrictionsnone

Ordinarily, skeema diff and skeema push ignore certain table differences which have no functional impact in MySQL and serve purely cosmetic purposes. Currently this includes:

  • If a table’s *.sql file lists its indexes in a different order than the live MySQL table, this difference is normally ignored to avoid needlessly dropping and re-adding the indexes, which may be slow if the table is large.
  • If a table’s *.sql file has foreign keys with the same definition, but different name, this difference is normally ignored to avoid needlessly dropping and re-adding the foreign keys. This provides better compatibility with external tools like pt-online-schema-change, which need to manipulate foreign key names in order to function.
  • Foreign key action clauses RESTRICT and NO ACTION have the exact same effect, but are represented differently in information_schema and SHOW CREATE TABLE; attempts to change between these two clauses are ignored beginning in Skeema v1.6.0. This prevents unnecessary diffs that drop and re-add foreign keys after an in-place upgrade to MySQL 8.0, which swapped the default clause from RESTRICT to NO ACTION.

If the exact-match option is used, these purely-cosmetic differences will be included in the generated ALTER TABLE statements instead of being suppressed. In other words, Skeema will attempt to make the exact table definition in MySQL exactly match the corresponding table definition specified in the *.sql file.

Be aware that MySQL itself sometimes also suppresses attempts to make cosmetic changes to a table’s definition! For example, MySQL may ignore attempts to cosmetically re-order indexes unless the table is forcibly rebuilt. You can combine the exact-match option with alter-algorithm=copy to circumvent this behavior on the MySQL side, but it may be slow for large tables.

Please note that in the one case in InnoDB when index ordering has a functional impact (tables with no primary key, but multiple unique indexes over all non-nullable columns), Skeema will automatically respect index ordering, regardless of whether exact-match is enabled.

first-only

Commandsdiff, push
Defaultfalse
Typeboolean
Restrictionsnone

Ordinarily, for individual directories that map to multiple instances and/or multiple schemas, skeema diff and skeema push will operate on all mapped instances, and all mapped schemas on those instances. If the first-only option is used, these commands instead only operate on the first instance and schema per directory.

In a sharded environment, this option can be useful to examine or execute a change only on one shard, before pushing it out on all shards. Alternatively, for more complex control, a similar effect can be achieved by using environment names. For example, you could create an environment called “production-canary” with host configured to map to a subset of the instances in the “production” environment.

flavor

Commandsall, as well as Cloud Linter
Defaultempty string
Typestring
RestrictionsShould only appear in a .skeema option file that also contains host

This option indicates the database server vendor and version corresponding to the first host defined in this directory. The value is typically formatted as “vendor:major.minor”, for example “mysql:5.6”, “percona:5.7”, or “mariadb:10.1”. A patch number may optionally be included as well, for example “mysql:8.0.19”.

This option is automatically populated in host-level .skeema files by skeema init, skeema pull, and skeema add-environment beginning in Skeema v1.0.3.

The notion of a database flavor affects various aspects of schema introspection and DDL generation in skeema diff and skeema push. Ordinarily, Skeema auto-detects the flavor of each database server it interacts with, and the value of flavor in a .skeema file has no real effect. However, there are a few special cases where the option value is used directly:

  • In some environments, Skeema may display a warning that it cannot automatically parse the database server’s actual vendor and/or version properly. In this situation, you can manually configure the correct flavor in a .skeema file, and Skeema will use the configured value.

  • With workspace=docker, the flavor value controls what Docker image is used for workspace containers. If no flavor is specified, an error is generated.

  • In Skeema Cloud Linter, the flavor value controls what database vendor and version is used for purposes of linting this directory. If no flavor is specified, Cloud Linter currently defaults to mysql:5.7.

Note that the database server’s actual auto-detected vendor and version take precedence over the flavor option in all other cases not listed above.

In some very specific situations, it can be useful to override flavor on the command-line. For example, if you currently run MySQL 5.7 but want to lint and test your schema definitions against an ephemeral MySQL 8.0.26 container in preparation of an upgrade, you could run skeema lint --flavor=mysql:8.0.26 --workspace=docker --docker-cleanup=destroy. This technique is not recommended for other commands aside from skeema lint though.

When the database server is hosted on AWS Aurora, the Community edition of the CLI will use a “mysql” flavor. However, due to some internal incompatibilities, the Community edition cannot diff certain tables on some versions of Aurora. Manually specifying an “aurora” flavor does not work properly in the Community edition.

The Premium edition of the CLI adds full support for AWS Aurora, detected automatically; it will use a flavor of “aurora:5.6”, “aurora:5.7”, or “aurora:8.0” as appropriate. In specific cases of Docker interactions, Premium products will internally map “aurora” flavors to the corresponding version of a standard MySQL container image from DockerHub. For example, with workspace=docker or in Skeema Cloud Linter, a “mysql:5.6” image is used if the flavor is “aurora:5.6”. However, the full Aurora table diff support will still work properly in this situation.

foreign-key-checks

Commandspush
Defaultfalse
Typeboolean
Restrictionsnone

By default, skeema push executes DDL in a session with foreign key checks disabled. This way, when adding a new foreign key 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.

This behavior may be overridden by enabling the foreign-key-checks option. When enabled, skeema push enables foreign key checks for any ALTER TABLE that adds one or more foreign keys to an existing table. This means the server will validate existing data’s referential integrity for new foreign keys, and the ALTER TABLE will fail with a fatal error if the constraint is not met for all rows.

This option does not affect Skeema’s behavior for other DDL, including CREATE TABLE or DROP TABLE; nor does it affect DML, such as INSERT statements from the inserts option. These statements are always executed in a session with foreign key checks disabled, to avoid any potential issues with thorny order-of-operations or circular references.

This option has no effect in cases where an external OSC tool is being used via alter-wrapper or ddl-wrapper.

format

Commandspull, lint
Defaulttrue
Typeboolean
Restrictionsnone

If true, skeema pull and skeema lint will normalize the format of all CREATE statements in *.sql files to match the canonical format shown in MySQL’s SHOW CREATE, just like if skeema format was called as part of the operation. If false, this step is skipped.

This option is enabled by default. To disable reformatting in skeema pull and skeema lint, use --skip-format on the command-line or skip-format in an option file.

When running skeema pull --skip-format, a workspace will be used to internally run diff operation between the filesystem and the database. CREATE statements in *.sql files will be rewritten only if the live database definition has structural (non-cosmetic) differences. This has several implications:

  • skeema pull takes longer to execute when using --skip-format, especially for directories containing hundreds of CREATE statements.
  • The update-partitioning option may not work properly; some tables that only have partitioning-related changes will not be updated when combining these options.
  • In premium Skeema products, any views with structural changes will be updated in the filesystem, even if the update-views option is not enabled. This causes loss of formatting and inline comments in the filesystem CREATE VIEW statement.
  • Tables with differences only in implicit clauses, such as a secondary index automatically created on the child side of a foreign key constraint, will not be updated. More information in issue #149.

generator

Commandsall
DefaultN/A
Typestring
Restrictionsnone

Beginning with Skeema v1.5, skeema init automatically records the Skeema version and edition that generated the directory structure, and skeema pull updates it as needed.

The generator value helps ensure backwards compatibility when a user upgrades to a new version of the Skeema CLI, or switches between the open source Community edition and the paid Premium edition.

For example, views and triggers are only supported in our Premium products. A user might originally run skeema init with the Community edition of the CLI, but then switch to the Premium edition CLI at some later date. If the user doesn’t ever run skeema pull, any views and triggers in their database won’t be present in the schema repo yet. In this case, skeema diff and skeema push need to differentiate between these two situations:

  • views and triggers are missing from *.sql because the dump was generated by the Community edition
  • views and triggers are missing from *.sql because the user intentionally wants to drop them

The generator value in .skeema files allows these commands to make the correct decision automatically.

Users should not modify this value in .skeema files manually, nor should it be supplied on the command-line. Doing so may result in undefined or incorrect behavior.

host

Commandsall
DefaultN/A
Typestring
Restrictionssee limitations on placement

Specifies the hostname, IP address, or lookup key to connect to when processing this directory or its subdirectories. A port number may optionally be included using hostname:port syntax in host instead of using the separate port option. IPv6 addresses must be wrapped in brackets; if also including a port, use format [ipv6:address:here]:port.

If host is “localhost”, and no port is specified (inline or via the port option), the connection will use a UNIX domain socket instead of TCP/IP. See the socket option to specify the socket file path. This behavior is consistent with how the standard mysql client operates. If you wish to connect to localhost using TCP/IP, supply host by IP (“127.0.0.1”).

For simple sharded environments with a small number of shards, you may optionally specify multiple addresses in a single host value by using a comma-separated list. In this situation, skeema diff and skeema push operate on all listed hosts, unless their first-only option is used. skeema pull always just operates on the first host as its source of truth.

Skeema can optionally integrate with service discovery systems via the host-wrapper option. In this situation, the purpose of host changes: instead of specifying a hostname or address, host is used for specifying a lookup key, which the service discovery system maps to one or more addresses. The lookup key may be inserted in the host-wrapper external command-line via the {HOST} placeholder variable. In this configuration host should be just a single value, never a comma-separated list; in a sharded environment it is the service discovery system’s responsibility to map a single lookup key to multiple addresses when appropriate. If all of your hosts are in the same group of shards and you have no need for a lookup key, you should still set host to a placeholder/dummy value in order to indicate that host-wrapper should be applied to a given directory.

In all cases, the specified host(s) should always be writable (master) instances, never replicas.

In option files, you may supply the value as an environment variable beginning with Skeema v1.9.0. The environment variable value may be a single host, or a comma-separated list of multiple sharded hosts. If the specified environment variable value is not actually set, the directory will be skipped by skeema diff and skeema push, with a warning message logged.

host-wrapper

Commandsall
Defaultempty string
Typestring
Restrictionsnone

This option controls how the host option is interpreted, and can be used to allow Skeema to interface with service discovery systems.

By default, host-wrapper is blank, and host values are interpreted literally as domain names or addresses (no service discovery). To configure Skeema to use service discovery instead, set host-wrapper to an external command-line to execute. Then, whenever Skeema needs to perform an operation on one or more database instances, it will execute the external command to determine which instances to operate on, instead of using host as a literal value.

The command line may contain special placeholder variables, which Skeema will dynamically replace with appropriate values. See options with variable interpolation for more information. The following variables are supported for this option:

  • {HOST} – the value of the host option, to use as a lookup key
  • {ENVIRONMENT} – environment name from the first positional arg on Skeema’s command-line, or “production” if none specified
  • {DIRNAME} – The base name (last path element) of the directory being processed.
  • {DIRPATH} – The full (absolute) path of the directory being processed.
  • {SCHEMA} – the value of the schema option for the directory being processed

Above, “the directory being processed” refers to a leaf directory defining the schema option and containing *.sql files.

The command’s STDOUT will be split on a consistent delimiter (newline, tab, comma, or space), and each token will be treated as an address. Here, “address” means any of the following formats:

  • hostname
  • hostname:port
  • ipv4
  • ipv4:port
  • [ipv6]
  • [ipv6]:port

If ports are omitted, the port option is used instead, which defaults to MySQL’s standard port 3306.

The external command should only return addresses of writable (master) instances, never replicas.

The host-wrapper option is designed to be specified generically at a high level directory, such as a .skeema file at the repository root, or perhaps a global option file. This way, you may specify a single generic service discovery command-line usable across your infrastructure, rather than redundantly configuring a command-line for each database cluster.

Setting or overriding host-wrapper in a subdirectory does not inherently cause the wrapper to be invoked upon processing that subdirectory; host-level subdirectories must also still specify some value for the host option in order to be processed. If your host-wrapper command-line does not make use of the {HOST} variable, then just use a static value such as host=1 in directories where the host-wrapper script should be invoked.

ignore-func

Commandsall
Defaultempty string
Typeregular expression
Restrictionsnone

If you wish to have Skeema ignore some or all functions, this option allows configuration of a regular expression which is matched against function names to ignore.

Note that functions and stored procedures have separate namespaces in the database, meaning that a function and a stored procedure may have the same name. For this reason, ignore-func only applies to functions. To ignore stored procedures, see ignore-proc.

The value of this option must be a valid regex, and should not be wrapped in delimiters. See the option types documentation for an example, and information on how to do case-insensitive matching.

To force Skeema to completely ignore all functions, configure ignore-func=., as this regex will match any function name.

When supplied on the command-line to skeema init, the value will be persisted into the auto-generated .skeema option file, so that subsequent commands continue to ignore the corresponding function names.

This option was first introduced in the Premium Edition in v1.6.0, and also became available in the Community Edition beginning with v1.9.0.

ignore-proc

Commandsall
Defaultempty string
Typeregular expression
Restrictionsnone

If you wish to have Skeema ignore some or all stored procedures, this option allows configuration of a regular expression which is matched against procedure names to ignore.

Note that stored procedures and functions have separate namespaces in the database, meaning that a function and a stored procedure may have the same name. For this reason, ignore-proc only applies to stored procedures. To ignore functions, see ignore-func.

The value of this option must be a valid regex, and should not be wrapped in delimiters. See the option types documentation for an example, and information on how to do case-insensitive matching.

To force Skeema to completely ignore all stored procedures, configure ignore-proc=., as this regex will match any procedure name.

When supplied on the command-line to skeema init, the value will be persisted into the auto-generated .skeema option file, so that subsequent commands continue to ignore the corresponding procedure names.

This option was first introduced in the Premium Edition in v1.6.0, and also became available in the Community Edition beginning with v1.9.0.

ignore-schema

Commandsinit, pull, diff, push
Defaultempty string
Typeregular expression
Restrictionsnone

Ordinarily, Skeema only ignores system schemas: information_schema, performance_schema, sys, test, mysql. The ignore-schema option allows you to specify a regular expression of additional schema names to ignore. (The system schemas are always ignored regardless.)

The value of this option must be a valid regex, and should not be wrapped in delimiters. See the option types documentation for an example, and information on how to do case-insensitive matching.

The intended purpose of this option is to ignore “scratch” databases and other things that you do not wish to track in version control at all. If instead you wish to run skeema init against just a single database schema, use the --schema option on the command line instead. For other commands, to interact with just a single schema at a time, simply cd to that schema’s subdirectory before invoking skeema; see the FAQ entry for more information.

When --ignore-schema is supplied on the command-line to skeema init, the value will be persisted into the auto-generated .skeema option file, so that subsequent commands continue to ignore the corresponding schema names.

Once configured, this option affects skeema pull, skeema diff, and skeema push, effectively acting as a filter against the schema option. The documentation for the schema option describes some potential sharding use-cases.

Note that this option does not affect skeema lint or skeema format, as these commands operate on the filesystem representation without needing to interact with the schema option.

ignore-table

Commandsall
Defaultempty string
Typeregular expression
Restrictionsnone

If you wish to have Skeema ignore some tables, this option allows configuration of a regular expression which is matched against table names to ignore.

The value of this option must be a valid regex, and should not be wrapped in delimiters. See the option types documentation for an example, and information on how to do case-insensitive matching.

For example, external tools such as pt-online-schema-change and gh-ost temporarily use shadow tables, which Skeema should not interact with directly. To ignore tables whose names end in “_new” (the default naming pattern for pt-online-schema-change’s shadow tables), use ignore-table=_new$.

To force Skeema to completely ignore all tables, configure ignore-table=., as this regex will match any table name. This could be useful in a .skeema file if you’re only ever using Skeema to deploy stored procedures and functions, for example. Alternatively, you can use it manually on the command-line to filter out object types into separate diff/push operations.

When supplied on the command-line to skeema init, the value will be persisted into the auto-generated .skeema option file, so that subsequent commands continue to ignore the corresponding table names.

In premium Skeema products, this option has additional implications for views and triggers:

  • Since views share a namespace with tables, this option also affects views. Any views with a name matching the ignore-table regex will be ignored. (To only ignore views and not tables, see the ignore-view option.)
  • If an ignored table has any associated triggers, those triggers will be ignored automatically as well, regardless of their name. (Aside from this behavior, this option does not affect triggers; see the separate ignore-trigger option to ignore match based on trigger name.)

The Community edition of Skeema does not support views or triggers, and always completely ignores them regardless of this option.

ignore-trigger

Commandsall
Defaultempty string
Typeregular expression
RestrictionsNot available in the Community edition of Skeema

Premium Skeema products add support for managing triggers. If you wish to have Skeema ignore some or all triggers, this option allows configuration of a regular expression which is matched against trigger names to ignore.

For example, pt-online-schema-change automatically creates and uses triggers beginning with the prefix “pt_osc_”. If you wish to configure Skeema to manage all triggers except those used by pt-online-schema-change, configure ignore-trigger=^pt_osc_.

To force premium Skeema products to completely ignore all triggers, configure ignore-trigger=., as this regex will match any trigger name.

The value of this option must be a valid regex, and should not be wrapped in delimiters. See the option types documentation for an example, and information on how to do case-insensitive matching.

When supplied on the command-line to skeema init, the value will be persisted into the auto-generated .skeema option file, so that subsequent commands continue to ignore the corresponding trigger names.

If a table is ignored due to ignore-table, any triggers on the table will also be ignored automatically, regardless of their name or the ignore-trigger option.

This option is not available in the Community edition of Skeema, which does not support triggers. All triggers are always ignored by the Community edition of Skeema.

ignore-view

Commandsall
Defaultempty string
Typeregular expression
RestrictionsNot available in the Community edition of Skeema

Premium Skeema products add support for managing views. If you wish to have Skeema ignore some or all views, this option allows configuration of a regular expression which is matched against view names to ignore.

Regardless of whether this option is used, any ignore-table regex will also be applied against views, since views share a namespace with tables. However, the opposite is not true: ignore-view only applies to views, in order to provide a view-specific mechanism. For example, to force premium Skeema products to completely ignore all views, configure ignore-view=., as this regex will match any view name.

The value of this option must be a valid regex, and should not be wrapped in delimiters. See the option types documentation for an example, and information on how to do case-insensitive matching.

When supplied on the command-line to skeema init, the value will be persisted into the auto-generated .skeema option file, so that subsequent commands continue to ignore the corresponding view names.

As of Skeema v1.10.0-premium, views with names beginning with “_seed_” are automatically ignored in most situations, regardless of the value of ignore-view. See update-seed-inserts for information regarding seed views.

This option is not available in the Community edition of Skeema, which does not support views. All views are always ignored by the Community edition of Skeema.

include-auto-inc

Commandsinit, pull
Defaultfalse
Typeboolean
Restrictionsnone

Determines whether or not table definitions should contain next-auto-increment values. Defaults to false, meaning that ordinarily next-auto-increment values are omitted from *.sql files, since they usually aren’t useful to track in version control.

In skeema init, a false value omits AUTO_INCREMENT=X clauses in all table definitions, whereas a true value includes them based on whatever value is currently present on the table (typically its highest already-generated ID, plus one).

In skeema pull, a false value omits AUTO_INCREMENT=X clauses in any newly-written table files (tables that were created outside of Skeema, which are now getting a *.sql file written for the first time). Modified tables that already had AUTO_INCREMENT=X clauses, where X > 1, will have their AUTO_INCREMENT values updated; otherwise the clause will continue to be omitted in any file that previously omitted it. Meanwhile a true value causes all table files to now have AUTO_INCREMENT=X clauses.

Only set this to true if you intentionally need to track auto_increment values in all tables. If only a few tables require nonstandard auto_increment, simply include the value manually in the CREATE TABLE statement in the *.sql file. Subsequent calls to skeema pull won’t strip it, even if include-auto-inc is false.

inserts

Commandsdiff, push
Default“seed”
Typeenum
RestrictionsRequires one of these values: “none”, “seed”, “all”; Not available in the Community edition of Skeema

Although Skeema primarily operates on CREATE statements, Premium CLI v1.10 also introduced support for using INSERT statements to manage seed data. The inserts option controls how skeema diff and skeema push process INSERT statements found among your *.sql files.

With the default of inserts=seed, INSERT statements are processed when their target table has no rows. This provides a mechanism for representing “seed data” in your *.sql files: initial data for a new table, and/or development data for use when populating a new dev/test/CI database.

With inserts=all, all INSERT statements are processed on every skeema diff or skeema push invocation, regardless of table emptiness. It is important to understand that Skeema does not diff your table row data or support imperative patching of table data / generic data migrations and this option simply re-runs your full INSERTs each time. When using inserts=all, you must take caution to avoid duplicate key errors in your INSERT statements, such as by manually ensuring your statements all use syntax INSERT IGNORE or INSERT … ON DUPLICATE KEY UPDATE as necessary.

With inserts=none, no INSERT statements are processed. This is effectively how the Community Edition operates. The alias skip-inserts may also be used equivalently.

As with all other operations, skeema diff only ever displays INSERTs (as appropriate based on the value of the inserts option) but does not execute them, whereas skeema push executes them, always after any DDL for the corresponding table. When skeema push executes INSERTs, each statement is run in its own separate transaction. If INSERTs are present for multiple tables, their execution ordering is random. The session will use foreign_key_checks=0, avoiding foreign key dependency chain issues, at the cost of not ensuring referential integrity of the seed data.

INSERT statements may be placed in any *.sql files within the same directory as the definition of their target tables. You may place them in the same file as their corresponding CREATE TABLE, or in a separate file per INSERT, or place all INSERTs into a single file. There are several approaches for creating and managing INSERT statements in your *.sql files:

  • Manually: write INSERT statements from scratch, and adjust them manually when needed.

  • Skeema seed views: skeema pull’s update-seed-inserts option provides an automated mechanism for using specially-named views to dump seed data from the appropriate environment.

  • External dump tools: for example, a custom wrapper around the output of mysqldump --no-create-info --where='...' --tables ... could be used in an automation pipeline, in order to obtain the desired INSERTs and write them to a consistent location in your schema repo.

If maintaining INSERT statements manually, you can split seed data for a single table into multiple separate INSERT statements if you wish. If a single table has multiple INSERT statements defined in the same .sql file, their execution ordering will match their order in the file. With inserts=seed, if a single table has multiple INSERT statements defined in the directory, the table-emptiness check is run only once prior to execution of any INSERT statements.

The ddl-wrapper option does not affect INSERTs. skeema push always executes INSERTs directly, not through a wrapper script. Query timeouts (readTimeout in connect-options) are always waived for INSERTs.

Neither skeema diff nor skeema push pre-verifies INSERT statements for correctness prior to execution. INSERTs are not run inside of a workspace.

Presence of INSERTs does not affect the exit code behavior of skeema diff.

Use of skeema diff --brief ignores INSERT statements, effectively forcing inserts=none in its behavior.

lax-column-order

Commandsdiff, push, Cloud Linter
Defaultfalse
Typeboolean
Restrictionsnone

Ordinarily, when diffing a table, skeema diff and skeema push take column ordering differences into account. If column ordering differs between the live database and the *.sql definition, by default Skeema will emit ALTER TABLE ... MODIFY COLUMN ... statements with FIRST / AFTER ... clauses to bring the column ordering into alignment. If you wish to prevent this behavior, enable the lax-column-order option.

With lax-column-order enabled, MODIFY COLUMN clauses are suppressed whenever the only difference in a column is its ordering within the table. This option is intended for use in situations where some environments have extensively different column ordering, caused by historical reasons or technical debt.

This option does not affect MODIFY COLUMN clauses when the diff contains additional changes to the column besides just the ordering. In this situation, the entire MODIFY COLUMN clause (including the FIRST or AFTER part) is still generated as-is. In other words, even with lax-column-order enabled, columns will still be re-ordered if some other aspect of the column is also being modified at the same time.

This option does not affect ADD COLUMN clauses. Any FIRST or AFTER portion of an ADD COLUMN clause will still be present. In other words, ordering for new columns is still respected.

lax-comments

Commandsdiff, push, Cloud Linter
Defaultfalse
Typeboolean
Restrictionsnone

Ordinarily, when diffing a table or routine, skeema diff and skeema push take COMMENT clause differences into account, emitting DDL to reconcile the difference accordingly. If you wish to prevent this behavior, enable the lax-comments option. This option is intended for use in situations where some environments have slightly different COMMENT clauses, caused by historical schema drift or technical debt.

When enabled, comment-only differences are suppressed as follows:

  • Column-level COMMENT clause differences are ignored if there are no other differences in the column definition.
  • Index-level COMMENT clause differences are ignored if there are no other changes to the index definition, or if there is only a change in visibility (INVISIBLE / IGNORED).
  • Table-level COMMENT clause differences are ignored if there are no other ALTER clauses being generated for the table.
  • Routine-level COMMENT clause differences are ignored if there are no other changes in the routine definition.

This option does not affect creation of new tables, columns, indexes, or routines. Any COMMENT clauses present for a new object will still be used as-is per the definition in the .sql file.

lint

Commandsdiff, push
Defaulttrue
Typeboolean
Restrictionsnone

If true, skeema diff and skeema push will perform linter checks on all modified objects in the diff. Any triggered check set to “warning” level will be displayed, but will not otherwise affect the diff or push operation, nor will it affect the process’s exit code. Any triggered check set to “error” level will prevent the operation from proceeding for the current instance/schema pair.

Objects that were not modified in the diff – i.e. anything where the filesystem definition already functionally matches the database definition – are not linted during skeema diff or skeema push. To check all objects, use skeema lint instead.

Users with sharded environments should note that the definition of “modified in the diff” may vary per shard, if the shards are not all in the same state. Skeema computes the diff for each shard and lints the modified objects for each shard individually. Linter warning and error output will be generated (potentially redundantly) for every shard. In the rare situation that only some shards have modified objects with linter errors, only those shards will be bypassed by skeema push.

This option is enabled by default. To disable linting of changed objects in skeema diff and skeema push, use --skip-lint on the command-line or skip-lint in an option file. This will cause the linting step of diff/push to be skipped entirely, regardless of the configuration of other lint-related options.

lint-auto-inc

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks the data type used in auto_increment columns. Unless set to “ignore”, a warning or error will be emitted for any auto_increment column using a data type not listed in option allow-auto-inc.

The primary purpose of this linter rule is to avoid problematic auto_increment edge cases. Please refer to the manual entry for allow-auto-inc for usage recommendations.

In addition to checking the type of the column, this linter rule also examines the next AUTO_INCREMENT value, if one is specifically defined in the filesystem (*.sql) version of the CREATE TABLE statement. If the defined value exceeds 80% of the maximum storable value for the column type, a warning or error will be emitted, even if the column data type is allowed. However, please note that Skeema’s linter only examines *.sql definitions, not live databases, and by default Skeema does not automatically put next AUTO_INCREMENT values into *.sql table definitions. You must regularly run skeema pull --include-auto-inc to put these values into *.sql table definitions.

lint-compression

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks the compression settings for InnoDB tables. Unless set to “ignore”, a warning or error will be emitted for any usage of compression settings not listed in option allow-compression. By default, this will allow uncompressed tables, as well as compressed tables using a KEY_BLOCK_SIZE of either 4 or 8 kilobytes.

Please note that this linter rule does not examine server global variables such as innodb_file_per_table, innodb_file_format, or innodb_page_size. Certain combinations of these server-side settings may silently prevent the database from actually using compression; however, compression options like KEY_BLOCK_SIZE will still be visible in SHOW CREATE TABLE and information_schema regardless, and this linter rule’s behavior is based on querying those sources. It is the user’s responsibility to ensure that the database server is properly configured to permit use of compression if desired.

This linter rule does not yet examine tables using non-InnoDB storage engines.

Column-level compression (available in Percona Server and MariaDB) is not evaluated by this linter rule at this time, but this may change in future releases.

lint-charset

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks each table’s default character set, along with the character set of each textual column. Unless set to “ignore”, a warning or error will be emitted for any usage of a character set not listed in option allow-charset.

This rule does not currently check any other object type besides tables.

lint-definer

Commandsdiff, push, lint, Cloud Linter
Default“error”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule specifies the severity of non-allowed DEFINER values for stored procedures, functions, views, and triggers. Unless set to “ignore”, a warning or error will be emitted for any DEFINER not listed in option allow-definer.

Although this option defaults to “error” severity, please note that the default value of corresponding option allow-definer is %@%, which intentionally permits all possible users. To enforce a restriction on definers, be sure to override allow-definer. Overriding lint-definer only controls the annotation severity (e.g. warning vs error) for definitions with non-allowed DEFINER users.

In the Community edition of Skeema, views and triggers are not supported, so this option only affects stored procedures and functions.

In premium Skeema products, if the strip-definer option is enabled, all DEFINER clauses are completely ignored, and the allow-definer and lint-definer options have no effect. Note that strip-definer is enabled by default in the Premium edition of the Skeema CLI whenever the user does not have sufficient privileges to set users, e.g. SUPER.

lint-display-width

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks that the display width of int-type columns, e.g. the 11 in int(11), is equal to the default display width for the column type. Integer display widths are commonly misunderstood, and do not affect the range of values that can be stored in the column. In almost all cases, the display width has no effect whatsoever, and should be left at its default value for the column type, or omitted entirely.

There are only 3 cases where non-default display widths are relevant:

  • By convention, boolean columns are typically defined using tinyint(1) (or as bool which is just an alias for tinyint(1)). lint-display-width always ignores such columns.
  • Int-type columns using the zerofill modifier are padded with leading zeroes based on the display width. lint-display-width always ignores such columns.
  • Display widths are included in query result metadata, and in theory some applications may use this information programmatically, and intentionally have non-default display widths for this reason. This is quite rare, but in this situation it makes sense to use lint-display-width=ignore.

MySQL 8.0.17 deprecated use of integer display widths, as well as the zerofill modifier. MySQL 8.0.19 removed display widths from appearance in SHOW CREATE TABLE and information_schema in most situations. As a result, lint-display-width has no effect in MySQL 8.0.19+.

lint-dupe-index

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks each table for duplicate, redundant, or otherwise unnecessary secondary indexes. Unless set to “ignore”, a warning or error will be emitted for each such index that is found.

For normal (BTREE) secondary indexes, a non-unique index is considered redundant if another BTREE index (whether non-unique, unique, or primary key) begins with the same columns in the same order. In this case, the larger index (covering more columns) is preferable to the smaller index.

Unique BTREE indexes are not considered redundant to other indexes in this manner, since a unique index is also inherently a uniqueness constraint. The uniqueness constraint is logically stronger when covering fewer columns; however, a unique index might contain additional columns for query optimization purposes. For this reason, unique BTREE indexes are only checked for exact duplicates, and only relative to the primary key or other unique BTREE indexes.

FULLTEXT indexes are only checked for exact duplicates with other FULLTEXT indexes, since these indexes do not follow the same left-side prefixing functionality as BTREE indexes.

SPATIAL indexes are checked for exact duplicates with other SPATIAL indexes. Additionally, in MySQL 8+, SPATIAL indexes are checked to ensure the column has an SRID attribute. If the indexed column lacks an SRID in MySQL 8+, the SPATIAL index is flagged as unnecessary since the database server will not use it.

This linter rule’s behavior is slightly less aggressive than Percona Toolkit’s pt-duplicate-key-checker, and does not implement behavior similar to pt-duplicate-key-checker’s --clustered option. This situation is not flagged by Skeema’s lint-dupe-index because it involves dropping and then recreating a secondary index, in order to remove primary key columns from the index suffix. This can be a delicate, operationally expensive operation if the table is large or heavily queried.

lint-engine

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks each table’s storage engine. Unless set to “ignore”, a warning or error will be emitted for any table using a storage engine not listed in option allow-engine.

lint-has-enum

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks for table columns using data type ENUM or SET. This option defaults to “ignore”, meaning that these data types do not result in a linter annotation by default. However, companies that restrict or forbid use of enumeration types may wish to set this to “warning” or “error”.

Companies that restrict use of enumeration types typically do so for these reasons:

  • Adding or removing a value from the list of allowed values requires a schema change each time, which is less flexible than storing data as an integer or varchar representation instead. While most ALTERs on the list of allowed values are fast or instantaneous, some special cases require a slow table copy, and the exact rules are easy to forget. For example, adding a new allowed value anywhere other than the end of the list requires a table copy; likewise for going from 8 to 9+ allowed values in a SET, or from 255 to 256+ allowed values in an ENUM.
  • Without strict sql_mode, attempting to insert an invalid value (for example due to a typo or application bug) does not generate an error, and instead stores an empty value.
  • If the list of allowed values includes digit strings, care must be taken to always quote the value, since an unquoted integer will instead be interpreted as an index in the list of allowed values.

lint-has-fk

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks for presence of foreign keys. This option defaults to “ignore”, meaning that presence of foreign keys does not result in a linter annotation by default. However, companies that restrict or forbid foreign keys may wish to set this to “warning” or “error”, which will flag any table defining one or more foreign keys (where the definition appears, i.e. on the “child” side of the foreign key relationship).

Companies that restrict foreign keys typically do so for these reasons:

  • Foreign keys introduce nontrivial write latency, due to the extra locking. In a high-write-volume OLTP environment, the performance impact can be quite substantial.
  • Foreign keys are problematic when using online schema change tools. Percona’s pt-osc allows them, albeit with extra complexity and risk. Most other OSC tools – gh-ost, Spirit, fb-osc, LHM – don’t support foreign keys at all.
  • Conceptually, foreign keys simply don’t work across a sharded environment. Although they still function within a single shard, application-level checks become necessary anyway for cross-shard purposes. As a result, sharded companies tend to converge on application-level checks exclusively.

lint-has-float

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks for table columns using data type FLOAT or DOUBLE. This option defaults to “ignore”, meaning that these data types do not result in a linter annotation by default. However, companies that restrict or forbid use of floating-point types may wish to set this to “warning” or “error”.

Some companies forbid use of floating-point types because they can only store approximate values. For use-cases requiring exact precision, such as monetary data, the DECIMAL type should be used instead.

lint-has-routine

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks for presence of stored procedures and functions. This option defaults to “ignore”, meaning that presence of routines does not result in a linter annotation by default. However, companies that restrict or forbid routines may wish to set this to “warning” or “error”.

Companies that restrict use of routines typically do so for these reasons:

  • Routines can present scalability challenges, since they involve moving computation onto the database (which is stateful and therefore harder to scale) instead of the application stack (which is stateless and easier to scale).
  • Routines involve some degree of operational complexity, in part because their bodies cannot be altered in-place without dropping and recreating the routine. Although Skeema automates this process, there is no way to avoid having a split-second period where a modified routine does not exist, which can result in application-facing query errors. As a work-around, some companies version routines using a naming scheme, but this can cause complicated deployment dependencies between the application and the database.

lint-has-time

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks for table columns using data type TIMESTAMP, DATETIME, or TIME. This option defaults to “ignore”, meaning that these data types do not result in a linter annotation by default. However, companies that restrict or forbid use of temporal types may wish to set this to “warning” or “error”. Some motivations for this include:

  • Timezone conversions, daylight savings time transitions, and leap second transitions are all common sources of application bugs or subtle data corruption. Any use of non-UTC timezones at the session, server, or system level will very likely introduce DST problems.
  • TIMESTAMP suffers from the Y2K38 problem: it cannot store values that exceed January 19, 2038, which is problematic for software with long-term support requirements. It should not be used for storing arbitrary future dates, especially from user input.
  • TIMESTAMP values have automatic timezone conversion behavior for storage and retrieval, between the session time_zone and UTC. In contrast, DATETIME and TIME simply store whatever raw value they were supplied, without any associated timezone information. Therefore, modifying the time_zone variable can effectively corrupt the interpretation of all existing DATETIME and TIME values, especially relative to any TIMESTAMP values.
  • By default, prior to MySQL 8.0 or MariaDB 10.10, the first TIMESTAMP column in a table automatically has DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP if no clauses are explicitly set and the column is not explicitly nullable. This behavior depends on the explicit_defaults_for_timestamp system variable, and that variable’s default depends on the database server version. This behavior can be surprising or confusing, and the version-specific change is often problematic upon upgrade to MySQL 8 or MariaDB 10.10.

Some companies opt to store all time-related values in an int unsigned or bigint unsigned (depending on the chosen epoch and precision) instead of using temporal types; this is typically motivated by having a non-UTC system time on database servers, which cannot be modified due to the 3rd bullet above. In this situation, consider setting lint-has-time=error to ban all temporal data types.

For a less strict policy, use lint-has-time=warning to ensure that developers keep the deficiencies of these data types in mind.

lint-has-trigger

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”; Not available in the Community edition of Skeema

This linter rule checks for presence of triggers. This option defaults to “ignore”, meaning that presence of triggers does not result in a linter annotation by default. However, companies that restrict or forbid triggers may wish to set this to “warning” or “error”.

Companies that restrict use of triggers typically do so for one of these reasons:

  • Triggers can cause performance problems at scale, since they increase transaction overhead, in terms of write amplification as well as locks.
  • Trigger handling differs between statement-based replication and row-based replication. This is problematic when switching from statement-based to row-based binlog format, in environments that rely on having triggers on only one side of the replication hierarchy.
  • Some online schema change tools, such as gh-ost and Spirit, cannot be used on tables with triggers.

This option is not available in the Community edition of Skeema, which does not support triggers. All triggers are completely ignored by the Community edition of Skeema.

lint-has-view

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”; Not available in the Community edition of Skeema

This linter rule checks for presence of views. This option defaults to “ignore”, meaning that presence of views does not result in a linter annotation by default. However, companies that restrict or forbid views may wish to set this to “warning” or “error”.

Companies that restrict use of views typically do so for one of these reasons:

  • Invalid views can be a source of operational problems. If the tables, views, columns, or definer user underlying a view are dropped, the view becomes invalidated automatically, but this won’t be apparent until the view is queried.
  • Queries on views may be difficult to optimize properly. When querying a view, index hints cannot be used.
  • Views are stored by the database in a canonical format which puts the entire query on one line and strips all inline comments. This makes SHOW CREATE VIEW difficult to read, which in turn affects skeema pull. See option update-views for more information.

This option is not available in the Community edition of Skeema, which does not support views. All views are completely ignored by the Community edition of Skeema.

lint-name-case

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks for tables and views with uppercase letters in their names, which can cause portability issues when using a mix of operating systems for database server environments – for example, using Linux in production but MacOS and/or Windows for local development databases.

Regardless of your servers' lower_case_table_names setting, you may enable lint-name-case if you wish to enforce an all-lowercase naming policy for tables and views. Even if all of your database servers use lower_case_table_names=1 (which automatically forces names to lowercase on the server side), lint-name-case can still be useful: it will confirm that names are expressed in all-lowercase in your *.sql files, in order to match the all-lowercase effect of lower_case_table_names=1 in the database.

This option defaults to “ignore”, which means the presence of uppercase letters in table or view names will not result in a linter annotation by default. Companies who wish to enforce an all-lowercase table/view name policy should set this option to “warning” or “error”.

Users should keep in mind that Skeema does not generate or manage RENAMEs, and therefore Skeema cannot be used directly to change the name-casing of existing objects. If your environment already has mixed-case table names, it is often impractical to change name-casing of existing tables, since this will break application queries on Linux database servers. This option is not recommended in that situation, especially at the “error” level. Setting lint-name-case=error is primarily useful for maintaining and enforcing an existing prohibition against mixed-case names, in an environment that is already all-lowercase.

This option does not affect names of columns, indexes, foreign keys, check constraints, procedures, functions, or triggers.

Currently this option does not lint names of databases (schemas) or partitions, even though these may have case-sensitivity implications. This may be improved in a future release.

Since the Community edition of Skeema does not manage views, this option ignores views in the Community edition and only checks table names. In the Premium edition, this option checks both tables and views.

lint-pk

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks each table for presence of a primary key. Unless set to “ignore”, a warning or error will be emitted for any table lacking an explicit primary key.

Tables without a primary key can be problematic in several ways. When an InnoDB table has no explicit primary key, and no non-NULLable unique keys, the storage engine will automatically use an internal global incremental ID as the clustered index key. This causes performance problems with concurrent inserts due to the use of a global mutex. It also prevents use of external online schema change tools, since the internal row ID is not exposed to SQL. Other storage engines may have similar problems when no primary key is present.

Note that if an InnoDB table has no explicit primary key, but does have one or more non-NULLable unique keys, InnoDB will use the first non-NULLable unique key as the implicit primary key. Such tables are still flagged by this linter rule, as many companies prefer to use an explicitly-defined primary key for clarity’s sake. In such cases, the linter annotation text is more brief, since presence of an implicit primary key does alleviate the performance and tooling problems of having no primary key at all.

lint-pk-type

Commandsdiff, push, lint, Cloud Linter
Default“ignore”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks the data types used in primary key columns. If set to “warning” or “error”, a warning or error (respectively) will be emitted for any primary key column using a data type not included in the corresponding list option allow-pk-type.

By default, lint-pk-type defaults to “ignore” (linter check disabled) while allow-pk-type defaults to an empty string. If you enable lint-pk-type, you must also set allow-pk-type to at least one column type.

This rule does not flag tables which have no primary key. For that use-case, see lint-pk. This design intentionally permits these linter rules to be set to different severity levels, such as setting lint-pk=error alongside lint-pk-type=warning.

The lint-pk-type option is intended for users who wish to prevent (or warn against) certain data types from being used in primary keys. This can be motivated by performance concerns (e.g. preventing inefficient varchars), scalability concerns (preventing smaller int types which are prone to ID space exhaustion), or for compatibility with third-party tools.

This linter rule is particularly useful when combining Skeema with the Spirit online schema change tool, which contains significant optimizations for tables that have memory-comparable primary keys. To encourage or require these optimizations, set lint-pk-type to either “warning” or “error”, and allow-pk-type to “tinyint,smallint,mediumint,int,bigint,decimal,varbinary,binary,date,datetime,timestamp,time,year” or any desired subset of those types.

lint-reserved-word

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule checks identifier names to see if they match reserved words. If set to “warning” (the default) or “error”, a warning or error (respectively) will be emitted for any identifier names which match a reserved word in any known version of your DBMS server vendor.

Reserved words are problematic for identifier names because they must be wrapped in `backticks` in SQL queries. This is irritating for ad hoc manual queries, and also tends to break ORM libraries and automation tools which don’t properly escape identifier names. It is especially troublesome when new reserved words are introduced in more recent database server versions than the one you are currently running: upon upgrading to the new server version, previously-working queries can suddenly break if any identifiers now match reserved words.

The logic for this linter check is aware of differences in the reserved word lists of MySQL vs MariaDB, and only checks the appropriate list for your DBMS. For example, if your server uses MySQL 5.7 (or a variant such as Percona Server 5.7 or AWS Aurora 5.7), identifiers will be checked against the full list of reserved words in any MySQL version up through the latest MySQL 8.0, but not any MariaDB-specific reserved words. Similarly if you use MariaDB, identifiers are checked against the full list of reserved words in any version of MariaDB, but not any MySQL-specific ones.

If you are preparing for a migration between MariaDB and MySQL (or vice versa) and need to test for reserved words in the other DBMS, combining the workspace=docker option with a command-line flavor override can accomplish this, for example: skeema lint --flavor=mysql:8.0 --workspace=docker --docker-cleanup=destroy

In the Community edition of Skeema, this linter rule checks names of tables, columns, stored procedures, and functions. In premium Skeema products, it additionally checks view names and trigger names. However, it does not check column names within a view at this time. Additionally, some uncommon edge cases, such as extra reserved words introduced by MariaDB’s ORACLE sql_mode, are not handled yet by this linter check.

lint-zero-date

Commandsdiff, push, lint, Cloud Linter
Default“warning”
Typeenum
RestrictionsRequires one of these values: “ignore”, “warning”, “error”

This linter rule examines the default values of DATE, DATETIME, and TIMESTAMP columns, and flags any that contain zero-dates. This includes the full zero-date value “0000-00-00” as well as any individual zero component (e.g. “2021-01-00” or “0000-01-02”). Use of DEFAULT NULL is generally preferable to zero-dates, as long as your application is able to interact with NULL dates properly.

Zero date default values are prohibited by strict sql_mode server-side. This linter rule is intended to help users who wish to transition from a non-strict sql_mode to a strict one.

Configuring this option only affects linter detection of zero dates. It does not change the sql_mode for Skeema’s connections. If you are receiving Error 1067: Invalid default value from a table with zero-date default values when executing Skeema with workspace=docker, upgrade to the latest Skeema version. As of Skeema v1.10.1, workspace=docker automatically mirrors your real database’s sql_mode. In older versions of Skeema, you would need to configure a matching sql_mode manually in connect-options. This situation typically arises when your real database is hosted on AWS RDS / Aurora: even though all modern versions of MySQL and MariaDB normally default to enabling strict sql_mode (including in Docker images), AWS inexplicably disables strict-mode by default in its managed database products.

my-cnf

Commandsall
Defaulttrue
Typeboolean
RestrictionsIgnored in .skeema files

If true, Skeema will parse the standard per-user MySQL configuration file, ~/.my.cnf, for configuration information in sections [skeema], [client], and [mysql]. This permits Skeema to re-use already-configured values for options shared with MySQL, such as user, password, and socket. If false, ~/.my.cnf parsing is skipped entirely.

This option is enabled by default. To disable it, use --skip-my-cnf on the command-line, or skip-my-cnf in either /etc/skeema or /usr/local/etc/skeema. This option has no effect if disabled in ~/.skeema or any other .skeema file, since these are parsed after ~/.my.cnf.

For more information on Skeema’s configuration files and order of parsing, please refer to the configuration documentation.

new-schemas

Commandspull
Defaulttrue
Typeboolean
Restrictionsnone

If true, skeema pull will look for schemas (databases) that exist on the instance, but have no filesystem representation yet. It will then create and populate new directories for these schemas. If false, this step is skipped, and new schemas will not be pulled into the filesystem.

This option is enabled by default. To disable it, use --skip-new-schemas on the command-line or skip-new-schemas in an option file.

When using a workflow that involves running skeema pull development regularly, it may be useful to disable this option. For example, if the development environment tends to contain various extra schemas for testing purposes, set skip-new-schemas in a global or top-level .skeema file’s [development] section to avoid storing these testing schemas in the filesystem.

partitioning

Commandsdiff, push
Default“keep”
Typeenum
RestrictionsRequires one of these values: “keep”, “remove”, “modify”

Skeema v1.4.0 added diff support for partitioned tables. This option affects how DDL involving partitioned tables is generated or executed via skeema diff and skeema push.

With the default value of “keep”, tables may be partitioned (through the filesystem CREATE TABLE containing a PARTITON BY clause, either initially or one subsequently being added), but will never be de-partitioned or re-partitioned. In other words, once a table is partitioned in a database, with partitioning=keep Skeema suppresses further modifications to the partitioning clause for the table.

With a value of “remove”, tables will not be partitioned, and any already-partitioned tables will be de-partitioned. If any filesystem CREATE TABLE statements contain a PARTITION BY clause, it will effectively be ignored. Any already-partitioned tables in a database will automatically have DDL generated to de-partition them via ALTER TABLE ... REMOVE PARTITIONING.

With a value of “modify”, partitioning clauses are handled permissively. Tables will be partitioned, re-partitioned, or de-partitioned based on the presence of a PARTITION BY clause in the filesystem CREATE TABLE statement.

Overall, the intended use of the partitioning option is as follows:

  • If you use partitioning in production but not in development (for example), place partitioning=remove in a [development] section of a top-level .skeema file. This will ensure that tables in your development databases are never partitioned, removing the need to run partition-management scripts in dev.
  • The default of partitioning=keep is useful in all environments where partitioning is actually in-use; it prevents accidental re-partitioning or de-partitioning. For example, if you choose to omit PARTITION BY clauses from your checked-in *.sql files entirely, you can use partitioning=keep in environments with partitioning to prevent skeema push from ever de-partitioning any tables.
  • For one-off situations where you intentionally want to re-partition or de-partition an existing partitioned table, you can use skeema push --partitioning=modify as a command-line override.

Regardless of this option, modifications to just the partition list of a partitioned table are always ignored for RANGE and LIST partitioning methods, and are unsupported for HASH and KEY methods. Skeema will not add or remove partitions from an already-partitioned table, regardless of differences between the filesystem CREATE TABLE and the table in a live database. The intended workflow is to use an external tool/cron for managing the partition list, e.g. to remove old time-based RANGE partitions and add new ones.

This option only affects database diff/manipulation behavior, meaning the skeema diff and skeema push commands. To control partitioning behavior of filesystem *.sql dump/manipulation commands – skeema init, skeema pull, skeema format, skeema lint – see options strip-partitioning and update-partitioning. These options are intentionally distinct, to permit configuring different combinations of partitioning handling for diff vs dump behaviors, whether via ad hoc command-line use or permanent environment configuration in a .skeema file.

password

Commandsall
Default“$MYSQL_PWD” (or no password if this env variable is not set)
Typestring
Restrictionsif supplied without =value, STDIN must be a TTY for interactive prompt

Specifies what password should be used when connecting to MySQL. The behavior of this option is designed to match the standard mysql client:

  • To interactively prompt for the password value on STDIN, omit the equals sign and value: use --password with no =value on the command-line, or a line with just “password” in an option file.
  • To supply a password value directly on the command-line, do not use whitespace between the option and value. In other words, --password=value and -pvalue work properly. However, attempting to use --password value or -p value will not work as expected, instead prompting on STDIN as per the previous bullet.
  • If you do not specify the password option anywhere, it will default to using the value of the MYSQL_PWD environment variable, if it is set. If this variable is also not set, connections will be attempted without a password.

In option files, you may supply the value as an environment variable beginning with Skeema v1.9.0.

Note that skeema init intentionally does not persist password to a .skeema file. If you would like to store the password, you may manually add it to a global option file, such as ~/.my.cnf. For security reasons, avoid putting passwords in .skeema files inside your schema repo, as this would typically be committed to source control.

If you wish to interact with multiple database hosts at once (e.g. running skeema diff from the “top” of a schema repo), but the hosts each have different passwords, Skeema v1.9.0 adds two possible solutions which avoid the need to commit passwords to source control:

  • Environment variables: For example, maindb/.skeema could contain password=$MAINDB_PASSWORD while otherdb/.skeema could contain password=$OTHERDB_PASSWORD.
  • Interactive prompting per host: In each .skeema file that specifies a host value, specify a bare “password” line alongside it, and Skeema will interactively prompt for the password for each host separately.

port

Commandsall
Default3306
Typeint
Restrictionsnone

Specifies a nonstandard port number to use when connecting to MySQL via TCP/IP.

In option files, you may supply the value as an environment variable beginning with Skeema v1.9.0.

safe-below-size

Commandsdiff, push
Default0
Typesize
Restrictionsnone

For any table below the specified size (in bytes), Skeema will allow execution of unsafe operations, even if allow-unsafe has not be enabled. (To see a list of which operations are considered unsafe, see the documentation for allow-unsafe.)

The size comparison is a strict less-than. This means that with the default value of 0, no unsafe operations will be allowed automatically, as no table can be less than 0 bytes.

To only allow unsafe operations on empty tables (ones without any rows), set safe-below-size to 1. Skeema always treats empty tables as size 0 bytes as a special-case.

This option is intended to permit rapid development when altering a new table before it’s in use, or dropping a table that was never in use. The intended pattern is to set safe-below-size in a global option file, potentially to a higher value in the development environment and a lower value in the production environment. This way, whenever unsafe operations are to be run on a larger table, the user must supply --allow-unsafe manually on the command-line when appropriate to confirm the action.

This option does not apply to other object types besides tables, such as stored procedures or functions, as they have no notion of “size”.

schema

Commandsall
DefaultN/A
Typestring
Restrictionssee limitations on placement

This option specifies which schema name to operate on, or which schema name(s) a directory maps to.

skeema init optionally may be supplied --schema on the command-line, to indicate that only a single schema should be exported to the filesystem, instead of the normal default of all non-system schemas on the database instance. In this situation, only a single subdirectory is created, rather than a subdirectory for the instance containing another nested level of subdirectories for each schema.

Aside from the special case of skeema init, the schema option should only appear in .skeema option files, inside “bottom level” subdirectories containing *.sql files. Other commands intentionally do not support this option on the command-line. To interact with just a single schema at a time, simply cd to that schema’s subdirectory before invoking skeema; see the FAQ entry for more information.

In .skeema option files, the value of the schema option may take any of these forms:

  • A single schema name, for example schema=foo
  • Multiple schema names in a comma-separated list, for example schema=shard1,shard2,shard3
  • An environment variable beginning with $, such as schema=$MY_DB_NAME (Skeema v1.9+)
  • A single asterisk character *, that is schema=*
  • A forward-slash-wrapped regular expression, such as schema=/^foo/ (Skeema v1.4+)
  • A backtick-wrapped command line to execute; the command’s STDOUT will be split on a consistent delimiter (newline, tab, comma, or space) and each token will be treated as a schema name

Most users will just use the first option, a single schema name.

The ability to specify multiple schema names is useful in sharded environments with multi-tenancy: each database instance contains several schemas, and they all have the same set of tables, and therefore each schema change needs to be applied to multiple schemas on an instance.

You may supply the value as an environment variable beginning with Skeema v1.9.0. The value stored in the environment variable may be a single schema name, or a comma-separated list of multiple sharded schema names. If the specified environment variable value is not actually set, the directory will be skipped by skeema diff and skeema push, with a warning message logged.

Setting schema=* is a special value meaning “all non-system schemas on the database instance”. This is the easiest choice for a multi-tenant sharded environment, where all non-system schemas have the exact same set of tables. The ignored system schemas include information_schema, performance_schema, mysql, sys, and test. Additional schemas may be ignored by using the ignore-schema option.

In some sharded environments, it is easier to express a dynamic set of schema names to include, rather than exclude. Setting the schema value to a forward-slash-wrapped regular expression accomplishes this. For example, schema=/^foo/ will map this directory to all schema names beginning with prefix “foo”. This approach is useful when some schemas (with a common naming convention) represent shards with the same set of tables, while other special unsharded schemas are also present.

Some sharded environments may need even more flexibility – for example, when the sharding scheme does not follow a consistent naming pattern. In this case, set schema to a backtick-wrapped external command shellout. This permits the directory to be mapped to one or more schema names dynamically, based on the output of any arbitrary script or binary, such as a service discovery client. The command line may contain special variables, which Skeema will dynamically replace with appropriate values. See options with variable interpolation for more information. The following variables are supported for this option:

  • {HOST} – hostname (or IP) for the database instance being processed
  • {PORT} – port number for the database instance being processed
  • {USER} – MySQL username defined by the user option either via command-line or option file
  • {PASSWORD} – MySQL password defined by the password option either via command-line or option file
  • {PASSWORDX} – Behaves like {PASSWORD} when the command-line is executed, but only displays X’s whenever the command-line is displayed on STDOUT
  • {ENVIRONMENT} – environment name from the first positional arg on Skeema’s command-line, or “production” if none specified
  • {DIRNAME} – The base name (last path element) of the directory being processed. May be useful as a key in a service discovery lookup.
  • {DIRPATH} – The full (absolute) path of the directory being processed.

Regardless of which form of the schema option is used, the ignore-schema option is applied last as a regex “filter” against it, potentially removing some of the listed schema names based on the configuration.

server-public-key-path

Commandsall
Defaultempty string
Typestring
RestrictionsNot available in the Community edition of Skeema

When connecting to a database server using an unencrypted connection (ssl-mode=disabled, or ssl-mode=preferred with a server that does not support SSL) and an authentication plugin of either sha256_password or caching_sha2_password, the server ordinarily sends a special-purpose public key to the client to use for password encryption.

This transfer is potentially insecure, and incurs an extra round-trip at connection time. To avoid this, place the public key on each machine using skeema and configure its path using server-public-key-path. This option’s behavior is analogous to the corresponding --server-public-key-path option of the mysql command-line client.

The file path may be either an absolute or relative path. When supplying --server-public-key-path on the command-line, a relative path will be interpreted based on the working directory from which skeema was initially invoked. When setting server-public-key-path in an option file, a relative path will be interpreted based on the directory containing that option file.

This option has no useful effect if encrypted connections are known to be in-use. It also has no effect when connecting to a MariaDB server, since MariaDB server does not support the sha256_password or caching_sha2_password authentication plugins.

This option is not available in the Community edition of Skeema.

socket

Commandsall
Default“/tmp/mysql.sock”
Typestring
Restrictionsnone

When the host option is “localhost”, this option specifies the path to a UNIX domain socket to connect to the local MySQL server. It is typically ignored if host isn’t “localhost” and/or if the port option is specified.

This option’s default of /tmp/mysql.sock is aligned with the stock MySQL default. However, most operating system distributions/packages actually configure a different location such as /var/run/mysqld/mysqld.sock or /var/lib/mysql/mysql.sock. In order for host=localhost connections to work, you will likely need to manually specify the correct socket path as well.

When using the ssh-to-db option in the Premium edition CLI, the socket option may be used even when the host isn’t “localhost”. This enables use of a UNIX domain socket on the remote database host, rather than using a TCP/IP connection to 127.0.0.1.

In option files, you may supply the value as an environment variable beginning with Skeema v1.9.0.

ssh

Commandsall
Defaultempty string
Typestring
RestrictionsNot available in the Community edition of Skeema

Beginning in v1.8.0, the Premium edition CLI includes the ability to dynamically create SSH tunnels. When enabled, this permits Skeema to launch database connections through an intermediate remote machine, and/or to establish database connections “locally” on a database host which is actually remote.

SSH tunnels are configured through the ssh and ssh-to-db options, which can be used individually (single-hop tunnel) or in combination (two-hop tunnel). The functionality is designed to handle any of these use-cases:

  • SSH tunnel through an intermediate bastion host / jump box. Typically this is motivated by security reasons, such as environments that restrict where direct database connections can be made. This use-case is handled by the ssh option.
  • SSH tunnel directly to each database host, for situations where database administrative users (with elevated grants/privileges) are restricted to localhost connections. This use-case is handled by the ssh-to-db option.
  • Two-hop SSH tunnel, first to an intermediate bastion / jump box, and then to the database host. This use-case involves configuring both ssh and ssh-to-db options at the same time.
  • Executing all DDL wrapper scripts (alter-wrapper or ddl-wrapper) from a common location, either centralized (ssh option) or on each individual database host (ssh-to-db option). This can be motivated by reasons of security, locking (preventing concurrent execution), uniformity (ensuring the same version of pt-osc or gh-ost), and portability (e.g. removing the need to get pt-osc working on Windows or MacOS).

To use the ssh option, at minimum set the option value to the name of the intermediate host to SSH to, or a hostname alias defined in your ~/.ssh/config OpenSSH configuration file. You may optionally also include an SSH username, SSH password, and/or nonstandard SSH port by specifying the option value in format [username[:password]@]hostname[:port], for example ssh=mybastion.com:2222, ssh=myuser@mybastion.com, ssh=myuser:mypassword@mybastion.com:2222, etc.

If an OpenSSH authentication agent (ssh-agent) is running, Skeema will communicate with it in order to supply your identities (SSH keys) for authentication with the remote server.

  • On Linux and MacOS, Skeema uses the UNIX socket location specified by $SSH_AUTH_SOCK.
  • On Windows, the named pipe \\.\pipe\openssh-ssh-agent is used. Please note that Skeema can communicate with the Windows port of the OpenSSH ssh-agent, but not the PuTTY agent pageant.

Skeema will also use your ~/.ssh/config OpenSSH configuration file for the following settings:

  • User
    • If a username is specified directly in the ssh option value, that takes precedence over ~/.ssh/config.
    • If no username is specified in the ssh option value, nor in ~/.ssh/config, then the current OS username is used by default.
  • Port
    • If a port is specified directly in the ssh option value, that takes precedence over ~/.ssh/config.
    • If no port is specified in the ssh option value, nor in ~/.ssh/config, then port 22 is used by default.
  • HostName
    • Specifies the “real” hostname or IP address to use in place of an SSH alias.
  • IdentityFile
    • Specifies an SSH identity file (private key) to use, instead of (or in addition to) using an SSH agent.
    • If not specified, defaults to ~/.ssh/id_rsa and/or ~/.ssh/id_dsa if these files exist.
    • Without an SSH agent, encrypted private key files may be used if STDIN is a TTY; Skeema will interactively prompt you for the passphrase in this situation.
    • If an SSH agent is running, Skeema assumes that all encrypted keys are managed by the agent, and will not prompt for key decryption passphrases interactively.
  • UserKnownHostsFile
    • Specifies the location of the known_hosts file, or defaults to ~/.ssh/known_hosts if that file exists. If the file does not exist, Skeema will not create it.
    • If the file does not exist or cannot be read, and StrictHostKeyChecking is yes, this is treated as a fatal error.
    • If the file does not exist or cannot be read, and StrictHostKeyChecking is not yes, Skeema will log a warning and skip all behavior relating to known_hosts. This reduces security, but may be preferable in non-interactive environments, or when using ephemeral hosts.
    • Ordinarily, if a host’s public key has changed from what was recorded in known_hosts, Skeema will refuse to connect. Overriding this behavior is not recommended (due to risk of MitM attacks), but may be achieved by setting UserKnownHostsFile /dev/null along with StrictHostKeyChecking no.
  • StrictHostKeyChecking
    • Controls handling of connection to hosts that are not already found in the UserKnownHostsFile. See OpenSSH configuration manual for a description.
    • May be set to ask (the default), yes, or no.
    • For non-interactive use, be sure to either pre-populate your known_hosts file, or set StrictHostKeyChecking no. With StrictHostKeyChecking ask, if STDIN is not a TTY, this is treated as a fatal error.

To debug SSH configuration and authentication problems, enable Skeema’s debug option to increase the amount of logging detail.

Whenever an SSH tunnel is used (whether via ssh and/or ssh-to-db), several important modifications are automatically made to Skeema’s behavior:

  • Any configured alter-wrapper or ddl-wrapper will be executed on the remote host, rather than locally.
    • Be sure to specify an absolute path for the wrapper. This should be a path on the remote host.
    • If you’re using a two-hop tunnel (setting both ssh and ssh-to-db at the same time), the wrapper will be executed on the database host, not the intermediate bastion/jump-box.
    • If your tables are large, consider putting a terminal multiplexer such as screen or tmux at the beginning of your configured alter-wrapper or ddl-wrapper command-line, in order to ensure that loss of SSH connectivity does not interrupt a long-lived online schema change operation.
    • If you intentionally detach/background a terminal multiplexer (e.g. CTRL-A CTRL-D in screen), Skeema will think your wrapper script has completed successfully, causing Skeema to immediately move on to the next operation – while the previous wrapper script continues to run remotely in the detached screen. If your push involves ALTERs to multiple tables, this means you may end up with multiple concurrent online schema changes running at once, for better or worse!
  • If no ssl-mode has been configured, it will default to disabled instead of the usual default of preferred. This is done to prevent unnecessary double-encryption, since the SSH tunnel is already encrypted.
    • If you need to connect with a database user account that has the REQUIRE SSL attribute on the server side, explicitly set ssl-mode=required to override this behavior, but be aware the double-encryption will increase latency.
  • Query timeouts and socket write timeouts specified in connect-options (readTimeout and writeTimeout, respectively) are ignored. No timeout is enforced at the query or socket level. Regardless, Skeema automatically sends keep-alive messages to keep the SSH tunnel active, and will automatically abort if the SSH server remains non-responsive after a grace period of several minutes.

ssh-to-db

Commandsall
Defaultfalse
Typebool
RestrictionsNot available in the Community edition of Skeema

Beginning in v1.8.0, the Premium edition CLI includes the ability to dynamically create SSH tunnels. When enabled, this permits Skeema to launch database connections through an intermediate remote machine (the ssh option), and/or to establish database connections “locally” on a database host which is actually remote (the ssh-to-db option).

For a broad overview, refer to the documentation for the ssh option above, as most of that option’s mechanics also apply to ssh-to-db. The rest of this section focuses only on unique aspects of ssh-to-db. This option is intended for use in situations where database administrative grants/privileges are restricted only to localhost connections.

If ssh-to-db is enabled, Skeema will establish an SSH tunnel to each database server host, for use in all database connections on that host. If a socket is also configured, database connections will use the specified UNIX domain socket for localhost connections. Otherwise, if no socket is configured, database connections will use TCP/IP to the configured database port over the loopback interface (127.0.0.1).

If both ssh and ssh-to-db are enabled, Skeema will create a two-hop SSH tunnel: first to the bastion/jump-box specified by ssh, and then to each database host.

  • This combination is rarely needed, as it is only necessary in environments which severely restrict both where database connections can be made and where tool binaries like skeema may be installed.
  • All SSH configuration and identity files will be used from your local machine, for both hops. If your local machine has an ssh-agent, it will be used for both hops automatically. (This does not require or use agent forwarding. Skeema does not ever set up agent forwarding, and it ignores the ForwardAgent OpenSSH configuration option.)

Since ssh-to-db is a boolean option, it does not permit directly configuring an SSH username or non-standard SSH port. By default, the current local OS username will be used, along with the standard SSH port of 22. To override this behavior, use an OpenSSH configuration file ~/.ssh/config with entries matching your host values. See the description for ssh for more information.

To debug SSH configuration and authentication problems, enable Skeema’s debug option to increase the amount of logging detail.

The ssh-to-db option is compatible with SSH hostname aliases. For example, you can use host=mydbalias and ssh-to-db, even if “mydbalias” is not defined in DNS, as long as your ~/.ssh/config contains a configuration section mapping “mydbalias” to a real HostName. This works because ssh-to-db inherently always uses localhost / 127.0.0.1 for its database connections, so no DNS lookup is made for database connections.

In managed database-as-a-service environments (AWS RDS, Google CloudSQL, etc), ssh-to-db cannot be used, as these services typically do not provide SSH access to the underlying database server host.

ssl-ca

Commandsall
Defaultempty string
Typestring
RestrictionsNot available in the Community edition of Skeema

This option specifies a file path to a Certificate Authority (CA) file, in PEM format. If supplied, Skeema will use it to verify the server’s certificate, helping to confirm that the server is the intended destination host and not a man-in-the-middle attacker. This option’s behavior is analogous to the corresponding --ssl-ca option of the mysql command-line client.

The file path may be either an absolute or relative path. When supplying --ssl-ca on the command-line, a relative path will be interpreted based on the working directory from which skeema was initially invoked. When setting ssl-ca in an option file, a relative path will be interpreted based on the directory containing that option file.

If no ssl-mode is specified, but an ssl-ca is supplied, ssl-mode=verify_ca will be used automatically: the server’s cert will be verified, but no strict server hostname verification is performed. To improve security further, use ssl-mode=verify_identity, or equivalently, enable ssl-verify-server-cert.

This option has no useful effect when combined with an explicit setting of ssl-mode=disabled, ssl-mode=preferred, or ssl-mode=required. These modes do not perform verification of the server.

This option is not available in the Community edition of Skeema.

ssl-cert

Commandsall
Defaultempty string
Typestring
RestrictionsNot available in the Community edition of Skeema

This option specifies the file path to the client-side SSL public key certificate in PEM format. Its behavior is analogous to the corresponding --ssl-cert option of the mysql command-line client.

If ssl-cert is specified, the corresponding private key option ssl-key must also be used. These options can permit connecting to the database server with a user account that has the REQUIRE X509, REQUIRE ISSUER, and/or REQUIRE SUBJECT options.

The file path may be either an absolute or relative path. When supplying --ssl-cert on the command-line, a relative path will be interpreted based on the working directory from which skeema was initially invoked. When setting ssl-cert in an option file, a relative path will be interpreted based on the directory containing that option file.

This option is not available in the Community edition of Skeema.

ssl-key

Commandsall
Defaultempty string
Typestring
RestrictionsNot available in the Community edition of Skeema

This option specifies the file path to the client-side SSL private key in PEM format. Its behavior is analogous to the corresponding --ssl-key option of the mysql command-line client.

If ssl-key is specified, the corresponding public key certificate option ssl-cert must also be used. These options can permit connecting to the database server with a user account that has the REQUIRE X509, REQUIRE ISSUER, and/or REQUIRE SUBJECT options.

The file path may be either an absolute or relative path. When supplying --ssl-key on the command-line, a relative path will be interpreted based on the working directory from which skeema was initially invoked. When setting ssl-key in an option file, a relative path will be interpreted based on the directory containing that option file.

This option is not available in the Community edition of Skeema.

ssl-mode

Commandsall
Default“preferred” (varies, see below)
Typeenum
RestrictionsRequires one of these values: “disabled”, “preferred”, “required”, “verify_ca”, “verify_identity”

This option specifies the desired level of connection security (SSL/TLS usage). Its behavior is designed to match the standard mysql 5.7+ command-line client’s equivalent ssl-mode setting.

With a value of “disabled”, an unencrypted connection will be used, even if the database server supports encryption. This value can be useful when connecting to a database over an already-encrypted link, such as an SSH tunnel or VPN, to avoid a significant latency penalty from unnecessary double-encryption. This is the default value for ssl-mode if an ssh or ssh-to-db SSH tunnel is being used.

With a value of “preferred”, an encrypted connection will be used if the database server supports one, or an unencrypted connection will be used if not. This is ordinarily the default value for ssl-mode, assuming no ssl-ca is supplied, and no ssh or ssh-to-db SSH tunnel is being used.

With a value of “required”, an encrypted connection must be used. If the database server does not support this, an error is returned.

With a value of “verify_ca”, an encrypted connection must be used, and additionally the server certificate will be verified against the supplied ssl-ca. This is the default value if no ssl-mode is specified explicitly but an ssl-ca is supplied. This value is not supported in the Community edition of the Skeema CLI.

With a value of “verify_identity”, an encrypted connection must be used, and additionally the server certificate will be verified against the supplied ssl-ca with a strict host name match as well. This helps to confirm that the server is the intended destination host and not a man-in-the-middle attacker. However, this value cannot be used if the server uses a self-signed certificate, as is the default in MySQL 5.7+ outside of DBaaS environments. This value is not supported in the Community edition of the Skeema CLI.

Prior to Skeema v1.6.0, some of this behavior could be configured at the driver level inside of connect-options. For backwards compatibility purposes, using connect-options to configure SSL mode is still supported, but deprecated. An error is returned if the SSL mode is set in both connect-options and ssl-mode at the same time.

ssl-verify-server-cert

Commandsall
Defaultfalse
Typebool
RestrictionsNot available in the Community edition of Skeema; requires ssl-ca be set

Enabling this option is completely equivalent to setting ssl-mode=verify_identity. The ssl-verify-server-cert option is provided only for purposes of option-handling compatibility with the standard mysql command-line client from MariaDB, as well as pre-5.7 versions of MySQL. These versions of the mysql client have an ssl-verify-server-cert setting instead of the newer ssl-mode setting.

When enabling this option, an error is returned if ssl-mode is also set at the same time, and/or if no ssl-ca is supplied.

This option is not available in the Community edition of Skeema.

strip-definer

Commandsinit, format, pull, lint, diff, push, Cloud Linter
Default“auto” (see below)
Typeenum / boolean
RestrictionsNot available in the Community edition of Skeema

All stored procedures, functions, views, and triggers always have a notion of a DEFINER user in the database. Even if the object was created without an explicit DEFINER clause, the user who ran the CREATE will become the definer, and the output of SHOW CREATE queries will always include this clause. Because commands such as skeema init and skeema pull rely on SHOW CREATE output, this means DEFINER clauses would normally be present in *.sql files.

Any database user account may specify a DEFINER equal to the current user, but specifying a different user as the DEFINER requires elevated privileges. Database-as-a-service platforms (such as AWS RDS and GCP CloudSQL) may not provide these privileges, especially in older database versions prior to MySQL 8 or MariaDB 10.5. If your environment has different database user account names in each environment, this can be problematic on these database-as-a-service platforms.

To solve this problem, premium Skeema products support the strip-definer option beginning in v1.6.0. When enabled, it affects command behavior as follows:

This option defaults to value “auto”, which will enable the option whenever the specified user lacks the SUPER privilege, or the newer finer-grained privileges SET_USER_ID (MySQL 8.0+), SET_ANY_DEFINER (MySQL 8.2+), or SET USER (MariaDB 10.5+). If you instead wish to explicitly enable or disable the option (regardless of user privilege grants), you may specify the option like a boolean. For example:

  • To always strip definers even if you have SUPER privileges, specify --strip-definer on the command-line or just strip-definer in an option file. As with booleans, the =true value is implied and may be omitted.
  • To keep definers intact even though you don’t have SUPER privileges, specify --skip-strip-definer or --strip-definer=false on the command-line, or skip-strip-definer or strip-definer=false in an option file.

If you encounter DEFINER-related workspace DDL errors in Skeema Premium v1.8.1 through v1.10.0, simply upgrade to the latest version of Skeema to solve this problem. Alternatively, manually remove DEFINER clauses from any CREATE PROCEDURE or CREATE FUNCTION statements in your *.sql files as a workaround without upgrading.

Note that Skeema Cloud Linter operates with full privileges on an ephemeral containerized database instance. This means it treats “auto” as “false”, and the lint-definer and allow-definer options still work properly by default. To strip DEFINER clauses from diffs generated by Cloud Linter, put strip-definer or loose-strip-definer in a .skeema config file. (The loose prefix can be used to maintain compatibility with Skeema products/versions which do not support strip-definer.)

This option is not available in the Community edition of Skeema. The Community edition of Skeema does not support management of views or triggers (it always ignores them). It does support stored procedures and functions, but does not provide a way to strip DEFINER clauses from their definitions.

strip-partitioning

Commandsinit, format, pull, lint
Defaultfalse
Typeboolean
Restrictionsnone

When enabled, this option strips PARTITION BY clauses from table definitions written to the filesystem. This is useful if you want to completely exclude partitioning-related clauses from version control, for example if external partitioning automation is completely responsible for partition management.

To exclude PARTITION BY clauses when initializing a new directory, use skeema init --strip-partitioning.

To strip PARTITION BY clauses from an existing repo, use skeema format --strip-partitioning.

This option is also supported by skeema lint, since this command inherently reformats files by default. This option has no effect when using skeema lint --skip-format.

When using skeema pull to update an existing repo, the effects of this option depend on what other options are used:

temp-schema

Commandsdiff, push, pull, lint, format
Default“_skeema_tmp”
Typestring
Restrictionsnone

This option specifies the name of the temporary schema to use for Skeema workspace operations. See the FAQ for more information on how this schema is used.

When setting this option, do not use a schema name which already exists and contains real data. Skeema will automatically detect if any tables contain rows, and abort in this situation. But if all tables are empty, Skeema will drop them and proceed to use the specified schema.

In option files, you may supply the value as an environment variable beginning with Skeema v1.9.0.

temp-schema-binlog

Commandsdiff, push, pull, lint, format
Default“auto”
Typeenum
RestrictionsRequires one of these values: “on”, “off”, “auto”

With workspace=temp-schema, this option controls whether or not workspace operations are written to the database’s binary log, which means they will be executed on replicas if replication is configured.

If possible, it is generally preferable to avoid replication of workspace queries. The workspace schema is “cleaned up” (dropped in a safe manner) after processing each directory, and typically Skeema should be configured to only interact with writable (master) databases anyway, so replicating the workspace queries serves no purpose. However, the ability to selectively skip binary logging requires at least one of the following administrative privileges: SUPER (any flavor), SYSTEM_VARIABLES_ADMIN (MySQL 8.0+), SESSION_VARIABLES_ADMIN (MySQL 8.0+), or BINLOG ADMIN (MariaDB 10.5+). These administrative privileges may be unavailable in database-as-a-service environments (e.g. AWS RDS or GCP CloudSQL), especially when using MySQL 5.7, MariaDB 10.4, or anything older.

With a value of “on”, Skeema will not do any special handling for workspace queries, meaning that they will be written to the binlog and be executed by replicas. This value is guaranteed to work regardless of user privileges.

With a value of “off”, Skeema will skip binary logging (via SET SESSION sql_log_bin=0) for workspace queries, meaning that they will not be written to the binlog or executed by replicas. If Skeema’s user lacks sufficient superuser privileges, a fatal error will be returned.

With the default value of “auto”, Skeema will detect whether the configured user has sufficient privileges to skip binary logging of workspace queries, and will do so if available. In other words, “auto” functions as “off” if running as a privileged administrative user, or “on” otherwise.

This option has no effect if the database’s binary log is already globally disabled.

This option does not impact non-workspace-related queries executed by skeema push.

temp-schema-environment

Commandsdiff, push, pull, lint, format
Defaultempty string
Typestring
RestrictionsNot available in the Community edition of Skeema

In order to accurately introspect the CREATE statements in your *.sql files, Skeema executes them in a temporary location, referred to as a “workspace”. Ordinarily, with the default of workspace=temp-schema, the temporary workspace schema is placed on each database instance that Skeema interacts with. The temp-schema-environment option provides a way to offload the temp-schema workspace to a different database server, as configured by the specified environment name (.skeema file section).

The motivations for offloading the temp-schema workspace are similar to the motivations of using workspace=docker, especially the desire to avoid disruption to busy production databases. However, whereas workspace=docker interacts with automatically-managed ephemeral containerized databases, in contrast temp-schema-environment permits use of any arbitrary pre-existing database server. This is especially useful when skeema itself is running inside a container, making workspace=docker problematic.

The temp-schema-environment option should be set to an environment name (.skeema file section) to use for configuring the workspace host location. You may either re-use another environment (for example, “run all workspaces on the staging database server”), or define a new environment dedicated to workspaces. For example, consider this .skeema file snippet:

temp-schema-environment=scratch

[production]
host=db-prod.example.com
user=produser

[staging]
host=db-stage.example.com
user=stageuser

[scratch]
host=db-scratch.example.com
user=scratchuser

[localdev]
host=localhost
user=root
socket=/var/lib/mysql/mysql.sock
skip-temp-schema-environment

With the above configuration, Skeema will perform temp-schema workspace operations on the dedicated “scratch” database server, instead of the normal target database host and user, when running skeema against any environment except localdev. (Because temp-schema-environment is configured at the top of the file in this example, it applies to all environments, except ones that explicitly override it.)

Alternatively, to re-use an existing environment for workspaces, the above example could have used temp-schema-environment=staging (run all workspaces on the staging DB, since it presumably matches production’s configuration closely) or perhaps temp-schema-environment=localdev (run all workspaces locally, to avoid network latency). In either case, the temp-schema workspace would be placed in _skeema_tmp (or whatever name the temp-schema option is set to), and it is not a problem if there are other “real” schemas on the database server.

Only connectivity-related options in the specified environment will take effect – options that specify which host to connect to, and how to connect to it. These include:

For any of these options that are not configured in the temp-schema’s environment, the original configuration (from the command-line, target environment, and/or “sectionless” lines at the top of a .skeema file) will be used instead as usual. For example, if both hosts should have the same user or password, there is no need to specify these options in the temp-schema environment if they are already configured in another location.

Note that the above option list deliberately excludes options relating to schema names (schema, temp-schema) or workspace configuration (workspace, temp-schema-threads, etc). To configure these options, you must do so in the original target environment, not the temp-schema environment. This design permits environment re-use and avoids ambiguous/conflicting workspace configurations.

If the hosts require different password values, consider using environment variables to avoid placing plaintext passwords in .skeema files. Alternatively, you can indicate that the temp-schema environment needs to re-prompt on STDIN interactively for a different password by including a bare “password” line (with no =value) in the environment’s configuration.

If multiple hosts are configured by the temp-schema environment – either via a comma-separated host value, or a host-wrapper script which emits multiple hosts – then the first successfully-reachable host will be used for the temp-schema. This functionality may be leveraged as a mechanism for high availability.

You should generally ensure that the database server for the temp-schema environment closely matches the real target environment, in terms of vendor (MySQL vs MariaDB), version series, and server options that affect DDL such as sql_mode and innodb_strict_mode. Skeema will emit a non-fatal warning if the servers differ in flavor, or a fatal error if the servers differ in lower_case_table_names, but no other validations are performed. If needed, you can override any differing session-scoped variables by setting connect-options in the temp-schema environment’s configuration.

The temp-schema-environment option only takes effect when using the default of workspace=temp-schema, rather than workspace=docker.

temp-schema-threads

Commandsdiff, push, pull, lint, format
Default5
Typeint
RestrictionsMust be a positive integer

With workspace=temp-schema, this option controls the concurrency level for CREATE queries when populating the workspace, as well as DROP queries when cleaning up the workspace.

When using Skeema in situations involving high object counts (hundreds or thousands of tables and routines in a single schema) and/or nontrivial latency between Skeema and the database server (running Skeema locally against a database server in a remote data center), increasing this value may improve Skeema’s performance.

In other cases, it may be beneficial to lower this value. Some high-volume OLTP workloads are especially sensitive to contention for InnoDB’s dict_sys mutex, meaning that the default concurrency level of 5 can cause other application queries to pile up or stall. This mutex contention is more prevalent in pre-MySQL 8.0 systems, especially if there are INSERTs to table(s) lacking a primary key.

In either situation, also consider use of workspace=docker as an alternative solution.

As of Skeema v1.5, this setting is ignored for workspace cleanup DROP TABLE statements if the database server’s innodb_buffer_pool_size is over 32GB and the server isn’t running MySQL 8.0.23+ or Percona Server 8.0.23+. In this case, DROP TABLEs will be executed sequentially (1 thread) regardless of temp-schema-threads, to avoid risk of stalls. MySQL 8.0.23 contains a major fix to DROP TABLE performance with large buffer pools, which alleviates the risk of stalls.

Regardless of database version or buffer pool size, all other temp-schema workspace statements (all CREATEs, as well as non-table DROPs) still respect temp-schema-threads.

update-partitioning

Commandspull
Defaultfalse
Typeboolean
Restrictionsnone

By default, when updating a CREATE TABLE statement that already exists in the filesystem, skeema pull ignores any database-side changes to the PARTITION BY clause of the table definition. This behavior is designed to help in these scenarios:

  • When running skeema pull against a production environment with external partition management automation/crons, it is undesirable to pollute the commit history with frequent changes to tables' partition lists. (This is conceptually similar to the default behavior of filtering out changes to auto_increment counters.)
  • When running skeema pull against a development environment that intentionally omits partitioning (via partitioning=remove in a .skeema file affecting diff/push), it is typically preferable to leave any PARTITION BY clauses from the *.sql files in-place, despite the development environment tables lacking partitioning.

To override this default behavior for situations in which you do intentionally want to update PARTITION BY clauses in *.sql files, to literally reflect whatever is currently used in the database, enable update-partitioning for example via skeema pull --update-partitioning.

This option may not work properly when combined with --skip-format; some tables that only have partitioning-related changes will not be updated when combining these options.

update-seed-inserts

Commandspull
Defaultfalse
Typeboolean
RestrictionsNot available in the Community edition of Skeema

The Premium edition of the Skeema CLI supports optional execution of INSERT statements by skeema push, either when the target table is empty (inserts=seed) or always (inserts=all). There are several ways to populate INSERTs in your *.sql files, as described in the inserts option. One approach is to use “seed views”, which allow you to define SELECT queries that skeema pull uses to dump INSERT statements for seed data. The update-seed-inserts option enables this behavior.

In Skeema, a seed view is any CREATE VIEW statement where the view name begins with “_seed_”, followed by a table name. As an example, consider the following view definition:

CREATE VIEW _seed_app_users AS SELECT * FROM app_users WHERE user_id < 100;

Skeema would consider this to be a seed view, defining which seed data to dump for the table `app_users`. When running skeema pull, if this view definition is present in a .sql file and update-seed-inserts is enabled, Skeema will automatically run the query from `_seed_app_users`, convert that row data into an INSERT statement, and write it to the end of app_users.sql – or, if there was already an INSERT statement there, update its contents to reflect the current data.

The usage of update-seed-inserts depends on your preferred workflow for INSERT statements and the source of the seed data. A few possibilities include:

  • Obtaining seed data using a subset of production data: enable update-seed-inserts in the [production] section of a .skeema file. Your seed views will typically involve WHERE clauses which define what subset of the row data to dump on pull, and your SELECT clauses might use string manipulation functions to mask sensitive data if desired. Running skeema pull production will update CREATE as well as INSERT statements; pulling from other environments will only update CREATE statements.

  • Maintaining a canonical set of seed data in one database environment, such as considering the “staging” DB to be the source-of-truth for dumping seed data: enable update-seed-inserts in that environment section in a .skeema file. In this case, typically each seed view will be of the simple form CREATE VIEW _seed_foo AS SELECT * FROM foo; which dump the entirety of the table. Running skeema pull staging (for example) will update CREATE as well as INSERT statements; meanwhile pulling from other environments will only update CREATE statements.

  • Updating seed data ad hoc: enable update-seed-inserts on the command-line when needed, for example skeema pull production --update-seed-inserts.

When update-seed-inserts is enabled, its exact behavior is as follows: skeema pull looks in the *.sql files for view definitions where the view name begins with “_seed_”. For each such view definition that is found, skeema pull will take the following actions:

  1. Create the view on the target database (replacing it if it already exists) using the filesystem definition.
    • Note that this is the only case where skeema pull treats the filesystem CREATE VIEW definition as its source-of-truth, rather than the live database’s definition. It is also the only case where skeema pull modifies the live database schema.
    • This design decision is based on the notion of treating the filesystem view definition as a declarative mechanism for mapping a result-set to a table name. In a sense, the seed views are representing configuration metadata for the pull operation, rather than expressing a desired state for the schema.
    • Favoring the filesystem definition also avoids problems with the DBMS normalizing view definitions. For example, the filesystem definition can use SELECT * and this will reflect all current columns dynamically, whereas view definitions on the database side become “frozen” at creation time by the DBMS.
  2. Query all row data exposed by the view, e.g. SELECT * FROM _seed_foo
  3. Convert the data into an INSERT statement
    • The table’s name will be whatever followed “_seed_” in the view’s name; for example view “_seed_foo” would emit INSERT INTO `foo` ....
    • The column names in the INSERT statement will match whatever columns were found in the view’s definition.
  4. Write the INSERT statement to the filesystem
    • If there is no INSERT statement for this table yet in the directory, the new INSERT statement will be written to the same file as the table’s definition. In the previous example of view “_seed_foo” and table “foo”, by default this would write the INSERT to foo.sql. You may manually move the statement to a different file if you wish.
    • If there was already a single INSERT statement for this table in the directory, its text will be replaced with that of the new INSERT statement.
    • If there were multiple INSERT statements for this table in the directory, a warning will be logged and none of the statements will be modified.

Seed views may be defined anywhere within a directory’s *.sql files: you may place them all in a single file, or place them in the same file as each corresponding table definition, or place each of them in their own file. Seed view definitions are always manually maintained: commands such as skeema init, skeema pull, and skeema format will not ever export or update CREATE VIEW statements if the view name begins with “_seed_”.

This feature currently has several minor shortcomings, which will likely be improved in future Skeema releases:

  • As a boolean, the operation of update-seed-inserts is currently “all or nothing”, operating on all or none of your seed views. If you only wish to update a subset of your tables' INSERT statements, simply use git restore (or similar) to discard unwanted changes from your working directory.
  • In order to create or update the seed view definition in the live database, Skeema’s database user needs the CREATE VIEW privilege, along with the DROP privilege. In future releases, skeema pull may be able to just run the raw SELECT from the view if these privileges are missing.
  • Currently, this feature only emits standard INSERT statements. If you need INSERT IGNORE or perhaps INSERT … ON DUPLICATE KEY UPDATE, you will need to manually edit the generated statements after each pull.
  • This feature does not yet do any manipulation of SELECT * clauses. If your table uses generated columns, you must manually list columns in your seed view’s SELECT, excluding the generated ones.

update-views

Commandspull, lint, format
Defaultfalse
Typeboolean
RestrictionsNot available in the Community edition of Skeema

Several Skeema commands rewrite all CREATE statements in *.sql files using the canonical format returned by the database’s SHOW CREATE output. This is beneficial for most object types (tables, procs, funcs, triggers), but is often undesirable for views, since their canonical format places the entire query on one line and strips inline SQL comments. For this reason, by default these commands won’t rewrite CREATE VIEW statements unless the update-views option is enabled. The exact impact varies by command:

skeema format: CREATE VIEW statements are only reformatted if update-views is enabled. Otherwise, views are ignored entirely.

skeema lint with --format (which is enabled by default): CREATE VIEW statements are only reformatted if update-views is enabled. Otherwise, views will not be reformatted, but they are still linted.

skeema lint with --skip-format: The update-views option has no effect, since in this case CREATE statements are not reformatted at all, for any object type.

skeema pull with --format (which is enabled by default): Existing CREATE VIEW statements in the filesystem are only updated if update-views is enabled. Otherwise, existing views will not be updated, even if the database definition has structural differences from the filesystem definition. Regardless of update-views, new views (which exist in the DB but not the FS yet) are always written to the filesystem, and dropped views (which still exist in the FS but are no longer in the DB) are always removed from the filesystem.

skeema pull with --skip-format: The update-views option has no effect. Views are treated the same as any other object type: a full diff between the FS and DB is run using a workspace, and existing views are updated if their canonical form in the database is different than the converted canonical form of the filesystem definition.

As of Skeema v1.10.0-premium, views with names beginning with “_seed_” are never updated by any of these commands, regardless of the update-views option. See update-seed-inserts for information regarding seed views.

This option is not available in the Community edition of Skeema, which does not support views. All views are completely ignored by the Community edition of Skeema.

user

Commandsall
Default“root”
Typestring
Restrictionsnone

Specifies the name of the MySQL user to connect with.

In option files, you may supply the value as an environment variable beginning with Skeema v1.9.0.

verify

Commandsdiff, push
Defaulttrue
Typeboolean
Restrictionsnone

This option controls whether generated ALTER TABLE statements are automatically verified for correctness. If true, each generated ALTER will be tested in a temporary workspace schema. See the FAQ for more information.

This option is enabled by default. To disable it, use --skip-verify on the command-line or skip-verify in an option file. Disabling this option can improve performance of skeema diff and skeema push.

Even if this option is disabled, in some cases Skeema will still verify certain generated ALTER TABLE statements for correctness. This occurs whenever a table uses unsupported features, which require extra care in ensuring the correct introspection of the desired modifications to the table.

workspace

Commandsdiff, push, pull, lint, format
Default“temp-schema”
Typeenum
RestrictionsRequires one of these values: “temp-schema”, “docker”

In order to accurately introspect the CREATE statements in your *.sql files, Skeema executes them in a temporary location, referred to as a “workspace”. The following commands use workspaces:

The workspace option controls where workspaces are created: either as a temporary schema on each real database server (workspace=temp-schema), or inside of a dedicated ephemeral Docker container (workspace=docker).

With the default value of workspace=temp-schema, workspaces function as follows:

  • The workspace schema is created on each database server that Skeema interacts with, based on your configuration (host option) and the environment name specified on the command-line.
    • For example, skeema diff staging will connect to your configured staging server, run your *.sql CREATEs in a temporary workspace schema there, and then compare the resulting objects to the corresponding “real” schema on that same server.
    • In the Premium edition, the temp-schema-environment option may be used to configure an alternative host for the temp-schema workspace, instead of placing it on each database instance that Skeema interacts with.
  • The workspace schema name defaults to “_skeema_tmp”, but may be configured using the temp-schema option.
  • When the operation completes, Skeema automatically cleans up and drops the workspace schema in a careful fashion.
  • See the temp-schema-threads option to control the level of concurrency for temp-schema workspace operations.
  • See the temp-schema-binlog option to control whether temp-schema workspace queries are written to the server’s binary log.

In contrast, if you configure workspace=docker, a Docker container on localhost is used for the workspace instead. This can be advantageous for three reasons:

  1. Safety: If you want to avoid risk of the workspace impacting production workloads in any way. For example, prior to MySQL 8.0.23, whenever innodb_buffer_pool_size is large, DROP TABLE can impact database performance even if the table being dropped is empty. This means the cleanup step of workspace=temp-schema can impact other queries, especially if the number of tables is high.
  2. Security: If you want to avoid giving Skeema extensive privileges, or otherwise want to avoid creating and dropping the temporary schema on live databases.
  3. Performance: If the machine you’re running Skeema from is in a different region/datacenter than your database, interacting with a container on localhost will avoid the network latency penalty, especially when your schemas contain a large number of tables.

Using workspace=docker requires a Docker Engine daemon, reachable at /var/run/docker.sock on the same machine as Skeema. As of Skeema v1.11.0, it also requires a docker CLI binary in your PATH.

The containers managed by workspace=docker have the following properties:

  • The container image will be based on the flavor option specified for the corresponding database instance, ensuring that the workspace behavior matches the live database. For example, when interacting with a live database running Percona Server 5.7 (flavor=percona:5.7), the local container will use image “percona:5.7”.
    • In the Premium edition CLI, with Aurora flavors, vanilla MySQL images will be substituted automatically (e.g. flavor=aurora:5.7 uses image “mysql:5.7”) without harming Aurora-specific behaviors.
    • On an ARM system, such as a Mac with an Apple Silicon CPU, some database images are not available for the arm64 architecture: mysql:5.5, mysql:5.6, mysql:5.7, and percona (any version). When using workspace=docker on an ARM system with these flavor values, a warning will be logged, and the “mysql:8.0” image will be substituted instead. This may cause incorrect behavior if your schema relies on pre-8.0 defaults, or Percona Server’s column compression feature. This limitation does not affect MariaDB flavors, since arm64 builds are available for MariaDB 10.1+.
  • The container name follows a template based on the image. For example, with flavor=percona:5.7, the container will be called “skeema-percona-5.7”.
  • The containerized DB server will only listen on the 127.0.0.1 localhost loopback interface, to ensure that external machines cannot communicate with it.
  • The containerized DB server will have an empty root password.
  • The vast majority of global variables of the containerized DB server are left at their defaults for the corresponding version/flavor. This may cause divergent behavior if your live databases configure non-default global settings that affect DDL, such as innodb_strict_mode, explicit_defaults_for_timestamp, innodb_large_prefix, innodb_file_format, among others. Many of these may be set at the session level via connect-options; such settings will be used for Skeema’s sessions on the workspace container as well as live databases.
    • As of Skeema v1.10.1, your real database server’s sql_mode will automatically be applied to sessions on the containerized DB server. This behavior avoids common problems with strict sql_mode mismatches: Docker images for all modern MySQL and MariaDB versions default to using a strict sql_mode, but database-as-a-service platforms such as RDS / Aurora use a non-strict / blank sql_mode instead.
    • In older versions of Skeema, if your real database server uses a different sql_mode than the default listed in your database server’s documentation, you must manually configure sql_mode in connect-options to match that of your real database server.

Skeema dynamically manages containers and images as needed. If a container with the expected name does not already exist in the local Docker engine, it will be created on-the-fly. If the necessary image tag also is not already present in the local Docker engine, the image will be fetched from DockerHub automatically. These steps may take some time upon first use of workspace=docker.

If needed, you can circumvent some of these automatic behaviors by taking Docker-related actions outside of Skeema. For example, if your company uses a different container registry than DockerHub, you could fetch or manually tag another image with the expected name.

By default, containers remain running after Skeema exits, to avoid the performance hit of recreating or restarting containers for subsequent invocations. This behavior is configurable using the docker-cleanup option.

The workspace=docker option generally works properly even if cross-schema foreign keys are present. However, it cannot be used with cross-schema views, as the database will throw an error for the invalid schema reference inside the CREATE VIEW. (Note that the Community edition of Skeema does not support views, so this limitation only affects the premium edition.) You may work around this problem by using ignore-view, or consider using temp-schema-environment instead of workspace=docker to offload the workspace to a configurable location which already has the necessary referenced objects.

If skeema itself is also being run in a Docker container, use of workspace=docker is difficult: you must either bind-mount the host’s Docker socket into Skeema’s container and install a docker CLI in the container; or use a privileged Docker-in-Docker (dind) image. Each choice has trade-offs involving operational complexity and security. For more information, see GitHub issue #89. As an alternative, in the Premium Edition, consider using workspace=temp-schema with temp-schema-environment to offload the workspace to a configurable location.

write

Commandsformat
Defaulttrue
Typeboolean
Restrictionsnone

If true, skeema format will rewrite .sql files to match the canonical format shown in MySQL’s SHOW CREATE. If false, this step is skipped. Either way, the command’s exit code will be non-zero if any files contained statements that were not already in the canonical format.

This option is enabled by default. To disable file writes in skeema format, use --skip-write on the command-line. This may be useful in CI pipelines that verify proper formatting of commits, to enforce a strict style guide.