WEBINAR
MariaDB 101: Learning the Basics of MariaDB
The NEW_MODE system variable and command line switch were introduced in MariaDB 11.4 to provide a way to enable new behavior in otherwise stable versions. Specifying a flag in the NEW_MODE variable enables the corresponding new behavior; otherwise, the old (stable) behavior is used. This can be useful to preserve execution plans in stable versions that may change when the new behavior is active.
A sample usage scenario is:
a fix (new behavior) is pushed into stable releases (for example, MariaDB 11.4 and MariaDB 11.8). It becomes available in NEW_MODE but isn't enabled by default.
in MariaDB 12.1, the fix (new behavior) is enabled without the switch. There's no way to turn it off.
NEW_MODE does not list the fix as something that can be turned on.
if you specify fix_X that is no longer switchable, a warning is printed. However, if you specify fix_that_never_existed, an error is produced.
You can set NEW_MODE from the using the --new-mode option, or by setting the system variable.
The session value only affects the current client and can be changed by the client when required. Setting the global value requires the SUPER privilege, and the change will affect any clients that connect from that point forward.
FIX_DISK_TMPTABLE_COSTS
FIX_INDEX_STATS_FOR_ALL_NULLS
From to . Starting from , this behavior is enabled by default.
This flag improves the cost computation for using temporary tables in certain cases, including semi-join subquery materialization ().
From to .
This flag improves the selection of execution plans when indexed columns contain only NULL values (). Starting from , this behavior is enabled by default.
For proper application of the fix, must be collected for tables having columns with only NULL values:
or at least for indexed columns with only NULL values:
SET [GLOBAL|SESSION] new_mode = 'fix_1[,fix_2]...';SET GLOBAL new_mode = 'FIX_DISK_TMPTABLE_COSTS';
...
SET new_mode = 'FIX_DISK_TMPTABLE_COSTS,FIX_INDEX_STATS_FOR_ALL_NULLS';
...
SET SESSION new_mode = 'FIX_INDEX_STATS_FOR_ALL_NULLS';
SET new_mode = '';ANALYZE TABLE table_name PERSISTENT FOR ALL;ANALYZE TABLE table_name PERSISTENT FOR COLUMNS (b) INDEXES (key_b);The old_mode system variable was introduced in to replace the old variable with a new one with better granularity.
MariaDB supports several different modes which allow you to tune it to suit your needs.
The most important ways for doing this are with SQL_MODE and OLD_MODE.
SQL_MODE is used for getting MariaDB to emulate behavior from other SQL servers, while OLD_MODE is used for emulating behavior from older MariaDB or MySQL versions.
OLD_MODE is a string with different options separated by commas (',') without spaces. The options are case insensitive.
Normally OLD_MODE should be empty. It's mainly used to get old behavior when switching to MariaDB or to a new major version of MariaDB, until you have time to fix your application.
Between major versions of MariaDB various options supported by OLD_MODE may be removed. This is intentional as we assume that the application will be fixed to conform with the new MariaDB behavior between releases.
In other words, OLD_MODE options are by design deprecated from the day they were added and will eventually be removed .
You can check the variable's local and global value with:
You can set the OLD_MODE either from the (option --old-mode) or by setting the system variable.
Non-default old mode features are deprecated by design, and from , a warning will be issued when set.
The different values of OLD_MODE are:
From , the is deprecated. This option allows behaviour of the --old option for enabling the old-style checksum for CHECKSUM TABLE that MySQL 5.1 supports
From , the is deprecated. This option allows behaviour of the --old option for disabling the index only for joins, but allow it for ORDER BY.
From . The non-locking copy ALTER introduced in should be beneficial in the vast majority of cases, but scenarios can exist which significantly impact performance. For example, RBR on tables without a primary key. When non-locking ALTER is performed on such a table, and DML affecting a large number of records is run in parallel, the ALTER can become extremely slow, and further DML can also be affected. If there is a chance of such scenarios (and no possibility of improving the schema by immediately adding primary keys), ALTER should be performed with the explicit LOCK=SHARED clause. If this is also impossible, then LOCK_ALTER_TABLE_COPY flag should be added to the old_mode variable until the schema can be improved.
Don't print duplicate key warnings when using INSERT .
A compatibility setting to support connectors (in particular MySQL Connector/NET) that give an exception when collation ids returned by are NULL. It is automatically set when a MySQL Connector/NET connection is determined. From , , , .
Don't show progress information in .
From , restores the pre- behavior of .
From , restores the pre- behavior of .
From , the main name of the previous 3 byte utf has been changed to utf8mb3. If set, the default, utf8 is an alias for utf8mb3. If not set, utf8 would be an alias for utf8mb4.
When a value is cast to a , the date part will be 0000-00-00, not (as dictated by the SQL standard).
In contrast to , use the current user's OLD_MODEvalue.
Changes to OLD_MODE are not sent to replicas.
This example shows how to get a readable list of enabled OLD_MODE flags:
Adding a new flag:
If the specified flag is already ON, the above example has no effect but does not produce an error.
How to unset a flag:
How to check if a flag is set:
From :
This page is licensed: CC BY-SA / Gnu FDL
SELECT @@OLD_MODE, @@GLOBAL.OLD_MODE;SELECT REPLACE(@@OLD_MODE, ',', '\n');
+---------------------------------------------------+
| REPLACE(@@OLD_MODE, ',', '\n') |
+---------------------------------------------------+
| NO_DUP_KEY_WARNINGS_WITH_IGNORE |
| NO_PROGRESS_INFO |
+---------------------------------------------------+SET @@OLD_MODE = CONCAT(@@OLD_MODE, ',NO_PROGRESS_INFO');SET @@OLD_MODE = REPLACE(@@OLD_MODE, 'NO_PROGRESS_INFO', '');SELECT @@OLD_MODE LIKE '%NO_PROGRESS_INFO';
+------------------------------------+
| @@OLD_MODE LIKE '%NO_PROGESS_INFO' |
+------------------------------------+
| 1 |
+------------------------------------+SET @@OLD_MODE = CONCAT(@@OLD_MODE, ',NO_PROGRESS_INFO');
Query OK, 0 rows affected, 1 warning (0.000 sec)
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1287 | 'NO_PROGRESS_INFO' is deprecated and will be removed in a future release |
+---------+------+--------------------------------------------------------------------------+Explore MariaDB Server variables and modes. This section explains how to configure global and session variables, and how different SQL modes influence database behavior and compatibility.
MariaDB supports several different modes which allow you to tune it to suit your needs.
The most important ways for doing this are using SQL_MODE (controlled by the sql_mode system variable) and OLD_MODE (the old_mode system variable). SQL_MODE is used for getting MariaDB to emulate behavior from other SQL servers, while OLD_MODE is used for emulating behavior from older MariaDB or MySQL versions.
SQL_MODEis a string with different options separated by commas (',') without spaces. The options are case insensitive.
You can check the local and global value of it with:
You can set the SQL_MODE either from the (the --sql-mode option) or by setting the system variable.
The session value only affects the current client, and can be changed by the client when required. To set the global value, the SUPER privilege is required, and the change affects any clients that connect from that point on.
The different SQL_MODE values are:
Allow any day between 1-31 in the day part. This is convenient when you want to read in all (including wrong data) into the database and then manipulate it there.
Note, that MariaDB assumes that table content matches the current setting of this mode. If you enable it, insert some invalid dates into the table and then disable it for a session, the result will be not well defined — some queries might return invalid dates, while other will not.
Changes the SQL syntax to be closer to ANSI SQL.
Sets: , , , .
It also adds a restriction: an error will be returned if a subquery uses an with a reference to a column from an outer query in a way that cannot be resolved.
If set, output will not display MariaDB-specific table attributes.
Changes " to be treated as ```, the identifier quote character. This may break old MariaDB applications which assume that " is used as a string quote character.
Same as: , , , , , ,
If set, output will not display MariaDB-specific table attributes.
Oracle-compatibility option that translates Item_string created in the parser to Item_null, and translates binding an empty string as prepared statement parameters to binding NULL. For example, SELECT '' IS NULL returns TRUE, INSERT INTO t1 VALUES ('') inserts NULL. Since
If not set, division by zero returns NULL. If set returns an error if one tries to update a column with 1/0 and returns a warning as well. Also see . Default since .
Compatibility option for MySQL 5.0.1 and before; This changes NOT a BETWEEN b AND c to be parsed as (NOT a) BETWEEN b AND c
If this is set generate a warning (not an error) for wrong table option in CREATE TABLE. Also, since 10.0.13, do not comment out these wrong table options in .
Allow one to have spaces (including tab characters and new line characters) between function name and '('. The drawback is that this causes built in functions to become .
Same as: , , , , , , , .
Also has the effect of silently converting fields into fields when created or modified.
If set, output will not display MariaDB-specific table attributes.
Additionally implies the following: , , , , , .
Additionally from , implements a limited subset of Microsoft SQL Server's language. See for more.
If set, output will not display MariaDB-specific table attributes.
Same as: , .
Same as: , .
Don't automatically create users with GRANT unless authentication information is specified. If none is provided, will produce a 1133 error: "Can't find any matching row in the user table". Default since .
If set, don't generate an on of zero in an AUTO_INCREMENT column, or when adding an attribute with the statement. Normally both zero and NULL generate new AUTO_INCREMENT values.
Disables using the backslash character \ as an escape character within strings, making it equivalent to an ordinary character.
Ignore all INDEX DIRECTORY and DATA DIRECTORY directives when creating a table. Can be useful on servers.
If not set, if the available storage engine specified by a CREATE TABLE or ALTER TABLE is not available, a warning is given and the default storage engine is used instead. If set, generate a 1286 error when creating a table if the specified is not available. See also . Default since .
Remove MariaDB-specific column options from the output of . This is also used by the portability mode of .
Remove MariaDB-specific index options from the output of . This is also used by the portability mode of .
Remove MariaDB-specific table options from the output of . This is also used by the portability mode of .
When enabled, subtraction results are signed even if the operands are unsigned.
Don't allow '0000-00-00' as a valid date in strict mode (produce a 1525 error). Zero dates can be inserted with . If not in strict mode, a warning is generated.
Don't allow dates where the year is not zero but the month or day parts of the date are zero (produce a 1525 error). For example, with this set, '0000-00-00' is allowed, but '1970-00-10' or '1929-01-00' are not. If the ignore option is used, MariaDB will insert '0000-00-00' for those types of dates. If not in strict mode, a warning is generated instead.
For queries, disallow columns which are not referred to in the GROUP BY clause, unless they are passed to an aggregate function like or . Produce a 1055 error.
In all versions of MariaDB up to , this sets sql_mode that is equivalent to: , , , , , ,
From , this mode also sets and configures the server to understand a large subset of Oracle's PL/SQL language instead of MariaDB's traditional syntax for stored routines. See .
If set, output will not display MariaDB-specific table attributes.
Trailing spaces in columns are by default trimmed upon retrieval. With PAD_CHAR_TO_FULL_LENGTH enabled, no trimming occurs. Does not apply to .
Allows using the pipe character (ASCII 124) as string concatenation operator. This means that "A" || "B" can be used in place of CONCAT("A", "B").
Same as: , , , , , , .
If set, output will not display MariaDB-specific table attributes.
REAL is a synonym for rather than .
Setting this makes the SET part of the statement evaluate all assignments simultaneously, not left-to-right. From .
Strict mode. Statements with invalid or missing data are aborted and rolled back. For a non-transactional storage engine with a statement affecting multiple rows, this may mean a partial insert or update if the error is found in a row beyond the first.
Strict mode. Statements with invalid or missing data are aborted and rolled back, except that for non-transactional storage engines and statements affecting multiple rows where the invalid or missing data is not the first row, MariaDB will convert the invalid value to the closest valid value, or, if a value is missing, insert the column default value. Default since .
With this mode unset, MariaDB truncates fractional seconds when changing precision to smaller. When set, MariaDB will round when converting to TIME, DATETIME and TIMESTAMP, and truncate when converting to DATE. Since
Makes MariaDB work like a traditional SQL server. Same as: , , , , , , .
A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode.
With strict mode set (default from ), statements that modify tables (either transactional for STRICT_TRANS_TABLES or all for STRICT_ALL_TABLES) will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.
With strict mode not set (default in version <= ), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.
Statements that don't modify data will return a warning when adjusted regardless of mode.
always use the SQL_MODE that was active when they were created. This means that users can safely change session or global SQL_MODE; the stored programs they use will still work as usual.
It is possible to change session SQL_MODE within a stored program. In this case, the new SQL_MODE will be in effect only in the body of the current stored program. If it calls some stored procedures, they will not be affected by the change.
Some Information Schema tables (such as ) and SHOW CREATE statements such as show the SQL_MODE used by the stored programs.
This example shows how to get a readable list of enabled SQL_MODE flags:
Adding a new flag:
If the specified flag is already ON, the above example has no effect but does not produce an error.
How to unset a flag:
How to check if a flag is set:
Without and with strict mode:
Overriding strict mode with the IGNORE keyword:
This page is licensed: CC BY-SA / Gnu FDL
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER
<=
No value
SET sql_mode = 'modes';
SET GLOBAL sql_mode = 'modes';SELECT REPLACE(@@SQL_MODE, ',', '\n');
+-------------------------------------------------------------------------+
| REPLACE(@@SQL_MODE, ',', '\n') |
+-------------------------------------------------------------------------+
| STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------+SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_ENGINE_SUBSTITUTION');SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ENGINE_SUBSTITUTION', '');SELECT @@SQL_MODE LIKE '%NO_ZERO_DATE%';
+----------------------------------+
| @@SQL_MODE LIKE '%NO_ZERO_DATE%' |
+----------------------------------+
| 1 |
+----------------------------------+CREATE TABLE strict (s CHAR(5), n TINYINT);
INSERT INTO strict VALUES ('MariaDB', '128');
Query OK, 1 row affected, 2 warnings (0.14 sec)
SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 's' at row 1 |
| Warning | 1264 | Out of range value for column 'n' at row 1 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)
SELECT * FROM strict;
+-------+------+
| s | n |
+-------+------+
| Maria | 127 |
+-------+------+
SET sql_mode='STRICT_TRANS_TABLES';
INSERT INTO strict VALUES ('MariaDB', '128');
ERROR 1406 (22001): Data too long for column 's' at row 1INSERT IGNORE INTO strict VALUES ('MariaDB', '128');
Query OK, 1 row affected, 2 warnings (0.15 sec)Most status variables are described on this page, but some are described elsewhere:
Use the statement to view status variables. This information also can be obtained using the command, or by querying the tables.
Issuing a will reset many status variables to zero.
Aborted_clientsDescription: Number of aborted client connections. This can be due to the client not calling mysql_close() before exiting, the client sleeping without issuing a request to the server for more seconds than specified by or , or by the client program ending in the midst of transferring data. The global value can be flushed by .
Scope: Global
Data Type: numeric
Aborted_connectsDescription: Number of failed server connection attempts. This can be due to a client using an incorrect password, a client not having privileges to connect to a database, a connection packet not containing the correct information, or if it takes more than seconds to get a connect packet. The global value can be flushed by .
Scope: Global
Data Type: numeric
Aborted_connects_preauthDescription: Number of connection attempts that were aborted prior to authentication (regardless of whether or not an error occured).
Scope: Global
Data Type: numeric
Access_denied_errorsDescription: Number of access denied errors. For details on when this is incremented, see .
Scope: Global
Data Type: numeric
Acl_column_grantsDescription: Number of column permissions granted (rows in the ).
Scope: Global
Data Type: numeric
Acl_database_grantsDescription: Number of database permissions granted (rows in the ).
Scope: Global
Data Type: numeric
Acl_function_grantsDescription: Number of function permissions granted (rows in the with a routine type of FUNCTION).
Scope: Global
Data Type: numeric
Acl_package_body_grantsDescription:
Scope: Global
Data Type: numeric
Acl_package_spec_grantsDescription:
Scope: Global
Data Type: numeric
Acl_procedure_grantsDescription: Number of procedure permissions granted (rows in the with a routine type of PROCEDURE).
Scope: Global
Data Type: numeric
Acl_proxy_usersDescription: Number of proxy permissions granted (rows in the ).
Scope: Global
Data Type: numeric
Acl_role_grantsDescription: Number of role permissions granted (rows in the ).
Scope: Global
Data Type: numeric
Acl_rolesDescription: Number of roles (rows in the where is_role='Y').
Scope: Global
Data Type: numeric
Acl_table_grantsDescription: Number of table permissions granted (rows in the ).
Scope: Global
Data Type: numeric
Acl_usersDescription: Number of users (rows in the where is_role='N').
Scope: Global
Data Type: numeric
Busy_timeDescription: Cumulative time in seconds of activity on connections. Part of . Requires the system variable to be set in order to be recorded.
Scope: Global
Data Type: numeric
Bytes_receivedDescription: Total bytes received from all clients.
Scope: Global
Data Type: numeric
Bytes_sentDescription: Total bytes sent to all clients.
Scope: Global, Session
Data Type: numeric
Com_admin_commandsDescription: Number of admin commands executed. These include table dumps, change users, binary log dumps, shutdowns, pings and debugs.
Scope: Global, Session
Data Type: numeric
Com_alter_dbDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_db_upgradeDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_eventDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_functionDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_procedureDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_sequenceDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_serverDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_alter_tablespaceDescription: Number of commands executed (unsupported by MariaDB).
Scope: Global, Session
Data Type: numeric
Removed:
Com_alter_userDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_analyzeDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_assign_to_keycacheDescription: Number of assign to keycache commands executed.
Scope: Global, Session
Data Type: numeric
Com_backupDescription:
Scope: Global, Session
Data Type: numeric
Removed:
Com_backup_lockDescription:
Scope: Global, Session
Data Type: numeric
Removed:
Com_backup_tableDescription: Removed in . In older versions, Com_backup_table contains the number of commands executed.
Scope: Global, Session
Data Type: numeric
Removed:
Com_beginDescription: Number of or statements executed.
Scope: Global, Session
Data Type: numeric
Com_binlogDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_call_procedureDescription: Number of procedure_name statements executed.
Scope: Global, Session
Data Type: numeric
Com_change_dbDescription: Number of database_name commands executed.
Scope: Global, Session
Data Type: numeric
Com_checkDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_checksumDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_commitDescription: Number of commands executed. Differs from , which counts internal commit statements.
Scope: Global, Session
Data Type: numeric
Com_compound_sqlDescription: Number of sql statements.
Scope: Global, Session
Data Type: numeric
Com_create_dbDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_eventDescription: Number of commands executed. Differs from in that it is incremented when the CREATE EVENT is run, and not when the event executes.
Scope: Global, Session
Data Type: numeric
Com_create_functionDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_indexDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_packageDescription:
Scope: Global, Session
Data Type: numeric
Com_create_package_bodyDescription:
Scope: Global, Session
Data Type: numeric
Com_create_procedureDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_roleDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_sequenceDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_serverDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_temporary_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_triggerDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_udfDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_userDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_create_viewDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_dealloc_sqlDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_deleteDescription: Number of commands executed. Differs from , which counts the number of times rows have been deleted from tables.
Scope: Global, Session
Data Type: numeric
Com_delete_multiDescription: Number of multi-table commands executed.
Scope: Global, Session
Data Type: numeric
Com_doDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_dbDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_eventDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_functionDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_indexDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_packageDescription:
Scope: Global, Session
Data Type: numeric
Com_drop_package_bodyDescription:
Scope: Global, Session
Data Type: numeric
Com_drop_procedureDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_roleDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_sequenceDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_serverDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_temporary_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_triggerDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_userDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_drop_viewDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_empty_queryDescription: Number of queries to the server that do not produce SQL queries. An SQL query simply returning no results does not increment Com_empty_query - see instead. An example of an empty query sent to the server is mariadb --comments -e '-- sql comment'
Scope: Global, Session
Data Type: numeric
Com_execute_immediateDescription: Number of statements executed.
Scope: Global, Session
Data Type: numeric
Com_execute_sqlDescription: Number of statements executed.
Scope: Global, Session
Data Type: numeric
Com_flushDescription: Number of commands executed. This differs from , which also counts internal server flush requests.
Scope: Global, Session
Data Type: numeric
Com_get_diagnosticsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_grantDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_grant_roleDescription: Number of role commands executed.
Scope: Global, Session
Data Type: numeric
Com_ha_closeDescription: Number of table_name CLOSE commands executed.
Scope: Global, Session
Data Type: numeric
Com_ha_openDescription: Number of table_name OPEN commands executed.
Scope: Global, Session
Data Type: numeric
Com_ha_readDescription: Number of table_name READ commands executed.
Scope: Global, Session
Data Type: numeric
Com_helpDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_insertDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_insert_selectDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_install_pluginDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_killDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_loadDescription: Number of LOAD commands executed.
Scope: Global, Session
Data Type: numeric
Com_load_master_dataDescription:
Scope: Global, Session
Data Type: numeric
Removed:
Com_load_master_tableDescription:
Scope: Global, Session
Data Type: numeric
Removed:
Com_multiDescription:
Scope: Global, Session
Data Type: numeric
Com_lock_tablesDescription: Number of [lock-tables|LOCK TABLES]] commands executed.
Scope: Global, Session
Data Type: numeric
Com_optimizeDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_preload_keysDescription:
Scope: Global, Session
Data Type: numeric
Com_prepare_sqlDescription: Number of statements executed.
Scope: Global, Session
Data Type: numeric
Com_purgeDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_purge_before_dateDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_release_savepointDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_rename_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_rename_userDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_repairDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_replaceDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_replace_selectDescription: Number of ... commands executed.
Scope: Global, Session
Data Type: numeric
Com_resetDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_resignalDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_restore_tableDescription: Removed in . In older versions, Com_restore_table contains the number of commands executed.
Scope: Global, Session
Data Type: numeric
Removed:
Com_revokeDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_revoke_allDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_revoke_grantDescription: Number of role commands executed.
Scope: Global, Session
Data Type: numeric
Com_rollbackDescription: Number of commands executed. Differs from , which is the number of transaction rollback requests given to a storage engine.
Scope: Global, Session
Data Type: numeric
Com_rollback_to_savepointDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_savepointDescription: Number of commands executed. Differs from , which is the number of transaction savepoint creation requests.
Scope: Global, Session
Data Type: numeric
Com_selectDescription: Number of commands executed. Also includes queries that make use of the .
Scope: Global, Session
Data Type: numeric
Com_set_optionDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_signalDescription: Number of statements executed.
Scope: Global, Session
Data Type: numeric
Com_show_authorsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_binlog_eventsDescription: Number of statements executed.
Scope: Global, Session
Data Type: numeric
Com_show_binlogsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_charsetsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_client_statisticsDescription: Number of commands executed. Removed in when that statement was replaced by the generic .
Scope: Global, Session
Data Type: numeric
Removed:
Com_show_collationsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_column_typesDescription:
Scope: Global, Session
Data Type: numeric
Removed:
Com_show_contributorsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_dbDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_eventDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_funcDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_packageDescription:
Scope: Global, Session
Data Type: numeric
Com_show_create_package_bodyDescription:
Scope: Global, Session
Data Type: numeric
Com_show_create_procDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_tableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_triggerDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_create_userDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_databasesDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_engine_logsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_engine_mutexDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_engine_statusDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_eventsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_errorsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_explainDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_fieldsDescription: Number of or SHOW FIELDS commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_function_statusDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_genericDescription: Number of generic commands executed, such as and
Scope: Global, Session
Data Type: numeric
Com_show_grantsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_keysDescription: Number of or SHOW KEYS commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_index_statisticsDescription: Number of commands executed. Removed in when that statement was replaced by the generic .
Scope: Global, Session
Data Type: numeric
Removed:
Com_show_open_tablesDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_package_statusDescription:
Scope: Global, Session
Data Type: numeric
Com_show_package_body_statusDescription:
Scope: Global, Session
Data Type: numeric
Com_show_pluginsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_privilegesDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_procedure_statusDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_processlistDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_profileDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_profilesDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_relaylog_eventsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_statusDescription: Number of commands executed.
Scope: Global, Session
Data Type: numericst
Com_show_storage_enginesDescription: Number of - or SHOW ENGINES - commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_table_statisticsDescription: Number of commands executed. Removed in when that statement was replaced by the generic .
Scope: Global, Session
Data Type: numeric
Removed:
Com_show_table_statusDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_tablesDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_triggersDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_user_statisticsDescription: Number of commands executed. Removed in when that statement was replaced by the generic .
Scope: Global, Session
Data Type: numeric
Removed:
Com_show_variableDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_show_warningsDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_shutdownDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_stmt_closeDescription: Number of closed ().
Scope: Global, Session
Data Type: numeric
Com_stmt_executeDescription: Number of .
Scope: Global, Session
Data Type: numeric
Com_stmt_fetchDescription: Number of fetched.
Scope: Global, Session
Data Type: numeric
Com_stmt_prepareDescription: Number of .
Scope: Global, Session
Data Type: numeric
Com_stmt_reprepareDescription: Number of reprepared.
Scope: Global, Session
Data Type: numeric
Com_stmt_resetDescription: Number of where the data of a prepared statement which was accumulated in chunks by sending long data has been reset.
Scope: Global, Session
Data Type: numeric
Com_stmt_send_long_dataDescription: Number of where the parameter data has been sent in chunks (long data).
Scope: Global, Session
Data Type: numeric
Com_truncateDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_uninstall_pluginDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_unlock_tablesDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_updateDescription: Number of commands executed.
Scope: Global, Session
Data Type: numeric
Com_update_multiDescription: Number of multi-table commands executed.
Scope: Global, Session
Data Type: numeric
Com_xa_commitDescription: Number of XA statements committed.
Scope: Global, Session
Data Type: numeric
Com_xa_endDescription: Number of XA statements ended.
Scope: Global, Session
Data Type: numeric
Com_xa_prepareDescription: Number of XA statements prepared.
Scope: Global, Session
Data Type: numeric
Com_xa_recoverDescription: Number of XA RECOVER statements executed.
Scope: Global, Session
Data Type: numeric
Com_xa_rollbackDescription: Number of XA statements rolled back.
Scope: Global, Session
Data Type: numeric
Com_xa_startDescription: Number of XA statements started.
Scope: Global, Session
Data Type: numeric
CompressionDescription: Whether client-server traffic is compressed.
Scope: Session
Data Type: boolean
Connection_errors_acceptDescription: Number of errors that occurred during calls to accept() on the listening port. The global value can be flushed by .
Scope: Global
Data Type: numeric
Connection_errors_internalDescription: Number of refused connections due to internal server errors, for example out of memory errors, or failed thread starts. The global value can be flushed by .
Scope: Global
Data Type: numeric
Connection_errors_max_connectionsDescription: Number of refused connections due to the limit being reached. The global value can be flushed by .
Scope: Global
Data Type: numeric
Connection_errors_peer_addressDescription: Number of errors while searching for the connecting client IP address. The global value can be flushed by .
Scope: Global
Data Type: numeric
Connection_errors_selectDescription: Number of errors during calls to select() or poll() on the listening port. The client would not necessarily have been rejected in these cases. The global value can be flushed by .
Scope: Global
Data Type: numeric
Connection_errors_tcpwrapDescription: Number of connections the libwrap library refused. The global value can be flushed by .
Scope: Global
Data Type: numeric
ConnectionsDescription: Number of connection attempts (both successful and unsuccessful)
Scope: Global
Data Type: numeric
Cpu_timeDescription: Total CPU time used. Part of . Requires the system variable to be set in order to be recorded.
Scope: Global, Session
Data Type: numeric
Created_tmp_disk_tablesDescription: Number of on-disk temporary tables created.
Scope: Global, Session
Data Type: numeric
Created_tmp_filesDescription: Number of temporary files created. The global value can be flushed by .
Scope: Global
Data Type: numeric
Created_tmp_tablesDescription: Number of in-memory temporary tables created.
Scope: Global
Data Type: numeric
Delayed_errorsDescription: Number of errors which occurred while doing . The global value can be flushed by .
Scope: Global
Data Type: numeric
Delayed_insert_threadsDescription: Number of threads.
Scope: Global
Data Type: numeric
Delayed_writesDescription: Number of rows written. The global value can be flushed by .
Scope: Global
Data Type: numeric
Delete_scanDescription: Number of s that required a full table scan.
Scope: Global
Data Type: numeric
Empty_queriesDescription: Number of queries returning no results. Note this is not the same as .
Scope: Global, Session
Data Type: numeric
Executed_eventsDescription: Number of times events created with have executed. This differs from in that it is only incremented when the event has run, not when it executes.
Scope: Global, Session
Data Type: numeric
Executed_triggersDescription: Number of times triggers created with have executed. This differs from in that it is only incremented when the trigger has run, not when it executes.
Scope: Global, Session
Data Type: numeric
Feature_application_time_periodsDescription: Number of times a table created with has been opened.
Scope: Global, Session
Data Type: numeric
Feature_check_constraintDescription: Number of times were checked. The global value can be flushed by .
Scope: Global, Session
Data Type: numeric
Feature_custom_aggregate_functionsDescription: Number of queries which make use of .
Scope: Global, Session
Data Type: numeric
Feature_delay_key_writeDescription: Number of tables opened that are using . The global value can be flushed by .
Scope: Global, Session
Data Type: numeric
Feature_dynamic_columnsDescription: Number of times the function was used.
Scope: Global, Session
Data Type: numeric
Feature_fulltextDescription: Number of times the function was used.
Scope: Global, Session
Data Type: numeric
Feature_gisDescription: Number of times a table with a any of the columns was opened.
Scope: Global, Session
Data Type: numeric
Feature_insert_returningDescription:
Scope: Global, Session
Data Type: numeric
Introduced:
Feature_invisible_columnsDescription: Number of invisible columns in all opened tables.
Scope: Global, Session
Data Type: numeric
Feature_jsonDescription: Number of times JSON functionality has been used, such as one of the . Does not include the , or .
Scope: Global, Session
Data Type: numeric
Feature_localeDescription: Number of times the variable was assigned into.
Scope: Global, Session
Data Type: numeric
Feature_subqueryDescription: Number of subqueries (excluding subqueries in the FROM clause) used.
Scope: Global, Session
Data Type: numeric
Feature_system_versioningDescription: Number of times functionality has been used (opening a table WITH SYSTEM VERSIONING).
Scope: Global, Session
Data Type: numeric
Feature_timezoneDescription: Number of times an explicit timezone (excluding and SYSTEM) was specified.
Scope: Global, Session
Data Type: numeric
Feature_triggerDescription: Number of triggers loaded.
Scope: Global, Session
Data Type: numeric
Feature_window_functionsDescription: Number of times were used.
Scope: Global, Session
Data Type: numeric
Feature_xmlDescription: Number of times XML functions ( and ) were used.
Scope: Global, Session
Data Type: numeric
Flush_commandsDescription: Number of statements executed, as well as due to internal server flush requests. This differs from , which simply counts FLUSH statements, not internal server flush operations.
Scope: Global
Data Type: numeric
Removed:
Handler_commitDescription: Number of internal requests. Differs from , which counts the number of statements executed.
Scope: Global, Session
Data Type: numeric
Handler_deleteDescription: Number of times rows have been deleted from tables. Differs from , which counts statements.
Scope: Global, Session
Data Type: numeric
Handler_discoverDescription: Discovery is when the server asks the NDBCLUSTER storage engine if it knows about a table with a given name. Handler_discover indicates the number of times that tables have been discovered in this way.
Scope: Global, Session
Data Type: numeric
Handler_external_lockDescription: Incremented for each call to the external_lock() function, which generally occurs at the beginning and end of access to a table instance.
Scope: Global, Session
Data Type: numeric
Handler_icp_attemptsDescription: Number of times pushed index condition was checked. The smaller the ratio of Handler_icp_attempts to the better the filtering. See .
Scope: Global, Session
Data Type: numeric
Handler_icp_matchDescription: Number of times pushed index condition was matched. The smaller the ratio of to Handler_icp_match the better the filtering. See .
Scope: Global, Session
Data Type: numeric
Handler_mrr_initDescription: Counts how many MRR (multi-range read) scans were performed. See .
Scope: Global, Session
Data Type: numeric
Handler_mrr_key_refillsDescription: Number of times key buffer was refilled (not counting the initial fill). A non-zero value indicates there wasn't enough memory to do key sort-and-sweep passes in one go. See .
Scope: Global, Session
Data Type: numeric
Handler_mrr_rowid_refillsDescription: Number of times rowid buffer was refilled (not counting the initial fill). A non-zero value indicates there wasn't enough memory to do rowid sort-and-sweep passes in one go. See .
Scope: Global, Session
Data Type: numeric
Handler_prepareDescription: Number of two-phase commit prepares.
Scope: Global, Session
Data Type: numeric
Handler_read_firstDescription: Number of requests to read the first row from an index. A high value indicates many full index scans, e.g. SELECT a FROM table_name where a is an indexed column.
Scope: Global, Session
Data Type: numeric
Handler_read_keyDescription: Number of row read requests based on an index value. A high value indicates indexes are regularly being used, which is usually positive.
Scope: Global, Session
Data Type: numeric
Handler_read_lastDescription: Number of requests to read the last row from an index. results in a last-key request followed by several previous-key requests.
Scope: Global, Session
Data Type: numeric
Handler_read_nextDescription: Number of requests to read the next row from an index (in order). Increments when doing an index scan or querying an index column with a range constraint.
Scope: Global, Session
Data Type: numeric
Handler_read_prevDescription: Number of requests to read the previous row from an index (in order). Mostly used with .
Scope: Global, Session
Data Type: numeric
Handler_read_retryDescription: Number of read retrys triggered by semi_consistent_read (InnoDB feature).
Scope: Global
Data Type: numeric
Handler_read_rndDescription: Number of requests to read a row based on its position. If this value is high, you may not be using joins that don't use indexes properly, or be doing many full table scans.
Scope: Global, Session
Data Type: numeric
Handler_read_rnd_deletedDescription: Number of requests to delete a row based on its position.
Scope: Global, Session
Data Type: numeric
Handler_read_rnd_nextDescription: Number of requests to read the next row. A large number of these may indicate many table scans and improperly used indexes.
Scope: Global, Session
Data Type: numeric
Handler_rollbackDescription: Number of transaction rollback requests given to a storage engine. Differs from , which is the number of commands executed.
Scope: Global, Session
Data Type: numeric
Handler_savepointDescription: Number of transaction savepoint creation requests. Differs from which is the number of commands executed.
Scope: Global, Session
Data Type: numeric
Handler_savepoint_rollbackDescription: Number of requests to rollback to a transaction .
Scope: Global, Session
Data Type: numeric
Handler_tmp_deleteDescription: Number of requests to delete a row in a temporary table.
Scope: Global, Session
Data Type: numeric
Handler_tmp_updateDescription: Number of requests to update a row to a temporary table.
Scope: Global, Session
Data Type: numeric
Handler_tmp_writeDescription: Number of requests to write a row to a temporary table.
Scope: Global, Session
Data Type: numeric
Handler_updateDescription: Number of requests to update a row in a table. Since , this no longer counts temporary tables - see .
Scope: Global, Session
Data Type: numeric
Handler_writeDescription: Number of requests to write a row to a table. Since , this no longer counts temporary tables - see .
Scope: Global, Session
Data Type: numeric
Key_blocks_not_flushedDescription: Number of key cache blocks which have been modified but not flushed to disk.
Scope: Global
Data Type: numeric
Key_blocks_unusedDescription: Number of unused key cache blocks.
Scope: Global
Data Type: numeric
Key_blocks_usedDescription: Max number of key cache blocks which have been used simultaneously.
Scope: Global
Data Type: numeric
Key_blocks_warmDescription: Number of key cache blocks in the warm list.
Scope: Global
Data Type: numeric
Key_read_requestsDescription: Number of key cache block read requests. See .
Scope: Global
Data Type: numeric
Key_readsDescription: Number of physical index block reads. See .
Scope: Global
Data Type: numeric
Key_write_requestsDescription: Number of requests to write a block to the key cache.
Scope: Global
Data Type: numeric
Key_writesDescription: Number of key cache block write requests
Scope: Global
Data Type: numeric
Last_query_costDescription: The most recent query optimizer query cost calculation. Can not be calculated for complex queries, such as subqueries or UNION. It will be set to 0 for complex queries.
Scope: Session
Data Type: numeric
Maria_*Description: When the Maria storage engine was renamed Aria, the Maria variables existing at the time were renamed at the same time. See .
Max_memory_usedDescription: The maximum memory allocation used by the current connection.
Scope: Session
Data Type: numeric
Introduced:
Max_statement_time_exceededDescription: Number of queries that exceeded the execution time specified by . See .
Data Type: numeric
Max_tmp_space_usedDescription: Maximum temporary space used. See
Scope: Global, Session
Data Type: numeric
Introduced:
Max_used_connectionsDescription: Max number of connections ever open at the same time. The global value can be flushed by .
Scope: Global
Data Type: numeric
Max_used_connections_timeDescription: The time at which the last change of occured. The global value can be flushed by .
Scope: Global
Data Type: datetime
Introduced: ,
Memory_usedDescription: Global or per-connection memory usage, in bytes. This includes all per-connection memory allocations, and as of includes global allocations such as the key_buffer, innodb_buffer_pool etc (which were excluded before MariaDB 10.6.16).
Scope: Global, Session
Data Type: numeric
Memory_used_initialDescription: Amount of memory that was used when the server started to service the user connections.
Scope: Global
Data Type: numeric
Not_flushed_delayed_rowsDescription: Number of rows waiting to be written.
Scope: Global
Data Type: numeric
Open_filesDescription: Number of regular files currently opened by the server. Does not include sockets or pipes, or storage engines using internal functions.
Scope: Global
Data Type: numeric
Open_streamsDescription: Number of currently opened streams, usually log files.
Scope: Global
Data Type: numeric
Open_table_definitionsDescription: Number of currently cached .frm files.
Scope: Global
Data Type: numeric
Open_tablesDescription: Number of currently opened tables, excluding temporary tables.
Scope: Global, Session
Data Type: numeric
Opened_filesDescription: Number of files the server has opened.
Scope: Global
Data Type: numeric
Opened_plugin_librariesDescription: Number of shared libraries that the server has opened to load .
Scope: Global
Data Type: numeric
Opened_table_definitionsDescription: Number of .frm files that have been cached.
Scope: Global, Session
Data Type: numeric
Opened_tablesDescription: Number of tables the server has opened.
Scope: Global, Session
Data Type: numeric
Opened_viewsDescription: Number of views the server has opened.
Scope: Global, Session
Data Type: numeric
Prepared_stmt_countDescription: Current number of prepared statements.
Scope: Global
Data Type: numeric
Qcache_free_blocksDescription: Number of free memory blocks.
Scope: Global
Data Type: numeric
Qcache_free_memoryDescription: Amount of free memory.
Scope: Global
Data Type: numeric
Qcache_hitsDescription: Number of requests served by the . The global value can be flushed by .
Scope: Global
Data Type: numeric
Qcache_insertsDescription: Number of queries ever cached in the . The global value can be flushed by .
Scope: Global
Data Type: numeric
Qcache_lowmem_prunesDescription: Number of pruning operations performed to remove old results to make space for new results in the . The global value can be flushed by .
Scope: Global
Data Type: numeric
Qcache_not_cachedDescription: Number of queries that are uncacheable by the , or use SQL_NO_CACHE. The global value can be flushed by .
Scope: Global
Data Type: numeric
Qcache_queries_in_cacheDescription: Number of queries currently cached by the .
Scope: Global
Data Type: numeric
Qcache_total_blocksDescription: Number of blocks used by the .
Scope: Global
Data Type: numeric
QueriesDescription: Number of statements executed by the server, excluding COM_PING and COM_STATISTICS. Differs from in that it also counts statements executed within .
Scope: Global, Session
Data Type: numeric
Query_timeDescription: Cumulative time in seconds, with microsecond precision, of running queries.
Scope: Global,Session
Data Type: numeric
Introduced:
QuestionsDescription: Number of statements executed by the server, excluding COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, and COM_STMT_RESET statements. Differs from in that it doesn't count statements executed within .
Scope: Global, Session
Data Type: numeric
Resultset_metadata_skippedDescription: Number of times sending the metadata has been skipped. Metadata is not resent if metadata does not change between prepare and execute of prepared statement, or between executes.
Scope: Global, Session
Data Type: numeric
Introduced:
Rows_readDescription: Number of requests to read a row (excluding temporary tables).
Scope: Global, Session
Data Type: numeric
Rows_sentDescription:
Scope: Global, Session
Data Type: numeric
Rows_tmp_readDescription: Number of requests to read a row in a temporary table.
Scope: Global, Session
Data Type: numeric
Select_full_joinDescription: Number of joins which did not use an index. If not zero, you may need to check table indexes.
Scope: Global, Session
Data Type: numeric
Select_full_range_joinDescription: Number of joins which used a range search of the first table.
Scope: Global, Session
Data Type: numeric
Select_rangeDescription: Number of joins which used a range on the first table.
Scope: Global, Session
Data Type: numeric
Select_range_checkDescription: Number of joins without keys that check for key usage after each row. If not zero, you may need to check table indexes.
Scope: Global, Session
Data Type: numeric
Select_scanDescription: Number of joins which used a full scan of the first table.
Scope: Global, Session
Data Type: numeric
Slow_launch_threadsDescription: Number of threads which took longer than to create. The global value can be flushed by .
Scope: Global, Session
Data Type: numeric
Slow_queriesDescription: Number of queries which took longer than to run. The does not need to be active for this to be recorded.
Scope: Global, Session
Data Type: numeric
Sort_merge_passesDescription: Number of merge passes performed by the sort algorithm. If too high, you may need to look at improving your query indexes, or increasing the .
Scope: Global, Session
Data Type: numeric
Sort_priority_queue_sortsDescription: The number of times that sorting was done through a priority queue. (The total number of times sorting was done is a sum and ). See .
Scope: Global, Session
Data Type: numeric
Sort_rangeDescription: Number of sorts which used a range.
Scope: Global, Session
Data Type: numeric
Sort_rowsDescription: Number of rows sorted.
Scope: Global, Session
Data Type: numeric
Sort_scanDescription: Number of sorts which used a full table scan.
Scope: Global, Session
Data Type: numeric
Subquery_cache_hitDescription: Counter for all hits. The global value can be flushed by .
Scope: Global, Session
Data Type: numeric
Subquery_cache_missDescription: Counter for all misses. The global value can be flushed by .
Scope: Global, Session
Data Type: numeric
SyncsDescription: Number of times my_sync() has been called, or the number of times the server has had to force data to disk. Covers the , .frm creation (if these operations are configured to sync) and some storage engines (,, ), but not ).
Scope: Global, Session
Data Type: numeric
Table_locks_immediateDescription: Number of table locks which were completed immediately. The global value can be flushed by .
Scope: Global
Data Type: numeric
Table_locks_waitedDescription: Number of table locks which had to wait. Indicates table lock contention. The global value can be flushed by .
Scope: Global
Data Type: numeric
Table_open_cache_active_instancesDescription: Number of active instances for open tables cache lookups.
Scope:
Data Type: numeric
Table_open_cache_hitsDescription: Number of hits for open tables cache lookups.
Scope:
Data Type: numeric
Table_open_cache_missesDescription: Number of misses for open tables cache lookups.
Scope:
Data Type: numeric
Table_open_cache_overflowsDescription: Number of overflows for open tables cache lookups.
Scope:
Data Type: numeric
Tc_log_max_pages_usedDescription: Max number of pages used by the memory-mapped file-based . The global value can be flushed by .
Scope: Global
Data Type: numeric
Tc_log_page_sizeDescription: Page size of the memory-mapped file-based .
Scope: Global
Data Type: numeric
Tc_log_page_waitsDescription: Number of times a two-phase commit was forced to wait for a free memory-mapped file-based page. The global value can be flushed by .
Scope: Global
Data Type: numeric
Threads_cachedDescription: Number of threads cached in the thread cache. This value will be zero if the is in use.
Scope: Global
Data Type: numeric
Threads_connectedDescription: Number of clients connected to the server. See . The Threads_connected name is inaccurate when the is in use, since each client connection does not correspond to a dedicated thread in that case.
Scope: Global
Data Type: numeric
Threads_createdDescription: Number of threads created to respond to client connections. If too large, look at increasing .
Scope: Global
Data Type: numeric
Threads_runningDescription: Number of client connections that are actively running a command, and not just sleeping while waiting to receive the next command to execute. Some internal system threads also count towards this status variable if they would show up in the output of the statement.
In and before, a global counter was updated each time a client connection dispatched a command. In these versions, the global and session status variable are always the same value.
In and later, the global counter has been removed as a performance improvement. Instead, when the global status variable is queried, it is calculated dynamically by essentially adding up all the running client connections as they would appear in output. A client connection is only considered to be running if its thread value is not equal to Sleep
Tmp_space_usedDescription: Temporary space used. See
Scope: Global, Session
Data Type: numeric
Introduced:
Update_scanDescription: Number of updates that required a full table scan.
Scope: Global
Data Type: numeric
UptimeDescription: Number of seconds the server has been running.
Scope: Global
Data Type: numeric
Uptime_since_flush_statusDescription: Number of seconds since the last .
Scope: Global
Data Type: numeric
This page is licensed: CC BY-SA / Gnu FDL
1Scope: Global
Data Type: numeric
--,
--
-b,
--,
--,
--,
--
--
-C,
-r, --
--
-h,
-#,
--
--
--
--
--
--
--
-T, --
--
--
--
--
--
-h,
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-L,
-l,
--
--
--
-0, --
--
--
--
--
-W, --,
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--,
--
--
-P, --,
--,
--
--
-P, --,
--
--
--
--
-r, --
--
--
-s, --
--
--
--
--
-O, --
--
--
--
--,
--
--
--
--
--
--
--,
--
--
--,
--
--
--
--
--
--
--
--
--
--,
--
--
--
-s, --
--
--
-T, --
--
--
--
--
--
--
-t,
--
-u, --
-u, --
--,
-v, --
-V,
-W,
--
This page is licensed: CC BY-SA / Gnu FDL
MariaDB has many system variables that can be changed to suit your needs.
Many of the general system variables are described on this page, but others are described elsewhere:
See also the .
Most of these can be set with 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 for instructions on using this command.
From your shell, run mariadbd like so:
View the Information Schema , , and tables.
There are several ways to set server system variables:
Specify them on the command line:
Specify them in your my.cnf file (see for more information):
Set them from the mariadb client using the 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:
The suffix can be upper or lower-case.
allow_suspicious_udfsDescription: Allows use of 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 , available as an .
Command line: --allow-suspicious-udfs
alter_algorithmDescription: The implied ALGORITHM for if no ALGORITHM clause is specified. The deprecated variable is an alias for this. The feature was removed in . See .
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.
analyze_max_lengthDescription: Prevents collection of column statistics for / columns that can be analyzed automatically by . Columns exceeding this threshold in bytes will be skipped unless included explicitly in .
Command line: --analyze-max-length=val
Scope: Global, Session
analyze_sample_percentageDescription: Percentage of rows from the 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
autocommitDescription: If set to 1, the default, all queries are committed immediately. The and clauses therefore have no effect. If set to 0, they are only committed upon a statement or rolled back with a statement. If autocommit is set to 0, and then changed to 1, all open transactions are immediately committed.
Command line: --autocommit[=#]
Scope: Global, Session
automatic_sp_privilegesDescription: When set to 1, the default, when a stored routine is created, the creator is automatically granted permission to (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
back_logDescription: 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 + /5).
Command line: --back-log=#
basedirDescription: 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
big_tablesDescription: 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 system variable.
bind_addressDescription: 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 ; from it's also available as a system variable. Before "::" implied listening additionally on IPv4 addresses like "". From 10.6.0 onwards it refers to IPv6 stictly. Starting with , a comma-separated list of addresses to bind to can be given. See also .
Command line: --bind-address=addr
block_encryption_modeDescription: Default block encryption mode for and functions.
Command line: --block-encryption-mode=val
Scope: Global, Session
Dynamic: Yes
bulk_insert_buffer_sizeDescription: Size in bytes of the per-thread cache tree used to speed up bulk inserts into and tables. A value of 0 disables the cache tree.
Command line: --bulk-insert-buffer-size=#
Scope: Global, Session
Dynamic: Yes
character_set_clientDescription: Determines the 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 , the utf8 (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the system variable.
Scope: Global, Session
character_set_collationsDescription: 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
character_set_connectionDescription: used for number to string conversion, as well as for literals that don't have a character set introducer. From , the utf8 (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the system variable.
Scope: Global, Session
Dynamic: Yes
character_set_databaseDescription: 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 . This variable is dynamic, but should not be set manually, only by the server.
Scope: Global, Session
Dynamic: Yes
Data Type: string
character_set_filesystemDescription: The for the filesystem. Used for converting file names specified as a string literal from 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 or on system where multi-byte file names are use.
Command line: --character-set-filesystem=name
Scope: Global, Session
character_set_resultsDescription: used for results and error messages returned to the client. From , the utf8 (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the system variable.
Scope: Global, Session
Dynamic: Yes
character_set_serverDescription: Default used by the server. See for character sets used by the default database. Defaults may be different on some systems, see for example .
Command line: --character-set-server
Scope: Global, Session
Dynamic: Yes
character_set_systemDescription: used by the server to store identifiers, always set to utf8, or its synonym utf8mb3 starting with . From , the utf8 (and related collations) is by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the system variable.
Scope: Global
Dynamic: No
character_sets_dirDescription: Directory where the are installed.
Command line: --character-sets-dir=path
Scope: Global
Dynamic: No
check_constraint_checksDescription: If set to 0, will disable , for example when loading a table that violates some constraints that you plan to fix later.
Scope: Global, Session
Dynamic: Yes
Type: boolean
collation_connectionDescription: Collation used for the connection .
Scope: Global, Session
Dynamic: Yes
Data Type: string
collation_databaseDescription: 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_serverDescription: Default used by the server. This is set to the default collation for a given character set automatically when is changed, but it can also be set manually. Defaults may be different on some systems, see for example .
Command line: --collation-server=name
Scope: Global, Session
Dynamic: Yes
completion_typeDescription: 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 statement is equivalent to COMMIT AND CHAIN, while a 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 statement is equivalent to COMMIT RELEASE, while a 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.
concurrent_insertDescription: If set to AUTO or 1, the default, MariaDB allows and SELECTs for 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 option is used when starting the server, concurrent_insert is set to NEVER. Changing the variable only affects new opened tables. Use If you want it to also affect cached tables. See for more.
connect_timeoutDescription: 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
core_fileDescription: 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 for more information.
Previously this system variable existed only as an , but it was also made into a read-only system variable starting with , and .
datadirDescription: Directory where the data is stored.
Command line: --datadir=path or -h path
Scope: Global
Dynamic: No
date_formatDescription: Unused.
Removed:
datetime_formatDescription: Unused.
Removed:
debug/debug_dbugDescription: 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
debug_no_thread_alarmDescription: 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
debug_syncDescription: Used in debugging to show the interface to the . MariaDB needs to be configured with -DENABLE_DEBUG_SYNC=1 for this variable to be available.
Scope: Session
Dynamic: Yes
Data Type: string
default_password_lifetimeDescription: This defines the global . 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 .
Command line: --default-password-lifetime=#
Scope: Global
Dynamic: Yes
default_regex_flagsDescription: Introduced to address remaining incompatibilities between and the old regex library. Accepts a comma-separated list of zero or more of the following values:
Command line: --default-regex-flags=value
Scope: Global, Session
Dynamic: Yes
Type: enumeration
default_storage_engineDescription: The default . 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
default_table_typeDescription: A synonym for . Removed in .
Command line: --default-table-type=name
Scope: Global, Session
Dynamic: Yes
default_tmp_storage_engineDescription: Default storage engine that will be used for tables created with where no engine is specified. For internal temporary tables see ). The storage engine used must be active or the server will not start. See for the default for non-temporary tables. Defaults to NULL, in which case the value from is used. temporary tables cannot be created. Before , attempting to do so would silently fail, and a MyISAM table would instead be created. From , an error is returned.
Command line: --default-tmp-storage-engine=name
Scope: Global, Session
default_week_formatDescription: Default mode for the function. See that page for details on the different modes
Command line: --default-week-format=#
Scope: Global, Session
Dynamic: Yes
delay_key_writeDescription: Specifies how MyISAM tables handles 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]
delayed_insert_limitDescription: After this many rows have been inserted with , the handler will check for and execute any waiting statements.
Command line: --delayed-insert-limit=#
Scope: Global
Dynamic: Yes
delayed_insert_timeoutDescription: Time in seconds that the handler will wait for INSERTs before terminating.
Command line: --delayed-insert-timeout=#
Scope: Global
Dynamic: Yes
delayed_queue_sizeDescription: Number of rows, per table, that can be queued when performing 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
disconnect_on_expired_passwordDescription: When a user password has expired (see ), 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
div_precision_incrementDescription: The precision of the result of the decimal division will be the larger than the precision of the dividend by that number. By default it's 4, so SELECT 2/15 would return 0.1333 and SELECT 2.0/15 would return 0.13333. After setting div_precision_increment to 6, for example, the same operation would return 0.133333 and 0.1333333 respectively.
From , , , and , div_precision_increment is taken into account in intermediate calculations. Previous versions did not, and the results were dependent on the optimizer, and therefore unpredictable.
In , , , , , , , , , and only, the fix truncated decimal values after every division, resulting in lower precision in some cases for those versions only.
From , , , and , a different fix was implemented. Instead of truncating decimal values after every division, they are instead truncated for comparison purposes only.
For example
Versions other than , , , , , , , , , and :
, , , , , , , , , and only:
This is because the intermediate result, SELECT 55/23244 takes into account div_precision_increment and results were truncated after every division in those versions only.
Command line: --div-precision-increment=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
encrypt_tmp_disk_tablesDescription: Enables automatic encryption of all internal on-disk temporary tables that are created during query execution if is set. See and .
Command line: --encrypt-tmp-disk-tables[={0|1}]
Scope: Global
Dynamic: Yes
encrypt_tmp_filesDescription: Enables automatic encryption of temporary files, such as those created for filesort operations, binary log file caches, etc. See .
Command line: --encrypt-tmp-files[={0|1}]
Scope: Global
Dynamic: No
encryption_algorithmDescription: Which encryption algorithm to use for table encryption. aes_cbc is the recommended one. See .
Command line: --encryption-algorithm=value
Scope: Global
Dynamic: No
enforce_storage_engineDescription: 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 will prevent any tables from being created. If another engine is specified in a statement, the outcome depends on whether the NO_ENGINE_SUBSTITUTION 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
engine_condition_pushdownDescription: Deprecated in and removed and replaced by the engine_condition_pushdown={on|off} flag in . Specifies whether the engine condition pushdown optimization is enabled. Since , engine condition pushdown is enabled for all engines that support it.
Command line: --engine-condition-pushdown
Scope: Global, Session
eq_range_index_dive_limitDescription: 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
error_countDescription: Read-only variable denoting the number of errors from the most recent statement in the current session that generated errors. See .
Scope: Session
Dynamic: Yes
Data Type: numeric
event_schedulerDescription: Status of the 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
expensive_subquery_limitDescription: 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
explicit_defaults_for_timestampDescription: This option causes to create all columns as 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:
external_userDescription: External user name set by the plugin used to authenticate the client. NULL if native MariaDB authentication is used. For example, from , the permits an authentication string, so that the OS and MariaDB user will be different. external_user then contains the external OS user. See
Scope: Session
Dynamic: No
flushDescription: 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
flush_timeDescription: 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
foreign_key_checksDescription: If set to 1 (the default) (including ON UPDATE and ON DELETE behavior) 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.
Command line: None
Scope: Global, Session
ft_boolean_syntaxDescription: List of operators supported by an IN BOOLEAN MODE . 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
ft_max_word_lenDescription: Maximum length for a word to be included in the . 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 for the equivalent.
Command line: --ft-max-word-len=#
Scope: Global
ft_min_word_lenDescription: Minimum length for a word to be included in the . 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 for the equivalent.
Command line: --ft-min-word-len=#
Scope: Global
ft_query_expansion_limitDescription: For , denotes the numer of top matches when using WITH QUERY EXPANSION.
Command line: --ft-query-expansion-limit=#
Scope: Global
Dynamic: No
ft_stopword_fileDescription: File containing a list of for use in . 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 for the equivalent.
Command line: --ft-stopword-file=file_name
general_logDescription: If set to 0, the default unless the --general-log option is used, the is disabled, while if set to 1, the general query log is enabled. See 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
general_log_fileDescription: Name of the file. If this is not specified, the name is taken from the setting or from your system hostname with .log as a suffix. If 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
group_concat_max_lenDescription: Maximum length in bytes of the returned result for the functions , and .
Command line: --group-concat-max-len=#
Scope: Global, Session
Dynamic: Yes
.
have_compressDescription: If the zlib compression library is accessible to the server, this will be set to YES, otherwise it will be NO. The and functions will only be available if set to YES.
Scope: Global
Dynamic: No
have_cryptDescription: 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 function cannot be used.
Scope: Global
Dynamic: No
have_csvDescription: If the server supports , will be set to YES, otherwise will be set to NO. Removed in , use the table or instead.
Scope: Global
Dynamic: No
have_dynamic_loadingDescription: If the server supports dynamic loading of , will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No
have_geometryDescription: If the server supports spatial data types, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No
have_ndbclusterDescription: If the server supports NDBCluster.
Scope: Global
Dynamic: No
Removed:
have_partitioningDescription: 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 - should be used instead.
Scope: Global
Dynamic: No
have_profilingDescription: If statement profiling is available, will be set to YES, otherwise will be set to NO. See and .
Scope: Global
Dynamic: No
have_query_cacheDescription: If the server supports the , will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No
have_rtree_keysDescription: If RTREE indexes (used for ) are available, will be set to YES, otherwise will be set to NO.
Scope: Global
Dynamic: No
have_symlinkDescription: 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.
histogram_sizeDescription: Number of bytes used for a , or, from when is set to JSON_HB, number of buckets. If set to 0, no histograms are created by .
Command line: --histogram-size=#
Scope: Global, Session
histogram_typeDescription: Specifies the type of created by ..
SINGLE_PREC_HB - single precision height-balanced.
DOUBLE_PREC_HB - double precision height-balanced.
host_cache_sizeDescription: 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 , clearing the host cache and truncating the 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=#.
hostnameDescription: When the server starts, this variable is set to the server host name.
Scope: Global
Dynamic: No
Data Type: string
identityDescription: A synonym for variable.
idle_readonly_transaction_timeoutDescription: 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 , and .
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
idle_transaction_timeoutDescription: 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 , and .
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
idle_write_transaction_timeoutDescription: 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 , and . Called idle_readwrite_transaction_timeout until .
Scope: Global, Session
Dynamic: Yes
ignore_db_dirsDescription: Tells the server that this directory can never be a database. That means two things - firstly it is ignored by the command and 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
in_predicate_conversion_thresholdDescription: 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 .
Command line: --in-predicate-conversion-threshold=#
Scope: Global, Session
Dynamic: No
in_transactionDescription: 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
init_connectDescription: 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 privilege or, from , the privilege, who can then still connect and correct the error. See also .
Command line: --init-connect=name
Scope: Global
init_fileDescription: 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 .
Command line: init-file=file_name
Scope: Global
Dynamic: No
insert_idDescription: Value to be used for the next statement inserting a new value.
Scope: Session
Dynamic: Yes
Data Type: numeric
interactive_timeoutDescription: 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 .
Command line: --interactive-timeout=#
Scope: Global, Session
Dynamic: Yes
join_buffer_sizeDescription: 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
join_buffer_space_limitDescription: Maximum size in bytes of the query buffer, By default 1024_128_10.
Command line: --join-buffer-space-limit=#
Scope: Global, Session
Dynamic: Yes
join_cache_levelDescription: 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)
keep_files_on_createDescription: If a 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. Deprecated in .
Command line: --keep-files-on-create=#
Scope: Global, Session
large_files_supportDescription: ON if the server if was compiled with large file support or not, else OFF
Scope: Global
Dynamic: No
large_page_sizeDescription: 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 . Deprecated and unused in since multiple page size support was added.
Scope: Global
Dynamic: No
Data Type: numeric
large_pagesDescription: Indicates whether large page support (prior to , 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 the and for online DDL (of size 3* (or 6 when encryption is used)). To use large pages, the Linux sysctl variable kernel.shmmax must be large than the llocation. Also, the sysctl variable vm.nr_hugepages multipled by ) must be larger than the usage. The ulimit for locked memory must be sufficient to cover the amount used (ulimit -l and equalivent in /etc/security/limits.conf / or in systemd ). If these operating system controls or insufficient free huge pages are available, the allocation of large pages will fall back to conventional memory allocation, and a warning will appear in the logs. Only allocations of the default
last_insert_idDescription: Contains the same value as that returned by . Note that setting this variable doen't update the value returned by the underlying function.
Scope: Session
Dynamic: Yes
Data Type: numeric
lc_messagesDescription: This system variable can be specified as a name. The language of the associated will be used for error messages. See 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 name, but the server can't find an for the language associated with the , then the default language will be used instead.
lc_messages_dirDescription: This system variable can be specified either as the path to the directory storing the server's or as the path to the directory storing the specific language's . See 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 . Therefore, it constructs the path to the language's by concatenating the value of this system variable with the language name of the specified by the system variable.
If the server does not find the 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 .
lc_time_namesDescription: The locale that determines the language used for the date and time functions , and . 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 for a full list of supported locales.
Command line: --lc-time-names=name
Scope: Global, Session
licenseDescription: Server license, for example GPL.
Scope: Global
Dynamic: No
Data Type: string
local_infileDescription: If set to 1, LOCAL is supported for 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
lock_wait_timeoutDescription: Timeout in seconds for attempts to acquire . Statements using metadata locks include , , HANDLER and DML and DDL operations on tables, and , and . The timeout is separate for each attempt, of which there may be multiple in a single statement. 0 means no wait. See .
Command line: --lock-wait-timeout=#
Scope: Global, Session
locked_in_memoryDescription: Indicates whether --memlock was used to lock mariadbd in memory.
Command line: --memlock
Scope: Global
Dynamic: No
logDescription: Deprecated and removed in , use instead.
Command line: -l [filename] or --log[=filename]
Scope: Global
Dynamic: Yes
log_disabled_statementsDescription: If set, the specified type of statements (slave and/or stored procedure statements) will not be logged to the . Multiple values are comma-separated, without spaces.
Command line: --log-disabled_statements=value
Scope: Global, Session
Dynamic: No
log_errorDescription: Specifies the name of the . If 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 for details. Note that if 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.
log_outputDescription: How the output for the and the is stored. By default, written to file (FILE), it can also be stored in the and 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 , and the and server system variables.
Command line: --log-output=name
log_queries_not_using_indexesDescription: 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 (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 .
Command line: --log-queries-not-using-indexes
Scope: Global
log_slow_admin_statementsDescription: Log slow , , and other statements to the if it is open. See also and . Deprecated, use without admin.
Command line: --log-slow-admin-statements
Scope: Global
log_slow_disabled_statementsDescription: If set, the specified type of statements will not be logged to the . See also and .
Command line: --log-slow-disabled_statements=value
Scope: Global, Session
Dynamic: No
log_slow_filterDescription: Comma-delimited string (without spaces) containing one or more settings for filtering what is logged to the . If a query matches one of the types listed in the filter, and takes longer than , it will be logged (except for 'not_using_index' which is always logged if enabled, regardless of the time). Sets to ON. See also .
admin log queries (create, optimize, drop etc...)
filesort logs queries that use a filesort.
log_slow_max_warningsDescription: Max numbers of warnings printed to slow query log per statement
Command line: log-slow-max-warnings=#
Scope: Global, Session
Dynamic: Yes
log_slow_min_examined_row_limitDescription: Don't write queries to 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 , queries slower than will always be logged.
Command line: --log-slow-min-examined-row-limit=#
Scope: Global, Session
log_slow_queriesDescription: Deprecated and removed in , use instead.
Command line: --log-slow-queries[=name]
Scope: Global
Dynamic: Yes
log_slow_queryDescription: If set to 0, the default unless the --slow-query-log option is used, the is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. Named before , which is now an alias.
Command line: --slow-query-log
Scope: Global, Session
Dynamic: Yes
log_slow_query_fileDescription: Name of the file. Before , was named . This was named log_slow_query_file_name in the preview release. If 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
log_slow_query_timeDescription: If a query takes longer than this many seconds to execute (microseconds can be specified too), the status variable is incremented and, if enabled, the query is logged to the . Before , was named . Affected by and .
Command line: --log-slow-query-time=#
Scope: Global, Session
log_slow_rate_limitDescription: The 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 . From , queries slower than will always be logged.
Command line: log-slow-rate-limit=#
Scope: Global, Session
log_slow_verbosityDescription: Controls information to be added to the . Options are added in a comma-delimited string. See also . log_slow_verbosity is not supported when log_output='TABLE'.
query_plan logs query execution plan information
innodb Alias to engine (from and ), previously ignored.
log_tc_sizeDescription: Defines the size in bytes of the memory-mapped file-based transaction coordinator log, which is only used if the 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 for more information. Also see the server option and the option.
Command line: log-tc-size=#
Scope: Global
log_warningsDescription: Determines which additional warnings are logged. Setting to 0 disables additional warning logging. Note that this does not prevent all warnings, there is a core set of warnings that will always be written to the error log. The additional warnings are as follows:
log_warnings >= 1
information.
long_query_timeDescription: If a query takes longer than this many seconds to execute (microseconds can be specified too), the status variable is incremented and, if enabled, the query is logged to the . From , this is an alias for .
Command line: --long-query-time=#
Scope: Global, Session
low_priority_updatesDescription: If set to 1 (0 is the default), for that use only table-level locking (, , and ), 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 and earlier, is a synonym.
Command line: --low-priority-updates
lower_case_file_systemDescription: 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
lower_case_table_namesDescription: 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[=#]
max_allowed_packetDescription: Maximum size in bytes of a packet or a generated/intermediate string. The packet message buffer is initialized with the value from , 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 for a specific limit for replication purposes.
Command line: --max-allowed-packet=#
Scope: Global, Session
max_connect_errorsDescription: 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 statement or . The table contains the status of the current hosts.
Command line: --max-connect-errors=#
Scope: Global
max_connectionsDescription: The maximum number of simultaneous client connections. See also . 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 (). 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=#
max_delayed_threadsDescription: Limits to the number of 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
max_digest_lengthDescription: Maximum length considered for computing a statement digest, such as used by the 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,
max_error_countDescription: Specifies the maximum number of messages stored for display by and statements.
Command line: --max-error-count=#
Scope: Global, Session
Dynamic: Yes
max_heap_table_sizeDescription: Maximum size in bytes for user-created 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 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 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
max_insert_delayed_threadsDescription: Synonym for .
max_join_sizeDescription: 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 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 . Previously named , which is still a synonym.
Command line: --max-join-size=#
Scope: Global, Session
max_length_for_sort_dataDescription: 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
max_long_data_sizeDescription: Maximum size for parameter values sent with mysql_stmt_send_long_data(). If not set, will default to the value of . Deprecated in and removed in ; use instead.
Command line: --max-long-data-size=#
Scope: Global
Dynamic: No
max_open_cursorsDescription: The maximum number of open allowed per session.
Command line: --max-open-cursors=#
Scope: Global, Session
Dynamic: Yes
max_password_errorsDescription: The maximum permitted number of failed connection attempts due to an invalid password before a user is blocked from further connections. will permit the user to connect again. This limit is not applicable for users with the privilege or, from , the privilege, with a hostname of localhost, 127.0.0.1 or ::1. See also the .
Command line: --max-password-errors=#
Scope: Global
max_prepared_stmt_countDescription: 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
max_recursive_iterationsDescription: Maximum number of iterations when executing recursive queries, used to prevent infinite loops in .
Command line: --max-recursive-iterations=#
Scope: Global, Session
Dynamic: Yes
max_rowid_filter_sizeDescription: The maximum size of the container of a rowid filter.
Command line: --max-rowid-filter-size=#
Scope: Global, Session
Dynamic: Yes
max_seeks_for_keyDescription: 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
max_session_mem_usedDescription: Amount of memory a single user session is allowed to allocate. This limits the value of the session variable .
Command line: --max-session-mem-used=#
Scope: Global, Session
Dynamic: Yes
max_sort_lengthDescription: 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 to be increased (especially if ER_OUT_OF_SORTMEMORY errors start appearing). From , a warning is generated when max_sort_length is exceeded.
Command line: --max-sort-length=#
Scope: Global, Session
max_sp_recursion_depthDescription: Permitted number of recursive calls for a . 0, the default, no recursion is permitted. Increasing this value increases the thread stack requirements, so you may need to increase as well. This limit doesn't apply to .
Command line: --max-sp-recursion-depth[=#]
Scope: Global, Session
max_statement_timeDescription: Maximum time in seconds that a query can execute before being aborted. This includes all queries, not just statements, but excludes statements in stored procedures. If set to 0, no limit is applied. See for details and limitations. Useful when combined with for limiting the execution times of individual queries. Replicas are not affected by this variable, however, from , there's that sets the limit to abort queries on a replica.
Command line: --max-statement-time[=#]
Scope: Global, Session
max_tmp_tablesDescription: Unused.
Removed:
max_user_connectionsDescription:
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 privilege or, from , the 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 resource option is non-zero. When both global variable and the user resource option are set, the user's is used. Note: This variable does not affect users with the privilege or, from , the privilege.
Command line: --max-user-connections=#
max_write_lock_countDescription: 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 or .
Command line: --max-write-lock-count=#
Scope: Global
Dynamic: No
metadata_locks_cache_sizeDescription: Unused since 10.1.4
Command line: --metadata-locks-cache-size=#
Scope: Global
Dynamic: No
metadata_locks_hash_instancesDescription: Unused since 10.1.4
Command line: --metadata-locks-hash-instances=#
Scope: Global
Dynamic: No
metadata_locks_instancesDescription: Number of fast lanes to create for metadata locks. Can be used to improve DML scalability by eliminating MDL_lock::rwlock load. Use 1 to disable MDL fast lanes. Supported MDL namespaces: BACKUP.
Command line: --metadata-locks-instances=#
Scope: Global
Dynamic: No
min_examined_row_limitDescription: Don't write queries to that examine fewer rows than the set value. If set to 0, the default, no row limit is used. From , this is an alias for .
Command line: --min-examined-row-limit=#
Scope: Global, Session
mrr_buffer_sizeDescription: Size of buffer to use when using multi-range read with range access. See for more information.
Command line: --mrr-buffer-size=#
Scope: Global, Session
Dynamic: Yes
multi_range_countDescription: Ignored. Use instead.
Command line: --multi-range-count=#
Default Value: 256
Removed:
mysql56_temporal_formatDescription: If set (the default), MariaDB uses the MySQL 5.6 low level formats for , and instead of the version. The version MySQL introduced in 5.6 requires more storage, but potentially allows negative dates and has some advantages in replication. There should be no reason to revert to the old microsecond format. See also .
Command line: --mysql56-temporal-format
Scope: Global
named_pipeDescription: On Windows systems, determines whether connections over named pipes are permitted.
Command line: --named-pipe
Scope: Global
Dynamic: No
net_buffer_lengthDescription: The starting size, in bytes, for the connection and thread buffers for each client thread. The size can grow to . This variable's session value is read-only. Can be set to the expected length of client statements if memory is a limitation.
Command line: --net-buffer-length=#
Scope: Global, Session
Dynamic: Yes
net_read_timeoutDescription: Time in seconds the server will wait for a client connection to send more data before aborting the read. See also and
Command line: --net-read-timeout=#
Scope: Global, Session
Dynamic: Yes
net_retry_countDescription: Permit this many retries before aborting when attempting to read or write on a communication port. On FreeBSD systems should be set higher as threads are sent internal interrupts..
Command line: --net-retry-count=#
Scope: Global, Session
Dynamic: Yes
net_write_timeoutDescription: Time in seconds to wait on writing a block to a connection before aborting the write. See also and .
Command line: --net-write-timeout=#
Scope: Global, Session
Dynamic: Yes
new_modeDescription: Used to enable new behavior in otherwise stable versions. See . Non-default NEW_MODE options are by design deprecated and will eventually be removed.
Command line: --new-mode
Scope: Global, Session
Dynamic: Yes
note_verbosityDescription: Verbosity level for note-warnings given to the user. Options are added in a comma-delimited string, except for all, which sets all options. Be aware that if the old variable is 0, one will not get any notes. Setting note_verbosity to "" is the recommended way to disable notes.
basic All old notes.
unusable_keys
oldDescription: Disabled by default, enabling it reverts index hints to those used before MySQL 5.1.17. Enabling may lead to replication errors. Deprecated and replaced by from .
Command line: --old
Scope: Global, Session
Dynamic: Yes
old_alter_tableDescription: From , an alias for . Prior to that, if set to 1 (0 is default), MariaDB reverts to the non-optimized, pre-MySQL 5.1, method of processing statements. A temporary table is created, the data is copied over, and then the temporary table is renamed to the original.
Command line: --old-alter-table
Scope: Global, Session
old_modeDescription: Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See . Fully replaces the variable from . Non-default OLD_MODE options are by design deprecated and will eventually be removed.
Command line: --old-mode
Scope: Global, Session
Dynamic: Yes
old_passwordsDescription: If set to 1 (0 is default), MariaDB reverts to using the authentication plugin by default for newly created users and passwords, instead of the authentication plugin.
Scope: Global, Session
Dynamic: Yes
open_files_limitDescription: The number of file descriptors available to MariaDB. If you are getting the Too many open files error, then you should increase this limit. If set to 0, then MariaDB will calculate a limit based on the following:
MAX(*5, +*2)
MariaDB sets the limit with . MariaDB cannot set this to exceed the hard limit imposed by the operating system. Therefore, you may also need to change the hard limit. There are a few ways to do so.
If you are using to start mariadbd, then see the instructions at .
If you are using to start mariadbd, then see the instructions at .
Otherwise, you can change the hard limit for the mysql user account by modifying . See for more details.
optimizer_extra_pruning_depthDescription:If the optimizer needs to enumerate a join prefix of this size or larger, then it will try aggressively prune away the search space.
Command line: --optimizer-extra-pruning-depth[=#]
Scope: Global, Session
Dynamic: Yes
optimizer_join_limit_pref_ratio
Description:Controls the .
Command line: --optimizer-join-limit-pref-ratio[=#]
Scope: Global, Session
Dynamic: Yes
optimizer_max_sel_arg_weightDescription: This is an actively enforced maximum effective SEL_ARG tree weight limit. A SEL_ARG weight is the number of effective "ranges" hanging off this root (that is, merged tree elements are "unmerged" to count the weight). During range analysis, looking for possible index merges, SEL_ARG graphs related to key ranges in query conditions are being processed. Graphs exceeding this limit will stop keys being 'and'ed and 'or'ed together to form a new larger SEL_ARG graph. After each 'and' or 'or' process, this maximum weight limit is enforced. It enforces this limit by pruning the key part being used. This key part pruning can be used to limit/disable index merge SEL_ARG graph construction on overly long query conditions.
Command line: --optimizer-max-sel-arg-weight=#
Scope: Global, Session
optimizer_max_sel_argsDescription: The maximum number of SEL_ARG objects created when optimizing a range. If more objects would be needed, range scans will not be used by the optimizer.
Command line: --optimizer-max-sel-args=#
Scope: Global, Session
Dynamic: Yes
optimizer_prune_levelDescription:Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space.
0: heuristics are disabled and an exhaustive search is performed
1: the optimizer will use heuristics to prune less-promising partial plans from the optimizer search space
optimizer_search_depthDescription: Maximum search depth by the query optimizer. Smaller values lead to less time spent on execution plans, but potentially less optimal results. If set to 0, MariaDB will automatically choose a reasonable value. Since the better results from more optimal planning usually offset the longer time spent on planning, this is set as high as possible by default. 63 is a valid value, but its effects (switching to the original find_best search) are deprecated.
Command line: --optimizer-search-depth[=#]
Scope: Global, Session
optimizer_selectivity_sampling_limitDescription: Controls number of record samples to check condition selectivity. Only used if [optimizer_use_condition_selectivity](server-system-variables.md#optimizer_use_condition_selectivity) > 4.
Command line: optimizer-selectivity-sampling-limit[=#]
Scope: Global, Session
optimizer_switchDescription: A series of flags for controlling the query optimizer. See for defaults, and a comparison to MySQL.
Command line: --optimizer-switch=value
Scope: Global, Session
Dynamic: Yes
optimizer_record_contextDescription: Controls storing of optimizer context of all tables that are referenced in a query.
Command line: --optimizer-record-context{=0|1}
Scope: Session
Dynamic: Yes
optimizer_traceDescription: Controls : optimizer_trace=option=val[,option=val...], where option is one of {enabled} and val is one of {on, off, default}
Command line: --optimizer-trace=value
Scope: Global, Session
Dynamic: Yes
optimizer_trace_max_mem_sizeDescription: Limits the memory used while tracing a query by specifying the maximum allowed cumulated size, in bytes, of stored .
Command line: --optimizer-trace-max-mem-size=#
Scope: Global, Session
Dynamic: Yes
optimizer_use_condition_selectivityDescription: Controls which statistics can be used by the optimizer when looking for
the best query execution plan. In most cases, the default value, 4 will be suitable. However, if you are hitting some of the rare cases where this does not work well (see ), you can usually work around this by setting this variable to 1.
1 Use selectivity of predicates as in .
2
pid_fileDescription: Full path of the process ID file. If is also set, pid_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: --pid-file=file_name
Scope: Global
plugin_dirDescription: Path to the directory. For security reasons, either make sure this directory can only be read by the server, or set .
Command line: --plugin-dir=path
Scope: Global
Dynamic: No
plugin_maturityDescription: The lowest acceptable maturity. MariaDB will not load plugins less mature than the specified level.
Command line: --plugin-maturity=level
Scope: Global
Dynamic: No
portDescription: Port to listen for TCP/IP connections. If set to 0, will default to, in order of preference, my.cnf, the MYSQL_TCP_PORT , /etc/services, built-in default (3306).
Command line: --port=#, -P
Scope: Global
preload_buffer_sizeDescription: Size in bytes of the buffer allocated when indexes are preloaded.
Command line: --preload-buffer-size=#
Scope: Global, Session
Dynamic: Yes
profilingDescription: If set to 1 (0 is default), statement profiling will be enabled. See and .
Scope: Global, Session
Dynamic: Yes
profiling_history_sizeDescription: Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See .
Command line: --profiling-history-size=#
Scope: Global, Session
progress_report_timeDescription: Time in seconds between sending to the client for time-consuming statements. If set to 0, progress reporting will be disabled.
Command line: --progress-report-time=#
Scope: Global, Session
protocol_versionDescription: The version of the client/server protocol used by the MariaDB server.
Command line: None
Scope: Global
Dynamic: No
proxy_protocol_networksDescription: Enable for these source networks. The syntax is a comma separated list of IPv4 and IPv6 networks. If the network doesn't contain a mask, it is considered to be a single host. "*" represents all networks and must be the only directive on the line. String "localhost" represents non-TCP local connections (Unix domain socket, Windows named pipe or shared memory). See .
Command line: --proxy-protocol-networks=value
Scope: Global
proxy_userDescription: Set to the proxy user account name if the current client is a proxy, else NULL.
Scope: Session
Dynamic: No
Data Type: string
pseudo_slave_modeDescription: For internal use by the server.
Scope: Session
Dynamic: Yes
Data Type: numeric
pseudo_thread_idDescription: For internal use only.
Scope: Session
Dynamic: Yes
Data Type: numeric
query_alloc_block_sizeDescription: Size in bytes of the extra blocks allocated during query parsing and execution (after is used up).
Command line: --query-alloc-block-size=#
Scope: Global, Session
Dynamic: Yes
query_cache_limitDescription: Size in bytes for which results larger than this are not stored in the .
Command line: --query-cache-limit=#
Scope: Global
Dynamic: Yes
query_cache_min_res_unitDescription: Minimum size in bytes of the blocks allocated for results.
Command line: --query-cache-min-res-unit=#
Scope: Global
Dynamic: Yes
query_cache_sizeDescription: Size in bytes available to the . About 40KB is needed for query cache structures, so setting a size lower than this will result in a warning. 0, the default before , effectively disables the query cache.
Warning: Starting from , is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value. This will happen even if is explicitly set to OFF in the configuration.
Command line: --query-cache-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
query_cache_strip_commentsDescription: If set to 1 (0 is default), the server will strip any comments from the query before searching to see if it exists in the . Multiple space, line feeds, tab and other white space characters will also be removed.
Command line: query-cache-strip-comments
Scope: Session, Global
query_cache_typeDescription: If set to 0, the is disabled (although a buffer of bytes is still allocated). If set to 1 all SELECT queries will be cached unless SQL_NO_CACHE is specified. If set to 2 (or DEMAND), only queries with the SQL CACHE clause will be cached. Note that if the server is started with the query cache disabled, it cannot be enabled at runtime.
Warning: Starting from , query_cache_type is automatically set to ON if the server is started with the set to a non-zero (and non-default) value. This will happen even if is explicitly set to OFF in the configuration.
Command line: --query-cache-type=#
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
query_cache_wlock_invalidateDescription: If set to 0, the default, results present in the will be returned even if there's a write lock on the table. If set to 1, the client will first have to wait for the lock to be released.
Command line: --query-cache-wlock-invalidate
Scope: Global, Session
query_prealloc_sizeDescription: Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect. Increasing may be useful if complex queries are being run, as this will reduce the need for more memory allocations during query operation. See also .
Command line: --query-prealloc-size=#
Scope: Global, Session
Dynamic: Yes
rand_seed1Description: rand_seed1 and rand_seed2 facilitate replication of the function. The master passes the value of these to the slaves so that the random number generator is seeded in the same way, and generates the same value, on the slave as on the master.
Command line: None
Scope: Session
rand_seed2Description: See .
range_alloc_block_sizeDescription: Size in bytes of blocks allocated during range optimization. The unit size in 1024.
Command line: --range-alloc-block-size=#
Scope: Global, Session
Dynamic: Yes
read_buffer_sizeDescription: Each thread performing a sequential scan (for MyISAM, Aria and MERGE tables) allocates a buffer of this size in bytes for each table scanned. Increase if you perform many sequential scans. If not in a multiple of 4KB, will be rounded down to the nearest multiple. Also used in ORDER BY's for caching indexes in a temporary file (not temporary table), for caching results of nested queries, for bulk inserts into partitions, and to determine the memory block size of tables.
Command line: --read-buffer-size=#
Scope: Global, Session
read_onlyDescription: Do not allow changes to non-temporary tables. Options are: OFF — changes allowed; ON — Disallow changes for users without the READ ONLY ADMIN privilege; NO_LOCK — Additionally disallows LOCK TABLES and SELECT ... IN SHARE MODE; NO_LOCK_NO_ADMIN — Disallows also for users with READ_ONLY ADMIN privilege. Replication (slave) threads are not affected by this option.
read_rnd_buffer_sizeDescription: Size in bytes of the buffer used when reading rows from a table in sorted order after a key sort. Larger values improve ORDER BY performance, although rather increase the size by SESSION where the need arises to avoid excessive memory use.
Command line: --read-rnd-buffer-size=#
Scope: Global, Session
Dynamic: Yes
redirect_urlDescription: URL of another server to redirect clients to. Format should be {mysql,mariadb}://host [:port]. Empty string means no redirection. For example, set global redirect_url="mysql://mariadb.org:12345". See .
Command line: --redirect_url=val
Scope: Global, Session
require_secure_transportDescription: When this option is enabled, connections attempted using insecure transport will be rejected. Secure transports are SSL/TLS, Unix sockets or named pipes. Note that take precedence.
Command line: --require-secure-transport[={0|1}]
Scope: Global
Dynamic: Yes
rowid_merge_buff_sizeDescription: The maximum size in bytes of the memory available to the Rowid-merge strategy. See for more information.
Command line: --rowid-merge-buff-size=#
Scope: Global, Session
Dynamic: Yes
rpl_recovery_rankDescription: Unused.
Removed:
safe_show_databaseDescription: This variable was removed in and has been replaced by the more flexible privilege.
Command line: --safe-show-database (until MySQL 4.1.1)
Scope: Global
Dynamic: Yes
secure_authDescription: Connections will be blocked if they use the authentication plugin. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format. secure_auth=0 was deprecated in , , , , .
Command line: --secure-auth
Scope: Global
secure_file_privDescription: , and will only work with files in the specified path. If not set, the default, or set to empty string, the statements will work with any files that can be accessed.
Command line: --secure-file-priv=path
Scope: Global
Dynamic: No
secure_timestampDescription: Restricts direct setting of a session timestamp. Possible levels are:
YES - timestamp cannot deviate from the system clock. Intended to prevent tampering with history. Should not be used on replicas, as when a value based on the timestamp is inserted in , discrepancies can occur.
REPLICATION - replication thread can adjust timestamp to match the primary's
SUPER - a user with this privilege and a replication thread can adjust timestamp
server_uidDescription: Automatically calculated server unique id hash. Added to the to allow one to verify if error reports are from the same server. UID is a base64-encoded SHA1 hash of the MAC address of one of the interfaces, and the tcp port that the server is listening on.
Command line: None
Scope: Global
Dynamic: No
session_track_schemaDescription: Whether to track changes to the default schema within the current session.
Command line: --session-track-schema={0|1}
Scope: Global, Session
Dynamic: Yes
session_track_state_changeDescription: Whether to track changes to the session state.
Command line: --session-track-state-change={0|1}
Scope: Global, Session
Dynamic: Yes
session_track_system_variablesDescription: Comma-separated list of session system variables for which to track changes. For compatibility with MySQL defaults, this variable should be set to "autocommit, character_set_client, character_set_connection, character_set_results, time_zone". The * character tracks all session variables.
Command line: --session-track-system-variables=value
Scope: Global, Session
session_track_transaction_infoDescription: Track changes to the transaction attributes. OFF to disable; STATE to track just transaction state (Is there an active transaction? Does it have any data? etc.); CHARACTERISTICS to track transaction state and report all statements needed to start a transaction with the same characteristics (isolation level, read only/read write,snapshot - but not any work done / data modified within the transaction).
Command line: --session-track-transaction-info=value
Scope: Global, Session
shared_memoryDescription: Windows only, determines whether the server permits shared memory connections. See also .
Scope: Global
Dynamic: No
shared_memory_base_nameDescription: Windows only, specifies the name of the shared memory to use for shared memory connection. Mainly used when running more than one instance on the same physical machine. By default the name is MYSQL and is case sensitive. See also .
Scope: Global
Dynamic: No
Data Type: string
shutdown_wait_for_slavesDescription: When ON, SHUTDOWN command runs with implicit WAIT FOR ALL SLAVES option. That is, when running SHUTDOWN, before killing the binary log dump threads, the server will first kill all client threads and send all binary log events to all connected replicas.
Scope: Global
Dynamic: No
skip_external_lockingDescription: If this system variable is set, then some kinds of external table locks will be disabled for some .
If this system variable is set, then the storage engine will not use file-based locks. Otherwise, it will use the function with the F_SETLK option to get file-based locks on Unix, and it will use the function to get file-based locks on Windows.
If this system variable is set, then the storage engine will not lock a table when it decrements the table's in-file counter that keeps track of how many connections currently have the table open. See for more information.
skip_grant_tablesDescription: Start without grant tables. This gives all users FULL ACCESS to all tables. Before , available as an . Use , or to resume using the grant tables.
Command line: --skip-grant-tables
Scope: Global
Dynamic: No
skip_name_resolveDescription: If set to ON (OFF is the default), only IP addresses are used for connections. Host names are not resolved. All host values in the GRANT tables must be IP addresses (or localhost). Some container configs explicitly set skip_name_resolve to ON, rather than leave it as the default, OFF.
Command line: --skip-name-resolve
skip_networkingDescription: If set to 1, (0 is the default), the server does not listen for TCP/IP connections. All interaction with the server will be through socket files (Unix) or named pipes or shared memory (Windows). It's recommended to use this option if only local clients are permitted to connect to the server.
Command line: --skip-networking
Scope: Global
Dynamic: No
skip_show_databaseDescription: If set to 1, (0 is the default), only users with the privilege can use the SHOW DATABASES statement to see all database names.
Command line: --skip-show-database
Scope: Global
Dynamic: No
slow_launch_timeDescription: Time in seconds. If a thread takes longer than this to launch, the slow_launch_threads is incremented.
Command line: --slow-launch-time=#
Scope: Global
Dynamic: Yes
slow_query_logDescription: If set to 0, the default unless the --slow-query-log option is used, the is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. From , an alias for .
Command line: --slow-query-log
Scope: Global, Session
Dynamic: Yes
slow_query_log_fileDescription: Name of the file. From , an alias for . If is also set, slow_query_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: --slow-query-log-file=file_name
Scope: Global
socketDescription: On Unix-like systems, this is the name of the socket file used for local client connections, by default /tmp/mysql.sock, often changed by the distribution, for example /var/lib/mysql/mysql.sock. On Windows, this is the name of the named pipe used for local client connections, by default MySQL. On Windows, this is not case-sensitive.
Command line: --socket=name
Scope: Global
sort_buffer_sizeDescription: Each session performing a sort allocates a buffer with this amount of memory. Not specific to any storage engine. If the status variable is too high, you may need to look at improving your query indexes or increasing this. Consider reducing where there are many small sorts, such as OLTP, and increasing where needed by session. 16k is a suggested minimum.
Command line: --sort-buffer-size=#
Scope: Global, Session
sql_auto_is_nullDescription: If set to 1, the query SELECT * FROM table_name WHERE auto_increment_column IS NULL will return an auto-increment that has just been successfully inserted, the same as the LAST_INSERT_ID() function. Some ODBC programs make use of this IS NULL comparison.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
sql_big_selectsDescription: If set to 0, MariaDB will not perform large SELECTs. See for details. If max_join_size is set to anything but DEFAULT, sql_big_selects is automatically set to 0. If sql_big_selects is again set, max_join_size will be ignored.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
sql_big_tablesDescription: Old variable, which if set to 1, allows large result sets by saving all temporary sets to disk, avoiding 'table full' errors. No longer needed, as the server now handles this automatically.
This is a synonym for .
Command line: --sql-big-tables
sql_buffer_resultDescription: If set to 1 (0 is default), results from SELECT statements are always placed into temporary tables. This can help the server when it takes a long time to send the results to the client by allowing the table locks to be freed early.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
sql_if_existsDescription: If set to 1, adds an implicit IF EXISTS to ALTER, RENAME and DROP of TABLES, VIEWS, FUNCTIONS and PACKAGES. This variable is mainly used in replication to tag DDLs that can be ignored on the slave if the target table doesn't exist.
Command line: --sql-if-exists[={0|1}]
Scope: Global, Session
Dynamic: Yes
sql_log_offDescription: If set to 1 (0 is the default), no logging to the is done for the client. Only clients with the privilege can update this variable.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
sql_log_updateDescription: Removed. Use instead.
Removed: MariaDB/MySQL 5.5
sql_low_priority_updatesDescription: If set to 1 (0 is the default), for that use only table-level locking (, , and ), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes.
This is a synonym for .
Command line: --sql-low-priority-updates
sql_max_join_sizeDescription: Synonym for , the preferred name.
Deprecated:
Removed:
sql_modeDescription: Sets the . Multiple modes can be set, separated by a comma.
Command line: --sql-mode=value[,value[,value...]]
Scope: Global, Session
Dynamic: Yes
sql_notesDescription: If set to 1, the default, is incremented each time a Note warning is encountered. If set to 0, Note warnings are not recorded. has outputs to set this variable to 0 so that no unnecessary increments occur when data is reloaded. See also , which defines which notes should be given. The recommended way, as of , to disable notes is to set note_verbosity to "".
Command line: None
Scope: Global, Session
sql_quote_show_createDescription: If set to 1, the default, the server will quote identifiers for , and statements. Quoting is disabled if set to 0. Enable to ensure replication works when identifiers require quoting.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
sql_safe_updatesDescription: If set to 1, UPDATEs and DELETEs must be executed by using an index (simply mentioning an indexed column in a WHERE clause is not enough, optimizer must actually use it) or they must mention an indexed column and specify a LIMIT clause. Otherwise a statement will be aborted. Prevents the common mistake of accidentally deleting or updating every row in a table. Until , could not be set as a command-line option or in my.cnf.
Command line: --sql-safe-updates[={0|1}]
Scope: Global, Session
sql_select_limitDescription: Maximum number of rows that can be returned from a SELECT query. Default is the maximum number of rows permitted per table by the server, usually 232-1 or 264-1. Can be restored to the default value after being changed by assigning it a value of DEFAULT. If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of the variable.
Command line: None
Scope: Global, Session
Dynamic: Yes
sql_warningsDescription: If set to 1, single-row INSERTs will produce a string containing warning information if a warning occurs.
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
storage_engineDescription: See .
Deprecated:
Remove:
standard_compliant_cteDescription: Allow only standard-compliant . Prior to , this variable was named standards_compliant_cte.
Command line: --standard-compliant-cte={0|1}
Scope: Global, Session
stored_program_cacheDescription: Limit to the number of held in the stored procedures and stored functions caches. Each time a stored routine is executed, this limit is first checked, and if the number held in the cache exceeds this, that cache is flushed and memory freed.
Command line: --stored-program-cache=#
Scope: Global
Dynamic: Yes
strict_password_validationDescription: When plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash). This excludes direct updates to the privilege tables.
Command line: --strict-password-validation
Scope: Global
Dynamic: Yes
sync_frmDescription: If set to 1, the default, each time a non-temporary table is created, its .frm definition file is synced to disk. Fractionally slower, but safer in case of a crash.
Command line: --sync-frm
Scope: Global
Dynamic: Yes
system_time_zoneDescription: The system time zone is determined when the server starts. The system is usually read from the operating system's environment but can be overridden by setting the 'TZ' environment variable before starting the server. See for the various ways to change the system time zone. This variable is not the same as the system variable, which is the variable that actually controls a session's active time zone. The system time zone is used for a session when time_zone is set to the special value SYSTEM.
Scope: Global
Dynamic: No
table_definition_cacheDescription: Number of table definitions that can be cached. Table definitions are taken from the .frm files, and if there are a large number of tables increasing the cache size can speed up table opening. Unlike the , as the table_definition_cache doesn't use file descriptors and is much smaller.
Command line: --table-definition-cache=#
Scope: Global
Dynamic: Yes
table_lock_wait_timeoutDescription: Unused, and removed.
Command line: --table-lock-wait-timeout=#
Scope: Global
Dynamic: Yes
table_open_cacheDescription: Maximum number of open tables cached in one table cache instance. See for suggestions on optimizing. Increasing table_open_cache increases the number of file descriptors required.
Command line: --table-open-cache=#
Scope: Global
Dynamic: Yes
table_open_cache_instancesDescription: This system variable specifies the maximum number of table cache instances. MariaDB Server initially creates just a single instance. However, whenever it detects contention on the existing instances, it will automatically create a new instance. When the number of instances has been increased due to contention, it does not decrease again. The default value of this system variable is 8, which is expected to handle up to 100 CPU cores. If your system is larger than this, then you may benefit from increasing the value of this system variable.
Depending on the ratio of actual available file handles, and size, the max. instance count may be auto adjusted to a lower value on server startup.
The implementation and behavior of this feature is different than the same feature in MySQL 5.6.
table_typeDescription: Removed and replaced by . Use instead.
tcp_keepalive_intervalDescription: The interval, in seconds, between when successive keep-alive packets are sent if no acknowledgement is received. If set to 0, the system dependent default is used.
Command line: --tcp-keepalive-interval=#
Scope: Global
Dynamic: Yes
tcp_keepalive_probesDescription: The number of unacknowledged probes to send before considering the connection dead and notifying the application layer. If set to 0, a system dependent default is used.
Command line: --tcp-keepalive-probes=#
Scope: Global
Dynamic: Yes
tcp_keepalive_timeDescription: Timeout, in seconds, with no activity until the first TCP keep-alive packet is sent. If set to 0, a system dependent default is used.
Command line: --tcp-keepalive-time=#
Scope: Global
Dynamic: Yes
tcp_nodelayDescription: Set the TCP_NODELAY option (disable Nagle's algorithm) on socket.
Command line: --tcp-nodelay={0|1}
Scope: Session
Dynamic: Yes
thread_cache_sizeDescription: Number of threads server caches for re-use. If this limit hasn't been reached, when a client disconnects, its threads are put into the cache and re-used where possible. In , , and newer, the threads are freed after 5 minutes of idle time. Normally this setting has little effect, as the other aspects of the thread implementation are more important, but increasing it can help servers with high volumes of connections per second so that most can use a cached, rather than a new, thread. The cache miss rate can be calculated as the threads_created/connections. If the is active, thread_cache_size is ignored. If thread_cache_size is set to greater than the value of , thread_cache_size will be set to the value.
Command line: --thread-cache-size=#
thread_concurrencyDescription: Allows applications to give the system a hint about the desired number of threads. Specific to Solaris only, invokes thr_setconcurrency(). Deprecated and has no effect from .
Command line: --thread-concurrency=#
Scope: Global
Dynamic: No
thread_stackDescription: Stack size for each thread. If set too small, limits recursion depth of stored procedures and complexity of SQL statements the server can handle in memory. Also affects limits in the crash-me test.
Command line: --thread-stack=#
Scope: Global
Dynamic: No
time_formatDescription: Unused.
Removed:
time_zoneDescription: The global value determines the default for sessions that connect. The session value determines the session's active . When it is set to SYSTEM, the session's time zone is determined by the system variable.
Command line: --default-time-zone=string
Scope: Global, Session
timed_mutexesDescription: Determines whether mutexes are timed. OFF, the default, disables mutex timing, while ON enables it. See also for more on mutex statistics. Deprecated and has no effect.
Command line: --timed-mutexes
Scope: Global
timestampDescription: Sets the time for the client. This will affect the result returned by the function, not the function, unless the server is started with the option, in which case SYSDATE becomes an alias of NOW, and will also be affected. Also used to get the original timestamp when restoring rows from the .
Scope: Session
Dynamic: Yes
Valid Values: timestamp_value
tmp_disk_table_sizeDescription: Max size for data for an internal temporary on-disk or table. These tables are created as part of complex queries when the result doesn't fit into the memory engine. You can set this variable if you want to limit the size of temporary tables created in your temporary directory .
Command line: --tmp-disk-table-size=#
Scope: Global, Session
tmp_memory_table_sizeDescription: An alias for .
Command line: --tmp-memory-table-size=#
tmp_table_sizeDescription: The largest size for temporary tables in memory (not tables) although if is smaller the lower limit will apply. You can see if it's necessary to increase by comparing the Created_tmp_disk_tables and Created_tmp_tables to see how many temporary tables out of the total created needed to be converted to disk. Often complex GROUP BY queries are responsible for exceeding the limit. Defaults may be different on some systems, see for example . From , is an alias.
Command line: --tmp-table-size=#
tmpdirDescription: Directory for storing temporary tables and files. Can specify a list (separated by semicolons in Windows, and colons in Unix) that will then be used in round-robin fashion. This can be used for load balancing across several disks. Note that if the server is a replica, and , which overrides tmpdir for replicas, is not set, you should not set tmpdir to a directory that is cleared when the machine restarts, or else replication may fail.
Command line: --tmpdir=path or -t path
transaction_alloc_block_sizeDescription: Size in bytes to increase the memory pool available to each transaction when the available pool is not large enough. See .
Command line: --transaction-alloc-block-size=#
Scope: Global, Session
Dynamic: Yes
transaction_isolationDescription: The transaction isolation level. See also . Introduced in to replace the system variable and align the option and the system variable name.
Command line: --transaction-isolation=name
Scope: Global, Session
Dynamic: Yes
transaction_prealloc_sizeDescription: Initial size of a memory pool available to each transaction for various memory allocations. If the memory pool is not large enough for an allocation, it is increased by bytes, and truncated back to transaction_prealloc_size bytes when the transaction is completed. If set large enough to contain all statements in a transaction, extra malloc() calls are avoided.
Command line: --transaction-prealloc-size=#
Scope: Global, Session
transaction_read_onlyDescription: Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See and .
Command line: None
Scope: Global, Session
tx_isolationDescription: The transaction isolation level. Setting this session variable via set @@tx_isolation= will take effect for only the subsequent transaction in the current session, much like . To set for a session, use SET SESSION tx_isolation or SET @@session.tx_isolation. See . See also . In , this system variable is deprecated and replaced by .
Command line: --transaction-isolation=name
tx_read_onlyDescription: Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See and . In , this system variable is deprecated and replaced by .
Command line: --transaction-read-only=#
unique_checksDescription: If set to 0, storage engines can (but are not required to) assume that duplicate keys are not present in input data. If set to 0, inserting duplicates into a UNIQUE index can succeed, causing the table to become corrupted. Set to 0 to speed up imports of large tables to InnoDB.
Scope: Global, Session
Dynamic: Yes
Type: boolean
updatable_views_with_limitDescription: Determines whether view updates can be made with an UPDATE or DELETE statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table. 0 prohibits this, while 1 permits it while issuing a warning (the default).
Command line: --updatable-views-with-limit=#
Scope: Global, Session
use_stat_tablesDescription: Controls the use of .
never: The optimizer will not use data from statistics tables.
complementary: The optimizer uses data from statistics tables if the same kind of data is not provided by the storage engine.
versionDescription: Server version number. It may also include a suffix with configuration or build information. -debug indicates debugging support was enabled on the server, and -log indicates at least one of the binary log, general log or are enabled, for example 10.0.1-MariaDB-mariadb1precise-log. Can be set at startup in order to fake the server version.
Command line: -V, --version[=name]
version_commentDescription: Value of the COMPILATION_COMMENT option specified by CMake when building MariaDB, for example mariadb.org binary distribution.
Scope: Global
Dynamic: No
Type: string
version_compile_machineDescription: The machine type or architecture MariaDB was built on, for example i686.
Scope: Global
Dynamic: No
Type: string
version_compile_osDescription: Operating system that MariaDB was built on, for example debian-linux-gnu.
Scope: Global
Dynamic: No
Type: string
version_malloc_libraryDescription: Version of the used malloc library.
Command line: None
Scope: Global
Dynamic: No
version_source_revisionDescription: Source control revision id for MariaDB source code, enabling one to see exactly which version of the source was used for a build.
Command line: None
Scope: Global
Dynamic: No
wait_timeoutDescription: Time in seconds that the server waits for a connection to become active before closing it. The session value is initialized when a thread starts up from either the global value, if the connection is non-interactive, or from the value, if the connection is interactive.
Command line: --wait-timeout=#
Scope: Global, Session
Dynamic: Yes
warning_countDescription: Read-only variable indicating the number of warnings, errors and notes resulting from the most recent statement that generated messages. See for more. Note warnings will only be recorded if is true (the default).
Scope: Session
Dynamic: No
Type: numeric
This page is licensed: CC BY-SA / Gnu FDL
P
petabytes
10245 (from )
E
exabytes
10246 (from )
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced:
INPLACEDEFAULT (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:
Deprecated:
Data Type: INT UNSIGNED
Default Value: 4294967295 (4G)
Range: 32 to 4294967295
Introduced: , , , , , ,
Data Type: numeric
Default Value: 100.000000
Range: 0 to 100
Introduced:
Data Type: boolean
Default Value: 1
Data Type: boolean
Default Value: 1
Dynamic: No
Type: number
Default Value:
The lower of 900 and (50 + max_connections/5)
Type: directory name
0.In and earlier, sql_big_tables is a synonym.
From , this system variable is deprecated.
Command line: --big-tables
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Deprecated:
Removed:
Scope: Global
Dynamic: No
Data Type: string
Default Value: (Empty string)
Valid Values: Host name, IPv4, IPv6, ::, *
Introduced: (as a system variable)
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:
Data Type: numeric
Default Value: 8388608
Range - 32 bit: 0 to 4294967295
Range - 64 bit: 0 to 18446744073709547520
Dynamic: Yes
Data Type: string
Default Value:
>= : utf8mb4
<= : latin1
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 (>= )
Empty (<= )
Introduced:
Data Type: string
Default Value:
>=: utf8mb4
>= : utf8mb3
<= : utf8
Default Value: utf8mb4 (>= ), latin1 (<= )
Dynamic: Yes
Data Type: string
Default Value: binary
Data Type: string
Default Value: utf8mb3 (>= ), utf8 (<= )
Data Type: string
Default Value: utf8mb4 (>= ), latin1 (<= )
Data Type: string
Default Value: utf8mb3 (>= ), utf8 (<= )
Type: directory name
Default: ON
Data Type: string
Default Value: latin1_swedish_ci
--completion-type=nameScope: Global, Session
Dynamic: Yes
Data Type: enumerated
Default Value: NO_CHAIN
Valid Values: 0, 1, 2, NO_CHAIN, CHAIN, RELEASE
Command line: --concurrent-insert[=value]
Scope: Global
Dynamic: Yes
Data Type: enumerated
Default Value: AUTO
Valid Values: 0, 1, 2, AUTO, NEVER, ALWAYS
Type: numeric
Default Value: 10
Range: 2 to 31536000
On Windows >= , 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 >= .
Command line: --core-file
Scope: Global
Dynamic: No
Type: boolean
Default Value:
Windows >= : ON
All other systems: OFF
Type: directory name
Dynamic: Yes
Data Type: string
Default Value:
= :
d:t:i:o,/tmp/mariadbd.trace(Unix) ord:t:i:O,\mariadbd.trace(Windows)
Debug Options: See the option flags on the page
Data Type: boolean
Default Value: OFF
Removed:
Default Value: OFF or ON - current signal name
Type: numeric
Default Value: 0
Range: 0 to 4294967295
(?m)
^ and $ match newlines within data
UNGREEDY
(?U)
Invert greediness of quantifiers
Default Value: empty
Valid Values: DOTALL, DUPNAMES, EXTENDED, EXTRA, MULTILINE, UNGREEDY
Type: enumeration
Default Value: InnoDB
Removed:
Dynamic: Yes
Data Type: enumeration
Default Value: NULL
Data Type: numeric
Default Value: 0
Range: 0 to 7
Dynamic: Yes
Data Type: enumeration
Default Value: ON
Valid Values: ON, OFF, ALL
Data Type: numeric
Default Value: 100
Range: 1 to 4294967295
Data Type: numeric
Default Value: 300
Type: numeric
Default Value: 1000
Range: 1 to 4294967295
Type: boolean
Default Value: OFF
Default Value: 4
Range: 0 to 30
Data Type: boolean
Default Value: OFF
Data Type: boolean
Default Value: OFF
Data Type: enum
Default Value: none
Valid Values: none, aes_ecb, aes_cbc, aes_ctr
Introduced:
Removed:
Dynamic: Yes
Data Type: string
Default Value: none
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Deprecated:
Removed:
Data Type: numeric
Default Value: 200
Range: 0 to 4294967295
Dynamic: Yes
Data Type: enumeration
Default Value: OFF
Valid Values: ON (or 1), OFF (or 0), DISABLED
Data Type: numeric
Default Value: 100
Range: 0 upwards
Global (<= , , , )
Dynamic:
Yes (>= , , , )
No (<= , , , )
Data Type: boolean
Default Value:ON (>= ), OFF (<= )
Data Type: string
Default Value: NULL
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value: 0
Data Type: boolean
Default Value: 1
Data Type: string
Default Value: + -><()*:""&|
Dynamic: No
Data Type: numeric
Default Value: 84
Minimum Value: 10
Dynamic: No
Data Type: numeric
Default Value: 4
Minimum Value: 1
Data Type: numeric
Default Value: 20
Range: 0 to 1000
Scope: Global
Dynamic: No
Data Type: file name
Default Value: (built-in)
Dynamic: Yes
Data Type: boolean
Default Value: 0
Dynamic: Yes
Data Type: file name
Default Value: host_name.log
Data Type: numeric
Default Value:
1048576 (1M)
Range: 4 to 4294967295
Removed:
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
Data Type: numeric
Default Value: 254
Range: 0 to 255
JSON_HB - JSON height-balanced histograms (from )Command line: --histogram-type=value
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value:
JSON_HB (>= )
DOUBLE_PREC_HB (<= , >= )
Valid Values:
SINGLE_PREC_HB, DOUBLE_PREC_HB (<= MariaDB 10.6)
SINGLE_PREC_HB, DOUBLE_PREC_HB, JSON_HB (>= )
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 128
Range: 0 to 65536
Default Value: 0
Range: 0 to 31536000
Default Value: 0
Range: 0 to 31536000
Data Type: numeric
Default Value: 0
Range: 0 to 31536000
Dynamic: No
Data Type: string
Data Type: numeric
Default Value: 1000
Range: 0 to 4294967295
Data Type: boolean
Default Value: 0
Data Type: string
Data Type: file name
Data Type: numeric
Default Value: 28800
Range: (Windows): 1 to 2147483
Range: (Other): 1 to 31536000
Data Type: numeric
Default Value: 262144 (256kB)
Range (non-Windows): 128 to 18446744073709547520
Range (Windows): 8228 to 18446744073709547520
Data Type: numeric
Default Value: 2097152
Range: 2048 to 18446744073709551615
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
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Deprecated:
Default Value: Autosized (see description)
Deprecated:
Removed:
Hugepagesize/proc/meminfoCommand line: --large-pages, --skip-large-pages
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
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
See Setting the Language for Error Messages for more information.
Command line: --lc-messages-dir=path
Scope: Global
Dynamic: No
Data Type: directory name
Data Type: string
Default Value: en_US
Dynamic: Yes
Data Type: boolean
Default Value: ON
Dynamic: Yes
Data Type: numeric
Default Value:
86400 (1 day)
Range:
0 to 31536000
Data Type: boolean
Default Value: OFF
Data Type: string
Default Value: OFF
Removed:
Data Type: set
Default Value: sp
Valid Values: slave and/or sp, or empty string for none
Command line: --log-error[=name], --skip-log-error
Scope: Global
Dynamic: No
Data Type: file name
Default Value: (empty string)
Scope: Global
Dynamic: Yes
Data Type: set
Default Value: FILE
Valid Values: TABLE, FILE or NONE
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Dynamic: Yes
Data Type: boolean
Default Value:
ON
Deprecated:
Data Type: set
Default Value: sp
Valid Vales: admin, call, slave and/or sp
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 .
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
Data Type: numeric
Default Value: 10
Range: 0 to 1000
Introduced: , , , ,
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0-4294967295
Introduced:
Data Type: boolean
Default Value: OFF
Removed:
Data Type: boolean
Default Value: 0
Introduced:
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.
Dynamic: Yes
Data Type: file name
Default Value: host_name-slow.log
Introduced:
Data Type: numeric
Default Value: 10.000000
Range: 0 to 31536000
Introduced:
Dynamic: Yes
Data Type: numeric
Default Value: 1
Range: 1 upwards
explain prints EXPLAIN output in the slow query log. See EXPLAIN in the Slow Query Log.
engine Logs engine statistics (from and ).
warnings Print all errors, warnings and notes for the statement to the slow query log. (from ).
all Enables all above options (From )
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:
= , : (Empty),
query_plan,innodb,explain,engine,warnings,all,full
= , : (Empty),
query_plan,innodb,explain,engine,full
<= , : (Empty), query_plan, innodb, explain
Data Type: numeric
Default Value: 24576
Range: 12288 to 18446744073709551615
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 :
Either the default master.info file or the file that is configured by the option.
Either the default relay-log.info file or the file that is configured by the system variable.
Information about a master's .
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 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
Data Type: numeric
Default Value: 10.000000
Range: 0 upwards
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
booleanDefault Value: ##
Dynamic: No
Data Type: numeric
Default Value: 0 (Unix), 1 (Windows), 2 (Mac OS X)
Range: 0 to 2
Data Type: numeric
Default Value:
16777216 (16M)
1073741824 (1GB) (client-side)
Range: 1024 to 1073741824
Data Type: numeric
Default Value: 100
Range: 1 to 4294967295
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 151
Range: 10 to 100000
Dynamic: Yes
Data Type: numeric
Default Value: 20
Range: 0 to 16384
Data Type: numeric
Default Value: 1024
Range: 0 to 1048576
Data Type: numeric
Default Value: 64
Range: 0 to 65535
Dynamic: Yes
Data Type: numeric
Default Value: 16777216
Range : 16384 to 4294966272
Dynamic: Yes
Data Type: numeric
Default Value: 18446744073709551615
Range: 1 to 18446744073709551615
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 4 to 8388608
Data Type: numeric
Default Value: 16777216 (16M)
Range: 1024 to 4294967295
Deprecated:
Removed:
Data Type: numeric
Default Value: 50
Range: 0 to 65536
Introduced:
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295
Data Type: numeric
Default Value: 16382
Range: 0 to 4294967295
Data Type: numeric
Default Value: 1000 (>= ), 4294967295 (<= )
Range: 0 to 4294967295
Data Type: numeric
Default Value: 131072
Range: 1024 to 18446744073709551615
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295
Data Type: numeric
Default Value: 9223372036854775807 (8192 PB)
Range: 8192 to 18446744073709551615
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range:
4 to 8388608 (<= , )
8 to 8388608 (>= , )
Data Type: numeric
Default Value: 0
Range: 0 to 255
Dynamic: Yes
Data Type: numeric
Default Value: 0.000000
Range: 0 to 31536000
Scope: Global, Session
Dynamic: Yes, (except when globally set to 0 or -1)
Data Type: numeric
Default Value: 0
Range: -1 to 4294967295
Data Type: numeric
Default Value: 4294967295
Range: 1 to 4294967295
Data Type: numeric
Default Value: 1024
Range: 1 to 1048576
Data Type: numeric
Default Value: 8
Range: 1 to 1024
Data Type: numeric
Default Value: 8
Range: 1 to 256
Introduced:
Data Type: numeric
Default Value: 0
Range: 0-4294967295
Data Type: numeric
Default Value: 262144
Range 8192 to 2147483647
Dynamic: Yes
Data Type: boolean
Default Value: ON
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value: 16384
Range: 1024 to 1048576
Data Type: numeric
Default Value: 30
Range: 1 to 31536000
Data Type: numeric
Default Value: 10
Range: 1 to 4294967295
Data Type: numeric
Default Value: 60
Range: 1 upwards
Data Type: string
Default Value: (empty string)
Valid Values: See NEW Mode for the full list.
explain Give warnings for unusable keys for EXPLAIN.
all Enables all above options. This has to be given alone.
Command line: note-verbosity=value1[,value2...]
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: basic,explain
Valid Values: basic,explain,unusable_keys or all.
Introduced:
Data Type: boolean
Default Value: OFF
Deprecated:
Dynamic: Yes
Data Type: enumerated (>=)
Default Value: See alter_algorithm
Valid Values: See alter_algorithm for the full list.
Deprecated: (superceded by alter_algorithm)
Removed:
Data Type: string
Default Value: UTF8_IS_UTF8MB3 (>= ) (empty string) (<= )
Valid Values: See OLD Mode for the full list.
booleanDefault Value: OFF
Command line: --open-files-limit=count
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: Autosized (see description)
Range: 0 to 4294967295
Data Type: numeric
Default Value: 8
Range: 0 to 62
Introduced:
Data Type: numeric
Default Value: 0 (Disable)
Range: 0 to 4294967295
Introduced: , , , ,
Dynamic: Yes
Data Type: numeric
Default Value: 32000
Range: 0 to 18446744073709551615
Introduced:
Data Type: numeric
Default Value: 16000
Range: 0 to 4294967295
Introduced: , , , ,
2: tables using EQ_REF will be joined together as 'one entity' and the different combinations of these tables will not be considered (from )
Command line: --optimizer-prune-level[=#]
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 2 (>= ), 1 (<= )
Dynamic: Yes
Data Type: numeric
Default Value: 62
Range: 0 to 63
Data Type: numeric
Default Value: 100
Range: 10 upwards
Data Type: string
Valid Values:
condition_pushdown_for_derived={on|off}
condition_pushdown_for_subquery={on|off}
condition_pushdown_from_having={on|off}
cset_narrowing={on|off} - see (>= , , , and )
default - set all optimizations to their default values.
derived_merge={on|off} - see
derived_with_keys={on|off} - see
duplicateweedout={on|off}. From .
engine_condition_pushdown={on|off}. Deprecated in as engine condition pushdown is now automatically enabled for all engines that support it.
exists_to_in={on|off} - see
extended_keys={on|off} - see
firstmatch={on|off} - see
hash_join_cardinality={on|off} - see (>= , , )
index_condition_pushdown={on|off} - see
index_merge={on|off}
index_merge_intersection={on|off}
index_merge_sort_intersection={on|off} -
index_merge_sort_union={on|off}
index_merge_union={on|off}
in_to_exists={on|off} - see
join_cache_bka={on|off} - see
join_cache_hashed={on|off} - see
join_cache_incremental={on|off} - see
loosescan={on|off} - see
materialization={on|off} - and materialization.
mrr={on|off} - see
mrr_cost_based={on|off} - see
mrr_sort_keys={on|off} - see
not_null_range_scan={on|off} - see ( >= )
optimize_join_buffer_size={on|off} - see
orderby_uses_equalities={on|off} - if not set, the optimizer ignores equality propagation. See .
outer_join_with_cache={on|off} - see
partial_match_rowid_merge={on|off} - see
partial_match_table_scan={on|off} - see
rowid_filter={on|off} - see
sargable_casefold={on|off} (>= )
semijoin={on|off} - see
semijoin_with_cache={on|off} - see
split_materialized={on|off}
subquery_cache={on|off} - see .
table_elimination={on|off} - see
Data Type: boolean
Default Value: OFF
Introduced:
Data Type: enum
Default Value: enabled=off
Valid Values: enabled={on|off|default}
Data Type: numeric
Default Value: 1048576
Range: 1 to 18446744073709551615
3 Use selectivity of all range predicates estimated without histogram.
4 Use selectivity of all range predicates estimated with histogram.
5 Additionally use selectivity of certain non-range predicates calculated on record sample.
Command line: --optimizer-use-condition-selectivity=#
Scope: Global, Session
Dynamic: Yes
Data Type: numeric
Default Value: 4
Range: 1 to 5
Dynamic: No
Data Type: file name
Data Type: directory name
Default Value: BASEDIR/lib/plugin
Type: enum
Default Value: One less than the server maturity
Valid Values: unknown, experimental, alpha, beta, gamma, stable
Dynamic: No
Data Type: numeric
Default Value: 3306
Range: 0 to 65535
Data Type: numeric
Default Value: 32768
Range: 1024 to 1073741824
booleanDefault Value: OFF
Data Type: numeric
Default Value: 15
Range: 0 to 100
Data Type: numeric
Default Value: 5
Range: 0 to 4294967295
numericDefault Value: 10
Range: 0 to 4294967295
Data Type: string
Default Value: (empty)
Default Value: OFF
Data Type: BIGINT UNSIGNED
Default Value:
>= , , : 32768
<= , , : 16384
Range: 1024 to 4294967295
Block size: 1024
Data Type: numeric
Default Value: 1048576 (1MB)
Range: 0 to 4294967295
Data Type: numeric
Default Value: 4096 (4KB)
Range - 32 bit: 1024 to 4294967295
Range - 64 bit: 1024 to 18446744073709547520
Default Value: 1M (although frequently given a default value in some setups)
Valid Values: 0 upwards in units of 1024.
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Default Value: OFF
Valid Values: 0 or OFF, 1 or ON, 2 or DEMAND
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value:
>= , , : 32768
<= , , : 24576
Range: 1024 to 4294967295
Data Type: numeric
Default Value: Varies
Range: 0 to 18446744073709551615
Data Type: numeric
Default Value: 4096
Range - 32 bit: 4096 to 4294967295
Range - 64 bit: 4096 to 18446744073709547520
Data Type: numeric
Default Value: 131072
Range: 8192 to 2147479552
Command line: --read-only
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: OFF
Valid values: OFF, ON, NO_LOCK, NO_LOCK_NO_ADMIN
Description: When set to 1 (0 is default), no updates are permitted except from users with the SUPER privilege or, from , the READ ONLY ADMIN privilege, or replica servers updating from a primary. The read_only variable is useful for replica servers to ensure no updates are accidentally made outside of what are performed on the primary. Inserting rows to log tables, updates to temporary tables and OPTIMIZE TABLE or ANALYZE TABLE statements are excluded from this limitation. If read_only is set to 1, then the SET PASSWORD statement is limited only to users with the SUPER privilege (<= ) or READ ONLY ADMIN privilege (>= ). Attempting to set this variable to 1 will fail if the current session has table locks or transactions pending, while if other sessions hold table locks, the statement will wait until these locks are released before completing. While the attempt to set read_only is waiting, other requests for table locks or transactions will also wait until read_only has been set. See for more. From , the privilege will allow users granted that privilege to perform writes, even if the read_only variable is set. In earlier versions, and until , users with the can perform writes while this variable is set.
Command line: --read-only
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value: 262144
Range: 8200 to 2147483647
Dynamic: Yes
Data Type: string
Default Value: Empty
Introduced:
Data Type: boolean
Default Value: OFF
Introduced:
Data Type: numeric
Default Value: 8388608
Range: 0 to 2147483647
Data Type: boolean
Removed:
Dynamic: Yes
Data Type: boolean
Default Value: ON
Data Type: path name
Default Value: None
NO - historical behavior, anyone can modify session timestamp
Command line: --secure-timestamp=value
Scope: Global
Dynamic: No
Data Type: enum
Default Value: NO
Data Type: varchar
Default Value: None
Introduced: , , , , , , ,
Data Type: boolean
Default Value: ON
Data Type: boolean
Default Value: OFF
Data Type: string
Default Value:
= :
autocommit,character_set_client,character_set_connection,character_set_results,redirect_url,time_zone
<= : autocommit, character_set_client, character_set_connection, character_set_results, time_zone
Data Type: enum
Default Value: OFF
Valid Values: OFF, STATE, CHARACTERISTICS
Default Value: MYSQL
Data Type: Boolean
Default Value: OFF
Note that command line option name is the opposite of the variable name, and the value is the opposite too. --external-locking=1 means @@skip_external_locking=0, and vice versa.
Command line: --external-locking
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: 1 (for the variable, that is 0 for the command line option)
Data Type: boolean
Default Value: OFF
Introduced:
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Data Type: boolean
Default Value: 0
Data Type: boolean
Default Value: 0
Data Type: numeric
Default Value: 2
Data Type: boolean
Data Type: boolean
Default Value: 0
See also: See log_output to see how log files are written. If that variable is set to NONE, no logs will be written even if slow_query_log is set to 1.
Dynamic: Yes
Data Type: file name
Default Value: host_name-slow.log
Dynamic: No
Data Type: file name
Default Value: /tmp/mysql.sock (Unix), MySQL (Windows)
Data Type: number
Default Value: 2M (2097152) (some distributions increase the default)
Default Value: 0
Default Value: 1
Dynamic: Yes
Data Type: boolean
Default Value: 0
Removed:
Default Value: 0
Data Type: boolean
Default Value: OFF
Introduced:
Default Value: 0
Scope: Global, Session
Dynamic: Yes
Data Type: boolean
Default Value: 0
Removed:
Data Type: string
Default Value:
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Valid Values: See SQL Mode for the full list.
Data Type: boolean
Default Value: 1
Default Value: 1
Data Type: boolean
Default Value: OFF
Data Type: numeric
Default Value: 18446744073709551615
Default Value: OFF (0)
Data Type: boolean
Default Value: ON
Data Type: numeric
Default Value: 256
Range: 256 to 524288
Data Type: boolean
Default Value: ON
Data Type: boolean
Default Value: TRUE
Data Type: string
Data Type: numeric
Default Value: 400
Range: 400 to 2097152
Data Type: numeric
Default Value: 50
Range: 1 to 1073741824
Removed:
Data Type: numeric
Default Value: 2000
Range:
1 to 1048576 (1024K)
See Optimizing table_open_cache: Automatic Creation of New Table Open Cache Instances for more information.
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 8 (>= )
Range: 1 to 64
Data Type: numeric
Default Value: 0
Range: 0 to 2147483
Data Type: numeric
Default Value: 0
Range: 0 to 2147483
Data Type: numeric
Default Value: 0
Range: 0 to 2147483
Data Type: boolean
Default Value: 1
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 256 (adjusted if thread pool is active)
Range: 0 to 16384
Data Type: numeric
Default Value: 10
Range: 1 to 512
Deprecated:
Removed:
Data Type: numeric
Default Value:
299008
Range: 131072 to 18446744073709551615
Data Type: string
Default Value: SYSTEM
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Deprecated:
Removed:
DEFAULTData Type: numeric
Default Value: 18446744073709551615 (max unsigned integer, no limit)
Range: 1024 to 18446744073709551615
Dynamic: Yes
Data Type: numeric
Default Value: 16777216 (16MB)
Range:
1024 to 4294967295 (< )
0 to 4294967295 (>= )
Dynamic: No
Type: directory name/s
Default:
$TMPDIR (environment variable) if set
otherwise $TEMP if set and on Windows
otherwise $TMP if set and on Windows
otherwise, P_tmpdir ("/tmp") or C:\TEMP (unless overridden during buid time)
Type: numeric
Default Value: 8192
Range: 1024 to 134217728 (128M)
Block Size: 1024
Type: enumeration
Default Value: REPEATABLE-READ
Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
Introduced:
Type: numeric
Default Value: 4096
Range: 1024 to 134217728 (128M)
Block Size: 1024
Dynamic: Yes
Type: boolean
Default Value: OFF
Introduced:
Dynamic: Yes
Type: enumeration
Default Value: REPEATABLE-READ
Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
Deprecated:
Dynamic: Yes
Type: boolean
Default Value: OFF
Deprecated:
Default Value: 1
Dynamic: Yes
Type: boolean
Default Value: 1
preferably: Prefer the data from statistics tables, if it's not available there, use the data from the storage engine.complementary_for_queries: Same as complementary, but for queries only (to avoid needlessly collecting for ANALYZE TABLE).
preferably_for_queries: Same as preferably, but for queries only (to avoid needlessly collecting for ANALYZE TABLE).
Command line: --use-stat-tables=mode
Scope: Global, Session
Dynamic: Yes
Data Type: enum
Default Value: preferably_for_queries
Dynamic: No
Type: string
Type: numeric
Default Value: 28800
Range: (Windows): 1 to 2147483
Range: (Other): 1 to 31536000
K
kilobytes
1024
M
megabytes
10242
G
gigabytes
10243
T
terabytes
10244 (from )
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
SHOW VARIABLES;mariadbd --verbose --helpshell> ./mariadbd-safe --aria_group_commit="hard"aria_group_commit = "hard"SET GLOBAL aria_group_commit="hard";SELECT (55/23244*1000);
+-----------------+
| (55/23244*1000) |
+-----------------+
| 2.3662 |
+-----------------+SELECT (55/23244*1000);
+-----------------+
| (55/23244*1000) |
+-----------------+
| 2.4000 |
+-----------------+[Service]
TasksMax=infinityWEBINAR
MariaDB 101: Learning the Basics of MariaDB