| 5.1.5. Using System Variables The MySQL server maintains many system variables that indicate how it is configured. Section 5.1.4, "Server System Variables", describes the meaning of these variables. 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 while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions. The server maintains two kinds of system variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value. Global and session system variables are related as follows: When the server starts, it initializes all global variables to their default values. These defaults can be changed by options specified on the command line or in an option file. (See Section 4.2.3, "Specifying Program Options".) The server also maintains a set of session variables for each client that connects. The client's session variables are initialized at connect time using the current values of the corresponding global variables. For example, the client's SQL mode is controlled by the session sql_mode value, which is initialized when the client connects to the value of the global sql_mode value.
System variable values can be set globally at server startup by using options on the command line or in an option file. When you use a startup option to set a variable that takes a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243; that is, units of kilobytes, megabytes, or gigabytes, respectively. Thus, the following command starts the server with a query cache size of 16 megabytes and a maximum packet size of one gigabyte: mysqld --query_cache_size=16M --max_allowed_packet=1G Within an option file, those variables are set like this: [mysqld]query_cache_size=16Mmax_allowed_packet=1G The lettercase of suffix letters does not matter; 16M and 16m are equivalent, as are 1G and 1g. If you want to restrict the maximum value to which a system variable can be set at runtime with the SET statement, you can specify this maximum by using an option of the form --maximum-var_name=value at server startup. For example, to prevent the value of query_cache_size from being increased to more than 32MB at runtime, use the option --maximum-query_cache_size=32M. Many system variables are dynamic and can be changed while the server runs by using the SET statement. For a list, see Section 5.1.5.2, "Dynamic System Variables". To change a system variable with SET, refer to it as var_name, optionally preceded by a modifier: To indicate explicitly that a variable is a global variable, precede its name by GLOBAL or @@global.. The SUPER privilege is required to set global variables. To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client. LOCAL and @@local. are synonyms for SESSION and @@session.. If no modifier is present, SET changes the session variable.
A SET statement can contain multiple variable assignments, separated by commas. If you set several system variables, the most recent GLOBAL or SESSION modifier in the statement is used for following variables that have no modifier specified. Examples: SET sort_buffer_size=10000;SET @@local.sort_buffer_size=10000;SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;SET @@sort_buffer_size=1000000;SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000; The @@var_name syntax for system variables is supported for compatibility with some other database systems. If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients. If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement). To prevent incorrect usage, MySQL produces an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you do not specify GLOBAL (or @@global.) when setting a global variable. To set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MySQL default value, use the DEFAULT keyword. For example, the following two statements are identical in setting the session value of max_join_size to the global value: SET max_join_size=DEFAULT;SET @@session.max_join_size=@@global.max_join_size; Not all system variables can be set to DEFAULT. In such cases, use of DEFAULT results in an error. You can refer to the values of specific global or session system variables in expressions by using one of the @@-modifiers. For example, you can retrieve values in a SELECT statement like this: SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode; When you refer to a system variable in an expression as @@var_name (that is, when you do not specify @@global. or @@session.), MySQL returns the session value if it exists and the global value otherwise. (This differs from SET @@var_name = value, which always refers to the session value.) Note Some variables displayed by SHOW VARIABLES may not be available using SELECT @@var_name syntax; an Unknown system variable occurs. As a workaround in such cases, you can use SHOW VARIABLES LIKE 'var_name'. Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not: shell> mysql --max_allowed_packet=16Mshell> mysql --max_allowed_packet=16*1024*1024 Conversely, the second of the following lines is legal at runtime, but the first is not: mysql> SET GLOBAL max_allowed_packet=16M;mysql> SET GLOBAL max_allowed_packet=16*1024*1024; Note Some system variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not. To display system variable names and values, use the SHOW VARIABLES statement: mysql> SHOW VARIABLES;+---------------------------------+-----------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------+| auto_increment_increment | 1 || auto_increment_offset | 1 || automatic_sp_privileges | ON || back_log | 50 || basedir | /home/mysql/ || binlog_cache_size | 32768 || bulk_insert_buffer_size | 8388608 || character_set_client | latin1 || character_set_connection | latin1 || character_set_database | latin1 || character_set_results | latin1 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /home/mysql/share/mysql/charsets/ || collation_connection | latin1_swedish_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci |...| innodb_additional_mem_pool_size | 1048576 || innodb_autoextend_increment | 8 || innodb_buffer_pool_size | 8388608 || innodb_checksums | ON || innodb_commit_concurrency | 0 || innodb_concurrency_tickets | 500 || innodb_data_file_path | ibdata1:10M:autoextend || innodb_data_home_dir | |...| version | 5.1.6-alpha-log || version_comment | Source distribution || version_compile_machine | i686 || version_compile_os | suse-linux || wait_timeout | 28800 |+---------------------------------+-----------------------------------+ With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, use a LIKE clause as shown: SHOW VARIABLES LIKE 'max_join_size';SHOW SESSION VARIABLES LIKE 'max_join_size'; To get a list of variables whose name match a pattern, use the "%" wildcard character in a LIKE clause: SHOW VARIABLES LIKE '%size%';SHOW GLOBAL VARIABLES LIKE '%size%'; Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because "_" is a wildcard that matches any single character, you should escape it as "\_" to match it literally. In practice, this is rarely necessary. For SHOW VARIABLES, if you specify neither GLOBAL nor SESSION, MySQL returns SESSION values. The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future. If we were to remove a SESSION variable that has the same name as a GLOBAL variable, a client with the SUPER privilege might accidentally change the GLOBAL variable rather than just the SESSION variable for its own connection. If we add a SESSION variable with the same name as a GLOBAL variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed. 5.1.5.1. Structured System Variables A structured variable differs from a regular system variable in two respects: Its value is a structure with components that specify server parameters considered to be closely related. There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.
MySQL 5.5 supports one structured variable type, which specifies parameters governing the operation of key caches. A key cache structured variable has these components: This section describes the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in Section 8.9.2, "The MyISAM Key Cache". To refer to a component of a structured variable instance, you can use a compound name in instance_name.component_name format. Examples: hot_cache.key_buffer_sizehot_cache.key_cache_block_sizecold_cache.key_cache_block_size For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus, default.key_buffer_size and key_buffer_size both refer to the same system variable. Structured variable instances and components follow these naming rules: For a given type of structured variable, each instance must have a name that is unique within variables of that type. However, instance names need not be unique across structured variable types. For example, each structured variable has an instance named default, so default is not unique across variable types. The names of the components of each structured variable type must be unique across all system variable names. If this were not true (that is, if two different types of structured variables could share component member names), it would not be clear which default structured variable to use for references to member names that are not qualified by an instance name. If a structured variable instance name is not legal as an unquoted identifier, refer to it as a quoted identifier using backticks. For example, hot-cache is not legal, but `hot-cache` is. global, session, and local are not legal instance names. This avoids a conflict with notation such as @@global.var_name for referring to nonstructured system variables.
Currently, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future. With one exception, you can refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option: shell> mysqld --hot_cache.key_buffer_size=64K In an option file, use this syntax: [mysqld]hot_cache.key_buffer_size=64K If you start the server with this option, it creates a key cache named hot_cache with a size of 64KB in addition to the default key cache that has a default size of 8MB. Suppose that you start the server as follows: shell> mysqld --key_buffer_size=256K \ --extra_cache.key_buffer_size=128K \ --extra_cache.key_cache_block_size=2048 In this case, the server sets the size of the default key cache to 256KB. (You could also have written --default.key_buffer_size=256K.) In addition, the server creates a second key cache named extra_cache that has a size of 128KB, with the size of block buffers for caching table index blocks set to 2048 bytes. The following example starts the server with three different key caches having sizes in a 3:1:1 ratio: shell> mysqld --key_buffer_size=6M \ --hot_cache.key_buffer_size=2M \ --cold_cache.key_buffer_size=2M Structured variable values may be set and retrieved at runtime as well. For example, to set a key cache named hot_cache to a size of 10MB, use either of these statements: mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024; To retrieve the cache size, do this: mysql> SELECT @@global.hot_cache.key_buffer_size; However, the following statement does not work. The variable is not interpreted as a compound name, but as a simple string for a LIKE pattern-matching operation: mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size'; This is the exception to being able to use structured variable names anywhere a simple variable name may occur. 5.1.5.2. Dynamic System Variables Many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also obtain their values using SELECT. See Section 5.1.5, "Using System Variables". The following table shows the full list of all dynamic system variables. The last column indicates for each variable whether GLOBAL or SESSION (or both) apply. The table also lists session options that can be set with the SET statement. Section 5.1.4, "Server System Variables", discusses these options. Variables that have a type of "string" take a string value. Variables that have a type of "numeric" take a numeric value. Variables that have a type of "boolean" can be set to 0, 1, ON or OFF. (If you set them on the command line or in an option file, use the numeric values.) Variables that are marked as "enumeration" normally should be set to one of the available values for the variable, but can also be set to the number that corresponds to the desired enumeration value. For enumerated system variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1. Table 5.3. Dynamic Variable Summary 5.1.6. Server Status Variables The server maintains many status variables that provide information about its operation. You can view these variables and their values by using the SHOW [GLOBAL | SESSION] STATUS statement (see Section 13.7.5.36, "SHOW STATUS Syntax"). The optional GLOBAL keyword aggregates the values over all connections, and SESSION shows the values for the current connection. mysql> SHOW GLOBAL STATUS;+-----------------------------------+------------+| Variable_name | Value |+-----------------------------------+------------+| Aborted_clients | 0 || Aborted_connects | 0 || Bytes_received | 155372598 || Bytes_sent | 1176560426 |...| Connections | 30023 || Created_tmp_disk_tables | 0 || Created_tmp_files | 3 || Created_tmp_tables | 2 |...| Threads_created | 217 || Threads_running | 88 || Uptime | 1389872 |+-----------------------------------+------------+ Many status variables are reset to 0 by the FLUSH STATUS statement. The following table lists all available server status variables: Table 5.4. Status Variable Summary The status variables have the following meanings. For meanings of status variables specific to MySQL Cluster, see Section 17.3.4.4, "MySQL Cluster Status Variables". Aborted_clients The number of connections that were aborted because the client died without closing the connection properly. See Section C.5.2.11, "Communication Errors and Aborted Connections". Aborted_connects The number of failed attempts to connect to the MySQL server. See Section C.5.2.11, "Communication Errors and Aborted Connections". Binlog_cache_disk_use The number of transactions that used the binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store changes from the transaction. In MySQL versions 5.5.3 through 5.5.8, this variable also included the number of nontransactional statements that caused the binary log transaction cache to be written to disk. Beginning with MySQL 5.5.9, the number of such nontransactional statements is tracked separately in the Binlog_stmt_cache_disk_use status variable. Binlog_cache_use The number of transactions that used the binary log cache. Binlog_stmt_cache_disk_use The number of nontransaction statements that used the binary log statement cache but that exceeded the value of binlog_stmt_cache_size and used a temporary file to store those statements. Binlog_stmt_cache_use The number of nontransactional statements that used the binary log statement cache. Bytes_received The number of bytes received from all clients. Bytes_sent The number of bytes sent to all clients. Com_xxx The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_insert count DELETE and INSERT statements, respectively. However, if a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select. See Section 8.9.3.4, "Query Cache Status and Maintenance". All of the Com_stmt_xxx variables are increased even if a prepared statement argument is unknown or an error occurred during execution. In other words, their values correspond to the number of requests issued, not to the number of requests successfully completed. The Com_stmt_xxx status variables are as follows: Com_stmt_prepare Com_stmt_execute Com_stmt_fetch Com_stmt_send_long_data Com_stmt_reset Com_stmt_close
Those variables stand for prepared statement commands. Their names refer to the COM_xxx command set used in the network layer. In other words, their values increase whenever prepared statement API calls such as mysql_stmt_prepare(), mysql_stmt_execute(), and so forth are executed. However, Com_stmt_prepare, Com_stmt_execute and Com_stmt_close also increase for PREPARE, EXECUTE, or DEALLOCATE PREPARE, respectively. Additionally, the values of the older statement counter variables Com_prepare_sql, Com_execute_sql, and Com_dealloc_sql increase for the PREPARE, EXECUTE, and DEALLOCATE PREPARE statements. Com_stmt_fetch stands for the total number of network round-trips issued when fetching from cursors. Com_stmt_reprepare indicates the number of times statements were automatically reprepared by the server after metadata changes to tables or views referred to by the statement. A reprepare operation increments Com_stmt_reprepare, and also Com_stmt_prepare. Compression Whether the client connection uses compression in the client/server protocol. Connections The number of connection attempts (successful or not) to the MySQL server. Created_tmp_disk_tables The number of internal on-disk temporary tables created by the server while executing statements. If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size values. value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. See also Section 8.4.3.3, "How MySQL Uses Internal Temporary Tables". Created_tmp_files How many temporary files mysqld has created. Created_tmp_tables The number of internal temporary tables created by the server while executing statements. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. See also Section 8.4.3.3, "How MySQL Uses Internal Temporary Tables". Each invocation of the SHOW STATUS statement uses an internal temporary table and increments the global Created_tmp_tables value. Delayed_errors The number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key). Delayed_insert_threads The number of INSERT DELAYED handler threads in use. Delayed_writes The number of INSERT DELAYED rows written. Flush_commands The number of times the server flushes tables, whether because a user executed a FLUSH TABLES statement or due to internal server operation. It is also incremented by receipt of a COM_REFRESH packet. This is in contrast to Com_flush, which indicates how many FLUSH statements have been executed, whether FLUSH TABLES, FLUSH LOGS, and so forth. Handler_commit The number of internal COMMIT statements. Handler_delete The number of times that rows have been deleted from tables. Handler_prepare A counter for the prepare phase of two-phase commit operations. Handler_read_first The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed. Handler_read_key The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries. Handler_read_last The number of requests to read the last key in an index. With ORDER BY, the server will issue a first-key request followed by several next-key requests, whereas with With ORDER BY DESC, the server will issue a last-key request followed by several previous-key requests. This variable was added in MySQL 5.5.7. Handler_read_next The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan. Handler_read_prev The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC. Handler_read_rnd The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly. Handler_read_rnd_next The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. Handler_rollback The number of requests for a storage engine to perform a rollback operation. Handler_savepoint The number of requests for a storage engine to place a savepoint. Handler_savepoint_rollback The number of requests for a storage engine to roll back to a savepoint. Handler_update The number of requests to update a row in a table. Handler_write The number of requests to insert a row in a table. Innodb_buffer_pool_pages_data The number of pages containing data (dirty or clean). Innodb_buffer_pool_pages_dirty The number of pages currently dirty. Innodb_buffer_pool_pages_flushed The number of buffer pool page-flush requests. Innodb_buffer_pool_pages_free The number of free pages. Innodb_buffer_pool_pages_latched The number of latched pages in InnoDB buffer pool. These are pages currently being read or written or that cannot be flushed or removed for some other reason. Calculation of this variable is expensive, so it is available only when the UNIV_DEBUG system is defined at server build time. Innodb_buffer_pool_pages_misc The number of pages that are busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index. This value can also be calculated as Innodb_buffer_pool_pages_total � Innodb_buffer_pool_pages_free � Innodb_buffer_pool_pages_data. Innodb_buffer_pool_pages_total The total size of the buffer pool, in pages. Innodb_buffer_pool_read_ahead The number of pages read into the InnoDB buffer pool by the read-ahead background thread. Innodb_buffer_pool_read_ahead_evicted The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries. Innodb_buffer_pool_read_requests The number of logical read requests InnoDB has done. Innodb_buffer_pool_reads The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from the disk. Innodb_buffer_pool_wait_free Normally, writes to the InnoDB buffer pool happen in the background. However, if it is necessary to read or create a page and no clean pages are available, it is also necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size has been set properly, this value should be small. Innodb_buffer_pool_write_requests The number writes done to the InnoDB buffer pool. Innodb_data_fsyncs The number of fsync() operations so far. Innodb_data_pending_fsyncs The current number of pending fsync() operations. Innodb_data_pending_reads The current number of pending reads. Innodb_data_pending_writes The current number of pending writes. Innodb_data_read The amount of data read since the server was started. Innodb_data_reads The total number of data reads. Innodb_data_writes The total number of data writes. Innodb_data_written The amount of data written so far, in bytes. Innodb_dblwr_pages_written The number of pages that have been written for doublewrite operations. See Section 14.3.12.1, "InnoDB Disk I/O". Innodb_dblwr_writes The number of doublewrite operations that have been performed. See Section 14.3.12.1, "InnoDB Disk I/O". Innodb_have_atomic_builtins Indicates whether the server was built with atomic instructions. Innodb_log_waits The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing. Innodb_log_write_requests The number of log write requests. Innodb_log_writes The number of physical writes to the log file. Innodb_os_log_fsyncs The number of fsync() writes done to the log file. Innodb_os_log_pending_fsyncs The number of pending log file fsync() operations. Innodb_os_log_pending_writes The number of pending log file writes. Innodb_os_log_written The number of bytes written to the log file. Innodb_page_size The compiled-in InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be easily converted to bytes. Innodb_pages_created The number of pages created. Innodb_pages_read The number of pages read. Innodb_pages_written The number of pages written. Innodb_row_lock_current_waits The number of row locks currently being waited for. Innodb_row_lock_time The total time spent in acquiring row locks, in milliseconds. Innodb_row_lock_time_avg The average time to acquire a row lock, in milliseconds. Innodb_row_lock_time_max The maximum time to acquire a row lock, in milliseconds. Innodb_row_lock_waits The number of times a row lock had to be waited for. Innodb_rows_deleted The number of rows deleted from InnoDB tables. Innodb_rows_inserted The number of rows inserted into InnoDB tables. Innodb_rows_read The number of rows read from InnoDB tables. Innodb_rows_updated The number of rows updated in InnoDB tables. Innodb_truncated_status_writes The number of times output from the SHOW ENGINE INNODB STATUS is truncated. Monitoring applications that parse the output from this command can test this value before and after issuing the SHOW ENGINE command, to confirm if the output is complete or not. Key_blocks_not_flushed The number of key blocks in the key cache that have changed but have not yet been flushed to disk. Key_blocks_unused The number of unused blocks in the key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size in Section 5.1.4, "Server System Variables". Key_blocks_used The number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time. Key_read_requests The number of requests to read a key block from the cache. Key_reads The number of physical reads of a key block from disk. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests. Key_write_requests The number of requests to write a key block to the cache. Key_writes The number of physical writes of a key block to disk. Last_query_cost The total cost of the last compiled query as computed by the query optimizer. This is useful for comparing the cost of different query plans for the same query. The default value of 0 means that no query has been compiled yet. The default value is 0. Last_query_cost has session scope. The Last_query_cost value can be computed accurately only for simple "flat" queries, not complex queries such as those with subqueries or UNION. For the latter, the value is set to 0. Max_used_connections The maximum number of connections that have been in use simultaneously since the server started. Not_flushed_delayed_rows The number of rows waiting to be written in INSERT DELAYED queues. Open_files The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so. Open_streams The number of streams that are open (used mainly for logging). Open_table_definitions The number of cached .frm files. Open_tables The number of tables that are open. Opened_files The number of files that have been opened with my_open() (a mysys library function). Parts of the server that open files without using this function do not increment the count. Opened_table_definitions The number of .frm files that have been cached. Opened_tables The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small. Performance_schema_xxx Performance Schema status variables are listed in Section 21.9, "Performance Schema Status Variables". Prepared_stmt_count The current number of prepared statements. (The maximum number of statements is given by the max_prepared_stmt_count system variable.) Qcache_free_blocks The number of free memory blocks in the query cache. Qcache_free_memory The amount of free memory for the query cache. Qcache_hits The number of query cache hits. Qcache_inserts The number of queries added to the query cache. Qcache_lowmem_prunes The number of queries that were deleted from the query cache because of low memory. Qcache_not_cached The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting). Qcache_queries_in_cache The number of queries registered in the query cache. Qcache_total_blocks The total number of blocks in the query cache. Queries The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands. Questions The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands. Rpl_semi_sync_master_clients The number of semisynchronous slaves. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_net_avg_wait_time The average time in microseconds the master waited for a slave reply. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_net_wait_time The total time in microseconds the master waited for slave replies. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_net_waits The total number of times the master waited for slave replies. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_no_times The number of times the master turned off semisynchronous replication. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_no_tx The number of commits that were not acknowledged successfully by a slave. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_status Whether semisynchronous replication currently is operational on the master. The value is ON if the plugin has been enabled and a commit acknowledgment has occurred. It is OFF if the plugin is not enabled or the master has fallen back to asynchronous replication due to commit acknowledgment timeout. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_timefunc_failures The number of times the master failed when calling time functions such as gettimeofday(). This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_tx_avg_wait_time The average time in microseconds the master waited for each transaction. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_tx_wait_time The total time in microseconds the master waited for transactions. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_tx_waits The total number of times the master waited for transactions. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_wait_pos_backtraverse The total number of times the master waited for an event with binary coordinates lower than events waited for previously. This can occur when the order in which transactions start waiting for a reply is different from the order in which their binary log events are written. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_wait_sessions The number of sessions currently waiting for slave replies. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_master_yes_tx The number of commits that were acknowledged successfully by a slave. This variable is available only if the master-side semisynchronous replication plugin is installed. Rpl_semi_sync_slave_status Whether semisynchronous replication currently is operational on the slave. This is ON if the plugin has been enabled and the slave I/O thread is running, OFF otherwise. This variable is available only if the slave-side semisynchronous replication plugin is installed. Rpl_status The status of fail-safe replication (not implemented). This variable is unused and is removed in MySQL 5.6. Select_full_join The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables. Select_full_range_join The number of joins that used a range search on a reference table. Select_range The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large. Select_range_check The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables. Select_scan The number of joins that did a full scan of the first table. Slave_heartbeat_period Shows the replication heartbeat interval (in seconds) on a replication slave. Slave_open_temp_tables The number of temporary tables that the slave SQL thread currently has open. If the value is greater than zero, it is not safe to shut down the slave; see Section 16.4.1.21, "Replication and Temporary Tables". Slave_received_heartbeats This counter increments with each replication heartbeat received by a replication slave since the last time that the slave was restarted or reset, or a CHANGE MASTER TO statement was issued. Slave_retried_transactions The total number of times since startup that the replication slave SQL thread has retried transactions. Slave_running This is ON if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is OFF. Slow_launch_threads The number of threads that have taken more than slow_launch_time seconds to create. Slow_queries The number of queries that have taken more than long_query_time seconds. See Section 5.2.5, "The Slow Query Log". Sort_merge_passes The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable. Sort_range The number of sorts that were done using ranges. Sort_rows The number of sorted rows. Sort_scan The number of sorts that were done by scanning the table. Ssl_accept_renegotiates The number of negotiates needed to establish the connection. Ssl_accepts The number of accepted SSL connections. Ssl_callback_cache_hits The number of callback cache hits. Ssl_cipher The current SSL cipher (empty for non-SSL connections). Ssl_cipher_list The list of possible SSL ciphers. Ssl_client_connects The number of SSL connection attempts to an SSL-enabled master. Ssl_connect_renegotiates The number of negotiates needed to establish the connection to an SSL-enabled master. Ssl_ctx_verify_depth The SSL context verification depth (how many certificates in the chain are tested). Ssl_ctx_verify_mode The SSL context verification mode. Ssl_default_timeout The default SSL timeout. Ssl_finished_accepts The number of successful SSL connections to the server. Ssl_finished_connects The number of successful slave connections to an SSL-enabled master. Ssl_session_cache_hits The number of SSL session cache hits. Ssl_session_cache_misses The number of SSL session cache misses. Ssl_session_cache_mode The SSL session cache mode. Ssl_session_cache_overflows The number of SSL session cache overflows. Ssl_session_cache_size The SSL session cache size. Ssl_session_cache_timeouts The number of SSL session cache timeouts. Ssl_sessions_reused How many SSL connections were reused from the cache. Ssl_used_session_cache_entries How many SSL session cache entries were used. Ssl_verify_depth The verification depth for replication SSL connections. Ssl_verify_mode The verification mode for replication SSL connections. Ssl_version The SSL version number. Table_locks_immediate The number of times that a request for a table lock could be granted immediately. Table_locks_waited The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. Tc_log_max_pages_used For the memory-mapped implementation of the log that is used by mysqld when it acts as the transaction coordinator for recovery of internal XA transactions, this variable indicates the largest number of pages used for the log since the server started. If the product of Tc_log_max_pages_used and Tc_log_page_size is always significantly less than the log size, the size is larger than necessary and can be reduced. (The size is set by the --log-tc-size option. Currently, this variable is unused: It is unneeded for binary log-based recovery, and the memory-mapped recovery log method is not used unless the number of storage engines capable of two-phase commit is greater than one. (InnoDB is the only applicable engine.) Tc_log_page_size The page size used for the memory-mapped implementation of the XA recovery log. The default value is determined using getpagesize(). Currently, this variable is unused for the same reasons as described for Tc_log_max_pages_used. Tc_log_page_waits For the memory-mapped implementation of the recovery log, this variable increments each time the server was not able to commit a transaction and had to wait for a free page in the log. If this value is large, you might want to increase the log size (with the --log-tc-size option). For binary log-based recovery, this variable increments each time the binary log cannot be closed because there are two-phase commits in progress. (The close operation waits until all such transactions are finished.) Threads_cached The number of threads in the thread cache. Threads_connected The number of currently open connections. Threads_created The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections. Threads_running The number of threads that are not sleeping. Uptime The number of seconds that the server has been up. Uptime_since_flush_status The number of seconds since the most recent FLUSH STATUS statement.
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements. For answers to some questions that are often asked about server SQL modes in MySQL, see Section B.3, "MySQL 5.5 FAQ: Server SQL Mode". Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. When working with InnoDB tables, consider also the innodb_strict_mode configuration option. It enables additional error checks for InnoDB tables, as listed in Section 14.4.8.4, "InnoDB Strict Mode". Setting the SQL Mode You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option, or by using sql-mode="modes" in my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by comma (",") characters. The default value is empty (no modes set). The modes value also can be empty (--sql-mode="" on the command line, or sql-mode="" in my.cnf on Unix systems or in my.ini on Windows) if you want to clear it explicitly. You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system value. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time. Important SQL mode and user-defined partitioning. Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data. It is strongly recommended that you never change the SQL mode once you have created tables employing user-defined partitioning. When replicating partitioned tables, differing SQL modes on master and slave can also lead to problems. For best results, you should always use the same server SQL mode on the master and on the slave. See Section 18.5, "Restrictions and Limitations on Partitioning", for more information. You can retrieve the current global or session sql_mode value with the following statements: SELECT @@GLOBAL.sql_mode;SELECT @@SESSION.sql_mode; Most Important SQL Modes The most important sql_mode values are probably these: ANSI This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section. STRICT_TRANS_TABLES If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. TRADITIONAL Make MySQL behave like a "traditional" SQL database system. A simple description of this mode is "give an error instead of a warning" when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section. Note The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a nontransactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a "partially done" update.
When this manual refers to "strict mode," it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled. Full List of SQL Modes The following list describes all supported modes: ALLOW_INVALID_DATES Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date. The server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. ANSI_QUOTES Treat """ as an identifier quote character (like the "`" quote character) and not as a string quote character. You can still use "`" to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier. ERROR_FOR_DIVISION_BY_ZERO Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL. HIGH_NOT_PRECEDENCE The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode. mysql> SET sql_mode = '';mysql> SELECT NOT 1 BETWEEN -5 AND 5; -> 0mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';mysql> SELECT NOT 1 BETWEEN -5 AND 5; -> 1 IGNORE_SPACE Permit spaces between a function name and the "(" character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 9.2, "Schema Object Names". For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error: mysql> CREATE TABLE count (i INT);ERROR 1064 (42000): You have an error in your SQL syntax The table name should be quoted: mysql> CREATE TABLE `count` (i INT);Query OK, 0 rows affected (0.00 sec) The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always permissible to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled. For further discussion of IGNORE_SPACE, see Section 9.2.4, "Function Name Parsing and Resolution". NO_AUTO_CREATE_USER Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH. NO_AUTO_VALUE_ON_ZERO NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem. NO_BACKSLASH_ESCAPES Disable the use of the backslash character ("\") as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other. NO_DIR_IN_CREATE When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers. NO_ENGINE_SUBSTITUTION Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in. Because storage engines can be pluggable at runtime, unavailable engines are treated the same way: With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered. With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable. NO_FIELD_OPTIONS Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. NO_KEY_OPTIONS Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. NO_TABLE_OPTIONS Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. NO_UNSIGNED_SUBTRACTION By default, subtraction between integer operands produces an UNSIGNED result if any operand isUNSIGNED. When NO_UNSIGNED_SUBTRACTION is enabled, the subtraction result is signed, even if any operand is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 in table t2: mysql> SET sql_mode='';mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;mysql> DESCRIBE t1;+-------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| c2 | bigint(21) unsigned | | | 0 | |+-------+---------------------+------+-----+---------+-------+mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;mysql> DESCRIBE t2;+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| c2 | bigint(21) | | | 0 | |+-------+------------+------+-----+---------+-------+ Note that this means that BIGINT UNSIGNED is not 100% usable in all contexts. See Section 12.10, "Cast Functions and Operators". mysql> SET sql_mode = '';mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+| CAST(0 AS UNSIGNED) - 1 |+-------------------------+| 18446744073709551615 |+-------------------------+mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+| CAST(0 AS UNSIGNED) - 1 |+-------------------------+| -1 |+-------------------------+ NO_ZERO_DATE In strict mode, do not permit '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated. NO_ZERO_IN_DATE In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If used with the IGNORE option, MySQL inserts a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated. ONLY_FULL_GROUP_BY Do not permit queries for which the select list or or HAVING list refers to nonaggregated columns that are not named in the GROUP BY clause. The following queries are invalid with ONLY_FULL_GROUP_BY enabled. The first is invalid because address in the select list is not named in the GROUP BY clause, and the second because max_age in the HAVING clause is not named in the GROUP BY clause: mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;ERROR 1055 (42000): 't.address' isn't in GROUP BY mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name -> HAVING max_age < 30;Empty set (0.00 sec)ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause In the second example, the query could be rewritten to use HAVING MAX(age) instead, so that the reference is to a column named in an aggregate function. (max_age fails because it is an aggregate function.) In addition, if a query has aggregate functions and no GROUP BY clause, it cannot have nonaggregated columns in the select list or ORDER BY list: mysql> SELECT name, MAX(age) FROM t;ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)with no GROUP columns is illegal if there is no GROUP BY clause For more information, see Section 12.16.3, "MySQL Extensions to GROUP BY". PAD_CHAR_TO_FULL_LENGTH By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval. mysql> CREATE TABLE t1 (c1 CHAR(10));Query OK, 0 rows affected (0.37 sec)mysql> INSERT INTO t1 (c1) VALUES('xy');Query OK, 1 row affected (0.01 sec)mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;+------+-----------------+| c1 | CHAR_LENGTH(c1) |+------+-----------------+| xy | 2 |+------+-----------------+1 row in set (0.00 sec)mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';Query OK, 0 rows affected (0.00 sec)mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;+------------+-----------------+| c1 | CHAR_LENGTH(c1) |+------------+-----------------+| xy | 10 |+------------+-----------------+1 row in set (0.00 sec) PIPES_AS_CONCAT Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR. REAL_AS_FLOAT Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE. STRICT_ALL_TABLES Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows. STRICT_TRANS_TABLES Enable strict mode for transactional storage engines, and when possible for nontransactional storage engines. Additional details follow.
Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back. For nontransactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled: For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it is best to use single-row statements because these can be aborted without changing the table. For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.5, "Data Type Default Values".
Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero month or day parts such as '2004-04-00' or "zero" dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode. If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See Section 13.7.5.41, "SHOW WARNINGS Syntax". Strict mode does not affect whether foreign key constraints are checked. foreign_key_checks can be used for that. (See Section 5.1.4, "Server System Variables".) Combination Modes The following special modes are provided as shorthand for combinations of mode values from the preceding list. The descriptions include all mode values that are available in the most recent version of MySQL. For older versions, a combination mode does not include individual mode values that are not available except in newer versions. ANSI Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. ANSI mode also causes the server to return an error for queries where a set function S with an outer reference S(outer_ref) cannot be aggregated in the outer query against which the outer reference has been resolved. This is such a query: SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...); Here, MAX(t1.b) cannot aggregated in the outer query because it appears in the WHERE clause of that query. Standard SQL requires an error in this situation. If ANSI mode is not enabled, the server treats S(outer_ref) in such queries the same way that it would interpret S(const). See Section 1.8.3, "Running MySQL in ANSI Mode". DB2 Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS. MAXDB Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER. MSSQL Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS. MYSQL323 Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE. MYSQL40 Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE. ORACLE Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER. POSTGRESQL Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS. TRADITIONAL Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
MySQL supports a plugin API that enables creation of server components. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server. The components supported by this interface include, but are not limited to, storage engines, full-text parser plugins, partitioning support, and server extensions. 5.1.8.1. Installing and Uninstalling Plugins Server plugins must be loaded in to the server before they can be used. MySQL enables you to load a plugin at server startup or at runtime. It is also possible to control the activation of loaded plugins at startup, and to unload them at runtime. Installing Plugins Server plugins must be known to the server before they can be used. A plugin can be made known several ways, as described here. In the following descriptions, plugin_name stands for a plugin name such as innodb or csv. Built-in plugins: A plugin that is built in to the server is known by the server automatically. Normally, the server enables the plugin at startup, although this can be changed with the --plugin_name option. Plugins registered in the mysql.plugin table: The mysql.plugin table serves as a registry of plugins. The server normally enables each plugin listed in the table at startup, although whether a given plugin is enabled can be changed with the --plugin_name option. If the server is started with the --skip-grant-tables option, it does not consult this table and does not load the plugins listed there. Plugins named with the --plugin-load option: A plugin that is located in a plugin library file can be loaded at server startup with the --plugin-load option. Normally, the server enables the plugin at startup, although this can be changed with the --plugin_name option. The option value is a semicolon-separated list of name=plugin_library pairs. Each name is the name of the plugin, and plugin_library is the name of the shared library that contains the plugin code. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. Each library file must be located in the directory named by the plugin_dir system variable. This option does not register any plugin in the mysql.plugin table. For subsequent restarts, the server loads the plugin again only if --plugin-load is given again. That is, this option effects a one-time installation that persists only for one server invocation. --plugin-load enables plugins to be loaded even when --skip-grant-tables is given (which causes the server to ignore the mysql.plugin table). --plugin-load also enables plugins to be loaded at startup under configurations when plugins cannot be loaded at runtime. Plugins installed with the INSTALL PLUGIN statement: A plugin that is located in a plugin library file can be loaded at runtime with the INSTALL PLUGIN statement. The statement also registers the plugin in the mysql.plugin table to cause the server to load it on subsequent restarts. For this reason, INSTALL PLUGIN requires the INSERT privilege for the mysql.plugin table. If a plugin is named both using a --plugin-load option and in the mysql.plugin table, the server starts but writes these messages to the error log: 100310 19:15:44 [ERROR] Function 'plugin_name' already exists100310 19:15:44 [Warning] Couldn't load plugin named 'plugin_name'with soname 'plugin_object_file'. Example: The --plugin-load option installs a plugin at server startup. To install a plugin named myplugin in a plugin library file named somepluglib.so, use these lines in a my.cnf file: [mysqld]plugin-load=myplugin=somepluglib.so In this case, the plugin is not registered in mysql.plugin. Restarting the server without the --plugin-load option causes the plugin not to be loaded at startup. Alternatively, the INSTALL PLUGIN statement causes the server to load the plugin code from the library file at runtime: mysql> INSTALL PLUGIN myplugin SONAME 'somepluglib.so'; INSTALL PLUGIN also causes "permanent" plugin registration: The server lists the plugin in the mysql.plugin table to ensure that it is loaded on subsequent server restarts. Many plugins can be loaded either at server startup or at runtime. However, if a plugin is designed such that it must be loaded and initialized during server startup, use --plugin-load rather than INSTALL PLUGIN. While a plugin is loaded, information about it is available at runtime from several sources, such as the INFORMATION_SCHEMA.PLUGINS table and the SHOW PLUGINS statement. For more information, see Section 5.1.8.2, "Obtaining Server Plugin Information". Controlling Plugin Activation If the server knows about a plugin when it starts (for example, because the plugin is named using a --plugin-load option or registered in the mysql.plugin table), the server loads and enables the plugin by default. It is possible to control activation for such a plugin using a --plugin_name[=value] startup option named after the plugin. In the following descriptions, plugin_name stands for a plugin name such as innodb or csv. As with other options, dashes and underscores are interchangeable in option names. For example, --my_plugin=ON and --my-plugin=ON are equivalent. --plugin_name=OFF Tells the server to disable the plugin. --plugin_name[=ON] Tells the server to enable the plugin. (Specifying the option as --plugin_name without a value has the same effect.) If the plugin fails to initialize, the server runs with the plugin disabled. --plugin_name=FORCE Tells the server to enable the plugin, but if plugin initialization fails, the server does not start. In other words, this option forces the server to run with the plugin enabled or not at all. --plugin_name=FORCE_PLUS_PERMANENT Like FORCE, but in addition prevents the plugin from being unloaded at runtime. If a user attempts to do so with UNINSTALL PLUGIN, an error occurs. This value is available as of MySQL 5.5.7.
The values OFF, ON, FORCE, and FORCE_PLUS_PERMANENT are not case sensitive. The activation state for plugins is visible in the LOAD_OPTION column of the INFORMATION_SCHEMA.PLUGINS table. Suppose that CSV, BLACKHOLE, and ARCHIVE are built-in pluggable storage engines and that you want the server to load them at startup, subject to these conditions: The server is permitted to run if CSV initialization fails, but must require that BLACKHOLE initialization succeeds, and ARCHIVE should be disabled. To accomplish that, use these lines in an option file: [mysqld]csv=ONblackhole=FORCEarchive=OFF The --enable-plugin_name option format is supported as a synonym for --plugin_name=ON. The --disable-plugin_name and --skip-plugin_name option formats are supported as synonyms for --plugin_name=OFF. Before MySQL 5.1.36, plugin options are boolean options (see Section 4.2.3.2, "Program Option Modifiers"). That is, any of these options enable the plugin: --plugin_name--plugin_name=1--enable-plugin_name And these options disable the plugin: --plugin_name=0--disable-plugin_name--skip-plugin_name If you upgrade to MySQL 5.5 from a version older than 5.1.36 and previously used options of the form --plugin_name=0 or --plugin_name=1, the equivalent options are now --plugin_name=OFF and --plugin_name=ON, respectively. You also have the choice of requiring plugins to start successfully by using --plugin_name=FORCE or --plugin_name=FORCE_PLUS_PERMANENT. If a plugin is disabled, either explicitly with OFF or implicitly because it was enabled with ON but failed to initialize, aspects of server operation that require the plugin will change. For example, if the plugin implements a storage engine, existing tables for the storage engine become inaccessible, and attempts to create new tables for the storage engine result in tables that use the default storage engine unless the NO_ENGINE_SUBSTITUTION SQL mode has been enabled to cause an error to occur instead. Disabling a plugin may require adjustment to other options. For example, if you start the server using --skip-innodb to disable InnoDB, other innodb_xxx options likely will need to be omitted from the startup command. In addition, because InnoDB is the default storage engine, it will not start unless you specify another available storage engine with --default-storage-engine. Uninstalling Plugins A plugin known to the server can be uninstalled to disable it at runtime with the UNINSTALL PLUGIN statement. The statement unloads the plugin and removes it from the mysql.plugin table if it is registered there. For this reason, UNINSTALL PLUGIN statement requires the DELETE privilege for the mysql.plugin table. With the plugin no longer registered in the table, the server will not load the plugin automatically for subsequent restarts. UNINSTALL PLUGIN can unload plugins regardless of whether they were loaded with INSTALL PLUGIN or --plugin-load. UNINSTALL PLUGIN is subject to these exceptions: It cannot unload plugins that are built in to the server. These can be identified as those that have a library name of NULL in the output from INFORMATION_SCHEMA.PLUGINS or SHOW PLUGINS. It cannot unload plugins for which the server was started with --plugin_name=FORCE_PLUS_PERMANENT, which prevents plugin unloading at runtime. These can be identified from the LOAD_OPTION column of the INFORMATION_SCHEMA.PLUGINS table.
5.1.8.2. Obtaining Server Plugin Information There are several ways to determine which plugins are installed in the server: The INFORMATION_SCHEMA.PLUGINS table contains a row for each loaded plugin. Any that have a PLUGIN_LIBRARY value of NULL are built in and cannot be unloaded. mysql> SELECT * FROM information_schema.PLUGINS\G*************************** 1. row *************************** PLUGIN_NAME: binlog PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 50158.0 PLUGIN_LIBRARY: NULLPLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: MySQL AB PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction PLUGIN_LICENSE: GPL LOAD_OPTION: FORCE...*************************** 10. row *************************** PLUGIN_NAME: InnoDB PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 50158.0 PLUGIN_LIBRARY: ha_innodb_plugin.soPLUGIN_LIBRARY_VERSION: 1.0 PLUGIN_AUTHOR: Innobase Oy PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys PLUGIN_LICENSE: GPL LOAD_OPTION: ON... The SHOW PLUGINS statement displays a row for each loaded plugin. Any that have a Library value of NULL are built in and cannot be unloaded. mysql> SHOW PLUGINS\G*************************** 1. row *************************** Name: binlog Status: ACTIVE Type: STORAGE ENGINELibrary: NULLLicense: GPL...*************************** 10. row *************************** Name: InnoDB Status: ACTIVE Type: STORAGE ENGINELibrary: ha_innodb_plugin.soLicense: GPL... The mysql.plugin table shows which plugins have been registered with INSTALL PLUGIN. The table contains only plugin names and library file names, so it does not provide as much information as the PLUGINS table or the SHOW PLUGINS statement.
As of MySQL 5.5.3, support for IPv6 includes these capabilities: MySQL Server can accept TCP/IP connections from clients connecting over IPv6. For example, this command connects over IPv6 to the MySQL server on the local host: shell> mysql -h ::1 To use this capability, two things must be true: MySQL account names permit IPv6 addresses to enable DBAs to specify privileges for clients that connect to the server over IPv6. See Section 6.2.3, "Specifying Account Names". IPv6 addresses can be specified in account names in statements such as CREATE USER, GRANT, and REVOKE. For example: mysql> CREATE USER 'bill'@'::1' IDENTIFIED BY 'secret';mysql> GRANT SELECT ON mydb.* TO 'bill'@'::1';
The following sections describe how to set up MySQL so that clients can connect to the server over IPv6. 5.1.9.1. Verifying System Support for IPv6 Before MySQL Server can accept IPv6 connections, the operating system on your server host must support IPv6. As a simple test to determine whether that is true, try this command: shell> ping6 ::116 bytes from ::1, icmp_seq=0 hlim=64 time=0.171 ms16 bytes from ::1, icmp_seq=1 hlim=64 time=0.077 ms... To produce a description of your system's network interfaces, invoke ifconfig -a and look for IPv6 addresses in the output. If your host does not support IPv6, consult your system documentation for instructions on enabling it. It might be that you need only reconfigure an existing network interface to add an IPv6 address. Or a more extensive change might be needed, such as rebuilding the kernel with IPv6 options enabled. These links may be helpful in setting up IPv6 on various platforms: 5.1.9.2. Configuring the MySQL Server to Permit IPv6 Connections 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. The default address is 0.0.0.0. To specify an address explicitly, use the --bind-address=addr option at server startup, where addr is an IPv4 or IPv6 address or a host name. (IPv6 addresses are not supported before MySQL 5.5.3.) If addr is a host name, the server resolves the name to an IP address and binds to that address. The server treats different types of addresses as follows: 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. Use this address to permit both IPv4 and IPv6 connections on all server 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 you intend to bind the server to a specific address, be sure that the mysql.user grant table contains an account with administrative privileges that you can use to connect to that address. Otherwise, you will not be 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. 5.1.9.3. Connecting Using the IPv6 Local Host Address The following procedure shows how to configure MySQL to permit IPv6 connections by clients that connect to the local server using the ::1 local host address. The instructions given here assume that your system supports IPv6. Start the MySQL server with an appropriate --bind-address option to permit it to accept IPv6 connections. For example, put the following lines in your server option file and restart the server: [mysqld]bind-address = :: Alternatively, you can bind the server to ::1, but that makes the server more restrictive for TCP/IP connections. It accepts only IPv6 connections for that single address and rejects IPv4 connections. For more information, see Section 5.1.9.2, "Configuring the MySQL Server to Permit IPv6 Connections". As an administrator, connect to the server and create an account for a local user who will connect from the ::1 local IPv6 host address: mysql> CREATE USER 'ipv6user'@'::1' IDENTIFIED BY 'ipv6pass'; For the permitted syntax of IPv6 addresses in account names, see Section 6.2.3, "Specifying Account Names". In addition to the CREATE USER statement, you can issue GRANT statements that give specific privileges to the account, although that is not necessary for the remaining steps in this procedure. Invoke the mysql client to connect to the server using the new account: shell> mysql -h ::1 -u ipv6user -pipv6pass Try some simple statements that show connection information: mysql> STATUS...Connection: ::1 via TCP/IP...mysql> SELECT CURRENT_USER();+----------------+| CURRENT_USER() |+----------------+| ipv6user@::1 |+----------------+
5.1.9.4. Connecting Using IPv6 Nonlocal Host Addresses The following procedure shows how to configure MySQL to permit IPv6 connections by remote clients. It is similar to the preceding procedure for local clients, but the server and client hosts are distinct and each has its own nonlocal IPv6 address. The example uses these addresses: Server host: 2001:db8:0:f101::1Client host: 2001:db8:0:f101::2 These addresses are chosen from the nonroutable address range recommended by IANA for documentation purposes and suffice for testing on your local network. To accept IPv6 connections from clients outside the local network, the server host must have a public address. If your network provider assigns you an IPv6 address, you can use that. Otherwise, another way to obtain an address is to use an IPv6 broker; see Section 5.1.9.5, "Obtaining an IPv6 Address from a Broker". Start the MySQL server with an appropriate --bind-address option to permit it to accept IPv6 connections. For example, put the following lines in your server option file and restart the server: [mysqld]bind-address = :: Alternatively, you can bind the server to 2001:db8:0:f101::1, but that makes the server more restrictive for TCP/IP connections. It accepts only IPv6 connections for that single address and rejects IPv4 connections. For more information, see Section 5.1.9.2, "Configuring the MySQL Server to Permit IPv6 Connections". On the server host (2001:db8:0:f101::1), create an account for a user who will connect from the client host (2001:db8:0:f101::2): mysql> CREATE USER 'remoteipv6user'@'2001:db8:0:f101::2' IDENTIFIED BY 'remoteipv6pass'; On the client host (2001:db8:0:f101::2), invoke the mysql client to connect to the server using the new account: shell> mysql -h 2001:db8:0:f101::1 -u remoteipv6user -premoteipv6pass Try some simple statements that show connection information: mysql> STATUS...Connection: 2001:db8:0:f101::1 via TCP/IP...mysql> SELECT CURRENT_USER();+-----------------------------------+| CURRENT_USER() |+-----------------------------------+| remoteipv6user@2001:db8:0:f101::2 |+-----------------------------------+
5.1.9.5. Obtaining an IPv6 Address from a Broker If you do not have a public IPv6 address that enables your system to communicate over IPv6 outside your local network, you can obtain one from an IPv6 broker. The Wikipedia IPv6 Tunnel Broker page lists several brokers and their features, such as whether they provide static addresses and the supported routing protocols. After configuring your server host to use a broker-supplied IPv6 address, start the MySQL server with an appropriate --bind-address option to permit the server to accept IPv6 connections. For example, put the following lines in the server option file and restart the server: [mysqld]bind-address = :: Alternatively, you can bind the server to to the specific IPv6 address provided by the broker, but that makes the server more restrictive for TCP/IP connections. It accepts only IPv6 connections for that single address and rejects IPv4 connections. For more information, see Section 5.1.9.2, "Configuring the MySQL Server to Permit IPv6 Connections". In addition, if the broker allocates dynamic addresses, the address provided for your system might change the next time you connect to the broker. If so, any accounts you create that name the original address become invalid. To bind to a specific address but avoid this change-of-address problem, you may be able to arrange with the broker for a static IPv6 address. The following example shows how to use Freenet6 as the broker and the gogoc IPv6 client package on Gentoo Linux. Create a account at Freenet6 by visiting this URL and signing up: http://gogonet.gogo6.com After creating the account, go to this URL, sign in, and create a user ID and password for the IPv6 broker: http://gogonet.gogo6.com/page/freenet6-registration As root, install gogoc: shell> emerge gogoc Edit /etc/gogoc/gogoc.conf to set the userid and password values. For example: userid=gogouserpasswd=gogopass Start gogoc: shell> /etc/init.d/gogoc start To start gogoc each time your system boots, execute this command: shell> rc-update add gogoc default Use ping6 to try to ping a host: shell> ping6 ipv6.google.com To see your IPv6 address: shell> ifconfig tun
MySQL Server supports a HELP statement that returns online information from the MySQL Reference manual (see Section 13.8.3, "HELP Syntax"). The proper operation of this statement requires that the help tables in the mysql database be initialized with help topic information, which is done by processing the contents of the fill_help_tables.sql script. If you install MySQL using a binary or source distribution on Unix, help table setup occurs when you run mysql_install_db. For an RPM distribution on Linux or binary distribution on Windows, help table setup occurs as part of the MySQL installation process. If you upgrade MySQL using a binary distribution, the help tables are not upgraded automatically, but you can upgrade them manually. Locate the fill_help_tables.sql file in the share or share/mysql directory. Change location into that directory and process the file with the mysql client as follows: shell> mysql -u root mysql < fill_help_tables.sql You can also obtain the latest fill_help_tables.sql at any time to upgrade your help tables. Download the proper file for your version of MySQL from http://dev.mysql.com/doc/index-other.html. After downloading and uncompressing the file, process it with mysql as described previously. If you are working with Bazaar and a MySQL development source tree, you will need to download the fill_help_tables.sql file because the tree contains only a "stub" version. 5.1.11. Server Response to Signals On Unix, signals can be sent to processes. mysqld responds to signals sent to it as follows: SIGTERM causes the server to shut down. SIGHUP causes the server to reload the grant tables and flush the logs (like FLUSH PRIVILEGES and FLUSH LOGS). It also writes a status report to the error log that has this format: Status information:Current dir: /var/mysql/data/Running threads: 0 Stack size: 196608Current locks:Key caches:defaultBuffer_size: 8388600Block_size: 1024Division_limit: 100Age_limit: 300blocks used: 0not flushed: 0w_requests: 0writes: 0r_requests: 0reads: 0handler status:read_key: 0read_next: 0read_rnd 0read_first: 1write: 0delete 0update: 0Table status:Opened tables: 5Open tables: 0Open files: 7Open streams: 0Alarm status:Active alarms: 1Max used alarms: 2Next alarm time: 67
On some Mac OS X 10.3 versions, mysqld ignores SIGHUP and SIGQUIT. 5.1.12. The Shutdown Process The server shutdown process takes place as follows: The shutdown process is initiated. This can occur initiated several ways. For example, a user with the SHUTDOWN privilege can execute a mysqladmin shutdown command. mysqladmin can be used on any platform supported by MySQL. Other operating system-specific shutdown initiation methods are possible as well: The server shuts down on Unix when it receives a SIGTERM signal. A server running as a service on Windows shuts down when the services manager tells it to. The server creates a shutdown thread if necessary. Depending on how shutdown was initiated, the server might create a thread to handle the shutdown process. If shutdown was requested by a client, a shutdown thread is created. If shutdown is the result of receiving a SIGTERM signal, the signal thread might handle shutdown itself, or it might create a separate thread to do so. If the server tries to create a shutdown thread and cannot (for example, if memory is exhausted), it issues a diagnostic message that appears in the error log: Error: Can't create thread to kill server The server stops accepting new connections. To prevent new activity from being initiated during shutdown, the server stops accepting new client connections by closing the handlers for the network interfaces to which it normally listens for connections: the TCP/IP port, the Unix socket file, the Windows named pipe, and shared memory on Windows. The server terminates current activity. For each thread associated with a client connection, the server breaks the connection to the client and marks the thread as killed. Threads die when they notice that they are so marked. Threads for idle connections die quickly. Threads that currently are processing statements check their state periodically and take longer to die. For additional information about thread termination, see Section 13.7.6.4, "KILL Syntax", in particular for the instructions about killed REPAIR TABLE or OPTIMIZE TABLE operations on MyISAM tables. For threads that have an open transaction, the transaction is rolled back. Note that if a thread is updating a nontransactional table, an operation such as a multiple-row UPDATE or INSERT may leave the table partially updated because the operation can terminate before completion. If the server is a master replication server, it treats threads associated with currently connected slaves like other client threads. That is, each one is marked as killed and exits when it next checks its state. If the server is a slave replication server, it stops the I/O and SQL threads, if they are active, before marking client threads as killed. The SQL thread is permitted to finish its current statement (to avoid causing replication problems), and then stops. If the SQL thread is in the middle of a transaction at this point, the server waits until the current replication event group (if any) has finished executing, or until the user issues a KILL QUERY or KILL CONNECTION statement. See also Section 13.4.2.6, "STOP SLAVE Syntax". If the slave is updating a non-transactional table when it is forcibly killed, the slave's data may become inconsistent with the master. The server shuts down or closes storage engines. At this stage, the server flushes the table cache and closes all open tables. Each storage engine performs any actions necessary for tables that it manages. For example, MyISAM flushes any pending index writes for a table. InnoDB flushes its buffer pool to disk (unless innodb_fast_shutdown is 2), writes the current LSN to the tablespace, and terminates its own internal threads. The server exits.
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |
