MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The MySQL server maintains many system variables that configure
its operation. Each system variable has a default value. System
variables can be set at server startup using options on the
command line or in an option file. Most of them can be changed
dynamically at runtime using the
SET
statement, which enables you to modify operation of the server
without having to stop and restart it. You can also use system
variable values in expressions.
At runtime, setting a global system variable value requires the
SUPER
privilege. Setting a session
system variable value normally requires no special privileges and
can be done by any user, although there are exceptions. For more
information, see Section 5.1.8.1, “System Variable Privileges”
There are several ways to see the names and values of system variables:
To see the values that a server uses based on its compiled-in defaults and any option files that it reads, use this command:
mysqld --verbose --help
To see the values that a server uses based only on its compiled-in defaults, ignoring the settings in any option files, use this command:
mysqld --no-defaults --verbose --help
To see the current values used by a running server, use the
SHOW VARIABLES
statement.
This section provides a description of each system variable. For a system variable summary table, see Section 5.1.4, “Server System Variable Reference”. For more information about manipulation of system variables, see Section 5.1.8, “Using System Variables”.
For additional system variable information, see these sections:
Section 5.1.8, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.8.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
Information on tuning system variables can be found in Section 5.1.1, “Configuring the Server”.
Section 14.14, “InnoDB Startup Options and System Variables”, lists
InnoDB
system variables.
Section 18.3.3.8.2, “NDB Cluster System Variables”, lists system variables which are specific to NDB Cluster.
For information on server system variables specific to replication, see Section 17.1.4, “Replication and Binary Logging Options and Variables”.
Some of the following variable descriptions refer to
“enabling” or “disabling” a variable.
These variables can be enabled with the
SET
statement by setting them to ON
or
1
, or disabled by setting them to
OFF
or 0
. Boolean
variables can be set at startup to the values
ON
, TRUE
,
OFF
, and FALSE
(not
case-sensitive), as well as 1
and
0
. See Section 4.2.2.4, “Program Option Modifiers”.
Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server adjusts a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server sets the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
Some system variables take file name values. Unless otherwise
specified, the default file location is the data directory if the
value is a relative path name. To specify the location explicitly,
use an absolute path name. Suppose that the data directory is
/var/mysql/data
. If a file-valued variable is
given as a relative path name, it is located under
/var/mysql/data
. If the value is an absolute
path name, its location is as given by the path name.
authentication_windows_log_level
Command-Line Format | --authentication-windows-log-level=# |
---|---|
System Variable | authentication_windows_log_level |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 2 |
Minimum Value | 0 |
Maximum Value | 4 |
This variable is available only if the
authentication_windows
Windows
authentication plugin is enabled and debugging code is
enabled. See
Section 6.4.1.7, “Windows Pluggable Authentication”.
This variable sets the logging level for the Windows authentication plugin. The following table shows the permitted values.
Value | Description |
---|---|
0 | No logging |
1 | Log only error messages |
2 | Log level 1 messages and warning messages |
3 | Log level 2 messages and information notes |
4 | Log level 3 messages and debug messages |
authentication_windows_use_principal_name
Command-Line Format | --authentication-windows-use-principal-name[={OFF|ON}] |
---|---|
System Variable | authentication_windows_use_principal_name |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | ON |
This variable is available only if the
authentication_windows
Windows
authentication plugin is enabled. See
Section 6.4.1.7, “Windows Pluggable Authentication”.
A client that authenticates using the
InitSecurityContext()
function should
provide a string identifying the service to which it connects
(targetName
). MySQL uses the
principal name (UPN) of the account under which the server is
running. The UPN has the form
and need not be registered anywhere to be used. This UPN is
sent by the server at the beginning of authentication
handshake.
user_id
@computer_name
This variable controls whether the server sends the UPN in the
initial challenge. By default, the variable is enabled. For
security reasons, it can be disabled to avoid sending the
server's account name to a client as cleartext. If the
variable is disabled, the server always sends a
0x00
byte in the first challenge, the
client does not specify targetName
,
and as a result, NTLM authentication is used.
If the server fails to obtain its UPN (which happens primarily in environments that do not support Kerberos authentication), the UPN is not sent by the server and NTLM authentication is used.
Command-Line Format | --autocommit[={OFF|ON}] |
---|---|
System Variable | autocommit |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
The autocommit mode. If set to 1, all changes to a table take
effect immediately. If set to 0, you must use
COMMIT
to accept a transaction
or ROLLBACK
to cancel it. If autocommit
is 0 and you change it to 1, MySQL performs an automatic
COMMIT
of any open transaction.
Another way to begin a transaction is to use a
START
TRANSACTION
or
BEGIN
statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.
By default, client connections begin with
autocommit
set to 1. To cause
clients to begin with a default of 0, set the global
autocommit
value by starting
the server with the
--autocommit=0
option. To set
the variable using an option file, include these lines:
[mysqld] autocommit=0
Command-Line Format | --automatic-sp-privileges[={OFF|ON}] |
---|---|
System Variable | automatic_sp_privileges |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
When this variable has a value of 1 (the default), the server
automatically grants the
EXECUTE
and
ALTER ROUTINE
privileges to the
creator of a stored routine, if the user cannot already
execute and alter or drop the routine. (The
ALTER ROUTINE
privilege is
required to drop the routine.) The server also automatically
drops those privileges from the creator when the routine is
dropped. If
automatic_sp_privileges
is 0,
the server does not automatically add or drop these
privileges.
The creator of a routine is the account used to execute the
CREATE
statement for it. This might not be
the same as the account named as the
DEFINER
in the routine definition.
If you start mysqld with
--skip-new
,
automatic_sp_privileges
is
set to OFF
.
See also Section 20.2.2, “Stored Routines and MySQL Privileges”.
Command-Line Format | --avoid-temporal-upgrade[={OFF|ON}] |
---|---|
Introduced | 5.6.24 |
Deprecated | 5.6.24 |
System Variable | avoid_temporal_upgrade |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
This variable controls whether ALTER
TABLE
implicitly upgrades temporal columns found to
be in pre-5.6.4 format (TIME
,
DATETIME
, and
TIMESTAMP
columns without
support for fractional seconds precision). Upgrading such
columns requires a table rebuild, which prevents any use of
fast alterations that might otherwise apply to the operation
to be performed.
This variable is disabled by default. Enabling it causes
ALTER TABLE
not to rebuild
temporal columns and thereby be able to take advantage of
possible fast alterations.
This variable was added in MySQL 5.6.24. It is deprecated; expect it to be removed in a future MySQL release.
Command-Line Format | --back-log=# |
---|---|
System Variable | back_log |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
Minimum Value | 1 |
Maximum Value | 65535 |
The number of outstanding connection requests MySQL can have.
This comes into play when the main MySQL thread gets very many
connection requests in a very short time. It then takes some
time (although very little) for the main thread to check the
connection and start a new thread. The
back_log
value indicates how
many requests can be stacked during this short time before
MySQL momentarily stops answering new requests. You need to
increase this only if you expect a large number of connections
in a short period of time.
In other words, this value is the size of the listen queue for
incoming TCP/IP connections. Your operating system has its own
limit on the size of this queue. The manual page for the Unix
listen()
system call should have more
details. Check your OS documentation for the maximum value for
this variable. back_log
cannot be set higher than your operating system limit.
The default value is based on the following formula, capped to a limit of 900:
50 + (max_connections / 5)
Command-Line Format | --basedir=dir_name |
---|---|
System Variable | basedir |
Scope | Global |
Dynamic | No |
Type | Directory name |
Default Value | configuration-dependent default |
The path to the MySQL installation base directory.
Command-Line Format | --big-tables[={OFF|ON}] |
---|---|
System Variable | big_tables |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If enabled, the server stores all temporary tables on disk
rather than in memory. This prevents most The table
errors
for tbl_name
is fullSELECT
operations that
require a large temporary table, but also slows down queries
for which in-memory tables would suffice.
The default value for new connections is
OFF
(use in-memory temporary tables).
Normally, it should never be necessary to enable this variable
because the server is able to handle large result sets
automatically by using memory for small temporary tables and
switching to disk-based tables as required.
Command-Line Format | --bind-address=addr |
---|---|
System Variable | bind_address |
Scope | Global |
Dynamic | No |
Type | String |
Default Value | * |
The MySQL server listens on a single network socket for TCP/IP
connections. This socket is bound to a single address, but it
is possible for an address to map onto multiple network
interfaces. To specify an address, set
bind_address=
at server startup, where addr
addr
is an
IPv4 or IPv6 address or a host name. If
addr
is a host name, the server
resolves the name to an IP address and binds to that address.
If a host name resolves to multiple IP addresses, the server
uses the first IPv4 address if there are any, or the first
IPv6 address otherwise.
The server treats different types of addresses as follows:
If the address is *
, the server accepts
TCP/IP connections on all server host IPv4 interfaces,
and, if the server host supports IPv6, on all IPv6
interfaces. Use this address to permit both IPv4 and IPv6
connections on all server interfaces. This value is the
default.
If the address is 0.0.0.0
, the server
accepts TCP/IP connections on all server host IPv4
interfaces.
If the address is ::
, the server
accepts TCP/IP connections on all server host IPv4 and
IPv6 interfaces.
If the address is an IPv4-mapped address, the server
accepts TCP/IP connections for that address, in either
IPv4 or IPv6 format. For example, if the server is bound
to ::ffff:127.0.0.1
, clients can
connect using --host=127.0.0.1
or
--host=::ffff:127.0.0.1
.
If the address is a “regular” IPv4 or IPv6
address (such as 127.0.0.1
or
::1
), the server accepts TCP/IP
connections only for that IPv4 or IPv6 address.
If binding to the address fails, the server produces an error and does not start.
If you intend to bind the server to a specific address, be
sure that the mysql.user
system table
contains an account with administrative privileges that you
can use to connect to that address. Otherwise, you are not
able to shut down the server. For example, if you bind the
server to *
, you can connect to it using
all existing accounts. But if you bind the server to
::1
, it accepts connections only on that
address. In that case, first make sure that the
'root'@'::1'
account is present in the
mysql.user
table so you can still connect
to the server to shut it down.
Command-Line Format | --block-encryption-mode=# |
---|---|
Introduced | 5.6.17 |
System Variable | block_encryption_mode |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | aes-128-ecb |
This variable controls the block encryption mode for
block-based algorithms such as AES. It affects encryption for
AES_ENCRYPT()
and
AES_DECRYPT()
.
block_encryption_mode
takes a
value in
aes-
format, where keylen
-mode
keylen
is the key
length in bits and mode
is the
encryption mode. The value is not case-sensitive. Permitted
keylen
values are 128, 192, and
256. Permitted encryption modes depend on whether MySQL was
compiled using OpenSSL or yaSSL:
For OpenSSL, permitted mode
values are: ECB
,
CBC
, CFB1
,
CFB8
, CFB128
,
OFB
For yaSSL, permitted mode
values are: ECB
, CBC
For example, this statement causes the AES encryption functions to use a key length of 256 bits and the CBC mode:
SET block_encryption_mode = 'aes-256-cbc';
An error occurs for attempts to set
block_encryption_mode
to a
value containing an unsupported key length or a mode that the
SSL library does not support.
This variable was added in MySQL 5.6.17.
Command-Line Format | --bulk-insert-buffer-size=# |
---|---|
System Variable | bulk_insert_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 8388608 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
MyISAM
uses a special tree-like cache to
make bulk inserts faster for
INSERT ...
SELECT
, INSERT ... VALUES (...), (...),
...
, and LOAD DATA
when adding data to nonempty tables. This variable limits the
size of the cache tree in bytes per thread. Setting it to 0
disables this optimization. The default value is 8MB.
System Variable | character_set_client |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | utf8 |
The character set for statements that arrive from the client.
The session value of this variable is set using the character
set requested by the client when the client connects to the
server. (Many clients support a
--default-character-set
option to enable this
character set to be specified explicitly. See also
Section 10.4, “Connection Character Sets and Collations”.) The global value of the
variable is used to set the session value in cases when the
client-requested value is unknown or not available, or the
server is configured to ignore client requests:
The client requests a character set not known to the
server. For example, a Japanese-enabled client requests
sjis
when connecting to a server not
configured with sjis
support.
The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
mysqld was started with the
--skip-character-set-client-handshake
option, which causes it to ignore client character set
configuration. This reproduces MySQL 4.0 behavior and is
useful should you wish to upgrade the server without
upgrading all the clients.
Some character sets cannot be used as the client character
set. Attempting to use them as the
character_set_client
value
produces an error. See
Impermissible Client Character Sets.
System Variable | character_set_connection |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | utf8 |
The character set used for literals specified without a character set introducer and for number-to-string conversion. For information about introducers, see Section 10.3.8, “Character Set Introducers”.
System Variable | character_set_database |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | latin1 |
Footnote | This option is dynamic, but should be set only by server. You should not set this variable manually. |
The character set used by the default database. The server
sets this variable whenever the default database changes. If
there is no default database, the variable has the same value
as character_set_server
.
Command-Line Format | --character-set-filesystem=name |
---|---|
System Variable | character_set_filesystem |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | binary |
The file system character set. This variable is used to
interpret string literals that refer to file names, such as in
the LOAD DATA
and
SELECT ... INTO
OUTFILE
statements and the
LOAD_FILE()
function. Such file
names are converted from
character_set_client
to
character_set_filesystem
before the file opening attempt occurs. The default value is
binary
, which means that no conversion
occurs. For systems on which multibyte file names are
permitted, a different value may be more appropriate. For
example, if the system represents file names using UTF-8, set
character_set_filesystem
to
'utf8mb4'
.
System Variable | character_set_results |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | utf8 |
The character set used for returning query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.
Command-Line Format | --character-set-server=name |
---|---|
System Variable | character_set_server |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | latin1 |
The servers default character set. See
Section 10.15, “Character Set Configuration”. If you set this
variable, you should also set
collation_server
to specify
the collation for the character set.
System Variable | character_set_system |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
Default Value | utf8 |
The character set used by the server for storing identifiers.
The value is always utf8
.
Command-Line Format | --character-sets-dir=dir_name |
---|---|
System Variable | character_sets_dir |
Scope | Global |
Dynamic | No |
Type | Directory name |
The directory where character sets are installed. See Section 10.15, “Character Set Configuration”.
System Variable | collation_connection |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
The collation of the connection character set.
collation_connection
is
important for comparisons of literal strings. For comparisons
of strings with column values,
collation_connection
does not
matter because columns have their own collation, which has a
higher collation precedence (see
Section 10.8.4, “Collation Coercibility in Expressions”).
System Variable | collation_database |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | latin1_swedish_ci |
Footnote | This option is dynamic, but should be set only by server. You should not set this variable manually. |
The collation used by the default database. The server sets
this variable whenever the default database changes. If there
is no default database, the variable has the same value as
collation_server
.
Command-Line Format | --collation-server=name |
---|---|
System Variable | collation_server |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | latin1_swedish_ci |
The server's default collation. See Section 10.15, “Character Set Configuration”.
Command-Line Format | --completion-type=# |
---|---|
System Variable | completion_type |
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | NO_CHAIN |
Valid Values |
|
The transaction completion type. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.
Value | Description |
---|---|
NO_CHAIN (or 0) |
COMMIT and
ROLLBACK
are unaffected. This is the default value. |
CHAIN (or 1) |
COMMIT and
ROLLBACK
are equivalent to COMMIT AND CHAIN
and ROLLBACK AND CHAIN , respectively.
(A new transaction starts immediately with the same
isolation level as the just-terminated transaction.) |
RELEASE (or 2) |
COMMIT and
ROLLBACK
are equivalent to COMMIT RELEASE and
ROLLBACK RELEASE , respectively. (The
server disconnects after terminating the transaction.) |
completion_type
affects
transactions that begin with
START
TRANSACTION
or
BEGIN
and
end with COMMIT
or
ROLLBACK
. It
does not apply to implicit commits resulting from execution of
the statements listed in Section 13.3.3, “Statements That Cause an Implicit Commit”. It
also does not apply for
XA
COMMIT
,
XA
ROLLBACK
, or when
autocommit=1
.
Command-Line Format | --concurrent-insert[=value] |
---|---|
System Variable | concurrent_insert |
Scope | Global |
Dynamic | Yes |
Type | Enumeration |
Default Value | AUTO |
Valid Values |
|
If AUTO
(the default), MySQL permits
INSERT
and
SELECT
statements to run
concurrently for MyISAM
tables that have no
free blocks in the middle of the data file.
This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.
Value | Description |
---|---|
NEVER (or 0) |
Disables concurrent inserts |
AUTO (or 1) |
(Default) Enables concurrent insert for MyISAM tables
that do not have holes |
ALWAYS (or 2) |
Enables concurrent inserts for all MyISAM tables,
even those that have holes. For a table with a hole, new
rows are inserted at the end of the table if it is in
use by another thread. Otherwise, MySQL acquires a
normal write lock and inserts the row into the hole. |
If you start mysqld with
--skip-new
,
concurrent_insert
is set to
NEVER
.
See also Section 8.11.3, “Concurrent Inserts”.
Command-Line Format | --connect-timeout=# |
---|---|
System Variable | connect_timeout |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 10 |
Minimum Value | 2 |
Maximum Value | 31536000 |
The number of seconds that the mysqld
server waits for a connect packet before responding with
Bad handshake
. The default value is 10
seconds.
Increasing the
connect_timeout
value might
help if clients frequently encounter errors of the form
Lost connection to MySQL server at
'
.
XXX
', system error:
errno
System Variable | core_file |
---|---|
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | OFF |
Whether to write a core file if the server unexpectedly exits.
This variable is set by the
--core-file
option.
Command-Line Format | --datadir=dir_name |
---|---|
System Variable | datadir |
Scope | Global |
Dynamic | No |
Type | Directory name |
The path to the MySQL server data directory. Relative paths
are resolved with respect to the current directory. If the
server is likely to be started automatically (that is, in
contexts for which you cannot assume what the current
directory is), it is best to specify the
datadir
value as an absolute
path.
This variable is unused. It is deprecated; expect it to be removed in a future MySQL release.
This variable is unused. It is deprecated; expect it to be removed in a future MySQL release.
Command-Line Format | --debug[=debug_options] |
---|---|
System Variable | debug |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value (Windows) | d:t:i:O,\mysqld.trace |
Default Value (Unix) | d:t:i:o,/tmp/mysqld.trace |
This variable indicates the current debugging settings. It is
available only for servers built with debugging support. The
initial value comes from the value of instances of the
--debug
option given at server
startup. The global and session values may be set at runtime.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.
Assigning a value that begins with +
or
-
cause the value to added to or subtracted
from the current value:
mysql>SET debug = 'T';
mysql>SELECT @@debug;
+---------+ | @@debug | +---------+ | T | +---------+ mysql>SET debug = '+P';
mysql>SELECT @@debug;
+---------+ | @@debug | +---------+ | P:T | +---------+ mysql>SET debug = '-P';
mysql>SELECT @@debug;
+---------+ | @@debug | +---------+ | T | +---------+
For more information, see Section 5.8.3, “The DBUG Package”.
System Variable | debug_sync |
---|---|
Scope | Session |
Dynamic | Yes |
Type | String |
This variable is the user interface to the Debug Sync
facility. Use of Debug Sync requires that MySQL be configured
with the -DENABLE_DEBUG_SYNC=1
CMake option (see
Section 2.9.7, “MySQL Source-Configuration Options”). If Debug Sync
is not compiled in, this system variable is not available.
The global variable value is read only and indicates whether
the facility is enabled. By default, Debug Sync is disabled
and the value of debug_sync
is OFF
. If the server is started with
--debug-sync-timeout=
,
where N
N
is a timeout value greater
than 0, Debug Sync is enabled and the value of
debug_sync
is ON -
current signal
followed by the signal name. Also,
N
becomes the default timeout for
individual synchronization points.
The session value can be read by any user and has the same value as the global variable. The session value can be set to control synchronization points.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
Command-Line Format | --default-storage-engine=name |
---|---|
System Variable | default_storage_engine |
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | InnoDB |
The default storage engine for tables. See
Chapter 15, Alternative Storage Engines. This variable sets the
storage engine for permanent tables only. To set the storage
engine for TEMPORARY
tables, set the
default_tmp_storage_engine
system variable.
To see which storage engines are available and enabled, use
the SHOW ENGINES
statement or
query the INFORMATION_SCHEMA
ENGINES
table.
default_storage_engine
should
be used in preference to
storage_engine
, which is
deprecated.
If you disable the default storage engine at server startup,
you must set the default engine for both permanent and
TEMPORARY
tables to a different engine or
the server cannot start.
Command-Line Format | --default-tmp-storage-engine=name |
---|---|
System Variable | default_tmp_storage_engine |
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | InnoDB |
The default storage engine for TEMPORARY
tables (created with
CREATE TEMPORARY
TABLE
). To set the storage engine for permanent
tables, set the
default_storage_engine
system
variable.
If you disable the default storage engine at server startup,
you must set the default engine for both permanent and
TEMPORARY
tables to a different engine or
the server cannot start.
Command-Line Format | --default-week-format=# |
---|---|
System Variable | default_week_format |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 7 |
The default mode value to use for the
WEEK()
function. See
Section 12.7, “Date and Time Functions”.
Command-Line Format | --delay-key-write[={OFF|ON|ALL}] |
---|---|
System Variable | delay_key_write |
Scope | Global |
Dynamic | Yes |
Type | Enumeration |
Default Value | ON |
Valid Values |
|
This variable specifies how to use delayed key writes. It
applies only to MyISAM
tables. Delayed key
writing causes key buffers not to be flushed between writes.
See also Section 15.2.1, “MyISAM Startup Options”.
This variable can have one of the following values to affect
handling of the DELAY_KEY_WRITE
table
option that can be used in CREATE
TABLE
statements.
Option | Description |
---|---|
OFF |
DELAY_KEY_WRITE is ignored. |
ON |
MySQL honors any DELAY_KEY_WRITE option specified in
CREATE TABLE statements.
This is the default value. |
ALL |
All new opened tables are treated as if they were created with the
DELAY_KEY_WRITE option enabled. |
If you set this variable to ALL
, you
should not use MyISAM
tables from within
another program (such as another MySQL server or
myisamchk) when the tables are in use.
Doing so leads to index corruption.
If DELAY_KEY_WRITE
is enabled for a table,
the key buffer is not flushed for the table on every index
update, but only when the table is closed. This speeds up
writes on keys a lot, but if you use this feature, you should
add automatic checking of all MyISAM
tables
by starting the server with the
myisam_recover_options
system
variable set (for example,
myisam_recover_options='BACKUP,FORCE'
).
See Section 5.1.7, “Server System Variables”, and
Section 15.2.1, “MyISAM Startup Options”.
If you start mysqld with
--skip-new
,
delay_key_write
is set to
OFF
.
If you enable external locking with
--external-locking
, there is
no protection against index corruption for tables that use
delayed key writes.
Command-Line Format | --delayed-insert-limit=# |
---|---|
Deprecated | Yes |
System Variable | delayed_insert_limit |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 100 |
Minimum Value | 1 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
After inserting
delayed_insert_limit
delayed
rows into a nontransactional table, the
INSERT DELAYED
handler thread
checks whether there are any
SELECT
statements pending. If
so, it permits them to execute before continuing to insert
delayed rows.
This system variable is deprecated (because
DELAYED
inserts are deprecated); expect it
to be removed in a future release.
Command-Line Format | --delayed-insert-timeout=# |
---|---|
Deprecated | Yes |
System Variable | delayed_insert_timeout |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 300 |
How many seconds an INSERT
DELAYED
handler thread should wait for
INSERT
statements before
terminating.
This system variable is deprecated (because
DELAYED
inserts are deprecated); expect it
to be removed in a future release.
Command-Line Format | --delayed-queue-size=# |
---|---|
Deprecated | Yes |
System Variable | delayed_queue_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1000 |
Minimum Value | 1 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
This is a per-table limit on the number of rows to queue when
handling INSERT DELAYED
statements for nontransactional tables. If the queue becomes
full, any client that issues an INSERT
DELAYED
statement waits until there is room in the
queue again.
This system variable is deprecated (because
DELAYED
inserts are deprecated); expect it
to be removed in a future release.
disconnect_on_expired_password
Command-Line Format | --disconnect-on-expired-password[={OFF|ON}] |
---|---|
System Variable | disconnect_on_expired_password |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | ON |
This variable controls how the server handles clients with expired passwords:
If the client indicates that it can handle expired
passwords, the value of
disconnect_on_expired_password
is irrelevant. The server permits the client to connect
but puts it in sandbox mode.
If the client does not indicate that it can handle expired
passwords, the server handles the client according to the
value of
disconnect_on_expired_password
:
If
disconnect_on_expired_password
:
is enabled, the server disconnects the client.
If
disconnect_on_expired_password
:
is disabled, the server permits the client to connect
but puts it in sandbox mode.
For more information about the interaction of client and server settings relating to expired-password handling, see Section 6.2.10, “Server Handling of Expired Passwords”.
Command-Line Format | --div-precision-increment=# |
---|---|
System Variable | div_precision_increment |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 4 |
Minimum Value | 0 |
Maximum Value | 30 |
This variable indicates the number of digits by which to
increase the scale of the result of division operations
performed with the
/
operator.
The default value is 4. The minimum and maximum values are 0
and 30, respectively. The following example illustrates the
effect of increasing the default value.
mysql>SELECT 1/7;
+--------+ | 1/7 | +--------+ | 0.1429 | +--------+ mysql>SET div_precision_increment = 12;
mysql>SELECT 1/7;
+----------------+ | 1/7 | +----------------+ | 0.142857142857 | +----------------+
Command-Line Format | --end-markers-in-json[={OFF|ON}] |
---|---|
System Variable | end_markers_in_json |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Whether optimizer JSON output should add end markers. See MySQL Internals: The end_markers_in_json System Variable.
Command-Line Format | --eq-range-index-dive-limit=# |
---|---|
System Variable | eq_range_index_dive_limit |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 10 |
Minimum Value | 0 |
Maximum Value | 4294967295 |
This variable indicates the number of equality ranges in an
equality comparison condition when the optimizer should switch
from using index dives to index statistics in estimating the
number of qualifying rows. It applies to evaluation of
expressions that have either of these equivalent forms, where
the optimizer uses a nonunique index to look up
col_name
values:
col_name
IN(val1
, ...,valN
)col_name
=val1
OR ... ORcol_name
=valN
In both cases, the expression contains
N
equality ranges. The optimizer
can make row estimates using index dives or index statistics.
If eq_range_index_dive_limit
is greater than 0, the optimizer uses existing index
statistics instead of index dives if there are
eq_range_index_dive_limit
or
more equality ranges. Thus, to permit use of index dives for
up to N
equality ranges, set
eq_range_index_dive_limit
to
N
+ 1. To disable use of index
statistics and always use index dives regardless of
N
, set
eq_range_index_dive_limit
to
0.
For more information, see Equality Range Optimization of Many-Valued Comparisons.
To update table index statistics for best estimates, use
ANALYZE TABLE
.
The number of errors that resulted from the last statement that generated messages. This variable is read only. See Section 13.7.5.18, “SHOW ERRORS Statement”.
Command-Line Format | --event-scheduler[=value] |
---|---|
System Variable | event_scheduler |
Scope | Global |
Dynamic | Yes |
Type | Enumeration |
Default Value | OFF |
Valid Values |
|
This variable enables or disables, and starts or stops, the
Event Scheduler. The possible status values are
ON
, OFF
, and
DISABLED
, with the default being
OFF
. Turning the Event Scheduler
OFF
is not the same as disabling the Event
Scheduler, which requires setting the status to
DISABLED
. This variable and its effects on
the Event Scheduler's operation are discussed in greater
detail in Section 20.4.2, “Event Scheduler Configuration”
explicit_defaults_for_timestamp
Command-Line Format | --explicit-defaults-for-timestamp[={OFF|ON}] |
---|---|
Deprecated | Yes |
System Variable | explicit_defaults_for_timestamp |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
This system variable determines whether the server enables
certain nonstandard behaviors for default values and
NULL
-value handling in
TIMESTAMP
columns. By default,
explicit_defaults_for_timestamp
is disabled, which enables the nonstandard behaviors.
If
explicit_defaults_for_timestamp
is disabled, the server enables the nonstandard behaviors and
handles TIMESTAMP
columns as
follows:
TIMESTAMP
columns not
explicitly declared with the NULL
attribute are automatically declared with the NOT
NULL
attribute. Assigning such a column a value
of NULL
is permitted and sets the
column to the current timestamp.
The first TIMESTAMP
column
in a table, if not explicitly declared with the
NULL
attribute or an explicit
DEFAULT
or ON UPDATE
attribute, is automatically declared with the
DEFAULT CURRENT_TIMESTAMP
and
ON UPDATE CURRENT_TIMESTAMP
attributes.
TIMESTAMP
columns following
the first one, if not explicitly declared with the
NULL
attribute or an explicit
DEFAULT
attribute, are automatically
declared as DEFAULT '0000-00-00
00:00:00'
(the “zero” timestamp).
For inserted rows that specify no explicit value for such
a column, the column is assigned '0000-00-00
00:00:00'
and no warning occurs.
Depending on whether strict SQL mode or the
NO_ZERO_DATE
SQL mode is
enabled, a default value of '0000-00-00
00:00:00'
may be invalid. Be aware that the
TRADITIONAL
SQL mode
includes strict mode and
NO_ZERO_DATE
. See
Section 5.1.10, “Server SQL Modes”.
In MySQL 5.6, the nonstandard behaviors just described are deprecated; expect them to be removed in a future MySQL release.
If
explicit_defaults_for_timestamp
is enabled, the server disables the nonstandard behaviors and
handles TIMESTAMP
columns as
follows:
It is not possible to assign a
TIMESTAMP
column a value of
NULL
to set it to the current
timestamp. To assign the current timestamp, set the column
to CURRENT_TIMESTAMP
or a
synonym such as NOW()
.
TIMESTAMP
columns not
explicitly declared with the NOT NULL
attribute are automatically declared with the
NULL
attribute and permit
NULL
values. Assigning such a column a
value of NULL
sets it to
NULL
, not the current timestamp.
TIMESTAMP
columns declared
with the NOT NULL
attribute do not
permit NULL
values. For inserts that
specify NULL
for such a column, the
result is either an error for a single-row insert or if
strict SQL mode is enabled, or '0000-00-00
00:00:00'
is inserted for multiple-row inserts
with strict SQL mode disabled. In no case does assigning
the column a value of NULL
set it to
the current timestamp.
TIMESTAMP
columns
explicitly declared with the NOT NULL
attribute and without an explicit
DEFAULT
attribute are treated as having
no default value. For inserted rows that specify no
explicit value for such a column, the result depends on
the SQL mode. If strict SQL mode is enabled, an error
occurs. If strict SQL mode is not enabled, the column is
declared with the implicit default of '0000-00-00
00:00:00'
and a warning occurs. This is similar
to how MySQL treats other temporal types such as
DATETIME
.
No TIMESTAMP
column is
automatically declared with the DEFAULT
CURRENT_TIMESTAMP
or ON UPDATE
CURRENT_TIMESTAMP
attributes. Those attributes
must be explicitly specified.
The first TIMESTAMP
column
in a table is not handled differently from
TIMESTAMP
columns following
the first one.
If
explicit_defaults_for_timestamp
is disabled at server startup, this warning appears in the
error log:
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
As indicated by the warning, to disable the deprecated
nonstandard behaviors, enable the
explicit_defaults_for_timestamp
system variable at server startup.
explicit_defaults_for_timestamp
is itself deprecated because its only purpose is to permit
control over deprecated
TIMESTAMP
behaviors that are
to be removed in a future MySQL release. When removal of
those behaviors occurs,
explicit_defaults_for_timestamp
has no purpose; expect it to be removed as well.
For additional information, see Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
System Variable | external_user |
---|---|
Scope | Session |
Dynamic | No |
Type | String |
The external user name used during the authentication process,
as set by the plugin used to authenticate the client. With
native (built-in) MySQL authentication, or if the plugin does
not set the value, this variable is NULL
.
See Section 6.2.12, “Proxy Users”.
Command-Line Format | --flush[={OFF|ON}] |
---|---|
System Variable | flush |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If ON
, the server flushes (synchronizes)
all changes to disk after each SQL statement. Normally, MySQL
does a write of all changes to disk only after each SQL
statement and lets the operating system handle the
synchronizing to disk. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”. This
variable is set to ON
if you start
mysqld with the
--flush
option.
If flush
is enabled, the
value of flush_time
does
not matter and changes to
flush_time
have no effect
on flush behavior.
Command-Line Format | --flush-time=# |
---|---|
System Variable | flush_time |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
If this is set to a nonzero value, all tables are closed every
flush_time
seconds to free up
resources and synchronize unflushed data to disk. This option
is best used only on systems with minimal resources. The
default is 0.
If flush
is enabled, the
value of flush_time
does
not matter and changes to
flush_time
have no effect
on flush behavior.
System Variable | foreign_key_checks |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If set to 1 (the default), foreign key constraints are
checked. If set to 0, foreign key constraints are ignored,
with a couple of exceptions. When re-creating a table that was
dropped, an error is returned if the table definition does not
conform to the foreign key constraints referencing the table.
Likewise, an ALTER TABLE
operation returns an error if a foreign key definition is
incorrectly formed. For more information, see
Section 13.1.17.5, “FOREIGN KEY Constraints”.
Beginning with MySQL NDB Cluster 7.3.2, setting this variable
has the same effect on NDB
tables
as it does for InnoDB
tables—previously, the setting was ignored and all such
checks were enforced (Bug #14095855). Typically you leave this
setting enabled during normal operation, to enforce
referential
integrity.
Disabling foreign key checking can be useful for reloading such tables in an order different from that required by their parent/child relationships. See Section 13.1.17.5, “FOREIGN KEY Constraints”.
Setting foreign_key_checks
to 0 also
affects data definition statements:
DROP
SCHEMA
drops a schema even if it contains tables
that have foreign keys that are referred to by tables outside
the schema, and DROP TABLE
drops tables that have foreign keys that are referred to by
other tables.
Setting foreign_key_checks
to 1 does not
trigger a scan of the existing table data. Therefore, rows
added to the table while
foreign_key_checks=0
are
not verified for consistency.
With foreign_key_checks=0
,
dropping an index required by a foreign key constraint
places the table in an inconsistent state and causes the
foreign key check that occurs at table load to fail. To
avoid this problem, remove the foreign key constraint before
dropping the index (Bug #70260).
Command-Line Format | --ft-boolean-syntax=name |
---|---|
System Variable | ft_boolean_syntax |
Scope | Global |
Dynamic | Yes |
Type | String |
Default Value | + -><()~*:""&| |
The list of operators supported by boolean full-text searches
performed using IN BOOLEAN MODE
. See
Section 12.10.2, “Boolean Full-Text Searches”.
The default variable value is
'+ -><()~*:""&|'
. The rules
for changing the value are as follows:
Operator function is determined by position within the string.
The replacement value must be 14 characters.
Each character must be an ASCII nonalphanumeric character.
Either the first or second character must be a space.
No duplicates are permitted except the phrase quoting operators in positions 11 and 12. These two characters are not required to be the same, but they are the only two that may be.
Positions 10, 13, and 14 (which by default are set to
:
, &
, and
|
) are reserved for future extensions.
Command-Line Format | --ft-max-word-len=# |
---|---|
System Variable | ft_max_word_len |
Scope | Global |
Dynamic | No |
Type | Integer |
Minimum Value | 10 |
The maximum length of the word to be included in a
MyISAM
FULLTEXT
index.
FULLTEXT
indexes on
MyISAM
tables must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name
QUICK
Command-Line Format | --ft-min-word-len=# |
---|---|
System Variable | ft_min_word_len |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 4 |
Minimum Value | 1 |
The minimum length of the word to be included in a
MyISAM
FULLTEXT
index.
FULLTEXT
indexes on
MyISAM
tables must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name
QUICK
Command-Line Format | --ft-query-expansion-limit=# |
---|---|
System Variable | ft_query_expansion_limit |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 20 |
Minimum Value | 0 |
Maximum Value | 1000 |
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION
.
Command-Line Format | --ft-stopword-file=file_name |
---|---|
System Variable | ft_stopword_file |
Scope | Global |
Dynamic | No |
Type | File name |
The file from which to read the list of stopwords for
full-text searches on MyISAM
tables. The
server looks for the file in the data directory unless an
absolute path name is given to specify a different directory.
All the words from the file are used; comments are
not honored. By default, a built-in list
of stopwords is used (as defined in the
storage/myisam/ft_static.c
file). Setting
this variable to the empty string (''
)
disables stopword filtering. See also
Section 12.10.4, “Full-Text Stopwords”.
FULLTEXT
indexes on
MyISAM
tables must be rebuilt after
changing this variable or the contents of the stopword file.
Use REPAIR TABLE
.
tbl_name
QUICK
Command-Line Format | --general-log[={OFF|ON}] |
---|---|
System Variable | general_log |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Whether the general query log is enabled. The value can be 0
(or OFF
) to disable the log or 1 (or
ON
) to enable the log. The destination for
log output is controlled by the
log_output
system variable;
if that value is NONE
, no log entries are
written even if the log is enabled.
Command-Line Format | --general-log-file=file_name |
---|---|
System Variable | general_log_file |
Scope | Global |
Dynamic | Yes |
Type | File name |
Default Value | host_name.log |
The name of the general query log file. The default value is
,
but the initial value can be changed with the
host_name
.log--general_log_file
option.
Command-Line Format | --group-concat-max-len=# |
---|---|
System Variable | group_concat_max_len |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1024 |
Minimum Value | 4 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The maximum permitted result length in bytes for the
GROUP_CONCAT()
function. The
default is 1024.
YES
if the zlib
compression library is available to the server,
NO
if not. If not, the
COMPRESS()
and
UNCOMPRESS()
functions cannot
be used.
YES
if the crypt()
system call is available to the server, NO
if not. If not, the ENCRYPT()
function cannot be used.
YES
if mysqld supports
dynamic loading of plugins, NO
if not. If
the value is NO
, you cannot use options
such as --plugin-load
to load
plugins at server startup, or the INSTALL
PLUGIN
statement to load plugins at runtime.
YES
if the server supports spatial data
types, NO
if not.
This variable is a synonym for
have_ssl
.
YES
if statement profiling capability is
present, NO
if not. If present, the
profiling
system variable controls whether
this capability is enabled or disabled. See
Section 13.7.5.32, “SHOW PROFILES Statement”.
This variable is deprecated; expect it to be removed in a future MySQL release.
YES
if mysqld supports
the query cache, NO
if not.
YES
if RTREE
indexes are
available, NO
if not. (These are used for
spatial indexes in MyISAM
tables.)
System Variable | have_ssl |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
Valid Values |
|
YES
if mysqld supports
SSL connections, DISABLED
if the server was
not started with the appropriate appropriate
connection-encryption options to enable SSL connections. For
more information, see
Section 2.9.6, “Configuring SSL Library Support”.
YES
if symbolic link support is enabled,
NO
if not. This is required on Unix for
support of the DATA DIRECTORY
and
INDEX DIRECTORY
table options, and on
Windows for support of data directory symlinks. If the server
is started with the
--skip-symbolic-links
option, the value is DISABLED
.
Command-Line Format | --host-cache-size=# |
---|---|
System Variable | host_cache_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
Minimum Value | 0 |
Maximum Value | 65536 |
The MySQL server maintains an in-memory host cache that contains client host name and IP address information and is used to avoid Domain Name System (DNS) lookups; see Section 5.1.11.2, “DNS Lookups and the Host Cache”.
The host_cache_size
variable
controls the size of the host cache, as well as the size of
the Performance Schema host_cache
table that exposes the cache contents. Setting
host_cache_size
has these
effects:
Setting the size to 0 disables the host cache. With the cache disabled, the server performs a DNS lookup every time a client connects.
Changing the size at runtime causes an implicit host cache
flushing operation that clears the host cache, truncates
the host_cache
table, and
unblocks any blocked hosts.
The default value is autosized to 128, plus 1 for a value of
max_connections
up to 500,
plus 1 for every increment of 20 over 500 in the
max_connections
value, capped
to a limit of 2000.
Using the --skip-host-cache
option is similar to setting the
host_cache_size
system
variable to 0, but
host_cache_size
is more
flexible because it can also be used to resize, enable, and
disable the host cache at runtime, not just at server startup.
Starting the server with
--skip-host-cache
does not
prevent runtime changes to the value of
host_cache_size
, but such
changes have no effect and the cache is not re-enabled even if
host_cache_size
is set larger
than 0.
System Variable | hostname |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
The server sets this variable to the server host name at startup.
This variable is a synonym for the
last_insert_id
variable. It
exists for compatibility with other database systems. You can
read its value with SELECT @@identity
, and
set it using SET identity
.
System Variable | ignore_db_dirs |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
A comma-separated list of names that are not considered as
database directories in the data directory. The value is set
from any instances of
--ignore-db-dir
given at server
startup.
Command-Line Format | --init-connect=name |
---|---|
System Variable | init_connect |
Scope | Global |
Dynamic | Yes |
Type | String |
A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters.
For users that have the SUPER
privilege, the content of
init_connect
is not executed.
This is done so that an erroneous value for
init_connect
does not prevent
all clients from connecting. For example, the value might
contain a statement that has a syntax error, thus causing
client connections to fail. Not executing
init_connect
for users that
have the SUPER
privilege
enables them to open a connection and fix the
init_connect
value.
The server discards any result sets produced by statements in
the value of init_connect
.
Command-Line Format | --init-file=file_name |
---|---|
System Variable | init_file |
Scope | Global |
Dynamic | No |
Type | File name |
If specified, this variable names a file containing SQL statements to be read and executed during the startup process. Each statement must be on a single line and should not include comments.
If the server is started with the
--bootstrap
option, it operates
in bootstap mode and some functionality is unavailable that
limits the statements permitted in the file. These include
statements that relate to account management (such as
CREATE USER
or
GRANT
), replication, and global
transaction identifiers. See
Section 17.1.3, “Replication with Global Transaction Identifiers”.
innodb_
xxx
InnoDB
system variables are
listed in Section 14.14, “InnoDB Startup Options and System Variables”. These variables
control many aspects of storage, memory use, and I/O patterns
for InnoDB
tables, and are especially
important now that InnoDB
is the default
storage engine.
The value to be used by the following
INSERT
or
ALTER TABLE
statement when
inserting an AUTO_INCREMENT
value. This is
mainly used with the binary log.
Command-Line Format | --interactive-timeout=# |
---|---|
System Variable | interactive_timeout |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 28800 |
Minimum Value | 1 |
The number of seconds the server waits for activity on an
interactive connection before closing it. An interactive
client is defined as a client that uses the
CLIENT_INTERACTIVE
option to
mysql_real_connect()
. See also
wait_timeout
.
Command-Line Format | --join-buffer-size=# |
---|---|
System Variable | join_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 262144 |
Minimum Value | 128 |
Maximum Value (Other, 64-bit platforms) | 18446744073709547520 |
Maximum Value (Other, 32-bit platforms) | 4294967295 |
Maximum Value (Windows) | 4294967295 |
The minimum size of the buffer that is used for plain index
scans, range index scans, and joins that do not use indexes
and thus perform full table scans. Normally, the best way to
get fast joins is to add indexes. Increase the value of
join_buffer_size
to get a
faster full join when adding indexes is not possible. One join
buffer is allocated for each full join between two tables. For
a complex join between several tables for which indexes are
not used, multiple join buffers might be necessary.
Unless a Block Nested-Loop or Batched Key Access algorithm is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change the session setting to a larger value only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
When Block Nested-Loop is used, a larger join buffer can be beneficial up to the point where all required columns from all rows in the first table are stored in the join buffer. This depends on the query; the optimal size may be smaller than holding all rows from the first tables.
When Batched Key Access is used, the value of
join_buffer_size
defines how
large the batch of keys is in each request to the storage
engine. The larger the buffer, the more sequential access is
made to the right hand table of a join operation, which can
significantly improve performance.
The default is 256KB. The maximum permissible setting for
join_buffer_size
is
4GB−1. Larger values are permitted for 64-bit platforms
(except 64-bit Windows, for which large values are truncated
to 4GB−1 with a warning).
For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.
Command-Line Format | --keep-files-on-create[={OFF|ON}] |
---|---|
System Variable | keep_files_on_create |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If a MyISAM
table is created with no
DATA DIRECTORY
option, the
.MYD
file is created in the database
directory. By default, if MyISAM
finds an
existing .MYD
file in this case, it
overwrites it. The same applies to .MYI
files for tables created with no INDEX
DIRECTORY
option. To suppress this behavior, set the
keep_files_on_create
variable
to ON
(1), in which case
MyISAM
does not overwrite existing files
and returns an error instead. The default value is
OFF
(0).
If a MyISAM
table is created with a
DATA DIRECTORY
or INDEX
DIRECTORY
option and an existing
.MYD
or .MYI
file is
found, MyISAM always returns an error. It does not overwrite a
file in the specified directory.
Command-Line Format | --key-buffer-size=# |
---|---|
System Variable | key_buffer_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 8388608 |
Minimum Value | 8 |
Maximum Value (64-bit platforms) | OS_PER_PROCESS_LIMIT |
Maximum Value (32-bit platforms) | 4294967295 |
Index blocks for MyISAM
tables are buffered
and are shared by all threads.
key_buffer_size
is the size
of the buffer used for index blocks. The key buffer is also
known as the key cache.
The maximum permissible setting for
key_buffer_size
is
4GB−1 on 32-bit platforms. Larger values are permitted
for 64-bit platforms. The effective maximum size might be
less, depending on your available physical RAM and per-process
RAM limits imposed by your operating system or hardware
platform. The value of this variable indicates the amount of
memory requested. Internally, the server allocates as much
memory as possible up to this amount, but the actual
allocation might be less.
You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM
storage engine, 25% of the
machine's total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine's total memory), your system might start to page
and become extremely slow. This is because MySQL relies on the
operating system to perform file system caching for data
reads, so you must leave some room for the file system cache.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM
.
For even more speed when writing many rows at the same time,
use LOCK TABLES
. See
Section 8.2.4.1, “Optimizing INSERT Statements”.
You can check the performance of the key buffer by issuing a
SHOW STATUS
statement and
examining the
Key_read_requests
,
Key_reads
,
Key_write_requests
, and
Key_writes
status variables.
(See Section 13.7.5, “SHOW Statements”.) The
Key_reads/Key_read_requests
ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests
ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE
table option.
The fraction of the key buffer in use can be determined using
key_buffer_size
in
conjunction with the
Key_blocks_unused
status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:
1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in the key buffer is allocated internally for administrative structures. Factors that influence the amount of overhead for these structures include block size and pointer size. As block size increases, the percentage of the key buffer lost to overhead tends to decrease. Larger blocks results in a smaller number of read operations (because more keys are obtained per read), but conversely an increase in reads of keys that are not examined (if not all keys in a block are relevant to a query).
It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 8.10.2, “The MyISAM Key Cache”.
Command-Line Format | --key-cache-age-threshold=# |
---|---|
System Variable | key_cache_age_threshold |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 300 |
Minimum Value | 100 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
This value controls the demotion of buffers from the hot sublist of a key cache to the warm sublist. Lower values cause demotion to happen more quickly. The minimum value is 100. The default value is 300. See Section 8.10.2, “The MyISAM Key Cache”.
Command-Line Format | --key-cache-block-size=# |
---|---|
System Variable | key_cache_block_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1024 |
Minimum Value | 512 |
Maximum Value | 16384 |
The size in bytes of blocks in the key cache. The default value is 1024. See Section 8.10.2, “The MyISAM Key Cache”.
Command-Line Format | --key-cache-division-limit=# |
---|---|
System Variable | key_cache_division_limit |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 100 |
Minimum Value | 1 |
Maximum Value | 100 |
The division point between the hot and warm sublists of the key cache buffer list. The value is the percentage of the buffer list to use for the warm sublist. Permissible values range from 1 to 100. The default value is 100. See Section 8.10.2, “The MyISAM Key Cache”.
System Variable | large_files_support |
---|---|
Scope | Global |
Dynamic | No |
Type | Boolean |
Whether mysqld was compiled with options for large file support.
Command-Line Format | --large-pages[={OFF|ON}] |
---|---|
System Variable | large_pages |
Scope | Global |
Dynamic | No |
Platform Specific | Linux |
Type | Boolean |
Default Value | OFF |
Whether large page support is enabled (via the
--large-pages
option). See
Section 8.12.4.2, “Enabling Large Page Support”.
System Variable | large_page_size |
---|---|
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 0 |
If large page support is enabled, this shows the size of memory pages. Large memory pages are supported only on Linux; on other platforms, the value of this variable is always 0. See Section 8.12.4.2, “Enabling Large Page Support”.
The value to be returned from
LAST_INSERT_ID()
. This is
stored in the binary log when you use
LAST_INSERT_ID()
in a statement
that updates a table. Setting this variable does not update
the value returned by the
mysql_insert_id()
C API
function.
Command-Line Format | --lc-messages=name |
---|---|
System Variable | lc_messages |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | en_US |
The locale to use for error messages. The default is
en_US
. The server converts the argument to
a language name and combines it with the value of
lc_messages_dir
to produce
the location for the error message file. See
Section 10.12, “Setting the Error Message Language”.
Command-Line Format | --lc-messages-dir=dir_name |
---|---|
System Variable | lc_messages_dir |
Scope | Global |
Dynamic | No |
Type | Directory name |
The directory where error messages are located. The server
uses the value together with the value of
lc_messages
to produce the
location for the error message file. See
Section 10.12, “Setting the Error Message Language”.
Command-Line Format | --lc-time-names=value |
---|---|
System Variable | lc_time_names |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
This variable specifies the locale that controls the language
used to display day and month names and abbreviations. This
variable affects the output from the
DATE_FORMAT()
,
DAYNAME()
and
MONTHNAME()
functions. Locale
names are POSIX-style values such as
'ja_JP'
or 'pt_BR'
. The
default value is 'en_US'
regardless of your
system's locale setting. For further information, see
Section 10.16, “MySQL Server Locale Support”.
System Variable | license |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
Default Value | GPL |
The type of license the server has.
Command-Line Format | --local-infile[={OFF|ON}] |
---|---|
System Variable | local_infile |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
This variable controls server-side LOCAL
capability for LOAD DATA
statements. Depending on the
local_infile
setting, the
server refuses or permits local data loading by clients that
have LOCAL
enabled on the client side.
To explicitly cause the server to refuse or permit
LOAD DATA
LOCAL
statements (regardless of how client programs
and libraries are configured at build time or runtime), start
mysqld with
local_infile
disabled or
enabled, respectively.
local_infile
can also be set
at runtime. For more information, see
Section 6.1.6, “Security Considerations for LOAD DATA LOCAL”.
Command-Line Format | --lock-wait-timeout=# |
---|---|
System Variable | lock_wait_timeout |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 31536000 |
Minimum Value | 1 |
Maximum Value | 31536000 |
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.
This timeout applies to all statements that use metadata
locks. These include DML and DDL operations on tables, views,
stored procedures, and stored functions, as well as
LOCK TABLES
,
FLUSH TABLES WITH READ LOCK
,
and HANDLER
statements.
This timeout does not apply to implicit accesses to system
tables in the mysql
database, such as grant
tables modified by GRANT
or
REVOKE
statements or table
logging statements. The timeout does apply to system tables
accessed directly, such as with
SELECT
or
UPDATE
.
The timeout value applies separately for each metadata lock
attempt. A given statement can require more than one lock, so
it is possible for the statement to block for longer than the
lock_wait_timeout
value
before reporting a timeout error. When lock timeout occurs,
ER_LOCK_WAIT_TIMEOUT
is
reported.
lock_wait_timeout
does not
apply to delayed inserts, which always execute with a timeout
of 1 year. This is done to avoid unnecessary timeouts because
a session that issues a delayed insert receives no
notification of delayed insert timeouts.
System Variable | locked_in_memory |
---|---|
Scope | Global |
Dynamic | No |
Command-Line Format | --log-error[=file_name] |
---|---|
System Variable | log_error |
Scope | Global |
Dynamic | No |
Type | File name |
The name of the error log file, or empty if the server is writing error messages to the console rather than to a named file. See Section 5.4.2, “The Error Log”.
Command-Line Format | --log-output=name |
---|---|
System Variable | log_output |
Scope | Global |
Dynamic | Yes |
Type | Set |
Default Value | FILE |
Valid Values |
|
The destination or destinations for general query log and slow
query log output. The value is a list one or more
comma-separated words chosen from TABLE
,
FILE
, and NONE
.
TABLE
selects logging to the
general_log
and
slow_log
tables in the
mysql
system database.
FILE
selects logging to log files.
NONE
disables logging. If
NONE
is present in the value, it takes
precedence over any other words that are present.
TABLE
and FILE
can both
be given to select both log output destinations.
This variable selects log output destinations, but does not
enable log output. To do that, enable the
general_log
and
slow_query_log
system
variables. For FILE
logging, the
general_log_file
and
slow_query_log_file
system
variables determine the log file locations. For more
information, see Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”.
Command-Line Format | --log-queries-not-using-indexes[={OFF|ON}] |
---|---|
System Variable | log_queries_not_using_indexes |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If you enable this variable with the slow query log enabled, queries that are expected to retrieve all rows are logged. See Section 5.4.5, “The Slow Query Log”. This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.
Command-Line Format | --log-slow-admin-statements[={OFF|ON}] |
---|---|
Introduced | 5.6.11 |
System Variable | log_slow_admin_statements |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Include slow administrative statements in the statements
written to the slow query log. Administrative statements
include ALTER TABLE
,
ANALYZE TABLE
,
CHECK TABLE
,
CREATE INDEX
,
DROP INDEX
,
OPTIMIZE TABLE
, and
REPAIR TABLE
.
This variable was added in MySQL 5.6.11 as a replacement for
the --log-slow-admin-statements
option. The system variable can be set on the command line or
in option files the same way as the option, so there is no
need for any changes at server startup, but the system
variable also makes it possible to examine or set the value at
runtime.
log_throttle_queries_not_using_indexes
Command-Line Format | --log-throttle-queries-not-using-indexes=# |
---|---|
System Variable | log_throttle_queries_not_using_indexes |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
If
log_queries_not_using_indexes
is enabled, the
log_throttle_queries_not_using_indexes
variable limits the number of such queries per minute that can
be written to the slow query log. A value of 0 (the default)
means “no limit”. For more information, see
Section 5.4.5, “The Slow Query Log”.
Command-Line Format | --log-warnings[=#] |
---|---|
System Variable | log_warnings |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Whether to produce additional warning messages to the error log. This variable is enabled by default with a value of 1. To disable it, set it to 0. If the value is greater than 0, the server logs messages about statements that are unsafe for statement-based logging. If the value is greater than 1, the server logs aborted connections and access-denied errors for new connection attempts. See Section B.3.2.10, “Communication Errors and Aborted Connections”.
Command-Line Format | --long-query-time=# |
---|---|
System Variable | long_query_time |
Scope | Global, Session |
Dynamic | Yes |
Type | Numeric |
Default Value | 10 |
Minimum Value | 0 |
If a query takes longer than this many seconds, the server
increments the Slow_queries
status variable. If the slow query log is enabled, the query
is logged to the slow query log file. This value is measured
in real time, not CPU time, so a query that is under the
threshold on a lightly loaded system might be above the
threshold on a heavily loaded one. The minimum and default
values of long_query_time
are
0 and 10, respectively. The value can be specified to a
resolution of microseconds. For logging to a file, times are
written including the microseconds part. For logging to
tables, only integer times are written; the microseconds part
is ignored. See Section 5.4.5, “The Slow Query Log”.
Smaller values of this variable result in more statements being considered long-running, with the result that more space is required for the slow query log. For very small values (less than one second), the log may grow quite large in a small time. Increasing the number of statements considered long-running may also result in false positives for the “excessive Number of Long Running Processes” alert in MySQL Enterprise Monitor. For these reasons, very small values should be used in test environments only, or, in production environments, only for a short period.
Command-Line Format | --low-priority-updates[={OFF|ON}] |
---|---|
System Variable | low_priority_updates |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If set to 1
, all
INSERT
,
UPDATE
,
DELETE
, and LOCK TABLE
WRITE
statements wait until there is no pending
SELECT
or LOCK TABLE
READ
on the affected table. The same effect can be
obtained using {INSERT | REPLACE | DELETE | UPDATE}
LOW_PRIORITY ...
to lower the priority of only one
query. This variable affects only storage engines that use
only table-level locking (such as MyISAM
,
MEMORY
, and MERGE
). See
Section 8.11.2, “Table Locking Issues”.
System Variable | lower_case_file_system |
---|---|
Scope | Global |
Dynamic | No |
Type | Boolean |
This variable describes the case sensitivity of file names on
the file system where the data directory is located.
OFF
means file names are case-sensitive,
ON
means they are not case-sensitive. This
variable is read only because it reflects a file system
attribute and setting it would have no effect on the file
system.
Command-Line Format | --lower-case-table-names[=#] |
---|---|
System Variable | lower_case_table_names |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 2 |
If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case-sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 9.2.3, “Identifier Case Sensitivity”.
On Windows the default value is 1. On macOS, the default value is 2. On Linux, a value of 2 is not supported; the server forces the value to 0 instead.
You should not set
lower_case_table_names
to 0
if you are running MySQL on a system where the data directory
resides on a case-insensitive file system (such as on Windows
or macOS). It is an unsupported combination that could result
in a hang condition when running an INSERT INTO ...
SELECT ... FROM
operation with the wrong tbl_name
tbl_name
letter case. With MyISAM
, accessing table
names using different letter cases could cause index
corruption.
As of MySQL 5.6.27, an error message is printed and the server
exits if you attempt to start the server with
--lower_case_table_names=0
on
a case-insensitive file system.
If you are using InnoDB
tables, you should
set this variable to 1 on all platforms to force names to be
converted to lowercase.
The setting of this variable in MySQL 5.6 affects the behavior of replication filtering options with regard to case sensitivity. This is a change from previous versions of MySQL. (Bug #51639) See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”, for more information.
Command-Line Format | --max-allowed-packet=# |
---|---|
System Variable | max_allowed_packet |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 4194304 |
Minimum Value | 1024 |
Maximum Value | 1073741824 |
The maximum size of one packet or any generated/intermediate
string, or any parameter sent by the
mysql_stmt_send_long_data()
C
API function. The default is 4MB.
The packet message buffer is initialized to
net_buffer_length
bytes, but
can grow up to
max_allowed_packet
bytes when
needed. This value by default is small, to catch large
(possibly incorrect) packets.
You must increase this value if you are using large
BLOB
columns or long strings.
It should be as big as the largest
BLOB
you want to use. The
protocol limit for
max_allowed_packet
is 1GB.
The value should be a multiple of 1024; nonmultiples are
rounded down to the nearest multiple.
When you change the message buffer size by changing the value
of the max_allowed_packet
variable, you should also change the buffer size on the client
side if your client program permits it. The default
max_allowed_packet
value
built in to the client library is 1GB, but individual client
programs might override this. For example,
mysql and mysqldump have
defaults of 16MB and 24MB, respectively. They also enable you
to change the client-side value by setting
max_allowed_packet
on the
command line or in an option file.
The session value of this variable is read only. The client
can receive up to as many bytes as the session value. However,
the server does not send to the client more bytes than the
current global
max_allowed_packet
value.
(The global value could be less than the session value if the
global value is changed after the client connects.)
Command-Line Format | --max-connect-errors=# |
---|---|
System Variable | max_connect_errors |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 100 |
Minimum Value | 1 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
After max_connect_errors
successive connection requests from a host are interrupted
without a successful connection, the server blocks that host
from further connections. If a connection from a host is
established successfully within fewer than
max_connect_errors
attempts
after a previous connection was interrupted, the error count
for the host is cleared to zero. To unblock blocked hosts,
flush the host cache; see
Flushing the Host Cache.
Command-Line Format | --max-connections=# |
---|---|
System Variable | max_connections |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 151 |
Minimum Value | 1 |
Maximum Value | 100000 |
The maximum permitted number of simultaneous client connections. For more information, see Section 5.1.11.1, “Connection Interfaces”.
Command-Line Format | --max-delayed-threads=# |
---|---|
Deprecated | Yes |
System Variable | max_delayed_threads |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 20 |
Minimum Value | 0 |
Maximum Value | 16384 |
Do not start more than this number of threads to handle
INSERT DELAYED
statements for
nontransactional tables. If you try to insert data into a new
table after all INSERT DELAYED
threads are in use, the row is inserted as if the
DELAYED
attribute was not specified. If you
set this to 0, MySQL never creates a thread to handle
DELAYED
rows; in effect, this disables
DELAYED
entirely.
For the SESSION
value of this variable, the
only valid values are 0 or the GLOBAL
value.
This system variable is deprecated (because
DELAYED
inserts are deprecated); expect it
to be removed in a future release.
Command-Line Format | --max-digest-length=# |
---|---|
Introduced | 5.6.24 |
System Variable | max_digest_length |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 1024 |
Minimum Value | 0 |
Maximum Value | 1048576 |
The maximum number of bytes of memory reserved per session for computation of normalized statement digests. Once that amount of space is used during digest computation, truncation occurs: no further tokens from a parsed statement are collected or figure into its digest value. Statements that differ only after that many bytes of parsed tokens produce the same normalized statement digest and are considered identical if compared or if aggregated for digest statistics.
Decreasing the
max_digest_length
value
reduces memory use but causes the digest value of more
statements to become indistinguishable if they differ only at
the end. Increasing the value permits longer statements to be
distinguished but increases memory use, particularly for
workloads that involve large numbers of simultaneous sessions
(the server allocates
max_digest_length
bytes per
session).
The parser uses this system variable as a limit on the maximum
length of normalized statement digests that it computes. The
Performance Schema, if it tracks statement digests, makes a
copy of the digest value, using the
performance_schema_max_digest_length
.
system variable as a limit on the maximum length of digests
that it stores. Consequently, if
performance_schema_max_digest_length
is less than
max_digest_length
, digest
values stored in the Performance Schema are truncated relative
to the original digest values.
In MySQL 5.6.24 and 5.6.25,
performance_schema_max_digest_length
is not available and
max_digest_length
applies to
all digest computation. Before MySQL 5.6.24, neither
max_digest_length
nor
performance_schema_max_digest_length
are available and a fixed maximum of 1024 bytes applies to all
digest computation.
For more information about statement digesting, see Section 22.10, “Performance Schema Statement Digests”.
Command-Line Format | --max-error-count=# |
---|---|
System Variable | max_error_count |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 64 |
Minimum Value | 0 |
Maximum Value | 65535 |
The maximum number of error, warning, and information messages
to be stored for display by the SHOW
ERRORS
and SHOW
WARNINGS
statements. This is the same as the number
of condition areas in the diagnostics area, and thus the
number of conditions that can be inspected by
GET DIAGNOSTICS
.
Command-Line Format | --max-heap-table-size=# |
---|---|
System Variable | max_heap_table_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 16777216 |
Minimum Value | 16384 |
Maximum Value (64-bit platforms) | 1844674407370954752 |
Maximum Value (32-bit platforms) | 4294967295 |
This variable sets the maximum size to which user-created
MEMORY
tables are permitted to grow. The
value of the variable is used to calculate
MEMORY
table MAX_ROWS
values. Setting this variable has no effect on any existing
MEMORY
table, unless the table is
re-created with a statement such as
CREATE TABLE
or altered with
ALTER TABLE
or
TRUNCATE TABLE
. A server
restart also sets the maximum size of existing
MEMORY
tables to the global
max_heap_table_size
value.
This variable is also used in conjunction with
tmp_table_size
to limit the
size of internal in-memory tables. See
Section 8.4.4, “Internal Temporary Table Use in MySQL”.
max_heap_table_size
is not replicated. See
Section 17.4.1.20, “Replication and MEMORY Tables”, and
Section 17.4.1.35, “Replication and Variables”, for more
information.
Deprecated | Yes |
---|---|
System Variable | max_insert_delayed_threads |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
This variable is a synonym for
max_delayed_threads
.
This system variable is deprecated (because
DELAYED
inserts are deprecated); expect it
to be removed in a future release.
Command-Line Format | --max-join-size=# |
---|---|
System Variable | max_join_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 18446744073709551615 |
Minimum Value | 1 |
Maximum Value | 18446744073709551615 |
Do not permit statements that probably need to examine more
than max_join_size
rows (for
single-table statements) or row combinations (for
multiple-table statements) or that are likely to do more than
max_join_size
disk seeks. By
setting this value, you can catch statements where keys are
not used properly and that would probably take a long time.
Set it if your users tend to perform joins that lack a
WHERE
clause, that take a long time, or
that return millions of rows. For more information, see
Using Safe-Updates Mode (--safe-updates).
Setting this variable to a value other than
DEFAULT
resets the value of
sql_big_selects
to
0
. If you set the
sql_big_selects
value again,
the max_join_size
variable is
ignored.
If a query result is in the query cache, no result size check is performed, because the result has previously been computed and it does not burden the server to send it to the client.
Command-Line Format | --max-length-for-sort-data=# |
---|---|
System Variable | max_length_for_sort_data |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1024 |
Minimum Value | 4 |
Maximum Value | 8388608 |
The cutoff on the size of index values that determines which
filesort
algorithm to use. See
Section 8.2.1.13, “ORDER BY Optimization”.
Command-Line Format | --max-prepared-stmt-count=# |
---|---|
System Variable | max_prepared_stmt_count |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 16382 |
Minimum Value | 0 |
Maximum Value | 1048576 |
This variable limits the total number of prepared statements in the server. (The sum of the number of prepared statements across all sessions.) It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. Setting the value to 0 disables prepared statements.
Command-Line Format | --max-seeks-for-key=# |
---|---|
System Variable | max_seeks_for_key |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value (Other, 64-bit platforms) | 18446744073709551615 |
Default Value (Other, 32-bit platforms) | 4294967295 |
Default Value (Windows) | 4294967295 |
Minimum Value | 1 |
Maximum Value (Other, 64-bit platforms) | 18446744073709551615 |
Maximum Value (Other, 32-bit platforms) | 4294967295 |
Maximum Value (Windows) | 4294967295 |
Limit the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required when searching for matching rows in a table by scanning an index, regardless of the actual cardinality of the index (see Section 13.7.5.23, “SHOW INDEX Statement”). By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans.
Command-Line Format | --max-sort-length=# |
---|---|
System Variable | max_sort_length |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1024 |
Minimum Value | 4 |
Maximum Value | 8388608 |
The number of bytes to use when sorting data values. The
server uses only the first
max_sort_length
bytes of each
value and ignores the rest. Consequently, values that differ
only after the first
max_sort_length
bytes compare
as equal for GROUP BY
, ORDER
BY
, and DISTINCT
operations.
Increasing the value of
max_sort_length
may require
increasing the value of
sort_buffer_size
as well. For
details, see Section 8.2.1.13, “ORDER BY Optimization”
Command-Line Format | --max-sp-recursion-depth[=#] |
---|---|
System Variable | max_sp_recursion_depth |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Maximum Value | 255 |
The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255.
Stored procedure recursion increases the demand on thread
stack space. If you increase the value of
max_sp_recursion_depth
, it
may be necessary to increase thread stack size by increasing
the value of thread_stack
at
server startup.
This variable is unused. It is deprecated; expect it to be removed in a future MySQL release.
Command-Line Format | --max-user-connections=# |
---|---|
System Variable | max_user_connections |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 4294967295 |
The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.”
This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:
If the user account has a nonzero
MAX_USER_CONNECTIONS
resource limit,
the session
max_user_connections
value is set to that limit.
Otherwise, the session
max_user_connections
value is set to the global value.
Account resource limits are specified using the
GRANT
statement. See
Section 6.2.13, “Setting Account Resource Limits”, and Section 13.7.1.4, “GRANT Statement”.
Command-Line Format | --max-write-lock-count=# |
---|---|
System Variable | max_write_lock_count |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value (Other, 64-bit platforms) | 18446744073709551615 |
Default Value (Other, 32-bit platforms) | 4294967295 |
Default Value (Windows) | 4294967295 |
Minimum Value | 1 |
Maximum Value (Other, 64-bit platforms) | 18446744073709551615 |
Maximum Value (Other, 32-bit platforms) | 4294967295 |
Maximum Value (Windows) | 4294967295 |
After this many write locks, permit some pending read lock
requests to be processed in between. Write lock requests have
higher priority than read lock requests. However, if
max_write_lock_count
is set
to some low value (say, 10), read lock requests may be
preferred over pending write lock requests if the read lock
requests have already been passed over in favor of 10 write
lock requests. Normally this behavior does not occur because
max_write_lock_count
by
default has a very large value.
Command-Line Format | --metadata-locks-cache-size=# |
---|---|
System Variable | metadata_locks_cache_size |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 1024 |
Minimum Value | 1 |
Maximum Value | 1048576 |
The size of the metadata locks cache. The server uses this cache to avoid creation and destruction of synchronization objects. This is particularly helpful on systems where such operations are expensive, such as Windows XP.
Command-Line Format | --metadata-locks-hash-instances=# |
---|---|
System Variable | metadata_locks_hash_instances |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 8 |
Minimum Value | 1 |
Maximum Value | 1024 |
The set of metadata locks can be partitioned into separate
hashes to permit connections accessing different objects to
use different locking hashes and reduce contention. The
metadata_locks_hash_instances
system variable specifies the number of hashes (default 8).
Command-Line Format | --min-examined-row-limit=# |
---|---|
System Variable | min_examined_row_limit |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Queries that examine fewer than this number of rows are not logged to the slow query log.
Command-Line Format | --multi-range-count=# |
---|---|
Deprecated | Yes |
System Variable | multi_range_count |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 256 |
Minimum Value | 1 |
Maximum Value | 4294967295 |
This variable has no effect. It is deprecated; expect it to be removed in a future MySQL release.
Command-Line Format | --myisam-data-pointer-size=# |
---|---|
System Variable | myisam_data_pointer_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 6 |
Minimum Value | 2 |
Maximum Value | 7 |
The default pointer size in bytes, to be used by
CREATE TABLE
for
MyISAM
tables when no
MAX_ROWS
option is specified. This variable
cannot be less than 2 or larger than 7. The default value is
6. See Section B.3.2.11, “The table is full”.
Command-Line Format | --myisam-max-sort-file-size=# |
---|---|
System Variable | myisam_max_sort_file_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value (Other, 64-bit platforms) | 9223372036853727232 |
Default Value (Other, 32-bit platforms) | 2147483648 |
Default Value (Windows) | 2146435072 |
Maximum Value (Other, 64-bit platforms) | 9223372036853727232 |
Maximum Value (Other, 32-bit platforms) | 2147483648 |
Maximum Value (Windows) | 2146435072 |
The maximum size of the temporary file that MySQL is permitted
to use while re-creating a MyISAM
index
(during REPAIR TABLE
,
ALTER TABLE
, or
LOAD DATA
). If the file size
would be larger than this value, the index is created using
the key cache instead, which is slower. The value is given in
bytes.
If MyISAM
index files exceed this size and
disk space is available, increasing the value may help
performance. The space must be available in the file system
containing the directory where the original index file is
located.
Command-Line Format | --myisam-mmap-size=# |
---|---|
System Variable | myisam_mmap_size |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value (64-bit platforms) | 18446744073709551615 |
Default Value (32-bit platforms) | 4294967295 |
Minimum Value | 7 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The maximum amount of memory to use for memory mapping
compressed MyISAM
files. If many
compressed MyISAM
tables are used, the
value can be decreased to reduce the likelihood of
memory-swapping problems.
Command-Line Format | --myisam-recover-options[=list] |
---|---|
System Variable | myisam_recover_options |
Scope | Global |
Dynamic | No |
Type | Enumeration |
Default Value | OFF |
Valid Values |
|
Set the MyISAM
storage engine recovery
mode. The variable value is any combination of the values of
OFF
, DEFAULT
,
BACKUP
, FORCE
, or
QUICK
. If you specify multiple values,
separate them by commas. Specifying the variable with no value
at server startup is the same as specifying
DEFAULT
, and specifying with an explicit
value of ""
disables recovery (same as a
value of OFF
). If recovery is enabled, each
time mysqld opens a
MyISAM
table, it checks whether the table
is marked as crashed or was not closed properly. (The last
option works only if you are running with external locking
disabled.) If this is the case, mysqld runs
a check on the table. If the table was corrupted,
mysqld attempts to repair it.
The following options affect how the repair works.
Option | Description |
---|---|
OFF |
No recovery. |
DEFAULT |
Recovery without backup, forcing, or quick checking. |
BACKUP |
If the data file was changed during recovery, save a backup of the
file as
. |
FORCE |
Run recovery even if we would lose more than one row from the
.MYD file. |
QUICK |
Do not check the rows in the table if there are not any delete blocks. |
Before the server automatically repairs a table, it writes a
note about the repair to the error log. If you want to be able
to recover from most problems without user intervention, you
should use the options BACKUP,FORCE
. This
forces a repair of a table even if some rows would be deleted,
but it keeps the old data file as a backup so that you can
later examine what happened.
Command-Line Format | --myisam-repair-threads=# |
---|---|
System Variable | myisam_repair_threads |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
If this value is greater than 1, MyISAM
table indexes are created in parallel (each index in its own
thread) during the Repair by sorting
process. The default value is 1.
Multithreaded repair is still beta-quality code.
Command-Line Format | --myisam-sort-buffer-size=# |
---|---|
System Variable | myisam_sort_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 8388608 |
Minimum Value | 4096 |
Maximum Value (Other, 64-bit platforms) | 18446744073709551615 |
Maximum Value (Other, 32-bit platforms) | 4294967295 |
Maximum Value (Windows, 64-bit platforms) | 18446744073709551615 |
Maximum Value (Windows, 32-bit platforms) | 4294967295 |
The size of the buffer that is allocated when sorting
MyISAM
indexes during a
REPAIR TABLE
or when creating
indexes with CREATE INDEX
or
ALTER TABLE
.
Command-Line Format | --myisam-stats-method=name |
---|---|
System Variable | myisam_stats_method |
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | nulls_unequal |
Valid Values |
|
How the server treats NULL
values when
collecting statistics about the distribution of index values
for MyISAM
tables. This variable has three
possible values, nulls_equal
,
nulls_unequal
, and
nulls_ignored
. For
nulls_equal
, all NULL
index values are considered equal and form a single value
group that has a size equal to the number of
NULL
values. For
nulls_unequal
, NULL
values are considered unequal, and each
NULL
forms a distinct value group of size
1. For nulls_ignored
,
NULL
values are ignored.
The method that is used for generating table statistics influences how the optimizer chooses indexes for query execution, as described in Section 8.3.7, “InnoDB and MyISAM Index Statistics Collection”.
Command-Line Format | --myisam-use-mmap[={OFF|ON}] |
---|---|
System Variable | myisam_use_mmap |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Use memory mapping for reading and writing
MyISAM
tables.
Command-Line Format | --named-pipe[={OFF|ON}] |
---|---|
System Variable | named_pipe |
Scope | Global |
Dynamic | No |
Platform Specific | Windows |
Type | Boolean |
Default Value | OFF |
(Windows only.) Indicates whether the server supports connections over named pipes.
Command-Line Format | --named-pipe-full-access-group=value |
---|---|
Introduced | 5.6.43 |
System Variable | named_pipe_full_access_group |
Scope | Global |
Dynamic | No |
Platform Specific | Windows |
Type | String |
Default Value | *everyone* |
Valid Values |
|
(Windows only.) The access control granted to clients on the
named pipe created by the MySQL server is set to the minimum
necessary for successful communication when the
named_pipe
system variable is
enabled to support named-pipe connections. Newer MySQL client
software can open named pipe connections without any
additional configuration, however, older client software may
still require full access to open a named pipe connection.
This variable sets the name of a Windows local group whose
members are granted sufficient access by the MySQL server to
use older named-pipe clients. Initially, the value is set to
'*everyone*'
by default, which permits
users of the Everyone group on Windows to continue using older
clients until the older clients are upgraded. In contrast,
setting the value to an empty string means that no Windows
user is granted full access to the named pipe. The default
value '*everyone*'
provides a
language-independent way of referring to the Everyone group on
Windows.
Ideally, a new Windows local group name (for example,
mysql_old_client_users
) should be created
in Windows and then used to replace the default value for this
variable when access to older client software is absolutely
necessary. In this case, limit the membership of the group to
as few users as possible, removing users from the group when
their client software is upgraded. A non-member of the group
who attempts to open a connection to MySQL with the older
named-pipe client is denied access until the user is added to
the group by a Windows administrator, and then the user logs
out and logs in (required by Windows).
Command-Line Format | --net-buffer-length=# |
---|---|
System Variable | net_buffer_length |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 16384 |
Minimum Value | 1024 |
Maximum Value | 1048576 |
Each client thread is associated with a connection buffer and
result buffer. Both begin with a size given by
net_buffer_length
but are
dynamically enlarged up to
max_allowed_packet
bytes as
needed. The result buffer shrinks to
net_buffer_length
after each
SQL statement.
This variable should not normally be changed, but if you have
very little memory, you can set it to the expected length of
statements sent by clients. If statements exceed this length,
the connection buffer is automatically enlarged. The maximum
value to which
net_buffer_length
can be set
is 1MB.
The session value of this variable is read only.
Command-Line Format | --net-read-timeout=# |
---|---|
System Variable | net_read_timeout |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 30 |
Minimum Value | 1 |
The number of seconds to wait for more data from a connection
before aborting the read. When the server is reading from the
client, net_read_timeout
is
the timeout value controlling when to abort. When the server
is writing to the client,
net_write_timeout
is the
timeout value controlling when to abort. See also
slave_net_timeout
.
Command-Line Format | --net-retry-count=# |
---|---|
System Variable | net_retry_count |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 10 |
Minimum Value | 1 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
If a read or write on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.
Command-Line Format | --net-write-timeout=# |
---|---|
System Variable | net_write_timeout |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 60 |
Minimum Value | 1 |
The number of seconds to wait for a block to be written to a
connection before aborting the write. See also
net_read_timeout
.
Command-Line Format | --new[={OFF|ON}] |
---|---|
System Variable | new |
Scope | Global, Session |
Dynamic | Yes |
Disabled by | skip-new |
Type | Boolean |
Default Value | OFF |
This variable was used in MySQL 4.0 to turn on some 4.1
behaviors, and is retained for backward compatibility. Its
value is always OFF
.
In NDB Cluster, setting this variable to ON
makes it possible to employ partitioning types other than
KEY
or LINEAR KEY
with
NDB
tables. This
feature is experimental only, and not supported in
production. For additional information, see
User-defined partitioning and the NDB storage engine (NDB Cluster).
Command-Line Format | --old[={OFF|ON}] |
---|---|
System Variable | old |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | OFF |
old
is a compatibility
variable. It is disabled by default, but can be enabled at
startup to revert the server to behaviors present in older
versions.
When old
is enabled, it
changes the default scope of index hints to that used prior to
MySQL 5.1.17. That is, index hints with no
FOR
clause apply only to how indexes are
used for row retrieval and not to resolution of ORDER
BY
or GROUP BY
clauses. (See
Section 8.9.3, “Index Hints”.) Take care about enabling this
in a replication setup. With statement-based binary logging,
having different modes for the source and replicas might lead
to replication errors.
Command-Line Format | --old-alter-table[={OFF|ON}] |
---|---|
System Variable | old_alter_table |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
When this variable is enabled, the server does not use the
optimized method of processing an ALTER
TABLE
operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE
, see
Section 13.1.7, “ALTER TABLE Statement”.
Command-Line Format | --old-passwords=value |
---|---|
System Variable | old_passwords |
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | 0 |
Valid Values |
|
This variable controls the password hashing method used by the
PASSWORD()
function. It also
influences password hashing performed by
CREATE USER
and
GRANT
statements that specify a
password using an IDENTIFIED BY
clause.
The following table shows, for each password hashing method,
the permitted value of old_passwords
and
which authentication plugins use the hashing method.
Password Hashing Method | old_passwords Value | Associated Authentication Plugin |
---|---|---|
MySQL 4.1 native hashing | 0 | mysql_native_password |
Pre-4.1 (“old”) hashing | 1 | mysql_old_password |
SHA-256 hashing | 2 | sha256_password |
Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated; expect support for them to be removed in a
future MySQL release. Consequently,
old_passwords=1
, which
causes PASSWORD()
to generate
pre-4.1 password hashes, is also deprecated. For account
upgrade instructions, see
Section 6.4.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password
Plugin”.
If old_passwords=1
,
PASSWORD(
returns the same value as
str
)OLD_PASSWORD(
.
The latter function is not affected by the value of
str
)old_passwords
.
If you set old_passwords=2
,
follow the instructions for using the
sha256_password
plugin at
Section 6.4.1.4, “SHA-256 Pluggable Authentication”.
The server sets the global
old_passwords
value during
startup to be consistent with the password hashing method
required by the default authentication plugin. The default
plugin is mysql_native_password
unless the
--default-authentication-plugin
option is set otherwise.
When a client successfully connects to the server, the server
sets the session
old_passwords
value
appropriately for the account authentication method. For
example, if the account uses the
sha256_password
authentication plugin, the
server sets old_passwords=2
.
For additional information about authentication plugins and hashing formats, see Section 6.2.11, “Pluggable Authentication”, and Section 6.1.2.4, “Password Hashing in MySQL”.
Command-Line Format | --open-files-limit=# |
---|---|
System Variable | open_files_limit |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 5000, with possible adjustment |
Minimum Value | 0 |
Maximum Value | platform dependent |
The number of file descriptors available to mysqld from the operating system:
At startup, mysqld reserves descriptors
with setrlimit()
, using the value
requested at by setting this variable directly or by using
the --open-files-limit
option to mysqld_safe. If
mysqld produces the error Too
many open files
, try increasing the
open_files_limit
value.
Internally, the maximum value for this variable is the
maximum unsigned integer value, but the actual maximum is
platform dependent.
At runtime, the value of
open_files_limit
indicates the number of file descriptors actually
permitted to mysqld by the operating
system, which might differ from the value requested at
startup. If the number of file descriptors requested
during startup cannot be allocated,
mysqld writes a warning to the error
log.
The effective
open_files_limit
value is
based on the value specified at system startup (if any) and
the values of max_connections
and table_open_cache
, using
these formulas:
10 + max_connections + (table_open_cache *
2)
max_connections * 5
The operating system limit if that limit is positive but not Infinity.
If the operating system limit is Infinity:
open_files_limit
value if specified at
startup, 5000 if not.
The server attempts to obtain the number of file descriptors using the maximum of those values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system permits.
The effective value is 0 on systems where MySQL cannot change the number of open files.
On Unix, the value cannot be set greater than the value displayed by the ulimit -n command.
Command-Line Format | --optimizer-prune-level=# |
---|---|
System Variable | optimizer_prune_level |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 0 |
Maximum Value | 1 |
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.
Command-Line Format | --optimizer-search-depth=# |
---|---|
System Variable | optimizer_search_depth |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 62 |
Minimum Value | 0 |
Maximum Value | 62 |
The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value.
Command-Line Format | --optimizer-switch=value |
---|---|
System Variable | optimizer_switch |
Scope | Global, Session |
Dynamic | Yes |
Type | Set |
Valid Values |
|
The optimizer_switch
system
variable enables control over optimizer behavior. The value of
this variable is a set of flags, each of which has a value of
on
or off
to indicate
whether the corresponding optimizer behavior is enabled or
disabled. This variable has global and session values and can
be changed at runtime. The global default can be set at server
startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,
subquery_materialization_cost_based=on,
use_index_extensions=on
For more information about the syntax of this variable and the optimizer behaviors that it controls, see Section 8.9.2, “Switchable Optimizations”.
Command-Line Format | --optimizer-trace=value |
---|---|
System Variable | optimizer_trace |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
This variable controls optimizer tracing. For details, see MySQL Internals: Tracing the Optimizer.
Command-Line Format | --optimizer-trace-features=value |
---|---|
System Variable | optimizer_trace_features |
Scope | Global, Session |
Dynamic | Yes |
Type | String |
This variable enables or disables selected optimizer tracing features. For details, see MySQL Internals: Tracing the Optimizer.
Command-Line Format | --optimizer-trace-limit=# |
---|---|
System Variable | optimizer_trace_limit |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
The maximum number of optimizer traces to display. For details, see MySQL Internals: Tracing the Optimizer.
Command-Line Format | --optimizer-trace-max-mem-size=# |
---|---|
System Variable | optimizer_trace_max_mem_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 16384 |
The maximum cumulative size of stored optimizer traces. For details, see MySQL Internals: Tracing the Optimizer.
Command-Line Format | --optimizer-trace-offset=# |
---|---|
System Variable | optimizer_trace_offset |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | -1 |
The offset of optimizer traces to display. For details, see MySQL Internals: Tracing the Optimizer.
performance_schema_
xxx
Performance Schema system variables are listed in Section 22.15, “Performance Schema System Variables”. These variables may be used to configure Performance Schema operation.
Command-Line Format | --pid-file=file_name |
---|---|
System Variable | pid_file |
Scope | Global |
Dynamic | No |
Type | File name |
The path name of the file in which the server writes its
process ID. The server creates the file in the data directory
unless an absolute path name is given to specify a different
directory. If you specify this variable, you must specify a
value. If you do not specify this variable, MySQL uses a
default value of
,
where host_name
.pidhost_name
is the name of the
host machine.
The process ID file is used by other programs such as mysqld_safe to determine the server's process ID. On Windows, this variable also affects the default error log file name. See Section 5.4.2, “The Error Log”.
Command-Line Format | --plugin-dir=dir_name |
---|---|
System Variable | plugin_dir |
Scope | Global |
Dynamic | No |
Type | Directory name |
Default Value | BASEDIR/lib/plugin |
The path name of the plugin directory.
If the plugin directory is writable by the server, it may be
possible for a user to write executable code to a file in the
directory using SELECT
... INTO DUMPFILE
. This can be prevented by making
plugin_dir
read only to the
server or by setting
secure_file_priv
to a
directory where SELECT
writes
can be made safely.
Command-Line Format | --port=port_num |
---|---|
System Variable | port |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 3306 |
Minimum Value | 0 |
Maximum Value | 65535 |
The number of the port on which the server listens for TCP/IP
connections. This variable can be set with the
--port
option.
Command-Line Format | --preload-buffer-size=# |
---|---|
System Variable | preload_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 32768 |
Minimum Value | 1024 |
Maximum Value | 1073741824 |
The size of the buffer that is allocated when preloading indexes.
If set to 0 or OFF
(the default), statement
profiling is disabled. If set to 1 or ON
,
statement profiling is enabled and the
SHOW PROFILE
and
SHOW PROFILES
statements
provide access to profiling information. See
Section 13.7.5.32, “SHOW PROFILES Statement”.
This variable is deprecated; expect it to be removed in a future MySQL release.
The number of statements for which to maintain profiling
information if profiling
is
enabled. The default value is 15. The maximum value is 100.
Setting the value to 0 effectively disables profiling. See
Section 13.7.5.32, “SHOW PROFILES Statement”.
This variable is deprecated; expect it to be removed in a future MySQL release.
System Variable | protocol_version |
---|---|
Scope | Global |
Dynamic | No |
Type | Integer |
The version of the client/server protocol used by the MySQL server.
System Variable | proxy_user |
---|---|
Scope | Session |
Dynamic | No |
Type | String |
If the current client is a proxy for another user, this
variable is the proxy user account name. Otherwise, this
variable is NULL
. See
Section 6.2.12, “Proxy Users”.
System Variable | pseudo_slave_mode |
---|---|
Scope | Session |
Dynamic | Yes |
Type | Boolean |
This variable is for internal server use.
System Variable | pseudo_thread_id |
---|---|
Scope | Session |
Dynamic | Yes |
Type | Integer |
This variable is for internal server use.
Changing the session value of the
pseudo_thread_id
system
variable changes the value returned by the
CONNECTION_ID()
function.
Command-Line Format | --query-alloc-block-size=# |
---|---|
System Variable | query_alloc_block_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 8192 |
Minimum Value | 1024 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Unit | bytes |
Block Size | 1024 |
The allocation size in bytes of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this parameter.
Command-Line Format | --query-cache-limit=# |
---|---|
System Variable | query_cache_limit |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1048576 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Do not cache results that are larger than this number of bytes. The default value is 1MB.
Command-Line Format | --query-cache-min-res-unit=# |
---|---|
System Variable | query_cache_min_res_unit |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 4096 |
Minimum Value | 512 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB). Tuning information for this variable is given in Section 8.10.3.3, “Query Cache Configuration”.
Command-Line Format | --query-cache-size=# |
---|---|
System Variable | query_cache_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1048576 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The amount of memory allocated for caching query results. By
default, the query cache is disabled. This is achieved using a
default value of 1M, with a default for
query_cache_type
of 0.
The permissible values are multiples of 1024; other values are
rounded down to the nearest multiple. For nonzero values of
query_cache_size
, that many
bytes of memory are allocated even if
query_cache_type=0
. See
Section 8.10.3.3, “Query Cache Configuration”, for more
information.
The query cache needs a minimum size of about 40KB to allocate
its structures. (The exact size depends on system
architecture.) If you set the value of
query_cache_size
too small, a
warning occurs, as described in
Section 8.10.3.3, “Query Cache Configuration”.
Command-Line Format | --query-cache-type=# |
---|---|
System Variable | query_cache_type |
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | 0 |
Valid Values |
|
Set the query cache type. Setting the
GLOBAL
value sets the type for all clients
that connect thereafter. Individual clients can set the
SESSION
value to affect their own use of
the query cache. Possible values are shown in the following
table.
Option | Description |
---|---|
0 or OFF |
Do not cache results in or retrieve results from the query cache. Note
that this does not deallocate the query cache buffer. To
do that, you should set
query_cache_size to 0. |
1 or ON |
Cache all cacheable query results except for those that begin with
SELECT SQL_NO_CACHE . |
2 or DEMAND |
Cache results only for cacheable queries that begin with SELECT
SQL_CACHE . |
This variable defaults to OFF
.
If the server is started with
query_cache_type
set to 0, it does not
acquire the query cache mutex at all, which means that the
query cache cannot be enabled at runtime and there is reduced
overhead in query execution.
Command-Line Format | --query-cache-wlock-invalidate[={OFF|ON}] |
---|---|
System Variable | query_cache_wlock_invalidate |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Normally, when one client acquires a WRITE
lock on a MyISAM
table, other clients are
not blocked from issuing statements that read from the table
if the query results are present in the query cache. Setting
this variable to 1 causes acquisition of a
WRITE
lock for a table to invalidate any
queries in the query cache that refer to the table. This
forces other clients that attempt to access the table to wait
while the lock is in effect.
Command-Line Format | --query-prealloc-size=# |
---|---|
System Variable | query_prealloc_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 8192 |
Minimum Value | 8192 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Block Size | 1024 |
The size in bytes of the persistent buffer used for statement
parsing and execution. This buffer is not freed between
statements. If you are running complex queries, a larger
query_prealloc_size
value
might be helpful in improving performance, because it can
reduce the need for the server to perform memory allocation
during query execution operations.
System Variable | rand_seed1 |
---|---|
Scope | Session |
Dynamic | Yes |
Type | Integer |
The rand_seed1
and
rand_seed2
variables exist as
session variables only, and can be set but not read. The
variables—but not their values—are shown in the
output of SHOW VARIABLES
.
The purpose of these variables is to support replication of
the RAND()
function. For
statements that invoke RAND()
,
the source passes two values to the replica, where they are
used to seed the random number generator. The replica uses
these values to set the session variables
rand_seed1
and
rand_seed2
so that
RAND()
on the replica generates
the same value as on the source.
See the description for
rand_seed1
.
Command-Line Format | --range-alloc-block-size=# |
---|---|
System Variable | range_alloc_block_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 4096 |
Minimum Value | 4096 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Block Size | 1024 |
The size in bytes of blocks that are allocated when doing range optimization.
Command-Line Format | --read-buffer-size=# |
---|---|
System Variable | read_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 131072 |
Minimum Value | 8192 |
Maximum Value | 2147479552 |
Each thread that does a sequential scan for a
MyISAM
table allocates a buffer of this
size (in bytes) for each table it scans. If you do many
sequential scans, you might want to increase this value, which
defaults to 131072. The value of this variable should be a
multiple of 4KB. If it is set to a value that is not a
multiple of 4KB, its value is rounded down to the nearest
multiple of 4KB.
This option is also used in the following context for all storage engines:
For caching the indexes in a temporary file (not a
temporary table), when sorting rows for ORDER
BY
.
For bulk insert into partitions.
For caching results of nested queries.
read_buffer_size
is also used
in one other storage engine-specific way: to determine the
memory block size for MEMORY
tables.
For more information about memory use during different operations, see Section 8.12.4.1, “How MySQL Uses Memory”.
Command-Line Format | --read-only[={OFF|ON}] |
---|---|
System Variable | read_only |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
When the read_only
system
variable is enabled, the server permits no client updates
except from users who have the
SUPER
privilege. This variable
is disabled by default.
Even with read_only
enabled,
the server permits these operations:
Updates performed by replica threads, if the server is a
replica. In replication setups, it can be useful to enable
read_only
on replica
servers to ensure that replicas accept updates only from
the source server and not from clients.
Use of ANALYZE TABLE
or
OPTIMIZE TABLE
statements.
The purpose of read-only mode is to prevent changes to
table structure or contents. Analysis and optimization do
not qualify as such changes. This means, for example, that
consistency checks on read-only replicas can be performed
with mysqlcheck --all-databases
--analyze.
Operations on TEMPORARY
tables.
Inserts into the log tables
(mysql.general_log
and
mysql.slow_log
; see
Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).
read_only
exists only as a
GLOBAL
variable, so changes to its value
require the SUPER
privilege.
Changes to read_only
on a
source server are not replicated to replica servers. The value
can be set on a replica server independent of the setting on
the source.
The following conditions apply to attempts to enable
read_only
:
The attempt fails and an error occurs if you have any
explicit locks (acquired with LOCK
TABLES
) or have a pending transaction.
The attempt blocks while other clients have any ongoing
statement, active LOCK TABLES WRITE
, or
ongoing commit, until the locks are released and the
statements and transactions end. While the attempt to
enable read_only
is
pending, requests by other clients for table locks or to
begin transactions also block until
read_only
has been set.
The attempt blocks if there are active transactions that hold metadata locks, until those transactions end.
read_only
can be enabled
while you hold a global read lock (acquired with
FLUSH TABLES WITH READ
LOCK
) because that does not involve table locks.
Command-Line Format | --read-rnd-buffer-size=# |
---|---|
System Variable | read_rnd_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 262144 |
Minimum Value | 1 |
Maximum Value | 2147483647 |
This variable is used for reads from MyISAM
tables, and, for any storage engine, for Multi-Range Read
optimization.
When reading rows from a MyISAM
table in
sorted order following a key-sorting operation, the rows are
read through this buffer to avoid disk seeks. See
Section 8.2.1.13, “ORDER BY Optimization”. Setting the variable
to a large value can improve ORDER BY
performance by a lot. However, this is a buffer allocated for
each client, so you should not set the global variable to a
large value. Instead, change the session variable only from
within those clients that need to run large queries.
For more information about memory use during different operations, see Section 8.12.4.1, “How MySQL Uses Memory”. For information about Multi-Range Read optimization, see Section 8.2.1.10, “Multi-Range Read Optimization”.
Command-Line Format | --secure-auth[={OFF|ON}] |
---|---|
System Variable | secure_auth |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Enable this variable to prevent all use of passwords employing the old format (and hence insecure communication over the network). This variable is enabled by default.
Server startup fails with an error if this variable is enabled and the privilege tables are in pre-4.1 format. See Section B.3.2.4, “Client does not support authentication protocol”.
Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated; expect support for them to be removed in a
future MySQL release. Consequently, disabling secure
authentication by disabling
secure_auth
is also
deprecated.
Command-Line Format | --secure-file-priv=dir_name |
---|---|
System Variable | secure_file_priv |
Scope | Global |
Dynamic | No |
Type | String |
Default Value (≥ 5.6.34) | platform specific |
Default Value (≤ 5.6.33) | empty string |
Valid Values (≥ 5.6.34) |
|
Valid Values (≤ 5.6.33) |
|
This variable is used to limit the effect of data import and
export operations, such as those performed by the
LOAD DATA
and
SELECT ... INTO
OUTFILE
statements and the
LOAD_FILE()
function. These
operations are permitted only to users who have the
FILE
privilege.
secure_file_priv
may be set
as follows:
If empty, the variable has no effect. This is not a secure setting.
If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server does not create it.
If set to NULL
, the server disables
import and export operations. This value is permitted as
of MySQL 5.6.34.
Before MySQL 5.6.34, this variable is empty by default. As of
5.6.34, the default value is platform specific and depends on
the value of the INSTALL_LAYOUT
CMake option, as shown in the following
table. To specify the default
secure_file_priv
value
explicitly if you are building from source, use the
INSTALL_SECURE_FILE_PRIVDIR
CMake option.
INSTALL_LAYOUT Value |
Default secure_file_priv Value |
---|---|
STANDALONE , WIN |
NULL |
DEB , RPM , SLES ,
SVR4 |
/var/lib/mysql-files |
Otherwise | mysql-files under the
CMAKE_INSTALL_PREFIX value |
To set the default
secure_file_priv
value for
the libmysqld
embedded server, use the
INSTALL_SECURE_FILE_PRIV_EMBEDDEDDIR
CMake option. The default value for this
option is NULL
.
As of MySQL 5.6.34, the server checks the value of
secure_file_priv
at startup
and writes a warning to the error log if the value is
insecure. A non-NULL
value is considered
insecure if it is empty, or the value is the data directory or
a subdirectory of it, or a directory that is accessible by all
users. If secure_file_priv
is
set to a nonexistent path, the server writes an error message
to the error log and exits.
sha256_password_private_key_path
Command-Line Format | --sha256-password-private-key-path=file_name |
---|---|
System Variable | sha256_password_private_key_path |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | private_key.pem |
This variable is available if MySQL was compiled using OpenSSL
(see Section 6.3.4, “SSL Library-Dependent Capabilities”). Its value is the path
name of the RSA private key file for the
sha256_password
authentication plugin. If
the file is named as a relative path, it is interpreted
relative to the server data directory. The file must be in PEM
format.
Because this file stores a private key, its access mode should be restricted so that only the MySQL server can read it.
For information about sha256_password
, see
Section 6.4.1.4, “SHA-256 Pluggable Authentication”.
sha256_password_public_key_path
Command-Line Format | --sha256-password-public-key-path=file_name |
---|---|
System Variable | sha256_password_public_key_path |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | public_key.pem |
This variable is available if MySQL was compiled using OpenSSL
(see Section 6.3.4, “SSL Library-Dependent Capabilities”). Its value is the path
name of the RSA public key file for the
sha256_password
authentication plugin. If
the file is named as a relative path, it is interpreted
relative to the server data directory. The file must be in PEM
format. Because this file stores a public key, copies can be
freely distributed to client users. (Clients that explicitly
specify a public key when connecting to the server using RSA
password encryption must use the same public key as that used
by the server.)
For information about sha256_password
,
including information about how clients specify the RSA public
key, see Section 6.4.1.4, “SHA-256 Pluggable Authentication”.
Command-Line Format | --shared-memory[={OFF|ON}] |
---|---|
System Variable | shared_memory |
Scope | Global |
Dynamic | No |
Platform Specific | Windows |
Type | Boolean |
Default Value | OFF |
(Windows only.) Whether the server permits shared-memory connections.
Command-Line Format | --shared-memory-base-name=name |
---|---|
System Variable | shared_memory_base_name |
Scope | Global |
Dynamic | No |
Platform Specific | Windows |
Type | String |
Default Value | MYSQL |
(Windows only.) The name of shared memory to use for
shared-memory connections. This is useful when running
multiple MySQL instances on a single physical machine. The
default name is MYSQL
. The name is
case-sensitive.
This variable applies only if the server is started with the
shared_memory
system variable
enabled to support shared-memory connections.
Command-Line Format | --show-old-temporals[={OFF|ON}] |
---|---|
Introduced | 5.6.24 |
Deprecated | 5.6.24 |
System Variable | show_old_temporals |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Whether SHOW CREATE TABLE
output includes comments to flag temporal columns found to be
in pre-5.6.4 format (TIME
,
DATETIME
, and
TIMESTAMP
columns without
support for fractional seconds precision). This variable is
disabled by default. If enabled, SHOW
CREATE TABLE
output looks like this:
CREATE TABLE `mytbl` ( `ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP, `dt` datetime /* 5.5 binary format */ DEFAULT NULL, `t` time /* 5.5 binary format */ DEFAULT NULL ) DEFAULT CHARSET=latin1
Output for the COLUMN_TYPE
column of the
INFORMATION_SCHEMA.COLUMNS
table
is affected similarly.
This variable was added in MySQL 5.6.24. It is deprecated; expect it to be removed in a future MySQL release.
Command-Line Format | --skip-external-locking[={OFF|ON}] |
---|---|
System Variable | skip_external_locking |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | ON |
This is OFF
if mysqld
uses external locking (system locking), ON
if external locking is disabled. This affects only
MyISAM
table access.
This variable is set by the
--external-locking
or
--skip-external-locking
option. External locking is disabled by default.
External locking affects only
MyISAM
table access. For more
information, including conditions under which it can and
cannot be used, see Section 8.11.5, “External Locking”.
Command-Line Format | --skip-name-resolve[={OFF|ON}] |
---|---|
System Variable | skip_name_resolve |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | OFF |
Whether to resolve host names when checking client
connections. If this variable is OFF
,
mysqld resolves host names when checking
client connections. If it is ON
,
mysqld uses only IP numbers; in this case,
all Host
column values in the grant tables
must be IP addresses or localhost
. See
Section 5.1.11.2, “DNS Lookups and the Host Cache”.
Depending on the network configuration of your system and the
Host
values for your accounts, clients may
need to connect using an explicit
--host
option, such as
--host=localhost
,
--host=127.0.0.1
, or
--host=::1
.
An attempt to connect to the host 127.0.0.1
normally resolves to the localhost
account.
However, this fails if the server is run with
skip_name_resolve
enabled. If
you plan to do that, make sure an account exists that can
accept a connection. For example, to be able to connect as
root
using
--host=127.0.0.1
or
--host=::1
, create these
accounts:
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password
'; CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password
';
Command-Line Format | --skip-networking[={OFF|ON}] |
---|---|
System Variable | skip_networking |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | OFF |
This variable controls whether the server permits TCP/IP connections. By default, it is disabled (permit TCP connections). If enabled, the server permits only local (non-TCP/IP) connections and all interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted. See Section 5.1.11.2, “DNS Lookups and the Host Cache”.
Command-Line Format | --skip-show-database |
---|---|
System Variable | skip_show_database |
Scope | Global |
Dynamic | No |
This prevents people from using the SHOW
DATABASES
statement if they do not have the
SHOW DATABASES
privilege. This
can improve security if you have concerns about users being
able to see databases belonging to other users. Its effect
depends on the SHOW DATABASES
privilege: If the variable value is ON
, the
SHOW DATABASES
statement is
permitted only to users who have the SHOW
DATABASES
privilege, and the statement displays all
database names. If the value is OFF
,
SHOW DATABASES
is permitted to
all users, but displays the names of only those databases for
which the user has the SHOW
DATABASES
or other privilege.
Because a global privilege is considered a privilege for all
databases, any global privilege enables
a user to see all database names with
SHOW DATABASES
or by
examining the INFORMATION_SCHEMA
SCHEMATA
table.
Command-Line Format | --slow-launch-time=# |
---|---|
System Variable | slow_launch_time |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 2 |
If creating a thread takes longer than this many seconds, the
server increments the
Slow_launch_threads
status
variable.
Command-Line Format | --slow-query-log[={OFF|ON}] |
---|---|
System Variable | slow_query_log |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Whether the slow query log is enabled. The value can be 0 (or
OFF
) to disable the log or 1 (or
ON
) to enable the log. The destination for
log output is controlled by the
log_output
system variable;
if that value is NONE
, no log entries are
written even if the log is enabled.
“Slow” is determined by the value of the
long_query_time
variable. See
Section 5.4.5, “The Slow Query Log”.
Command-Line Format | --slow-query-log-file=file_name |
---|---|
System Variable | slow_query_log_file |
Scope | Global |
Dynamic | Yes |
Type | File name |
Default Value | host_name-slow.log |
The name of the slow query log file. The default value is
,
but the initial value can be changed with the
host_name
-slow.log--slow_query_log_file
option.
Command-Line Format | --socket={file_name|pipe_name} |
---|---|
System Variable | socket |
Scope | Global |
Dynamic | No |
Type | String |
Default Value (Other) | /tmp/mysql.sock |
Default Value (Windows) | MySQL |
On Unix platforms, this variable is the name of the socket
file that is used for local client connections. The default is
/tmp/mysql.sock
. (For some distribution
formats, the directory might be different, such as
/var/lib/mysql
for RPMs.)
On Windows, this variable is the name of the named pipe that
is used for local client connections. The default value is
MySQL
(not case-sensitive).
Command-Line Format | --sort-buffer-size=# |
---|---|
System Variable | sort_buffer_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 262144 |
Minimum Value | 32768 |
Maximum Value (Other, 64-bit platforms) | 18446744073709551615 |
Maximum Value (Other, 32-bit platforms) | 4294967295 |
Maximum Value (Windows) | 4294967295 |
Each session that must perform a sort allocates a buffer of
this size. sort_buffer_size
is not specific to any storage engine and applies in a general
manner for optimization. At minimum the
sort_buffer_size
value must
be large enough to accommodate fifteen tuples in the sort
buffer. Also, increasing the value of
max_sort_length
may require
increasing the value of
sort_buffer_size
. For more
information, see Section 8.2.1.13, “ORDER BY Optimization”
If you see many
Sort_merge_passes
per second
in SHOW GLOBAL
STATUS
output, you can consider increasing the
sort_buffer_size
value to
speed up ORDER BY
or GROUP
BY
operations that cannot be improved with query
optimization or improved indexing.
The optimizer tries to work out how much space is needed but can allocate more, up to the limit. Setting it larger than required globally slows down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section B.3.3.5, “Where MySQL Stores Temporary Files”.
The maximum permissible setting for
sort_buffer_size
is
4GB−1. Larger values are permitted for 64-bit platforms
(except 64-bit Windows, for which large values are truncated
to 4GB−1 with a warning).
System Variable | sql_auto_is_null |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If this variable is enabled, then after a statement that
successfully inserts an automatically generated
AUTO_INCREMENT
value, you can find that
value by issuing a statement of the following form:
SELECT * FROMtbl_name
WHEREauto_col
IS NULL
If the statement returns a row, the value returned is the same
as if you invoked the
LAST_INSERT_ID()
function. For
details, including the return value after a multiple-row
insert, see Section 12.16, “Information Functions”. If no
AUTO_INCREMENT
value was successfully
inserted, the SELECT
statement
returns no row.
The behavior of retrieving an
AUTO_INCREMENT
value by using an
IS NULL
comparison is used by
some ODBC programs, such as Access. See
Obtaining Auto-Increment Values.
This behavior can be disabled by setting
sql_auto_is_null
to
OFF
.
The default value of
sql_auto_is_null
is
OFF
.
System Variable | sql_big_selects |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If set to OFF
, MySQL aborts
SELECT
statements that are
likely to take a very long time to execute (that is,
statements for which the optimizer estimates that the number
of examined rows exceeds the value of
max_join_size
). This is
useful when an inadvisable WHERE
statement
has been issued. The default value for a new connection is
ON
, which permits all
SELECT
statements.
If you set the max_join_size
system variable to a value other than
DEFAULT
,
sql_big_selects
is set to
OFF
.
System Variable | sql_buffer_result |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If enabled, sql_buffer_result
forces results from SELECT
statements to be put into temporary tables. This helps MySQL
free the table locks early and can be beneficial in cases
where it takes a long time to send results to the client. The
default value is OFF
.
System Variable | sql_log_off |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Valid Values |
|
This variable controls whether logging to the general query
log is disabled for the current session (assuming that the
general query log itself is enabled). The default value is
OFF
(that is, enable logging). To disable
or enable general query logging for the current session, set
the session sql_log_off
variable to ON
or OFF
.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.
Command-Line Format | --sql-mode=name |
---|---|
System Variable | sql_mode |
Scope | Global, Session |
Dynamic | Yes |
Type | Set |
Default Value | NO_ENGINE_SUBSTITUTION |
Valid Values |
|
The current server SQL mode, which can be set dynamically. The
default is
NO_ENGINE_SUBSTITUTION
. For
details, see Section 5.1.10, “Server SQL Modes”.
MySQL installation programs may configure the SQL mode
during the installation process. For example,
mysql_install_db creates a default option
file named my.cnf
in the base
installation directory. This file contains a line that sets
the SQL mode; see Section 4.4.3, “mysql_install_db — Initialize MySQL Data Directory”.
If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.
System Variable | sql_notes |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If enabled (the default), diagnostics of
Note
level increment
warning_count
and the server records them.
If disabled, Note
diagnostics do not
increment warning_count
and
the server does not record them. mysqldump
includes output to disable this variable so that reloading the
dump file does not produce warnings for events that do not
affect the integrity of the reload operation.
System Variable | sql_quote_show_create |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If enabled (the default), the server quotes identifiers for
SHOW CREATE TABLE
and
SHOW CREATE DATABASE
statements. If disabled, quoting is disabled. This option is
enabled by default so that replication works for identifiers
that require quoting. See Section 13.7.5.12, “SHOW CREATE TABLE Statement”,
and Section 13.7.5.8, “SHOW CREATE DATABASE Statement”.
System Variable | sql_safe_updates |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
If this variable is enabled,
UPDATE
and
DELETE
statements that do not
use a key in the WHERE
clause or a
LIMIT
clause produce an error. This makes
it possible to catch UPDATE
and
DELETE
statements where keys
are not used properly and that would probably change or delete
a large number of rows. The default value is
OFF
.
For the mysql client,
sql_safe_updates
can be
enabled by using the
--safe-updates
option. For more
information, see Using Safe-Updates Mode (--safe-updates).
System Variable | sql_select_limit |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
The maximum number of rows to return from
SELECT
statements. For more
information, see Using Safe-Updates Mode (--safe-updates).
The default value for a new connection is the maximum number
of rows that the server permits per table. Typical default
values are (232)−1 or
(264)−1. If you have changed
the limit, the default value can be restored by assigning a
value of DEFAULT
.
If a SELECT
has a
LIMIT
clause, the LIMIT
takes precedence over the value of
sql_select_limit
.
System Variable | sql_warnings |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
This variable controls whether single-row
INSERT
statements produce an
information string if warnings occur. The default is
OFF
. Set the value to ON
to produce an information string.
Command-Line Format | --ssl-ca=file_name |
---|---|
System Variable | ssl_ca |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | NULL |
The path name of the Certificate Authority (CA) certificate
file in PEM format. The file contains a list of trusted SSL
Certificate Authorities. Setting this variable implies
enabling --ssl
.
Command-Line Format | --ssl-capath=dir_name |
---|---|
System Variable | ssl_capath |
Scope | Global |
Dynamic | No |
Type | Directory name |
Default Value | NULL |
The path name of the directory that contains trusted SSL
Certificate Authority (CA) certificate files in PEM format.
Support for this capability depends on the SSL library used to
compile MySQL; see Section 6.3.4, “SSL Library-Dependent Capabilities”. Setting
this variable implies enabling
--ssl
.
Command-Line Format | --ssl-cert=file_name |
---|---|
System Variable | ssl_cert |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | NULL |
The path name of the server SSL public key certificate file in
PEM format. Setting this variable implies enabling
--ssl
.
Command-Line Format | --ssl-cipher=name |
---|---|
System Variable | ssl_cipher |
Scope | Global |
Dynamic | No |
Type | String |
Default Value | NULL |
The list of permissible ciphers for connection encryption. If
no cipher in the list is supported, encrypted connections do
not work. Setting this variable implies enabling
--ssl
.
For greatest portability, the cipher list should be a list of one or more cipher names, separated by colons. This format is understood both by OpenSSL and yaSSL. Examples:
[mysqld] ssl_cipher="AES128-SHA" ssl_cipher="DHE-RSA-AES256-SHA:AES128-SHA"
OpenSSL supports a more flexible syntax for specifying ciphers, as described in the OpenSSL documentation at https://www.openssl.org/docs/manmaster/man1/ciphers.html. yaSSL does not, so attempts to use that extended syntax fail for a MySQL distribution compiled using yaSSL.
For information about which encryption ciphers MySQL supports, see Section 6.3.2, “Encrypted Connection TLS Protocols and Ciphers”.
Command-Line Format | --ssl-crl=file_name |
---|---|
System Variable | ssl_crl |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | NULL |
The path name of the file containing certificate revocation
lists in PEM format. Support for revocation-list capability
depends on the SSL library used to compile MySQL. See
Section 6.3.4, “SSL Library-Dependent Capabilities”. Setting this variable implies
enabling --ssl
.
Command-Line Format | --ssl-crlpath=dir_name |
---|---|
System Variable | ssl_crlpath |
Scope | Global |
Dynamic | No |
Type | Directory name |
Default Value | NULL |
The path of the directory that contains certificate
revocation-list files in PEM format. Support for
revocation-list capability depends on the SSL library used to
compile MySQL. See Section 6.3.4, “SSL Library-Dependent Capabilities”. Setting
this variable implies enabling
--ssl
.
Command-Line Format | --ssl-key=file_name |
---|---|
System Variable | ssl_key |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | NULL |
The path name of the server SSL private key file in PEM
format. For better security, use a certificate with an RSA key
size of at least 2048 bits. Setting this variable implies
enabling --ssl
.
If the key file is protected by a passphrase, the server prompts the user for the passphrase. The password must be given interactively; it cannot be stored in a file. If the passphrase is incorrect, the program continues as if it could not read the key.
System Variable | storage_engine |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | InnoDB |
The default storage engine (table type). See Chapter 15, Alternative Storage Engines.
This variable is deprecated. Use
default_storage_engine
instead.
Command-Line Format | --stored-program-cache=# |
---|---|
System Variable | stored_program_cache |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 256 |
Minimum Value | 256 |
Maximum Value | 524288 |
Sets a soft upper limit for the number of cached stored routines per connection. The value of this variable is specified in terms of the number of stored routines held in each of the two caches maintained by the MySQL Server for, respectively, stored procedures and stored functions.
Whenever a stored routine is executed this cache size is checked before the first or top-level statement in the routine is parsed; if the number of routines of the same type (stored procedures or stored functions according to which is being executed) exceeds the limit specified by this variable, the corresponding cache is flushed and memory previously allocated for cached objects is freed. This allows the cache to be flushed safely, even when there are dependencies between stored routines.
Command-Line Format | --sync-frm[={OFF|ON}] |
---|---|
System Variable | sync_frm |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If this variable is set to 1, when any nontemporary table is
created its .frm
file is synchronized to
disk (using fdatasync()
). This is slower
but safer in case of a crash. The default is 1.
System Variable | system_time_zone |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
The server system time zone. When the server begins executing,
it inherits a time zone setting from the machine defaults,
possibly modified by the environment of the account used for
running the server or the startup script. The value is used to
set system_time_zone
.
Typically the time zone is specified by the
TZ
environment variable. It also can be
specified using the
--timezone
option of the
mysqld_safe script.
The system_time_zone
variable
differs from time_zone
.
Although they might have the same value, the latter variable
is used to initialize the time zone for each client that
connects. See Section 5.1.13, “MySQL Server Time Zone Support”.
Command-Line Format | --table-definition-cache=# |
---|---|
System Variable | table_definition_cache |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
Minimum Value | 400 |
Maximum Value | 524288 |
The number of table definitions (from
.frm
files) that can be stored in the
definition cache. If you use a large number of tables, you can
create a large table definition cache to speed up opening of
tables. The table definition cache takes less space and does
not use file descriptors, unlike the normal table cache. The
minimum value is 400. The default value is based on the
following formula, capped to a limit of 2000:
400 + (table_open_cache / 2)
For InnoDB
,
table_definition_cache
acts
as a soft limit for the number of open table instances in the
InnoDB
data dictionary cache. If the number
of open table instances exceeds the
table_definition_cache
setting, the LRU mechanism begins to mark table instances for
eviction and eventually removes them from the data dictionary
cache. The limit helps address situations in which significant
amounts of memory would be used to cache rarely used table
instances until the next server restart. The number of table
instances with cached metadata could be higher than the limit
defined by
table_definition_cache
,
because InnoDB
system table instances and
parent and child table instances with foreign key
relationships are not placed on the LRU list and are not
subject to eviction from memory.
Additionally,
table_definition_cache
defines a soft limit for the number of
InnoDB
file-per-table tablespaces
that can be open at one time, which is also controlled by
innodb_open_files
. If both
table_definition_cache
and
innodb_open_files
are set,
the highest setting is used. If neither variable is set,
table_definition_cache
, which
has a higher default value, is used. If the number of open
tablespace file handles exceeds the limit defined by
table_definition_cache
or
innodb_open_files
, the LRU
mechanism searches the tablespace file LRU list for files that
are fully flushed and are not currently being extended. This
process is performed each time a new tablespace is opened. If
there are no “inactive” tablespaces, no
tablespace files are closed.
Command-Line Format | --table-open-cache=# |
---|---|
System Variable | table_open_cache |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 2000 |
Minimum Value | 1 |
Maximum Value | 524288 |
The number of open tables for all threads. Increasing this
value increases the number of file descriptors that
mysqld requires. You can check whether you
need to increase the table cache by checking the
Opened_tables
status
variable. See Section 5.1.9, “Server Status Variables”. If
the value of Opened_tables
is large and you do not use FLUSH
TABLES
often (which just forces all tables to be
closed and reopened), then you should increase the value of
the table_open_cache
variable. For more information about the table cache, see
Section 8.4.3.1, “How MySQL Opens and Closes Tables”.
Command-Line Format | --table-open-cache-instances=# |
---|---|
System Variable | table_open_cache_instances |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value | 64 |
The number of open tables cache instances (default 1). To
improve scalability by reducing contention among sessions, the
open tables cache can be partitioned into several smaller
cache instances of size
table_open_cache
/
table_open_cache_instances
.
A session needs to lock only one instance to access it for DML
statements. This segments cache access among instances,
permitting higher performance for operations that use the
cache when there are many sessions accessing tables. (DDL
statements still require a lock on the entire cache, but such
statements are much less frequent than DML statements.)
A value of 8 or 16 is recommended on systems that routinely use 16 or more cores.
Command-Line Format | --thread-cache-size=# |
---|---|
System Variable | thread_cache_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
Minimum Value | 0 |
Maximum Value | 16384 |
How many threads the server should cache for reuse. When a
client disconnects, the client's threads are put in the cache
if there are fewer than
thread_cache_size
threads
there. Requests for threads are satisfied by reusing threads
taken from the cache if possible, and only when the cache is
empty is a new thread created. This variable can be increased
to improve performance if you have a lot of new connections.
Normally, this does not provide a notable performance
improvement if you have a good thread implementation. However,
if your server sees hundreds of connections per second you
should normally set
thread_cache_size
high enough
so that most new connections use cached threads. By examining
the difference between the
Connections
and
Threads_created
status
variables, you can see how efficient the thread cache is. For
details, see Section 5.1.9, “Server Status Variables”.
The default value is based on the following formula, capped to a limit of 100:
8 + (max_connections / 100)
Command-Line Format | --thread-concurrency=# |
---|---|
Deprecated | Yes |
System Variable | thread_concurrency |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 10 |
Minimum Value | 1 |
Maximum Value | 512 |
This variable is specific to Solaris 8 and earlier systems,
for which mysqld invokes the
thr_setconcurrency()
function with the
variable value. This function enables applications to give the
threads system a hint about the desired number of threads that
should be run at the same time. Current Solaris versions
document this as having no effect.
This variable is deprecated and is removed in MySQL 5.7. You should remove this from MySQL configuration files whenever you see it unless they are for Solaris 8 or earlier.
Command-Line Format | --thread-handling=name |
---|---|
System Variable | thread_handling |
Scope | Global |
Dynamic | No |
Type | Enumeration |
Default Value | one-thread-per-connection |
Valid Values |
|
The thread-handling model used by the server for connection
threads. The permissible values are
no-threads
(the server uses a single thread
to handle one connection),
one-thread-per-connection
(the server uses
one thread to handle each client connection), and
loaded-dynamically
(set by the thread pool
plugin when it initializes). no-threads
is
useful for debugging under Linux; see
Section 5.8, “Debugging MySQL”.
If the thread pool plugin is enabled, the server sets the
thread_handling
value to
loaded-dynamically
. See
Section 5.5.3.2, “Thread Pool Installation”.
Command-Line Format | --thread-pool-algorithm=# |
---|---|
System Variable | thread_pool_algorithm |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1 |
This variable controls which algorithm the thread pool plugin uses:
A value of 0 (the default) uses a conservative low-concurrency algorithm which is most well tested and is known to produce very good results.
A value of 1 increases the concurrency and uses a more aggressive algorithm which at times has been known to perform 5–10% better on optimal thread counts, but has degrading performance as the number of connections increases. Its use should be considered as experimental and not supported.
This variable is available only if the thread pool plugin is enabled. See Section 5.5.3, “MySQL Enterprise Thread Pool”.
thread_pool_high_priority_connection
Command-Line Format | --thread-pool-high-priority-connection=# |
---|---|
System Variable | thread_pool_high_priority_connection |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1 |
This variable affects queuing of new statements prior to execution. If the value is 0 (false, the default), statement queuing uses both the low-priority and high-priority queues. If the value is 1 (true), queued statements always go to the high-priority queue.
This variable is available only if the thread pool plugin is enabled. See Section 5.5.3, “MySQL Enterprise Thread Pool”.
thread_pool_max_unused_threads
Command-Line Format | --thread-pool-max-unused-threads=# |
---|---|
System Variable | thread_pool_max_unused_threads |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 4096 |
The maximum permitted number of unused threads in the thread pool. This variable makes it possible to limit the amount of memory used by sleeping threads.
A value of 0 (the default) means no limit on the number of
sleeping threads. A value of N
where N
is greater than 0 means 1
consumer thread and N
−1
reserve threads. In this case, if a thread is ready to sleep
but the number of sleeping threads is already at the maximum,
the thread exits rather than going to sleep.
A sleeping thread is either sleeping as a consumer thread or a reserve thread. The thread pool permits one thread to be the consumer thread when sleeping. If a thread goes to sleep and there is no existing consumer thread, it sleeps as a consumer thread. When a thread must be woken up, a consumer thread is selected if there is one. A reserve thread is selected only when there is no consumer thread to wake up.
This variable is available only if the thread pool plugin is enabled. See Section 5.5.3, “MySQL Enterprise Thread Pool”.
Command-Line Format | --thread-pool-prio-kickup-timer=# |
---|---|
System Variable | thread_pool_prio_kickup_timer |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1000 |
Minimum Value | 0 |
Maximum Value | 4294967294 |
This variable affects statements waiting for execution in the low-priority queue. The value is the number of milliseconds before a waiting statement is moved to the high-priority queue. The default is 1000 (1 second).
This variable is available only if the thread pool plugin is enabled. See Section 5.5.3, “MySQL Enterprise Thread Pool”.
Command-Line Format | --thread-pool-size=# |
---|---|
System Variable | thread_pool_size |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 16 |
Minimum Value | 1 |
Maximum Value | 64 |
The number of thread groups in the thread pool. This is the most important parameter controlling thread pool performance. It affects how many statements can execute simultaneously. If a value outside the range of permissible values is specified, the thread pool plugin does not load and the server writes a message to the error log.
This variable is available only if the thread pool plugin is enabled. See Section 5.5.3, “MySQL Enterprise Thread Pool”.
Command-Line Format | --thread-pool-stall-limit=# |
---|---|
System Variable | thread_pool_stall_limit |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 6 |
Minimum Value | 4 |
Maximum Value | 600 |
This variable affects executing statements. The value is the amount of time a statement has to finish after starting to execute before it becomes defined as stalled, at which point the thread pool permits the thread group to begin executing another statement. The value is measured in 10 millisecond units, so the default of 6 means 60ms. Short wait values permit threads to start more quickly. Short values are also better for avoiding deadlock situations. Long wait values are useful for workloads that include long-running statements, to avoid starting too many new statements while the current ones execute.
This variable is available only if the thread pool plugin is enabled. See Section 5.5.3, “MySQL Enterprise Thread Pool”.
Command-Line Format | --thread-stack=# |
---|---|
System Variable | thread_stack |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value (64-bit platforms) | 262144 |
Default Value (32-bit platforms) | 196608 |
Minimum Value | 131072 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Block Size | 1024 |
The stack size for each thread. Many of the limits detected by
the crash-me
test are dependent on this
value. See Section 8.13.2, “The MySQL Benchmark Suite”. The default is
large enough for normal operation. If the thread stack size is
too small, it limits the complexity of the SQL statements that
the server can handle, the recursion depth of stored
procedures, and other memory-consuming actions.
This variable is unused. It is deprecated; expect it to be removed in a future MySQL release.
System Variable | time_zone |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | String |
Default Value | SYSTEM |
Minimum Value | -12:59 |
Maximum Value | +13:00 |
The current time zone. This variable is used to initialize the
time zone for each client that connects. By default, the
initial value of this is 'SYSTEM'
(which
means, “use the value of
system_time_zone
”).
The value can be specified explicitly at server startup with
the --default-time-zone
option.
See Section 5.1.13, “MySQL Server Time Zone Support”.
If set to SYSTEM
, every MySQL function
call that requires a time zone calculation makes a system
library call to determine the current system time zone. This
call may be protected by a global mutex, resulting in
contention.
Command-Line Format | --timed-mutexes[={OFF|ON}] |
---|---|
Deprecated | 5.6.20 |
System Variable | timed_mutexes |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
This variable is deprecated; it has no use. Expect it to be removed in a future MySQL release.
System Variable | timestamp |
---|---|
Scope | Session |
Dynamic | Yes |
Type | Numeric |
Default Value | UNIX_TIMESTAMP() |
Minimum Value | 1 |
Maximum Value | 2147483647 |
Set the time for this client. This is used to get the original
timestamp if you use the binary log to restore rows.
timestamp_value
should be a Unix
epoch timestamp (a value like that returned by
UNIX_TIMESTAMP()
, not a value
in '
format) or
YYYY-MM-DD
hh:mm:ss
'DEFAULT
.
Setting timestamp
to a
constant value causes it to retain that value until it is
changed again. Setting
timestamp
to
DEFAULT
causes its value to be the current
date and time as of the time it is accessed. The maximum value
corresponds to '2038-01-19 03:14:07'
UTC,
the same as for the TIMESTAMP
data type.
In MySQL 5.6,
timestamp
is a
DOUBLE
rather than
BIGINT
because its value includes a
microseconds part.
SET timestamp
affects the value returned by
NOW()
but not by
SYSDATE()
. This means that
timestamp settings in the binary log have no effect on
invocations of SYSDATE()
. The
server can be started with the
--sysdate-is-now
option to
cause SYSDATE()
to be a synonym
for NOW()
, in which case
SET timestamp
affects both functions.
Command-Line Format | --tmp-table-size=# |
---|---|
System Variable | tmp_table_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 16777216 |
Minimum Value | 1024 |
Maximum Value | 18446744073709551615 |
The maximum size of internal in-memory temporary tables. This
variable does not apply to user-created
MEMORY
tables.
The actual limit is the smaller of
tmp_table_size
and
max_heap_table_size
. When an
in-memory temporary table exceeds the limit, MySQL
automatically converts it to an on-disk
MyISAM
table. Increase the value of
tmp_table_size
(and
max_heap_table_size
if
necessary) if you do many advanced GROUP BY
queries and you have lots of memory.
You can compare the number of internal on-disk temporary
tables created to the total number of internal temporary
tables created by comparing
Created_tmp_disk_tables
and
Created_tmp_tables
values.
See also Section 8.4.4, “Internal Temporary Table Use in MySQL”.
Command-Line Format | --tmpdir=dir_name |
---|---|
System Variable | tmpdir |
Scope | Global |
Dynamic | No |
Type | Directory name |
The path of the directory to use for creating temporary files.
It might be useful if your default /tmp
directory resides on a partition that is too small to hold
temporary tables. This variable can be set to a list of
several paths that are used in round-robin fashion. Paths
should be separated by colon characters (:
)
on Unix and semicolon characters (;
) on
Windows.
tmpdir
can be a non-permanent
location, such as a directory on a memory-based file system or
a directory that is cleared when the server host restarts. If
the MySQL server is acting as a replica, and you are using a
non-permanent location for
tmpdir
, consider setting a
different temporary directory for the replica using the
slave_load_tmpdir
variable.
For a replication replica, the temporary files used to
replicate LOAD DATA
statements
are stored in this directory, so with a permanent location
they can survive machine restarts, although replication can
now continue after a restart if the temporary files have been
removed.
For more information about the storage location of temporary files, see Section B.3.3.5, “Where MySQL Stores Temporary Files”.
Command-Line Format | --transaction-alloc-block-size=# |
---|---|
System Variable | transaction_alloc_block_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 8192 |
Minimum Value | 1024 |
Maximum Value (64-bit platforms, ≤ 5.6.23) | 18446744073709551615 |
Maximum Value (32-bit platforms, ≤ 5.6.23) | 4294967295 |
Maximum Value (≥ 5.6.24) | 131072 |
Block Size | 1024 |
The amount in bytes by which to increase a per-transaction
memory pool which needs memory. See the description of
transaction_prealloc_size
.
Command-Line Format | --transaction-prealloc-size=# |
---|---|
System Variable | transaction_prealloc_size |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 4096 |
Minimum Value | 1024 |
Maximum Value (64-bit platforms, ≤ 5.6.23) | 18446744073709551615 |
Maximum Value (32-bit platforms, ≤ 5.6.23) | 4294967295 |
Maximum Value (≥ 5.6.24) | 131072 |
Block Size | 1024 |
There is a per-transaction memory pool from which various
transaction-related allocations take memory. The initial size
of the pool in bytes is
transaction_prealloc_size
.
For every allocation that cannot be satisfied from the pool
because it has insufficient memory available, the pool is
increased by
transaction_alloc_block_size
bytes. When the transaction ends, the pool is truncated to
transaction_prealloc_size
bytes.
By making
transaction_prealloc_size
sufficiently large to contain all statements within a single
transaction, you can avoid many malloc()
calls.
System Variable | tx_isolation |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Enumeration |
Default Value | REPEATABLE-READ |
Valid Values |
|
The transaction isolation level. The default is
REPEATABLE-READ
.
The transaction isolation level has three scopes: global, session, and next transaction. This three-scope implementation leads to some nonstandard isolation-level assignment semantics, as described later.
To set the global transaction isolation level at startup, use
the --transaction-isolation
server option.
At runtime, the isolation level can be set directly using the
SET
statement to assign a value to the
tx_isolation
system variable,
or indirectly using the SET
TRANSACTION
statement. If you set
tx_isolation
directly to an
isolation level name that contains a space, the name should be
enclosed within quotation marks, with the space replaced by a
dash. For example, use this
SET
statement to set the global value:
SET GLOBAL tx_isolation = 'READ-COMMITTED';
Setting the global
tx_isolation
value sets the
isolation level for all subsequent sessions. Existing sessions
are unaffected.
To set the session or next-level
tx_isolation
value, use the
SET
statement. For most session system variables, these statements
are equivalent ways to set the value:
SET @@SESSION.var_name
=value
; SET SESSIONvar_name
=value
; SETvar_name
=value
; SET @@var_name
=value
;
As mentioned previously, the transaction isolation level has a
next-transaction scope, in addition to the global and session
scopes. To enable the next-transaction scope to be set,
SET
syntax for assigning session system variable values has
nonstandard semantics for
tx_isolation
:
To set the session isolation level, use any of these syntaxes:
SET @@SESSION.tx_isolation =value
; SET SESSION tx_isolation =value
; SET tx_isolation =value
;
For each of those syntaxes, these semantics apply:
Sets the isolation level for all subsequent transactions performed within the session.
Permitted within transactions, but does not affect the current ongoing transaction.
If executed between transactions, overrides any preceding statement that sets the next-transaction isolation level.
Corresponds to
SET
SESSION TRANSACTION ISOLATION LEVEL
(with
the SESSION
keyword).
To set the next-transaction isolation level, use this syntax:
SET @@tx_isolation = value
;
For that syntax, these semantics apply:
Sets the isolation level only for the next single transaction performed within the session.
Subsequent transactions revert to the session isolation level.
Not permitted within transactions.
Corresponds to
SET
TRANSACTION ISOLATION LEVEL
(without the
SESSION
keyword).
For more information about SET
TRANSACTION
and its relationship to the
tx_isolation
system variable,
see Section 13.3.6, “SET TRANSACTION Statement”.
System Variable | tx_read_only |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
The transaction access mode. The value can be
OFF
(read/write; the default) or
ON
(read only).
The transaction access mode has three scopes: global, session, and next transaction. This three-scope implementation leads to some nonstandard access-mode assignment semantics, as described later.
To set the global transaction access mode at startup, use the
--transaction-read-only
server
option.
At runtime, the access mode can be set directly using the
SET
statement to assign a value to the
tx_read_only
system variable,
or indirectly using the SET
TRANSACTION
statement. For example, use this
SET
statement to set the global value:
SET GLOBAL tx_read_only = ON;
Setting the global
tx_read_only
value sets the
access mode for all subsequent sessions. Existing sessions are
unaffected.
To set the session or next-level
tx_read_only
value, use the
SET
statement. For most session system variables, these statements
are equivalent ways to set the value:
SET @@SESSION.var_name
=value
; SET SESSIONvar_name
=value
; SETvar_name
=value
; SET @@var_name
=value
;
As mentioned previously, the transaction access mode has a
next-transaction scope, in addition to the global and session
scopes. To enable the next-transaction scope to be set,
SET
syntax for assigning session system variable values has
nonstandard semantics for
tx_read_only
,
To set the session access mode, use any of these syntaxes:
SET @@SESSION.tx_read_only =value
; SET SESSION tx_read_only =value
; SET tx_read_only =value
;
For each of those syntaxes, these semantics apply:
Sets the access mode for all subsequent transactions performed within the session.
Permitted within transactions, but does not affect the current ongoing transaction.
If executed between transactions, overrides any preceding statement that sets the next-transaction access mode.
Corresponds to
SET
SESSION TRANSACTION {READ WRITE | READ ONLY}
(with the SESSION
keyword).
To set the next-transaction access mode, use this syntax:
SET @@tx_read_only = value
;
For that syntax, these semantics apply:
Sets the access mode only for the next single transaction performed within the session.
Subsequent transactions revert to the session access mode.
Not permitted within transactions.
Corresponds to
SET
TRANSACTION {READ WRITE | READ ONLY}
(without the SESSION
keyword).
For more information about SET
TRANSACTION
and its relationship to the
tx_read_only
system variable,
see Section 13.3.6, “SET TRANSACTION Statement”.
System Variable | unique_checks |
---|---|
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
If set to 1 (the default), uniqueness checks for secondary
indexes in InnoDB
tables are performed. If
set to 0, storage engines are permitted to assume that
duplicate keys are not present in input data. If you know for
certain that your data does not contain uniqueness violations,
you can set this to 0 to speed up large table imports to
InnoDB
.
Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.
Command-Line Format | --updatable-views-with-limit[={OFF|ON}] |
---|---|
System Variable | updatable_views_with_limit |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | 1 |
This variable controls whether updates to a view can be made
when the view does not contain all columns of the primary key
defined in the underlying table, if the update statement
contains a LIMIT
clause. (Such updates
often are generated by GUI tools.) An update is an
UPDATE
or
DELETE
statement. Primary key
here means a PRIMARY KEY
, or a
UNIQUE
index in which no column can contain
NULL
.
The variable can have two values:
1
or YES
: Issue a
warning only (not an error message). This is the default
value.
0
or NO
: Prohibit
the update.
validate_password_
xxx
The validate_password
plugin implements a
set of system variables having names of the form
validate_password_
.
These variables affect password testing by that plugin; see
Section 6.4.3.2, “Password Validation Plugin Options and Variables”.
xxx
Command-Line Format | --validate-user-plugins[={OFF|ON}] |
---|---|
Introduced | 5.6.11 |
System Variable | validate_user_plugins |
Scope | Global |
Dynamic | No |
Type | Boolean |
Default Value | ON |
If this variable is enabled (the default), the server checks each user account and produces a warning if conditions are found that would make the account unusable:
The account requires an authentication plugin that is not loaded.
The account requires the
sha256_password
authentication plugin
but the server was started with neither SSL nor RSA
enabled as required by this plugin.
Enabling validate_user_plugins
slows down
server initialization and FLUSH PRIVILEGES
.
If you do not require the additional checking, you can disable
this variable at startup to avoid the performance decrement.
This variable was added in MySQL 5.6.11.
The version number for the server. The value might also
include a suffix indicating server build or configuration
information. -log
indicates that one or
more of the general log, slow query log, or binary log are
enabled. -debug
indicates that the server was
built with debugging support enabled.
System Variable | version_comment |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
The CMake configuration program has a
COMPILATION_COMMENT
option that
permits a comment to be specified when building MySQL. This
variable contains the value of that comment. See
Section 2.9.7, “MySQL Source-Configuration Options”.
System Variable | version_compile_machine |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
The type of the server binary.
System Variable | version_compile_os |
---|---|
Scope | Global |
Dynamic | No |
Type | String |
The type of operating system on which MySQL was built.
Command-Line Format | --wait-timeout=# |
---|---|
System Variable | wait_timeout |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 28800 |
Minimum Value | 1 |
Maximum Value (Other) | 31536000 |
Maximum Value (Windows) | 2147483 |
The number of seconds the server waits for activity on a noninteractive connection before closing it.
On thread startup, the session
wait_timeout
value is
initialized from the global
wait_timeout
value or from
the global
interactive_timeout
value,
depending on the type of client (as defined by the
CLIENT_INTERACTIVE
connect option to
mysql_real_connect()
). See
also interactive_timeout
.
The number of errors, warnings, and notes that resulted from the last statement that generated messages. This variable is read only. See Section 13.7.5.41, “SHOW WARNINGS Statement”.