This section summarizes what has been added to, deprecated in, and removed from 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
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 184.108.40.206, “The SHA-256 Authentication Plugin”.
mysql.user table now has a
password_expired column. Its default
'N', but can be set to
'Y' with the new
ALTER 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 a
SET PASSWORD statement to
establish a new account password. For more information,
see Section 220.127.116.11, “
ALTER USER Syntax”, and
Section 6.3.6, “Password Expiration and Sandbox Mode”.
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
error). This affects the
statements. Passwords given as arguments to the
functions are checked as well.
The strength of potential passwords can be assessed
using the new
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
Section 18.104.22.168, “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 22.214.171.124, “Password Hashing in MySQL”
On Unix platforms, mysql_install_db
supports a new option,
that provides for more secure MySQL installation. Invoking
causes it to assign a random password to the MySQL
root 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 126.96.36.199, “Passwords and Logging”.
START SLAVE syntax has been
modified to permit connection parameters to be specified
for connecting to the master. This provides an alternative
to storing the password in the
master.info file. See
Section 188.8.131.52, “
START SLAVE Syntax”.
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.3.12, “MySQL Enterprise Audit Log Plugin”, and Section 184.108.40.206, “The Audit Log File”.
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 220.127.116.11, “Changes to Server Defaults”.
InnoDB enhancements were added:
You can create
FULLTEXT indexes on
InnoDB tables, and query them using the
MATCH() ... AGAINST syntax.
This feature includes a new proximity search operator
@) and several new configuration
See Section 18.104.22.168, “FULLTEXT Indexes” for more
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.11, “InnoDB and Online DDL” for details.
InnoDB now supports the
clause of the
statement, which allows you to create
.ibd files) in a location
outside the MySQL data directory. This enhancement
provides the flexibility to create
tablespaces in locations that better suit your server
environment. For example, you could place busy tables on
an SSD device, or large
tables on a high-capacity
For additional information, see Section 14.5.4, “Specifying the Location of a Tablespace”.
InnoDB now supports the notion of
“transportable tablespaces”, allowing
.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
FOR EXPORT clause of the
TABLE command 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, the
TABLESPACE clauses of the
ALTER TABLE statement are
used to bring the table data into a different MySQL
This enhancement provides the flexibility to move 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.5.5, “Copying Tablespaces to Another Server (Transportable Tablespaces)”.
You can now set the
page size for
uncompressed tables to 8KB or 4KB, as an alternative to
the default 16KB. This setting is controlled by the
configuration option. You specify the size when creating
the MySQL instance. All
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
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 22.214.171.124, “Tuning InnoDB 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
GET for key-value pairs. These simple
operations to store and retrieve data avoid the SQL
overhead such as parsing and constructing a
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.18, “InnoDB Integration with memcached” for details.
Optimizer statistics for
are gathered at more predictable intervals and can persist
across server restarts, for improved
You can also control the amount of sampling done for
InnoDB indexes, to make the optimizer
statistics more accurate and improve the query execution
plan. See Section 126.96.36.199, “Configuring Persistent Optimizer Statistics Parameters” for
New optimizations apply to read-only
improving performance and concurrency for ad-hoc queries
and report-generating applications. These optimizations
are applied automatically when practical, or you can
TRANSACTION READ ONLY to ensure the transaction
is read-only. See
Section 14.13.14, “Optimizing InnoDB Read-Only Transactions” for details.
You can move the
undo log out of the
tablespace into one or more separate
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.13.16, “Storing InnoDB Undo Logs in Separate Tablespaces”.
You can improve the efficiency of the
InnoDB checksum feature by specifying
the configuration option
which turns on a faster checksum algorithm. This option
Data written using the old checksum algorithm (option
innodb) is fully
upward-compatible; tablespaces modified using the new
checksum algorithm (option value
cannot be downgraded to an earlier version of MySQL that
does not support the
redo log files now
have a maximum combined size of 512GB, increased from 4GB.
You can specify the larger values through the
option. The startup behavior now automatically handles the
situation where the size of the existing redo log files
does not match the size specified by
option lets you run a MySQL server in read-only mode. You
InnoDB 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.3.1, “Configuring InnoDB for Read-Only Operation” for usage
A new configuration option,
allows you to select a
InnoDB compressed tables, from the
familiar range of 0-9 used by
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 the
Data blocks in an
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 are
INFORMATION_SCHEMA tables provide
information about the
pool, metadata about tables, indexes, and foreign keys
InnoDB data dictionary, and
low-level information about performance metrics that
complements the information from the Performance Schema
To ease the memory load on systems with huge numbers of
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 open
InnoDB tables, increase the value of
this value as a “soft limit” for the number
of open table instances in the
data dictionary cache. For additional information, refer
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
InnoDB uses a new, faster algorithm to
Information about all
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
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 188.8.131.52, “Preloading the InnoDB Buffer Pool for Faster Restart” for details.
As of MySQL 5.6.16, new global configuration parameters,
allow you to dynamically enable and disable the standard
InnoDB Monitor and
InnoDB 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.15, “InnoDB Monitors”.
As of MySQL 5.6.17, MySQL supports rebuilding regular and
InnoDB tables using
ALGORITHM=INPLACE) for the following
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
... 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
with some integer column
c has 4
p3. Then the query
FROM t PARTITION (p0, p1) WHERE c < 5 returns
only those rows from partitions
p1 for which
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
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 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 Cluster NDB
7.3, see Chapter 18, MySQL Cluster NDB 7.3.
MySQL Cluster NDB 7.2, the previous GA release, is based on MySQL Server 5.5, and is still available for use in production, although we recommend that new deployments use MySQL Cluster NDB 7.3. For more information about MySQL Cluster NDB 7.2, see MySQL Cluster NDB 7.2.
MySQL Cluster NDB 6.3, MySQL Cluster NDB 7.0, and MySQL Cluster NDB 7.1 are also still available and supported (although we recommend that new deployments use the latest GA release series, currently MySQL Cluster NDB 7.3). These versions of MySQL Cluster are based on MySQL Server 5.1 and documented in the MySQL 5.1 Manual; see https://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html, for more information.
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 slaves.
Enabling of GTIDs in a replication setup is done primarily
using the new
server options. For information about additional options
and variables introduced in support of GTIDs, see
Section 184.108.40.206, “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 slave or failing over to a new master, which greatly simplifies these tasks. For more information about provisioning servers for GTID replication with or without referring to binary log files, see Section 220.127.116.11, “Using GTIDs for Failover and Scaleout”.
GTID-based replication is completely transaction-based, which makes it simple to check the consistency of masters and slaves. If all transactions committed on a given master are also committed on a given slave, 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
system variable to one of the values
minimal (log required columns only),
full (log all columns), or
noblob (log all columns except for
TEXT columns). See
System Variables Used with Binary Logging, for more
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
system variable. To cause the server to read checksums
from the binary log, use the
system variable. The
system variable causes the slave SQL thread to read
checksums from the relay log.
MySQL now supports logging of master connection
information and of slave relay log information to tables
as well as files. Use of these tables can be controlled
independently, by the
server options. Setting
TABLE causes connection information to
be logged in the
TABLE causes relay log information
to be logged to the
slave_relay_log_info table. Both of
these tables are created automatically, in the
mysql system database.
In order for replication to be crash-safe, the
slave_relay_log_info tables must each
use a transactional storage engine, and beginning with
MySQL 5.6.6, these tables are created using
InnoDB for this reason. (Bug
#13538891) If you are using a previous MySQL 5.6 release
in which both of these tables use
MyISAM, this means that,
prior to starting replication, you must convert both of
them to a transactional storage engine (such as
InnoDB) if you wish for replication to
be crash-safe. You can do this in such cases by means of
... ENGINE=... statements. You should
not attempt to change the storage
engine used by either of these tables while replication is
See Crash-safe replication, for more information.
mysqlbinlog now has the capability to
back up a binary log in its original binary format. When
invoked with the
mysqlbinlog connects to a server,
requests the log files, and writes output files in the
same format as the originals. See
Section 18.104.22.168, “Using mysqlbinlog to Back Up Binary Log Files”.
MySQL now supports delayed replication such that a slave
server deliberately lags behind the master by at least a
specified amount of time. The default delay is 0 seconds.
Use the new
MASTER_DELAY option for
CHANGE MASTER TO to set the
Delayed replication can be used for purposes such as protecting against user mistakes on the master (a DBA can roll back a delayed slave to the time just before the disaster) or testing how the system behaves when there is a lag. See Section 17.3.9, “Delayed Replication”.
A replication slave having multiple network interfaces can
now be caused to use only one of these (to the exclusion
of the others) by using the
option when issuing a
system variable has been added. This variable contains the
complete filename and path to the binary log file. Whereas
variable shows only whether or not binary logging is
reflects the name set with the
--log-bin server option.
variable shows the filename and complete path to the relay
MySQL Replication now supports parallel execution of
transactions with multi-threading on the slave. When
parallel execution is enabled, the slave SQL thread acts
as the coordinator for a number of slave worker threads as
determined by the value of the
server system variable. The current implementation of
multi-threading on the slave 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 master. 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 slave slave can
process successive transactions on a given database
without waiting for updates to other databases to
Since transactions on different databases can occur in a
different order on the slave than on the master, simply
checking for the most recently executed transaction is not
a guarantee that all previous transactions on the master
have been executed on the slave. This has implications for
logging and recovery when using a multi-threaded slave.
For information about how to interpret binary logging
information when using multi-threading on the slave, see
Section 22.214.171.124, “
SHOW SLAVE STATUS Syntax”.
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 [
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 for
N rows are
small enough to fit in the sort buffer
M was specified), the
server can avoid using a merge file and perform the sort
entirely in memory. For details, see
Section 126.96.36.199, “Optimizing
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 188.8.131.52, “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
WHERE condition for the rows. With
ICP enabled, and if parts of the
condition can be evaluated by using only fields from the
index, the MySQL server pushes this part of the
WHERE 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 184.108.40.206, “Index Condition Pushdown Optimization”.
EXPLAIN statement now
provides execution plan information for
provided information only for
SELECT statements. In
statement now can produce output in JSON format. See
Section 13.8.2, “
The optimizer more efficiently handles subqueries in the
FROM clause (that is, derived tables).
Materialization of subqueries in the
FROM 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 220.127.116.11.3, “Optimizing Subqueries in the
FROM Clause (Derived
The optimizer uses semi-join and materialization strategies to optimize subquery execution. See Section 18.104.22.168.1, “Optimizing Subqueries with Semi-Join Transformations”, and Section 22.214.171.124.2, “Optimizing Subqueries with Subquery 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 semi-join operations, including nested outer joins and nested semi-joins. Benefits of BKA include improved join performance due to more efficient table scanning. For more information, see Section 126.96.36.199, “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
system variables and the
table. For details, see
Internals: Tracing the Optimizer.
MySQL now supports the
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 188.8.131.52, “
GET DIAGNOSTICS Syntax”.
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
statement. The condition information is cleared when the
handler exits, if it has not already been cleared during
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 184.108.40.206, “Scope Rules for Handlers”.
Data types. These data type changes have been implemented:
MySQL now permits fractional seconds for
TIMESTAMP values, with up to
microseconds (6 digits) precision. See
Section 11.3.6, “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.
definition can have any combination of
CURRENT_TIMESTAMP clauses. In addition, these
clauses now can be used with
definitions. For more information, see
Section 11.3.5, “Automatic Initialization and Updating for
In MySQL, the
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 the
system variable at server startup. See
Section 11.3.5, “Automatic Initialization and Updating for
Section 5.1.4, “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:
status variables provide information about connection
errors that do not apply to specific client IP addresses.
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 using
SELECT 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 the
The host cache size now is configurable using the
For more information, see Section 220.127.116.11, “DNS Lookup Optimization and the Host Cache”, and Section 18.104.22.168, “The host_cache Table”.
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
object bounding rectangles, whereas
ST_Contains() uses object
shapes. For more information, see
Section 12.18.8, “Functions That Test Spatial Relations Between Geometry Objects”.
The following features are deprecated in MySQL 5.6 and may be or will be removed in a future series. Where alternatives are shown, applications should be updated to use them.
NO_ZERO_IN_DATE SQL modes
are deprecated and setting the
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
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 will be 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
ORDER BY clause.
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 the
authentication plugin are now deprecated. To prevent
connections using accounts that have pre-4.1 password hashes,
variable is now enabled by default. (To permit connections for
accounts that have such password hashes, start the server with
because pre-4.1 passwords are deprecated, disabling
secure_auth is also
DBAs are advised to convert accounts that use the
mysql_old_password authentication plugin to
mysql_native_password instead. For
account upgrade instructions, see
Section 22.214.171.124, “Migrating Away from Pre-4.1 Password Hashing and the
option and its synonyms (
--disable-innodb, and so forth).
variable. It does nothing and has no effect.
IGNORE clause for
IGNORE TABLE causes problems for replication,
ALTER TABLE for unique
index creation, and causes problems with foreign keys (rows
removed in the parent table).
The following items are obsolete and have been removed in MySQL 5.6. Where alternatives are shown, applications should be updated to use them.
--log server option and the
log system variable. Instead, use the
--general_log option to enable
the general query log and the
option to set the general query log file name.
--log-slow-queries server option and the
log_slow_queries system variable. Instead,
to enable the slow query log and the
option to set the slow query log file name.
--one-thread server option. Use
--safe-mode server option.
--skip-thread-priority server option.
--table-cache server option. Use the
--rpl-recovery-rank options, the
rpl_recovery_rank system variable, and the
Rpl_status status variable.
variable. Use the
flag of the
sql_big_tables system variable. Use
sql_max_join_size system variable. Use
max_long_data_size system variable. Use
SHOW AUTHORS and
modifiers for the
It is explicitly disallowed to assign the value
DEFAULT to stored procedure or function
parameters or stored program local variables (for example with
SET statement). It remains permissible to assign
DEFAULT to system variables, as before.
INNODB MUTEX output is removed in 5.6.14.
INNODB MUTEX output is removed entirely in MySQL
5.7.2. Comparable information can be generated by creating
views on Performance