Changes in MySQL 5.7.7 (2015-04-08, Release Candidate)

Functionality Added or Changed

• Important Change; InnoDB: The following changes were made to InnoDB configuration option default values:

  • The innodb_file_format default value was changed to Barracuda. The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.

  • The innodb_large_prefix default value was changed to ON. The previous default was OFF. When innodb_file_format is set to Barracuda, innodb_large_prefix=ON allows index key prefixes longer than 767 bytes (up to 3072 bytes) for tables that use a Compressed or Dynamic row format.

  • The innodb_strict_mode default value was changed to ON. The previous default was OFF. When innodb_strict_mode is enabled, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior).

  The configuration parameter default changes described above may affect replication and mysqldump operations. Consider the following recommendations when using the new default settings:

  • When replicating or replaying mysqldump data from older MySQL versions to MySQL 5.7.7 or higher, consider setting innodb_strict_mode to OFF to avoid errors. Target settings should not be more strict than source settings.

  • When replicating from MySQL 5.7.7 or higher to older slaves, consider setting innodb_file_format=Barracuda and innodb_large_prefix=ON on the slave so that the target and source have the same settings.

  The following file format related configuration options are deprecated and will be removed in a future MySQL version:

  • innodb_file_format

  • innodb_file_format_check

  • innodb_file_format_max

  • innodb_large_prefix

  These four configuration options were provided for creating tables compatible with earlier versions of InnoDB in MySQL 5.1. Now that MySQL 5.1 has reached the end of its product lifecycle, these options are no longer required. Also, the file format scheme, by which named file formats would be introduced as new features were added, was not used after introduction of the Barracuda file format. InnoDB formats have changed since the introduction of the Barracuda file format, but new named file formats have not been added.

  If non-default values are used for any of the four deprecated options, InnoDB prints a deprecation and removal warning to the server error log. The same warning is issued to the client if the parameters are set dynamically using a SET statement.

• Important Change; InnoDB: The innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup configuration options are now enabled by default. With this change, a percentage of most-recently-used buffer pool pages is dumped at server shutdown and restored at server startup. This behavior helps avoid a lengthy buffer pool warmup period after restarting the server, particularly for instances with large buffer pools. The innodb_buffer_pool_dump_pct option defines the percentage of buffer pool pages that are dumped. The default value for innodb_buffer_pool_dump_pct is reduced from 100 to 25.

  These configuration option default value changes represent a change in behaviour at server shutdown and startup. If you prefer the previous default values, it is recommended that you configure the options explicitly in your MySQL configuration file after upgrading to MySQL 5.7.7 or later, and before restarting the server.

  For more information, see Saving and Restoring the Buffer Pool State.

• Important Change; InnoDB: The innodb_checksum_algorithm default value is now crc32. The previous default setting was innodb. This change also means that innodb_checksums=ON is now equivalent to innodb_checksum_algorithm=crc32 instead of innodb_checksum_algorithm=innodb.

• InnoDB: The InnoDB MeCab full-text parser plugin now supports the eucjpms, cp932, and utf8mb4 character sets. (Bug #20534096)

• InnoDB: To address a scalability bottleneck for some workloads where LOCK_grant is locked in read-mode, LOCK_grant locks are now partitioned. Read lock requests on LOCK_grant now acquire one of multiple LOCK_grant partitions. Write locks must acquire all partitions.

  To address another scalability bottleneck, the server no longer performs unnecessary lock acquisitions when creating interal temporary tables.

  References: See also: Bug #72829, Bug #20023139.

• Replication: The defaults of some replication related variables have been modified. The following changes have been made:

  • binlog_gtid_simple_recovery=TRUE

  • binlog-format=ROW

  • binlog_error_action=ABORT_SERVER

  • sync_binlog=1

  • slave_net_timeout=60

  Additionally, the session scope of gtid_executed has been deprecated. The global scope of gtid_executed remains supported.

• Replication: The XA implementation in MySQL has been made much more compatible with the XA specification. A prepared XA transaction is no longer rolled back at disconnect. Now when replicating using the binary log, an XA transaction in PREPARED state persists in the binary log until an explicit XA COMMIT or XA ROLLBACK statement is issued. In prior versions, an XA transaction that was in PREPARED state would be rolled back on clean server shutdown or client disconnect. Similarly, an XA transaction that was in PREPARED state would still exist in PREPARED state in case the server was shut down abnormally and then started again, but the contents of the transaction could not be written to the binary log. As part of this feature, a new event, XA_prepare_log_event, has been added to track XA transactions in the PREPARED state and enable them to be replicated. To finalize a two-phase XA transaction, the XA COMMIT or XA ROLLBACK is recorded separately in the binary log, possibly interleaving with other transactions. XA transactions committed with the new XA COMMIT ONE PHASE syntax are logged as one part using XA_prepare_log_event.

  References: See also: Bug #12161, Bug #11745231, Bug #75204, Bug #20214365.

• Performance Schema stage event instruments that provide statement progress information now are enabled and timed by default. The affected instruments are those displayed by this statement:

  SELECT * FROM performance_schema.setup_instruments WHERE
  ENABLED='YES' AND NAME LIKE "stage/%";

  (Bug #20685859)

• The XID column of Performance Schema transaction tables (for example, events_transactions_current) has been split into three columns to permit access to the component parts of XID values. The new columns are XID_FORMAT_ID, XID_GTRID, and XID_BQUAL. (Bug #18320361)

• In MySQL 5.7.6, the NO_AUTO_CREATE_USER was deprecated. (It is preferable to create MySQL accounts with CREATE USER rather than GRANT.) Now the default SQL mode includes NO_AUTO_CREATE_USER and assignments to sql_mode that change the NO_AUTO_CREATE_USER mode state produce a warning, except assignments that set sql_mode to DEFAULT. NO_AUTO_CREATE_USER will be removed in a future MySQL version, at which point its effect will be enabled at all times (GRANT will not create accounts).

• Previously, view definitions were not permitted to contain derived tables (subqueries) in the FROM clause. This restriction has now been lifted.

  Previously, derived tables (subqueries) or views in the FROM clause could not be merged into the outer query if they contained subqueries in the SELECT list. Instead, they were processed using materialization. This restriction has now been lifted so that queries previously executed using materialization can be executed more quickly using merging. (Bug #12755, Bug #11745276, Bug #60417, Bug #11865600)