Server System Variables

You are viewing an old version of this article. View the current version here.

About the Server System Variables

MariaDB has many system variables that can be changed to suit your needs.

The full list of server variables are listed in the contents on this page, and most are described on this page, but some are described elsewhere:

Most of these can be set with command line options and many of them can be changed at runtime.

There are a few ways to see the full list of server system variables:

While in the mysql client, run:

SHOW VARIABLES;

See SHOW VARIABLES for instructions on using this command.

From your shell, run mysqld like so:

mysqld --verbose --help

View the Information Schema GLOBAL_VARIABLES, SESSION_VARIABLES, and SYSTEM_VARIABLES tables.

Setting Server System Variables

There are several ways to set server system variables:

Specify them on the command line:

shell> ./mysqld_safe --aria_group_commit="hard"

Specify them in your my.cnf file (see Configuring MariaDB with my.cnf for more information):

aria_group_commit = "hard"

Set them from the mysql client using the SET command. Only variables that are dynamic can be set at runtime in this way. Note that variables set in this way will not persist after a restart.

SET GLOBAL aria_group_commit="hard";

By convention, server variables have usually been specified with an underscore in the configuration files, and a dash on the command line. You can however specify underscores as dashes - they are interchangeable.

Variables that take a numeric size can either be specified in full, or with a suffix for easier readability. Valid suffixes are:

Suffix	Description	Value
K	kilobytes	1024
M	megabytes	1024²
G	gigabytes	1024³
T	terabytes	1024⁴ (from MariaDB 10.3.3)
P	petabytes	1024⁵ (from MariaDB 10.3.3)
E	exabytes	1024⁶ (from MariaDB 10.3.3)

The suffix can be upper or lower-case.

List of Server System Variables

`alter_algorithm`

Description: The implied ALGORITHM for ALTER TABLE if no ALGORITHM clause is specified. The deprecated variable old_alter_table is an alias for this.
- COPY corresponds to the pre-MySQL 5.1 approach of creating an intermediate table, copying data one row at a time, and renaming and dropping tables.
- INPLACE requests that the operation be refused if it cannot be done natively inside a the storage engine.
- DEFAULT (the default) chooses INPLACE if available, and falls back to COPY.
- NOCOPY refuses to copy a table.
- INSTANT refuses an operation that would involve any other than metadata changes.
Commandline: --alter-algorithm=default
Scope: Global, Session
Dynamic: Yes
Data Type: enumerated
Default Value: DEFAULT
Valid Values: DEFAULT, COPY, INPLACE, NOCOPY, INSTANT
Introduced: MariaDB 10.3.7

`analyze_sample_percentage`

Description: Percentage of rows from the table ANALYZE TABLE will sample to collect table statistics. Set to 0 to let MariaDB decide what percentage of rows to sample.
Commandline: --analyze-sample-percentage=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 100.000000
Range: 0 to 100
Introduced: MariaDB 10.4.3

`autocommit`

Description: If set to 1, the default, all queries are committed immediately. The LOCK IN SHARE MODE and FOR UPDATE clauses therefore have no effect. If set to 0, they are only committed upon a COMMIT statement, or rolled back with a ROLLBACK statement. If autocommit is set to 0, and then changed to 1, all open transactions are immediately committed.
Commandline: --autocommit[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`automatic_sp_privileges`

Description: When set to 1, the default, when a stored routine is created, the creator is automatically granted permission to ALTER (which includes dropping) and to EXECUTE the routine. If set to 0, the creator is not automatically granted these privileges.
Commandline: --automatic-sp-privileges, --skip-automatic-sp-privileges
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 1

`back_log`

Description: Connections take a small amount of time to start, and this setting determines the number of outstanding connection requests MariaDB can have, or the size of the listen queue for incoming TCP/IP requests. Requests beyond this will be refused. Increase if you expect short bursts of connections. Cannot be set higher than the operating system limit (see the Unix listen() man page). If not set, set to 0, or the --autoset-back-log option is used, will be autoset to the lower of 900 and (50 + max_connections/5).
Commandline: --back-log=#
Scope: Global
Dynamic: No
Type: number
Default Value:
- The lower of 900 and (50 + max_connections/5)

`basedir`

Description: Path to the MariaDB installation directory. Other paths are usually resolved relative to this base directory.
Commandline: --basedir=path or -b path
Scope: Global
Dynamic: No
Type: directory name

`big_tables`

Description: If this system variable is set to 1, then temporary tables will be saved to disk intead of memory.
- This system variable's original intention was to allow result sets that were too big for memory-based temporary tables and to avoid the resulting 'table full' errors.
- This system variable is no longer needed, because the server can automatically convert large memory-based temporary tables into disk-based temporary tables when they exceed the value of the tmp_memory_table_size system variable.
- To prevent memory-based temporary tables from being used at all, set the tmp_memory_table_size system variable to 0.
- In MariaDB 5.5 and earlier, sql_big_tables is a synonym.
- In MariaDB 10.5, this system variable is deprecated.
Commandline: --big-tables
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Deprecated: MariaDB 10.5.0

`bind_address`

Description: By default, the MariaDB server listens for TCP/IP connections on a network socket bound to a single address, 0.0.0.0. You can specify an alternative when the server starts using this option; either a host name, an IPv4 or an IPv6 address. In Debian and Ubuntu, the default bind_address is 127.0.0.1, which binds the server to listen on localhost only. bind_address has always been available as a mysqld option, from MariaDB 10.3.3 its also available as a system variable.
Commandline: --bind-address=addr
Scope: Global
Dynamic: No
Data Type: string
Valid Values: Host name, IPv4, IPv6
Introduced: MariaDB 10.3.3 (as a system variable)

`bulk_insert_buffer_size`

Description: Size in bytes of the per-thread cache tree used to speed up bulk inserts into MyISAM and Aria tables. A value of 0 disables the cache tree.
Commandline: --bulk-insert-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 8388608
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520

`character_set_client`

Description: Determines the character set for queries arriving from the client. It can be set per session by the client, although the server can be configured to ignore client requests with the --skip-character-set-client-handshake option. If the client does not request a character set, or requests a character set that the server does not support, the global value will be used. utf16, utf32 and ucs2 cannot be used as client character sets.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: utf8

`character_set_connection`

Description: Character set used for number to string conversion, as well as for literals that don't have a character set introducer.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: utf8

`character_set_database`

Description: Character set used by the default database, and set by the server whenever the default database is changed. If there's no default database, character_set_database contains the same value as character_set_server. This variable is dynamic, but should not be set manually, only by the server.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: latin1

`character_set_filesystem`

Description: The character set for the filesystem. Used for converting file names specified as a string literal from character_set_client to character_set_filesystem before opening the file. By default set to binary, so no conversion takes place. This could be useful for statements such as LOAD_FILE() or LOAD DATA INFILE on system where multi-byte file names are use.
Commandline: --character-set-filesystem=name
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: binary

`character_set_results`

Description: Character set used for results and error messages returned to the client.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: utf8

`character_set_server`

Description: Default character set used by the server. See character_set_database for character sets used by the default database. Defaults may be different on some systems, see for example Differences in MariaDB in Debian.
Commandline: --character-set-server
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: latin1

`character_set_system`

Description: Character set used by the server to store identifiers, always set to utf8.
Scope: Global
Dynamic: No
Data Type: string
Default Value: utf8

`character_sets_dir`

Description: Directory where the character sets are installed.
Commandline: --character-sets-dir=path
Scope: Global
Dynamic: No
Type: directory name

`check_constraint_checks`

Description: If set to 0, will disable constraint checks, for example when loading a table that violates some constraints that you plan to fix later.
Commandline: --check-constraint-checks=[0|1]
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default: ON
Introduced: MariaDB 10.2.1

`collation_connection`

Description: Collation used for the connection character set.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`collation_database`

Description: Collation used for the default database. Set by the server if the default database changes, if there is no default database the value from the collation_server variable is used. This variable is dynamic, but should not be set manually, only by the server.
Scope: Global, Session
Dynamic: Yes
Data Type: string

`collation_server`

Description: Default collation used by the server. This is set to the default collation for a given character set automatically when character_set_server is changed, but it can also be set manually. Defaults may be different on some systems, see for example Differences in MariaDB in Debian.
Commandline: --collation-server=name
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: latin1_swedish_ci

`completion_type`

Description: The transaction completion type. If set to NO_CHAIN or 0 (the default), there is no effect on commits and rollbacks. If set to CHAIN or 1, a COMMIT statement is equivalent to COMMIT AND CHAIN, while a ROLLBACK is equivalent to ROLLBACK AND CHAIN, so a new transaction starts straight away with the same isolation level as transaction that's just finished. If set to RELEASE or 2, a COMMIT statement is equivalent to COMMIT RELEASE, while a ROLLBACK is equivalent to ROLLBACK RELEASE, so the server will disconnect after the transaction completes. Note that the transaction completion type only applies to explicit commits, not implicit commits.
Commandline: --completion-type=name
Scope: Global, Session
Dynamic: Yes
Data Type: enumerated
Default Value: NO_CHAIN
Valid Values: 0, 1, 2, NO_CHAIN, CHAIN, RELEASE

`concurrent_insert`

Description: If set to AUTO or 1, the default, MariaDB allows concurrent INSERTs and SELECTs for MyISAM tables with no free blocks in the data (deleted rows in the middle). If set to NEVER or 0, concurrent inserts are disabled. If set to ALWAYS or 2, concurrent inserts are permitted for all MyISAM tables, even those with holes, in which case new rows are added at the end of a table if the table is being used by another thread.

If the --skip-new option is used when starting the server, concurrent_insert is set to NEVER.

Changing the variable only affects new opened tables. Use FLUSH TABLES If you want it to also affect cached tables.

See Concurrent Inserts for more.
Commandline: --concurrent-insert[=value]
Scope: Global
Dynamic: Yes
Data Type: enumerated
Default Value: AUTO
Valid Values: 0, 1, 2, AUTO, NEVER, ALWAYS

`connect_timeout`

Description: Time in seconds that the server waits for a connect packet before returning a 'Bad handshake'. Increasing may help if clients regularly encounter 'Lost connection to MySQL server at 'X', system error: error_number' type-errors.
Commandline: --connect-timeout=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 10

`core_file`

Description: Write a core-file on crashes. The file name and location are system dependent. On Linux it is usually called core.${PID}, and it is usually written to the data directory. However, this can be changed.
- See Enabling Core Dumps for more information.
- Previously this system variable existed only as an option, but it was also made into a read-only system variable starting with MariaDB 10.3.9, MariaDB 10.2.17 and MariaDB 10.1.35.
- On Windows >= MariaDB 10.4.3, this option is set by default.
- Note that the option accepts no arguments; specifying --core-file sets the value to ON. It cannot be disabled in the case of Windows >= MariaDB 10.4.3.
Commandline: --core-file
Scope: Global
Dynamic: No
Type: boolean
Default Value:
- Windows >= MariaDB 10.4.3: ON
- All other systems: OFF
Introduced: MariaDB 10.3.9, MariaDB 10.2.17, MariaDB 10.1.35

`datadir`

Description: Directory where the data is stored.
Commandline: --datadir=path or -h path
Scope: Global
Dynamic: No
Type: directory name

`date_format`

Description: Unused.

`datetime_format`

Description: Unused.

`debug`

Description: Available in debug builds only (built with -DWITH_DEBUG=1). Used in debugging through the DBUG library to write to a trace file. Just using --debug will write a trace of what mysqld is doing to the default trace file.
Commandline: -#, --debug[=debug_options]
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
- <= MariaDB 10.4: d:t:i:o,/tmp/mysqld.trace (Unix) or d:t:i:O,\mysqld.trace (Windows)
- >= MariaDB 10.5: d:t:i:o,/tmp/mariadbd.trace (Unix) or d:t:i:O,\mariadbd.trace (Windows)
Debug Options: See the option flags on the mysql_debug page

`debug_no_thread_alarm`

Description: Disable system thread alarm calls. Disabling it may be useful in debugging or testing, never do it in production.
Commandline: --debug-no-thead-alarm=#
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced: MariaDB

`debug_sync`

Description: Used in debugging to show the interface to the Debug Sync facility. MariaDB needs to be configured with -DENABLE_DEBUG_SYNC=1 for this variable to be available.
Scope: Session
Dynamic: Yes
Data Type: string
Default Value: OFF or ON - current signal signal name

`default_password_lifetime`

Description: This defines the global password expiration policy. 0 means automatic password expiration is disabled. If the value is a positive integer N, the passwords must be changed every N days. This behavior can be overridden using the password expiration options in ALTER USER.
Commandline: --default-password-lifetime=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 0
Range: 0 to 4294967295
Introduced: MariaDB 10.4.3

`default_regex_flags`

Description: Introduced to address remaining incompatibilities between PCRE and the old regex library. Accepts a comma-separated list of zero or more of the following values:

Value	Pattern equivalent	Meaning
DOTALL	(?s)	. matches anything including NL
DUPNAMES	(?J)	Allow duplicate names for subpatterns
EXTENDED	(?x)	Ignore white space and # comments
EXTRA	(?X)	extra features (e.g. error on unknown escape character)
MULTILINE	(?m)	^ and $ match newlines within data
UNGREEDY	(?U)	Invert greediness of quantifiers

Commandline: --default-regex-flags=value
Scope: Global, Session
Dynamic: Yes
Type: enumeration
Default Value: empty
Valid Values: DOTALL, DUPNAMES, EXTENDED, EXTRA, MULTILINE, UNGREEDY
Introduced: MariaDB 10.0.11

`default_storage_engine`

Description: The default storage engine. The default storage engine must be enabled at server startup or the server won't start.
Commandline: --default-storage-engine=name
Scope: Global, Session
Dynamic: Yes
Type: enumeration
Default Value: InnoDB

`default_table_type`

Description: A synonym for default_storage_engine. Removed in MariaDB 5.5.
Commandline: --default-table-type=name
Scope: Global, Session
Dynamic: Yes
Removed: MariaDB/MySQL 5.5

`default_tmp_storage_engine`

Description: Default storage engine that will be used for tables created with CREATE TEMPORARY TABLE where no engine is specified. For internal temporary tables see aria_used_for_temp_tables). The storage engine used must be active or the server will not start. See default_storage_engine for the default for non-temporary tables. Defaults to NULL, in which case the value from default_storage_engine is used.
Commandline: --default-tmp-storage-engine=name
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: NULL
Introduced: MariaDB 10.1.0

`default_week_format`

Description: Default mode for the WEEK() function. See that page for details on the different modes
Commandline: --default-week-format=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 7

`delay_key_write`

Description: Specifies how MyISAM tables handles CREATE TABLE DELAY_KEY_WRITE. If set to ON, the default, any DELAY KEY WRITEs are honored. The key buffer is then flushed only when the table closes, speeding up writes. MyISAM tables should be automatically checked upon startup in this case, and --external locking should not be used, as it can lead to index corruption. If set to OFF, DELAY KEY WRITEs are ignored, while if set to ALL, all new opened tables are treated as if created with DELAY KEY WRITEs enabled.
Commandline: --delay-key-write[=name]
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: ON
Valid Values: ON, OFF, ALL

`delayed_insert_limit`

Description: After this many rows have been inserted with INSERT DELAYED, the handler will check for and execute any waiting SELECT statements.
Commandline: --delayed-insert-limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range: 1 to 4294967295

`delayed_insert_timeout`

Description: Time in seconds that the INSERT DELAYED handler will wait for INSERTs before terminating.
Commandline: --delayed-insert-timeout=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 300

`delayed_queue_size`

Description: Number of rows, per table, that can be queued when performing INSERT DELAYED statements. If the queue becomes full, clients attempting to perform INSERT DELAYED's will wait until the queue has room available again.
Commandline: --delayed-queue-size=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 1000
Range: 1 to 4294967295

`disconnect_on_expired_password`

Description: When a user password has expired (see User Password Expiry), this variable controls how the server handles clients that are not aware of the sandbox mode. If enabled, the client is not permitted to connect, otherwise the server puts the client in a sandbox mode.
Commandline: --disconnect-on-expired-password[={0|1}]
Scope: Global
Dynamic: Yes
Type: boolean
Default Value: OFF
Introduced: MariaDB 10.4.3

`div_precision_increment`

Description: The precision of the result of the decimal division will be the larger than the precision of the dividend by that number. By default it's 4, so SELECT 2/15 would return 0.1333 and SELECT 2.0/15 would return 0.13333. After setting div_precision_increment to 6, for example, the same operation would return 0.133333 and 0.1333333 respectively.

From MariaDB 10.1.46, MariaDB 10.2.33, MariaDB 10.3.24, MariaDB 10.4.14 and MariaDB 10.5.5, div_precision_increment is taken into account in intermediate calculations. Previous versions did not, and the results were dependent on the optimizer, and therefore unpredictable.

In MariaDB 10.1.46, MariaDB 10.1.47, MariaDB 10.2.33, MariaDB 10.2.34, MariaDB 10.2.35, MariaDB 10.3.24, MariaDB 10.3.25, MariaDB 10.4.14, MariaDB 10.4.15, MariaDB 10.5.5 and MariaDB 10.5.6 only, the fix truncated decimal values after every division, resulting in lower precision in some cases for those versions only.

From MariaDB 10.1.48, MariaDB 10.2.35, MariaDB 10.3.26, MariaDB 10.4.16 and MariaDB 10.5.7, a different fix was implemented. Instead of truncating decimal values after every division, they are instead truncated for comparison purposes only.

For example

Versions other than MariaDB 10.1.46, MariaDB 10.1.47, MariaDB 10.2.33, MariaDB 10.2.34, MariaDB 10.2.35, MariaDB 10.3.24, MariaDB 10.3.25, MariaDB 10.4.14, MariaDB 10.4.15, MariaDB 10.5.5 and MariaDB 10.5.6:

SELECT (55/23244*1000);
+-----------------+
| (55/23244*1000) |
+-----------------+
|          2.3662 |
+-----------------

MariaDB 10.1.46, MariaDB 10.1.47, MariaDB 10.2.33, MariaDB 10.2.34, MariaDB 10.2.35, MariaDB 10.3.24, MariaDB 10.3.25, MariaDB 10.4.14, MariaDB 10.4.15, MariaDB 10.5.5 and MariaDB 10.5.6 only:

SELECT (55/23244*1000);
+-----------------+
| (55/23244*1000) |
+-----------------+
|          2.4000 |
+-----------------+

This is because the intermediate result, SELECT 55/23244 takes into account div_precision_increment and results were truncated after every division in those versions only.

Commandline: --div-precision-increment=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4
Range: 0 to 30

`encrypt_tmp_disk_tables`

Description: Enables automatic encryption of all internal on-disk temporary tables that are created during query execution if aria_used_for_temp_tables=ON is set. See Data at Rest Encryption and Enabling Encryption for Internal On-disk Temporary Tables.
Commandline: --encrypt-tmp-disk-tables[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: 10.1.3

`encrypt_tmp_files`

Description: Enables automatic encryption of temporary files, such as those created for filesort operations, binary log file caches, etc. See Data at Rest Encryption.
Commandline: --encrypt-tmp-files[={0|1}]
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF (>= MariaDB 10.1.7), ON (<= MariaDB 10.1.6)
Introduced: MariaDB 10.1.5

`encryption_algorithm`

Description: Which encryption algorithm to use for table encryption. aes_cbc is the recommended one. See Table and Tablespace Encryption.
Commandline: --encryption-algorithm=value
Scope: Global
Dynamic: No
Data Type: enum
Default Value: none
Valid Values: none, aes_ecb, aes_cbc, aes_ctr
Introduced: MariaDB 10.1.3
Removed: MariaDB 10.1.4

`enforce_storage_engine`

Description: Force the use of a particular storage engine for new tables. Used to avoid unwanted creation of tables using another engine. For example, setting to InnoDB will prevent any MyISAM tables from being created. If another engine is specified in a CREATE TABLE statement, the outcome depends on whether the NO_ENGINE_SUBSTITUTION SQL_MODE has been set or not. If set (the default from MariaDB 10.1.7), the query will fail, while if not set, a warning will be returned and the table created according to the engine specified by this variable. The variable has a session scope, but is only modifiable by a user with the SUPER privilege.
Commandline: None
Scope: Session
Dynamic: Yes
Data Type: string
Default Value: none
Introduced: MariaDB 10.1.4

`engine_condition_pushdown`

Description: Deprecated in MariaDB 5.5 and removed and replaced by the optimizer_switch engine_condition_pushdown={on|off} flag in MariaDB 10.0.. Specifies whether the engine condition pushdown optimization is enabled. Since MariaDB 10.1.1, engine condition pushdown is enabled for all engines that support it.
Commandline: --engine-condition-pushdown
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Deprecated: MariaDB 5.5
Removed: MariaDB 10.0

`eq_range_index_dive_limit`

Description: Limit used for speeding up queries listed by long nested INs. The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0 (unlimited, the default), index dives are always used.
Commandline: --eq-range-index-dive-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 200 (>= MariaDB 10.4.3), 0 (<= MariaDB 10.4.2)
Range: 0 to 4294967295
Introduced: MariaDB 10.3.10, MariaDB 10.2.18

`error_count`

Description: Read-only variable denoting the number of errors from the most recent statement in the current session that generated errors. See SHOW_ERRORS().
Scope: Session
Dynamic: Yes
Data Type: numeric

`event_scheduler`

Description: Status of the Event Scheduler. Can be set to ON or OFF, while DISABLED means it cannot be set at runtime. Setting the variable will cause a load of events if they were not loaded at startup.
Commandline: --event-scheduler[=value]
Scope: Global
Dynamic: Yes
Data Type: enumeration
Default Value: OFF
Valid Values: ON (or 1), OFF (or 0), DISABLED

`expensive_subquery_limit`

Description: Number of rows to be examined for a query to be considered expensive, that is, maximum number of rows a subquery may examine in order to be executed during optimization and used for constant optimization.
Commandline: --expensive-subquery-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range: 0 upwards

`explicit_defaults_for_timestamp`

Description: This option causes CREATE TABLE to create all TIMESTAMP columns as NULL with the DEFAULT NULL attribute, Without this option, TIMESTAMP columns are NOT NULL and have implicit DEFAULT clauses.
Commandline: --explicit-defaults-for-timestamp=[={0|1}]
Scope: Global
Dynamic: No
Data Type: bolean
Default Value: OFF
Introduced: MariaDB 10.1.8

`external_user`

Description: External user name set by the plugin used to authenticate the client. NULL if native MariaDB authentication is used.
Scope: Session
Dynamic: No
Data Type: string
Default Value: NULL

`flush`

Description: Usually, MariaDB writes changes to disk after each SQL statement, and the operating system handles synchronizing (flushing) it to disk. If set to ON, the server will synchronize all changes to disk after each statement.
Commandline: --flush
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`flush_time`

Description: Interval in seconds that tables are closed to synchronize (flush) data to disk and free up resources. If set to 0, the default, there is no automatic synchronizing tables and closing of tables. This option should not be necessary on systems with sufficient resources.
Commandline: --flush_time=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0

`foreign_key_checks`

Description: If set to 1 (the default) foreign key constraints (including ON UPDATE and ON DELETE behavior) InnoDB tables are checked, while if set to 0, they are not checked. 0 is not recommended for normal use, though it can be useful in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0.
Commandline: None
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`ft_boolean_syntax`

Description: List of operators supported by an IN BOOLEAN MODE full-text search. If you wish to change, note that each character must be ASCII and non-alphanumeric, the full string must be 14 characters and the first or second character must be a space. Positions 10, 13 and 14 are reserved for future extensions. Also, no duplicates are permitted except for the phrase quoting characters in positions 11 and 12, which may be the same.
Commandline: --ft-boolean-syntax=name
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: + -><()*:""&|

`ft_max_word_len`

Description: Maximum length for a word to be included in the MyISAM full-text index. If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_max_token_size for the InnoDB equivalent.
Commandline: --ft-max-word-len=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 84
Minimum Value: 10

`ft_min_word_len`

Description: Minimum length for a word to be included in the MyISAM full-text index. If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_min_token_size for the InnoDB equivalent.
Commandline: --ft-min-word-len=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 4
Minimum Value: 1

`ft_query_expansion_limit`

Description: For full-text searches, denotes the numer of top matches when using WITH QUERY EXPANSION.
Commandline: --ft-query-expansion-limit=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 20
Range: 0 to 1000

`ft_stopword_file`

Description: File containing a list of stopwords for use in MyISAM full-text searches. Unless an absolute path is specified the file will be looked for in the data directory. The file is not parsed for comments, so all words found become stopwords. By default, a built-in list of words (built from storage/myisam/ft_static.c file) is used. Stopwords can be disabled by setting this variable to '' (an empty string). If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_server_stopword_table for the InnoDB equivalent.
Commandline: --ft-stopword-file=file_name
Scope: Global
Dynamic: No
Data Type: file name
Default Value: (built-in)

`general_log`

Description: If set to 0, the default unless the --general-log option is used, the general query log is disabled, while if set to 1, the general query log is enabled. See log_output for how log files are written. If that variable is set to NONE, no logs will be written even if general_query_log is set to 1.
Commandline: --general-log
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: 0

`general_log_file`

Description: Name of the general query log file. If this is not specified, the name is taken from the log-basename setting or from your system hostname with .log as a suffix.
Commandline: --general-log-file=file_name
Scope: Global
Dynamic: Yes
Data Type: file name
Default Value: host_name.log

`group_concat_max_len`

Description: Maximum length in bytes of the returned result for a GROUP_CONCAT() function.
Commandline: --group-concat-max-len=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value:
- 1048576 (1M) >= MariaDB 10.2.4
- 1024 (1K) <= MariaDB 10.2.3
Range - 32-bit: 4 to 4294967295
Range - 64-bit: 4 to 18446744073709547520

`have_compress`

Description: If the zlib compression library is accessible to the server, this will be set to YES, otherwise it will be NO. The COMPRESS() and UNCOMPRESS() functions will only be available if set to YES.
Scope: Global
Dynamic: No

`have_crypt`

Description: If the crypt() system call is available this variable will be set to YES, otherwise it will be set to NO. If set to NO, the ENCRYPT() function cannot be used.
Scope: Global
Dynamic: No

`have_csv`

Description: If the server supports CSV tables, will be set to YES, otherwise will be set to NO. Removed in MariaDB 10.0, use the Information Schema PLUGINS table or SHOW ENGINES instead.
Scope: Global
Dynamic: No
Removed: MariaDB 10.0

`have_dynamic_loading`

Description: If the server supports dynamic loading of plugins, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_geometry`

Description: If the server supports spatial data types, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_ndbcluster`

Description: If the server supports NDBCluster (disabled in MariaDB).
Scope: Global
Dynamic: No
Removed: MariaDB 10.0

`have_partitioning`

Description: If the server supports partitioning, will be set to YES, unless the --skip-partition option is used, in which case will be set to DISABLED. Will be set to NO otherwise. Removed in MariaDB 10.0 - SHOW PLUGINS should be used instead.
Scope: Global
Dynamic: No
Removed: MariaDB 10.0

`have_profiling`

Description: If statement profiling is available, will be set to YES, otherwise will be set to NO. See SHOW PROFILES() and SHOW PROFILE().
Scope: Global
Dynamic: No

`have_query_cache`

Description: If the server supports the query cache, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_rtree_keys`

Description: If RTREE indexes (used for spatial indexes) are available, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No

`have_symlink`

Description: This system variable can be used to determine whether the server supports symbolic links (note that it has no meaning on Windows).
- If symbolic links are supported, then the value will be YES.
- If symbolic links are not supported, then the value will be NO.
- If symbolic links are disabled with the --symbolic-links option and the skip option prefix (i.e. --skip-symbolic-links), then the value will be DISABLED.
- Symbolic link support is required for the INDEX DIRECTORY and DATA DIRECTORY table options.
Scope: Global
Dynamic: No

`histogram_size`

Description: Number of bytes used for a histogram. If set to 0, no histograms are created by ANALYZE.
Commandline: --histogram-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 254 (>= MariaDB 10.4.3), 0 (<= MariaDB 10.4.2)
Range: 0 to 255
Introduced: MariaDB 10.0.2

`histogram_type`

Description: Specifies the type of histograms created by ANALYZE.
- SINGLE_PREC_HB - single precision height-balanced.
- DOUBLE_PREC_HB - double precision height-balanced.
Commandline: --histogram-type=value
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: DOUBLE_PREC_HB (>= MariaDB 10.4.3), SINGLE_PREC_HB(<= MariaDB 10.4.2)
Valid Values: SINGLE_PREC_HB, DOUBLE_PREC_HB
Introduced: MariaDB 10.0.2

`host_cache_size`

Description: Number of host names that will be cached to avoid resolving. Setting to 0 disables the cache. Changing the value while the server is running causes an implicit FLUSH HOSTS, clearing the host cache and truncating the performance_schema.host_cache table. If you are connecting from a lot of different machines you should consider increasing.
Commandline: --host-cache-size=#.
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 128
Range: 0 to 65536
Introduced: MariaDB 10.0

`hostname`

Description: When the server starts, this variable is set to the server host name.
Scope: Global
Dynamic: No
Data Type: string

`identity`

Description: A synonym for last_insert_id variable.

`idle_readonly_transaction_timeout`

Description: Time in seconds that the server waits for idle read-only transactions before killing the connection. If set to 0, the default, connections are never killed. See also idle_transaction_timeout, idle_write_transaction_timeout and Transaction Timeouts.
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 31536000
Introduced: MariaDB 10.3.0

`idle_transaction_timeout`

Description: Time in seconds that the server waits for idle transactions before killing the connection. If set to 0, the default, connections are never killed. See also idle_readonly_transaction_timeout, idle_write_transaction_timeout and Transaction Timeouts.
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 31536000
Introduced: MariaDB 10.3.0

`idle_write_transaction_timeout`

Description: Time in seconds that the server waits for idle read-write transactions before killing the connection. If set to 0, the default, connections are never killed. See also idle_transaction_timeout, idle_readonly_transaction_timeout and Transaction Timeouts. Called idle_readwrite_transaction_timeout until MariaDB 10.3.2.
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 31536000
Introduced: MariaDB 10.3.0

`ignore_db_dirs`

Description: Tells the server that this directory can never be a database. That means two things - firstly it is ignored by the SHOW DATABASES command and INFORMATION_SCHEMA tables. And secondly, USE, CREATE DATABASE and SELECT statements will return an error if the database from the ignored list specified. Use this option several times if you need to ignore more than one directory. To make the list empty set the void value to the option as --ignore-db-dir=. If the option or configuration is specified multiple times, viewing this value will list the ignore directories separated by a period.
Commandline: --ignore-db-dirs=dir.
Scope: Global
Dynamic: No
Data Type: string

`in_predicate_conversion_threshold`

Description: The minimum number of scalar elements in the value list of an IN predicate that triggers its conversion to an IN subquery. Set to 0 to disable the conversion. See Conversion of Big IN Predicates Into Subqueries.
Commandline: --in-predicate-conversion-threshold=#
Scope: Global, Session
Dynamic: No
Data Type: numeric
Default Value: 1000
Range: 0 to 4294967295
Introduced: MariaDB 10.3.18 (previously debug builds only)

`in_transaction`

Description: Session-only and read-only variable that is set to 1 if a transaction is in progress, 0 if not.
Commandline: No
Scope: Session
Dynamic: No
Data Type: boolean
Default Value: 0

`init_connect`

Description: String containing one or more SQL statements, separated by semicolons, that will be executed by the server for each client connecting. If there's a syntax error in the one of the statements, the client will fail to connect. For this reason, the statements are not executed for users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege, who can then still connect and correct the error. See also init_file.
Commandline: --init-connect=name
Scope: Global
Dynamic: Yes
Data Type: string

`init_file`

Description: Name of a file containing SQL statements that will be executed by the server on startup. Each statement should be on a new line, and end with a semicolon. See also init_connect.
Commandline: init-file=file_name
Scope: Global
Dynamic: No
Data Type: file name

`insert_id`

Description: Value to be used for the next statement inserting a new AUTO_INCREMENT value.
Scope: Session
Dynamic: Yes
Data Type: numeric

`interactive_timeout`

Description: Time in seconds that the server waits for an interactive connection (one that connects with the mysql_real_connect() CLIENT_INTERACTIVE option) to become active before closing it. See also wait_timeout.
Commandline: --interactive-timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 28800
Range: (Windows): 1 to 2147483
Range: (Other): 1 to 31536000

`join_buffer_size`

Description: Minimum size in bytes of the buffer used for queries that cannot use an index, and instead perform a full table scan. Increase to get faster full joins when adding indexes is not possible, although be aware of memory issues, since joins will always allocate the minimum size. Best left low globally and set high in sessions that require large full joins. In 64-bit platforms, Windows truncates values above 4GB to 4GB with a warning. See also Block-Based Join Algorithms - Size of Join Buffers.
Commandline: --join-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 262144 (256kB) (>=MariaDB 10.1.7), 131072 (128kB) (<=MariaDB 10.1.6)
Range (>=MariaDB/MySQL 5.5): 128 to 18446744073709547520
Range (<=MariaDB/MySQL 5.3, Windows): 8228 to 18446744073709547520

`join_buffer_space_limit`

Description: Maximum size in bytes of the query buffer, By default 1024*128*10. See Block-based join algorithms.
Commandline: --join-buffer-space-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2097152
Range: 2048 to 99999999997952

`join_cache_level`

Description: Controls which of the eight block-based algorithms can be used for join operations. See Block-based join algorithms for more information.
- 1 – flat (Block Nested Loop) BNL
- 2 – incremental BNL
- 3 – flat Block Nested Loop Hash (BNLH)
- 4 – incremental BNLH
- 5 – flat Batch Key Access (BKA)
- 6 – incremental BKA
- 7 – flat Batch Key Access Hash (BKAH)
- 8 – incremental BKAH
Commandline: --join-cache-level=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2
Range: 0 to 8

`keep_files_on_create`

Description: If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is stored in the database directory. When set to 0, the default, if MariaDB finds another .MYD file in the database directory it will overwrite it. Setting this variable to 1 means that MariaDB will return an error instead, just as it usually does in the same situation outside of the database directory. The same applies for .MYI files and no INDEX DIRECTORY option.
Commandline: --keep-files-on-create=#
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`large_files_support`

Description: ON if the server if was compiled with large file support or not, else OFF
Scope: Global
Dynamic: No

`large_page_size`

Description: Indicates the size of memory page if large page support (Linux only) is enabled. The page size is determined from the Hugepagesize setting in /proc/meminfo. See large_pages. Deprecated and unused in MariaDB 10.5.3 since multiple page size support was added.
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: Autosized (see description)
Deprecated: MariaDB 10.5.3

`large_pages`

Description: Indicates whether large page support (Linux only - called huge pages) is used. This is set with --large-pages or disabled with --skip-large-pages. Large pages are used for the innodb buffer pool and for online DDL (of size 3* innodb_sort_buffer_size (or 6 when encryption is used)). To use large pages, the Linux sysctl variable kernel.shmmax must be large than the llocation. Also the sysctl variable vm.nr_hugepages multipled by large-page) must be larger than the usage. The ulimit for locked memory must be sufficient to cover the amount used (ulimit -l and equalivent in /etc/security/limits.conf / or in systemd LimitMEMLOCK). If these operating system controls or insufficient free huge pages are available, the allocation of large pages will fall back to conventional memory allocation and a warning will appear in the logs. Only allocations of the default Hugepagesize currently occur (see /proc/meminfo).
Commandline: --large-pages, --skip-large-pages
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`last_insert_id`

Description: Contains the same value as that returned by LAST_INSERT_ID(). Note that setting this variable doen't update the value returned by the underlying function.
Scope: Session
Dynamic: Yes
Data Type: numeric

`lc_messages`

Description: This system variable can be specified as a locale name. The language of the associated locale will be used for error messages. See Server Locales for a list of supported locales and their associated languages.
- This system variable is set to en_US by default, which means that error messages are in English by default.
- If this system variable is set to a valid locale name, but the server can't find an error message file for the language associated with the locale, then the default language will be used instead.
- This system variable is used along with the lc_messages_dir system variable to construct the path to the error messages file.
- See Setting the Language for Error Messages for more information.
Commandline: --lc-messages=name
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: en_us

`lc_messages_dir`

Description: This system variable can be specified either as the path to the directory storing the server's error message files or as the path to the directory storing the specific language's error message file. See Server Locales for a list of available locales and their related languages.
- The server initially tries to interpret the value of this system variable as a path to the directory storing the server's error message files. Therefore, it constructs the path to the language's error message file by concatenating the value of this system variable with the language name of the locale specified by the lc_messages system variable .
- If the server does not find the error message file for the language, then it tries to interpret the value of this system variable as a direct path to the directory storing the specific language's error message file.
- See Setting the Language for Error Messages for more information.
Commandline: --lc-messages-dir=path
Scope: Global
Dynamic: No
Data Type: directory name

`lc_time_names`

Description: The locale that determines the language used for the date and time functions DAYNAME(), MONTHNAME() and DATE_FORMAT(). Locale names are language and region subtags, for example 'en_ZA' (English - South Africa) or 'es_US: Spanish - United States'. The default is always 'en-US' regardless of the system's locale setting. See server locale for a full list of supported locales.
Commandline: --lc-time-names=name
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: en_US

`license`

Description: Server license, for example GPL.
Scope: Global
Dynamic: No
Data Type: string

`local_infile`

Description: If set to 1, LOCAL is supported for LOAD DATA INFILE statements. If set to 0, usually for security reasons, attempts to perform a LOAD DATA LOCAL will fail with an error message.
Commandline: --local-infile=#
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: ON

`lock_wait_timeout`

Description: Timeout in seconds for attempts to acquire metadata locks. Statements using metadata locks include FLUSH TABLES WITH READ LOCK, LOCK TABLES, HANDLER and DML and DDL operations on tables, stored procedures and functions, and views. The timeout is separate for each attempt, of which there may be multiple in a single statement. 0 (from MariaDB 10.3.0) means no wait. See WAIT and NOWAIT.
Commandline: --lock-wait-timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value:
- 86400 (1 day) >= MariaDB 10.2.4
- 31536000 (1 year) <= MariaDB 10.2.3
Range:
- 0 to 31536000 (>= MariaDB 10.3)
- 1 to 31536000 (<= MariaDB 10.2)

`locked_in_memory`

Description: Indicates whether --memlock was used to lock mysqld in memory.
Commandline: --memlock
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`log`

Description: Deprecated and removed in MariaDB 10.0, use general_log instead.
Commandline: -l [filename] or --log[=filename]
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: OFF
Removed: MariaDB 10.0

`log_disabled_statements`

Description: If set, the specified type of statements (slave or stored procedure statements) will not be logged to the general log.
Commandline: --log-disabled_statements=value
Scope: Global, Session
Dynamic: No
Data Type: set
Default Value: (empty string)
Valid Vales: slave and/or sp
Introduced: MariaDB 10.3.1

`log_error`

Description: Specifies the name of the error log. If --console is specified later in the configuration (Windows only) or this option isn't specified, errors will be logged to stderr. If no name is provided, errors will still be logged to hostname.err in the datadir directory by default. If a configuration file sets --log-error, one can reset it with --skip-log-error (useful to override a system wide configuration file). MariaDB always writes its error log, but the destination is configurable. See error log for details.
Commandline: --log-error[=name], --skip-log-error
Scope: Global
Dynamic: No
Data Type: file name
Default Value: (empty string)

`log_output`

Description: How the output for the general query log and the slow query log is stored. By default written to file (FILE), it can also be stored in the general_log and slow_log tables in the mysql database (TABLE), or not stored at all (NONE). More than one option can be chosen at the same time, with NONE taking precedence if present. Logs will not be written if logging is not enabled. See Writing logs into tables, and the slow_query_log and general_log server system variables.
Commandline: --log-output=name
Scope: Global
Dynamic: Yes
Data Type: set
Default Value: FILE
Valid Values: TABLE, FILE or NONE

`log_queries_not_using_indexes`

Description: Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log (regardless of time taken). The slow query log needs to be enabled for this to have an effect. Mapped to log_slow_filter='not_using_index' from MariaDB 10.3.1.
Commandline: --log-queries-not-using-indexes
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`log_slow_admin_statements`

Description: Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. Before MariaDB 10.1.13, this was only available as a mysqld option, not a server variable. See also log_slow_disabled_statements and log_slow_filter.
Commandline: --log-slow-admin-statements
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value:
- ON (>= MariaDB 10.2.4)
- OFF (<= MariaDB 10.2.3)
Introduced: MariaDB 10.1.13 (variable)

`log_slow_disabled_statements`

Description: If set, the specified type of statements will not be logged to the slow query log. See also log_slow_admin_statements and log_slow_filter.
Commandline: --log-slow-disabled_statements=value
Scope: Global, Session
Dynamic: No
Data Type: set
Default Value: sp
Valid Vales: admin, call, slave and/or sp
Introduced: MariaDB 10.3.1

`log_slow_filter`

Description: Comma-delimited string containing one or more settings for filtering what is logged to the slow query log. If a query matches one of the types listed in the filter, and takes longer than long_query_time, it will be logged(except for 'not_using_index' which is always logged if enabled, regardless of the time). Sets log-slow-admin-statements to ON. See also log_slow_disabled_statements.
- admin log administrative queries (create, optimize, drop etc...)
- filesort logs queries that use a filesort.
- filesort_on_disk logs queries that perform a a filesort on disk.
- filesort_priority_queue (from MariaDB 10.3.2)
- full_join logs queries that perform a join without indexes.
- full_scan logs queries that perform full table scans.
- not_using_index logs queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows. Disregards long_query_time, unlike other options. log_queries_not_using_indexes maps to this option. From MariaDB 10.3.1.
- query_cache log queries that are resolved by the query cache.
- query_cache_miss logs queries that are not found in the query cache.
- tmp_table logs queries that create an implicit temporary table.
- tmp_table_on_disk logs queries that create a temporary table on disk.
Commandline: log-slow-filter=value1[,value2...]
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value:
- admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk (<= MariaDB 10.3.0)
- admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk (>= MariaDB 10.3.1)
Valid Values: admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk

`log_slow_queries`

Description: Deprecated and removed in MariaDB 10.0, use slow_query_log instead.
Commandline: --log-slow-queries[=name]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Removed: MariaDB 10.0

`log_slow_rate_limit`

Description: The slow query log will log every this many queries. The default is 1, or every query, while setting it to 20 would log every 20 queries, or five percent. Aims to reduce I/O usage and excessively large slow query logs. See also Slow Query Log Extended Statistics.
Commandline: log-slow-rate-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 upwards

`log_slow_verbosity`

Description: Controls information to be added to the slow query log. Options are added in a comma-delimited string. See also Slow Query Log Extended Statistics. log_slow_verbosity is not supported when log_output='TABLE'.
- query_plan logs query execution plan information
- innodb an unused Percona XtraDB option for logging XtraDB/InnoDB statistics.
- explain prints EXPLAIN output in the slow query log. See EXPLAIN in the Slow Query Log.
Commandline: log-slow-verbosity=value1[,value2...]
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: (Empty)
Valid Values: (Empty), query_plan, innodb, explain (from MariaDB 10.0.5)

`log_tc_size`

Description: Defines the size in bytes of the memory-mapped file-based transaction coordinator log, which is only used if the binary log is disabled. If you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available. This size is defined in multiples of 4096. This size could always be set as a commandline option, but it was made into a system variable in MariaDB 10.1.3. See Transaction Coordinator Log for more information. Also see the --log-tc server option and the --tc-heuristic-recover option.
Commandline: log-tc-size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 24576
Range: 12288 to 18446744073709551615
Introduced: MariaDB 10.1.3 (variable)

`log_warnings`

Description: Determines which additional warnings are logged. Setting to 0 disables additional warning logging. Note that this does not prevent all warnings, there is a core set of warnings that will always be written to the error log. The additional warnings are as follows:
- log_warnings >= 1
  - Event scheduler information.
  - System signals
  - Wrong usage of --user
  - Failed setrlimit() and mlockall()
  - Changed limits
  - Wrong values of lower_case_table_names and stack_size
  - Wrong values for command line options
  - Start log position and some master information when starting slaves
  - Slave reconnects
  - Killed slaves
  - Error reading relay logs
  - Unsafe statements for statement-based replication. If this warning occurs frequently, it is throttled to prevent flooding the log.
  - Disabled plugins that one tried to enable or use.
  - UDF files that didn't include the required init functions.
  - DNS lookup failures.
- log_warnings >= 2
  - Access denied errors.
  - Connections aborted or closed due to errors or timeouts.
  - Table handler errors
  - Messages related to the files used to persist replication state:
    - Either the default master.info file or the file that is configured by the master_info_file option.
    - Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.
  - Information about a master's binary log dump thread.
- log_warnings >= 3
  - All errors and warnings during MyISAM repair and auto recover.
  - Information about old-style language options.
  - Information about progress of InnoDB online DDL.
- log_warnings >=4
  - Connections aborted due to "Too many connections" errors.
  - Connections closed normally.
  - Connections aborted due to KILL.
  - Connections closed due to released connections, such as when completion_type is set to RELEASE.
- log_warnings >=9
  - Information about initializing plugins.
Commandline: -W [level] or --log-warnings[=level]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value:
- 2 (>= MariaDB 10.2.4)
- 1 (<= MariaDB 10.2.3)
Range: 0 to 4294967295

`long_query_time`

Description: If a query takes longer than this many seconds to execute (microseconds can be specified too), the Slow_queries status variable is incremented and, if enabled, the query is logged to the slow query log.
Commandline: --long-query-time=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10.000000 >= MariaDB 10.1.13, 10 <= MariaDB 10.1.12
Range: 0 upwards

`low_priority_updates`

Description: If set to 1 (0 is the default), for storage engines that use only table-level locking (Aria, MyISAM, MEMORY and MERGE), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes.
- In MariaDB 5.5 and earlier, sql_low_priority_updates is a synonym.
Commandline: --low-priority-updates
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`lower_case_file_system`

Description: Read-only variable describing whether the file system is case-sensitive. If set to OFF, file names are case-sensitive. If set to ON, they are not case-sensitive.
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: ##

`lower_case_table_names`

Description: If set to 0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.
Commandline: --lower-case-table-names[=#]
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0 (Unix), 1 (Windows), 2 (Mac OS X)
Range: 0 to 2

`max_allowed_packet`

Description: Maximum size in bytes of a packet or a generated/intermediate string. The packet message buffer is initialized with the value from net_buffer_length, but can grow up to max_allowed_packet bytes. Set as large as the largest BLOB, in multiples of 1024. If this value is changed, it should be changed on the client side as well. See slave_max_allowed_packet for a specific limit for replication purposes.
Commandline: --max-allowed-packet=#
Scope: Global, Session
Dynamic: Yes (Global), No (Session)
Data Type: numeric
Default Value:
- 16777216 (16M) >= MariaDB 10.2.4
- 4194304 (4M) >= MariaDB 10.1.7
- 1048576 (1MB) <= MariaDB 10.1.6
- 1073741824 (1GB) (client-side)
Range: 1024 to 1073741824

`max_connect_errors`

Description: Limit to the number of successive failed connects from a host before the host is blocked from making further connections. The count for a host is reset to zero if they successfully connect. To unblock, flush the host cache with a FLUSH HOSTS statement or mysqladmin flush-hosts.
Commandline: --max-connect-errors=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 100 (from MariaDB 10.0), 10 (before MariaDB 10.0)

`max_connections`

Description: The maximum number of simultaneous client connections. See also Handling Too Many Connections. Note that this value affects the number of file descriptors required on the operating system. Minimum was changed from 1 to 10 to avoid possible unexpected results for the user (MDEV-18252).
Commandline: --max-connections=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 151
Range:
- 10 to 100000 (>= MariaDB 10.3.6, MariaDB 10.2.15, MariaDB 10.1.33)
- 1 to 100000 (<= MariaDB 10.3.5, MariaDB 10.2.14, MariaDB 10.1.32)

`max_delayed_threads`

Description: Limits to the number of INSERT DELAYED threads. Once this limit is reached, the insert is handled as if there was no DELAYED attribute. If set to 0, DELAYED is ignored entirely. The session value can only be set to 0 or to the same as the global value.
Commandline: --max-delayed-threads=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 20
Range: 0 to 16384

`max_digest_length`

Description: Maximum length considered for computing a statement digest, such as used by the Performance Schema and query rewrite plugins. Statements that differ after this many bytes produce the same digest, and are aggregated for statistics purposes. The variable is allocated per session. Increasing will allow longer statements to be distinguished from each other, but increase memory use, while decreasing will reduce memory use, but more statements may become indistinguishable.
Commandline: --max-digest-length=#
Scope: Global,
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 0 to 1048576
Introduced: MariaDB 10.1.5

`max_error_count`

Description: Specifies the maximum number of messages stored for display by SHOW ERRORS and SHOW WARNINGS statements.
Commandline: --max-error-count=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 64
Range: 0 to 65535

`max_heap_table_size`

Description: Maximum size in bytes for user-created MEMORY tables. Setting the variable while the server is active has no effect on existing tables unless they are recreated or altered. The smaller of max_heap_table_size and tmp_table_size also limits internal in-memory tables. When the maximum size is reached, any further attempts to insert data will receive a "table ... is full" error. Temporary tables created with CREATE TEMPORARY will not be converted to Aria, as occurs with internal temporary tables, but will also receive a table full error.
Commandline: --max-heap-table-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 16777216
Range : 16384 to 4294966272

`max_insert_delayed_threads`

Description: Synonym for max_delayed_threads.

`max_join_size`

Description: Statements will not be performed if they are likely to need to examine more than this number of rows, row combinations or do more disk seeks. Can prevent poorly-formatted queries from taking server resources. Changing this value to anything other the default will reset sql_big_selects to 0. If sql_big_selects is set again, max_join_size will be ignored. This limit is also ignored if the query result is sitting in the query cache. Previously named sql_max_join_size, which is still a synonym.
Commandline: --max-join-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 18446744073709551615
Range: 1 to 18446744073709551615

`max_length_for_sort_data`

Description: Used to decide which algorithm to choose when sorting rows. If the total size of the column data, not including columns that are part of the sort, is less than max_length_for_sort_data, then we add these to the sort key. This can speed up the sort as we don't have to re-read the same row again later. Setting the value too high can slow things down as there will be a higher disk activity for doing the sort.
Commandline: --max-length-for-sort-data=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 4 to 8388608

`max_long_data_size`

Description: Maximum size for parameter values sent with mysql_stmt_send_long_data(). If not set, will default to the value of max_allowed_packet. Deprecated in MariaDB 5.5 and removed in MariaDB 10.5.0; use max_allowed_packet instead.
Commandline: --max-long-data-size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value:
- 16777216 (16M) >= MariaDB 10.2.4
- 4194304 (4M) <= MariaDB 10.2.3, >= MariaDB 10.1.7
- 1048576 (1M) <= MariaDB 10.1.6
Range: 1024 to 4294967295
Deprecated: MariaDB 5.5
Removed: MariaDB 10.5.0

`max_password_errors`

Description: The maximum permitted number of failed connection attempts due to an invalid password before a user is blocked from further connections. FLUSH_PRIVILEGES will permit the user to connect again. This limit is ignored for users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege.
Commandline: --max-password-errors=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295
Introduced: MariaDB 10.4.2

`max_prepared_stmt_count`

Description: Maximum number of prepared statements on the server. Can help prevent certain forms of denial-of-service attacks. If set to 0, no prepared statements are permitted on the server.
Commandline: --max-prepared-stmt-count=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 16382
Range: 0 to 4294967295 (>= MariaDB 10.3.6), 0 to 1048576 (<= MariaDB 10.3.5)

`max_recursive_iterations`

Description: Maximum number of iterations when executing recursive queries, used to prevent infinite loops in recursive CTEs.
Commandline: --max-recursive-iterations=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1000 (>= MariaDB 10.6.0), 4294967295 (< MariaDB 10.6.0)
Range: 0 to 4294967295
Introduced: MariaDB 10.2.2

`max_rowid_filter_size`

Description: The maximum size of the container of a rowid filter.
Commandline: --max-rowid-filter-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 131072
Range: 1024 to 18446744073709551615
Introduced: MariaDB 10.4.3

`max_seeks_for_key`

Description: The optimizer assumes that the number specified here is the most key seeks required when searching with an index, regardless of the actual index cardinality. If this value is set lower than its default and maximum, indexes will tend to be preferred over table scans.
Commandline: --max-seeks-for-key=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295

`max_session_mem_used`

Description: Amount of memory a single user session is allowed to allocate. This limits the value of the session variable Memory_used.
Commandline: --max-session-mem-used=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 9223372036854775807 (8192 PB)
Range: 8192 to 18446744073709551615
Introduced: MariaDB 10.1.21

`max_sort_length`

Description: Maximum size in bytes used for sorting data values - anything exceeding this is ignored. The server uses only the first max_sort_length bytes of each value and ignores the rest.
Commandline: --max-sort-length=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range:
- 4 to 8388608 (<= MariaDB 10.1.45, MariaDB 10.2.32, MariaDB 10.3.23, MariaDB 10.4.13, MariaDB 10.5.3)
- 8 to 8388608 (>= MariaDB 10.1.46, MariaDB 10.2.33, MariaDB 10.3.24, MariaDB 10.4.14, MariaDB 10.5.4)

`max_sp_recursion_depth`

Description: Permitted number of recursive calls for a stored procedure. 0, the default, no recursion is permitted. Increasing this value increases the thread stack requirements, so you may need to increase thread_stack as well. This limit doesn't apply to stored functions.
Commandline: --max-sp-recursion-depth[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 255

`max_statement_time`

Description: Maximum time in seconds that a query can execute before being aborted. This includes all queries, not just SELECT statements, but excludes statements in stored procedures. If set to 0, no limit is applied. See Aborting statements that take longer than a certain time to execute for details and limitations. Useful when combined with SET STATEMENT for limiting the execution times of individual queries.
Commandline: --max-statement-time[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0.000000 >= MariaDB 10.1.13, 0 <= MariaDB 10.1.12
Range: 0 upwards
Introduced: MariaDB 10.1.1

`max_tmp_tables`

Description: Unused.

`max_user_connections`

Description: Maximum simultaneous connections permitted for each user account. When set to 0, there is no per user limit. Setting it to -1 stops users without the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege, from connecting to the server. The session variable is always read-only and only privileged users can modify user limits. The session variable defaults to the global max_user_connections variable, unless the user's specific MAX_USER_CONNECTIONS resource option is non-zero. When both global variable and the user resource option are set, the user's MAX_USER_CONNECTIONS is used. Note: This variable does not affect users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege.
Commandline: --max-user-connections=#
Scope: Global, Session
Dynamic: Yes, (except when globally set to 0 or -1)
Data Type: numeric
Default Value: 0
Range: -1 to 4294967295

`max_write_lock_count`

Description: Read lock requests will be permitted for processing after this many write locks. Applies only to storage engines that use table level locks (thr_lock), so no effect with InnoDB or Archive.
Commandline: --max-write-lock-count=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 4294967295
Range: 0-4294967295

`metadata_locks_cache_size`

Description: Size of the metadata locks cache, used for reducing the need to create and destroy synchronization objects. It is particularly helpful on systems where this process is inefficient, such as Windows XP.
Commandline: --metadata-locks-cache-size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1024
Range: 1 to 1048576

`metadata_locks_hash_instances`

Description: Number of hashes used by the set of metadata locks. The metadata locks are partitioned into separate hashes in order to reduce contention.
Commandline: --metadata-locks-hash-instances=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 8
Range: 1 to 1024
Introduced: MariaDB 10.0

`min_examined_row_limit`

Description: If a query examines more than this number of rows, it is logged to the slow query log. If set to 0, the default, no row limit is used.
Commandline: --min-examined-row-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0-4294967295

`mrr_buffer_size`

Description: Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization for more information.
Commandline: --mrr-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 262144
Range 8192 to 2147483648

`multi_range_count`

Description: Ignored. Use mrr_buffer_size instead.
Commandline: --multi-range-count=#
Default Value: 256
Removed: MariaDB 10.5.1

`mysql56_temporal_format`

Description: If set (the default), MariaDB uses the MySQL 5.6 low level formats for TIME, DATETIME and TIMESTAMP instead of the MariaDB 5.3 version. The version MySQL introduced in 5.6 requires more storage, but potentially allows negative dates and has some advantages in replication. There should be no reason to revert to the old MariaDB 5.3 microsecond format. See also MDEV-10723.
Commandline: --mysql56-temporal-format
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: ON
Introduced: MariaDB 10.1.2

`named_pipe`

Description: On Windows systems, determines whether connections over named pipes are permitted.
Commandline: --named-pipe
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`net_buffer_length`

Description: The starting size, in bytes, for the connection and thread buffers for each client thread. The size can grow to max_allowed_packet. This variable's session value is read-only. Can be set to the expected length of client statements if memory is a limitation.
Commandline: --net-buffer-length=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 16384
Range: 1024 to 1048576

`net_read_timeout`

Description: Time in seconds the server will wait for a client connection to send more data before aborting the read. See also net_write_timeout and slave_net_timeout
Commandline: --net-read-timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 30
Range: 1 upwards

`net_retry_count`

Description: Permit this many retries before aborting when attempting to read or write on a communication port. On FreeBSD systems should be set higher as threads are sent internal interrupts..
Commandline: --net-retry-count=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10
Range: 1 to 4294967295

`net_write_timeout`

Description: Time in seconds to wait on writing a block to a connection before aborting the write. See also net_read_timeout and slave_net_timeout.
Commandline: --net-write-timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 60
Range: 1 upwards

`old`

Description: Disabled by default, enabling it reverts index hints to those used before MySQL 5.1.17. Enabling may lead to replication errors. Being replaced by old_mode.
Commandline: --old
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`old_alter_table`

Description: From MariaDB 10.3.7, an alias for alter_algorithm. Prior to that, if set to 1 (0 is default), MariaDB reverts to the non-optimized, pre-MySQL 5.1, method of processing ALTER TABLE statements. A temporary table is created, the data is copied over, and then the temporary table is renamed to the original.
Commandline: --old-alter-table
Scope: Global, Session
Dynamic: Yes
Data Type: enumerated (>=MariaDB 10.3.7), boolean (<= MariaDB 10.3.6)
Default Value: See alter_algorithm (>= MariaDB 10.3.7), 0 (<= MariaDB 10.3.6)
Valid Values: See alter_algorithm for the full list.
Deprecated: MariaDB 10.3.7 (superceded by alter_algorithm)

`old_mode`

Description: Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode. Will be used to replace the old variable over time.
Commandline: --old-mode
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: (empty string)
Valid Values: See OLD Mode for the full list.

`old_passwords`

Description: If set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`open_files_limit`

Description: The number of file descriptors available to MariaDB. If you are getting the Too many open files error, then you should increase this limit. If set to 0, then MariaDB will calculate a limit based on the following:

MAX(max_connections*5, max_connections +table_open_cache*2)

MariaDB sets the limit with setrlimit. MariaDB cannot set this to exceed the hard limit imposed by the operating system. Therefore, you may also need to change the hard limit. There are a few ways to do so.
- If you are using mysqld_safe to start mysqld, then see the instructions at mysqld_safe: Configuring the Open Files Limit.
- If you are using systemd to start mysqld, then see the instructions at systemd: Configuring the Open Files Limit.
- Otherwise, you can change the hard limit for the mysql user account by modifying /etc/security/limits.conf. See Configuring Linux for MariaDB: Configuring the Open Files Limit for more details.
Commandline: --open-files-limit=count
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: Autosized (see description)
Range: 0 to 4294967295

`optimizer_max_sel_arg_weight`

Description: The maximum weight of the SEL_ARG graph. Set to 0 for no limit.
Commandline: --optimizer-max-sel-arg-weight=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 32000
Range: 0 to 18446744073709551615
Introduced: MariaDB 10.5.9

`optimizer_prune_level`

Description: If set to 1, the default, the optimizer will use heuristics to prune less-promising partial plans from the optimizer search space. If set to 0, heuristics are disabled and an exhaustive search is performed.
Commandline: --optimizer-prune-level[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`optimizer_search_depth`

Description: Maximum search depth by the query optimizer. Smaller values lead to less time spent on execution plans, but potentially less optimal results. If set to 0, MariaDB will automatically choose a reasonable value. Since the better results from more optimal planning usually offset the longer time spent on planning, this is set as high as possible by default. 63 is a valid value, but its effects (switching to the original find_best search) are deprecated.
Commandline: --optimizer-search-depth[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 62
Range: 0 to 63

`optimizer_selectivity_sampling_limit`

Description: Controls number of record samples to check condition selectivity
Commandline: optimizer-selectivity-sampling-limit[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range: 10 upwards
Introduced: MariaDB 10.0

`optimizer_switch`

Description: A series of flags for controlling the query optimizer. See Optimizer Switch for defaults, and a comparison to MySQL.
Commandline: --optimizer-switch=value
Scope: Global, Session
Dynamic: Yes
Data Type: string
Valid Values:
- condition_pushdown_for_derived={on|off} (>=MariaDB 10.2.2)
- condition_pushdown_for_subquery={on|off} (>=MariaDB 10.4.0)
- default - set all optimizations to their default values.
- derived_merge={on|off} - see Derived table merge optimization
- derived_with_keys={on|off} - see Derived table with key optimization
- engine_condition_pushdown={on|off}. Deprecated in MariaDB 10.1.1 as engine condition pushdown is now automatically enabled for all engines that support it.
- exists_to_in={on|off} - see EXISTS-to-IN optimization
- extended_keys={on|off} - see Extended Keys
- firstmatch={on|off} - see First Match Strategy
- index_condition_pushdown={on|off} - see Index Condition Pushdown
- index_merge={on|off}
- index_merge_intersection={on|off}
- index_merge_sort_intersection={on|off} - more details
- index_merge_sort_union={on|off}
- index_merge_union={on|off}
- in_to_exists={on|off} - see IN-TO-EXISTS transformation
- join_cache_bka={on|off} - see Block-Based Join Algorithms
- join_cache_hashed={on|off} - see Block-Based Join Algorithms
- join_cache_incremental={on|off} - see Block-Based Join Algorithms
- loosescan={on|off} - see LooseScan strategy
- materialization={on|off} - Semi-join and non semi-join materialization.
- mrr={on|off} - see Multi Range Read optimization
- mrr_cost_based={on|off} - see Multi Range Read optimization
- mrr_sort_keys={on|off} - see Multi Range Read optimization
- optimize_join_buffer_size={on|off} - see Block-Based Join Algorithms
- orderby_uses_equalities={on|off} (>= MariaDB 10.1.15, MariaDB 10.2.1) - if not set, the optimizer ignores equality propagation. See MDEV-8989.
- outer_join_with_cache={on|off} - see Block-Based Join Algorithms
- partial_match_rowid_merge={on|off} - see Non-semi-join subquery optimizations
- partial_match_table_scan={on|off} - see Non-semi-join subquery optimizations
- semijoin={on|off} - see Semi-join subquery optimizations
- semijoin_with_cache={on|off} - see Block-Based Join Algorithms
- subquery_cache={on|off} - see subquery cache.
- table_elimination={on|off} - see Table Elimination User Interface

`optimizer_trace`

Description: Controls tracing of the optimizer: optimizer_trace=option=val[,option=val...], where option is one of {enabled} and val is one of {on, off, default}
Commandline: --optimizer-trace=value
Scope: Global, Session
Dynamic: Yes
Data Type: enum
Default Value: enabled=off
Valid Values: enabled={on|off|default}
Introduced: MariaDB 10.4.3

`optimizer_trace_max_mem_size`

Description: Limits the memory used while tracing a query by specifying the maximum allowed cumulated size, in bytes, of stored optimizer traces.
Commandline: --optimizer-trace-max-mem-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1048576
Range: 1 to 18446744073709551615
Introduced: MariaDB 10.4.3

`optimizer_use_condition_selectivity`

Description: Controls which statistics can be used by the optimizer when looking for the best query execution plan.
- 1 Use selectivity of predicates as in MariaDB 5.5.
- 2 Use selectivity of all range predicates supported by indexes.
- 3 Use selectivity of all range predicates estimated without histogram.
- 4 Use selectivity of all range predicates estimated with histogram.
- 5 Additionally use selectivity of certain non-range predicates calculated on record sample.
Commandline: --optimizer-use-condition-selectivity=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4 (>= MariaDB 10.4.1), 1 (<= MariaDB 10.4.0)
Range: 1 to 5
Introduced: MariaDB 10.0

`pid_file`

Description: Full path of the process ID file.
Commandline: --pid-file=file_name
Scope: Global
Dynamic: No
Data Type: file name

`plugin_dir`

Description: Path to the plugin directory. For security reasons, either make sure this directory can only be read by the server, or set secure_file_priv.
Commandline: --plugin-dir=path
Scope: Global
Dynamic: No
Data Type: directory name
Default Value: BASEDIR/lib/plugin

`plugin_maturity`

Description: The lowest acceptable plugin maturity. MariaDB will not load plugins less mature than the specified level.
Commandline: --plugin-maturity=level
Scope: Global
Dynamic: No
Type: enum
Default Value: One less than the server maturity (>= MariaDB 10.3.3), unknown (<= MariaDB 10.3.2)
Valid Values: unknown, experimental, alpha, beta, gamma, stable

`port`

Description: Port to listen for TCP/IP connections. If set to 0, will default to, in order of preference, my.cnf, the MYSQL_TCP_PORT environment variable, /etc/services, built-in default (3306).
Commandline: --port=#, -P
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 3306
Range: 0 to 65535

`preload_buffer_size`

Description: Size in bytes of the buffer allocated when indexes are preloaded.
Commandline: --preload-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 32768
Range: 1024 to 1073741824

`profiling`

Description: If set to 1 (0 is default), statement profiling will be enabled. See SHOW PROFILES() and SHOW PROFILE().
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`profiling_history_size`

Description: Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES.
Commandline: --profiling-history-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 15
Range: 0 to 100

`progress_report_time`

Description: Time in seconds between sending progress reports to the client for time-consuming statements. If set to 0, progress reporting will be disabled.
Commandline: --progress-report-time=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 5
Range: 0 to 4294967295

`protocol_version`

Description: The version of the client/server protocol used by the MariaDB server.
Commandline: None
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 10
Range: 0 to 4294967295

`proxy_protocol_networks`

Description: Enable proxy protocol for these source networks. The syntax is a comma separated list of IPv4 and IPv6 networks. If the network doesn't contain a mask, it is considered to be a single host. "*" represents all networks and must be the only directive on the line. String "localhost" represents non-TCP local connections (Unix domain socket, Windows named pipe or shared memory). See Proxy Protocol Support.
Commandline: --proxy-protocol-networks=value
Scope: Global
Dynamic: Yes (>= MariaDB 10.3.6), No (<= MariaDB 10.3.5)
Data Type: string
Default Value: (empty)
Introduced: MariaDB 10.3.1

`proxy_user`

Description: Set to the proxy user account name if the current client is a proxy, else NULL.
Scope: Session
Dynamic: No
Data Type: string

`pseudo_slave_mode`

Description: For internal use by the server.
Scope: Session
Dynamic: Yes
Data Type: numeric
Default Value: OFF

`pseudo_thread_id`

Description: For internal use only.
Scope: Session
Dynamic: Yes
Data Type: numeric

`query_alloc_block_size`

Description: Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).
Commandline: --query-alloc-block-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 16384 (from MariaDB 10.1.2), 8192 (before MariaDB 10.1.2)
Range: 1024 to 4294967295

`query_cache_limit`

Description: Size in bytes for which results larger than this are not stored in the query cache.
Commandline: --query-cache-limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1048576 (1MB)
Range: 0 to 4294967295

`query_cache_min_res_unit`

Description: Minimum size in bytes of the blocks allocated for query cache results.
Commandline: --query-cache-min-res-unit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 4096 (4KB)
Range: 0 to 4294967295

`query_cache_size`

Description: Size in bytes available to the query cache. About 40KB is needed for query cache structures, so setting a size lower than this will result in a warning. 0, the default before MariaDB 10.1.7, effectively disables the query cache.

Warning: Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value. This will happen even if query_cache_type is explicitly set to OFF in the configuration.

Commandline: --query-cache-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1M (>= MariaDB 10.1.7), 0 (<= MariaDB 10.1.6) (although frequently given a default value in some setups)
Valid Values: 0 upwards in units of 1024.

`query_cache_strip_comments`

Description: If set to 1 (0 is default), the server will strip any comments from the query before searching to see if it exists in the query cache. Multiple space, line feeds, tab and other white space characters will also be removed.
Commandline: query-cache-strip-comments
Scope: Session, Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`query_cache_type`

Description: If set to 0, the query cache is disabled (although a buffer of query_cache_size bytes is still allocated). If set to 1 all SELECT queries will be cached unless SQL_NO_CACHE is specified. If set to 2 (or DEMAND), only queries with the SQL CACHE clause will be cached. Note that if the server is started with the query cache disabled, it cannot be enabled at runtime.

Commandline: --query-cache-type=#
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: OFF (>= MariaDB 10.1.7), ON (<= MariaDB 10.1.6)
Valid Values: 0 or OFF, 1 or ON, 2 or DEMAND

`query_cache_wlock_invalidate`

Description: If set to 0, the default, results present in the query cache will be returned even if there's a write lock on the table. If set to 1, the client will first have to wait for the lock to be released.
Commandline: --query-cache-wlock-invalidate
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`query_prealloc_size`

Description: Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect. Increasing may be useful if complex queries are being run, as this will reduce the need for more memory allocations during query operation. See also query_alloc_block_size.
Commandline: --query-prealloc-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 24576 (from MariaDB 10.1.2) 8192 (before MariaDB 10.1.2)
Range: 1024 to 4294967295 (from MariaDB 10.1.2), 8192 to 4294967295 (before MariaDB 10.1.2)

`rand_seed1`

Description: rand_seed1 and rand_seed2 facilitate replication of the RAND() function. The master passes the value of these to the slaves so that the random number generator is seeded in the same way, and generates the same value, on the slave as on the master. Until MariaDB 10.1.4, the variable value could not be viewed, with the SHOW VARIABLES output always displaying zero.
Commandline: None
Scope: Session
Dynamic: Yes
Data Type: numeric
Default Value: Varies
Range: 0 to 18446744073709551615

`rand_seed2`

Description: See rand_seed1.

`range_alloc_block_size`

Description: Size in bytes of blocks allocated during range optimization. The unit size in 1024.
Commandline: --range-alloc-block-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4096
Range - 32 bit: 4096 to 4294967295

`read_buffer_size`

Description: Each thread performing a sequential scan (for MyISAM, Aria and MERGE tables) allocates a buffer of this size in bytes for each table scanned. Increase if you perform many sequential scans. If not in a multiple of 4KB, will be rounded down to the nearest multiple. Also used in ORDER BY's for caching indexes in a temporary file (not temporary table), for caching results of nested queries, for bulk inserts into partitions, and to determine the memory block size of MEMORY tables.
Commandline: --read-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 131072
Range: 8200 to 2147479552

`read_only`

Description: When set to 1 (0 is default), no updates are permitted except from users with the SUPER privilege or, from MariaDB 10.5.2, the READ ONLY ADMIN privilege, or replica servers updating from a primary. The read_only variable is useful for replica servers to ensure no updates are accidentally made outside of what are performed on the primary. Inserting rows to log tables, updates to temporary tables and OPTIMIZE TABLE or ANALYZE TABLE statements are excluded from this limitation. If read_only is set to 1, then the SET PASSWORD statement is limited only to users with the SUPER privilege (<= MariaDB 10.5.1) or READ ONLY ADMIN privilege (>= MariaDB 10.5.2). Attempting to set this variable to 1 will fail if the current session has table locks or transactions pending, while if other sessions hold table locks, the statement will wait until these locks are released before completing. While the attempt to set read_only is waiting, other requests for table locks or transactions will also wait until read_only has been set. See Read-Only Replicas for more.
Commandline: --read-only
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`read_rnd_buffer_size`

Description: Size in bytes of the buffer used when reading rows from a MyISAM table in sorted order after a key sort. Larger values improve ORDER BY performance, although rather increase the size by SESSION where the need arises to avoid excessive memory use.
Commandline: --read-rnd-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 262144
Range: 8200 to 2147483647

`require_secure_transport`

Description: When this option is enabled, connections attempted using insecure transport will be rejected. Secure transports are SSL/TLS, Unix sockets or named pipes. Note that per-account requirements take precedence.
Commandline: --require-secure-transport[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.5.2

`rowid_merge_buff_size`

Description: The maximum size in bytes of the memory available to the Rowid-merge strategy. See Non-semi-join subquery optimizations for more information.
Commandline: --rowid-merge-buff-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 8388608
Range: 0 to 2147483647

`rpl_recovery_rank`

Description: Unused.
Removed: MariaDB 10.1.2

`safe_show_database`

Description: This variable was removed in MariaDB 5.5, and has been replaced by the more flexible SHOW DATABASES privilege.
Commandline: --safe-show-database (until MySQL 4.1.1)
Scope: Global
Dynamic: Yes
Data Type: boolean
Removed: MariaDB 5.5

`secure_auth`

Description: Connections will be blocked if they use the the mysql_old_password authentication plugin. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format.
Commandline: --secure-auth
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: ON (>= MariaDB 10.1.7), OFF (<= MariaDB 10.1.6)

`secure_file_priv`

Description: LOAD DATA, SELECT ... INTO and LOAD FILE() will only work with files in the specified path. If not set, the default, or set to empty string, the statements will work with any files that can be accessed.
Commandline: --secure-file-priv=path
Scope: Global
Dynamic: No
Data Type: path name
Default Value: None

`secure_timestamp`

Description: Restricts direct setting of a session timestamp. Possible levels are:
- YES - timestamp cannot deviate from the system clock
- REPLICATION - replication thread can adjust timestamp to match the master's
- SUPER - a user with this privilege and a replication thread can adjust timestamp
- NO - historical behavior, anyone can modify session timestamp
Commandline: --secure-timestamp=value
Scope: Global
Dynamic: No
Data Type: enum
Default Value: NO
Introduced: MariaDB 10.3.7

`session_track_schema`

Description: Whether to track changes to the default schema within the current session.
Commandline: --session-track-schema={0|1}
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: ON
Introduced: MariaDB 10.2.2

`session_track_state_change`

Description: Whether to track changes to the session state.
Commandline: --session-track-state-change={0|1}
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.2.2

`session_track_system_variables`

Description: Comma-separated list of session system variables for which to track changes. In MariaDB 10.2, by default no variables are tracked. For compatibility with MySQL defaults, this variable should be set to "autocommit, character_set_client, character_set_connection, character_set_results, time_zone" (the default from MariaDB 10.3.1). The * character tracks all session variables.
Commandline: --session-track-system-variables=value
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: autocommit, character_set_client, character_set_connection, character_set_results, time_zone (>= MariaDB 10.3.1), empty string (<= MariaDB 10.3.0)
Introduced: MariaDB 10.2.2

`session_track_transaction_info`

Description: Track changes to the transaction attributes. OFF to disable; STATE to track just transaction state (Is there an active transaction? Does it have any data? etc.); CHARACTERISTICS to track transaction state and report all statements needed to start a transaction with the same characteristics (isolation level, read only/read write,snapshot - but not any work done / data modified within the transaction).
Commandline: --session-track-transaction-info=value
Scope: Global, Session
Dynamic: Yes
Data Type: enum
Default Value: OFF
Valid Values: OFF, STATE, CHARACTERISTICS
Introduced: MariaDB 10.2.2

`shared_memory`

Description: Windows only, determines whether the server permits shared memory connections. See also shared_memory_base_name.
Scope: Global
Dynamic: No

`shared_memory_base_name`

Description: Windows only, specifies the name of the shared memory to use for shared memory connection. Mainly used when running more than one instance on the same physical machine. By default the name is MYSQL and is case sensitive. See also shared_memory.
Scope: Global
Dynamic: No
Data Type: string
Default Value: MYSQL

`skip_external_locking`

Description: If this system variable is set, then some kinds of external table locks will be disabled for some storage engines.
- If this system variable is set, then the MyISAM storage engine will not use file-based locks. Otherwise, it will use the fcntl() function with the F_SETLK option to get file-based locks on Unix, and it will use the LockFileEx() function to get file-based locks on Windows.
- If this system variable is set, then the Aria storage engine will not lock a table when it decrements the table's in-file counter that keeps track of how many connections currently have the table open. See MDEV-19393 for more information.
Commandline: --skip-external-locking
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 1

`skip_name_resolve`

Description: If set to 1 (0 is the default), only IP addresses are used for connections. Host names are not resolved. All host values in the GRANT tables must be IP addresses (or localhost).
Commandline: --skip-name-resolve
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 0

`skip_networking`

Description: If set to 1, (0 is the default), the server does not listen for TCP/IP connections. All interaction with the server by be through socket files (Unix) or named pipes or shared memory (Windows). It's recommended to use this option if only local clients are permitted to connect to the server. Enabling this option also prevents a server from functioning as a replication client.
Commandline: --skip-networking
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 0

`skip_show_database`

Description: If set to 1, (0 is the default), only users with the SHOW DATABASES privilege can use the SHOW DATABASES statement to see all database names.
Commandline: --skip-show-database
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 0

`slow_launch_time`

Description: Time in seconds. If a thread takes longer than this to launch, the slow_launch_threads server status variable is incremented.
Commandline: --slow-launch-time=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 2

`slow_query_log`

Description: If set to 0, the default unless the --slow-query-log option is used, the slow query log is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. MariaDB 10.1 added support for session variables.
Commandline: --slow-query-log
Scope: Global, Session (MariaDB 10.1)
Dynamic: Yes
Data Type: boolean
Default Value: 0
See also: See log_output to see how log files are written. If that variable is set to NONE, no logs will be written even if slow_query_log is set to 1.

`slow_query_log_file`

Description: Name of the slow query log file.
Commandline: --slow-query-log-file=file_name
Scope: Global
Dynamic: Yes
Data Type: file name
Default Value: host_name-slow.log

`socket`

Description: On Unix-like systems, this is the name of the socket file used for local client connections, by default /tmp/mysql.sock, often changed by the distribution, for example /var/lib/mysql/mysql.sock. On Windows, this is the name of the named pipe used for local client connections, by default MySQL. On Windows, this is not case-sensitive.
Commandline: --socket=name
Scope: Global
Dynamic: No
Data Type: file name
Default Value: /tmp/mysql.sock (Unix), MySQL (Windows)

`sort_buffer_size`

Description: Each session performing a sort allocates a buffer with this amount of memory. Not specific to any storage engine. If the status variable sort_merge_passes is too high, you may need to look at improving your query indexes, or increasing this. Consider reducing where there are many small sorts, such as OLTP, and increasing where needed by session. 16k is a suggested minimum.
Commandline: --sort-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: number
Default Value: 2M (2097152) (some distributions increase the default)

`sql_auto_is_null`

Description: If set to 1, the query SELECT * FROM table_name WHERE auto_increment_column IS NULL will return an auto-increment that has just been successfully inserted, the same as the LAST_INSERT_ID() function. Some ODBC programs make use of this IS NULL comparison.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`sql_big_selects`

Description: If set to 0, MariaDB will not perform large SELECTs. See max_join_size for details. If max_join_size is set to anything but DEFAULT, sql_big_selects is automatically set to 0. If sql_big_selects is again set, max_join_size will be ignored.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`sql_big_tables`

Description: Old variable, which if set to 1, allows large result sets by saving all temporary sets to disk, avoiding 'table full' errors. No longer needed, as the server now handles this automatically.
- This is a synonym for big_tables.
Commandline: --sql-big-tables
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Removed: MariaDB 10.0

`sql_buffer_result`

Description: If set to 1 (0 is default), results from SELECT statements are always placed into temporary tables. This can help the server when it takes a long time to send the results to the client by allowing the table locks to be freed early.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`sql_if_exists`

Description: If set to 1, adds an implicit IF EXISTS to ALTER, RENAME and DROP of TABLES, VIEWS, FUNCTIONS and PACKAGES. This variable is mainly used in replication to tag DDLs that can be ignored on the slave if the target table doesn't exist.
Commandline: --sql-if-exists[={0|1}]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.5.2

`sql_log_off`

Description: If set to 1 (0 is the default), no logging to the general query log is done for the client. Only clients with the SUPER privilege can update this variable.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0

`sql_log_update`

Description: Removed. Use sql_log_bin instead.
Removed: MariaDB/MySQL 5.5

`sql_low_priority_updates`

Description: If set to 1 (0 is the default), for storage engines that use only table-level locking (Aria, MyISAM, MEMORY and MERGE), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes.
- This is a synonym for low_priority_updates.
Commandline: --sql-low-priority-updates
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Removed: MariaDB 10.0

`sql_max_join_size`

Description: Synonym for max_join_size, the preferred name.
Deprecated: MariaDB 5.5
Removed: MariaDB 10.0

`sql_mode`

Description: Sets the SQL Mode. Multiple modes can be set, separated by a comma.
Commandline: --sql-mode=value[,value[,value...]]
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
- STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (>= MariaDB 10.2.4)
- NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (>= MariaDB 10.1.7)
- (empty string) (<= MariaDB 10.1.6)
Valid Values: See SQL Mode for the full list.

`sql_notes`

Description: If set to 1, the default, warning_count is incremented each time a Note warning is encountered. If set to 0, Note warnings are not recorded. mysqldump has outputs to set this variable to 0 so that no unnecessary increments occur when data is reloaded.
Commandline: None
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`sql_quote_show_create`

Description: If set to 1, the default, the server will quote identifiers for SHOW CREATE DATABASE, SHOW CREATE TABLE and SHOW CREATE VIEW statements. Quoting is disabled if set to 0. Enable to ensure replications works when identifiers require quoting.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 1

`sql_safe_updates`

Description: If set to 1, UPDATEs and DELETEs need either a key in the WHERE clause, or a LIMIT clause, or else they will aborted. Prevents the common mistake of accidentally deleting or updating every row in a table. Until MariaDB 10.3.11, could not be set as a command-line option or in my.cnf.
Commandline: --sql-safe-updates[={0|1}]
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`sql_select_limit`

Description: Maximum number of rows that can be returned from a SELECT query. Default is the maximum number of rows permitted per table by the server, usually 2³²-1 or 2⁶⁴-1. Can be restored to the default value after being changed by assigning it a value of DEFAULT.
Commandline: None
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 18446744073709551615

`sql_warnings`

Description: If set to 1, single-row INSERTs will produce a string containing warning information if a warning occurs.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: OFF (0)

`storage_engine`

Description: See default_storage_engine.
Deprecated: MariaDB 5.5

`standard_compliant_cte`

Description: Allow only standard-compliant common table expressions. Prior to MariaDB 10.2.4, this variable was named standards_compliant_cte.
Commandline: --standard-compliant-cte={0|1}
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: ON
Introduced: MariaDB 10.2.2

`stored_program_cache`

Description: Limit to the number of stored routines held in the stored procedures and stored functions caches. Each time a stored routine is executed, this limit is first checked, and if the number held in the cache exceeds this, that cache is flushed and memory freed.
Commandline: --stored-program-cache=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 256
Range: 256 to 524288

`strict_password_validation`

Description: When password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash). This excludes direct updates to the privilege tables.
Commandline: --strict-password-validation
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: ON
Introduced: MariaDB 10.1.2

`sync_frm`

Description: If set to 1, the default, each time a non-temporary table is created, its .frm definition file is synced to disk. Fractionally slower, but safer in case of a crash.
Commandline: --sync-frm
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: TRUE

`system_time_zone`

Description: The system time zone is determined when the server starts. The system time zone is usually read from the operating system's environment. See Time Zones: System Time Zone for the various ways to change the system time zone. This variable is not the same as the time_zone system variable, which is the variable that actually controls a session's active time zone. The system time zone is used for a session when time_zone is set to the special value SYSTEM.
Scope: Global
Dynamic: No
Data Type: string

`table_definition_cache`

Description: Number of table definitions that can be cached. Table definitions are taken from the .frm files, and if there are a large number of tables increasing the cache size can speed up table opening. Unlike the table_open_cache, as the table_definition_cache doesn't use file descriptors, and is much smaller.
Commandline: --table-definition-cache=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 400
Range:
- 400 to 2097152 (>= MariaDB 10.4.2, MariaDB 10.3.13, MariaDB 10.2.22, MariaDB 10.1.38, MariaDB 10.0.38)
- 400 to 524288 (<= MariaDB 10.4.1, MariaDB 10.3.12, MariaDB 10.2.21, MariaDB 10.1.37, MariaDB 10.0.37)

`table_lock_wait_timeout`

Description: Unused, and removed.
Commandline: --table-lock-wait-timeout=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 50
Range: 1 to 1073741824
Removed: MariaDB 5.5

`table_open_cache`

Description: Maximum number of open tables cached in one table cache instance. See Optimizing table_open_cache for suggestions on optimizing. Increasing table_open_cache increases the number of file descriptors required.
Commandline: --table-open-cache=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 2000 (>= MariaDB 10.1.7), 400 (<= MariaDB 10.1.6)
Range:
- 1 to 1048576 (1024K) (>= MariaDB 10.1.20, MariaDB 10.0.35)
- 1 to 524288 (512K) (<= MariaDB 10.1.19, MariaDB 10.0.34)

`table_open_cache_instances`

Description: This system variable specifies the maximum number of table cache instances. MariaDB Server initially creates just a single instance. However, whenever it detects contention on the existing instances, it will automatically create a new instance. When the number of instances has been increased due to contention, it does not decrease again. The default value of this system variable is 8, which is expected to handle up to 100 CPU cores. If your system is larger than this, then you may benefit from increasing the value of this system variable.
- Depending on the ratio of actual available file handles, and table_open_cache size, the max. instance count may be auto adjusted to a lower value on server startup.
- The implementation and behavior of this feature is different than the same feature in MySQL 5.6.
- See Optimizing table_open_cache: Automatic Creation of New Table Open Cache Instances for more information.
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 8 (>= MariaDB 10.2.2)
Range: 1 to 64
Introduced: MariaDB 10.2.2

`table_type`

Description: Removed and replaced by storage_engine. Use default_storage_engine instead.

`tcp_keepalive_interval`

Description: The interval, in seconds, between when successive keep-alive packets are sent if no acknowledgement is received. If set to 0, the system dependent default is used.
Commandline: --tcp-keepalive-interval=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 2147483
Introduced: MariaDB 10.3.3

`tcp_keepalive_probes`

Description: The number of unacknowledged probes to send before considering the connection dead and notifying the application layer. If set to 0, a system dependent default is used.
Commandline: --tcp-keepalive-probes=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 2147483
Introduced: MariaDB 10.3.3

`tcp_keepalive_time`

Description: Timeout, in seconds, with no activity until the first TCP keep-alive packet is sent. If set to 0, a system dependent default is used.
Commandline: --tcp-keepalive-time=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 2147483
Introduced: MariaDB 10.3.3

`tcp_nodelay`

Description: Set the TCP_NODELAY option (disable Nagle's algorithm) on socket.
Commandline: --tcp-nodelay={0|1}
Scope: Session
Dynamic: Yes
Data Type: boolean
Default Value: 1
Introduced: MariaDB 10.4.0

`thread_cache_size`

Description: Number of threads server caches for re-use. If this limit hasn't been reached, when a client disconnects, its threads are put into the cache, and re-used where possible. In MariaDB 10.2.0 and newer the threads are freed after 5 minutes of idle time. Normally this setting has little effect, as the other aspects of the thread implementation are more important, but increasing it can help servers with high volumes of connections per second so that most can use a cached, rather than a new, thread. The cache miss rate can be calculated as the server status variables threads_created/connections. If the thread pool is active, thread_cache_size is ignored. If thread_cache_size is set to greater than the value of max_connections, thread_cache_size will be set to the max_connections value.
Commandline: --thread-cache-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0 (<= MariaDB 10.1), 256 (from MariaDB 10.2.0)
Range: 0 to 16384

`thread_concurrency`

Description: Allows applications to give the system a hint about the desired number of threads. Specific to Solaris only, invokes thr_setconcurrency(). Deprecated and has no effect from MariaDB 5.5.
Commandline: --thread-concurrency=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 10
Range: 1 to 512
Deprecated: MariaDB 5.5
Removed: MariaDB 10.5.1

`thread_stack`

Description: Stack size for each thread. If set too small, limits recursion depth of stored procedures and complexity of SQL statements the server can handle in memory. Also affects limits in the crash-me test.
Commandline: --thread-stack=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value:
- 299008 (MariaDB 10.2.5)
- 297984 (MariaDB 10.2.1)
- 296960 (MariaDB 10.2.0)
- 295936 (MariaDB 10.1)
- 294912 (<= MariaDB 10.0)
Range: 131072 to 18446744073709551615

`time_format`

Description: Unused.

`time_zone`

Description: The global value determines the default time zone for sessions that connect. The session value determines the session's active time zone. When it is set to SYSTEM, the session's time zone is determined by the system_time_zone system variable.
Commandline: --default-time-zone=string
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: SYSTEM

`timed_mutexes`

Description: Determines whether InnoDB mutexes are timed. OFF, the default, disables mutex timing, while ON enables it. See also SHOW ENGINE for more on mutex statistics. Deprecated and has no effect.
Commandline: --timed-mutexes
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Deprecated: MariaDB 5.5.39
Removed: MariaDB 10.5.1

`timestamp`

Description: Sets the time for the client. This will affect the result returned by the NOW() function, not the SYSDATE() function, unless the server is started with the --sysdate-is-now option, in which case SYSDATE becomes an alias of NOW, and will also be affected. Also used to get the original timestamp when restoring rows from the binary log.
Scope: Session
Dynamic: Yes
Valid Values: timestamp_value (Unix epoch timestamp, not MariaDB timestamp), DEFAULT

`tmp_disk_table_size`

Description: Max size for data for an internal temporary on-disk MyISAM or Aria table. These tables are created as part of complex queries when the result doesn't fit into the memory engine. You can set this variable if you want to limit the size of temporary tables created in your temporary directory tmpdir.
Commandline: --tmp-disk-table-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 18446744073709551615 (max unsigned integer, no limit)
Range: 1024 to 18446744073709551615
Introduced: MariaDB 10.2.7

`tmp_memory_table_size`

Description: An alias for tmp_table_size.
Commandline: --tmp-memory-table-size=#
Introduced: MariaDB 10.2.7

`tmp_table_size`

Description: The largest size for temporary tables in memory (not MEMORY tables) although if max_heap_table_size is smaller the lower limit will apply. If a table exceeds the limit, MariaDB converts it to a MyISAM or Aria table. You can see if it's necessary to increase by comparing the status variables Created_tmp_disk_tables and Created_tmp_tables to see how many temporary tables out of the total created needed to be converted to disk. Often complex GROUP BY queries are responsible for exceeding the limit. Defaults may be different on some systems, see for example Differences in MariaDB in Debian. From MariaDB 10.2.7, tmp_memory_table_size is an alias.
Commandline: --tmp-table-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 16777216 (16MB)
Range:
- 1024 to 4294967295 (< MariaDB 10.5)
- 0 to 4294967295 (>= MariaDB 10.5.0)

`tmpdir`

Description: Directory for storing temporary tables and files. Can specify a list (separated by semicolons in Windows, and colons in Unix) that will then be used in round-robin fashion. This can be used for load balancing across several disks. Note that if the server is a replication replica, and slave_load_tmpdir, which overrides tmpdir for replicas, is not set, you should not set tmpdir to a directory that is cleared when the machine restarts, or else replication may fail.
Commandline: --tmpdir=path or -t path
Scope: Global
Dynamic: No
Type: directory name/s
Default:
- $TMPDIR (environment variable) if set
- otherwise $TEMP if set and on Windows
- otherwise $TMP if set and on Windows
- otherwise P_tmpdir ("/tmp") or C:\TEMP (unless overridden during buid time)

`transaction_alloc_block_size`

Description: Size in bytes to increase the memory pool available to each transaction when the available pool is not large enough. See transaction_prealloc_size.
Commandline: --transaction-alloc-block-size=#
Scope: Global, Session
Dynamic: Yes
Type: numeric
Default Value: 8192
Range: 1024 to 4294967295
Block Size: 1024

`transaction_prealloc_size`

Description: Initial size of a memory pool available to each transaction for various memory allocations. If the memory pool is not large enough for an allocation, it is increased by transaction_alloc_block_size bytes, and truncated back to transaction_prealloc_size bytes when the transaction is completed. If set large enough to contain all statements in a transaction, extra malloc() calls are avoided.
Commandline: --transaction-prealloc-size=#
Scope: Global, Session
Dynamic: Yes
Type: numeric
Default Value: 4096
Range: 1024 to 4294967295
Block Size: 1024

`tx_isolation`

Description: The transaction isolation level. See also SET TRANSACTION ISOLATION LEVEL.
Commandline: --transaction-isolation=name
Scope: Global, Session
Dynamic: Yes
Type: enumeration
Default Value: REPEATABLE-READ
Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

`tx_read_only`

Description: Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION.
Commandline: --transaction-read-only=#
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default Value: OFF
Introduced: MariaDB 10.0

`unique_checks`

Description: If set to 1, the default, secondary indexes in InnoDB tables are performed. If set to 0, storage engines can (but are not required to) assume that duplicate keys are not present in input data. Set to 0 to speed up imports of large tables to InnoDB. The storage engine will still issue a duplicate key error if it detects one, even if set to 0.
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default Value: 1

`updatable_views_with_limit`

Description: Determines whether view updates can be made with an UPDATE or DELETE statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table. 0 prohibits this, while 1 permits it while issuing a warning (the default).
Commandline: --updatable-views-with-limit=#
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default Value: 1

`use_stat_tables`

Description: Controls the use of engine-independent table statistics.
- never: The optimizer will not use data from statistics tables.
- complementary: The optimizer uses data from statistics tables if the same kind of data is not provided by the storage engine.
- preferably: Prefer the data from statistics tables, if it's not available there, use the data from the storage engine.
- complementary_for_queries: Same as complementary, but for queries only (to avoid needlessly collecting for ANALYZE TABLE). From MariaDB 10.4.1.
- preferably_for_queries: Same as preferably, but for queries only (to avoid needlessly collecting for ANALYZE TABLE). From MariaDB 10.4.1.
Commandline: --use-stat-tables=mode
Scope: Global, Session
Dynamic: Yes
Data Type: enum
Default Value: preferably_for_queries (>= MariaDB 10.4.1), never (<= MariaDB 10.4.0)
Introduced: MariaDB 10.0.1

`version`

Description: Server version number. It may also include a suffix with configuration or build information. -debug indicates debugging support was enabled on the server, and -log indicates at least one of the binary log, general log or slow query log are enabled, for example 10.0.1-MariaDB-mariadb1precise-log. From MariaDB 10.2.1, this variable can be set at startup in order to fake the server version.
Commandline: -V, --version[=name] (>= MariaDB 10.2.1), --version (<= MariaDB 10.2.0)
Scope: Global
Dynamic: No
Type: string

`version_comment`

Description: Value of the COMPILATION_COMMENT option specified by CMake when building MariaDB, for example mariadb.org binary distribution.
Scope: Global
Dynamic: No
Type: string

`version_compile_machine`

Description: The machine type or architecture MariaDB was built on, for example i686.
Scope: Global
Dynamic: No
Type: string

`version_compile_os`

Description: Operating system that MariaDB was built on, for example debian-linux-gnu.
Scope: Global
Dynamic: No
Type: string

`version_malloc_library`

Description: Version of the used malloc library.
Commandline: No
Scope: Global
Dynamic: No
Type: string
Introduced: MariaDB 10.0.8

`version_source_revision`

Description: Source control revision id for MariaDB source code, enabling one to see exactly which version of the source was used for a build.
Commandline: None
Scope: Global
Dynamic: No
Type: string
Introduced: MariaDB 10.3.2

`wait_timeout`

Description: Time in seconds that the server waits for a connection to become active before closing it. The session value is initialized when a thread starts up from either the global value, if the connection is non-interactive, or from the interactive_timeout value, if the connection is interactive.
Commandline: --wait-timeout=#
Scope: Global, Session
Dynamic: Yes
Type: numeric
Default Value: 28800
Range: (Windows): 1 to 2147483
Range: (Other): 1 to 31536000

`warning_count`

Description: Read-only variable indicating the number of warnings, errors and notes resulting from the most recent statement that generated messages. See SHOW WARNINGS for more. Note warnings will only be recorded if sql_notes is true (the default).
Scope: Session
Dynamic: No
Type: numeric

Comments

Comments loading...

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Server System Variables

Contents

About the Server System Variables

Setting Server System Variables

List of Server System Variables

alter_algorithm

analyze_sample_percentage

autocommit

automatic_sp_privileges

back_log

basedir

big_tables

bind_address

bulk_insert_buffer_size

character_set_client

character_set_connection

character_set_database

character_set_filesystem

character_set_results

character_set_server

character_set_system

character_sets_dir

check_constraint_checks

collation_connection

collation_database

collation_server

completion_type

concurrent_insert

connect_timeout

core_file

datadir

date_format

datetime_format

debug

debug_no_thread_alarm

debug_sync

default_password_lifetime

default_regex_flags

default_storage_engine

default_table_type

default_tmp_storage_engine

default_week_format

delay_key_write

delayed_insert_limit

delayed_insert_timeout

delayed_queue_size

disconnect_on_expired_password

div_precision_increment

encrypt_tmp_disk_tables

encrypt_tmp_files

encryption_algorithm

enforce_storage_engine

engine_condition_pushdown

eq_range_index_dive_limit

error_count

event_scheduler

expensive_subquery_limit

explicit_defaults_for_timestamp

external_user

flush

flush_time

foreign_key_checks

ft_boolean_syntax

ft_max_word_len

ft_min_word_len

ft_query_expansion_limit

ft_stopword_file

general_log

general_log_file

group_concat_max_len

have_compress

have_crypt

have_csv

have_dynamic_loading

have_geometry

have_ndbcluster

have_partitioning

have_profiling

have_query_cache

have_rtree_keys