9.1 Server System Variables
Controls the interval between successive column values.
determines the starting point for the AUTO_INCREMENT column value.
A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled.
The name of the file specified with the –init-file option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts. Each statement must be on a single line and should not include comments. No statement terminator such as ;, \g, or \G should be given at the end of each statement.
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. The maximum permissible setting for join_buffer_size is 4GB.
The maximum size of one packet or any generated/intermediate string.You must increase this value if you are using large BLOB columns or long strings.
If there are more than this number of interrupted connections from a host, that host is blocked from further connections. You can unblock blocked hosts with the FLUSH HOSTS statement. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero.
The maximum permitted number of simultaneous client connections. By default, this is 151, beginning with MySQL 5.1.15.
Do not use external locking (system locking). This affects only MyISAM table access.
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.
9.2 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 The optional GLOBAL keyword aggregates the values over all connections, and SESSIONshows the values for the current connection.
The number of connections that were aborted because the client died without closing the connection properly.
The number of failed attempts to connect to the MySQL server.
The maximum number of connections that have been in use simultaneously since the server started.
The number of statements executed by the server. This variable includes statements executed within stored programs.
The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs.
The number of joins that did a full scan.
The number of times that a request for a table lock could be granted immediately.
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.
The number of currently open connections.
The number of threads created to handle connections. If Threads_created is big, you may want to increase thethread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.
9.3 Server SQL Modes
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. 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.
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. The default value is empty.
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 SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.
You can retrieve the current global or session sql_mode value with the following statements:
mysql> SELECT @@GLOBAL.sql_mode;
mysql> SELECT @@SESSION.sql_mode;
The most important sql_mode values are probably these:
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. 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.
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.
Permit spaces between a function name and the “(” character.
Prevent the GRANT statement from automatically creating new users unless nonempty password is specified.
When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.
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.
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.
Treat || as a string concatenation operator.
Treat REAL as a synonym for FLOAT.
STRICT_TRANS_TABLES & STRICT_ALL_TABLES
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.
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 non-transactional 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.
This mode changes syntax and behavior to confirm more closely to standard SQL
REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI
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.
Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.
Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.