MySQL 5.7 Release Notes
MySQL attempts to use an ordered index for any ORDER
BY
or GROUP BY
query that has a
LIMIT
clause, overriding any other choices
made by the optimizer, whenever it determines that this would
result in faster execution. Because the algorithm for making
this determination makes certain assumptions about data
distribution and other conditions, it may not always be
completely correct, and it is possible in some cases that
choosing a different optimization for such queries can provide
better performance. To handle such occurrences, it is now
possible to disable this optimization by setting the
optimizer_switch
system
variable's
prefer_ordering_index
flag to
off
.
For more information about this flag and examples of its use, see Switchable Optimizations, and LIMIT Query Optimization.
Our thanks to Jeremy Cole for the contribution. (Bug #31686878, WL #14315)
References: See also: Bug #97001, Bug #30348211.
The linked OpenSSL library for MySQL Server has been updated to version 1.1.1i. Issues fixed in the new OpenSSL version are described at https://www.openssl.org/news/cl111.txt and https://www.openssl.org/news/vulnerabilities.html. (Bug #32260610)
When invoked with the
--all-databases
option,
mysqldump now dumps the
mysql database first, so that when the dump
file is reloaded, any accounts named in the
DEFINER
clause of other objects will already
have been created.
(Bug #32141046)
InnoDB: The full-text search synchronization thread attempted to read a previously-freed word from the index cache. (Bug #31310404)
InnoDB:
Calls to numa_all_nodes_ptr
were replaced by
the numa_get_mems_allowed()
function. Thanks
to Daniel Black for the contribution.
(Bug #24693086, Bug #83044)
Replication: As the number of replicas replicating from a semisynchronous source server increased, locking contention could result in a performance degradation. The locking mechanisms used by the plugins have been changed to use shared locks where possible, avoid unnecessary lock acquisitions, and limit callbacks. The new behaviors can be implemented by enabling the following system variables:
replication_sender_observe_commit_only=1
limits callbacks.
replication_optimize_for_static_plugin_config=1
adds shared locks and avoids unnecessary lock acquisitions.
This system variable must be disabled if you want to
uninstall the plugin.
Both system variables can be enabled before or after installing the semisynchronous replication plugin, and can be enabled while replication is running. Semisynchronous replication source servers can also get performance benefits from enabling these system variables, because they use the same locking mechanisms as the replicas. (Bug #30519928)
Replication: On a multi-threaded replica where the commit order is preserved, worker threads must wait for all transactions that occur earlier in the relay log to commit before committing their own transactions. If a deadlock occurs because a thread waiting to commit a transaction later in the commit order has locked rows needed by a transaction earlier in the commit order, a deadlock detection algorithm signals the waiting thread to roll back its transaction. Previously, if transaction retries were not available, the worker thread that rolled back its transaction would exit immediately without signalling other worker threads in the commit order, which could stall replication. A worker thread in this situation now waits for its turn to call the rollback function, which means it signals the other threads correctly. (Bug #26883680, Bug #87796)
Replication:
GTIDs are only available on a server instance up to the number
of non-negative values for a signed 64-bit integer (2 to the
power of 63 minus 1). If you set the value of
gtid_purged
to a number that
approaches this limit, subsequent commits can cause the server
to run out of GTIDs and take the action specified by
binlog_error_action
. From MySQL
8.0.23, a warning message is issued when the server instance is
approaching the limit.
(Bug #26035544)
Group Replication:
When the system variable
transaction_write_set_extraction=XXHASH64
is set, which is the default in MySQL 8.0 and a requirement for
Group Replication, the collection of writes for a transaction
previously had no upper size limit. Now, for standard source to
replica replication, the numeric limit on write sets specified
by
binlog_transaction_dependency_history_size
is applied, after which the write set information is discarded
but the transaction continues to execute. Because the write set
information is then unavailable for the dependency calculation,
the transaction is marked as non-concurrent, and is processed
sequentially on the replica. For Group Replication, the process
of extracting the writes from a transaction is required for
conflict detection and certification on all group members, so
the write set information cannot be discarded if the transaction
is to complete. The byte limit set by
group_replication_transaction_size_limit
is applied instead of the numeric limit, and if the limit is
exceeded, the transaction fails to execute.
(Bug #32019842)
Microsoft Windows: On Windows, running the MySQL server as a service caused shared-memory connections to fail. (Bug #32009251)
The MRR iterator normally filters out NULL
keys by checking impossible_null_ref()
, but
when a join condition either contained an IS
NULL
predicate, or used the
NULL
-safe equals operator
≪=>
, the optimizer had to check whether
the join condition used the predicate terms as part of its join
condition, and not set the internal flag
HA_MRR_NO_NULL_ENDPOINTS
in such cases. Now
we check, using a bitmask, whether the each column in the key
rejects NULL
, in which case we can set
HA_MRR_NO_NULL_ENDPOINTS
without further
checks.
(Bug #32774281)
The server did not handle all cases of the
WHERE_CONDITION
optimization correctly.
(Bug #31905199)
For the engines which support primary key extension, when the
total key length exceeded MAX_KEY_LENGTH
or
the number of key parts exceeded
MAX_REF_PARTS
, key parts of primary keys
which did not fit within these limits were not added to the
secondary key, but key parts of primary keys were
unconditionally marked as part of secondary keys.
This led to a situation in which the secondary key was treated as a covering index, which meant sometimes the wrong access method was chosen.
This is fixed by modifying the way in which key parts of primary keys are added to secondary keys so that those which do not fit within which do not fit within the limits mentioned previously mentioned are cleared. (Bug #31617858)
Privileges for some
INFORMATION_SCHEMA
tables were
checked incorrectly.
(Bug #31553323)
In certain cases, the server did not handle multiply-nested subqueries correctly. (Bug #31472704)
Certain accounts could cause server startup failure if the
skip_name_resolve
system
variable was enabled.
(Bug #31018510)
Client programs could unexpectedly exit if communication packets contained bad data. (Bug #30890850)
A buffer overflow in the client library was fixed. (Bug #30885987)
mysql_config_editor incorrectly treated
#
in password values as a comment character.
(Bug #29861961, Bug #95597)