Server System Variables

Reference for MariaDB Server system variables — how to view and set them, their scope and dynamism, and their effect on connections, caching, logging, and performance.

Overview

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

For a full list of server options, system variables and status variables, see this page.

Many of the general system variables are described on this page, but others are described elsewhere:

Most of these can be set with command line options and many of them can be changed at runtime. Variables that can be changed at runtime (and therefore are not read-only) are described as "Dynamic" below, and elsewhere in the documentation.

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

While in the mariadb client, run:

See SHOW VARIABLES for instructions on using this command.
From your shell, run mariadbd like so:

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:

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

Set them from the mariadb 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.

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

kilobytes

1024

megabytes

10242

gigabytes

10243

terabytes

10244 (from MariaDB 10.3.3)

petabytes

10245 (from MariaDB 10.3.3)

exabytes

10246 (from MariaDB 10.3.3)

The suffix can be upper or lower-case.

List of Server System Variables

`allow_suspicious_udfs`

Description: Allows use of user-defined functions consisting of only one symbol x() without corresponding x_init() or x_deinit(). That also means that one can load any function from any library, for example exit() from libc.so. Not recommended unless you require old UDFs with one symbol that cannot be recompiled. Before MariaDB 10.10, available as an option only.
Command line: --allow-suspicious-udfs
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.10

`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. The feature was removed in MariaDB 11.5. See ALGORITHM=DEFAULT.
- 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.
Command line: --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
Deprecated: MariaDB 11.5

`analyze_max_length`

Description: Prevents collection of column statistics for CHAR/VARCHAR columns that can be analyzed automatically by ANALYZE TABLE PERSISTENT. Columns exceeding this threshold in bytes will be skipped unless included explicitly in FOR COLUMNS(...).
Command line: --analyze-max-length=val
Scope: Global, Session
Dynamic: Yes
Data Type: INT UNSIGNED
Default Value: 4294967295 (4G)
Range: 32 to 4294967295
Introduced: MariaDB 10.6.23, MariaDB 10.11.14, MariaDB 11.4.8, MariaDB 11.8.3, MariaDB 12.0.2, MariaDB 12.1.1, MariaDB Enterprise Server 10.6, MariaDB Enterprise Server 11.4, MariaDB Enterprise Server 11.8

`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.
Command line: --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.
Command line: --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.
Command line: --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).
Command line: --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.
Command line: --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 instead 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.
- From MariaDB 10.5, this system variable is deprecated.
Command line: --big-tables
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Deprecated: MariaDB 10.5.0
Removed: MariaDB 12.0

`bind_address`

Description: By default, the MariaDB server listens for TCP/IP connections on all addresses. You can specify an alternative when the server starts using this option; either a host name, an IPv4 or an IPv6 address, "::" or "" (all addresses). In some systems, such as Debian and Ubuntu, the bind_address is set to 127.0.0.1, which binds the server to listen on localhost only. bind_address has always been available as a mariadbd option; from MariaDB 10.3.3 it's also available as a system variable. Before MariaDB 10.6.0 "::" implied listening additionally on IPv4 addresses like "". From 10.6.0 onwards it refers to IPv6 stictly. Starting with MariaDB 10.11, a comma-separated list of addresses to bind to can be given. See also Configuring MariaDB for Remote Client Access.
Command line: --bind-address=addr
Scope: Global
Dynamic: No
Data Type: string
Default Value: (Empty string)
Valid Values: Host name, IPv4, IPv6, ::, *
Introduced: MariaDB 10.3.3 (as a system variable)

`block_encryption_mode`

Description: Default block encryption mode for AES_ENCRYPT() and AES_DECRYPT() functions.
Command line: --block-encryption-mode=val
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: aes-128-ecb
Valid values: aes-128-ecb, aes-192-ecb, aes-256-ecb, aes-128-cbc, aes-192-cbc, aes-256-cbc, aes-128-ctr, aes-192-ctr, aes-256-ctr
Introduced: MariaDB 11.2.0

`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.
Command line: --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, utf16le, utf32 and ucs2 cannot be used as client character sets. From MariaDB 10.6, the utf8 character set (and related collations) was by default an alias for utf8mb3 rather than the other way around. From MariaDB 13.1, utf8 is by default an alias for utf8mb4; the previous utf8mb3 default can be restored with the deprecated UTF8_IS_UTF8MB3 flag of the old_mode system variable.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
- >= MariaDB 11.6: utf8mb4
- <= MariaDB 11.5: latin1

`character_set_collations`

Description: Overrides for character set default collations. Takes a comma-delimited list of character set and collation settings, for example SET @@character_set_collations = 'utf8mb4=uca1400_ai_ci, latin2=latin2_hungarian_ci'; The new variable will take effect in all cases where a character set is explicitly or implicitly specified without an explicit COLLATE clause, including but not limited to:
- Column collation
- Table collation
- Database collation
- CHAR(expr USING csname)
- CONVERT(expr USING csname)
- CAST(expr AS CHAR CHARACTER SET csname)
- '' - character string literal
- _utf8mb3'text' - a character string literal with an introducer
- _utf8mb3 X'61' - a character string literal with an introducer with hex notation
- _utf8mb3 0x61 - a character string literal with an introducer with hex hybrid notation
- @@collation_connection after a SET NAMES without COLLATE
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
- utf8mb3=utf8mb3_uca1400_ai_ci, ucs2=ucs2_uca1400_ai_ci, utf8mb4=utf8mb4_uca1400_ai_ci, utf16=utf16_uca1400_ai_ci, utf32=utf32_uca1400_ai_ci (>= MariaDB 11.5)
- Empty (<= MariaDB 11.4)
Introduced: MariaDB 11.2

`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. From MariaDB 10.6, the utf8 character set (and related collations) was by default an alias for utf8mb3 rather than the other way around. From MariaDB 13.1, utf8 is by default an alias for utf8mb4; the previous utf8mb3 default can be restored with the deprecated UTF8_IS_UTF8MB3 flag of the old_mode system variable.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
- >= MariaDB 11.6: utf8mb4
- >= MariaDB 10.6: utf8mb3
- <= MariaDB 10.5: 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: utf8mb4 (>= MariaDB 11.6.0), latin1 (<= MariaDB 11.5)

`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.
Command line: --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. From MariaDB 10.6, the utf8 character set (and related collations) was by default an alias for utf8mb3 rather than the other way around. From MariaDB 13.1, utf8 is by default an alias for utf8mb4; the previous utf8mb3 default can be restored with the deprecated UTF8_IS_UTF8MB3 flag of the old_mode system variable.
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: utf8mb3 (>= MariaDB 10.6), utf8 (<= MariaDB 10.5)

`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.
Command line: --character-set-server
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value: utf8mb4 (>= MariaDB 11.6.0), latin1 (<= MariaDB 11.5)

`character_set_system`

Description: Character set used by the server to store identifiers, always set to utf8, or its synonym utf8mb3 starting with MariaDB 10.6. From MariaDB 10.6, the utf8 character set (and related collations) was by default an alias for utf8mb3 rather than the other way around. From MariaDB 13.1, utf8 is by default an alias for utf8mb4; the previous utf8mb3 default can be restored with the deprecated UTF8_IS_UTF8MB3 flag of the old_mode system variable.
Scope: Global
Dynamic: No
Data Type: string
Default Value: utf8mb3 (>= MariaDB 10.6), utf8 (<= MariaDB 10.5)

`character_sets_dir`

Description: Directory where the character sets are installed.
Command line: --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.
Scope: Global, Session
Dynamic: Yes
Type: boolean
Default: ON

`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.
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --connect-timeout=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 10
Range: 2 to 31536000

`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.
Command line: --core-file
Scope: Global
Dynamic: No
Type: boolean
Default Value:
- Windows >= MariaDB 10.4.3: ON
- All other systems: OFF

`datadir`

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

`date_format`

Description: Unused.
Removed: MariaDB 11.3.0

`datetime_format`

Description: Unused.
Removed: MariaDB 11.3.0

`debug/debug_dbug`

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 mariadbd is doing to the default trace file.
Command line: -#, --debug[=debug_options]
Scope: Global, Session
Dynamic: Yes
Data Type: string
Default Value:
- = 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.
Command line: --debug-no-thead-alarm=#
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Removed: MariaDB 11.4

`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 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 day. This behavior can be overridden using the password expiration options in ALTER USER.
Command line: --default-password-lifetime=#
Scope: Global
Dynamic: Yes
Type: numeric
Default Value: 0
Range: 0 to 4294967295

`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

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

`default_storage_engine`

Description: The default storage engine. The default storage engine must be enabled at server startup, or the server won't start.
Command line: --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.
Command line: --default-table-type=name
Scope: Global, Session
Dynamic: Yes
Removed: MariaDB 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. ROCKSDB temporary tables cannot be created. Before MariaDB 10.7, attempting to do so would silently fail, and a MyISAM table would instead be created. From MariaDB 10.7, an error is returned.
Command line: --default-tmp-storage-engine=name
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: NULL

`default_week_format`

Description: Default mode for the WEEK() function. See that page for details on the different modes
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --disconnect-on-expired-password[={0|1}]
Scope: Global
Dynamic: Yes
Type: boolean
Default Value: OFF

`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. div_precision_increment is taken into account in intermediate calculations. Instead of truncating decimal values after every division, they are instead truncated for comparison purposes only.
Command line: --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.
Command line: --encrypt-tmp-disk-tables[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`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.
Command line: --encrypt-tmp-files[={0|1}]
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF

`encryption_algorithm`

Description: Which encryption algorithm to use for table encryption. aes_cbc is the recommended one. See Table and Tablespace Encryption.
Command line: --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 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.
Command line: None
Scope: Session
Dynamic: Yes
Data Type: string
Default Value: none

`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.
Command line: --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), index dives are always used.
Command line: --eq-range-index-dive-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 200
Range: 0 to 4294967295

`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.
Command line: --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.
Command line: --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.
Command line: --explicit-defaults-for-timestamp=[={0|1}]
Scope:
- Global, Session (>= MariaDB 10.8.4, MariaDB 10.7.5, MariaDB 10.6.9, MariaDB 10.5.17)
- Global (<= MariaDB 10.8.3, MariaDB 10.7.4, MariaDB 10.6.8, MariaDB 10.5.16)
Dynamic:
- Yes (>= MariaDB 10.8.4, MariaDB 10.7.5, MariaDB 10.6.9, MariaDB 10.5.17)
- No (<= MariaDB 10.8.3, MariaDB 10.7.4, MariaDB 10.6.8, MariaDB 10.5.16)
Data Type: boolean
Default Value:ON (>= MariaDB 10.10), OFF (<= MariaDB 10.9)

`external_user`

Description: External user name set by the plugin used to authenticate the client. NULL if native MariaDB authentication is used. For example, from MariaDB 11.6, the Unix socket authentication plugin permits an authentication string, so that the OS and MariaDB user will be different. external_user then contains the external OS user. See Authentication Plugin - Unix Socket: Creating Users
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.
Command line: --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.
Command line: --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 specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0. Setting it to 0 suspends the checks on data only; it does not permit schema changes that would leave a constraint invalid, so a column used in a foreign key still cannot be dropped, renamed, or changed in type, character set, or collation. See Changing the Character Set or Collation of a Foreign Key Column.
Command line: 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 (marking the behavior by default). 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.
Command line: --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 in order for the new value to take effect. 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.
Command line: --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 in order for the new value to take effect. 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.
Command line: --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 number of top matches when using WITH QUERY EXPANSION.
Command line: --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.
Command line: --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.
Command line: --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. If --log-basename is also set, general_log_file should be placed after in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.
Command line: --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 the functions GROUP_CONCAT(), JSON_OBJECTAGG and JSON_ARRAYAGG.
Command line: --group-concat-max-len=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value:
- 1048576 (1M)
Range: 4 to 1073741824

`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.
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, or, from MariaDB 10.7 when histogram_type is set to JSON_HB, number of buckets. If set to 0, no histograms are created by ANALYZE.
Command line: --histogram-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 254
Range: 0 to 255

`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.
- JSON_HB - JSON height-balanced histograms (from MariaDB 10.8)
Command line: --histogram-type=value
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value:
- JSON_HB (>= MariaDB 11.0)
- DOUBLE_PREC_HB (<= MariaDB 10.11, >= MariaDB 10.4.3)
Valid Values:
- SINGLE_PREC_HB, DOUBLE_PREC_HB (<= MariaDB 10.6)
- SINGLE_PREC_HB, DOUBLE_PREC_HB, JSON_HB (>= MariaDB 10.8)

`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. Some container configs explicitly set host_cache_size to 0, rather than leave it as the default, 128.
Command line: --host-cache-size=#.
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 128
Range: 0 to 65536

`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

`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

`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

`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 for 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 commas.
Command line: --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.
Command line: --in-predicate-conversion-threshold=#
Scope: Global, Session
Dynamic: No
Data Type: numeric
Default Value: 1000
Range: 0 to 4294967295

`in_transaction`

Description: Session-only and read-only variable that is set to 1 if a transaction is in progress, 0 if not.
Command line: 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.
Command line: --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.
Command line: 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.
Command line: --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.
Command line: --join-buffer-size=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 262144 (256kB)
Range (non-Windows): 128 to 18446744073709547520
Range (Windows): 8228 to 18446744073709547520

`join_buffer_space_limit`

Description: Maximum size in bytes of the query buffer, By default 1024_128_10.
Command line: --join-buffer-space-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2097152
Range: 2048 to 18446744073709551615

`join_cache_level`

Description: Controls which of the eight block-based algorithms can be used for join operations.
- 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
Command line: --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. This variable was deprecated in MariaDB 10.8.0, but that deprecation was reverted in MariaDB 13.0 and the variable is no longer deprecated.
Command line: --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, 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 (since multiple page size support was added).
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0 (zero, from MariaDB 10.5.3)
Deprecated: MariaDB 10.5.3
Removed: MariaDB 12.0

`large_pages`

Description: Whether or not large page support 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)).
- Linux: The sysctl variable kernel.shmmax must be larger than llocation, and the sysctl variable vm.nr_hugepages must be larger than the usage. The ulimit for locked memory must be sufficient to cover the amount used (ulimit -l and equivalent 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 falls back to conventional memory allocation, and a warning appears in the logs. Only allocations of the default Hugepagesize currently occur (see /proc/meminfo). The implementation supports multiple page sizes using the Linux built-in huge page feature with the enhancements available in the Linux kernel 3.8 and later.
  - To configure Linux to use huge pages, set the hugepages and hugepagesz kernel parameters, which can be done via GRUB_CMDLINE_LINUX.
  - To find possible huge page size values, issue this command: ls -la /sys/devices/system/node/node0/hugepages/
  - To determine how many huge pages are free, issue this command: more /sys/devices/system/node/node*/hugepages/hugepages-*kB/free_hugepages
- Windows: The implementation uses the Windows GetLargePageMinimum() syscall to determine the supported page sizes.
  - This works only if the user that runs mariadbd has the SeLockMemoryPrivilege privilege.
- Solaris, FreeBSD, NetBSD: The implementation uses the getpagesizes() syscall to determine the supported page sizes.
  - On Solaris, find possible huge page sizes by calling getpagessizes syscall.
- macOS, OpenBSD: The implementation uses the getpagesize() syscall to determine the system's current page size.
Command line: --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 doesn'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.
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --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 means no wait. See WAIT and NOWAIT.
Command line: --lock-wait-timeout=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value:
- 86400 (1 day)
Range:
- 0 to 31536000

`locked_in_memory`

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

`log`

Description: Deprecated and removed in MariaDB 10.0, use general_log instead.
Command line: -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 and/or stored procedure statements) will not be logged to the general log. Multiple values are comma-separated, without spaces.
Command line: --log-disabled_statements=value
Scope: Global, Session
Dynamic: No
Data Type: set
Default Value: sp
Valid Values: slave and/or sp, or empty string for none

`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. Note that if --log-basename is also set, log_error should be placed after in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.
Command line: --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.
Command line: --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.
Command line: --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. See also log_slow_disabled_statements and log_slow_filter. Deprecated, use log_slow_filter without admin.
Command line: --log-slow-admin-statements
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value:
- ON
Deprecated: MariaDB 11.0.1

`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.
Command line: --log-slow-disabled_statements=value
Scope: Global, Session
Dynamic: No
Data Type: set
Default Value: sp
Valid Vales: admin, call, slave and/or sp

`log_slow_filter`

Description: Comma-delimited string (without spaces) 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 filesort on disk.
- filesort_priority_queue
- 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.
Command line: log-slow-filter=value1[,value2...]
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value:
- admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk
Valid Values:
- admin, filesort, filesort_on_disk, filesort_priority_queue, full_join, full_scan, not_using_index, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk

`log_slow_max_query_length`

Description: Queries whose text is longer than this many bytes are not written to the slow query log. The entire statement is skipped — it is not truncated — which keeps statements carrying very large TEXT or BLOB literals out of the log. The default, 4294967295 (the maximum value), effectively means no limit.
Command line: --log-slow-max-query-length=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295
Introduced: MariaDB 13.1.1

`log_slow_max_warnings`

Description: Max numbers of warnings printed to slow query log per statement
Command line: log-slow-max-warnings=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10
Range: 0 to 1000
Introduced: MariaDB 10.6.16, MariaDB 10.10.7, MariaDB 10.11.6, MariaDB 11.0.4, MariaDB 11.1.3

`log_slow_min_examined_row_limit`

Description: Don't write queries to slow query log that examine fewer rows than the set value. If set to 0, the default, no row limit is used. min_examined_row_limit is an alias. From MariaDB 11.7, queries slower than log_slow_always_query_time will always be logged.
Command line: --log-slow-min-examined-row-limit=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0-4294967295
Introduced: MariaDB 10.11

`log_slow_queries`

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

`log_slow_query`

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. Named slow_query_log before MariaDB 10.11.0, which is now an alias.
Command line: --slow-query-log
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Introduced: MariaDB 10.11.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 log_slow_query is set to 1.

`log_slow_query_file`

Description: Name of the slow query log file. Before MariaDB 10.11, was named slow_query_log_file. This was named log_slow_query_file_name in the MariaDB 10.11.0 preview release. If --log-basename is also set, log_slow_query_file should be placed after in the config files. Later settings override earlier settings, so log-basename will override any earlier log file name settings.
Command line: --log-slow-query-file=file_name
Scope: Global
Dynamic: Yes
Data Type: file name
Default Value: host_name-slow.log
Introduced: MariaDB 10.11.0

`log_slow_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. Before MariaDB 10.11, was named long_query_time. Affected by log_slow_rate_limit and log_slow_min_examined_row_limit.
Command line: --log-slow-query-time=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10.000000
Range: 0 to 31536000
Introduced: MariaDB 10.11.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. From MariaDB 11.7, queries slower than log_slow_always_query_time will always be logged.
Command line: 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 Alias to engine (from MariaDB 10.6.15 and MariaDB 10.11.5), previously ignored.
- explain prints EXPLAIN output in the slow query log. See EXPLAIN in the Slow Query Log.
- engine Logs engine statistics (from MariaDB 10.6.15 and MariaDB 10.11.5).
- warnings Print all errors, warnings and notes for the statement to the slow query log. (from MariaDB 10.6.16).
- all Enables all above options (From MariaDB 10.6.16)
- full Enables all above options.
Command line: log-slow-verbosity=value1[,value2...]
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: (Empty)
Valid Values:
- = MariaDB 10.6.16, MariaDB 10.11.6: (Empty), query_plan, innodb, explain, engine, warnings, all, full
- = MariaDB 10.6.15, MariaDB 10.11.5: (Empty), query_plan, innodb, explain, engine, full
- <= MariaDB 10.6.14, MariaDB 10.11.4: (Empty), query_plan, innodb, explain

`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. See Transaction Coordinator Log for more information. Also see the --log-tc server option and the --tc-heuristic-recover option.
Command line: log-tc-size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 24576
Range: 12288 to 18446744073709551615

`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 = 0
  - No notes from the event scheduler, only warnings and errors (from MariaDB 10.11.16, 11.4.10, 11.8.6, 12.2.2)
- log_warnings >= 1
  - Only startup and shutdown event scheduler notes (from MariaDB 10.11.16, 11.4.10, 11.8.6, 12.2.2)
  - 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 without authentication.
  - Connections aborted due to KILL.
  - Connections closed due to released connections, such as when completion_type is set to RELEASE.
  - Could not read packet: (a lot more information)
  - All read/write errors for a connection are logged to the error log.
- log_warnings >=9
  - Information about initializing plugins.
Command line: -W [level] or --log-warnings[=level]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2
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. From MariaDB 10.11.0, this is an alias for log_slow_query_time.
Command line: --long-query-time=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 10.000000
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.
Command line: --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. This system variable's value cannot be changed after the datadir has been initialized. lower_case_table_names is set when a MariaDB instance starts, and it remains constant afterwards.
Command line: --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.
Command line: --max-allowed-packet=#
Scope: Global, Session
Dynamic: Yes (Global), No (Session)
Data Type: numeric
Default Value:
- 16777216 (16M)
- 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 mariadb-admin flush-hosts. The performance_schema.host_cache table contains the status of the current hosts.
Command line: --max-connect-errors=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 100
Range: 1 to 4294967295

`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). Note that MariaDB always has one reserved connection for a SUPER (or CONNECTION ADMIN user). Additionally, it can listen on a separate port, so will be available even when the max_connections limit is reached.
Command line: --max-connections=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 151
Range: 10 to 100000

Systemd thread limit (MDEV-30236) When running MariaDB under systemd, be aware that systemd's default TasksMax (≈ 4,915 tasks per service) may prevent reaching high max_connections values—even if configured higher. Starting with MariaDB 10.4.33, the systemd unit includes:

This setting removes the artificial cap, allowing max_connections to scale per your configuration (subject to OS memory and thread limits).

`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.
Command line: --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.
Command line: --max-digest-length=#
Scope: Global,
Dynamic: No
Data Type: numeric
Default Value: 1024
Range: 0 to 1048576

`max_error_count`

Description: Specifies the maximum number of messages stored for display by SHOW ERRORS and SHOW WARNINGS statements.
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --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.
Command line: --max-long-data-size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 16777216 (16M)
Range: 1024 to 4294967295
Deprecated: MariaDB 5.5
Removed: MariaDB 10.5.0

`max_open_cursors`

Description: The maximum number of open cursors allowed per session.
Command line: --max-open-cursors=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 50
Range: 0 to 65536
Introduced: MariaDB 12.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 not applicable for users with the SUPER privilege or, from MariaDB 10.5.2, the CONNECTION ADMIN privilege, with a hostname of localhost, 127.0.0.1 or ::1. See also the Information Schema USERS table.
Command line: --max-password-errors=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295

`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.
Command line: --max-prepared-stmt-count=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 16382
Range: 0 to 4294967295

`max_recursive_iterations`

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

`max_rowid_filter_size`

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

`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.
Command line: --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.
Command line: --max-session-mem-used=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 9223372036854775807 (8192 PB)
Range: 8192 to 18446744073709551615

`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. Increasing this may require sort_buffer_size to be increased (especially if ER_OUT_OF_SORTMEMORY errors start appearing). From MariaDB 11.7, a warning is generated when max_sort_length is exceeded.
Command line: --max-sort-length=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range:
- 4 to 8388608 (<= MariaDB 10.4.13, MariaDB 10.5.3)
- 8 to 8388608 (>= MariaDB 10.4.14, MariaDB 10.5.4)
- 64 to 8388608 (>= MariaDB 10.4.16, MariaDB 10.5.7)

`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.
Command line: --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. Replicas are not affected by this variable, however, from MariaDB 10.10, there's slave_max_statement_time that sets the limit to abort queries on a replica.
Command line: --max-statement-time[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 0.000000
Range: 0 to 31536000

`max_tmp_tables`

Description: Unused.
Removed: MariaDB 11.3.0

`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.
Command line: --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.
Command line: --max-write-lock-count=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295

`metadata_locks_cache_size`

Description: Unused since 10.1.4
Command line: --metadata-locks-cache-size=#
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 1024
Range: 1 to 1048576