360SDN.COM

首页/MySQL/列表

MySQL 8.0 Server System Variables【一】英文

来源:  2018-08-02 20:01:04    评论:0点击:

来源:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_use_secondary_engine


 Server System Variables

The MySQL server maintains many system variables that indicate how it is configured. 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 the global value of a system variable normally requires the SYSTEM_VARIABLES_ADMIN or SUPER privilege, with exceptions as noted in individual variable descriptions. For some system variables, setting the session value also requires the sme privilege; again, with exceptions as noted in individual variable descriptions.

There are several ways to see the names and values of system variables:

  • To see the values that a server will use 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 will use based 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 or the Performance Schema system variable tables. See Section 25.11.13, “Performance Schema System Variable Tables”.

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:

Note

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.5, “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 will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set 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 will be located under /var/mysql/data. If the value is an absolute path name, its location is as given by the path name.

  • activate_all_roles_on_login

    Property Value
    Command-Line Format --activate-all-roles-on-login
    Introduced 8.0.2
    System Variable activate_all_roles_on_login
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Whether to enable automatic activation of all granted roles when users log in to the server:

    Granted roles include those granted explicitly to the user and those named in the mandatory_roles system variable value.

    activate_all_roles_on_login applies only at login time, and at the beginning of execution for stored programs and views that execute in definer context. To change the active roles within a session, use SET ROLE. To change the active roles for a stored program, the program body should execute SET ROLE.

  • authentication_windows_log_level

    Property Value
    Command-Line Format --authentication-windows-log-level
    Introduced 8.0.11
    Type integer
    Default Value 0
    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.5.1.6, “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

    Property Value
    Command-Line Format --authentication-windows-use-principal-name
    Introduced 8.0.11
    Type boolean
    Default Value ON

    This variable is available only if the authentication_windows Windows authentication plugin is enabled. See Section 6.5.1.6, “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 user_id@computer_name and need not be registered anywhere to be used. This UPN is sent by the server at the beginning of authentication handshake.

    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 in clear text. 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 will happen primarily in environments that do not support Kerberos authentication), the UPN is not sent by the server and NTLM authentication is used.

  • autocommit

    Property Value
    Command-Line Format --autocommit[=#]
    System Variable autocommit
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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 Syntax”.

    By default, client connections begin with autocommit set to 1. To cause clients to begin with a default of 0, set the globalautocommit value by starting the server with the --autocommit=0 option. To set the variable using an option file, include these lines:

    [mysqld]
    autocommit=0
  • automatic_sp_privileges

    Property Value
    System Variable automatic_sp_privileges
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value TRUE

    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.

    See also Section 23.2.2, “Stored Routines and MySQL Privileges”.

  • auto_generate_certs

    Property Value
    Command-Line Format --auto-generate-certs[={OFF|ON}]
    System Variable auto_generate_certs
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean
    Default Value ON

    This variable is available if the server was compiled using OpenSSL (see Section 6.4.4, “OpenSSL Versus wolfSSL”). It controls whether the server autogenerates SSL key and certificate files in the data directory, if they do not already exist.

    At startup, the server automatically generates server-side and client-side SSL certificate and key files in the data directory if theauto_generate_certs system variable is enabled, no SSL options other than --ssl are specified, and the server-side SSL files are missing from the data directory. These files enable secure client connections using SSL; see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.

    For more information about SSL file autogeneration, including file names and characteristics, see Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”

    The sha256_password_auto_generate_rsa_keys and caching_sha2_password_auto_generate_rsa_keys system variables are related but control autogeneration of RSA key-pair files needed for secure password exchange using RSA over unencypted connections.

  • avoid_temporal_upgrade

    Property Value
    Command-Line Format --avoid-temporal-upgrade={OFF|ON}
    Deprecated Yes
    System Variable avoid_temporal_upgrade
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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 is deprecated and will be removed in a future MySQL release.

  • back_log

    Property Value
    System Variable back_log
    Scope Global
    Dynamic No
    SET_VAR Hint Applies 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 the value of max_connections, which enables the permitted backlog to adjust to the maximum permitted number of connections.

  • basedir

    Property Value
    Command-Line Format --basedir=dir_name
    System Variable basedir
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name
    Default Value (>= 8.0.2) parent of mysqld installation directory
    Default Value (<= 8.0.1) configuration-dependent default

    The path to the MySQL installation base directory.

  • big_tables

    Property Value
    Command-Line Format --big-tables
    System Variable big_tables
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The tabletbl_name is full does not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable. When in-memory internaltemporary tables are managed by the TempTable storage engine (the default), and the maximum amount of memory that can be occupied by the TempTable storage engine is exceeded, the TempTable storage engine starts storing data to temporary files on disk. When in-memory temporary tables are managed by the MEMORY storage engine, in-memory tables are automatically converted to disk-based tables as required. For more information, see Section 8.4.4, “Internal Temporary Table Use in MySQL”.

  • bind_address

    Property Value
    Command-Line Format --bind-address=addr
    System Variable bind_address
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value *

    The value of the --bind-address option.

  • block_encryption_mode

    Property Value
    Command-Line Format --block-encryption-mode=#
    System Variable block_encryption_mode
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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-keylen-mode format, where 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 wolfSSL:

    • For OpenSSL, permitted mode values are: ECB, CBC, CFB1, CFB8, CFB128, OFB

    • For wolfSSL, 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.

  • bulk_insert_buffer_size

    Property Value
    Command-Line Format --bulk-insert-buffer-size=#
    System Variable bulk_insert_buffer_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies 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 INFILE 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.

  • caching_sha2_password_auto_generate_rsa_keys

    Property Value
    Command-Line Format --caching-sha2-password-auto-generate-rsa-keys[={OFF|ON}]
    Introduced 8.0.4
    System Variable caching_sha2_password_auto_generate_rsa_keys
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean
    Default Value ON

    This variable is available if the server was compiled using OpenSSL (see Section 6.4.4, “OpenSSL Versus wolfSSL”). The server uses it to determine whether to autogenerate RSA private/public key-pair files in the data directory if they do not already exist.

    At startup, the server automatically generates RSA private/public key-pair files in the data directory if all of these conditions are true: The sha256_password_auto_generate_rsa_keys or caching_sha2_password_auto_generate_rsa_keys system variable is enabled; no RSA options are specified; the RSA files are missing from the data directory. These key-pair files enable secure password exchange using RSA over unencrypted connections for accounts authenticated by the sha256_password orcaching_sha2_password plugin; see Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.

    For more information about RSA file autogeneration, including file names and characteristics, see Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”

    The auto_generate_certs system variable is related but controls autogeneration of SSL certificate and key files needed for secure connections using SSL.

  • caching_sha2_password_private_key_path

    Property Value
    Command-Line Format --caching-sha2-password-private-key-path=file_name
    Introduced 8.0.3
    System Variable caching_sha2_password_private_key_path
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name
    Default Value private_key.pem

    This variable specifies the path name of the RSA private key file for the caching_sha2_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.

    Important

    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 caching_sha2_password, see Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.

  • caching_sha2_password_public_key_path

    Property Value
    Command-Line Format --caching-sha2-password-public-key-path=file_name
    Introduced 8.0.3
    System Variable caching_sha2_password_public_key_path
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name
    Default Value public_key.pem

    This variable specifies the path name of the RSA public key file for the caching_sha2_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.

    For information about caching_sha2_password, including information about how clients request the RSA public key, seeSection 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.

  • character_set_client

    Property Value
    System Variable character_set_client
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4
    Default Value (8.0.0) 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 is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.

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

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

    ucs2, utf16, utf16le, and utf32 cannot be used as a client character set, which means that they also do not work for SET NAMES or SET CHARACTER SET.

  • character_set_connection

    Property Value
    System Variable character_set_connection
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4
    Default Value (8.0.0) utf8

    The character set used for literals that do not have a character set introducer and for number-to-string conversion. For information about introducers, see Section 10.3.8, “Character Set Introducers”.

  • character_set_database

    Property Value
    System Variable character_set_database
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4
    Default Value (8.0.0) latin1
    Footnote This option is dynamic, but only the server should set this information. You should not set the value of 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.

    The global character_set_database and collation_database system variables are deprecated and will be removed in a future version of MySQL.

    Assigning a value to the session character_set_database and collation_database system variables is deprecated and assignments produce a warning. The session variables will become read only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database.

  • character_set_filesystem

    Property Value
    Command-Line Format --character-set-filesystem=name
    System Variable character_set_filesystem
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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 INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted fromcharacter_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 'utf8'.

  • character_set_results

    Property Value
    System Variable character_set_results
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4
    Default Value (8.0.0) utf8

    The character set used for returning query results such as result sets or error messages to the client.

  • character_set_server

    Property Value
    Command-Line Format --character-set-server
    System Variable character_set_server
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4
    Default Value (8.0.0) latin1

    The server's default character set.

  • character_set_system

    Property Value
    System Variable character_set_system
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value utf8

    The character set used by the server for storing identifiers. The value is always utf8.

  • character_sets_dir

    Property Value
    Command-Line Format --character-sets-dir=dir_name
    System Variable character_sets_dir
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name

    The directory where character sets are installed.

  • check_proxy_users

    Property Value
    Command-Line Format --check-proxy-users=[={OFF|ON}]
    System Variable check_proxy_users
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Some authentication plugins implement proxy user mapping for themselves (for example, the PAM and Windows authentication plugins). Other authentication plugins do not support proxy users by default. Of these, some can request that the MySQL server itself map proxy users according to granted proxy privileges: mysql_native_password, sha256_password.

    If the check_proxy_users system variable is enabled, the server performs proxy user mapping for any authentication plugins that make such a request. However, it may also be necessary to enable plugin-specific system variables to take advantage of server proxy user mapping support:

    For information about user proxying, see Section 6.3.11, “Proxy Users”.

  • collation_connection

    Property Value
    System Variable collation_connection
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string

    The collation of the connection character set.

  • collation_database

    Property Value
    System Variable collation_database
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4_0900_ai_ci
    Default Value (8.0.0) latin1_swedish_ci
    Footnote This option is dynamic, but only the server should set this information. You should not set the value of 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.

    The global character_set_database and collation_database system variables are deprecated and will be removed in a future version of MySQL.

    Assigning a value to the session character_set_database and collation_database system variables is deprecated and assignments produce a warning. The session variables will become read only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database.

  • collation_server

    Property Value
    Command-Line Format --collation-server
    System Variable collation_server
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value (>= 8.0.1) utf8mb4_0900_ai_ci
    Default Value (8.0.0) latin1_swedish_ci

    The server's default collation.

  • completion_type

    Property Value
    Command-Line Format --completion-type=#
    System Variable completion_type
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value NO_CHAIN
    Valid Values

    NO_CHAIN

    CHAIN

    RELEASE

    0

    1

    2

    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.

  • concurrent_insert

    Property Value
    Command-Line Format --concurrent-insert[=#]
    System Variable concurrent_insert
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value AUTO
    Valid Values

    NEVER

    AUTO

    ALWAYS

    0

    1

    2

    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. If you start mysqld with --skip-new, this variable is set to NEVER.

    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.

    See also Section 8.11.3, “Concurrent Inserts”.

  • connect_timeout

    Property Value
    Command-Line Format --connect-timeout=#
    System Variable connect_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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.

  • core_file

    Property Value
    System Variable core_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean
    Default Value OFF

    Whether to write a core file if the server crashes. This variable is set by the --core-file option.

  • cte_max_recursion_depth

    Property Value
    Command-Line Format --cte-max-recursion-depth=#
    Introduced 8.0.3
    System Variable cte_max_recursion_depth
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1000
    Minimum Value 0
    Maximum Value 4294967295

    The common table expression (CTE) maximum recursion depth. The server terminates execution of any CTE that recurses more levels than the value of this variable. For more information, see Limiting Common Table Expression Recursion.

  • datadir

    Property Value
    Command-Line Format --datadir=dir_name
    System Variable datadir
    Scope Global
    Dynamic No
    SET_VAR Hint Applies 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 will be started automatically (that is, in contexts for which you cannot assume what the current directory will be), it is best to specify thedatadir value as an absolute path.

  • date_format

    This system variable was removed in MySQL 8.0.3.

  • datetime_format

    This system variable was removed in MySQL 8.0.3.

  • debug

    Property Value
    Command-Line Format --debug[=debug_options]
    System Variable debug
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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. The SYSTEM_VARIABLES_ADMIN or SUPER privilege is required, even for the session value.

    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 28.5.3, “The DBUG Package”.

  • debug_sync

    Property Value
    System Variable debug_sync
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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.8.4, “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=N, where 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 will have the same value as the global variable. The session value can be set by users that have the SYSTEM_VARIABLES_ADMIN or SUPER privilege to control synchronization points.

    For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.

  • default_authentication_plugin

    Property Value
    Command-Line Format --default-authentication-plugin=plugin_name
    System Variable default_authentication_plugin
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type enumeration
    Default Value (>= 8.0.4) caching_sha2_password
    Default Value (<= 8.0.3) mysql_native_password
    Valid Values (>= 8.0.3)

    mysql_native_password

    sha256_password

    caching_sha2_password

    Valid Values (<= 8.0.2)

    mysql_native_password

    sha256_password

    The default authentication plugin. These values are permitted:

    Note

    In MySQL 8.0, caching_sha2_password is the default authentication plugin rather thanmysql_native_password. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

    The default_authentication_plugin value affects these aspects of server operation:

    • It determines which authentication plugin the server assigns to new accounts created by CREATE USER and GRANTstatements that do not explicitly specify an authentication plugin.

    • For an account created with the following statement, the server associates the account with the default authentication plugin and assigns the account the given password, hashed as required by that plugin:

      CREATE USER ... IDENTIFIED BY 'cleartext password';
  • default_collation_for_utf8mb4

    Property Value
    Introduced 8.0.11
    System Variable default_collation_for_utf8mb4
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Valid Values

    utf8mb4_0900_ai_ci

    utf8mb4_general_ci

    For internal use by replication. This system variable is set to the default collation for the utf8mb4 character set. The value of the variable is replicated from a master to a slave so that the slave can correctly process data originating from a master with a different default collation for utf8mb4. This variable is primarily intended to support replication from a MySQL 5.7 or older master server to a MySQL 8.0 slave server, or group replication with a MySQL 5.7 primary node and one or more MySQL 8.0 secondaries. The default collation for utf8mb4 in MySQL 5.7 is utf8mb4_general_ci, but utf8mb4_0900_ai_ci in MySQL 8.0. The variable is not present in releases earlier than MySQL 8.0, so if the slave does not receive a value for the variable, it assumes the master is from an earlier release and sets the value to the previous default collation utf8mb4_general_ci.

    The default utf8mb4 collation is used in the following statements:

    • SHOW COLLATION and SHOW CHARACTER SET.

    • CREATE TABLE and ALTER TABLE having a CHARACTER SET utf8mb4 clause without a COLLATION clause, either for the table character set or for a column character set.

    • CREATE DATABASE and ALTER DATABASE having a CHARACTER SET utf8mb4 clause without a COLLATION clause.

    • Any statement containing a string literal of the form _utf8mb4'some text' without a COLLATE clause.

  • default_password_lifetime

    Property Value
    Command-Line Format --default-password-lifetime=#
    System Variable default_password_lifetime
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 65535

    This variable defines the global automatic password expiration policy. The default default_password_lifetime value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

    The global password expiration policy can be overridden as desired for individual accounts using the password expiration option of the CREATE USER and ALTER USER statements. See Section 6.3.8, “Password Management”.

  • default_storage_engine

    Property Value
    Command-Line Format --default-storage-engine=name
    System Variable default_storage_engine
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value InnoDB

    The default storage engine. 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_SCHEMAENGINES table.

    If you disable the default storage engine at server startup, you must set the default engine for both permanent and TEMPORARYtables to a different engine or the server will not start.

  • default_tmp_storage_engine

    Property Value
    Command-Line Format --default-tmp-storage-engine=name
    System Variable default_tmp_storage_engine
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies 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. Also see the discussion of that variable regarding possible values.

    If you disable the default storage engine at server startup, you must set the default engine for both permanent and TEMPORARYtables to a different engine or the server will not start.

  • default_week_format

    Property Value
    Command-Line Format --default-week-format=#
    System Variable default_week_format
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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”.

  • delay_key_write

    Property Value
    Command-Line Format --delay-key-write[=name]
    System Variable delay_key_write
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value ON
    Valid Values

    ON

    OFF

    ALL

    This option applies only to MyISAM tables. It 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 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 MyISAMtables by starting the server with the --myisam-recover-options option (for example, --myisam-recover-options=BACKUP,FORCE). See Section 5.1.6, “Server Command Options”, and Section 16.2.1, “MyISAM Startup Options”.

    Warning

    If you enable external locking with --external-locking, there is no protection against index corruption for tables that use delayed key writes.

  • delayed_insert_limit

    Property Value
    Command-Line Format --delayed-insert-limit=#
    Deprecated Yes
    System Variable delayed_insert_limit
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 100
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • delayed_insert_timeout

    Property Value
    Command-Line Format --delayed-insert-timeout=#
    Deprecated Yes
    System Variable delayed_insert_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 300

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • delayed_queue_size

    Property Value
    Command-Line Format --delayed-queue-size=#
    Deprecated Yes
    System Variable delayed_queue_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1000
    Minimum Value 1
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • disabled_storage_engines

    Property Value
    Command-Line Format --disabled-storage-engines=engine[,engine]...
    System Variable disabled_storage_engines
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value empty string

    This variable indicates which storage engines cannot be used to create tables or tablespaces. For example, to prevent newMyISAM or FEDERATED tables from being created, start the server with these lines in the server option file:

    [mysqld]
    disabled_storage_engines="MyISAM,FEDERATED"

    By default, disabled_storage_engines is empty (no engines disabled), but it can be set to a comma-separated list of one or more engines (not case sensitive). Any engine named in the value cannot be used to create tables or tablespaces with CREATE TABLE or CREATE TABLESPACE, and cannot be used with ALTER TABLE ... ENGINE or ALTER TABLESPACE ... ENGINE to change the storage engine of existing tables or tablespaces. Attempts to do so result in an ER_DISABLED_STORAGE_ENGINE error.

    disabled_storage_engines does not restrict other DDL statements for existing tables, such as CREATE INDEX, TRUNCATE TABLE, ANALYZE TABLE, DROP TABLE, or DROP TABLESPACE. This permits a smooth transition so that existing tables or tablespaces that use a disabled engine can be migrated to a permitted engine by means such as ALTER TABLE ... ENGINEpermitted_engine.

    It is permitted to set the default_storage_engine or default_tmp_storage_engine system variable to a storage engine that is disabled. This could cause applications to behave erratically or fail, although that might be a useful technique in a development environment for identifying applications that use disabled engines, so that they can be modified.

    disabled_storage_engines is disabled and has no effect if the server is started with any of these options: --initialize, --initialize-insecure, --skip-grant-tables.

  • disconnect_on_expired_password

    Property Value
    Command-Line Format --disconnect-on-expired-password[=#]
    System Variable disconnect_on_expired_password
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type boolean
    Default Value ON

    This variable controls how the server handles clients with expired passwords:

    For more information about the interaction of client and server settings relating to expired-password handling, see Section 6.3.9, “Server Handling of Expired Passwords”.

  • div_precision_increment

    Property Value
    Command-Line Format --div-precision-increment=#
    System Variable div_precision_increment
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies 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 |
    +----------------+
  • dragnet.log_error_filter_rules

    Property Value
    Command-Line Format --dragnet.log-error-filter-rules
    Introduced 8.0.4
    System Variable dragnet.log_error_filter_rules
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value IF prio>=INFORMATION THEN drop. IF EXISTS source_line THEN unset source_line.

    The filter rules that control operation of the log_filter_dragnet error log filter component. If log_filter_dragnet is not installed, dragnet.log_error_filter_rules is unavailable. If log_filter_dragnet is installed but not enabled, changes todragnet.log_error_filter_rules have no effect.

    As of MySQL 8.0.12, the dragnet.Status status variable can be consulted to determine the result of the most recent assignment to dragnet.log_error_filter_rules.

    Prior to MySQL 8.0.12, successful assignments to dragnet.log_error_filter_rules at runtime produce a note confirming the new value:

    mysql> SET GLOBAL dragnet.log_error_filter_rules = 'IF prio <> 0 THEN unset prio.';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Note
       Code: 4569
    Message: filter configuration accepted:
             SET @@global.dragnet.log_error_filter_rules=
             'IF prio!=ERROR THEN unset prio.';

    The value displayed by SHOW WARNINGS indicates the decompiled canonical representation after the rule set has been successfully parsed and compiled into internal form. Semantically, this canonical form is identical to the value assigned todragnet.log_error_filter_rules, but there may be some differences between the assigned and canonical values, as illustrated by the preceding example:

    • The <> operator is changed to !=.

    • The numeric priority of 0 is changed to the corresponding severity symbol ERROR.

    • Optional spaces are removed.

    For additional information, see Section 5.4.2.5, “Error Log Filtering”, and Section 5.5.1, “Error Log Components”.

  • end_markers_in_json

    Property Value
    System Variable end_markers_in_json
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type boolean
    Default Value OFF

    Whether optimizer JSON output should add end markers. See MySQL Internals: The end_markers_in_json System Variable.

  • eq_range_index_dive_limit

    Property Value
    System Variable eq_range_index_dive_limit
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type integer
    Default Value 200
    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 ... OR col_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, seteq_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.

  • error_count

    The number of errors that resulted from the last statement that generated messages. This variable is read only. SeeSection 13.7.6.17, “SHOW ERRORS Syntax”.

  • event_scheduler

    Property Value
    Command-Line Format --event-scheduler[=value]
    System Variable event_scheduler
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type enumeration
    Default Value (>= 8.0.3) ON
    Default Value (<= 8.0.2) OFF
    Valid Values

    ON

    OFF

    DISABLED

    This variable indicates the status of the Event Scheduler; possible values are ON, OFF, and DISABLED, with the default being ON. This variable and its effects on the Event Scheduler's operation are discussed in greater detail in the Overview section of the Events chapter.

  • explicit_defaults_for_timestamp

    Property Value
    Command-Line Format --explicit-defaults-for-timestamp=#
    Deprecated Yes
    System Variable explicit_defaults_for_timestamp
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type boolean
    Default Value (>= 8.0.2) ON
    Default Value (<= 8.0.1) 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 enabled, which disables the nonstandard behaviors. Disabling explicit_defaults_for_timestamp results in a warning.

    If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMPcolumns 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 UPDATEattribute, 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. SeeSection 5.1.10, “Server SQL Modes”.

    The nonstandard behaviors just described are deprecated and will be removed in a future MySQL release.

    If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMPcolumns 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_TIMESTAMPattributes. 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_timestampsystem variable at server startup.

    Note

    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 will have no purpose and will be removed as well.

    For additional information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

  • external_user

    Property Value
    System Variable external_user
    Scope Session
    Dynamic No
    SET_VAR Hint Applies 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.3.11, “Proxy Users”.

  • flush

    Property Value
    Command-Line Format --flush
    System Variable flush
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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.5.3.3, “What to Do If MySQL Keeps Crashing”. This variable is set to ON if you start mysqld with the --flush option.

    Note

    If flush is enabled, the value of flush_time does not matter and changes to flush_time have no effect on flush behavior.

  • flush_time

    Property Value
    Command-Line Format --flush-time=#
    System Variable flush_time
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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.

    Note

    If flush is enabled, the value of flush_time does not matter and changes to flush_time have no effect on flush behavior.

  • foreign_key_checks

    Property Value
    System Variable foreign_key_checks
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type boolean
    Default Value 1

    If set to 1 (the default), foreign key constraints for InnoDB tables 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.18.6, “Using FOREIGN KEY Constraints”.

    Typically you leave this setting enabled during normal operation, to enforce referential integrity. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships. SeeSection 15.8.1.6, “InnoDB and 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.

    Note

    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 will not be verified for consistency.

    Dropping an index required by a foreign key constraint is not permitted, even with foreign_key_checks=0. The foreign key constraint must be removed before dropping the index.

  • ft_boolean_syntax

    Property Value
    Command-Line Format --ft-boolean-syntax=name
    System Variable ft_boolean_syntax
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type string
    Default Value + -><()~*:""&|

    The list of operators supported by boolean full-text searches performed using IN BOOLEAN MODE. See Section 12.9.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.

  • ft_max_word_len

    Property Value
    Command-Line Format --ft-max-word-len=#
    System Variable ft_max_word_len
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Minimum Value 10

    The maximum length of the word to be included in a MyISAM FULLTEXT index.

    Note

    FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLEtbl_name QUICK.

  • ft_min_word_len

    Property Value
    Command-Line Format --ft-min-word-len=#
    System Variable ft_min_word_len
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 4
    Minimum Value 1

    The minimum length of the word to be included in a MyISAM FULLTEXT index.

    Note

    FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLEtbl_name QUICK.

  • ft_query_expansion_limit

    Property Value
    Command-Line Format --ft-query-expansion-limit=#
    System Variable ft_query_expansion_limit
    Scope Global
    Dynamic No
    SET_VAR Hint Applies 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.

  • ft_stopword_file

    Property Value
    Command-Line Format --ft-stopword-file=file_name
    System Variable ft_stopword_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies 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.9.4, “Full-Text Stopwords”.

    Note

    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.

  • general_log

    Property Value
    Command-Line Format --general-log
    System Variable general_log
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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 default value depends on whether the --general_log option is given. The destination for log output is controlled by the log_outputsystem variable; if that value is NONE, no log entries are written even if the log is enabled.

为您推荐

友情链接 |九搜汽车网 |手机ok生活信息网|ok生活信息网|ok微生活
 Powered by www.360SDN.COM   京ICP备11022651号-4 © 2012-2016 版权