Mysql：Changes in MySQL 5.6.6 (2012-08-07, Milestone 9)：Group-Commit等等：重大变化版本！

Changes in MySQL 5.6.6 (2012-08-07, Milestone 9)

Note

This is a milestone release, for use at your own risk. Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. Significant development changes take place in milestone releases and you may encounter compatibility issues, such as data format changes that require attention in addition to the usual procedure of running mysql_upgrade. For example, you may find it necessary to dump your data with mysqldump before the upgrade and reload it afterward. (Making a backup before the upgrade is a prudent precaution in any case.)

• Binary Logging

• Configuration Notes

• Performance Schema Notes

• Security Notes

• Functionality Added or Changed

• Bugs Fixed

Binary Logging

• Performance: The server now implements group commit for the binary log: Multiple commits are grouped in memory, then written and flushed to disk as a group rather than individually. This reduces the number of writes and flushes, improving performance of binary logging. Group commit works for all storage engines. InnoDB implements some optimizations to take advantage of group commit capability.

  These system variables were added in conjunction with group commit:

  • binlog_order_commits: Whether to commit transactions in the same order they are written to the binary log or permit them to be committed in parallel.

  • binlog_max_flush_queue_time: How long in microseconds to keep reading transactions from the flush queue before proceeding with the group commit.

  • innodb_flush_log_at_timeout: Write and flush logs every N seconds.

Configuration Notes

• This MySQL release implements changes to the default values of several server parameters. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually. These changes are subject to revision in future releases as we gain feedback. (See Changes to Server Defaults.)

  In some cases, a parameter has a different fixed default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a fixed value. For example, the setting for back_log is its previous default of 50, adjusted up by an amount proportional to the value of max_connections. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.

  The following table summarizes changes to defaults. For variables that are autosized, the main variable description provides additional detail about the sizing algorithm. See Server System Variables, and InnoDB Startup Options and System Variables. Any of these default settings can be overridden by specifying an explicit value at server startup.

  Parameter	Old Default	New Default
  back_log	50	Autosized using max_connections
  binlog_checksum	NONE	CRC32
  --binlog-row-event-max-size	1024	8192
  flush_time	1800 (on Windows)	0
  innodb_autoextend_increment	8	64
  innodb_buffer_pool_instances	1	8 (platform dependent)
  innodb_checksum_algorithm	INNODB	CRC32
  innodb_concurrency_tickets	500	5000
  innodb_file_per_table	0	1
  innodb_old_blocks_time	0	1000
  innodb_open_files	300	Autosized using innodb_file_per_table, table_open_cache
  innodb_stats_on_metadata	ON	OFF
  join_buffer_size	128KB	256KB
  max_allowed_packet	1MB	4MB
  max_connect_errors	10	100
  performance_schema	OFF	ON
  performance_schema_events_waits_history_long_size	10000	Autosized
  performance_schema_events_waits_history_size	10	Autosized
  performance_schema_max_cond_instances	1000	Autosized
  performance_schema_max_file_instances	10000	Autosized
  performance_schema_max_mutex_instances	1000000	Autosized
  performance_schema_max_rwlock_instances	1000000	Autosized
  performance_schema_max_table_handles	100000	Autosized
  performance_schema_max_table_instances	50000	Autosized
  performance_schema_max_thread_instances	1000	Autosized
  sql_mode	'' (empty string)	NO_ENGINE_SUBSTITUTION
  sync_master_info	0	10000
  sync_relay_log	0	10000
  sync_relay_log_info	0	10000

  With regard to compatibility with previous releases, the most important changes are:

  • innodb_file_per_table is enabled (previously disabled)

  • innodb_checksum_algorithm is CRC32 (previously INNODB)

  • binlog_checksum is CRC32 (previously NONE)

  Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:

  [mysqld]
  innodb_file_per_table=0
  innodb_checksum_algorithm=INNODB
  binlog_checksum=NONE

  Those settings preserve compatibility as follows:

  • With the new default of innodb_file_per_table enabled, ALTER TABLE operations following an upgrade will move InnoDB tables that are in the system tablespace to individual .ibd files. Using innodb_file_per_table=0 will prevent this from happening.

  • Setting innodb_checksum_algorithm=INNODB permits binary downgrades after upgrading to this release. With a setting of CRC32, InnoDB would use checksumming that older MySQL versions cannot use.

  • With binlog_checksum=NONE, the server can be used as a replication master without causing failure of older slaves that do not understand binary log checksums.

Performance Schema Notes

• The Performance Schema is now enabled by default (the performance_schema system variable is enabled by default). To disable it, set performance_schema=off at server startup.

  In addition, the Performance Schema now automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For example, it sizes the parameters that control the sizes of the events waits tables this way. To see which parameters are sized under this policy, use mysqld --verbose --help and look for those with a default value of −1, or see Performance Schema System Variables.

  For each autosized parameter that is not set at server startup (or is set to −1), the Performance Schema determines how to set its value based on the value of the following system values, which are considered as “hints” about how you have configured your MySQL server:

  max_connections
  open_files_limit
  table_definition_cache
  table_open_cache

  To override autosizing for a given parameter, set it a value other than −1 at startup. In this case, the Performance Schema assigns it the specified value.

  At runtime, SHOW VARIABLES displays the actual values that autosized parameters were set to.

  If the Performance Schema is disabled, its autosized parameters remain set to −1 and SHOW VARIABLES displays −1.

Security Notes

• These security improvements were implemented:

  • MySQL now provides a method for storing authentication credentials encrypted in an option file named .mylogin.cnf. To create the file, use the mysql_config_editor utility. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to a MySQL server. mysql_config_editor writes the .mylogin.cnf file using encryption so the credentials are not stored as clear text, and its contents when decrypted by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable. For more information, see mysql_config_editor — MySQL Configuration Utility.

    The .mylogin.cnf file can contain multiple sets of options, known as “login paths.” This makes it easy to set up multiple “personalities” for connecting to different MySQL servers. Any of these can be selected by name later using the --login-path option when you invoke a client program. See Command-Line Options that Affect Option-File Handling.

  • MySQL now supports stronger encryption for user account passwords, available through an authentication plugin named sha256_password that implements SHA-256 password hashing. This plugin is built in, so it is always available and need not be loaded explicitly. For more information, including instructions for creating accounts that use SHA-256 passwords, see SHA-256 Pluggable Authentication.

    Other changes associated with the introduction of the sha256_password plugin:

    • The old_passwords system variable previously permitted values of 1 or 0 to control whether “old” or “new” MySQL native password hashing was used by the CREATE USER and GRANT statements and the PASSWORD() function. Now old_passwords permits a value of 2 to select use of SHA-256 password hashing.

      Note

      Previously, old_passwords permitted values of OFF or ON as synonyms for 0 or 1. That is no longer true.

    • SHA-256 password hashing (old_passwords=2) uses a random salt value, which makes the result from PASSWORD() nondeterministic. Consequently, statements that use this function are no longer safe for statement-based replication and cannot be stored in the query cache.

    • If MySQL is built with OpenSSL, RSA encryption can be used to transmit passwords during the client connection process. The sha256_password_private_key_path and sha256_password_public_key_path system variables permit the private and public key files to be named on the server side. The Rsa_public_key status variable displays the public key value. The mysql and mysqltest clients support a --server-public-key option permitting the public key file to be specified explicitly when connecting to the server. (This option is implemented through a new MYSQL_SERVER_PUBLIC_KEY option to the mysql_options() C API function.)

    MySQL Connector support: Connectors that use the C client library should work with sha256_password with no changes. Connectors that implement the authentication process for themselves must be updated to account for changes in the client/server protocol.

  • The server now has a --default-authentication-plugin option to specify the default plugin to associate with new accounts for which no plugin is named explicitly. Permitted values are mysql_native_password (use MySQL native passwords; this is the default value) and sha256_password (use SHA-256 passwords). This option also changes the initial old_passwords value to be consistent with the password hashing method required by the default plugin, if necessary.

    Note

    If you use this option to change the default authentication method to a value other than mysql_native_password, clients older than MySQL 5.5.7 will no longer be able to connect because they will not understand the change to the authentication protocol.

  • The mysql.user table now has a password_expired column to enable DBAs to expire account passwords and require users to reset their password. The default password_expired value is 'N', but can be set to 'Y' with the new ALTER USER statement. After an account's password has been expired, all operations performed by the account in subsequent connections to the server result in an error until the user issues a SET PASSWORD statement to establish a new account password. For more information, see ALTER USER Statement, and Server Handling of Expired Passwords.

    If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate this change into the mysql database.

    Caution

    Update: ALTER USER also set the Password column to the empty string, so do not use this statement in 5.6.6. This problem has been fixed in MySQL 5.6.7.

  • MySQL now has provision for checking password security:

    • In statements that assign a password supplied as a cleartext value, the value is checked against the current password policy and rejected if it is weak (the statement returns an ER_NOT_VALID_PASSWORD error). This affects the CREATE USER, GRANT, and SET PASSWORD statements. Passwords given as arguments to the PASSWORD() and OLD_PASSWORD() functions are checked as well.

    • The strength of potential passwords can be assessed using the new VALIDATE_PASSWORD_STRENGTH() SQL function, which takes a password argument and returns an integer from 0 (weak) to 100 (strong).

    Both capabilities are implemented by the validate_password plugin. If the plugin is not installed, the affected statements and PASSWORD() and OLD_PASSWORD() work as before (no password checking), and VALIDATE_PASSWORD_STRENGTH() always returns 0.

    The validate_password plugin also implements a set of system variables corresponding to the parameters that control password checking. If the plugin is installed, you can modify these variables to configure the password policy.

    The validate_password plugin is written using the MySQL plugin API, which has been extended to support writing password-validation plugins.

    For more information, see The Password Validation Plugin. For information about writing password-checking plugins, see Writing Password-Validation Plugins.

  • mysql_upgrade now produces a warning if it finds user accounts with passwords hashed with the older pre-4.1 hashing method. Such accounts should be updated to use more secure password hashing. See Password Hashing in MySQL

  (Bug #65461, Bug #14136939)

• For the WITH_SSL CMake option, no is no longer a permitted value or the default value. The default is now bundled. Consequently, MySQL now is always built with SSL support.

Functionality Added or Changed

• Incompatible Change: It is now explicitly disallowed to assign the value DEFAULT to stored procedure or function parameters or stored program local variables (for example with a SET var_name = DEFAULT statement). This was not previously supported, or documented as permitted, but is flagged as an incompatible change in case existing code inadvertently used this construct. It remains permissible to assign DEFAULT to system variables, as before, but assigning DEFAULT to parameters or local variables now results in a syntax error.

  After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.

• Incompatible Change: The --safe-mode server option has been removed.

• Important Change; Partitioning: MySQL nows supports partition lock pruning, which allows for many DDL and DML statements against partitioned tables using MyISAM (or another storage engine that employs table-level locking) to lock only those partitions directly affected by the statement. These statements include (but are not limited to) many SELECT, SELECT ... PARTITION, UPDATE, REPLACE, INSERT, and other statements. This enhancement improves especially the performance of many such statements when used with tables having many (32 or more) partitions. For a complete list of affected statements with particulars, and other information, see Partitioning and Locking. (Bug #37252, Bug #11748732)

• Important Change; Replication: It is now possible, in the event that a multithreaded slave fails while running with the --relay-log-recovery option, to switch it safely to single-threaded mode despite the presence of any gaps with unprocessed transactions in the relay log. To accomplish this, you can now use START SLAVE [SQL_THREAD] UNTIL SQL_AFTER_MTS_GAPS to cause the slave SQL threads to run until no more such gaps are found in the relay log. Once this statement has completed, you can change the slave_parallel_workers system variable, and (if necessary) issue a CHANGE MASTER TO statement before restarting the slave. (Bug #13893363)

  References: See also: Bug #13893310.

• Important Change; Replication: INSERT ON DUPLICATE KEY UPDATE is now marked as unsafe for statement-based replication if the target table has more than one primary or unique key. For more information, see Determination of Safe and Unsafe Statements in Binary Logging. (Bug #58637, Bug #11765650, Bug #13038678)

• Important Change; Replication: The SHOW BINARY LOGS statement (and its equivalent SHOW MASTER LOGS) may now be executed by a user with the REPLICATION CLIENT privilege. (Formerly, the SUPER privilege was necessary to use either form of this statement.)

• Important Change: INSERT DELAYED is now deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead. (Bug #13985071)

• Important Change: In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types:

  • TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types permit NULL values if not explicitly declared with the NOT NULL attribute.) Setting such a column to NULL sets it to the current timestamp.

  • The first TIMESTAMP column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

  • TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT attribute, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

  Those nonstandard behaviors remain the default for TIMESTAMP but now are deprecated and this warning appears at startup:

  [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
  Please use --explicit_defaults_for_timestamp server option (see
  documentation for more details).

  As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp system variable at server startup. With this variable enabled, the server handles TIMESTAMP as follows instead:

  • TIMESTAMP columns permit NULL values if not explicitly declared with the NOT NULL attribute. Setting such a column to NULL sets it to NULL, not the current timestamp.

  • No TIMESTAMP column is assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes must be explicitly specified.

  • TIMESTAMP columns declared as NOT NULL and without an explicit DEFAULT attribute are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME.

  To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp:

  1. Bring down the slaves, upgrade them, configure them with the desired value of explicit_defaults_for_timestamp, and bring them back up.

     The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of explicit_defaults_for_timestamp) and that operations on TIMESTAMP columns coming from the master use the old TIMESTAMP behavior.

  2. Bring down the master, upgrade it, and configure it with the same explicit_defaults_for_timestamp value used on the slaves, and bring it back up.

  (Bug #63034, Bug #13344629, Bug #55131, Bug #11762529)

• Important Change: The YEAR(2) data type is now deprecated because it is problematic. YEAR(2) columns in existing tables are treated as before, but YEAR(2) in new or altered tables are converted to YEAR(4). Support for YEAR(2) will be removed entirely in a future MySQL release. For more information, see 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR.

• Performance; InnoDB: Many DDL operations on InnoDB tables can now be performed online, without making the tables unavailable for queries. Some operations, such as creating or dropping indexes, even allow DML statements (INSERT, UPDATE, DELETE) on the table while the operation is in progress. A single online DDL operation can also take the place of a sequence of statements, such as several DROP INDEX statements, ALTER TABLE ... ADD COLUMN, and then several CREATE INDEX statements. See InnoDB and Online DDL for full details.

  An additional effect of this change occurs for consistent-read transactions that try to reread data from a table which was changed by ALTER TABLE in another session. Instead of receiving an empty set, the transaction will receive an error (ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”). (Bug #58368, Bug #11765404, Bug #11872643, Bug #12325508, Bug #11765266, Bug #60689)

• Performance; InnoDB: The persistent statistics feature for InnoDB tables is now enabled by default, and can be controlled at the level of individual tables. This feature involves the configuration options innodb_stats_persistent, innodb_stats_auto_recalc, and innodb_stats_persistent_sample_pages, and the clauses STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES of the CREATE TABLE and ALTER TABLE statements. See Configuring Persistent Optimizer Statistics Parameters for usage details.

• Performance; InnoDB: The MySQL server now includes the widely used memcached in-memory caching system, and a plugin that allows fast NoSQL-style access to InnoDB tables through the memcached protocol. This access method avoids the overhead of SQL parsing and constructing a query optimization plan. You can store the underlying data in a single InnoDB table, or spread it across multiple tables. You can read and write data through both memcached and SQL. For example, you can do fast single-key lookups through memcached get calls, and do statistical reports across all the data through SQL.

  Several configuration options let you fine-tune this system, in particular to balance raw performance against durability and consistency of data. The main new configuration options are daemon_memcached_option, daemon_memcached_r_batch_size, daemon_memcached_w_batch_size, innodb_api_trx_level, innodb_api_enable_mdl, and innodb_api_enable_binlog.

  See InnoDB memcached Plugin for full details.

• InnoDB: For systems with constant heavy workloads, or workloads that fluctuate widely, several new configuration options let you fine-tune the flushing behavior for InnoDB tables: innodb_adaptive_flushing_lwm, innodb_max_dirty_pages_pct_lwm, innodb_max_io_capacity (changed in subsequent point releases to innodb_io_capacity_max), and innodb_flushing_avg_loops. These options feed into an improved formula used by the innodb_adaptive_flushing option. See Configuring Buffer Pool Flushing.

• InnoDB: InnoDB tables now support the notion of “transportable tablespaces”, allowing .ibd files to be exported from a running MySQL instance and imported into another running instance. The FOR EXPORT clause of the FLUSH TABLES statement writes any unsaved changes from InnoDB memory buffers to the .ibd file. After copying the .ibd file and a separate metadata file to the other server, you can use the DISCARD TABLESPACE and IMPORT TABLESPACE clauses of the ALTER TABLE statement to bring the table data into a different MySQL instance.

  For more information, see Importing InnoDB Tables.

• InnoDB: InnoDB now supports the DATA DIRECTORY='directory' clause of the CREATE TABLE statement, which permits creating tables outside the data directory. For more information, see Creating Tables Externally.

• Replication: The STOP SLAVE option SQL_BEFORE_GTIDS did not function correctly, and the SQL_AFTER_GTIDS option for the same statement did not function at all. (Bug #13810456)

• Replication: Added the slave_rows_search_algorithms system variable for mysqld, which determines the search algorithms used for finding matches for slave updates when slave_allow_batching is enabled, including whether or not table or index hashing is used with searches employing a primary or unique key, some other key, or no key.

• The Performance Schema has a new system variable, performance_schema_session_connect_attrs_size, and new status variable, Performance_schema_session_connect_attrs_lost. The system variable is the amount of preallocated memory per thread reserved to hold connection attribute key/value pairs. If the aggregate size of connection attribute data sent by a client is larger than this amount, the Performance Schema truncates the attribute data and increments the status variable. See Performance Schema Connection Attribute Tables. (Bug #14076427)

• yaSSL was upgraded from version 1.7.2 to 2.1.4. (Bug #13713205)

  References: See also: Bug #13706828.

• The optimizer's cost model for disk-sweep Multi-Read Range (DS-MRR) has been improved. The improved cost model makes it more likely that DSMRR will be used for queries that read much data from disk.

• Previously, the default value for the bind_address system variable was 0.0.0.0, which causes the server to accept TCP/IP connections on all server host IPv4 interfaces. To make it easier to use IPv6 connections without special configuration, the default bind_address value now is *. This is similar to 0.0.0.0, but causes the server to also accept TCP/IP connections on all IPv6 interfaces if the server host supports IPv6. (Another way to accept IPv4 and IPv6 connections is by using bind_address=::, but in this case an error occurs if the server host does not support IPv6.)

• It is now possible for client programs to pass connection attributes to the server in the form of key/value pairs. Attributes are manipulated using the MYSQL_OPT_CONNECT_ATTR_RESET and MYSQL_OPT_CONNECT_ATTR_DELETE options for the mysql_options() C API function, and the MYSQL_OPT_CONNECT_ATTR_ADD option for the new mysql_options4() function. Connection attributes are exposed through the session_connect_attrs and session_account_connect_attrs Performance Schema tables.

  If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database.

  For more information, see C API Function Descriptions, and MySQL Performance Schema.

• Previously, for semijoin processing the outer query specification was limited to simple table scans or inner joins using comma syntax, and view references were not possible. Now outer join and inner join syntax is permitted in the outer query specification, and the restriction that table references must be base tables has been lifted.

• To improve scalability by reducing contention among sessions for the global lock on the open tables cache, the cache now can be partitioned into several smaller cache instances. A session now need lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that need to use the cache when many there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)

  A new system variable, table_open_cache_instances, permits control over the number of cache instances. Each instance has a size of table_open_cache / table_open_cache_instances. By default, the number of instances is 1.

  Three new status variables provide information about the operation of the open tables cache. Table_open_cache_hits and Table_open_cache_misses indicate the number of hits and misses or lookups in the cache. Table_open_cache_overflows indicates how many times, after a table is opened or closed, an instance has an unused entry and the size of the instance is larger than table_open_cache / table_open_cache_instances.

• The generic “procedure API” has been removed from the server. This was formerly present as a means of writing server procedures, but went unused except for PROCEDURE ANALYSE(). Removing the interface simplifies aspects of the internal procedure representation that were related to code no longer in the server but had a negative effect on its operation, in the sense that these aspects hindered the ability of the optimizer to perform better on more common query types. In addition, this code hindered future optimizer development and its removal will have benefit that development.

  PROCEDURE ANALYSE() remains available, but is no longer implemented using a public interface. (For information, see Using PROCEDURE ANALYSE.) One consequence of removing the procedure interface is that EXPLAIN SELECT ... PROCEDURE ANALYSE() now works where previously it produced an error.