About the Server System Variables

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

Most of the system variables are described on this page, but some are described elsewhere:

• Aria System Variables

• CONNECT System Variables

• Galera System Variables

• Global Transaction ID System Variables

• HandlerSocket Plugin System Variables

• InnoDB System Variables

• Mroonga System Variables

• MyRocks System Variables

• MyISAM System Variables

• Performance Schema System Variables

• Replication and Binary Log System Variables

• S3 Storage Engine System Variables

• Server_Audit System Variables

• Spider System Variables

• SQL_ERROR_LOG Plugin System Variables

• SSL System Variables

• Threadpool System Variables

• TokuDB System Variables

• Vector System Variables

See also theFull list of MariaDB options, system and status variables.

Most of these can be set withcommand 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:

SHOW VARIABLES;

• SeeSHOW VARIABLES for instructions on using this command.

• From your shell, run mariadbd like so:

mariadbd --verbose --help

• View the Information SchemaGLOBAL_VARIABLES,SESSION_VARIABLES, andSYSTEM_VARIABLES tables.

Setting Server System Variables

There are several ways to set server system variables:

• Specify them on the command line:

shell> ./mariadbd-safe --aria_group_commit="hard"

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

aria_group_commit = "hard"

• Set them from the mariadb client using theSET 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:

The suffix can be upper or lower-case.

List of Server System Variables

allow_suspicious_udfs

• Description: Allows use ofuser-defined functions consisting of only one symbolx() without correspondingx_init() orx_deinit(). That also means that one can load any function from any library, for exampleexit() fromlibc.so. Not recommended unless you require old UDFs with one symbol that cannot be recompiled. BeforeMariaDB 10.10, available as anoption only.

• Commandline:--allow-suspicious-udfs

• Scope: Global

• Dynamic: No

• Data Type:boolean

• Default Value:OFF

• Introduced:MariaDB 10.10

alter_algorithm

• Description: The impliedALGORITHM forALTER TABLE if noALGORITHM clause is specified. The deprecated variableold_alter_table is an alias for this. The feature was removed inMariaDB 11.5. SeeALGORITHM=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) choosesINPLACE if available, and falls back toCOPY.

  • 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

• Deprecated:MariaDB 11.5

analyze_sample_percentage

• Description: Percentage of rows from the tableANALYZE 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 to100

• Introduced:MariaDB 10.4.3

autocommit

• Description: If set to 1, the default, all queries are committed immediately. TheLOCK IN SHARE MODE andFOR UPDATE clauses therefore have no effect. If set to 0, they are only committed upon aCOMMIT statement, or rolled back with aROLLBACK 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 toALTER (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 to0, or the--autoset-back-log option is used, will be autoset to the lower of900 and (50 +max_connections/5).

• Commandline:--back-log=#

• Scope: Global

• Dynamic: No

• Type: number

• Default Value:

  • The lower of900 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 thetmp_memory_table_size system variable.

  • To prevent memory-based temporary tables from being used at all, set thetmp_memory_table_size system variable to0.

  • InMariaDB 5.5 and earlier,sql_big_tables is a synonym.

  • FromMariaDB 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 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 amariadbd option; fromMariaDB 10.3.3its also available as a system variable. BeforeMariaDB 10.6.0"::" implied listening additionally on IPv4 addresses like "". From 10.6.0 onwards it refers to IPv6 stictly. Starting withMariaDB 10.11, a comma-separated list of addresses to bind to can be given. See alsoConfiguring MariaDB for Remote Client Access.

• Commandline:--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 forAES_ENCRYPT() andAES_DECRYPT() functions.

• Commandline:--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 intoMyISAM andAria 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 to4294967295

• Range - 64 bit:0 to18446744073709547520

character_set_client

• Description: Determines thecharacter 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. FromMariaDB 10.6, theutf8character set (and related collations) is by default an alias forutf8mb3 rather than the other way around. It can be set to implyutf8mb4 by changing the value of theold_mode system variable.

• Scope: Global, Session

• Dynamic: Yes

• Data Type:string

• Default Value:utf8mb3 (>=MariaDB 10.6),utf8 (<=MariaDB 10.5)

character_set_collations

• Description: Overrides for character set default collations. Takes a comma-delimited list of character set and collation settings, for exampleSET @@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. FromMariaDB 10.6, theutf8character set (and related collations) is by default an alias forutf8mb3 rather than the other way around. It can be set to implyutf8mb4 by changing the value of theold_mode system variable.

• Scope: Global, Session

• Dynamic: Yes

• Data Type:string

• Default Value:utf8mb3 (>=MariaDB 10.6),utf8 (<=MariaDB 10.5)

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 ascharacter_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: Thecharacter set for the filesystem. Used for converting file names specified as a string literal fromcharacter_set_client to character_set_filesystem before opening the file. By default set tobinary, so no conversion takes place. This could be useful for statements such asLOAD_FILE() orLOAD 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. FromMariaDB 10.6, theutf8character set (and related collations) is by default an alias forutf8mb3 rather than the other way around. It can be set to implyutf8mb4 by changing the value of theold_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: Defaultcharacter set used by the server. Seecharacter_set_database for character sets used by the default database. Defaults may be different on some systems, see for exampleDifferences in MariaDB in Debian.

• Commandline:--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 withMariaDB 10.6. FromMariaDB 10.6, theutf8character set (and related collations) is by default an alias forutf8mb3 rather than the other way around. It can be set to implyutf8mb4 by changing the value of theold_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 thecharacter sets are installed.

• Commandline:--character-sets-dir=path

• Scope: Global

• Dynamic: No

• Type: directory name

check_constraint_checks

• Description: If set to0, will disableconstraint 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 connectioncharacter 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 thecollation_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: Defaultcollation used by the server. This is set to the default collation for a given character set automatically whencharacter_set_server is changed, but it can also be set manually. Defaults may be different on some systems, see for exampleDifferences 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 toNO_CHAIN or0 (the default), there is no effect on commits and rollbacks. If set toCHAIN or1, aCOMMIT statement is equivalent to COMMIT AND CHAIN, while aROLLBACK 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 toRELEASE or2, aCOMMIT statement is equivalent to COMMIT RELEASE, while aROLLBACK 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 toAUTO or1, the default, MariaDB allowsconcurrent INSERTs and SELECTs forMyISAM tables with no free blocks in the data (deleted rows in the middle). If set toNEVER or0, concurrent inserts are disabled. If set toALWAYS or2, 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 toNEVER. Changing the variable only affects new opened tables. UseFLUSH TABLES If you want it to also affect cached tables. SeeConcurrent 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

• Range:2 to31536000

core_file

• Description: Write a core-file on crashes. The file name and location are system dependent. On Linux it is usually calledcore.${PID}, and it is usually written to the data directory. However, this can be changed.

  • SeeEnabling Core Dumps for more information.

  • Previously this system variable existed only as anoption, but it was also made into a read-only system variable starting withMariaDB 10.3.9,MariaDB 10.2.17 andMariaDB 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 toON. 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

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.

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

• Commandline:-#,--debug[=debug_options]

• Scope: Global, Session

• Dynamic: Yes

• Data Type:string

• Default Value:

  • =MariaDB 10.5:d:t:i:o,/tmp/mariadbd.trace (Unix) ord:t:i:O,\mariadbd.trace (Windows)

• Debug Options: See the option flags on themysql_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

• Removed:MariaDB 11.4

debug_sync

• Description: Used in debugging to show the interface to theDebug 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 orON - current signal signal name

default_password_lifetime

• Description: This defines the globalpassword 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 inALTER USER.

• Commandline:--default-password-lifetime=#

• Scope: Global

• Dynamic: Yes

• Type: numeric

• Default Value:0

• Range:0 to4294967295

default_regex_flags

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

• Commandline:--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 defaultstorage 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 fordefault_storage_engine. Removed inMariaDB 5.5.

• Commandline:--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 withCREATE TEMPORARY TABLE where no engine is specified. For internal temporary tables seearia_used_for_temp_tables). The storage engine used must be active or the server will not start. Seedefault_storage_engine for the default for non-temporary tables. Defaults to NULL, in which case the value fromdefault_storage_engine is used.ROCKSDB temporary tables cannot be created. BeforeMariaDB 10.7, attempting to do so would silently fail, and a MyISAM table would instead be created. FromMariaDB 10.7, an error is returned.

• Commandline:--default-tmp-storage-engine=name

• Scope: Global, Session

• Dynamic: Yes

• Data Type:enumeration

• Default Value: NULL

default_week_format

• Description: Default mode for theWEEK() 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 to7

delay_key_write

• Description: Specifies how MyISAM tables handlesCREATE TABLE DELAY_KEY_WRITE. If set toON, 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 toOFF, DELAY KEY WRITEs are ignored, while if set toALL, 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 withINSERT DELAYED, the handler will check for and execute any waitingSELECT statements.

• Commandline:--delayed-insert-limit=#

• Scope: Global

• Dynamic: Yes

• Data Type:numeric

• Default Value:100

• Range:1 to4294967295

delayed_insert_timeout

• Description: Time in seconds that theINSERT 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 performingINSERT 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 (seeUser 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

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's4, soSELECT 2/15 would return 0.1333 andSELECT 2.0/15 would return 0.13333. After setting div_precision_increment to6, for example, the same operation would return 0.133333 and 0.1333333 respectively.

FromMariaDB 10.1.46,MariaDB 10.2.33,MariaDB 10.3.24,MariaDB 10.4.14 andMariaDB 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.

InMariaDB 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 andMariaDB 10.5.6 only, the fix truncated decimal values after every division, resulting in lower precision in some cases for those versions only.

FromMariaDB 10.1.48,MariaDB 10.2.35,MariaDB 10.3.26,MariaDB 10.4.16 andMariaDB 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 thanMariaDB 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 andMariaDB 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 andMariaDB 10.5.6 only:

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

This is because the intermediate result,SELECT 55/23244 takes into accountdiv_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 to30

encrypt_tmp_disk_tables

• Description: Enables automatic encryption of all internal on-disk temporary tables that are created during query execution ifaria_used_for_temp_tables=ON is set. SeeData at Rest Encryption andEnabling Encryption for Internal On-disk Temporary Tables.

• Commandline:--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. SeeData at Rest Encryption.

• Commandline:--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. SeeTable 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 toInnoDB will prevent anyMyISAM tables from being created. If another engine is specified in aCREATE TABLE statement, the outcome depends on whether theNO_ENGINE_SUBSTITUTIONSQL_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.

• Commandline: None

• Scope: Session

• Dynamic: Yes

• Data Type:string

• Default Value:none

engine_condition_pushdown

• Description: Deprecated inMariaDB 5.5 and removed and replaced by theoptimizer_switchengine_condition_pushdown={on|off} flag inMariaDB 10.0.. Specifies whether the engine condition pushdown optimization is enabled. SinceMariaDB 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 to0 (unlimited), index dives are always used.

• Commandline:--eq-range-index-dive-limit=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:200

• Range:0 to4294967295

error_count

• Description: Read-only variable denoting the number of errors from the most recent statement in the current session that generated errors. SeeSHOW_ERRORS().

• Scope: Session

• Dynamic: Yes

• Data Type:numeric

event_scheduler

• Description: Status of theEvent Scheduler. Can be set toON orOFF, whileDISABLED 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 (or1),OFF (or0),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 causesCREATE TABLE to create allTIMESTAMP columns asNULL 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, 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, fromMariaDB 11.6, theUnix 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. SeeAuthentication 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 toON, 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 MODEfull-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.

• 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 theMyISAMfull-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 aREPAIR TABLE table_name QUICK statement. Seeinnodb_ft_max_token_size for theInnoDB 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 theMyISAMfull-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 aREPAIR TABLE table_name QUICK statement. Seeinnodb_ft_min_token_size for theInnoDB equivalent.

• Commandline:--ft-min-word-len=#

• Scope: Global

• Dynamic: No

• Data Type:numeric

• Default Value:4

• Minimum Value:1

ft_query_expansion_limit

• Description: Forfull-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 to1000

ft_stopword_file

• Description: File containing a list ofstopwords for use inMyISAMfull-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 fromstorage/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 aREPAIR TABLE table_name QUICK statement. Seeinnodb_ft_server_stopword_table for theInnoDB 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, thegeneral query log is disabled, while if set to 1, the general query log is enabled. Seelog_output for how log files are written. If that variable is set toNONE, no logs will be written even if general_query_log is set to1.

• Commandline:--general-log

• Scope: Global

• Dynamic: Yes

• Data Type:boolean

• Default Value:0

general_log_file

• Description: Name of thegeneral query log file. If this is not specified, the name is taken from thelog-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, solog-basename will override any earlier log file name settings.

• 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 the functionsGROUP_CONCAT(),JSON_OBJECTAGG andJSON_ARRAYAGG.

• Commandline:--group-concat-max-len=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:

  • 1048576 (1M)

• Range:4 to4294967295

.

have_compress

• Description: If the zlib compression library is accessible to the server, this will be set toYES, otherwise it will beNO. TheCOMPRESS() andUNCOMPRESS() functions will only be available if set toYES.

• Scope: Global

• Dynamic: No

have_crypt

• Description: If the crypt() system call is available this variable will be set toYES, otherwise it will be set toNO. If set toNO, theENCRYPT() function cannot be used.

• Scope: Global

• Dynamic: No

have_csv

• Description: If the server supportsCSV tables, will be set toYES, otherwise will be set toNO. Removed inMariaDB 10.0, use theInformation Schema PLUGINS table orSHOW ENGINES instead.

• Scope: Global

• Dynamic: No

• Removed:MariaDB 10.0

have_dynamic_loading

• Description: If the server supports dynamic loading ofplugins, will be set toYES, otherwise will be set toNO.

• Scope: Global

• Dynamic: No

have_geometry

• Description: If the server supports spatial data types, will be set toYES, otherwise will be set toNO.

• 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 toYES, unless the--skip-partition option is used, in which case will be set toDISABLED. Will be set toNO otherwise. Removed inMariaDB 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 toYES, otherwise will be set toNO. SeeSHOW PROFILES() andSHOW PROFILE().

• Scope: Global

• Dynamic: No

have_query_cache

• Description: If the server supports thequery cache, will be set toYES, otherwise will be set toNO.

• Scope: Global

• Dynamic: No

have_rtree_keys

• Description: If RTREE indexes (used forspatial indexes) are available, will be set toYES, otherwise will be set toNO.

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

  • If symbolic links are not supported, then the value will beNO.

  • If symbolic links are disabled with the--symbolic-links option and theskipoption prefix (i.e. --skip-symbolic-links), then the value will beDISABLED.

  • Symbolic link support is required for theINDEX DIRECTORY andDATA DIRECTORY table options.

• Scope: Global

• Dynamic: No

histogram_size

• Description: Number of bytes used for ahistogram, or, fromMariaDB 10.7 whenhistogram_type is set toJSON_HB, number of buckets. If set to 0, no histograms are created byANALYZE.

• Commandline:--histogram-size=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:254

• Range:0 to255

histogram_type

• Description: Specifies the type ofhistograms created byANALYZE..

  • SINGLE_PREC_HB - single precision height-balanced.

  • DOUBLE_PREC_HB - double precision height-balanced.

  • JSON_HB - JSON height-balanced histograms (fromMariaDB 10.8)

• Commandline:--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 to0 disables the cache. Changing the value while the server is running causes an implicitFLUSH HOSTS, clearing the host cache and truncating theperformance_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 to65536

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 forlast_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 to0, the default, connections are never killed. See alsoidle_transaction_timeout,idle_write_transaction_timeout andTransaction Timeouts.

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:0

• Range:0 to31536000

idle_transaction_timeout

• Description: Time in seconds that the server waits for idle transactions before killing the connection. If set to0, the default, connections are never killed. See alsoidle_readonly_transaction_timeout,idle_write_transaction_timeout andTransaction Timeouts.

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:0

• Range:0 to31536000

idle_write_transaction_timeout

• Description: Time in seconds that the server waits for idle read-write transactions before killing the connection. If set to0, the default, connections are never killed. See alsoidle_transaction_timeout,idle_readonly_transaction_timeout andTransaction Timeouts. Calledidle_readwrite_transaction_timeout untilMariaDB 10.3.2.

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:0

• Range:0 to31536000

ignore_db_dirs

• Description: Tells the server that this directory can never be a database. That means two things - firstly it is ignored by theSHOW DATABASES command andINFORMATION_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 commas.

• 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. SeeConversion of Big IN Predicates Into Subqueries.

• Commandline:--in-predicate-conversion-threshold=#

• Scope: Global, Session

• Dynamic: No

• Data Type:numeric

• Default Value:1000

• Range:0 to4294967295

in_transaction

• Description: Session-only and read-only variable that is set to1 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 theSUPER privilege or, fromMariaDB 10.5.2, theCONNECTION ADMIN privilege, who can then still connect and correct the error. See alsoinit_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 alsoinit_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 newAUTO_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 alsowait_timeout.

• Commandline:--interactive-timeout=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:28800

• Range: (Windows):1 to2147483

• Range: (Other):1 to31536000

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.

• Commandline:--join-buffer-size=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:262144 (256kB)

• Range (non-Windows):128 to18446744073709547520

• Range (Windows):8228 to18446744073709547520

join_buffer_space_limit

• Description: Maximum size in bytes of the query buffer, By default 102412810.

• Commandline:--join-buffer-space-limit=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:2097152

• Range:2048 to18446744073709551615

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

• Commandline:--join-cache-level=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:2

• Range:0 to8

keep_files_on_create

• Description: If aMyISAM table is created with no DATA DIRECTORY option, the .MYD file is stored in the database directory. When set to0, the default, if MariaDB finds another .MYD file in the database directory it will overwrite it. Setting this variable to1 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. Deprecated inMariaDB 10.8.0.

• Commandline:--keep-files-on-create=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:boolean

• Default Value:OFF

• Deprecated:MariaDB 10.8.0

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. Seelarge_pages. Deprecated and unused inMariaDB 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 (prior toMariaDB 10.5, Linux only, by now supported Windows and BSD distros, also called huge pages) is used. This is set with--large-pages or disabled with--skip-large-pages. Large pages are used for theinnodb buffer pool and for online DDL (of size 3*innodb_sort_buffer_size (or 6 when encryption is used)). To use large pages, the Linuxsysctl variablekernel.shmmax must be large than the llocation. Also thesysctl variablevm.nr_hugepages multipled bylarge-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 systemdLimitMEMLOCK). 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 defaultHugepagesize 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 byLAST_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 alocale name. The language of the associatedlocale will be used for error messages. SeeServer Locales for a list of supported locales and their associated languages.

  • This system variable is set toen_US by default, which means that error messages are in English by default.

  • If this system variable is set to a validlocale name, but the server can't find anerror message file for the language associated with thelocale, then the default language will be used instead.

  • This system variable is used along with thelc_messages_dir system variable to construct the path to theerror messages file.

  • SeeSetting 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'serror message files or as the path to the directory storing the specific language'serror message file. SeeServer 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'serror message files. Therefore, it constructs the path to the language'serror message file by concatenating the value of this system variable with the language name of thelocale specified by thelc_messages system variable .

  • If the server does not find theerror 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'serror message file.

  • SeeSetting 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 functionsDAYNAME(),MONTHNAME() andDATE_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. Seeserver 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

legacy_xa_rollback_at_disconnect

• Description: If a user session disconnects after putting a transaction into theXA PREPARE state, roll back the transaction. Can be used for backwards compatibility to enable this pre-10.5 behavior for applications that expect it. Note that this violates the XA Specification and should not be used for new code.

• Scope: Global, Session

• Dynamic: Yes

• Data Type:boolean

• Introduced:MariaDB 10.5.27,MariaDB 10.6.20,MariaDB 10.11.10,MariaDB 11.4.4,MariaDB 11.7.1

license

• Description: Server license, for exampleGPL.

• Scope: Global

• Dynamic: No

• Data Type:string

local_infile

• Description: If set to1, LOCAL is supported forLOAD DATA INFILE statements. If set to0, 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 acquiremetadata locks. Statements using metadata locks includeFLUSH TABLES WITH READ LOCK,LOCK TABLES, HANDLER and DML and DDL operations on tables,stored procedures andfunctions, andviews. The timeout is separate for each attempt, of which there may be multiple in a single statement.0 means no wait. SeeWAIT and NOWAIT.

• Commandline:--lock-wait-timeout=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:

  • 86400 (1 day)

• Range:

  • 0 to31536000

locked_in_memory

• Description: Indicates whether --memlock was used to lock mariadbd in memory.

• Commandline:--memlock

• Scope: Global

• Dynamic: No

• Data Type:boolean

• Default Value:OFF

log

• Description: Deprecated and removed inMariaDB 10.0, usegeneral_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 and/or stored procedure statements) will not be logged to thegeneral log. Multiple values are comma-separated, without spaces.

• Commandline:--log-disabled_statements=value

• Scope: Global, Session

• Dynamic: No

• Data Type:set

• Default Value:sp

• Valid Values:slave and/orsp, or empty string for none

log_error

• Description: Specifies the name of theerror 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 tohostname.err in thedatadir 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. Seeerror 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, solog-basename will override any earlier log file name settings.

• 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 thegeneral query log and theslow query log is stored. By default written to file (FILE), it can also be stored in thegeneral_log andslow_log tables in the mysql database (TABLE), or not stored at all (NONE). More than one option can be chosen at the same time, withNONE taking precedence if present. Logs will not be written if logging is not enabled. SeeWriting logs into tables, and theslow_query_log andgeneral_log server system variables.

• Commandline:--log-output=name

• Scope: Global

• Dynamic: Yes

• Data Type:set

• Default Value:FILE

• Valid Values:TABLE,FILE orNONE

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 theslow query log (regardless of time taken). The slow query log needs to be enabled for this to have an effect. Mapped tolog_slow_filter='not_using_index' fromMariaDB 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 slowOPTIMIZE,ANALYZE,ALTER and otheradministrative statements to theslow log if it is open. See alsolog_slow_disabled_statements andlog_slow_filter. Deprecated, uselog_slow_filter withoutadmin.

• Commandline:--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 theslow query log. See alsolog_slow_admin_statements andlog_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/orsp

log_slow_filter

• Description: Comma-delimited string (without spaces) containing one or more settings for filtering what is logged to theslow query log. If a query matches one of the types listed in the filter, and takes longer thanlong_query_time, it will be logged(except for 'not_using_index' which is always logged if enabled, regardless of the time). Setslog-slow-admin-statements to ON. See alsolog_slow_disabled_statements.

  • admin logadministrative 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

  • 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. Disregardslong_query_time, unlike other options.log_queries_not_using_indexes maps to this option. FromMariaDB 10.3.1.

  • query_cache log queries that are resolved by the query cache.

  • query_cache_miss logs queries that are not found in thequery 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,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_warnings

• Description: Max numbers of warnings printed to slow query log per statement

• Commandline:log-slow-max-warnings=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:10

• Range:0 to1000

• 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 toslow query log that examine fewer rows than the set value. If set to0, the default, no row limit is used.min_examined_row_limit is an alias. FromMariaDB 11.7, queries slower thanlog_slow_always_query_time will always be logged.

• Commandline:--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 inMariaDB 10.0, useslow_query_log instead.

• Commandline:--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, theslow query log is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. Namedslow_query_log beforeMariaDB 10.11.0, which is now an alias.

• Commandline:--slow-query-log

• Scope: Global, Session

• Dynamic: Yes

• Data Type:boolean

• Default Value:0

• Introduced:MariaDB 10.11.0

• See also: Seelog_output to see how log files are written. If that variable is set toNONE, no logs will be written even if log_slow_query is set to1.

log_slow_query_file

• Description: Name of theslow query log file. BeforeMariaDB 10.11, was namedslow_query_log_file. This was namedlog_slow_query_file_name in theMariaDB 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, solog-basename will override any earlier log file name settings.

• Commandline:--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), theSlow_queries status variable is incremented and, if enabled, the query is logged to theslow query log. BeforeMariaDB 10.11, was namedlong_query_time. Affected bylog_slow_rate_limit andlog_slow_min_examined_row_limit.

• Commandline:--log-slow-query-time=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:10.000000

• Range:0 to31536000

• Introduced:MariaDB 10.11.0

log_slow_rate_limit

• Description: Theslow query log will log every this many queries. The default is1, or every query, while setting it to20 would log every 20 queries, or five percent. Aims to reduce I/O usage and excessively large slow query logs. See alsoSlow Query Log Extended Statistics. FromMariaDB 11.7, queries slower thanlog_slow_always_query_time will always be logged.

• 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 theslow query log. Options are added in a comma-delimited string. See alsoSlow Query Log Extended Statistics. log_slow_verbosity is not supported when log_output='TABLE'.

  • query_plan logs query execution plan information

  • innodb Alias toengine (fromMariaDB 10.6.15 andMariaDB 10.11.5), previously ignored.

  • explain prints EXPLAIN output in theslow query log. SeeEXPLAIN in the Slow Query Log.

  • engine Logs engine statistics (fromMariaDB 10.6.15 andMariaDB 10.11.5).

  • warnings Print all errors, warnings and notes for the statement to the slow query log. (fromMariaDB 10.6.16).

  • all Enables all above options (FromMariaDB 10.6.16)

  • full Enables all above options.

• Commandline: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 thebinary 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. SeeTransaction 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 to18446744073709551615

log_warnings

• Description: Determines which additional warnings are logged. Setting to0 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.

• Disabledplugins 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 topersist replication state:

• Either the defaultmaster.info file or the file that is configured by themaster_info_file option.

• Either the defaultrelay-log.info file or the file that is configured by therelay_log_info_file system variable.

• Information about a master'sbinary log dump thread.

• log_warnings >= 3

• All errors and warnings duringMyISAM repair and auto recover.

• Information about old-style language options.

• Information aboutprogress of InnoDB online DDL.

• log_warnings >=4

• Connections aborted due to "Too many connections" errors.

• Connections closed normally without authentication.

• Connections aborted due toKILL.

• Connections closed due to released connections, such as whencompletion_type is set toRELEASE.

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

• Commandline:-W [level] or--log-warnings[=level]

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:2

• Range:0 to4294967295

long_query_time

• Description: If a query takes longer than this many seconds to execute (microseconds can be specified too), theSlow_queries status variable is incremented and, if enabled, the query is logged to theslow query log. FromMariaDB 10.11.0, this is an alias forlog_slow_query_time.

• Commandline:--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), forstorage engines that use only table-level locking (Aria,MyISAM,MEMORY andMERGE), 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.

  • InMariaDB 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 toOFF, file names are case-sensitive. If set toON, they are not case-sensitive.

• Scope: Global

• Dynamic: No

• Data Type:boolean

• Default Value:##

lower_case_table_names

• Description: If set to0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to2 (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.

• Commandline:--lower-case-table-names[=#]

• Scope: Global

• Dynamic: No

• Data Type:numeric

• Default Value:0 (Unix),1 (Windows),2 (Mac OS X)

• Range:0 to2

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 fromnet_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. Seeslave_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)

  • 1073741824 (1GB) (client-side)

• Range:1024 to1073741824

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 aFLUSH HOSTS statement ormariadb-admin flush-hosts. Theperformance_schema.host_cache table contains the status of the current hosts.

• Commandline:--max-connect-errors=#

• Scope: Global

• Dynamic: Yes

• Data Type:numeric

• Default Value:100

• Range:1 to4294967295

max_connections

• Description: The maximum number of simultaneous client connections. See alsoHandling Too Many Connections. Note that this value affects the number of file descriptors required on the operating system. Minimum was changed from1 to10 to avoid possible unexpected results for the user (MDEV-18252). Note that MariaDB always has one reserved connection for aSUPER (orCONNECTION ADMIN user). Additionally it can listen on a separate port, so will be available even when the max_connections limit is reached.

• Commandline:--max-connections=#

• Scope: Global

• Dynamic: Yes

• Data Type:numeric

• Default Value:151

• Range:10 to100000

max_delayed_threads

• Description: Limits to the number ofINSERT DELAYED threads. Once this limit is reached, the insert is handled as if there was no DELAYED attribute. If set to0, DELAYED is ignored entirely. The session value can only be set to0 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 to16384

max_digest_length

• Description: Maximum length considered for computing a statement digest, such as used by thePerformance 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: No

• Data Type:numeric

• Default Value:1024

• Range:0 to1048576

max_error_count

• Description: Specifies the maximum number of messages stored for display bySHOW ERRORS andSHOW WARNINGS statements.

• Commandline:--max-error-count=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:64

• Range:0 to65535

max_heap_table_size

• Description: Maximum size in bytes for user-createdMEMORY 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 andtmp_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 withCREATE 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 to4294966272

max_insert_delayed_threads

• Description: Synonym formax_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 resetsql_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 thequery cache. Previously namedsql_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 to18446744073709551615

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 thanmax_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 to8388608

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 ofmax_allowed_packet. Deprecated inMariaDB 5.5 and removed inMariaDB 10.5.0; usemax_allowed_packet instead.

• Commandline:--max-long-data-size=#

• Scope: Global

• Dynamic: No

• Data Type:numeric

• Default Value:

  • 16777216 (16M)

• Range:1024 to4294967295

• 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 not applicable for users with theSUPER privilege or, fromMariaDB 10.5.2, theCONNECTION ADMIN privilege, with a hostname of localhost, 127.0.0.1 or ::1. See also theInformation Schema USERS table.

• Commandline:--max-password-errors=#

• Scope: Global

• Dynamic: Yes

• Data Type:numeric

• Default Value:4294967295

• Range:1 to4294967295

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 to0, 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 to4294967295

max_recursive_iterations

• Description: Maximum number of iterations when executing recursive queries, used to prevent infinite loops inrecursive CTEs.

• Commandline:--max-recursive-iterations=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:1000 (>=MariaDB 10.6.0),4294967295 (<=MariaDB 10.5)

• Range:0 to4294967295

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 to18446744073709551615

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 to4294967295

max_session_mem_used

• Description: Amount of memory a single user session is allowed to allocate. This limits the value of the session variableMemory_used.

• Commandline:--max-session-mem-used=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:9223372036854775807 (8192 PB)

• Range:8192 to18446744073709551615

max_sort_length

• Description: Maximum size in bytes used for sorting data values - anything exceeding this is ignored. The server uses only the firstmax_sort_length bytes of each value and ignores the rest. Increasing this may requiresort_buffer_size to be increased (especially if ER_OUT_OF_SORTMEMORY errors start appearing). FromMariaDB 11.7, a warning is generated when max_sort_length is exceeded.

• Commandline:--max-sort-length=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:1024

• Range:

  • 4 to8388608 (<=MariaDB 10.4.13,MariaDB 10.5.3)

  • 8 to8388608 (>=MariaDB 10.4.14,MariaDB 10.5.4)

max_sp_recursion_depth

• Description: Permitted number of recursive calls for astored procedure.0, the default, no recursion is permitted. Increasing this value increases the thread stack requirements, so you may need to increasethread_stack as well. This limit doesn't apply tostored functions.

• Commandline:--max-sp-recursion-depth[=#]

• Scope: Global, Session

• Dynamic: Yes

• Data Type:numeric

• Default Value:0

• Range:0 to255