This section summarizes what has been added to, deprecated in, and removed from MySQL 5.6. A companion section lists MySQL server options and variables that have been added, deprecated, or removed in MySQL 5.6; see Section 1.4, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.6”.
The following features have been added to MySQL 5.6:
Security improvements. These security improvements were made:
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 Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.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 Section 6.4.1.4, “SHA-256 Pluggable Authentication”.The
mysql.user
system table now has apassword_expired
column. Its default value is'N'
, but can be set to'Y'
with the newALTER USER
statement. After an account's password has been expired, all operations performed in subsequent connections to the server using the account result in an error until the user issues aSET PASSWORD
statement to establish a new account password. For more information, see Section 13.7.1.1, “ALTER USER Statement”, and Section 6.2.10, “Server Handling of Expired Passwords”.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 theCREATE USER
,GRANT
, andSET PASSWORD
statements. Passwords given as arguments to thePASSWORD()
andOLD_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. For more information, see Section 6.4.3, “The Password Validation Plugin”.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 Section 6.1.2.4, “Password Hashing in MySQL”
On Unix platforms, mysql_install_db supports a new option,
--random-passwords
, that provides for more secure MySQL installation. Invoking mysql_install_db with--random-passwords
causes it to assign a random password to the MySQLroot
accounts, set the “password expired” flag for those accounts, and remove the anonymous-user MySQL accounts. For additional details, see Section 4.4.3, “mysql_install_db — Initialize MySQL Data Directory”.Logging has been modified so that passwords do not appear in plain text in statements written to the general query log, slow query log, and binary log. See Section 6.1.2.3, “Passwords and Logging”.
The mysql client no longer logs to its history file statements that refer to passwords. See Section 4.5.1.3, “mysql Client Logging”.
START SLAVE
syntax has been modified to permit connection parameters to be specified for connecting to the source. This provides an alternative to storing the password in themaster.info
file. See Section 13.4.2.5, “START SLAVE Statement”.MySQL now sets the access control granted to clients on the named pipe to the minimum necessary for successful communication on Windows. Newer MySQL client software can open named pipe connections without any additional configuration. If older client software cannot be upgraded immediately, the new
named_pipe_full_access_group
system variable can be used to give a Windows group the necessary permissions to open a named pipe connection. Membership in the full-access group should be restricted and temporary.
MySQL Enterprise. The format of the file generated by the audit log plugin was changed for better compatibility with Oracle Audit Vault. See Section 6.4.4, “MySQL Enterprise Audit”, and Section 6.4.4.3, “Audit Log File Formats”.
The audit log plugin included in MySQL Enterprise Edition now has the capability of filtering audited events based on user account and event status. Several new system variables provide DBAs with filtering control. In addition, audit log plugin reporting capability has been improved by the addition of several status variables. For more information, see Section 6.4.4.4, “Configuring Audit Logging Characteristics”, and Audit Log Plugin Status Variables.
MySQL Enterprise Edition now includes a set of encryption functions based on the OpenSSL library that expose OpenSSL capabilities at the SQL level. These functions enable Enterprise applications to perform the following operations:
Implement added data protection using public-key asymmetric cryptography
Create public and private keys and digital signatures
Perform asymmetric encryption and decryption
Use cryptographic hashing for digital signing and data verification and validation
For more information, see Section 6.5, “MySQL Enterprise Encryption”.
MySQL Enterprise Edition now includes MySQL Enterprise Firewall, an application-level firewall that enables database administrators to permit or deny SQL statement execution based on matching against allowlists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics. For more information, see Section 6.4.5, “MySQL Enterprise Firewall”.
Changes to server defaults. Beginning with MySQL 5.6.6, several MySQL Server parameter defaults differ from the defaults in previous releases. 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. For more information, see Section 5.1.2.1, “Changes to Server Defaults”.
InnoDB enhancements. These
InnoDB
enhancements were added:You can create
FULLTEXT
indexes onInnoDB
tables, and query them using theMATCH() ... AGAINST
syntax. This feature includes a new proximity search operator (@
) and several new configuration options andINFORMATION_SCHEMA
tables: See Section 14.6.2.3, “InnoDB Full-Text Indexes” for more information.Several
ALTER TABLE
operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section 14.13, “InnoDB and Online DDL” for details.InnoDB
now supports theDATA DIRECTORY='
clause of thedirectory
'CREATE TABLE
statement, which permits creating tables outside of the data directory. This enhancement provides the flexibility to createtables in locations that better suit your server environment. For example, you can place busy tables on an SSD device, or large tables on a high-capacity HDD device.For more information, see Section 14.6.1.2, “Creating Tables Externally”.
InnoDB
now supports the notion of “transportable tablespaces”, allowing file-per-table tablespaces (.ibd
files) to be exported from a running MySQL instance and imported into another running instance without inconsistencies or mismatches caused by buffered data, in-progress transactions, and internal bookkeeping details such as the space ID and LSN.The
FOR EXPORT
clause of theFLUSH TABLE
command writes any unsaved changes fromInnoDB
memory buffers to the.ibd
file. After copying the.ibd
file and a separate metadata file to the other server, theDISCARD TABLESPACE
andIMPORT TABLESPACE
clauses of theALTER TABLE
statement are used to bring the table data into a different MySQL instance.This enhancement provides the flexibility to move tables that reside in file-per-table tablespaces around to better suit your server environment. For example, you could move busy tables to an SSD device, or move large tables to a high-capacity HDD device. For more information, see Section 14.6.1.3, “Importing InnoDB Tables”.
You can now set the
InnoDB
page size for uncompressed tables to 8KB or 4KB, as an alternative to the default 16KB. This setting is controlled by theinnodb_page_size
configuration option. You specify the size when creating the MySQL instance. AllInnoDB
tablespaces within an instance share the same page size. Smaller page sizes can help to avoid redundant or inefficient I/O for certain combinations of workload and storage devices, particularly SSD devices with small block sizes.Improvements to the algorithms for adaptive flushing make I/O operations more efficient and consistent under a variety of workloads. The new algorithm and default configuration values are expected to improve performance and concurrency for most users. Advanced users can fine-tune their I/O responsiveness through several configuration options. See Section 14.8.3.4, “Configuring Buffer Pool Flushing” for details.
You can code MySQL applications that access
InnoDB
tables through a NoSQL-style API. This feature uses the popular memcached daemon to relay requests such asADD
,SET
, andGET
for key-value pairs. These simple operations to store and retrieve data avoid the SQL overhead such as parsing and constructing a query execution plan. You can access the same data through the NoSQL API and SQL. For example, you might use the NoSQL API for fast updates and lookups, and SQL for complex queries and compatibility with existing applications. See Section 14.20, “InnoDB memcached Plugin” for details.Optimizer statistics for
InnoDB
tables are gathered at more predictable intervals and can persist across server restarts, for improved plan stability. You can also control the amount of sampling done forInnoDB
indexes, to make the optimizer statistics more accurate and improve the query execution plan. See Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters” for details.New optimizations apply to read-only transactions, improving performance and concurrency for ad-hoc queries and report-generating applications. These optimizations are applied automatically when practical, or you can specify
START TRANSACTION READ ONLY
to ensure the transaction is read-only. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for details.You can move the
InnoDB
undo log out of the system tablespace into one or more separate tablespaces. The I/O patterns for the undo log make these new tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. For details, see Section 14.6.3.3, “Undo Tablespaces”.You can improve the efficiency of the
InnoDB
checksum feature by specifying the configuration optioninnodb_checksum_algorithm=crc32
, which turns on a faster checksum algorithm. This option replaces theinnodb_checksums
option. Data written using the old checksum algorithm (option valueinnodb
) is fully upward-compatible; tablespaces modified using the new checksum algorithm (option valuecrc32
) cannot be downgraded to an earlier version of MySQL that does not support theinnodb_checksum_algorithm
option.The
InnoDB
redo log files now have a maximum combined size of 512GB, increased from 4GB. You can specify the larger values through theinnodb_log_file_size
option. The startup behavior now automatically handles the situation where the size of the existing redo log files does not match the size specified byinnodb_log_file_size
andinnodb_log_files_in_group
.The
--innodb-read-only
option lets you run a MySQL server in read-only mode. You can accessInnoDB
tables on read-only media such as a DVD or CD, or set up a data warehouse with multiple instances all sharing the same data directory. See Section 14.8.2, “Configuring InnoDB for Read-Only Operation” for usage details.A new configuration option,
innodb_compression_level
, allows you to select a compression level forInnoDB
compressed tables, from the familiar range of 0-9 used byzlib
. You can also control whether compressed pages in the buffer pool are stored in the redo log when an update operation causes pages to be compressed again. This behavior is controlled by theinnodb_log_compressed_pages
configuration option.Data blocks in an
InnoDB
compressed table contain a certain amount of empty space (padding) to allow DML operations to modify the row data without re-compressing the new values. Too much padding can increase the chance of a compression failure, requiring a page split, when the data does need to be re-compressed after extensive changes. The amount of padding can now be adjusted dynamically, so that DBAs can reduce the rate of compression failures without re-creating the entire table with new parameters, or re-creating the entire instance with a different page size. The associated new configuration options areinnodb_compression_failure_threshold_pct
,innodb_compression_pad_pct_max
.Several new
InnoDB
-relatedINFORMATION_SCHEMA
tables provide information about theInnoDB
buffer pool, metadata about tables, indexes, and foreign keys from theInnoDB
data dictionary, and low-level information about performance metrics that complements the information from the Performance Schema tables.To ease the memory load on systems with huge numbers of tables,
InnoDB
now frees up the memory associated with an opened table using an LRU algorithm to select tables that have gone the longest without being accessed. To reserve more memory to hold metadata for openInnoDB
tables, increase the value of thetable_definition_cache
configuration option.InnoDB
treats this value as a “soft limit” for the number of open table instances in theInnoDB
data dictionary cache. For additional information, refer to thetable_definition_cache
documentation.InnoDB
has several internal performance enhancements, including reducing contention by splitting the kernel mutex, moving flushing operations from the main thread to a separate thread, enabling multiple purge threads, and reducing contention for the buffer pool on large-memory systems.InnoDB
uses a new, faster algorithm to detect deadlocks. Information about allInnoDB
deadlocks can be written to the MySQL server error log, to help diagnose application issues.To avoid a lengthy warmup period after restarting the server, particularly for instances with large
InnoDB
buffer pools, you can reload pages into the buffer pool immediately after a restart. MySQL can dump a compact data file at shutdown, then consult that data file to find the pages to reload on the next restart. You can also manually dump or reload the buffer pool at any time, for example during benchmarking or after complex report-generation queries. See Section 14.8.3.5, “Saving and Restoring the Buffer Pool State” for details.As of MySQL 5.6.16, innochecksum provides support for files greater than 2GB in size. Previously, innochecksum only supported files up to 2GB in size.
As of MySQL 5.6.16, new global configuration parameters,
innodb_status_output
andinnodb_status_output_locks
, allow you to dynamically enable and disable the standardInnoDB
Monitor andInnoDB
Lock Monitor for periodic output. Enabling and disabling monitors for periodic output by creating and dropping specially named tables is deprecated and may be removed in a future release. For additional information, see Section 14.17, “InnoDB Monitors”.As of MySQL 5.6.17, Online DDL support is extended to the following operations for regular and partitioned
InnoDB
tables:ALTER TABLE ... ENGINE=INNODB
(when run on anInnoDB
table)Online DDL support reduces table rebuild time and permits concurrent DML. See Section 14.13, “InnoDB and Online DDL”.
As of MySQL 5.6.42, the zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11. MySQL implements compression with the help of the zlib library.
If you use
InnoDB
compressed tables, see Section 2.11.3, “Changes in MySQL 5.6” for related upgrade implications.
Partitioning. These table-partitioning enhancements were added:
The maximum number of partitions is increased to 8192. This number includes all partitions and all subpartitions of the table.
It is now possible to exchange a partition of a partitioned table or a subpartition of a subpartitioned table with a nonpartitioned table that otherwise has the same structure using the
ALTER TABLE ... EXCHANGE PARTITION
statement. This can be used, for example, to import and export partitions. For more information and examples, see Section 19.3.3, “Exchanging Partitions and Subpartitions with Tables”.Explicit selection of one or more partitions or subpartitions is now supported for queries, as well as for many data modification statements, that act on partitioned tables. For example, assume a table
t
with some integer columnc
has 4 partitions namedp0
,p1
,p2
, andp3
. Then the querySELECT * FROM t PARTITION (p0, p1) WHERE c < 5
returns only those rows from partitionsp0
andp1
for whichc
is less than 5.The following statements support explicit partition selection:
For syntax, see the descriptions of the individual statements. For additional information and examples, see Section 19.5, “Partition Selection”.
Partition lock pruning greatly improves performance of many DML and DDL statements acting on tables with many partitions by helping to eliminate locks on partitions that are not affected by these statements. Such statements include many
SELECT
,SELECT ... PARTITION
,UPDATE
,REPLACE
,INSERT
, as well as many other statements. For more information, including a complete listing of the statements whose performance has thus been improved, see Section 19.6.4, “Partitioning and Locking”.
Performance Schema. The Performance Schema includes several new features:
Instrumentation for table input and output. Instrumented operations include row-level accesses to persistent base tables or temporary tables. Operations that affect rows are fetch, insert, update, and delete.
Event filtering by table, based on schema and/or table names.
Event filtering by thread. More information is collected for threads.
Summary tables for table and index I/O, and for table locks.
Instrumentation for statements and stages within statements.
Configuration of instruments and consumers at server startup, which previously was possible only at runtime.
MySQL NDB Cluster. MySQL NDB Cluster is released as a separate product; the most recent GA releases are based on MySQL 5.6 and use version 7.3 of the
NDB
storage engine. Clustering support is not available in mainline MySQL Server 5.6 releases. For more information about MySQL NDB Cluster 7.3, see Chapter 18, MySQL NDB Cluster 7.3 and NDB Cluster 7.4. The latest current development version is MySQL NDB Cluster 7.4, based on version 7.4 of theNDB
storage engine and MySQL Server 5.6. MySQL NDB Cluster 7.4 is currently available for testing and evaluation. The most recent MySQL NDB Cluster 7.4 release can be obtained from https://dev.mysql.com/downloads/cluster/.For more information and an overview of improvements made in MySQL NDB Cluster 7.4, see Section 18.2.4.2, “What is New in NDB Cluster 7.4”.
MySQL NDB Cluster 7.2, the previous GA release, is based on MySQL Server 5.5, although we recommend that new deployments use MySQL NDB Cluster 7.3.
Replication and logging. These replication enhancements were added:
MySQL now supports transaction-based replication using global transaction identifiers (also known as “GTIDs”). This makes it possible to identify and track each transaction when it is committed on the originating server and as it is applied by any replicas.
Enabling of GTIDs in a replication setup is done primarily using the new
gtid_mode
andenforce_gtid_consistency
system variables. For information about additional options and variables introduced in support of GTIDs, see Section 17.1.4.5, “Global Transaction ID Options and Variables”.When using GTIDs it is not necessary to refer to log files or positions within those files when starting a new replica or failing over to a new source, which greatly simplifies these tasks. For more information about provisioning servers for GTID replication with or without referring to binary log files, see Section 17.1.3.3, “Using GTIDs for Failover and Scaleout”.
GTID-based replication is completely transaction-based, which makes it simple to check the consistency of sources and replicas. If all transactions committed on a given source are also committed on a given replica, consistency between the two servers is guaranteed.
For more complete information about the implementation and use of GTIDs in MySQL Replication, see Section 17.1.3, “Replication with Global Transaction Identifiers”.
MySQL row-based replication now supports row image control. By logging only those columns required for uniquely identifying and executing changes on each row (as opposed to all columns) for each row change, it is possible to save disk space, network resources, and memory usage. You can determine whether full or minimal rows are logged by setting the
binlog_row_image
server system variable to one of the valuesminimal
(log required columns only),full
(log all columns), ornoblob
(log all columns except for unneededBLOB
orTEXT
columns). See System Variables Used with Binary Logging, for more information.Binary logs written and read by the MySQL Server are now crash-safe, because only complete events (or transactions) are logged or read back. By default, the server logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly. You can also cause the server to write checksums for the events using CRC32 checksums by setting the
binlog_checksum
system variable. To cause the server to read checksums from the binary log, use themaster_verify_checksum
system variable. The--slave-sql-verify-checksum
system variable causes the replica SQL thread to read checksums from the relay log.MySQL now supports logging of source connection information and of replica relay log information to tables as well as files. Use of these tables can be controlled independently, by the
master_info_repository
andrelay_log_info_repository
system variable system variables. Settingmaster_info_repository
toTABLE
causes connection information logging to theslave_master_info
table. Settingrelay_log_info_repository
toTABLE
causes relay log information logging to theslave_relay_log_info
table. Both tables are created automatically in themysql
system database.In order for replication to be resilient to unexpected halts, the
slave_master_info
andslave_relay_log_info
tables must each use a transactional storage engine, and beginning with MySQL 5.6.6, these tables are created usingInnoDB
for this reason. (Bug #13538891) If you are using a previous MySQL 5.6 release in which both of these tables useMyISAM
, this means that, prior to starting replication, you must convert both of them to a transactional storage engine (such asInnoDB
) if you wish for replication to be resilient to unexpected halts. You can do this in such cases by means of the appropriateALTER TABLE ... ENGINE=...
statements. You should not attempt to change the storage engine used by either of these tables while replication is actually running.See Section 17.3.2, “Handling an Unexpected Halt of a Replica Server”, for more information.
mysqlbinlog now has the capability to back up a binary log in its original binary format. When invoked with the
--read-from-remote-server
and--raw
options, mysqlbinlog connects to a server, requests the log files, and writes output files in the same format as the originals. See Section 4.6.8.3, “Using mysqlbinlog to Back Up Binary Log Files”.MySQL now supports delayed replication such that a replica server deliberately lags behind the source by at least a specified amount of time. The default delay is 0 seconds. Use the new
MASTER_DELAY
option forCHANGE MASTER TO
to set the delay.Delayed replication can be used for purposes such as protecting against user mistakes on the source (a DBA can roll back a delayed replica to the time just before the disaster) or testing how the system behaves when there is a lag. See Section 17.3.10, “Delayed Replication”.
A replica having multiple network interfaces can now be caused to use only one of these (to the exclusion of the others) by using the
MASTER_BIND
option when issuing aCHANGE MASTER TO
statement.The
log_bin_basename
system variable has been added. This variable contains the complete filename and path to the binary log file. Whereas thelog_bin
system variable shows only whether or not binary logging is enabled,log_bin_basename
reflects the name set with the--log-bin
server option.Similarly, the
relay_log_basename
system variable shows the filename and complete path to the relay log file.MySQL Replication now supports parallel execution of transactions with multithreading on the replica. When parallel execution is enabled, the replica SQL thread acts as the coordinator for a number of replica worker threads as determined by the value of the
slave_parallel_workers
server system variable. The current implementation of multithreading on the replica assumes that data and updates are partitioned on a per-database basis, and that updates within a given database occur in the same relative order as they do on the source. However, it is not necessary to coordinate transactions between different databases. Transactions can then also be distributed per database, which means that a worker thread on the replica can process successive transactions on a given database without waiting for updates to other databases to complete.Since transactions on different databases can occur in a different order on the replica than on the source, simply checking for the most recently executed transaction is not a guarantee that all previous transactions on the source have been executed on the replica. This has implications for logging and recovery when using a multithreaded replica. For information about how to interpret binary logging information when using multithreading on the replica, see Section 13.7.5.35, “SHOW SLAVE STATUS Statement”.
Optimizer enhancements. These query optimizer improvements were implemented:
The optimizer now more efficiently handles queries (and subqueries) of the following form:
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;
That type of query is common in web applications that display only a few rows from a larger result set. For example:
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10; SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
The sort buffer has a size of
sort_buffer_size
. If the sort elements forN
rows are small enough to fit in the sort buffer (M
+N
rows ifM
was specified), the server can avoid using a merge file and perform the sort entirely in memory. For details, see Section 8.2.1.16, “LIMIT Query Optimization”.The optimizer implements Disk-Sweep Multi-Range Read. Reading rows using a range scan on a secondary index can result in many random disk accesses to the base table when the table is large and not stored in the storage engine's cache. With the Disk-Sweep Multi-Range Read (MRR) optimization, MySQL tries to reduce the number of random disk access for range scans by first scanning the index only and collecting the keys for the relevant rows. Then the keys are sorted and finally the rows are retrieved from the base table using the order of the primary key. The motivation for Disk-sweep MRR is to reduce the number of random disk accesses and instead achieve a more sequential scan of the base table data. For more information, see Section 8.2.1.10, “Multi-Range Read Optimization”.
The optimizer implements Index Condition Pushdown (ICP), an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the
WHERE
condition for the rows. With ICP enabled, and if parts of theWHERE
condition can be evaluated by using only fields from the index, the MySQL server pushes this part of theWHERE
condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is base row be read. ICP can reduce the number of accesses the storage engine has to do against the base table and the number of accesses the MySQL server has to do against the storage engine. For more information, see Section 8.2.1.5, “Index Condition Pushdown Optimization”.The
EXPLAIN
statement now provides execution plan information forDELETE
,INSERT
,REPLACE
, andUPDATE
statements. Previously,EXPLAIN
provided information only forSELECT
statements. In addition, theEXPLAIN
statement now can produce output in JSON format. See Section 13.8.2, “EXPLAIN Statement”.The optimizer more efficiently handles subqueries in the
FROM
clause (that is, derived tables). Materialization of subqueries in theFROM
clause is postponed until their contents are needed during query execution, which improves performance. In addition, during query execution, the optimizer may add an index to a derived table to speed up row retrieval from it. For more information, see Section 8.2.2.4, “Optimizing Derived Tables”.The optimizer uses semijoin and materialization strategies to optimize subquery execution. See Section 8.2.2.1, “Optimizing Subqueries with Semijoin Transformations”, and Section 8.2.2.2, “Optimizing Subqueries with Materialization”.
A Batched Key Access (BKA) join algorithm is now available that uses both index access to the joined table and a join buffer. The BKA algorithm supports inner join, outer join, and semijoin operations, including nested outer joins and nested semijoins. Benefits of BKA include improved join performance due to more efficient table scanning. For more information, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.
The optimizer now has a tracing capability, primarily for use by developers. The interface is provided by a set of
optimizer_trace_
system variables and the Information Schemaxxx
OPTIMIZER_TRACE
table. For details, see MySQL Internals: Tracing the Optimizer.
Condition handling. MySQL now supports the
GET DIAGNOSTICS
statement.GET DIAGNOSTICS
provides applications a standardized way to obtain information from the diagnostics area, such as whether the previous SQL statement produced an exception and what it was. For more information, see Section 13.6.7.3, “GET DIAGNOSTICS Statement”.In addition, several deficiencies in condition handler processing rules were corrected so that MySQL behavior is more like standard SQL:
Block scope is used in determining which handler to select. Previously, a stored program was treated as having a single scope for handler selection.
Condition precedence is more accurately resolved.
Diagnostics area clearing has changed. Bug #55843 caused handled conditions to be cleared from the diagnostics area before activating the handler. This made condition information unavailable within the handler. Now condition information is available to the handler, which can inspect it with the
GET DIAGNOSTICS
statement. The condition information is cleared when the handler exits, if it has not already been cleared during handler execution.Previously, handlers were activated as soon as a condition occurred. Now they are not activated until the statement in which the condition occurred finishes execution, at which point the most appropriate handler is chosen. This can make a difference for statements that raise multiple conditions, if a condition raised later during statement execution has higher precedence than an earlier condition and there are handlers in the same scope for both conditions. Previously, the handler for the first condition raised would be chosen, even if it had a lower precedence than other handlers. Now the handler for the condition with highest precedence is chosen, even if it is not the first condition raised by the statement.
For more information, see Section 13.6.7.6, “Scope Rules for Handlers”.
Data types. These data type changes have been implemented:
MySQL now permits fractional seconds for
TIME
,DATETIME
, andTIMESTAMP
values, with up to microseconds (6 digits) precision. See Section 11.2.7, “Fractional Seconds in Time Values”.Previously, at most one
TIMESTAMP
column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. AnyTIMESTAMP
column definition can have any combination ofDEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
clauses. In addition, these clauses now can be used withDATETIME
column definitions. For more information, see Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.In MySQL, the
TIMESTAMP
data type differs in nonstandard ways from other data types in terms of default value and assignment of automatic initialization and update attributes. These behaviors remain the default but now are deprecated, and can be turned off by enabling theexplicit_defaults_for_timestamp
system variable at server startup. See Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”, and Section 5.1.7, “Server System Variables”.
Host cache. MySQL now provides more information about the causes of errors that occur when clients connect to the server, as well as improved access to the host cache, which contains client IP address and host name information and is used to avoid DNS lookups. These changes have been implemented:
New
Connection_errors_
status variables provide information about connection errors that do not apply to specific client IP addresses.xxx
Counters have been added to the host cache to track errors that do apply to specific IP addresses, and a new
host_cache
Performance Schema table exposes the contents of the host cache so that it can be examined usingSELECT
statements. Access to host cache contents makes it possible to answer questions such as how many hosts are cached, what kinds of connection errors are occurring for which hosts, or how close host error counts are to reaching themax_connect_errors
system variable limit.The host cache size now is configurable using the
host_cache_size
system variable.
For more information, see Section 5.1.11.2, “DNS Lookups and the Host Cache”, and Section 22.12.10.1, “The host_cache Table”.
OpenGIS. The OpenGIS specification defines functions that test the relationship between two geometry values. MySQL originally implemented these functions such that they used object bounding rectangles and returned the same result as the corresponding MBR-based functions. Corresponding versions are now available that use precise object shapes. These versions are named with an
ST_
prefix. For example,Contains()
uses object bounding rectangles, whereasST_Contains()
uses object shapes. For more information, see Section 12.17.9, “Functions That Test Spatial Relations Between Geometry Objects”.
The following features are deprecated in MySQL 5.6; you should expect them to be removed in a future series. Where alternatives are shown, applications should be updated to use them.
For applications that use features deprecated in MySQL 5.6 that have been removed in a higher MySQL series, statements may fail when replicated from a MySQL 5.6 source to a higher-series replica, or may have different effects on source and replica. To avoid such problems, applications that use features deprecated in 5.6 should be revised to avoid them and use alternatives when possible.
The
ERROR_FOR_DIVISION_BY_ZERO
,NO_ZERO_DATE
, andNO_ZERO_IN_DATE
SQL modes are deprecated and setting thesql_mode
value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES
orSTRICT_TRANS_TABLES
). The motivation for the change in MySQL 5.7 is to reduce the number of SQL modes with an effect dependent on strict mode and make them part of strict mode itself.To make advance preparation for an upgrade to MySQL 5.7, see SQL Mode Changes in MySQL 5.7. That discussion provides guidelines to assess whether your applications are affected by the SQL mode changes in MySQL 5.7.
Relying on implicit
GROUP BY
sorting in MySQL 5.6 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicitORDER BY
clause.GROUP BY
sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.Pre-4.1 passwords and the
mysql_old_password
authentication plugin. Passwords stored in the older hash format used before MySQL 4.1 are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords and themysql_old_password
authentication plugin are now deprecated. To prevent connections using accounts that have pre-4.1 password hashes, thesecure_auth
system variable is now enabled by default. (To permit connections for accounts that have such password hashes, start the server with--secure_auth=0
. However, because pre-4.1 passwords are deprecated, disablingsecure_auth
is also deprecated.)DBAs are advised to convert accounts that use the
mysql_old_password
authentication plugin to usemysql_native_password
instead. For account upgrade instructions, see Section 6.4.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.The
OLD_PASSWORD()
function generates pre-4.1 password hashes, as doesPASSWORD()
if theold_passwords
system variable is set to 1.OLD_PASSWORD()
andold_passwords=1
are deprecated.The
--skip-innodb
option and its synonyms (--innodb=OFF
,--disable-innodb
, and so forth).The
innodb_locks_unsafe_for_binlog
system variable.The
date_format
,datetime_format
, andtime_format
system variables, which are unused.The
have_profiling
,profiling
, andprofiling_history_size
system variables.The
innodb_use_sys_malloc
andinnodb_additional_mem_pool_size
system variables.The
timed_mutexes
system variable. It does nothing and has no effect.The
--language
option. Use the--lc-messages-dir
and--lc-messages
options instead.The
IGNORE
clause forALTER TABLE
.ALTER IGNORE TABLE
causes problems for replication, prevents onlineALTER TABLE
for unique index creation, and causes problems with foreign keys (rows removed in the parent table).The msql2mysql, mysql_convert_table_format, mysql_find_rows, mysql_fix_extensions, mysql_setpermission, mysql_waitpid, mysql_zap, mysqlaccess, and mysqlbug utilities.
The mysqlhotcopy utility. Alternatives include mysqldump and MySQL Enterprise Backup.
The following items are obsolete and have been removed in MySQL 5.6. Where alternatives are shown, applications should be updated to use them.
For MySQL 5.5 applications that use features removed in MySQL 5.6, statements may fail when replicated from a MySQL 5.5 source to a MySQL 5.6 replica, or may have different effects on source and replica. To avoid such problems, applications that use features removed in MySQL 5.6 should be revised to avoid them and use alternatives when possible.
The
--log
server option and thelog
system variable. Instead, use thegeneral_log
system variable to enable the general query log and thegeneral_log_file
system variable to set the general query log file name.The
log_slow_queries
system variable. Instead, use theslow_query_log
system variable to enable the slow query log and theslow_query_log_file
system variable to set the slow query log file name.The
--one-thread
server option. Use--thread_handling=no-threads
instead.The
--safe-mode
server option.The
--skip-thread-priority
server option.The
--table-cache
server option. Use thetable_open_cache
system variable instead.The
--init-rpl-role
and--rpl-recovery-rank
options, therpl_recovery_rank
system variable, and theRpl_status
status variable.The
engine_condition_pushdown
system variable. Use theengine_condition_pushdown
flag of theoptimizer_switch
variable instead.The
have_csv
,have_innodb
,have_ndbcluster
, andhave_partitioning
system variables. UseSHOW PLUGINS
or query thePLUGINS
table in theINFORMATION_SCHEMA
database instead.The
sql_big_tables
system variable. Usebig_tables
instead.The
sql_low_priority_updates
system variable. Uselow_priority_updates
instead.The
sql_max_join_size
system variable. Usemax_join_size
instead.The
max_long_data_size
system variable. Usemax_allowed_packet
instead.The
FLUSH MASTER
andFLUSH SLAVE
statements. Use theRESET MASTER
andRESET SLAVE
statements instead.The
SLAVE START
andSLAVE STOP
statements. Use TheSTART SLAVE
andSTOP SLAVE
statements.The
SHOW AUTHORS
andSHOW CONTRIBUTORS
statements.The
OPTION
andONE_SHOT
modifiers for theSET
statement.It is explicitly disallowed to assign the value
DEFAULT
to stored procedure or function parameters or stored program local variables (for example with aSET
statement). It remains permissible to assignvar_name
= DEFAULTDEFAULT
to system variables, as before.Most
SHOW ENGINE INNODB MUTEX
output is removed in 5.6.14.SHOW ENGINE INNODB MUTEX
output is removed entirely in MySQL 5.7.2. Comparable information can be generated by creating views on Performance Schema tables.