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
• allow-charset
• allow-compression
• allow-definer
• allow-engine
• allow-pk-type
• allow-unsafe
• alter-algorithm
• alter-lock
• alter-validate-virtual
• alter-wrapper
• alter-wrapper-min-size
• brief
• compare-metadata
• concurrent-instances
• connect-options
• ddl-wrapper
• debug
• default-character-set
• default-collation
• dir
• docker-cleanup
• dry-run
• exact-match
• first-only
• flavor
• foreign-key-checks
• format / skip-format
• generator
• host
• host-wrapper
• ignore-event
• ignore-func
• ignore-proc
• ignore-schema
• ignore-table
• ignore-trigger
• ignore-view
• include-auto-inc
• inserts
• lax-column-order
• lax-comments
• lint / skip-lint
• lint-auto-inc
• lint-charset
• lint-compression
• lint-definer
• lint-display-width
• lint-dupe-index
• lint-engine
• lint-fk-parent
• lint-has-enum
• lint-has-event
• lint-has-fk
• lint-has-float
• lint-has-routine
• lint-has-time
• lint-has-trigger
• lint-has-view
• lint-name-case
• lint-pk
• lint-pk-type
• lint-reserved-word
• lint-zero-date
• my-cnf / skip-my-cnf
• new-schemas /
  skip-new-schemas
• partitioning
• password
• port
• safe-below-size
• schema
• server-public-key-path
• socket
• ssh
• ssh-to-db
• ssl-ca
• ssl-cert
• ssl-key
• ssl-mode
• ssl-verify-server-cert
• strip-definer
• strip-partitioning
• temp-schema
• temp-schema-binlog
• temp-schema-environment
• temp-schema-threads
• update-partitioning
• update-seed-inserts
• update-views
• user
• verify / skip-verify
• workspace
• write / skip-write

allow-auto-inc

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. Use of these types for auto_increment columns was deprecated in MySQL 8.0.17, and removed entirely in MySQL 8.4.0.

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

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 by allow-charset. 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, using the more-specific “utf8mb3” name is recommended for future-proofing your configuration.

allow-compression

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

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” (its default) or “warning”. If so, an error or warning (respectively) will be emitted for any definition which specifies a DEFINER not matched by any value in this list.

The default value for allow-definer is intentionally permissive of all possible DEFINER users. You must override this option if you wish to restrict what DEFINER users are permissible.

For information on the purpose of this option and examples of how to configure it, see linting definers.

In the Community edition of Skeema, views and triggers are ignored, 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 Skeema’s user does not have sufficient privileges to set users.

allow-engine

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

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

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.

For a list of unsafe operations, refer to the unsafe change detection section of the documentation. If allow-unsafe is set to true, these operations are fully permitted, for all objects.

It is not recommended to permanently enable this setting, 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

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

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

This option adds a WITH VALIDATION clause to any generated ALTER TABLE which affects virtual columns. This clause tells a MySQL 5.7+ 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.

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

This option causes skeema push to shell out to an external process, such as pt-online-schema-change, gh-ost, or spirit, 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 configured external command. Meanwhile the output of skeema diff will display what command-line would be executed, but it won’t actually be run.

For information on using this option, refer to the external online schema change tools documentation.

alter-wrapper-min-size

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

Sizes may be specified with a suffix of “K”, “M”, or “G” to multiply the preceding number by 1024, 1024^2, or 1024^3. See Size Option Type in the configuration guide.

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

Ordinarily, skeema diff outputs DDL statements to STDOUT. With brief, skeema diff will instead only output a newline-delimited list of unique DB servers (host:port) that had at least one difference. This can be useful in any environment with multiple database hosts, to see which 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 DB servers 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. In Skeema Premium, use of brief ignores INSERT statements, effectively forcing inserts=none in its behavior.

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

In MySQL and MariaDB, whenever a procedure, function, trigger, or event 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 Skeema’s current session 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 and events are completely ignored, and this option only impacts procedures and functions.

concurrent-instances

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

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

connect-options

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.

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

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:

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

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

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 DB server’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 DB server, an appropriate ALTER DATABASE statement will be generated.

default-collation

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 DB server’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 DB server, an appropriate ALTER DATABASE statement will be generated.

dir

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

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

For information on usage of this option, refer to the Docker workspace container lifecycle documentation.

dry-run

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

Ordinarily, skeema diff and skeema push ignore certain cosmetic table differences which have no functional impact in the database server:

• If a table’s .sql file lists its secondary indexes in a different order than the live database server table, this difference is normally ignored. Otherwise, Skeema would need to drop and re-add the indexes to match the order in the .sql file, which may be slow and disruptive.
  • Note: this refers to the relative ordering of entire index definitions for a table with multiple secondary indexes, not the ordering of columns within a single index. Column ordering within an index is always functional, not cosmetic.
  • There is one situation in InnoDB when the relative ordering of secondary indexes has a functional impact: tables with no explicit primary key, but multiple unique indexes over all non-nullable columns; the first such index effectively becomes the de facto primary key. In this case, Skeema will automatically respect relative index ordering, regardless of this option.
• If a table’s .sql file has foreign keys with the same definition, but different name than on the live database server, this difference is normally ignored to avoid needlessly dropping and re-adding the foreign keys. This provides better compatibility with pt-online-schema-change, which needs 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. 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.
• Similar to foreign keys, name changes for check constraints are similarly ignored by default (as of Skeema v1.12.1), because OSC tools need to manipulate their names in order to function.
• In MariaDB (and not MySQL), within SHOW CREATE TABLE a table’s non-column-inline check constraints are sorted based on their order of creation. Similar to secondary indexes, this relative ordering is purely cosmetic, and is ignored by default.

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 table definition in the database server exactly match the corresponding table definition specified in the .sql file.

Be aware that the database server itself sometimes also suppresses attempts to make cosmetic changes to a table’s definition! For example, the server 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 server side, but it may be slow for large tables.

first-only

Ordinarily, for individual directories that map to multiple hosts and/or multiple schemas, skeema diff and skeema push will operate on all mapped hosts, and all mapped schemas on those hosts. If the first-only option is used, these commands instead only operate on the first emitted host/schema pair 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. Refer to the canary shards documentation for details.

flavor

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.34”.

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

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:

• With Docker workspaces, the flavor value controls what Docker image is used for workspace containers.

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

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

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 container in preparation of an upgrade, you could run skeema lint --flavor=mysql:8.0 --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 older 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 Docker workspaces or in Skeema Cloud Linter, a “mysql:8.0” image is used if the flavor is “aurora:8.0”. Full Aurora table diff support will still work properly in this situation, as long as your schema definitions are valid in standard MySQL.

foreign-key-checks

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

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

When first running skeema init, the generated host-level .skeema file will automatically record the Skeema version and edition that created the directory structure. Subsequent use of 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

This option 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.

In .skeema option files, you may supply the value as an environment variable in Skeema v1.9+, for example host=$MY_DB_HOSTNAME. 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, with a warning message logged.

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. Refer to the documentation section covering dynamic shell-outs for shard mapping.

In all cases, the specified host(s) should always be writable (primary / master) DB servers, never replicas.

host-wrapper

This option controls how the host option is interpreted, and can be used to allow Skeema to interface with service discovery systems and/or dynamically map a directory to any number of database server shards.

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 servers, it will execute the external command to determine which hosts to operate on, instead of using host as a literal value.

For information on using this option, refer to the documentation section covering dynamic shell-outs for shard mapping.

ignore-event

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

For information on use of this option, refer to the ignore regex options documentation page.

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

ignore-func

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. For information on use of this option, refer to the ignore regex options documentation page.

This option was first introduced in the Premium Edition in Skeema v1.6, and also became available in the Community Edition beginning with v1.9.

ignore-proc

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. For information on use of this option, refer to the ignore regex options documentation page.

This option was first introduced in the Premium Edition in Skeema v1.6, and also became available in the Community Edition beginning with v1.9.

ignore-schema

If you wish to have Skeema ignore some specific “scratch” database schemas, this option allows configuration of a regular expression which is matched against schema names to ignore. For information on use of this option, refer to the ignore regex options documentation page, and especially the ignoring entire schemas section.

ignore-table

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. This is especially useful for ignoring “shadow tables” created during the operation of an external online schema change tool.

For information on use of this option, refer to the ignore regex options documentation page.

ignore-trigger

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 information on use of this option, refer to the ignore regex options documentation page.

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

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.

For information on use of this option, refer to the ignore regex options documentation page.

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

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

The inserts option controls how skeema diff and skeema push process INSERT statements found among your *.sql files:

• inserts=seed (default): 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.
• inserts=all: all INSERT statements are processed on every skeema diff or skeema push invocation, regardless of table emptiness.
• inserts=none: no INSERT statements are processed. This is effectively how the Community Edition operates. The alias skip-inserts may also be used equivalently.

For more information on using this option, refer to the seed data documentation.

lax-column-order

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

Ordinarily, when diffing a table, routine, or event, 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.
• Event-level COMMENT clause differences are ignored if there are no other relevant changes in the event definition.

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

lint

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 target (host/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

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

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

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

This linter rule specifies the annotation severity of non-allowed DEFINER values for stored procedures, functions, views, triggers, and events. 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. For information on the purpose of these options and examples of how to configure them, see linting definers.

In the Community edition of Skeema, views, triggers, and events are ignored, 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 Skeema’s user does not have sufficient privileges to set users.

lint-display-width

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

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 all of the same columns in the same order. In this case, the larger index (including more columns) is usually preferable to the smaller index, and you should evaluate whether the smaller index can be dropped.

However, in some edge cases, a seemingly-redundant non-unique BTREE index can optimize queries better than a larger index. These query patterns typically take advantage of InnoDB’s behavior of inherently including the primary key columns inside of all BTREE secondary indexes: for example, a query with a WHERE clause using all columns of the smaller (seemingly-redundant) index, and then an ORDER BY on the table’s primary key. Skeema’s linter cannot examine your query patterns to detect these scenarios, so proceed with caution before dropping a redundant index. When in doubt, consider making the index INVISIBLE (MySQL 8.0+) or IGNORED (MariaDB 10.6+) for some time before dropping it.

Unique BTREE indexes are not considered redundant to other indexes, 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.

lint-engine

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-fk-parent

This linter rule examines the parent (referenced) table of each same-schema foreign key constraint. If the parent table is missing, or exists but lacks a UNIQUE KEY corresponding to the referenced columns, a linter annotation will be emitted.

In terms of DDL syntax, foreign key definitions are always placed within the “child” side CREATE TABLE statement. Because skeema push ordinarily operates with foreign key checks disabled by default, the database server won’t automatically verify that the referenced parent side table exists. However, if the parent table remains missing, INSERTs to the child table are prohibited by the server. This linter rule helps to detect this situation.

Additionally, this linter rule confirms that the parent table has a UNIQUE KEY which exactly matches the referenced columns in the foreign key definition, as required by the SQL standard. Historically, MySQL and MariaDB have been more lenient about this behavior, allowing use of a non-unique parent-side index, and/or an index that contains additional columns beyond those referenced in the foreign key. However, MySQL 8.4 has deprecated that behavior, because lack of a uniqueness constraint on an FK parent can break multi-threaded group replication. A future MySQL version will strictly require an exactly-matching UNIQUE KEY on the parent side table.

This linter rule only checks foreign keys where the parent and child tables are in the same schema (database). Because Skeema only operates on one schema at a time, cross-schema foreign key constraints are completely ignored by this rule.

lint-has-enum

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 a numeric offset in the list of allowed values.

lint-has-event

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

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

• The event scheduler is not enabled by default prior to MySQL 8.0, nor in any version of MariaDB. If the event scheduler is disabled, events can still be created, but they will silently not be executed. Enabling the event scheduler requires administrative privileges.
• Events can be difficult to debug, since any failure log messages are only written to the database server error log.
• When using replication for high availability, upon a failover scenario (promoting a replica to become the new primary), all events on the new primary must be explicitly re-enabled.
• Event dates suffer from the Y2K38 problem: you cannot use values that exceed January 19, 2038 for the STARTS, ENDS, or AT clauses of an event.
• Events are not included in the SQL Standard, which means they can be a source of portability concerns when building software that must support other database systems (besides MySQL and MariaDB).

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

lint-has-fk

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.

Users of MySQL 9.0+ should be aware that REFERENCES clauses which are inline to a column definition now actually create foreign key constraints. These clauses were previously parsed but ignored entirely in all older versions of MySQL, as well as all versions of MariaDB. These inline REFERENCES clauses were previously useful as documentation about database relationships, for companies who are otherwise deliberately avoiding creation of foreign key constraints. This documentation-only use-case is no longer possible in MySQL 9.0+, and lint-has-fk will detect such foreign keys if the workspace server is running MySQL 9.0+.

lint-has-float

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

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

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. (This issue has recently been fixed in MariaDB 11.5+, but has not been solved yet in MySQL.)
• 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

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

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

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

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

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

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 server release supported by your version of Skeema), 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, see the recipe for combining Docker workspaces with a command-line flavor override.

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 names of views, triggers, and events. 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

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 Docker workspaces, upgrade to the latest Skeema version. As of Skeema v1.10.1, connections to a Docker workspace database automatically mirror your real database server’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

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

If true, skeema pull will look for schemas (databases) that exist on the DB server, 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

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

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 in Skeema v1.9+.

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+ offers 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

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 in Skeema v1.9+.

safe-below-size

For any table below the specified size (in bytes), Skeema will allow execution of unsafe operations, even if allow-unsafe has not be enabled.

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. Setting safe-below-size=1 also inherently permits renames on empty tables, albeit through use of drop-and-re-create.

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 much lower value in the production environment. This way, whenever unsafe operations are to be run on a non-trivially-small 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

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 server. In this situation, only a single subdirectory is created, rather than a subdirectory for the host 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/
• 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 static schema name.

The ability to specify multiple schema names is useful in sharded environments with multi-tenancy: each DB server contains several schemas with the same set of tables, and therefore each schema change needs to be applied to multiple schemas on a the same database server. See the documentation on mapping a directory to multiple schemas per server for an explanation of how to use these multi-schema-name configurations.

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. Additionally, system schemas are always ignored automatically; these include information_schema, performance_schema, mysql, sys, and test.

server-public-key-path

This option can improve security when connecting to a MySQL database server over an unencrypted connection. This option’s behavior is analogous to the corresponding --server-public-key-path option of the mysql command-line client.

For information about this option, refer to the documentation on using certificate options and secure password exchange without SSL/TLS.

socket

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 in Skeema v1.9+.

ssh

Skeema Premium v1.8+ 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).

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.

For full information on configuring SSH tunnels and using this option, refer to the SSH tunnel section of the Skeema manual.

ssh-to-db

Skeema Premium v1.8+ 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).

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

For full information on configuring SSH tunnels and using this option, refer to the SSH tunnel section of the Skeema manual.

ssl-ca

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.

For information about this option, refer to the documentation on using certificate options and certificate authorities.

ssl-cert

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.

For information about this option, refer to the documentation on using certificate options and client-side certificates.

ssl-key

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.

For information about this option, refer to the documentation on using certificate options and client-side certificates.

ssl-mode

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+ and MariaDB 11.4+ outside of DBaaS environments. This value is not supported in the Community edition of the Skeema CLI.

ssl-verify-server-cert

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 command-line client from MariaDB, as well as pre-5.7 versions of MySQL. These versions of the mysql / mariadb client have an ssl-verify-server-cert setting instead of the 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.

strip-definer

All stored procedures, functions, views, triggers, and events 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.0 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 include functionality to strip DEFINER clauses. By default, this functionality is enabled automatically whenever Skeema’s user lacks elevated privileges. For more information on this functionality and the strip-definer option, see stripping DEFINER clauses.

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

strip-partitioning

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:

• When the format option is enabled (as it is by default), if strip-partitioning is enabled, PARTITION BY clauses will be stripped from all tables.
• When the format option is disabled (e.g. skeema pull --skip-format), if strip-partitioning is enabled, only new or modified tables will have PARTITION BY clauses stripped.
• If the update-partitioning option is enabled, it takes precedence over strip-partitioning, meaning that strip-partitioning has no effect in this situation.
• When using skeema pull by default, e.g. without either update-partitioning or strip-partitioning options, existing table definitions will not get any updates to their PARTITION BY clause, or lack of such a clause. This behavior is designed to facilitate situations where partitioning is only used in some environments (such as production and staging) but not in other environments (such as development).

temp-schema

This option specifies the name of the temporary schema to use for Skeema workspace operations.

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 in Skeema v1.9+.

temp-schema-binlog

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

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 server 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 Docker workspaces, especially the desire to avoid disruption to busy production databases. However, while Docker workspaces involve using automatically-managed ephemeral containerized database servers, 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 Docker workspaces problematic.

For usage of this option, refer to the temp-schema offloading section of the workspaces documentation.

The temp-schema-environment option only takes effect when using workspace=temp-schema. It has no effect if workspace=docker is in-use.

temp-schema-threads

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. Although the server will typically execute all DDL serially under the hood, sending concurrent queries can improve Skeema’s performance by queueing up multiple operations at once, instead of waiting for network round-trips.

When using Skeema in situations involving high object counts (hundreds or thousands of tables and routines in a single schema) and high network latency between Skeema and the database server (running Skeema locally against a database server in a remote data center), increasing this value slightly may improve Skeema’s performance. This requires slow adjustment and careful tuning to ensure the new value is not too high. Alternatively, instead of raising temp-schema-threads, consider using either Docker workspaces or temp-schema offloading to side-step network latency issues.

If you are running Skeema in the same data center / availability zone as the database server, lowering temp-schema-threads is recommended in order to reduce unnecessary contention. In this case, try a value of 1 or 2.

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 tables that lack a primary key. In this situation, set temp-schema-threads to 1 in order to effectively disable concurrency, or consider either Docker workspaces or temp-schema offloading.

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+. In this case, DROP TABLEs will be sent sequentially (1 thread) regardless of temp-schema-threads. MySQL 8.0.23+ contains a major fix to DROP TABLE performance with large buffer pools. Older server versions can show performance issues when dropping tables, even when the tables are empty, as is the case for workspace cleanup.

update-partitioning

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

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 seed data documentation.

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, which is not enabled by default.

For information on using this option, refer to the seed data feature documentation.

update-views

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.

In Skeema v1.10+, 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

This option specifies the name of the database user to connect with.

In option files, you may supply the value as an environment variable in Skeema v1.9+.

verify

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. This confirms that running the generated DDL against an empty copy of the old (live) table definition correctly yields the expected new (from filesystem/repo) table definition. If verification fails, Skeema aborts with a fatal error.

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

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 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). For information on usage of this option, refer to the workspaces documentation.

In Skeema Premium, temp-schema workspaces can be configured to be “offloaded” to any arbitrary pre-existing database. See temp-schema offloading.

write

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.