MySQL 5.7 Release Notes
This is a milestone release, for use at your own risk. Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. Significant development changes take place in milestone releases and you may encounter compatibility issues, such as data format changes that require attention in addition to the usual procedure of running mysql_upgrade. For example, you may find it necessary to dump your data with mysqldump before the upgrade and reload it afterward. (Making a backup before the upgrade is a prudent precaution in any case.)
MySQL now supports stacked diagnostics areas. When a push to the
diagnostics area stack occurs, 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 GET DIAGNOSTICS Statement, and
The MySQL Diagnostics Area.
Important Change; Replication:
SHOW SLAVE STATUS
when run
concurrently with STOP SLAVE
can
take a long time to execute if the slave SQL thread was in the
midst of applying a large update. To fix this problem, a new
NONBLOCKING
option has been added to the
SHOW SLAVE STATUS
statement. When
this option is used, SHOW SLAVE STATUS
does
not wait on the SQL or I/O threads but returns immediately. This
means that the reported states of these threads may not be
completely up to date when the option is used.
NONBLOCKING
is intended primarily for use by
monitoring tools in which obtaining an immediate response is
more important than having the most timely data.
(Bug #15993588, Bug #67879)
Important Change; Replication:
Added the --idempotent
option for mysqlbinlog, which causes the
MySQL Server to employ idempotent mode. This causes suppression
of all duplicate-key and key-not-found errors when processing
updates from the binary log. The mode is in effect for the
current mysqlbinlog client and client session
only.
Important Change:
INSERT DELAYED
is no longer supported. The server recognizes but ignores the
DELAYED
keyword, handles the insert as a
nondelayed insert, and generates an
ER_WARN_LEGACY_SYNTAX_CONVERTED
warning. (“INSERT DELAYED is no longer supported. The
statement was converted to INSERT.”). Similarly,
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:
The --delayed-insert
option for
mysqldump.
The COUNT_WRITE_DELAYED
,
SUM_TIMER_WRITE_DELAYED
,
MIN_TIMER_WRITE_DELAYED
,
AVG_TIMER_WRITE_DELAYED
, and
MAX_TIMER_WRITE_DELAYED
columns of the
table_lock_waits_summary_by_table
Performance Schema table.
If you upgrade to this MySQL release from an earlier
version, you must run mysql_upgrade (and
restart the server) to incorporate these changes into the
performance_schema
database.
mysqlbinlog no longer writes comments
mentioning INSERT DELAYED
.
Microsoft Windows:
Windows Vista, Windows Server 2008, and newer support native
symbolic linking using the mklink command.
This makes the MySQL Server implementation of database symbolic
links using .sym
files redundant, so that
mechanism is now removed. This change has the following
implications:
Existing .sym
files are now ignored.
Database symbolic links should be recreated using
mklink. See
Using Symbolic Links for Databases on Windows.
The --symbolic-links
and
--skip-symbolic-links
options and the
have_symlink
system
variable now are meaningful only for Unix systems, and not
for Windows.
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. (Bug #66583, Bug #14554568)
The server now issues a warning if an index is created that duplicates an existing index, or an error in strict SQL mode. (Bug #37520, Bug #11748842)
The mysql_clear_password
cleartext
client-side authentication plugin is intended for authentication
schemes that require the server to receive the password as
entered on the client side, without hashing. Because the
password is sent in the clear, this plugin should be used within
the context of an encrypted connection, such as an SSL
connection, to avoid exposing the password over the network. To
make inadvertent use of this plugin less likely, it is now
required that clients explicitly enable it. This can be done
several ways:
Set the LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN
environment variable to a value that begins with
1
, Y
, or
y
. This enables the plugin for all client
connections.
The mysql, mysqladmin,
and mysqlslap client programs support an
--enable-cleartext-plugin
option that
enables the plugin on a per-invocation basis.
The mysql_options()
C API
function supports a
MYSQL_ENABLE_CLEARTEXT_PLUGIN
option that
enables the plugin on a per-connection basis. Also, any
program that uses libmysqlclient
and
reads option files can enable the plugin by including an
enable-cleartext-plugin
option in an
option group read by the client library.
Important Change; Replication:
Statements involving the Performance Schema tables should not be
written to the binary log, because the content of these tables
is applicable only to a given MySQL Server instance, and may
differ greatly between different servers in a replication
topology. The database administrator should be able to configure
(INSERT
,
UPDATE
, or
DELETE
) or flush
(TRUNCATE TABLE
) performance
schema tables on a single server without affecting others.
However, when using replication with GTIDs enabled (see
Replication with Global Transaction Identifiers), warnings about unsafe
statements updating Performance Schema tables were elevated to
errors, preventing the use of
performance_schema
and GTIDs together.
Similar problems were encountered with replication and system logging tables when GTIDs were enabled.
This fix introduces the concept of a nonreplicated or local table. Now when MySQL replication encounters a table that is marked as local, updates to this table are ignored.
This fix defines as local the following tables, which are no longer replicated:
All tables in the performance_schema
database
mysql.general_log
mysql.slow_log
mysql.slave_relay_log_info
mysql.slave_master_info
mysql.slave_worker_info
Before this fix, statements using the
performance_schema
and other tables just
listed were handled by being marked as unsafe for replication,
which caused warnings during execution; the statements were
nonetheless written to the binary log, regardless of the logging
format in effect.
Existing replication behavior for tables in the
INFORMATION_SCHEMA
database is not changed by
this fix.
For more information, see MySQL Performance Schema. See also MySQL Server Logs, and Replication Metadata Repositories. For information about general and slow query log tables, see Selecting General Query Log and Slow Query Log Output Destinations. (Bug #14741537)
Important Change:
Formerly, the ExtractValue()
and
UpdateXML()
functions supported a
maximum length of 127 characters for XPath expressions supplied
to them as arguments. This limitation has now been removed.
(Bug #13007062, Bug #62429)
InnoDB:
Creating and altering tables repeatedly would result in a memory
leak that was due to a duplicate key error. The duplicate key
error occurred because the
row_merge_build_indexes
function did not call
row_fts_psort_info_destroy
often enough. As
full-text search indexes were created with a unique index, the
unique index failed due to the duplicate key error, and
full-text search build resource would not be released.
(Bug #14759111)
InnoDB: During an online DDL operation, a duplicate key error could be incorrectly issued if a record was inserted and subsequently updated while the table was being rebuilt. (Bug #14723456)
InnoDB:
InnoDB IO threads within Performance Schema were exposed with
the following name:
“io_handler_thread
”. This fix
implements specific keys such as
io_read_handler_thread
,
io_write_handler_thread
,
io_ibuf_handler_thread
to differentiate
InnoDB IO threads within Performance Schema.
(Bug #14670810)
InnoDB:
If the server crashed at a precise moment during an
ALTER TABLE
operation that
rebuilt the clustered
index for an InnoDB
table, the
original table could be inaccessible afterward. An example of
such an operation is ALTER TABLE ... ADD PRIMARY
KEY
The fix preserves the original table if the server
halts during this operation. You might still need to rename the
.ibd
file manually to restore the original
table contents: in MySQL 5.6 and higher, rename from
#sql-ib$
to
new_table_id
.ibd
within the database directory; prior to MySQL 5.6, the temporary
file to rename is
table_name
.ibd
or
table_name
#1#2
.
(Bug #14669848)
InnoDB:
Inserting data of varying record lengths into an
InnoDB
table that used
compression could cause
the server to halt with an error.
(Bug #14554000, Bug #13523839, Bug #63815, Bug #12845774, Bug #61456, Bug #12595091, Bug #61208)
InnoDB: This fix addresses an assert condition that would occur when inserting large BLOBs into tablespaces with a 4KB physical page size or into some compressed tables. Extents would not be allocated soon enough for tablespaces with smaller physical page sizes. (Bug #14520559)
InnoDB: If a table was defined with an index key length very close to the upper length limit of 3072, a query against that table could cause a serious error. (Bug #14500557, Bug #14537695)
InnoDB:
In debug builds, a mismatch in the InnoDB
PAGE_FREE
list would cause an assertion.
(Bug #12701488)
InnoDB: On Linux systems, certain I/O requests that read or wrote fewer than the requested number of bytes could cause the server to crash. This issue could happen more frequently with asynchronous I/O requests. The messages did not clearly identify what type of error occurred:
InnoDB: Operating system error number 0 in a file operation. InnoDB: Error number 0 means 'Success'.
With this fix, MySQL retries the operation several times before
giving up. (The number of retries is defined by the constant
NUM_RETRIES_ON_PARTIAL_IO
in the source code,
default value 10.)
(Bug #11761646, Bug #54160)
Partitioning:
When the server is started with
--skip-partition
, it should reject DDL or DML
statements on partitioned tables. However, for
DROP TABLE
, the server dropped
the .frm
file, and for
RENAME TABLE
, the server renamed
the .frm
file.
(Bug #11763795)
Replication:
mysqlbinlog did not properly decode
DECIMAL
values in a row-based
binary log. This could cause invalid values to be printed out
for DECIMAL
columns.
(Bug #14309019)
References: See also: Bug #17544169.
Replication:
mysqlbinlog
-v
-v
prints in verbose mode, with comments on
data column types, from a binary log file. When
mysqlbinlog -v -v
encountered a column data value which was
NULL
, the column's data type was not updated;
as a result, the data type of the previous column was printed
instead, or—in the case where this was the table's first
column—the type was shown as <an
integer>
. Now in such cases, the data type is shown
correctly.
(Bug #14171756)
Replication:
When using mysqlbinlog with the
--verbose
option to read a
binary log written by a MySQL server using row-based or
mixed-format logging, invalid SQL could be produced when
comments appeared inside BINLOG
statements. One way in which this could happen was when a
function that updated data was used within an
INSERT ...
SELECT
statement.
(Bug #12889121)
Replication:
mysql_upgrade on the master broke replication
when the slave was run with
--log-output
equal to
FILE
or NONE
.
(Bug #11763447)
Replication:
Issuing STOP SLAVE
caused a
spurious Error reading packet from server: Lost
connection to MySQL server during query message to
be written to the error log.
(Bug #11761457, Bug #12977988, Bug #53955)
Replication:
When an error occurs in the slave SQL thread, this causes the
Slave_SQL_Error
and
Slave_SQL_Errno
columns from
SHOW SLAVE STATUS
to display the
reason for the error. The error number should be one of the
usual constants ER_* defined in
sql/share/errmsg.txt
, and the error message
should be the corresponding string. However, in some cases,
Slave_SQL_Errno
was set to something other
than an ER_*
number, and
Slave_SQL_Error
to a hard-coded error message
rather than a translatable string from
sql/share/errmsg.txt
. Now all errors shown
by SHOW SLAVE STATUS
originate in
sql/share/errmsg.txt
, as expected.
(Bug #11760365, Bug #52768)
Microsoft Windows: On Microsoft Windows, CMake entries for POSIX API's not found on Microsoft Windows were added to the CMake cache. This decreases the number of expected "Not found" errors while compiling MySQL. (Bug #14790333)
Microsoft Windows:
On Microsoft Windows, queries referring to a table with invalid
characters would search the system for invalid file names. The
generated system error code
(ERROR_INVALID_NAME
) was not recognized by
MySQL, so this unknown error would be reported to the server log
as "ERRNO: 22 - INVALID ARGUMENT". MySQL now recognizes these
errors and reports them as the table does not existing, and it
no longer logs them to the server error log.
(Bug #14642248)
Microsoft Windows:
On Windows, starting the server with
--log-error
and
--console
caused the server to
write to the log file but not the console. Before MySQL 5.5.3,
this occurred only if --log-error
was specified after --console
.
Now, --console
overrides
--log-error
no matter the option
order so that --console
produces
console output in all cases.
(Bug #14207773, Bug #65592)
Microsoft Windows:
It was possible to specify a Named Pipe that was already in use.
This is no longer allowed, as an error is now emitted and the
process is aborted. After mysqld.exe
was
started in Named Pipe mode with a pipe name that was already
used by a different instance, neither instance was able to shut
down properly when a shutdown command was received from a
TCP
socket in any of the processes.
Therefore, mysqld.exe
was not terminated.
(Bug #13891058, Bug #61885)
Microsoft Windows:
On Microsoft Windows, a failed API or function call in
mysqld.exe
could sometimes report the error
code 22, instead of the proper error code.
(Bug #11763004)
NDB Replication:
Transactions originating on a replication master are applied on
slaves as if using AO_AbortError
, but
transactions replayed from a binary log were not. Now
transactions being replayed from a log are handled in the same
way as those coming from a “live” replication
master.
See NdbOperation::AbortOption, for more information. (Bug #14615095)
Joins of exactly 32 tables and containing a
HAVING
clause returned an empty result.
(Bug #15972635)
The parser rejected some legal
UNION
statements.
(Bug #14730856)
Setting thread_cache_size
to a
negative value at server startup resulted in a value of 16384
rather than 0.
(Bug #14683107)
XA RECOVER
displayed nonprintable characters in the XID data. Now such
characters are hex encoded.
(Bug #14670465)
There was no warning at startup if the server was started with
an invalid query_cache_size
value.
(Bug #14576423)
The return value from
IS_USED_LOCK()
was reported using
the wrong data type.
(Bug #14575699)
When resolving outer fields,
Item_field::fix_outer_fields()
creates new
Item_ref
s for each execution of a prepared
statement, so these must be allocated in the runtime memroot.
The memroot switching before resolving
JOIN::having
caused these to be allocated in
the statement root, leaking memory for each prepared statement
execution.
(Bug #14409015)
Activation of a stored program handler did not preserve the current diagnostics stack. (Bug #14342913)
In debug builds, killing a HELP
statement caused an assertion to be raised.
(Bug #14221840)
If an error occurred during evaluation of the
BEFORE
expression of a
PURGE BINARY
LOGS BEFORE
statement, the statement did not abort as
it should have and later raised an assertion.
(Bug #14215847)
For the index merge access method, the optimizer could make a suboptimal choice of indexes to use. (Bug #14095506)
An assertion could be raised if the attempt to open the
.frm
file for a temporary table failed.
(Bug #13359247)
If the state of the Event Scheduler was changed during server shutdown, the server could crash. (Bug #13002460)
When storing the definition for a view that used the
UPPER()
or
LOWER()
function, the function
call was replaced by UCASE()
or
LCASE()
, respectively (as shown
in the output of SHOW CREATE
VIEW
). This was in spite of the fact that
UPPER()
and LOWER()
are
standard, with UCASE()
and
LCASE()
being MySQL synonyms for these. This
made it more difficult to move databases between MySQL and other
database systems.
With this fix, calls to UPPER()
and
LOWER()
within views are no longer rewritten
when storing their definitions; instead,
UCASE()
is now rewritten as
UPPER()
in stored view definitions, and
LCASE()
as LOWER()
, which
increases the portability of the views.
(Bug #12844279)
For queries that accessed an
INFORMATION_SCHEMA
table in a subquery, an
attempt to lock a mutex that had already been locked could cause
a server crash.
(Bug #11765744)
A view using INTERVAL() could be created, but it was not possible to select from the view, nor could it be shown with SHOW CREATE VIEW. (Bug #11753832)
The Range checked for each record
optimization is now used for conditions with outer query
references.
(Bug #11750963)
For an ALTER TABLE
statement that
renamed or changed the default value of a
BINARY
column, the alteration was
done using a table copy and not in place.
(Bug #67141, Bug #14735373, Bug #69580, Bug #17024290)
For queries using ref
access
on string data types, the ref
access condition could be evaluated again as part of the query
condition or pushed down as an index condition to the storage
engine.
(Bug #66983, Bug #14682735)
Concurrent execution of DROP
DATABASE
and any of CREATE
FUNCTION
, CREATE
PROCEDURE
, or CREATE
EVENT
could be written to the binary log in the wrong
order, causing replication failure.
(Bug #65428, Bug #14127220)
For a view defined on a UNION
,
the server could create an invalid view definition.
(Bug #65388, Bug #14117018, Bug #72018, Bug #18405221)
If read_only
is enabled, it is still
permitted to create TEMPORARY
tables. But in
this case, a non-TEMPORARY
table with the
same name could also be created, which should not be permitted.
(Bug #64992, Bug #13969578)
Enabling the session value of
low_priority_updates
had no
effect for INSERT
statements.
(Bug #64892, Bug #13939940)
References to a stored function without a database name
qualifier while there was no default database resulted in an
ER_SP_DOES_NOT_EXIST
error
rather than ER_NO_DB_ERROR
.
(Bug #64692, Bug #13864485)
The server refused client connections while executing
FLUSH PRIVILEGES
.
(Bug #63178, Bug #13418619)
A view was created with an incorrect definition if the
WHERE
clause contained string literals and
character_set_client
and
character_set_connection
were
set to different character sets.
(Bug #63094, Bug #13520710)
SHOW CREATE VIEW
failed if the
tables underlying the view were changed.
(Bug #61718, Bug #12762393)
Concurrent inserts were blocked by selects if the inserts were generated from within a stored procedure. (Bug #58689, Bug #11765698)
An INSERT INTO ...
SELECT
statement that inserted no rows unnecessarily
invalidated statements in the query cache that used the target
table.
(Bug #50065, Bug #11757947)
Using ALTER TABLE
to rename a
table to .
resulted in a table with no name.
(Bug #49636, Bug #11757569)
SHOW GLOBAL
STATUS
caused performance problems on busy servers due
to lock contention.
(Bug #42930, Bug #11751904)
INSERT INTO ... SELECT
... ON DUPLICATE KEY UPDATE
and
LOAD DATA CONCURRENT
REPLACE
took too weak a lock, leading to the
possibility of concurrent SELECT
statements
returning inconsistent results.
(Bug #38046, Bug #11749055)
An event was not dropped from the mysql.event
table under these circumstances: The event was created while the
event scheduler was enabled; the scheduler was disabled and
re-enabled; the event expiration time was reached.
(Bug #34804, Bug #11748012)