MySQL 8.0 Release Notes
For general information about upgrades, downgrades, platform support, etc., please visit https://docs.oracle.com/cd/E17952_01/mysql-8.0-relnotes-en/.
In the documentation for MySQL 8.0.21, we have started changing the term “master” to “source”, the term “slave” to “replica”, the term “whitelist” to “allowlist”, and the term “blacklist” to “blocklist”. There are currently no changes to the product's syntax, so these terms are still present in the documentation where the current code requires their use. See the blog post MySQL Terminology Updates for more information.
You can now set per-user comments and attributes when creating
or updating MySQL user accounts. A user comment consists of
arbitrary text passed as the argument to a
COMMENT
clause used with a
CREATE USER
or
ALTER USER
statement. A user
attribute consists of data in the form of a JSON object passed
as the argument to an ATTRIBUTE
clause used
with either of these two statements. The attribute can contain
any valid key-value pairs in JSON object notation.
For example, the first of the following two statements creates a
user account bill@localhost
with the comment
text This is Bill's user account
. The second
statement adds a user attribute to this account, using the key
email
, with the value
bill@example.com
.
CREATE USER 'bill'@'localhost' COMMENT 'This is Bill\'s user account'; ALTER USER 'mary'@'localhost' ATTRIBUTE '{"email":"bill@example.com"}';
Only one of COMMENT
or
ATTRIBUTE
can be used in the same
CREATE USER
or ALTER USER
statement.
User comments and user attributes are stored together internally
as a JSON object, with the comment text as the value of an
element with the key comment
. You can
information retrieve user comments and user attributes from the
ATTRIBUTE
column of the
INFORMATION_SCHEMA.USER_ATTRIBUTES
table; since this data is in JSON format, you can work with it
using MySQL's JSON function and operators (see
JSON Functions). Changes to an existing user
attribute are merged with its current value, as you had used
JSON_MERGE_PATCH()
; new key-value
pairs are appended to the attribute, and new values for existing
keys overwrite their previous values.
To remove a given key-value pair from a user attribute, use
ALTER USER
.
user
ATTRIBUTE
'{"key
":null}'
For more information and examples, see CREATE USER Statement, ALTER USER Statement, and The INFORMATION_SCHEMA USER_ATTRIBUTES Table. (WL #13562)
References: See also: Bug #31067575.
Per OpenSSL recommendation, x509_check_host()
and X509_check_ip_asc()
calls in the C client
library were replaced, respectively, with
X509_VERIFY_PARAM_set1_host()
and
X509_VERIFY_PARAM_set1_ip_asc()
calls.
(Bug #29684791)
The MySQL C API now supports compression for asynchronous
functions. This means that the
MYSQL_OPT_COMPRESSION_ALGORITHMS
and
MYSQL_OPT_ZSTD_COMPRESSION_LEVEL
options for
the mysql_options()
function now
affect asynchronous operations, not just synchronous operations.
See mysql_options().
Our thanks to Facebook for the contribution. (Bug #96802, Bug #30284871, WL #13510)
The minimum version of the Boost library for server builds is now 1.72.0. (Bug #30963985)
tcmalloc
is no longer a permitted value for
the mysqld_safe
--malloc-lib
option.
(Bug #31372027)
MySQL Server supports a “main” network interface for ordinary client connections, and optionally an administrative network interface for administrative client connections. Previously, the main and administrative interfaces used the same TLS configuration, such as the certificate and key files for encrypted connections. It is now possible to configure TLS material separately for the administrative interface:
There are new configuration parameters that apply specifically to the administrative interface.
The ALTER INSTANCE RELOAD TLS
statement is extended with a FOR CHANNEL
clause that enables specifying the channel (interface) for
which to reload the TLS context.
The new Performance Schema
tls_channel_status
table
exposes TLS context properties for the main and
administrative interfaces.
For backward compatibility, the administrative interface uses the same TLS context as the main interface unless some nondefault TLS parameter value is configured for the administrative interface.
For more information, see Administrative Interface Support for Encrypted Connections, ALTER INSTANCE Statement, and The tls_channel_status Table. (WL #13850)
Partitioning:
Columns with index prefixes are not supported as part of a
table's partitioning key; previously such columns were
simply omitted by the server when referenced in creating,
altering, or upgrading a table that was partitioned by key, with
no indication that this omission had taken place, except when
the proposed partitioning function used only columns with
prefixes, in which case the statement failed with an error
message that did not identify the actual source of the problem.
This behavior is now deprecated, and subject to removal in a
future release in which using any such columns in the proposed
partitioning key will cause the CREATE
TABLE
or ALTER TABLE
statement in which they occur to be rejected.
When one or more columns using index prefixes are specified as
part of the partitioning key, a warning is now generated for
each such column. In addition, when a
CREATE TABLE
or
ALTER TABLE
statement is rejected
because all columns specified in the proposed partitioning key
employ index prefixes, the error message returned now makes
clear the reason the statement did not succeed. This includes
cases in which the columns proposed the partitioning function
are defined implicitly as those in the table's primary key
by employing an empty PARTITION BY KEY()
clause.
For more information and examples, see Column index prefixes not supported for key partitioning, and KEY Partitioning. (Bug #29941932, Bug #29941959, Bug #31100205, WL #13588)
References: See also: Bug #29942014.
Added the JSON_VALUE()
function,
which simplifies creating indexes on
JSON
columns. A call to
JSON_VALUE(
is equivalent to
calling json_doc
,
path
RETURNING
type
)CAST
(
JSON_UNQUOTE
(
JSON_EXTRACT(
json_doc
,
path
) ) AS
, where
type
)json_doc
is a JSON document,
path
is a JSON path expression
pointing to a single value within the document, and
type
is a data type compatible with
CAST()
. RETURNING
is optional; if no
return type is specified, type
JSON_VALUE()
returns VARCHAR(512)
.
JSON_VALUE()
also supports ON
EMPTY
and ON ERROR
clauses similar
to those used with JSON_TABLE()
.
You can create indexes on a JSON
column using JSON_VALUE()
as shown here:
CREATE TABLE inventory( items JSON, INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ), INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ), INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) ) );
Assuming the items
column contains values
such as '{"name": "hat", "price": "22.95", "quantity":
"17"}'
, you can issue queries, such as the following,
that are able to use these indexes:
SELECT items->"$.price" FROM inventory WHERE JSON_VALUE(items, '$.name' RETURNING CHAR(50)) = "hat"; SELECT * FROM inventory WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01; SELECT items->"$.name" AS item, items->"$.price" AS amount FROM inventory WHERE JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED) > 500;
For more information and examples, see the description of the
JSON_VALUE()
function.
(WL #12228)
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 #97001, Bug #30348211, WL #13929)
References: See also: Bug #31686878.
A single-table UPDATE
or
DELETE
statement that uses a
subquery having a [NOT] IN
or [NOT]
EXISTS
predicate can now in many cases make use of a
semijoin transformation or subquery materialization. This can be
done when the statement does not use LIMIT
or
ORDER BY
, and when semijoin or subquery
materialization is allowed by any optimizer hints used in the
subquery, or by the value of the
optimizer_switch
server system
variable.
You can see when the semijoin optimization or subquery
materialization is used for an eligible single-table
DELETE
or UPDATE
due to
the presence of a join_optimization
object in
the optimizer trace. You can also see that the conversion is
performed by checking the output of
EXPLAIN
FORMAT=TREE
; if the optimization is not performed,
this shows <not executable by iterator
executor>
, while a multi-table statement reports a
full plan.
As part of this work, semi-consistent reads are now supported by
multi-table UPDATE
of
InnoDB
tables, when the transaction
isolation level is weaker than
REPEATABLE READ
.
(Bug #35794, Bug #96423, Bug #11748293, Bug #30139244, WL #6507)
Added the optimizer_switch
flag
subquery_to_derived
. When this
flag is set to on
, the optimizer transforms
eligible scalar subqueries into left outer joins (and in some
cases, inner joins) on derived tables. This optimization can be
applied to a subquery which meets the following conditions:
It uses one or more aggregate functions but no
GROUP BY
.
It is part of a SELECT
,
WHERE
, JOIN
, or
HAVING
clause.
It is not a correlated subquery.
It does not make use of any nondeterminstic functions.
ANY
and ALL
subqueries
which can be rewritten to use
MIN()
or
MAX()
are also not affected.
With subquery_to_derived=on
, the optimization
can also be applied to a table subquery which is the argument to
IN
, NOT IN
,
EXISTS
, or NOT EXISTS
, and
which does not contain a GROUP BY
clause.
The subquery_to_derived
flag is set to
off
by default, since it generally does not
improve performance, and its intended use for the most part is
for testing purposes.
For more information, see Switchable Optimizations, for more information and examples. See also Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization, and LIMIT Query Optimization. (WL #13851)
Building on work done in MySQL 8.0.18, the server now performs
injection of casts into queries to avoid mismatches when
comparing string data types with those of numeric or temporal
types; as when comparing numeric and temporal types, the
optimizer now adds casting operations in the item tree inside
expressions and conditions in which the data type of the
argument and the expected data type do not match. This makes
queries in which string types are compared with numeric or
temporal types equivalent to queries which are compliant with
the SQL standard, while maintaining backwards compatibility with
previous releases of MySQL. Such casts are now performed
whenever string values are compared to numeric or temporal
values using any of the standard numeric comparison operators
(=
,
>=
,
>
,
<
,
<=
,
<>
/!=
,
and
<=>
).
Such implicit casts are now performed between a string type
(CHAR
,
VARCHAR
,
BINARY
,
VARBINARY
,
BLOB
,
TEXT
,
ENUM
, or
SET
) and a numeric type
(SMALLINT
,
TINYINT
,
MEDIUMINT
,
INT
/INTEGER
,
BIGINT
;
DECIMAL
/NUMERIC
;
FLOAT
,
DOUBLE
,
REAL
; and
BIT
) by casting the string value
to DOUBLE
; if the numeric value
is not already of type DOUBLE
,
FLOAT
, or
REAL
, it is also cast to
DOUBLE
. A
YEAR
value is also cast to
DOUBLE
when compared with a string value (as
is the string value). For such comparisons between string types
and TIMESTAMP
or
DATETIME
values, the arguments
are cast as DATETIME
; when a string type is
compared with a DATE
value, the
string is cast to DATE
.
For example, a query such as SELECT * FROM t1 JOIN t2
ON t1.
is rewritten and
executed as char_col
=
t2.int_col
SELECT * FROM t1 JOIN t2 ON
CAST(t1.
,
and char_col
AS DOUBLE) =
CAST(t2.int_col
AS DOUBLE)SELECT * FROM t1 JOIN t2 ON
t1.
is
transformed to varchar_col
=
t2.timestamp_col
SELECT * FROM t1 JOIN t2 ON
CAST(t1.
prior to execution.
varchar_col
AS DATETIME) =
CAST(t2.timestamp_col
AS
DATETIME)
You can see when casts are injected into a given query by
viewing the output of EXPLAIN
ANALYZE
, EXPLAIN FORMAT=JSON
, or
EXPLAIN FORMAT=TREE
. EXPLAIN
[FORMAT=TRADITIONAL]
can also be used, but in this
case it is necessary, following execution of the
EXPLAIN
statement, to issue
SHOW WARNINGS
to view the
rewritten query.
This change is not expected to cause any difference in query results or performance. (WL #13456)
For RPM and Debian packages, client-side plugins were moved from the server package to the client package. Additionally, debug versions of client-side plugins were moved to the test package. (Bug #31123564, Bug #31336340)
MSI packages for Windows no longer include the legacy server data component. (Bug #31060177)
The bundled Protobuf library was upgraded from version 3.6.1 to version 3.11. (Bug #31000511, Bug #98852)
The libevent
library bundled with MySQL was
upgraded to version 2.1.11. In addition, for the
WITH_LIBEVENT
CMake option, the following two changes were
made:
yes
is no longer permitted as a synonym
for system
. Use system
instead.
If system
is specified but no system
libevent
is found, the bundled version is
no longer used in place of the missing system library, and
an error occurs instead.
(Bug #30926742)
The ICU (International Components for Unicode) library bundled with MySQL has been upgraded to version 65.1.
The MySQL Enterprise Edition authentication_ldap_sasl
plugin
that implements SASL LDAP authentication supports multiple
authentication methods, but depending on host system
configuration, they might not all be available. The new
Authentication_ldap_sasl_supported_methods
status variable provides discoverability for the supported
methods. Its value is a string consisting of supported method
names separated by spaces. Example: "SCRAM-SHA1
GSSAPI"
(WL #13876)
Incompatible Change:
Access to the
INFORMATION_SCHEMA.FILES
table now
requires the PROCESS
privilege.
This change affects users of the mysqldump
command, which accesses tablespace information in the
FILES
table, and thus now requires
the PROCESS
privilege as well.
Users who do not need to dump tablespace information can work
around this requirement by invoking mysqldump
with the --no-tablespaces
option.
(Bug #30350829)
For platforms on which OpenSSL libraries are bundled, the linked OpenSSL library for MySQL Server has been updated to version 1.1.1g. 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 #31296697)
Previously, LOCAL
data loading capability for
the LOAD DATA
statement could be
controlled on the client side only by enabling it for all files
accessible to the client, or by disabling it altogether. The new
MYSQL_OPT_LOAD_DATA_LOCAL_DIR
option for the
mysql_options()
C API function
enables clients to restrict LOCAL
data
loading to files located in a designated directory. See
Security Considerations for LOAD DATA LOCAL.
(WL #13168)
mysql-test-run.pl no longer accepts unique prefixes of command options. Complete option names must be given. (Bug #31390127)
MySQL tests were updated to use googletest 1.10.0. (Bug #31364750)
The innodb.innodb_mysql
test case was updated
to avoid nondeterminism of output row order. Thanks to Facebook
for the contribution.
(Bug #30810572, Bug #98377)
mysql-test-run.pl now supports an
--mtr-port-exclude
option for specifying the
range of ports to exclude when searching for available port
ranges to use. The MTR_PORT_EXCLUDE
environment variable may also be set to achieve the same effect.
Thanks to Facebook for the contribution.
(Bug #30809607)
In addition to aborting on receipt of CTRL+C (SIGINT), mysql-test-run.pl now also displays a list of test cases that failed up to that point. (Bug #30407014)
Where a dollar sign ($) was used to reference an entire document, X Plugin handled the reference differently depending on the context in which it was used. This has now been standardized. (Bug #31374713)
With certain settings for the global SQL mode, X Plugin's authentication process failed to accept a correct user password. The authentication process now operates independently from the global SQL mode's setting to ensure consistency. (Bug #31086109)
Important Change; Group Replication:
By default, a replication source server writes a checksum for
each event in the binary log, as specified by the system
variable binlog_checksum
, which
defaults to the setting CRC32
. Previously,
Group Replication did not support the presence of checksums in
the binary log, so
binlog_checksum
had to be set
to NONE
when configuring a server instance
that would become a group member. This requirement is now
removed, and the default can be used. The setting for
binlog_checksum
does not have
to be the same for all members of a group.
Note that Group Replication does not use checksums to verify
incoming events on the
group_replication_applier
channel, because
events are written to that relay log from multiple sources and
before they are actually written to the originating server's
binary log, which is when a checksum is generated. Checksums are
used to verify the integrity of events on the
group_replication_recovery
channel and on any
other replication channels on group members.
(WL #9038)
Performance:
Improved the implementation of the
UNHEX()
function by introducing a
lookup table for mapping a hexadecimal digit string to its
binary representation. This change speeds up execution of the
function by a factor of 8 or more in testing.
(Bug #31173103)
InnoDB:
Redo logging can now be enabled and disabled using
ALTER INSTANCE
{ENABLE|DISABLE} INNODB REDO_LOG
syntax. This
functionality is intended for loading data into a new MySQL
instance. Disabling redo logging helps speed up data loading by
avoiding redo log writes.
The new INNODB_REDO_LOG_ENABLE
privilege permits enabling and disabling redo logging.
The new
Innodb_redo_log_enabled
status
variable permits monitoring redo logging status.
See Disabling Redo Logging. (WL #13795)
InnoDB: Truncating an undo tablespace on a busy system could affect performance due to associated flushing operations that remove old undo tablespace pages from the buffer pool and flush the initial pages of the new undo tablespace to disk. To address this issue, the flushing operations were removed.
Old undo tablespace pages are now released passively as they become least recently used, or are removed at the next full checkpoint. The initial pages of the new undo tablespace are now redo logged instead of flushed to disk during the truncate operation, which also improves durability of the undo tablespace truncate operation.
To prevent potential issues caused by an excessive number of undo tablespace truncate operations, truncate operations on the same undo tablespace between checkpoints are now limited to 64. If the limit is exceeded, an undo tablespace can still be made inactive, but it is not truncated until after the next checkpoint.
INNODB_METRICS
counters associated
with defunct undo truncate flushing operations were removed.
Removed counters include:
undo_truncate_sweep_count
,
undo_truncate_sweep_usec
,
undo_truncate_flush_count
, and
undo_truncate_flush_usec
.
See Undo Tablespaces. (WL #11819)
InnoDB:
At startup, InnoDB
validates the paths of
known tablespace files against tablespace file paths stored in
the data dictionary in case tablespace files have been moved to
a different location. The new
innodb_validate_tablespace_paths
variable permits disabling tablespace path validation. This
feature is intended for environments where tablespaces files are
not moved. Disabling tablespace path validation improves startup
time on systems with a large number of tablespace files.
For more information, see Disabling Tablespace Path Validation. (WL #14008)
InnoDB:
Table and table partition data files created outside of the data
directory using the DATA DIRECTORY
clause are
now restricted to directories known to
InnoDB
. This change permits database
administrators to control where tablespace data files are
created and ensures that the data files can be found during
recovery.
General and file-per-table tablespaces data files
(.ibd
files) can no longer be created in
the undo tablespace directory
(innodb_undo_directory
) unless
that directly is known to InnoDB
.
Known directories are those defined by the
datadir
,
innodb_data_home_dir
, and
innodb_directories
variables.
Truncating an InnoDB
table that resides in a
file-per-table tablespace drops the existing tablespace and
creates a new one. As of MySQL 8.0.21, InnoDB
creates the new tablespace in the default location and writes a
warning to the error log if the tablespace was created with an
earlier version and the current tablespace directory is unknown.
To have TRUNCATE TABLE
create the
tablespace in its current location, add the directory to the
innodb_directories
setting
before running TRUNCATE TABLE
.
(WL #13065)
InnoDB:
To improve concurrency for operations that require access to
lock queues for table and row resources, the lock system mutex
(lock_sys->mutex
) was replaced by sharded
latches, and lock queues were grouped into table and page
lock queue shards, with each shard
protected by a dedicated mutex. Previously, the single lock
system mutex protected all lock queues, which was a point of
contention on high-concurrency systems. The new sharded
implementation permits more granular access to lock queues.
The lock system mutex (lock_sys->mutex
)
was replaced by the following sharded latches:
A global latch
(lock_sys->latches.global_latch
)
consisting of 64 read-write lock objects
(rw_lock_t
). Access to an individual lock
queue requires a shared global latch and a latch on the lock
queue shard. Operations that require access to all lock
queues take an exclusive global latch, which latches all
table and page lock queue shards.
Table shard latches
(lock_sys->latches.table_shards.mutexes
),
implemented as an array of 512 mutexes, with each mutex
dedicated to one of 512 table lock queue shards.
Page shard latches
(lock_sys->latches.page_shards.mutexes
),
implemented as an array of 512 mutexes, with each mutex
dedicated to one of 512 page lock queue shards.
The Performance Schema
wait/synch/mutex/innodb/lock_mutex
instrument
for monitoring the single lock system mutex was replaced by
instruments for monitoring the new global, table shard, and page
shard latches:
wait/synch/sxlock/innodb/lock_sys_global_rw_lock
wait/synch/mutex/innodb/lock_sys_table_mutex
wait/synch/mutex/innodb/lock_sys_page_mutex
(WL #10314)
Group Replication:
Group Replication group members can now advertise a list of IP
addresses that joining members can use to make connections to
them for state transfer during distributed recovery. Previously,
the existing member's standard SQL client connection was used
for this purpose as well as for client traffic. Advertising
distributed recovery endpoints instead gives you improved
control of distributed recovery traffic (comprising remote
cloning operations and state transfer from the binary log) in
your network infrastructure. The list of distributed recovery
endpoints for a member is specified using the new
group_replication_advertise_recovery_endpoints
system variable, and the same SSL requirements are applied that
would be in place if the SQL client connection was used for
distributed recovery.
(WL #13767)
Group Replication:
You can now specify user credentials for distributed recovery on
the START GROUP_REPLICATION
statement using the USER
,
PASSWORD
, and DEFAULT_AUTH
options. These credentials are used for distributed recovery on
the group_replication_recovery
channel. When
you specify user credentials on START
GROUP_REPLICATION
, the credentials are saved in memory
only, and are removed by a STOP
GROUP_REPLICATION
statement or server shutdown. These
credentials can replace user credentials set using a
CHANGE MASTER TO
statement, which
are stored in the replication metadata repositories, and can
therefore help to secure the Group Replication servers against
unauthorized access.
The new method of providing user credentials is not compatible
with starting Group Replication automatically on server start.
If user credentials have previously been set using a
CHANGE MASTER TO
statement,
credentials that you specify on START
GROUP_REPLICATION
take precedence over these. However,
the credentials from the replication metadata repositories are
used if START GROUP_REPLICATION
is specified without user credentials, which happens on
automatic starts if the
group_replication_start_on_boot
system variable is set to ON
(including after
a remote cloning operation for distributed recovery). To gain
the security benefits of specifying user credentials on
START GROUP_REPLICATION
, ensure
that
group_replication_start_on_boot
is set to OFF
(the default is
ON
), and use a CHANGE
MASTER TO
statement to clear any user credentials
previously set for the
group_replication_recovery
channel.
(WL #13768)
Group Replication:
The minimum setting for the maximum size for the XCom message
cache in Group Replication, specified by the
group_replication_message_cache_size
system variable, has been reduced from approximately 1 GB to
134217728 bytes, or approximately 128 MB. Note that this size
limit applies only to the data stored in the cache, and the
cache structures require an additional 50 MB of memory. The same
cache size limit should be set on all group members. The default
XCom message cache size of 1 GB, which was formerly also the
minimum setting, is unchanged.
The smaller message cache size is provided to enable deployment
on a host that has a restricted amount of available memory and
good network connectivity. Having a very low
group_replication_message_cache_size
setting is not recommended if the host is on an unstable
network, because a smaller message cache makes it harder for
group members to reconnect after a transient loss of
connectivity. If some messages that were exchanged during a
member's temporary absence have been deleted from the other
members' XCom message caches because their maximum size limit
was reached, the member cannot reconnect using the message
cache. It must leave the group and rejoin in order to retrieve
the transactions through distributed recovery, which is a slower
process than using the message cache, although the member still
can rejoin in this way without operator intervention.
Note that from MySQL 8.0.21, by default an expel timeout of 5
seconds is added before a member is expelled from the group
(specified by the
group_replication_member_expel_timeout
system variable). With this default setting the XCom message
cache therefore now needs to store the messages exchanged by the
group in a 10-second period (the expel timeout plus the initial
5-second detection period), rather than in a 5-second period as
previously (the initial 5-second detection period only).
(WL #13979)
Group Replication:
group_replication_member_expel_timeout
specifies the period of time in seconds that a Group Replication
group member waits after creating a suspicion, before expelling
from the group the member suspected of having failed. The
initial 5-second detection period before a suspicion is created
does not count as part of this time.
Previously, the waiting period specified by
group_replication_member_expel_timeout
defaulted to 0, meaning that a suspected member was liable for
expulsion immediately after the 5-second detection period ended.
Following user feedback, the waiting period now defaults to 5
seconds, giving a member that loses touch with the group 10
seconds in total to reconnect itself to the group. If the member
does reconnect in this time, it can recover missed messages from
the XCom message cache and return to ONLINE
state automatically, rather than being expelled from the group
and needing the auto-rejoin procedure or manual operator
intervention to rejoin.
If you previously tuned the size of the XCom message cache with
reference to the expected volume of messages in the previous
default time before a member was expelled (the 5-second
detection period only), increase your
group_replication_message_cache_size
setting to account for the new expel timeout, which doubles the
default time to 10 seconds. With the new default expel timeout
you might start to see warning messages from GCS on active group
members, stating that a message that is likely to be needed for
recovery by a member that is currently unreachable has been
removed from the message cache. This message shows that a member
has had a need to use the message cache to reconnect, and that
the cache size might not be sufficient to support the current
waiting period before a member is expelled.
(WL #13773)
Group Replication:
The Group Replication auto-rejoin feature is now activated by
default. The
group_replication_autorejoin_tries
system variable, which is available from MySQL 8.0.16, makes a
member that has been expelled or reached its unreachable
majority timeout try to rejoin the group automatically. This
system variable, which originally defaulted to 0 so auto-rejoin
was not activated, now defaults to 3, meaning that a member
makes three attempts to rejoin the group in the event of its
expulsion or unreachable majority timeout. Between each attempt
the member waits for 5 minutes. If the specified number of tries
is exhausted without the member rejoining or being stopped, the
member proceeds to the action specified by the
group_replication_exit_state_action
system variable.
The auto-rejoin feature minimizes the need for manual
intervention to bring a member back into the group, especially
where transient network issues are fairly common. During and
between auto-rejoin attempts, a member remains in super read
only mode and does not accept writes. However, reads can still
be made on the member, with an increasing likelihood of stale
reads over time. If you want to intervene to take the member
offline, the member can be stopped manually at any time by using
a STOP GROUP_REPLICATION
statement or shutting down the server. If you cannot tolerate
the possibility of stale reads for any period of time, set the
group_replication_autorejoin_tries
system variable to 0, in which case operator intervention is
required whenever a member is expelled from the group or reaches
its unreachable majority timeout.
(WL #13706)
Previously, the
--disabled-storage-engines
option
did not ignore spaces around storage engines listed in the
option value. Spaces around engine names are now ignored.
(Bug #31373361, Bug #99632)
The new HANDLE_FATAL_SIGNALS
CMake option enables configuring whether
Address Sanitizer and Undefined Behavior Sanitizer builds use
the sanitizer runtime library to handle fatal signals rather
than a MySQL-internal function. The option default is
ON
for non-sanitizer builds,
OFF
for sanitizer builds. If the option is
OFF
, the default action is used for SIGBUS,
SIGILL and SIGSEGV, rather than the internal function.
(Bug #31068443)
Using a column that is repeated twice or more in GROUP
BY
(through an alias), combined with
ROLLUP
, had behavior differing from MySQL
5.7. Example:
SELECT a, b AS a, COUNT(*) FROM t1 GROUP BY a, b WITH ROLLUP;
Behavior of such queries has been changed to better match MySQL 5.7. They should be avoided, however, because behavior may change again in the future or such queries may become illegal. (Bug #30921780, Bug #98663)
comp_err provides better error messages for certain input file issues. Thanks to Facebook for the contribution. (Bug #30810629, Bug #98390)
MySQL Server Docker containers now support server restart within
a client session (which happens, for example, when the
RESTART statement is executed by
a client or during the
configuration
of an InnoDB Cluster instance). To enable this
important feature, containers should be started with the
docker run option --restart
set to the value on-failure
. See
Starting a MySQL Server Instance for details.
(Bug #30750730)
EXPLAIN ANALYZE
now supports the
FORMAT
option. Currently,
TREE
is the only supported format.
(Bug #30315224)
ALTER INSTANCE ROTATE INNODB MASTER
KEY
is no longer permitted when
read_only
or
super_read_only
are enabled.
(Bug #30274240)
On storage engines that support atomic DDL, the
CREATE
TABLE ... SELECT
statement is now logged as one
transaction in the binary log when row-based replication is in
use. Previously, it was logged as two transactions, one to
create the table, and the other to insert data. With this
change,
CREATE
TABLE ... SELECT
statements are now safe for row-based
replication and permitted for use with GTID-based replication.
For more information, see Atomic Data Definition Statement Support.
(Bug #11756034, Bug #47899, WL #13355)
LOAD XML
now supports
CDATA
sections in the XML file to be
imported.
(Bug #98199, Bug #30753708)
X Plugin's mysqlx_bind_address
system variable now accepts multiple IP addresses like MySQL
Server's bind_address
system
variable does, enabling X Plugin to listen for TCP/IP
connections on multiple network sockets.
An important difference in behavior is that for MySQL Server,
any error in the list of addresses prevents the server from
starting, but X Plugin (which is not a mandatory plugin) does
not do this. With X Plugin, if one of the listed addresses
cannot be parsed or if X Plugin cannot bind to it, the address
is skipped, an error message is logged, and X Plugin attempts
to bind to each of the remaining addresses. X Plugin's
Mysqlx_address
status variable
displays only those addresses from the list for which the bind
succeeded. If none of the listed addresses results in a
successful bind, X Plugin logs an error message stating that
X Protocol cannot be used.
(WL #12715)
ENGINE_ATTRIBUTE
and
SECONDARY_ENGINE_ATTRIBUTE
options were added
to CREATE TABLE
,
ALTER TABLE
, and
CREATE INDEX
syntax. The
ENGINE_ATTRIBUTE
option was also added to
CREATE TABLESPACE
and
ALTER TABLESPACE
syntax. The new
options, which permit defining storage engine attributes for
tables, columns, indexes, and tablespaces, are reserved for
future use.
The following INFORMATION_SCHEMA
tables were added for querying storage engine attributes for
tables, columns, indexes, and tablespaces. Values are stored in
the data dictionary. The tables are reserved for future use.
(WL #13341)
The default logging level for MySQL Server omits informational log messages, which previously included some significant lifecycle events for Group Replication that were non-error situations, such as a group membership change. Messages about significant events for a replication group have now been reclassified as system messages, so they always appear in the server error log regardless of the server logging level. Operators can therefore review a complete history of the server's membership in a replication group. In addition, socket bind errors on the group communication layer have been reclassified from information to error messages. (WL #13769)
InnoDB:
A GROUP BY
operation on a
JSON
array column caused failures in an UBSan
build of MySQL due to incorrect type casting.
(Bug #31451475)
InnoDB:
Several InnoDB
error log messages were
defined without symbolic values.
(Bug #31401028)
InnoDB: The file segment for a single page write was not released after a data file write failure associated with a doublewrite flush and sync operation. (Bug #31370227)
InnoDB: Code that was accessed when undo tablespace truncation used the same space ID before and after a truncate operation was removed. That scenario no longer occurs. The truncated undo tablespace is replaced by a new undo tablespace datafile with a different space ID. (Bug #31354435)
InnoDB: The range of reserved space IDs for undo tablespaces was increased from 512 per undo tablespace to 400000. (Bug #31340834)
InnoDB:
An error that occurred while inserting a log into the
ddl_log
table was not returned making it
appear as though the operation was successful, and a transaction
was not registered while performing a tablespace encryption
operation.
(Bug #31236217)
InnoDB:
The lob::purge()
function did not free LOBs
correctly for an undo log record type
(TRX_UNDO_UPD_DEL_REC
) that is generated when
an insert operation modifies a delete-marked record.
(Bug #31222046, Bug #99339)
InnoDB: A shutdown error occurred following an attempt to rebuild a discarded partition. (Bug #31215415)
InnoDB:
The internal get_real_path()
function,
responsible for retrieving directory or a file path, was
modified to strip trailing separators before determining if a
path is a file or directory. Additionally, if a path does not
exist or cannot be identified as a file or subdirectory, the
function now assumes the path is a file if the basename has a
three letter suffix.
(Bug #31215160)
InnoDB: Tablespace-related error messages were revised. (Bug #31205520, Bug #31205441)
InnoDB:
To avoid potential compilation issues,
__attribute__((const))
and
__attribute__((pure))
attributes were removed
from internal InnoDB
functions.
(Bug #31153123)
InnoDB: The parallel read thread limit was not observed when spawning read threads for histogram sampling, causing an assertion failure. (Bug #31151218)
InnoDB: The transaction read view was not checked when sampling records for generation of histogram statistics. (Bug #31151077)
InnoDB: An I/O completion routine was not able acquire an LRU list mutex due to a latch held by another thread. (Bug #31128739)
InnoDB:
An attachable transaction thread requested an
InnoDB
ticket that was already reserved by
the main thread, causing a deadlock. Additionally, the server
failed to respond to KILL
statements in this deadlock scenario.
(Bug #31090777)
InnoDB:
The INNODB_METRICS
table
AVG_COUNT_RESET
value for a counter defined
as a module owner reported NULL. The
METRIC_AVG_VALUE_RESET
field was incorrectly
marked as NULL. Thanks to Fungo Wang for the contribution.
(Bug #31084706, Bug #98990)
InnoDB: At startup, following an unexpected stoppage during an undo tablespace truncate operation, some rollback segment header pages were found to be corrupted. Encryption of rollback segment header pages was initiated while the header pages were being written, resulting in some header pages not being encrypted, as expected. (Bug #31045160)
InnoDB:
Various aspects of the lock system (lock_sys
)
code were refactored, and issues with
lock_sys
lock_rec_block_validate()
and
lock_test_prdt_pacge_lock()
functions were
fixed. The lock_rec_block_validate()
function
called another function repeatedly, which could result in locks
not being validated under certain circumstances. The
implementation also had a potential quadratic time complexity.
The lock_test_prdt_page_lock()
function did
not iterate over all locks as intended.
(Bug #31001732)
InnoDB:
Use of memory-mapped files after exceeding the
temptable_max_ram
threshold
caused a performance degradation.
(Bug #30952983, Bug #98739)
InnoDB:
In debug mode, a DROP TABLE
operation on a table with an incorrectly defined
COMPRESSION
clause caused a failure.
InnoDB
did not return an error to the caller
for proper handling.
(Bug #30899683, Bug #98593)
InnoDB: Purge thread activity was excessive when the history list length approached zero, wasting CPU resource and causing mutex contention. (Bug #30875956)
InnoDB:
A regression introduced in MySQL 8.0.18 affected
INFORMATION_SCHEMA.INNODB_COLUMNS
query performance. Schema and table data dictionary objects were
fetched repeatedly to retrieve partition column information.
(Bug #30837086, Bug #98449)
References: This issue is a regression of: Bug #93033, Bug #28869903.
InnoDB:
An ALTER TABLE ...
IMPORT TABLESPACE
operation with a
.cfg
file failed with an “Incorrect
key file for table” error. The
row_import::m_flags
member was not
initialized.
(Bug #30830441)
InnoDB:
A DROP TABLE
operation performed
after discarding a partition did not remove the associated data
files, and DROP DATABASE
failed
with an error indicating that the database directory could not
be removed. Upgrade from MySQL 5.7 to MySQL 8.0 also failed if a
partitioned table with a discarded partition was present. The
DISCARD
attribute was applied to the table
object instead of the partition object in the data dictionary,
which made it appear that all partitions were discarded.
(Bug #30818917)
InnoDB: The server failed intermittently with an “ibuf cursor restoration fails” error. (Bug #30770380, Bug #91033)
InnoDB:
An ALTER TABLE
operation that
copied data from one table to another returned an “Out of
range value for column” error. The counter that tracks
the number of AUTO_INCREMENT
rows required
for a multi-row insert operation was not always set back to zero
after a bulk insert operation.
(Bug #30765952, Bug #98211)
InnoDB:
The internal TempTable records_in_range()
handler function contained a DBUG_ABORT()
call that caused assertion failures in debug builds, and empty
result sets in regular builds for some queries.
(Bug #30716037)
InnoDB:
The btr_cur_pessimistic_update()
function
failed to handle a cursor position change caused by a
lob::purge()
call.
(Bug #30712878)
InnoDB:
A type conversion failure during a DELETE
IGNORE
operation caused an assertion failure. A
JSON
value was not converted to the expected
value.
(Bug #30664660)
InnoDB: A purge operation encountered a null LOB reference, causing an assertion failure. (Bug #30658887)
InnoDB:
Chunk size was not calculated correctly when deallocating memory
from the TempTable
storage engine, causing a
regression in SELECT DISTINCT
query
performance.
(Bug #30562964)
InnoDB: A segmentation fault occurred in the TempTable storage engine while using the thread pool plugin. TempTable thread-local variables were not compatible with the use of different threads for statements issued by a single client connection. Use of thread local variables also lead to excessive memory consumption due to the memory used by thread-local variables remaining allocated for the life of the thread. To address these issues, thread-local variables were replaced by a caching mechanism. (Bug #30050452, Bug #31116036, Bug #99136)
InnoDB: A fatal “page still fixed or dirty” error occurred during shutdown. (Bug #29759555, Bug #95285)
References: This issue is a regression of: Bug #29207450.
Partitioning:
A query against a partitioned table, which used an
ORDER BY
, returned unordered results under
the following conditions:
The table had a composite index with a prefix on one of the columns.
The query's WHERE
clause contained an
equality condition on the prefixed column.
The column with the prefix was the leftmost column in the index.
The column used in the ORDER BY
was the
rightmost column in the index.
The index was used for handling the ORDER
BY
.
Our thanks to Quanan Han for the suggestion. (Bug #84070, Bug #25207522)
Replication:
If a group's consistency level (set by the
group_replication_consistency
system variable) was set to BEFORE
or
BEFORE_AND_AFTER
, it was possible for a
deadlock to occur in the event of a primary failover. The
primary failover is now registered differently to avoid this
situation.
(Bug #31175066, Bug #98643)
Replication:
When a replication source server shuts down and restarts, its
MEMORY
tables become empty. To
replicate this effect to replicas, the first time that the
source uses a given MEMORY
table
after startup, it notifies replicas that the table must be
emptied by writing a DELETE
statement for that table to the binary log. Previously, the
generated DELETE
statement was
written to the binary log statement cache for the current
session, which could result in it being logged together with
other statements under the same GTID, or logged without
BEGIN
and COMMIT
statements. Also, in some situations, the generated
DELETE
statement could consume
the GTID intended for the transaction that triggered it. The
generated DELETE
statement is now
logged with accompanying BEGIN
and
COMMIT
statements, and the resulting
transaction is flushed to the binary log immediately after it is
written to the statement cache, so that it always receives its
own GTID and is kept separate from other transactions.
(Bug #30527929, Bug #25681518, Bug #77729)
Replication:
Following a patch in MySQL 8.0.14, if a function call contained
operations on temporary tables, it could be written to the
binary log in statement format when
binlog_format = MIXED
was set.
This led to CREATE TEMPORARY
TABLE
statements being incorrectly written to the
binary log if they contained a function call. Following further
analysis, operations on temporary tables in stored functions and
triggers are now marked as unsafe for binary logging in
statement format, as they have a strong chance of causing issues
with replication. When binlog_format =
MIXED
is set, these operations are now logged in row
format.
(Bug #30395151, Bug #30320009)
Replication:
A fix made in MySQL 8.0.14 and MySQL 5.7.25 for a deadlock
scenario involving the system variables
binlog_transaction_dependency_tracking
and
binlog_transaction_dependency_history_size
had the side effect of leaving the writeset history used for
transaction dependency tracking unprotected from concurrent
update. The writeset history and tracking mode are now locked
correctly whenever they are accessed.
(Bug #29719364, Bug #95181)
References: See also: Bug #28511326, Bug #91941.
Replication:
If a CHANGE MASTER TO
statement
was issued with MASTER_USER
specified as
empty (MASTER_USER=''
), the statement
succeeded and cleared any previously specified user name in the
replication metadata repositories. However, if information was
subsequently read from the repositories, for example during an
automatic restart of a Group Replication channel, a default user
name could be substituted for the channel. This issue has now
been fixed, so from MySQL 8.0.21, it is a valid approach to set
an empty MASTER_USER
user name if you always
provide user credentials using the START
SLAVE
statement or START
GROUP_REPLICATION
statement that starts the
replication channel. This approach means that the replication
channel always needs operator intervention to restart, but the
user credentials are not recorded in the replication metadata
repositories.
The documentation for the CHANGE MASTER
TO
statement has also been corrected to clarify that
it is possible to specify MASTER_USER=''
, and
the resulting error occurs only if you attempt to start the
replication channel with the empty credentials.
(Bug #27357189)
Group Replication:
A global value that is set for the
group_replication_consistency
system variable, which controls all user connections, is applied
on Group Replication's internal connections to MySQL Server
modules using the SQL API, which are handled in a similar way to
user connections. This could sometimes lead to Group Replication
reporting the use of
group_replication_consistency
as an error, for example when checking the clone plugin status
during distributed recovery. Such internal connections using the
SQL API now use consistency level EVENTUAL
,
which matches the behavior before
group_replication_consistency
was available, and does not cause an error message.
(Bug #31303354, Bug #99345)
Group Replication: On Windows, Group Replication's use of the Windows API function SleepConditionVariableCS to wait for new write events caused noticeably high CPU usage by this function after Group Replication had been running for two days or more, which could be corrected by restarting the MySQL server instance, but then increased again over time as before. This was caused by the use of two clock functions to calculate the timeout after which the SleepConditionVariableCS function was called, which drifted relative to each other over time, making the timeout progressively shorter and the calls to the function more frequent. The issue has been corrected on Windows by using the current time from a single clock to calculate the timeout. (Bug #31117930)
Group Replication: If Group Replication was stopped while distributed recovery was in progress, memory issues could result from an attempt to access the record of the member that was selected as the donor. This record is now kept locally with the distributed recovery state. (Bug #31069563)
Group Replication:
When distributed recovery for Group Replication involves a
remote cloning operation, the flag set on the server to indicate
this remains set until the server instance is restarted.
Previously, if Group Replication was stopped and restarted on
the server, that flag caused Group Replication to purge the
relay log files for the
group_replication_applier
channel, as is
required on starting after a remote cloning operation to ensure
that there is no mismatch with the cloned data tables. If there
were any unapplied transactions in the purged relay log files,
the member could not subsequently be used to bootstrap a group,
although it could successfully join a group by retrieving the
transactions from another member. Group Replication now ignores
the flag on its second or subsequent starts, and only purges the
relay log files the first time it is started after a remote
cloning operation.
(Bug #31059680)
Group Replication: To avoid the possibility of data inconsistency, Group Replication blocks a new incarnation of the same server (with the same address but a new identifier) from joining the group while its old incarnation is still listed as a member. Previously, the Group Replication Group Communication System (GCS) treated the connection to the old incarnation of a server as active while it was attempting to send messages to the server, and only recognized that the connection was inactive when the socket returned an error, which might take a significant amount of time. During that period, the new incarnation of the server was unable to join the group because the existing members did not connect to it, as they were still waiting on the connection to the old incarnation. Now, GCS only treats a connection to a server as active while messages can be sent to it successfully. If the socket is no longer writeable, the server connection is treated as inactive and is proactively closed. The connection close triggers the group member to attempt reconnection to that server address, upon which a connection is established to the new incarnation of the server, enabling the new incarnation to join the group. (Bug #30770577)
Group Replication: Group Replication did not broadcast a notification when switching from single-primary mode to multi-primary mode. The change is now notified for use in routing. (Bug #30738896)
Group Replication:
Setting the
group_replication_force_members
system variable to force a specified membership for a group
could fail if another member had already requested the expulsion
of the member that was driving the
group_replication_force_members
operation. The operation to implement the configuration
specified by the
group_replication_force_members
system variable forced any pending group reconfigurations to
take place first. If one of those successfully expelled the
member where the system variable had been set, because the expel
timeout that was set on the member had expired, the operation
timed out and failed to complete. To avoid this situation, Group
Replication now proceeds directly to implementing the new
configuration specified by the
group_replication_force_members
system variable, and ignores any other pending group
reconfigurations.
(Bug #29820966)
Group Replication:
Group Replication tracking of connections to other group members
only took into account the incoming connections, not the
outgoing connections. This meant if the outgoing connection from
member A to member B was broken, for example by a firewall
configuration issue, but the incoming connection from member B
to member A was intact, member A would display member B's status
as ONLINE
, although member A's messages were
not reaching member B. Member B would display member A's status
as UNREACHABLE
. Now, if a group member starts
to receive pings from another group member to which it has an
active connection (in this case, if member A received pings from
member B), this is treated as an indicator of an issue with the
connection. If sufficient pings are received, the connection is
shut down by the recipient of the pings (in this case, member
A), so that the status of the connection is consistent for both
members.
(Bug #25660161, Bug #84796)
JSON:
When the expression and path passed to
JSON_TABLE()
yielded a JSON null,
the function raised an error instead of returning SQL
NULL
as required.
(Bug #31345503)
JSON:
In MySQL 5.7, and in MySQL 8.0 prior to 8.0.17, the server
attempted to convert JSON boolean values to their SQL
counterparts when testing them directly with IS
TRUE
, as shown here:
mysql>CREATE TABLE test (id INT, col JSON);
mysql>INSERT INTO test VALUES (1, '{"val":true}'), (2, '{"val":false}');
mysql>SELECT id, col, col->"$.val" FROM test WHERE col->"$.val" IS TRUE;
+------+---------------+--------------+ | id | col | col->"$.val" | +------+---------------+--------------+ | 1 | {"val": true} | true | +------+---------------+--------------+
As the result of work done in MySQL 8.0.17 to ensure that all
predicates in SQL conditions are complete (that is, a condition
of the form WHERE
is rewritten as
value
WHERE
), and that a value
<>
0NOT IN
or
NOT EXISTS
condition in a
WHERE
or ON
clause is
converted to an antijoin, evaluation of a JSON value in an SQL
boolean context performs an implicit comparison against JSON
integer 0. This means that the query shown previously returns
the following result in MySQL 8.0.17 and later:
mysql> SELECT id, col, col->"$.val" FROM test WHERE col->"$.val" IS TRUE;
+------+----------------+--------------+
| id | col | col->"$.val" |
+------+----------------+--------------+
| 1 | {"val": true} | true |
| 2 | {"val": false} | false |
+------+----------------+--------------+
In such cases, the server also now provides a warning:
Evaluating a JSON value in SQL boolean context does
an implicit comparison against JSON integer 0; if this is not
what you want, consider converting JSON to a SQL numeric type
with JSON_VALUE RETURNING. Thus, the query can now
be rewritten using JSON_VALUE()
as shown here:
mysql>SELECT id, col, col->"$.val" FROM test
->WHERE JSON_VALUE(col, "$.val" RETURNING UNSIGNED) IS TRUE;
+------+---------------+--------------+ | id | col | col->"$.val" | +------+---------------+--------------+ | 1 | {"val": true} | true | +------+---------------+--------------+
(Bug #31168181)
JSON:
A GROUP BY
query against a table having a
multi-valued index was not always handled correctly by the
server.
(Bug #31152942)
If log_error_services
was
persisted, in some cases it could take effect at the wrong time
during startup.
(Bug #31464539)
SHOW CREATE USER
after certain
manual grant table modifications could cause a server exit.
(Bug #31462844)
Some in-memory updates of partial revokes could produce incorrect privileges. (Bug #31430086)
If log_error_verbosity
was set
using SET
PERSIST
, it did not take effect early enough during
server startup to affect InnoDB
initialization.
(Bug #31410674)
The parser incorrectly raised an assertion before rejecting subqueries in generated column expressions. (Bug #31396191)
This release makes the following two micro-optimizations for degenerate hash joins (that is, those with no join conditions):
For a degenerate hash antijoin or semijoin, add
LIMIT 1
when building the hash table,
since having more rows than this cannot change the result.
For a degenerate hash antijoin with a nonempty hash table, avoid scanning the outer side.
Together, these changes handle a performance regression whereby
a rewrite to a hash antijoin caused a NOT
EXISTS
query which was not rewritten to be executed by
the optimizer and be replaced with “zero rows
found”. To handle the case in which a nested loop is used
instead, a non-correlated subquery inside NOT
EXISTS
is no longer transformed to an antijoin.
This fix also applies to subqueries using
.
(Bug #31376809)constant
NOT IN
(non_correlated_subquery
)
Configuring with
-DWITH_EDITLINE=system
resulted in
compilation failures for older library versions.
(Bug #31366715)
The upgrade of the bundled libedit
library in
the previous MySQL distribution caused a problem for builds
using that library such that
CTRL+C (SIGINT) in the
mysql client required a following Enter to
take effect in some circumstances.
(Bug #31360025)
Columns declared with both AUTO_INCREMENT
and
DEFAULT
value expressions are a nonpermitted
combination, but ALTER TABLE
failed to produce an error for SET DEFAULT
(
operations on
expr
)AUTO_INCREMENT
columns.
(Bug #31331454)
It was possible to set the
protocol_compression_algorithms
system variable to the empty string. This is no longer
permitted.
(Bug #31326231)
A lookup function used internally in the MySQL server returns integer -1 when the argument is ambiguous; this resulted in undefined behavior when this value was converted to an unsigned value prior to use as an argument in subsequent calculations. Now when the function returns -1, this is handled as an error and the value is not used further. (Bug #31326120)
Negation of a signed value in certain cases led to undefined behavior; to prevent this from occurring, the value to be negated is now treated as unsigned. (Bug #31325602)
The WEIGHT_STRING()
function did
not always return the correct result for an integer argument.
(Bug #31321257)
References: This issue is a regression of: Bug #30776132.
Assigning CONCAT('')
or
CONCAT_WS('')
to a variable set
the variable to NULL
, not the empty string.
(Bug #31320716, Bug #99485, Bug #31413167, Bug #99722)
Corrected problems where under some circumstances privilege restrictions could be ignored. (Bug #31306814, Bug #31315692)
Certain SELECT
statement
privileges to lock rows were not checked properly and could
block other users incorrectly.
(Bug #31293065)
When performing a filesort, an internal function could sometimes
return NULL
on failure, even if the subselect
being sorted was not nullable.
(Bug #31281602)
Statement rewriting for the binary log was inefficient on Windows. (Bug #31260698)
References: This issue is a regression of: Bug #30654405.
An inconsistency in representing anonymous users in memory could cause issues while performing privilege-granting operations. (Bug #31246179)
If the administrative connection interface was enabled, a race condition could lead to problems accepting Unix socket file connections on the main connection interface. (Bug #31241872)
When a role was granted with WITH ADMIN
OPTION
, the grantee was able to manage the role only
after activating it.
(Bug #31237368)
Invalid rows in the default_roles
or
role_edges
system tables could cause server
misbehavior.
(Bug #31217385)
Component deinitialization failure at runtime could lead to repeated messages written to the error log at shutdown. (Bug #31217037)
The prohibition on granting roles to anonymous users was incompletely enforced. (Bug #31215017)
A privilege-escalation issue was corrected. (Bug #31210226)
The keyring_hashicorp
keyring plugin did not
perform sufficient validity checking on the values of its
configuration parameters.
(Bug #31205363)
The keyring_hashicorp
keyring plugin did not
permit binary log encryption to be enabled (by setting the
binlog_encryption
system
variable).
(Bug #31204841)
The keyring_hashicorp
keyring plugin did not
permit an encryption password to be set by the
audit_log
plugin.
(Bug #31197670)
Some queries using
REGEXP_SUBSTR()
with an
ORDER BY
clause were not handled correctly by
the server.
(Bug #31184858)
Some instances where pointer arithmetic was applied to
nullptr
were corrected.
(Bug #31172750)
If the available file descriptors were exhausted,
mysql_real_connect()
caused the
client to exit.
(Bug #31151052)
Using the killall command to initiate a mysqld shutdown resulted in no message being logged to indicate the start of shutdown. This has been corrected. (Bug #31121907)
Calling
mysql_real_connect_nonblocking()
with an invalid host could cause the client to exit upon calling
mysql_close()
.
(Bug #31104389, Bug #99112)
For Debian packages, Python 2 dependencies that could cause installation failures were removed. (Bug #31099324)
A potential memory leak in lf_hash_insert()
was fixed.
(Bug #31090258, Bug #99078)
Within the LDAP SASL authentication plugins, multiple calls to
sasl_client_done()
could cause undefined
behavior in some cases.
(Bug #31088206)
With the thread pool plugin enabled, high concurrency conditions could cause loss of client context resulting in a server exit. (Bug #31085322)
For result sets processed using
mysql_use_result()
,
mysql_fetch_row_nonblocking()
did not increment the number of rows, so that after all the rows
were fetched, mysql_num_rows()
returned an incorrect number of rows.
(Bug #31082201, Bug #99073)
Removed an unneeded optimization for EXISTS()
that was never actually evaluated.
(Bug #31069510)
For a server started with the
--skip-grant-tables
option,
enabling the partial_revokes
system variable caused a server exit.
(Bug #31066069, Bug #31202963)
Queries that used a recursive common table expression with an outer reference could return incorrect results. (Bug #31066001, Bug #99025)
The parser could fail for multibyte character sets with a minimum character length greater than 1 byte. (Bug #31063981)
In some cases, the LEAST()
function could return NULL
for non-nullable
input.
(Bug #31054254)
References: This issue is a regression of: Bug #25123839.
mysql_real_connect_nonblocking()
blocked if the MYSQL_OPT_CONNECT_TIMEOUT
option was set.
(Bug #31049390, Bug #98980)
The last call to the
mysql_fetch_row_nonblocking()
C
API function to return the null row was setting an error when it
should not have.
(Bug #31048553, Bug #98947)
On Windows, the default connection type uses a named pipe. The nonblocking C API, which is intended for TCP/SSL connections, did not take that into account and caused the client to exit. It now produces an error message indicating the problem. (Bug #31047717)
X Plugin connections that failed to authenticate due to
nonexistence of the user modified the global
audit_log_filter_id
system
variable.
(Bug #31025461)
LOAD DATA
did not ignore hidden
generated columns when parsing input file rows.
(Bug #31024266, Bug #98925)
Pinbox exhaustion in the metadata locking subsystem could produce a misleading error message. (Bug #31019269, Bug #98911)
CREATE
TABLE ... SELECT
failed if it included a functional
index.
(Bug #31017765, Bug #98896)
For X Protocol connections, checking the global session mutex was improved to eliminate a minor performance degradation as the number of threads increased. (Bug #31000043)
In certain cases, executing a query containing multiple subqueries could lead to an unplanned shutdown of the server. (Bug #30975826)
SHOW CREATE TRIGGER
failed if
FLUSH TABLES WITH READ LOCK
was
in effect.
(Bug #30964944)
Excessive access checks were performed on certain of the data
dictionary tables that underlie
INFORMATION_SCHEMA
views, resulting in slow
SHOW COLUMNS
performance. These
checks were reduced to improve performance.
In addition, several SHOW
statements implemented as INFORMATION_SCHEMA
queries were found to benefit from enabling the
derived_merge
flag for the
optimizer_switch
system
variable. Such queries now internally enable that flag
temporarily for better performance, regardless of the flag
session value. Affected queries are:
SHOW SCHEMAS SHOW TABLES SHOW TABLE STATUS SHOW COLUMNS SHOW KEYS SHOW EVENTS SHOW TRIGGERS SHOW PROCEDURE STATUS SHOW FUNCTION STATUS SHOW CHARACTER SET SHOW COLLATION
(Bug #30962261, Bug #98750, Bug #30921214)
Two otherwise identical queries executed separately returned one
row when using a case-sensitive collation and two rows with a
case-insensitive collation. When the same two predicates were
combined in a single query using AND
, two
rows were returned when only one row should have been.
(Bug #30961924)
ALTER TABLE
on a
SET
column that had a display
width greater than 255 was not done in place, even if otherwise
possible.
(Bug #30943642, Bug #98523)
The server checked whether a number in yottabytes was too large
to print by comparing the value as a double to
ULLONG_MAX
, which cannot be represented as a
double. This caused the double value immediately above
ULLONG_MAX
yottabytes to be printed as
0Y
, the erroneous conversion being reported
by Clang 10.
(Bug #30927590)
Resource group SQL statements such as
CREATE RESOURCE GROUP
did not
work over connections that use X Protocol.
(Bug #30900411)
SHOW GRANTS
could display
function privileges as procedure privileges.
(Bug #30896461, Bug #98570)
The audit_log
plugin mishandled connection
events when multiple clients connected simultaneously.
(Bug #30893593)
The LOCK_ORDER
tool reported a syntax error
for empty dependency graphs. Empty graphs are now permitted.
The LOCK_ORDER
tool could exhibit unexpected
behavior due to mishandling thread list maintenance.
(Bug #30889192)
Upgrades from MySQL 5.7 did not grant the
REPLICATION_APPLIER
privilege to
root
.
(Bug #30783149)
The gen_range()
user-defined
function could mishandle its arguments, causing a server exit.
(Bug #30763294)
During UPDATE
processing,
conversion of an internal in-memory table to
InnoDB
could result in a key-length error.
(Bug #30674616)
Attempts to grant dynamic privileges (which are always global) at the procedure or function level did not produce an error. (Bug #30628160)
Table value constructors ignored the LIMIT
clause. The clause is now taken into account. For example:
VALUES ROW(1), ROW(2), ROW(3) LIMIT 2
outputs
1 and 2.
(Bug #30602659)
It is possible to define a column named *
(single asterisk character), but SELECT `*`
was treated identically to SELECT *
, making
it impossible to select only this column in a query; in other
words, the asterisk character was expanded to a list of all
table columns even when it was surrounded by backticks.
(Bug #30528450)
The FROM_DAYS()
function could
produce results that were out of range (with the year >
9999).
(Bug #30455845, Bug #97340)
For debug builds, altering the mysql.func
table to MyISAM
(not a recommended operation
in any case) caused a server exit. Now this operation is
prohibited.
(Bug #30248138, Bug #96692)
Queries on the INFORMATION_SCHEMA
KEY_COLUMN_USAGE
and
TABLE_CONSTRAINTS
views could be
slow due to UNION
use in their
definitions. These were rewritten to move the
UNION
into a
LATERAL
table to enable the optimizer to
better use indexes.
(Bug #30216864, Bug #30766181, Bug #98238)
In certain cases, a LIMIT
clause incorrectly
caused the optimizer to estimate that zero rows needed to be
read from a table.
(Bug #30204811)
References: This issue is a regression of: Bug #29487181.
An internal packet-length function returned values of the wrong integer type. (Bug #30139031)
Calculations by mysqldump for the length of
INSERT
statements did not take
into account the _binary
character set
introducer used for VARBINARY
strings.
(Bug #29998457, Bug #96053)
The messages printed to the error log during upgrade of partitioned tables defined with prefix keys did not provide sufficient details. Detailed warnings that indicate the schema, table, column, and prefix length are now printed. (Bug #29942014)
References: See also: Bug #31100205.
mysql_store_result()
could fail
to detect invalid data packets.
(Bug #29921423)
An assertion was raised if creating a child table in a foreign key relation caused an engine substitution. (Bug #29899151, Bug #95743)
mysqltest and
mysql-test-run.pl no longer support the
--sleep
command-line option.
mysqltest no longer supports the
real_sleep
command.
(Bug #29770237)
The server permitted connections for hosts with names longer than the maximum permitted length (255 characters). (Bug #29704941)
In a multiple-table UPDATE
that
updated the key of the first table, if a temporary table
strategy was used, duplicate entries could be written to the
temporary table, followed by occurrence of a Can't find
record
error.
(Bug #28716103)
The server sometimes mistakenly removed a subquery with a
GROUP BY
when optimizing a query, even in
some cases when this subquery was used by an outer select. This
could occur when the subquery also used an aggregate function.
(Bug #28240054)
Coercibility of the NAME_CONST()
function was assessed incorrectly.
(Bug #26319675)
When reading rows from a storage engine, errors other than “no more records” could be ignored, leading to problems later. (Bug #20162055)
When a multi-table update used a temporary table, this was not
shown in the output of
EXPLAIN
FORMAT=TREE
, even though such use could have an impact
on the performance of the UPDATE
statement for which this was done.
(Bug #17978975)
When performing a filesort for removing duplicates, such as when
executing SELECT DISTINCT
, it may be
necessary to perform another sort afterwards to satisfy an
ORDER BY
. In cases where such an
ORDER BY
had been pushed down into the first
table of a join, as opposed to the join as a whole, this final
sort was not actually performed.
(Bug #99687, Bug #31397840)
Refactoring work done in MySQL 8.0.20 caused single-row
buffering for GROUP BY
of non-nullable
columns not to function correctly, not taking into account that
such a column could be the inner table for an outer join, and
thus would have a NULL
flag that would need
to be copied. In a GROUP BY
without a
temporary table, this would cause the NULL
flag to come from the next output row instead of the previous
one, and the data returned to be inconsistent.
(Bug #99398, Bug #31252625)
References: This issue is a regression of: Bug #30460528.
A logical error in the constant folding code for the case in
which a constant of type DECIMAL
or FLOAT
was the left-hand
operand and an integer column value was the right-hand operand
yielded an incorrect result.
(Bug #99145, Bug #31110614)
A query whose predicate compared 0
with
-0
where at least one of these was a
floating-point value returned incorrect results.
(Bug #99122, Bug #31102789)
Reimplemented rollups without using slices. This fixes the following known issues:
A repeating column in a GROUP BY ... WITH
ROLLUP
yielded the wrong result; that is, a
GROUP BY
of the form GROUP BY a,
b, a WITH ROLLUP
erroneously produced
NULL
for some of the column names in the
result.
A GROUP BY ... WITH ROLLUP
that did not
require a temporary table to print the result also produced
an erroneous NULL
in place of at least
one of the expected column names in the output.
(Bug #98768, Bug #99141, Bug #26227613, Bug #29134467, Bug #30967158, Bug #30969045, Bug #31110494)
SELECT DISTINCT(
HEX(
WEIGHT_STRING(varchar_column) )
)
returned a truncated result.
(Bug #98592, Bug #30898753)
Problems with error handling in queries with
MAX()
,
MIN()
, or both, combined with a
GROUP BY
clause, meant that such a query
continued executing until it went through all possible
iterations even when an error should have caused it to terminate
immediately.
(Bug #98242, Bug #30769515)
After refactoring the type propagation code for
LEAST()
,
GREATEST()
, and other functions,
as well as UNION
, an adjustment
of the result type for data types like
ENUM
also replaced the calculated
integer data type with a type that could not accommodate both
signed and unsigned values.
(Bug #95148, Bug #29698617)
References: This issue is a regression of: Bug #83895, Bug #25123839.