This section summarizes what has been added to, deprecated in, and removed from MySQL 5.7.
The following features have been added to MySQL 5.7:
The server now requires account rows in the
mysql.user table to have a nonempty
plugin column value and disables accounts
with an empty value. For server upgrade instructions, see
Section 184.108.40.206, “Upgrading from MySQL 5.6 to 5.7”. DBAs are
advised to also convert accounts that use the deprecated
mysql_old_password authentication plugin
mysql_native_password instead. For
account upgrade instructions, see
Section 220.127.116.11, “Migrating Away from Pre-4.1 Password Hashing and the
MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password. For more information, see Section 6.3.6, “Password Expiration Policy”.
MySQL deployments installed using RPM packages now are secure by default. The following changes have been implemented as the default deployment characteristics:
The installation process creates only a single
generates a random password for this account, and marks
the password expired. The MySQL administrator must connect
root using the random password and
SET PASSWORD to select
a new password. (The random password is found in the
Installation creates no anonymous-user accounts.
Installation creates no
ALTER TABLE now supports a
RENAME INDEX clause that renames an
index. The change is made in place without a table-copy
operation. It works for all storage engines. See
Section 13.1.6, “
ALTER TABLE Syntax”.
InnoDB enhancements were added:
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
This is true as long as the number of length bytes
required by a
column remains the same. For
VARCHAR values of 0 to 255,
one length byte is required to encode the value. For
VARCHAR values of 256 bytes
or more, two length bytes are required. As a result,
ALTER TABLE only
size from 0 to 255 bytes or increasing
VARCHAR size from a value
equal to or greater than 256 bytes.
ALTER TABLE does
not support increasing
VARCHAR size from less than
256 bytes to a value equal to or greater than 256 bytes.
In this case, the number of required length bytes would
change from 1 to 2, which is only supported by a table
ALGORITHM=COPY). For example,
attempting to change
VARCHAR column size from
255 to 256 using in-place
TABLE would return an error:
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
InnoDB temporary table metadata is no longer stored to
InnoDB system tables. Instead, a new table,
INNODB_TEMP_TABLE_INFO, provides users
with a snapshot of active temporary tables. The table
contains metadata and reports on all user and
system-created temporary tables that are active within a
given InnoDB instance. The table is created when the first
SELECT statement is run against it.
InnoDB now supports MySQL-supported
spatial data types. Prior to this release, InnoDB would
store spatial data as binary
BLOB remains the underlying data type
but spatial data types are now mapped to a new InnoDB
internal data type,
There is now a separate tablespace for all non-compressed
InnoDB temporary tables. The new tablespace is always
recreated on server startup and is located in
DATADIR by default. A newly added
configuration file option,
allows for a user-defined temporary data file path.
In MySQL 5.7.2, innochecksum functionality is enhanced with several new options and extended capabilities. See Section 4.6.1, “innochecksum — Offline InnoDB File Checksum Utility”.
A new type of non-redo undo log for both normal and
compressed temporary tables and related objects now
resides in the temporary tablespace. For more information,
see Section 18.104.22.168, “
InnoDB Temporary Table Undo Logs”.
In MySQL 5.7.2,
InnoDB buffer pool dump
and load operations are enhanced. A new system variable,
allows you to specify the percentage of most recently used
pages in each buffer pool to read out and dump. When there
is other I/O activity being performed by
InnoDB background tasks,
InnoDB attempts to limit the number of
buffer pool load operations per second using the
In MySQL 5.7.3, support is added to
InnoDB for full-text parser plugins.
For information about full-text parser plugins, see
Section 22.214.171.124, “Full-Text Parser Plugins” and
Section 126.96.36.199, “Writing Full-Text Parser Plugins”.
As of MySQL 5.7.4,
multiple page_cleaner threads for flushing dirty pages
from buffer pool instances. A new system variable,
innodb_page_cleaners, is used to
specify the number of page_cleaner threads. The default
1 maintains the pre-MySQL
5.7.4 configuration in which there is a single
page_cleaner thread. This enhancement builds on work
completed in MySQL 5.6.2, which introduced a single
page_cleaner thread to offload buffer pool flushing work
InnoDB master thread.
As of MySQL 5.7.4, MySQL supports rebuilding regular and
InnoDB tables using
ALGORITHM=INPLACE) for the following
The Fusion-io Non-Volatile Memory (NVM) file system on
Linux provides atomic
write capability, which makes the
buffer redundant. In MySQL 5.7.4, the
InnoDB doublewrite buffer is
automatically disabled for system tablespace files
located on Fusion-io devices that support atomic writes.
As of MySQL 5.7.4,
InnoDB supports the
Tablespace feature for partitioned
InnoDB tables and individual
InnoDB table partitions. This
enhancement eases backup procedures for partitioned tables
and enables copying of partitioned tables and individual
table partitions between MySQL instances. For additional
information, see Section 188.8.131.52, “Copying Tablespaces to Another Server (Transportable Tablespaces)”.
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.13, “MySQL Enterprise Audit Log Plugin”, and Section 184.108.40.206, “The Audit Log File”.
MySQL now supports stacked diagnostics areas. When the
diagnostics area stack is pushed, the first (current)
diagnostics area becomes the second (stacked) diagnostics
area and a new current diagnostics area is created as a copy
of it. Within a condition handler, executed statements
modify the new current diagnostics area, but
DIAGNOSTICS can be used to inspect the stacked
diagnostics area to obtain information about the condition
that caused the handler to activate, independent of current
conditions within the handler itself. (Previously, there was
a single diagnostics area. To inspect handler-activating
conditions within a handler, it was necessary to check this
diagnostics area before executing any statements that could
change it.) See Section 220.127.116.11, “
GET DIAGNOSTICS Syntax”, and
Section 18.104.22.168, “The MySQL Diagnostics Area”.
EXPLAIN can now be used to
obtain the execution plan for an explainable statement
executing in a named connection:
options] FOR CONNECTION
For more information, see Section 8.8.3, “Obtaining Execution Plan Information for a Named Connection”.
Previously, a table could have at most one trigger for each
combination of trigger event
DELETE) and action time
limitation has been lifted and multiple triggers are
permitted. For more information, see
Section 18.3, “Using Triggers”.
The mysql client now has a
--syslog option that causes
interactive statements to be sent to the system
syslog facility. Logging is suppressed
for statements that match the default “ignore”
pattern list (
as well as statements that match any patterns specified
See Section 22.214.171.124, “mysql Logging”.
The MySQL test suite now uses
the default storage engine.
mysql client. Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.
Database name rewriting with mysqlbinlog.
Renaming of databases by mysqlbinlog when
reading from binary logs written using the row-based format
is now supported using the
added in MySQL 5.7.1.
This option uses the format
You can implement multiple rewrite rules, by specifying the
option multiple times.
HANDLER with partitioned tables.
HANDLER statement may now
be used with user-partitioned tables. Such tables may use
any of the available partitioning types (see
Section 17.2, “Partitioning Types”).
Index condition pushdown support for partitioned tables.
In MySQL 5.7.3 and later, queries on partitioned tables
MyISAM storage engine may
employ the index condition pushdown optimization that was
introduced in MySQL 5.6. See
Section 126.96.36.199, “Index Condition Pushdown Optimization”, for
Master dump thread improvements. The master dump thread was refactored to reduce lock contention and improve master throughput. Previous to MySQL 5.7.2, the dump thread took a lock on the binary log whenever reading an event; in MySQL 5.7.2 and later, this lock is held only while reading the position at the end of the last successfully written event. This means both that multiple dump threads are now able to read concurrently from the binary log file, and that dump threads are now able to read while clients are writing to the binary log.
MySQL 5.7.4 includes a
set that supports the China National Standard GB18030
character set. For more information about MySQL character
set support, see Section 10.1, “Character Set Support”.
Changing the replication master without
In MySQL 5.7.4 and later, the strict requirement to execute
STOP SLAVE prior to issuing
CHANGE MASTER TO
statement is removed. Instead of depending on whether the
slave is stopped, the behavior of
TO now depends on the states of the slave SQL
thread and slave I/O threads; which of these threads is
stopped or running now determines the options that can or
cannot be used with a
CHANGE MASTER TO
statement at a given point in time. The rules for making
this determination are listed here:
If the SQL thread is stopped, you can execute
CHANGE MASTER TO using any combination
MASTER_DELAY options, even if the slave
I/O thread is running. No other options may be used with
this statement when the I/O thread is running.
If the I/O thread is stopped, you can execute
CHANGE MASTER TO using any of the
options for this statement (in any allowed combination)
MASTER_DELAY, even when the SQL thread
is running. These three options may not be used when the
I/O thread is running.
Both the SQL thread and the I/O thread must be stopped
CHANGE MASTER TO ...
MASTER_AUTO_POSITION = 1.
You can check the current state of the slave SQL and I/O
If you are using statement-based replication and temporary
tables, it is possible for a
TO statement following a
SLAVE statement to leave behind temporary tables on
the slave. As part of this set of improvements, a warning is
now issued whenever
CHANGE MASTER TO is
STOP SLAVE when
statement-based replication is in use and
remains greater than 0.
For more information, see Section 188.8.131.52, “
CHANGE MASTER TO Syntax”,
and Section 16.3.6, “Switching Masters During Failover”.
The following features are deprecated in MySQL 5.7 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
that were deprecated in MySQL 5.6 remain deprecated in 5.7 but
do nothing. Instead, their previous effects are included in
the effects of strict SQL mode
other words, strict mode now means the same thing as the
previous meaning of strict mode plus the
NO_ZERO_IN_DATE modes. This
change reduces the number of SQL modes with an effect
dependent on strict mode and makes them part of strict mode
To prepare for these SQL mode changes, it is advisable before upgrading to read SQL Mode Changes in MySQL 5.7. That discussion provides guidelines to assess whether your applications will be affected by these changes.
NO_ZERO_IN_DATE SQL modes
are still recognized so that statements that name them do not
produce an error, but will be removed in a future version of
MySQL. To make advance preparation for versions of MySQL in
which these modes do not exist, applications should be
modified to not refer to those mode names.
Relying on implicit
GROUP BY sorting in
MySQL 5.7 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.
PARTITIONS keywords for the
EXPLAIN statement. These
keywords are still recognized but are now unnecessary because
their effect is always enabled.
variable. It does nothing and has no effect.
The following constructs are obsolete and have been removed in MySQL 5.7. Where alternatives are shown, applications should be updated to use them.
variable. The only supported value was 1, so it had no
thread_concurrency system variable.
IGNORE clause for
DELAYED is no longer supported. The server
recognizes but ignores the
handles the insert as a nondelayed insert, and generates an
(“INSERT DELAYED is no longer supported. The statement
was converted to INSERT.”) Similarly,
DELAYED is handled as a nondelayed replace. The
DELAYED keyword will be removed in a future
In addition, several
options or features were removed:
--delayed-insert option for
MAX_TIMER_WRITE_DELAYED columns of the
mysqlbinlog no longer writes comments
Database symlinking on Windows using for
.sym files has been removed because it is
redundant with native symlink support available using
symbolic links will be ignored and should be replaced with
symlinks created using mklink. See
Section 184.108.40.206.3, “Using Symbolic Links for Databases on Windows”.
--datadir options for
mysql_upgrade were removed.
Previously, program options could be specified in full or as
any unambiguous prefix. For example, the
--compress option could be
given to mysqldump as
--compr, but not as
because the latter is ambiguous. Option prefixes are no longer
supported; only full options are accepted. This is because
prefixes can cause problems when new options are implemented
for programs and a prefix that is currently unambiguous might
become ambiguous in the future.
InnoDB Tablespace Monitor and
InnoDB Table Monitor are removed in MySQL
5.7.4. For the Tablespace Monitor, equivalent functionality
will be introduced before the GA release of MySQL 5.7. For the
Table Monitor, equivalent information can be obtained from
The specially named tables used to enable and disable the
InnoDB Monitor and
InnoDB Lock Monitor
innodb_lock_monitor) are removed in MySQL
5.7.4 and replaced by two dynamic system variables:
For additional information, see
Section 220.127.116.11, “
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices