This section summarizes what has been added to and removed from MySQL 5.7.
The following features have been added to MySQL 5.7:
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
ALTER TABLE Syntax".
InnoDB enhancements. These
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
VARCHAR 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, in-place
ALTER TABLE only supports increasing
VARCHAR size from 0 to 255 bytes or increasing
size from a value equal to or greater than 256 bytes.
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 copy (
ALGORITHM=COPY). For example, attempting to change
column size from 255 to 256 using in-place
ALTER 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 changecolumn type INPLACE. Try ALGORITHM=COPY.
InnoDB temporary table metadata is no longer stored to InnoDB system
tables. Instead, a new table,
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
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
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,
innodb_temp_data_file_path, allows for a user-defined
temporary data file path.
In MySQL 5.7.2, innochecksum functionality has been 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 will now reside in the temporary tablespace. For more
information, see Section 220.127.116.11, "
InnoDB Temporary Table Undo Logs".
Condition handling. 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
GET STACKED 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
GET DIAGNOSTICS Syntax", and Section
18.104.22.168, "The MySQL Diagnostics Area".
Logging. The mysql client now has a
--syslog option that causes interactive statements to be sent to the
syslog facility. Logging is suppressed for statements that match the
default "ignore" pattern list (
"*IDENTIFIED*:*PASSWORD*"), as well as statements that match any patterns
specified using the
option. See Section
22.214.171.124, "mysql Logging".
Test suite. The MySQL test suite now uses
InnoDB as 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
--rewrite-db option added in MySQL 5.7.1.
This option uses the format
--rewrite-db='. You can implement multiple rewrite
rules, by specifying the option multiple times.
HANDLER with partitioned tables. The
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").
The following constructs are obsolete and have been removed in MySQL 5.7. Where alternatives are shown, applications should be updated to use them.
innodb_mirrored_log_groups system variable. The
only supported value was 1, so it had no purpose.
INSERT DELAYED is no longer supported. The server recognizes but ignores
DELAYED keyword, handles the insert as a nondelayed insert, and
ER_WARN_LEGACY_SYNTAX_CONVERTED warning. ("INSERT DELAYED is no longer supported. The statement was converted to INSERT.")
REPLACE DELAYED is handled as a nondelayed replace. The
DELAYED keyword will be removed in a future release.
In addition, several
DELAYED-related options or features were removed:
--delayed-insert option for mysqldump.
columns of the Performance Schema
mysqlbinlog no longer writes comments
Database symlinking on Windows using for
has been removed because it is redundant with native symlink support available using mklink. Any
symbolic links will be ignored and should be replaced with symlinks created using mklink.
See Section 126.96.36.199.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
but not as
--comp 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.