MySQL 8.0 Release Notes
This release adds support for macOS 11 (Big Sur).
Granting the RELOAD
privilege
enables a user to perform a wide variety of operations. In some
cases, it may be desirable for a user to be able to perform only
some of these operations. To enable DBAs to avoid granting
RELOAD
and tailor user privileges
more closely to the operations permitted, these new privileges
of more limited scope are available:
FLUSH_OPTIMIZER_COSTS
:
Enables use of the FLUSH
OPTIMIZER_COSTS
statement.
FLUSH_STATUS
: Enables use of
the FLUSH STATUS
statement.
FLUSH_TABLES
: Enables use of
the FLUSH TABLES
statement.
FLUSH_USER_RESOURCES
: Enables
use of the FLUSH
USER_RESOURCES
statement.
The new privileges apply only at the global level. For more information, see Privileges Provided by MySQL, and FLUSH Statement.
The mysql_refresh()
C API
function performs operations similar to those of various
FLUSH
statements, but is
unaffected by this change. It still requires the
RELOAD
privilege regardless of
the operation for which it is invoked.
(WL #14303)
For some applications, it may be useful to define metadata on a per-query basis. Examples include the URL of the page that produced a query, or extra processing information to be passed with a query for use by a plugin such as an audit plugin or query rewrite plugin. MySQL now supports this capability without the use of workarounds such as specially formatted comments included in query strings:
On the client side, the
mysql_bind_param()
C API
function enables defining query attributes. These attributes
apply to the next SQL statement sent to the server for
execution. Additionally, the mysql and
mysqltest clients have a
query_attributes
command that enables
defining query attributes.
On the server side, a component service provides access to
query attributes. A component named
query_attributes
uses this service to
implement a
mysql_query_attribute_string()
loadable function that enables obtaining attribute values
within SQL statements. The
query_attributes
component is optional
but must be installed for the function to be available.
For more information, see Query Attributes.
Thanks to Facebook for suggesting the idea (and for contributing code, although it was not used). (Bug #27855905, Bug #28686334, WL #12542)
Thanks to Tzachi Zidenberg, who contributed a patch for compiling MySQL on aarch64 (ARM64). (Bug #31815236, Bug #100664)
Selection of the account that matches incoming TCP client connections could be affected by account creation order. To make the matching algorithm more deterministic, matching the host name part of accounts now checks accounts specified using host IP addresses, in a specific order, before attempting to match accounts specified using host names. Host name matching remains unchanged. See Access Control, Stage 1: Connection Verification. (WL #14074)
From MySQL 8.0.23, the statement CHANGE
MASTER TO
is deprecated. The alias
CHANGE REPLICATION SOURCE TO
should be used instead. The parameters for the statement also
have aliases that replace the term MASTER
with the term SOURCE
. For example,
MASTER_HOST
and
MASTER_PORT
can now be entered as
SOURCE_HOST
and
SOURCE_PORT
. The
START REPLICA |
SLAVE
statement’s parameters
MASTER_LOG_POS
and
MASTER_LOG_FILE
now have aliases
SOURCE_LOG_POS
and SOURCE_LOG_FILE. The
statements work in the same way as before, only the terminology
used for each statement has changed. A deprecation warning is
issued if the old versions are used.
A new status variable,
Com_change_replication_source
, has been added
as an alias for the Com_change_master
status
variable. Both the old and new version of the statement update
both the old and new version of the status variable.
The server rewrites all CHANGE MASTER
TO
statements as CHANGE
REPLICATION SOURCE TO
statements in the query log. The
same is done for the statements START SLAVE
,
STOP SLAVE
, SHOW SLAVE
STATUS
, SHOW SLAVE HOSTS
and
RESET SLAVE
. The event name for the
CHANGE MASTER TO
statement is set
to statement/sql/change_replication_source
in
the statement history table.
(Bug #32145023, WL #14189)
The gen_blacklist()
user-defined
function is deprecated. Use
gen_blocklist()
instead, which
performs the same term-replacement operation.
(WL #14176)
The use of the system variables
master_info_repository
and
relay_log_info_repository
is now deprecated,
and a warning message is issued if you attempt to set them or
read their values. The system variables will be removed in a
future MySQL version. These system variables were used to
specify whether the replica’s connection metadata repository
and applier metadata repository were written to an
InnoDB
table in the mysql system database, or
to a file in the data directory. The FILE
setting was already deprecated in a previous release, and tables
are the default for the replication metadata repositories in
MySQL 8.0.
(WL #13958)
Flushing the host cache can be done using any of these methods:
Execute a TRUNCATE TABLE
statement that truncates the Performance Schema
host_cache
table. This requires
the DROP
privilege for the
table.
Execute a FLUSH HOSTS
statement. This requires the
RELOAD
privilege.
Execute a mysqladmin flush-hosts command.
This requires the RELOAD
privilege.
Although those methods are equivalent in effect, granting the
RELOAD
privilege enables a number
of other operations in addition to host cache flushing, which is
undesirable from a security standpoint. Granting the
DROP
privilege for the
host_cache
table is preferable
because it has a more limited scope. Therefore, the
FLUSH HOSTS
statement is
deprecated and will be removed in a future MySQL version.
Instead, truncate the host_cache
table.
mysqladmin flush-hosts previously executed a
FLUSH HOSTS
statement. Now it
attempts to truncate the host_cache
table,
falling back to FLUSH HOSTS
only
if the truncate operation fails.
(WL #14329)
Administrators perform MySQL Enterprise Firewall management by registering profiles that specify sets of rules for permitted statements (allowlists). Previously, profiles could be associated only with individual accounts, so that, to apply a given allowlist to multiple account profiles, it was necessary to duplicate the rule set for each profile. For easier administration and greater flexibility, the firewall now provides group profile capabilities:
Named group profiles can be created. A group profile can include multiple accounts as members, and an account can be a member of multiple group profiles.
Each group profile has its own allowlist. The profile allowlist applies to all member accounts, eliminating the need to duplicate it across multiple account profiles.
For more information, see MySQL Enterprise Firewall. (WL #11740)
Switched the hash table used for hash joins from an unordered multimap to an unordered flat map implemented with a multimap adapter. This change yields the following improvements:
A faster hash table
Less memory usage due to less hash table overhead, less space used for alignment and key/value lengths, and better memory usage with many equal keys; this should also reduce the frequency at which it is necessary to spill to disk
Better memory control by approaching the allowed join buffer
size more closely rather than being effectively limited to
approximately 2/3 of
join_buffer_size
, and by
making it possible to free old memory when the hash table
grows
(Bug #99933, Bug #31516149, WL #13459)
Performance Schema macros that previously expanded to dynamic calls now expand to static calls when possible to reduce processing overhead. (Bug #32028160)
Performance overhead of timer code was reduced. This should be of most benefit to workloads with high concurrency using the Performance Schema. Thanks to Georgy Kirichenko for the contribution. (Bug #31960377, Bug #101018)
The MySQL Enterprise Edition SASL LDAP authentication plugin now supports
SCRAM-SHA-256
as an authentication method for
MySQL clients and servers. SCRAM-SHA-256
is
similar to SCRAM-SHA-1
but is more secure.
Use of SCRAM-SHA-256
requires an OpenLDAP
server built using Cyrus SASL 2.1.27 or higher. See
LDAP Authentication Methods.
(WL #14180)
For platforms on which OpenSSL libraries are bundled, 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)
The new ST_HausdorffDistance()
and ST_FrechetDistance()
functions return the discrete Fréchet and Hausdorff distances
between two geometries, reflecting how similar the geometries
are. See
Spatial Relation Functions That Use Object Shapes.
(WL #14128, WL #14129)
MySQL now supports invisible columns, which normally are hidden to queries, but can be accessed if explicitly referenced. See Invisible Columns. (WL #10905)
For X Protocol connections using the MYSQL41
authentication method, if the nonce sent by the server was
shorter than 20 bytes, the connection logic did not handle it
correctly.
(Bug #32036194)
If a query that was building up a resultset was killed, X Plugin interpreted this as meaning the server session had been killed, and dropped the connection. The status of a query is now checked separately from the status of the server session. (Bug #31954296)
A deadlock could occur if an X Protocol session attempted to display X Plugin status variables or settings at the same time as another X Protocol session was being released and reset. The situation is now handled appropriately. (Bug #31931873)
If an X Protocol client with a connection to a server remains idle (not sending to the server) for longer than the relevant X Plugin timeout setting (read, write, or wait timeout), X Plugin closes the connection. In the case of a read timeout, the plugin returns a warning notice with the error code ER_IO_READ_ERROR to the client application.
From MySQL 8.0.23, X Plugin now also sends a warning notice if a connection is actively closed due to a server shutdown, or by the connection being killed from another client session. In the case of a server shutdown, the warning notice is sent to all authenticated X Protocol clients with open connections, with the ER_SERVER_SHUTDOWN error code. In the case of a killed connection, the warning notice is sent to the relevant client with the ER_SESSION_WAS_KILLED error code, unless the connection was killed during SQL execution, in which case a fatal error is returned with the ER_QUERY_INTERRUPTED error code.
Client applications can use the warning notices to display to users, or to analyze the reason for disconnection and decide whether to attempt reconnection to the same server, or to a different server. (WL #14166)
For classic MySQL protocol, if an SQL query is using metadata locking or the sleep function, the connection to the server is checked periodically to verify that it is still alive. If not, the query can be stopped so that it does not continue to consume resources. Previously, X Protocol did not carry out these checks, and assumed that the connection was still alive. The check has now been added for X Protocol. (WL #14167)
InnoDB: Performance was improved for the following operations:
Dropping a large tablespace on a MySQL instance with a large buffer pool (>32GBs).
Dropping a tablespace with a significant number of pages referenced from the adaptive hash index.
Truncating temporary tablespaces.
The pages of dropped or truncated tablespaces and associated AHI entries are now removed from the buffer pool passively as pages are encountered during normal operations. Previously, dropping or truncating tablespaces initiated a full list scan to remove pages from the buffer pool immediately, which negatively impacted performance. (Bug #31008942, Bug #98869, WL #14100)
InnoDB:
The new AUTOEXTEND_SIZE
option defines the
amount by which InnoDB
extends the size of a
tablespace when it becomes full, making it possible to extend
tablespace size in larger increments. Allocating space in larger
increments helps to avoid fragmentation and facilitates
ingestion of large amounts of data. The
AUTOEXTEND_SIZE
option is supported with the
CREATE TABLE
,
ALTER TABLE
,
CREATE TABLESPACE
, and
ALTER TABLESPACE
statements. For
more information, see
Tablespace AUTOEXTEND_SIZE Configuration.
An AUTOEXTEND_SIZE
size column was added to
the
INFORMATION_SCHEMA.INNODB_TABLESPACES
table.
(WL #13895)
InnoDB:
InnoDB
now supports encryption of doublewrite
file pages belonging to encrypted tablespaces. The pages are
encrypted using the encryption key of the associated tablespace.
For more information, see
InnoDB Data-at-Rest Encryption.
(WL #13775)
InnoDB:
InnoDB
atomics code was revised to use C++
std::atomic
.
(WL #14235)
Group Replication:
The MySQL Server asynchronous connection failover mechanism now
supports Group Replication topologies, by automatically
monitoring changes to group membership and distinguishing
between primary and secondary servers. When you add a group
member to the source list and define it as part of a managed
group, the asynchronous connection failover mechanism updates
the source list to keep it in line with membership changes,
adding and removing group members automatically as they join or
leave. The new
asynchronous_connection_failover_add_managed()
and
asynchronous_connection_failover_delete_managed()
functions are used to add and remove managed sources.
The connection is failed over to another group member in the following situations:
The currently connected source goes offline or leaves the group.
The currently connected source is no longer in the majority.
The currently connected source does not have the highest weighted priority in the group.
For a managed group, a source’s weight is assigned depending on whether it is a primary or a secondary server. So assuming that you set up the managed group to give a higher weight to a primary and a lower weight to a secondary, when the primary changes, the higher weight is assigned to the new primary, so the replica changes over the connection to it. This also applies to single (non- managed) servers, so the connection for a single server is also now failed over if another source server is available that has a higher weighted priority. (WL #14019)
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)
Some overhead for disabled Performance Schema and
LOCK_ORDER
tool instrumentation was
identified and eliminated.
(Bug #32105698)
For BLOB
and TEXT
columns
that have a default value expression, the
INFORMATION_SCHEMA.COLUMNS
table
and SHOW COLUMNS
statement now
display the expression.
(Bug #31856459)
For a multithreaded replica (where
slave_parallel_workers
is
greater than 0), setting
slave_preserve_commit_order=1
ensures that transactions are executed and committed on the
replica in the same order as they appear in the replica's relay
log. Each executing worker thread waits until all previous
transactions are committed before committing. If a worker thread
fails to execute a transaction because a possible deadlock was
detected, or because the transaction's execution time exceeded a
relevant wait timeout, it automatically retries the number of
times specified by
slave_transaction_retries
before stopping with an error. Transactions with a non-temporary
error are not retried.
The replication applier on a multithreaded replica has always
handled data access deadlocks that were identified by the
storage engines involved. However, some other types of lock were
not detected by the replication applier, such as locks involving
access control lists (ACLs) or metadata locking (for example,
FLUSH TABLES WITH READ LOCK
statements). This
could lead to three-actor deadlocks with the commit order
locking, which could not be resolved by the replication applier,
and caused replication to hang indefinitely. From MySQL 8.0.23,
deadlock handling on multithreaded replicas that preserve the
commit order has been enhanced to mitigate these types of
deadlocks. The deadlocks are not specifically resolved by the
replication applier, but the applier is aware of them and
initiates automatic retries for the transaction, rather than
hanging. If the retries are exhausted, replication stops in a
controlled manner so that the deadlock can be resolved manually.
(Bug #107574, Bug #34291887, WL #13574)
CRC calculations for binlog checksums are faster on ARM platforms. Thanks to Krunal Bauskar for the contribution. (Bug #99118, Bug #31101633, Bug #32163391)
Replication channels can now be set to assign a GTID to
replicated transactions that do not already have one, using the
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
option
of the CHANGE REPLICATION SOURCE TO
statement. This feature enables replication from a source that
does not use GTID-based replication, to a replica that does. For
a multi-source replica, you can have a mix of channels that use
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
, and
channels that do not. The GTID can include the replica’s own
server UUID or a server UUID that you assign to identify
transactions from different sources.
Note that a replica set up with
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
on any
channel cannot be promoted to replace the replication source
server in the event that a failover is required, and a backup
taken from the replica cannot be used to restore the replication
source server. The same restriction applies to replacing or
restoring other replicas that use
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
on any
channel. The GTID set (gtid_executed) from a replica set up with
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
is
nonstandard and should not be transferred to another server, or
compared with another server's gtid_executed
set.
(WL #12819)
The new temptable_max_mmap
variable defines the maximum amount of memory the TempTable
storage engine is permitted to allocate from memory-mapped
temporary files before it starts storing data to
InnoDB
internal temporary tables on disk. A
setting of 0 disables allocation of memory from memory-mapped
temporary files. For more information, see
Internal Temporary Table Use in MySQL.
(WL #14125)
InnoDB:
A CREATE TABLE
operation that
specified the COMPRESSION
option was
permitted with a warning on a system that does not support hole
punching. The operation now fails with an error instead.
(Bug #32174200)
InnoDB: A MySQL DB system restart following an upgrade that was initiated while a data load operation was in progress raised an assertion failure. (Bug #32173596)
InnoDB: An error message regarding the number of truncate operations on the same undo tablespace between checkpoints incorrectly indicated a limit of 64. The limit was raised from 64 to 50,000 in MySQL 8.0.22. (Bug #32151601, Bug #101601)
InnoDB:
rw_lock_t
and buf_block_t
source code structures were reduced in size.
(Bug #32084500)
InnoDB:
An InnoDB
transaction became
inconsistent after creating a table using a storage engine other
than InnoDB
from a query expression that
operated on InnoDB
tables.
(Bug #32079103)
InnoDB:
In some circumstances, such as when an existing gap lock
inherits a lock from a deleted record, the number of locks that
appear in the
INFORMATION_SCHEMA.INNODB_TRX
table
could diverge from the actual number of record locks.
Thanks to Fungo Wang from Alibaba for the patch. (Bug #32068538, Bug #101305)
InnoDB:
An off-by-one error in Fil_system
sharding
code was corrected, and the maximum number of shards
(MAX_SHARDS
) was changed to 69.
(Bug #32052821, Bug #101260)
InnoDB: The TempTable storage engine memory allocator allocated extra blocks of memory unnecessarily. (Bug #32018553)
InnoDB:
A SELECT COUNT(*)
operation on a table
containing uncommitted data performed poorly due to unnecessary
I/O.
Thanks to Brian Yue for the contribution. (Bug #31997733, Bug #100966)
InnoDB: A race condition when shutting down the log writer raised an assertion failure. (Bug #31997362)
InnoDB:
Page cleaner threads were not utilized optimally in sync-flush
mode, which could cause page flush operations to slow down or
stall in some cases. Sync-flush mode occurs when
InnoDB
is close to running out of free space
in the redo log, causing the page cleaner coordinator to
initiate aggressive page flushing.
(Bug #31994031)
InnoDB:
A high frequency of updates while undo log truncation was
enabled caused purge to lag. The lag was due to the
innodb_purge_rseg_truncate_frequency
setting being changed temporarily from 128 to 1 when an undo
tablespace was selected for truncation. The code that modified
the setting has been removed.
(Bug #31991688)
InnoDB:
Automated truncation of undo tablespaces caused a performance
regression. To address this issue, undo tablespace files are now
initialized at 16MB and extended by a minimum of 16MB. To handle
aggressive growth, the file extension size is doubled if the
previous file extension happened less than 0.1 seconds earlier.
Doubling of the extension size can occur multiple times to a
maximum of 256MB. If the previous file extension occurred more
than 0.1 seconds earlier, the extension size is reduced by half,
which can also occur multiple times, to a minimum of 16MB.
Previously, the initial size of an undo tablespace depended on
the InnoDB
page size, and undo tablespaces
were extended four extents at a time.
If the AUTOEXTEND_SIZE
option is defined for
an undo tablespace, the undo tablespace is extended by the
greater of the AUTOEXTEND_SIZE
setting and
the extension size determined by the logic described above.
When an undo tablespace is truncated, it is normally recreated
at 16MB in size, but if the current file extension size is
larger than 16MB, and the previous file extension happened
within the last second, the new undo tablespace is created at a
quarter of the size defined by the
innodb_max_undo_log_size
variable.
Stale undo tablespace pages are no longer removed at the next
checkpoint. Instead, the pages are removed in the background by
the InnoDB
master thread.
(Bug #31965404, Bug #32020900, Bug #101194)
InnoDB:
A posix_fallocate()
failure while
preallocating space for a temporary tablespace raised an error
and caused an initialization failure. A warning is now issued
instead, and InnoDB
falls back to the
non-posix_fallocate()
method for
preallocating space.
(Bug #31965379)
InnoDB:
An invalid pointer caused a shutdown failure on a MySQL Server
compiled with the
DISABLE_PSI_MEMORY
source
configuration option enabled.
(Bug #31963333)
InnoDB: A long SX lock held by an internal function that calculates new statistics for a given index caused a failure. (Bug #31889883)
InnoDB:
The
INFORMATION_SCHEMA.INNODB_TABLESPACES
table reported a FILE_SIZE
of 0 for some
tables and schemas. When the associated tablespace was not in
the memory cache, the tablespace name was used to determine the
tablespace file name, which was not always a reliable method.
The tablespace ID is now used instead. Using the tablespace name
remains as a fallback method.
(Bug #31841617)
InnoDB:
After dropping a FULLTEXT
index and renaming
the table to move it to a new schema, the
FULLTEXT
auxiliary tables were not renamed
accordingly and remained in the old schema directory.
(Bug #31773368, Bug #100570)
InnoDB: After upgrading to MySQL 8.0, a failure occurred when attempting to perform a DML operation on a table that was previously defined with a full-text search index. (Bug #31749490)
InnoDB:
Importing a tablespace with a page-compressed table did not
report a schema mismatch error for source and destination tables
defined with a different COMPRESSION
setting.
The COMPRESSION
setting of the exported table
is now saved to the .cfg
metadata file
during the
FLUSH
TABLES ... FOR EXPORT
operation, and that information
is checked on import to ensure that both tables are defined with
the same COMPRESSION
setting.
(Bug #31744694)
InnoDB: Dummy keys used to check if the MySQL Keyring plugin is functioning were left behind in an inactive state, and the number of inactive dummy keys increased over time. The actual master key is now used instead, if present. If no master key is available, a dummy master key is generated. (Bug #31737924)
InnoDB:
Querying the
INFORMATION_SCHEMA.FILES
table
after moving the InnoDB
system tablespace
outside of the data directory raised a warning indicating that
the innodb_system
filename is unknown.
(Bug #31603047)
InnoDB:
In a replication scenario involving a replica with binary
logging or log_slave_updates
disabled, the server failed to start due to an excessive number
of gaps in the mysql.gtid_executed
table. The
gaps occurred for workloads that included both
InnoDB
and non-InnoDB
transactions. GTIDs for InnoDB
transactions
are flushed to the mysql.gtid_executed
table
by the GTID persister thread, which runs periodically, while
GTIDs for non-InnoDB
transactions are written
to the to the mysql.gtid_executed
table
directly by replica server threads. The GTID persister thread
fell behind as it cycled through merging entries and compressing
the mysql.gtid_executed
table. As a result,
the size of the GTID flush list for InnoDB
transactions grew over time along with the number of gaps in the
mysql.gtid_executed
table, eventually causing
a server failure and subsequent startup failures. To address
this issue, the GTID persister thread now writes GTIDs for both
InnoDB
and non-InnoDB
transactions, and foreground commits are forced to wait if the
GTID persister thread falls behind. Also, the
gtid_executed_compression_period
default setting was changed from 1000 to 0 to disabled explicit
compression of the mysql.gtid_executed
table
by default.
Thanks to Venkatesh Prasad for the contribution. (Bug #31599938, Bug #100118)
InnoDB:
Persisting GTID values for XA transactions affected XA
transaction performance. Two GTID values are generated for XA
transactions, one for the prepare stage and another for the
commit stage. The first GTID value is written to the undo log
and later overwritten by the second GTID value. Writing of the
second GTID value could only occur after flushing the first GTID
value to the gtid_executed
table. Space is
now reserved in the undo log for both XA transaction GTID
values.
(Bug #31467953, Bug #99638)
InnoDB:
InnoDB
source files were updated to address
warnings produced when building Doxygen source code
documentation.
(Bug #31354760)
InnoDB: The full-text search synchronization thread attempted to read a previously-freed word from the index cache. (Bug #31310404)
InnoDB:
A 20µs sleep in the buf_wait_for_read()
function introduced with parallel read functionality in MySQL
8.0.17 took 1ms on Windows, causing an unexpected timeout when
running certain tests. Also, AIO threads were found to have
uneven amounts of waiting operating system IO requests.
(Bug #31095274)
References: This issue is a regression of: Bug #31123564.
InnoDB:
Cleanup in certain replicated XA transactions failed to reattach
transaction object (trx_t
), which raised an
assertion failure.
(Bug #31006095)
InnoDB:
The tablespace encryption type setting was not properly updated
due to a failure during the resumption of an
ALTER
TABLESPACE ENCRYPTION
operation following a server
failure.
(Bug #30883833, Bug #98537)
InnoDB:
An interrupted tablespace encryption operation did not update
the encrypt_type
table option information in
the data dictionary when the operation resumed processing after
the server was restarted.
(Bug #30883833, Bug #98537, Bug #30888919, Bug #98564)
InnoDB:
Internal counter variables associated with thread sleep delay
and threads entering an leaving InnoDB
were
revised to use C++ std::atomic
. Built-in
atomic operations were removed. Thanks to Yibo Cai from ARM for
the contribution.
(Bug #30567060, Bug #97704)
InnoDB:
A relaxed memory order was implemented for dictionary memory
variable fetch-add
(dict_temp_file_num.fetch_add
) and store
(dict_temp_file_num.store
) operations.
Thanks to Yibo Cai for the contribution. (Bug #30567054, Bug #97703)
InnoDB: A background thread that resumed a tablespace encryption operation after the server started failed to take an metadata lock on the tablespace, which permitted concurrent DDL operations and led to a race condition with the startup thread. The startup thread now waits until the tablespace metadata lock is taken. (Bug #28531637)
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)
Partitioning:
ALTER
TABLE t1 EXCHANGE PARTITION ... WITH TABLE t2
led to
an assert when t1
was not a partitioned
table.
(Bug #100971, Bug #31941543)
References: This issue is a regression of: Bug #29706669.
Replication:
The network_namespace
parameter for the
asynchronous_connection_failover_add_source()
and
asynchronous_connection_failover_delete_source()
function is no longer used. These functions add and remove
replication source servers from the source list for a
replication channel for the asynchronous connection failover
mechanism. The network namespace for a replication channel is
managed using the CHANGE REPLICATION SOURCE
statement, and has special requirements for Group Replication
source servers, so it should no longer be specified in the
functions.
(Bug #32078189)
Replication:
When mysqlbinlog’s
--print-table-metadata
option was used,
mysqlbinlog used a different method for
assessing numeric fields to the method used by the server when
writing to the binary log, resulting in incorrect metadata
output relating to these fields. mysqlbinlog
now uses the same method as the server.
(Bug #31956206)
Replication: When using network namespaces in a replication channel and the initial connection from the replica to the master was interrupted, subsequent connection attempts failed to use the correct namespace information. (Bug #31954087)
Replication: If a group member was expelled and made an auto-rejoin attempt at a point when some tables on the instance were locked (for example while a backup was running), the attempt failed and no further attempts were made. This scenario is now handled correctly. (Bug #31460690)
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)
Group Replication:
If the Group Replication applier channel
(group_replication_applier
) was holding a
lock on a table, for example because of a backup in progress,
and the member was expelled from the group and tried to rejoin
automatically, the auto-rejoin attempt was unsuccessful and did
not retry. Now, Group Replication checks during startup and
rejoin attempts whether the
group_replication_applier
channel is already
running. If that is the case at startup, an error message is
returned. If that is the case during an auto-rejoin attempt,
that attempt fails, but further attempts are made as specified
by the
group_replication_autorejoin_tries
system variable.
(Bug #31648211)
Microsoft Windows: On Windows, running the MySQL server as a service caused shared-memory connections to fail. (Bug #32009251)
JSON:
JSON_ARRAYAGG()
did not always
perform proper error handling.
(Bug #31856260, Bug #32012559, Bug #32181438)
JSON:
When updating a JSON
value using
JSON_SET()
,
JSON_REPLACE()
, or
JSON_REMOVE()
, the target column
can sometimes be updated in-place. This happened only when the
target table of the update operation was a base table, but when
the target table was an updatable view, the update was always
performed by writing the full JSON
value.
Now in such cases, an in-place update (that is, a partial update) is also performed when the target table is an updatable view. (Bug #25840784)
JSON:
Work done in MySQL 8.0.22 to cause prepared statements to be
prepared only once introduced a regression in the handling of
dynamic parameters to JSON functions. All
JSON
arguments were classified as
data type MYSQL_TYPE_JSON
, which overlooked
the fact that JSON functions take two kinds of JSON
parameters—JSON values and JSON documents—and this
distinction cannot be made with the data type only. For Bug
#31667405, this problem was solved for comparison operators and
the IN()
operator by making it
possible to tag a JSON argument as being a scalar value, while
letting arguments to other JSON functions be treated as JSON
documents.
The present fix restores for a number of JSON functions their treatment of certain arguments as JSON values, as listed here:
The first argument to MEMBER OF()
The third, fifth, seventh, and subsequent odd-numbered arguments
to the functions JSON_INSERT()
,
JSON_REPLACE()
,
JSON_SET()
,
JSON_ARRAY_APPEND()
, and
JSON_ARRAY_INSERT()
.
(Bug #101284, Bug #32063203)
References: See also: Bug #31667405.
JSON:
When mysqld was run with
--debug
, attempting to execute a
query that made use of a multi-valued index raised an error.
(Bug #99833, Bug #31474182)
Use of the thread_pool
plugin could result in
Address Sanitizer warnings.
(Bug #32213294)
While pushing a condition down to a materialized derived table,
and a condition is partially pushed down, the optimizer may, in
some cases in which a query transformation has added new
conditions to the WHERE
condition, call the
internal fix_fields()
function for the
condition that remains in the outer query block. A successful
return from this function call was misinterpreted as an error,
leading to the silent failure of the original statement.
(Bug #32150145)
Multiple calls to a stored procedure containing an
ALTER TABLE
statement that
included an ORDER BY
clause could cause a
server exit.
(Bug #32147402)
Prepared statements involving stored programs could cause heap-use-after-free memory problems. (Bug #32131022, Bug #32045681, Bug #32051928)
Queries on INFORMATION_SCHEMA
tables that involved materialized derived tables could fail.
(Bug #32127562, Bug #101504)
A potential buffer overflow was fixed. Thanks to Sifang Zhao for pointing out the issue, and for suggesting a fix (although it was not used). (Bug #32113015, Bug #101448)
Conversion of FLOAT
values to values of type
INT
could generate Undefined Behavior
Sanitizer warnings.
(Bug #32099994, Bug #32100033)
In multiple-row queries, the
LOAD_FILE()
function evaluated to
the same value for every row.
(Bug #32096341, Bug #101401)
Generic Linux tar file distributions had too-restrictive file permissions after unpacking, requiring a manual chmod to correct. (Bug #32080900)
For debug builds, prepared
SET
statements containing subqueries in stored procedures could
raise an assertion.
(Bug #32078387)
References: See also: Bug #32100210.
For prepared statements, illegal mix of collations errors could occur for legal collation mixes. (Bug #32077842, Bug #101346, Bug #32145078, Bug #101575)
The functions REGEXP_LIKE()
,
REGEXP_INSTR()
, and
REGEXP_REPLACE()
raise errors for malformed
regular expression patterns, but could also return
NULL
for such cases, causing subsequent debug
asserts. Now we ensure that these functions do not return
NULL
except in certain specified cases.
The function REGEXP_SUBSTR()
can always
return NULL
, so no such check is needed, and
for this function we make sure that one is not performed.
(Bug #32053093)
Testing an aggregate function for IS NULL
or
IS NOT NULL
in a HAVING
condition using WITH ROLLUP
led to wrong
results.
(Bug #32049313)
When a new aggregate function was added to the current query block because an inner query block had an aggregate function requiring evaluation in the current one, the server did not add rollup wrappers to it as needed. (Bug #32034914)
For debug builds, certain CREATE
TABLE
statements with CHECK
constraints could raise an assertion.
(Bug #32018406, Bug #101180)
Incorrect BLOB field values were passed from
InnoDB
during a secondary engine load
operation.
(Bug #32014483)
The LOCK_ORDER tool did not correctly represent
InnoDB
share exclusive locks.
(Bug #31994052)
The server did not handle properly an error raised when trying to use an aggregation function with an invalid column type as part of a hash join. (Bug #31989333)
The length of the WORD
column of the
INFORMATION_SCHEMA.KEYWORDS
table
could change depending on table contents.
(Bug #31982157)
The Performance Schema host_cache
table was
empty and did not expose the contents of the host cache if the
Performance Schema was disabled. The table now shows cache
contents regardless of whether the Performance Schema is
enabled.
(Bug #31978763)
A HANDLER
READ
statement sometimes hit an assert when a previous
statement did not restore the original value of
THD::mark_used_columns
after use.
(Bug #31977414)
Importing a compressed table could cause an unexpected server exit if the table contained values that were very large when uncompressed. (Bug #31943021)
Removed a memory leak that could occur when a subquery using a
hash join and LIMIT
was executed repeatedly.
(Bug #31940549)
A compilation failure on Ubuntu was corrected. (Bug #31930934, Bug #100938)
Memory used for storing partial-revokes information could grow excessively for sessions that executed a large number of statements. (Bug #31919448)
The server did not handle all cases of the
WHERE_CONDITION
optimization correctly.
(Bug #31905199)
FLUSH TABLES WITH READ LOCK
could
block other sessions from executing SHOW
TABLE STATUS
.
(Bug #31894662)
In some cases, MIN()
and
MAX()
incorrectly returned
NULL
when used as window functions with
temporal or JSON
values as
arguments.
(Bug #31882291)
GRANT ... GRANT OPTION
... TO
and
GRANT ... TO .. WITH
GRANT OPTION
sometimes were not correctly written to
the server logs.
(Bug #31869146, Bug #100793)
For debug builds, CREATE TABLE
using a partition list of more than 256 entries raised an
assertion.
(Bug #31867653)
It was possible for queries in the file named by the
init_file
system variable to
cause server startup failure.
(Bug #31835782)
When performing a hash join, the optimizer could register a false match between a negative integer value and a very large unsigned integer value. (Bug #31832001, Bug #31940639, Bug #100967)
SHOW VARIABLES
could report an
incorrect value for the
partial_revokes
system
variable.
(Bug #31819558, Bug #100677)
In the Performance Schema
user_defined_functions
table, the
value of the UDF_LIBRARY
column is supposed
to be NULL
for loadable functions registered
via the service API. The value was incorrectly set to the empty
string.
(Bug #31791754)
The server automatic upgrade procedure failed to upgrade older
help tables that used the latin1
character
set.
(Bug #31789964)
Duplicate warnings could occur when executing an SQL statement that read the grant tables in serializable or repeatable-read transaction isolation level. (Bug #31769242)
In certain queries with DISTINCT
aggregates
(which in general are solved by sorting before aggregation), the
server used a temporary table instead of streaming due to the
mistaken assumption that the logic for handling the temporary
table performed deduplication. Now the server checks for the
implied unique index instead, which is more robust and allows
for the removal of unnecessary logic.
(Bug #31762806)
Certain combinations of
lower_case_table_names
values
and schema names in Event Scheduler event definitions could
cause the server to stall.
(Bug #31733090)
Calling one stored function from within another could produce a conflict in field resolution, resulting in a server exit. (Bug #31731334)
Loadable functions defined without a
udf_init()
method could cause an unexpected
server exit.
(Bug #31701219)
Setting the secure_file_priv
system variable to NULL
should disable its
action, but instead caused the server to create a directory
named NULL
.
(Bug #31700734, Bug #100384)
mysqlpump could exit unexpectedly due to improper simultaneous accesses to shared structures. (Bug #31696241)
Uninstalling a component and deregistering loadable functions installed by the component was not properly synchronized with whether the functions were currently in use. (Bug #31646698)
Cleanup following execution of a prepared statement that
performed a multi-table UPDATE
or
DELETE
was not always done
correctly, which meant that, following the first execution of
such a prepared statement, the server reported a nonzero number
of rows updated, even though no rows were actually changed.
(Bug #31640267)
References: See also: Bug #32100210.
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)
When MySQL is configured with
-DWITH_ICU=system
,
CMake now checks that the ICU library version
is sufficiently recent.
(Bug #31600044)
When invoked with the
--binary-as-hex
option,
mysql displayed NULL
values as empty binary strings (0x
).
Selecting an undefined variable returned the empty binary string
(0x
) rather than NULL
.
(Bug #31549724, Bug #31638968, Bug #100251)
Enabling
DISABLE_PSI_
Performance Schema-related CMake options
caused build failures.
(Bug #31549724)xxx
Some queries returned different results depending on the value
of
internal_tmp_mem_storage_engine
.
The root cause of this issue related to the fact that, when buffering rows for window functions, if the size of the in-memory temporary table holding these buffered rows exceeds the limit specified, a new temporary table is created on disk; the frame buffer partition offset is set at the beginning of a new partition to the total number of rows that have been read so far, and is updated specifically for use when the temporary table is moved to disk (this being used to calculate the hints required to process window functions). The problem arose because the frame buffer partition offset was not updated for the specific case when a new partition started while creating the temporary table on disk, which caused the wrong rows to be read.
This issue is fixed by making sure to update the frame buffer partition offset correctly whenever a new partition starts while a temporary table is moved to disk. (Bug #31546816)
While buffering rows for window functions, if the size of the
in-memory temporary table holding these buffered rows exceeds
the limit specified by
temptable_max_ram
, a new
temporary table is created on disk. After the creation of the
temporary table, hints used to process window functions need to
be reset, since the temporary table is now moved to disk, making
the existing hints unusable. When the creation of the temporary
table on disk occurred when the first row in the frame buffer
was being processed, the hints had not been initialized and
trying to reset these uninitialized hints resulted in an
unplanned server exit.
This issue is fixed by adding a check to verify whether frame buffer hints have been initialized, prior to resetting them. (Bug #31544404)
The Performance Schema could produce incorrect results for joins
on a CHANNEL_NAME
column when the index for
CHANNEL_NAME
was disabled with USE
INDEX ()
.
(Bug #31544023, Bug #99989)
When removing unused window definitions, a subquery that was
part of an ORDER BY
was not removed.
(Bug #31518806)
In certain cases, the server did not handle multiply-nested subqueries correctly. (Bug #31472704)
The recognized syntax for a
VALUES
statement includes an
ORDER BY
clause, but this clause was not
resolved, so the execution engine could encounter invalid data.
(Bug #31387510)
The server attempted to access a non-existent temporary
directory at startup, causing a failure. Checks were added to
ensure that temporary directories exist, and that files are
successfully created in the
tmpdir
directory.
(Bug #31377118)
While removing redundant sorting, a window's ordering was removed due to the fact that rows were expected to come in order because of the ordering of another window. When the other window was subsequently removed because it was unused, this resulted in unordered rows, which was not expected during evaluation.
Now in such cases, removal of redundant sorts is not performed until after any unused windows have been removed. In addition, resolution of any rollups has been moved to the preparation phase. (Bug #31361393)
Semisynchronous replication errors were incorrectly written to
the error log with a subsystem tag of Server
.
They are now written with a tag of Repl
, the
same as for other replication errors.
(Bug #31327337)
A user could grant itself as a role to itself. (Bug #31222230)
The server did not always correctly handle cases in which
multiple WHERE
conditions, one of which was
always FALSE, referred to the same subquery.
(Bug #31216115)
With a lower_case_table_names=2
setting, InnoDB
background threads sometimes
acquired table metadata locks using the wrong character case for
the schema name part of a lock key, resulting in unprotected
metadata and race conditions. The correct character case is now
applied. Changes were also implemented to prevent metadata locks
from being released before corresponding data dictionary
objects, and to improve assertion code that checks lock
protection when acquiring data dictionary objects.
(Bug #31165802)
If a CR_UNKNOWN_ERROR
was to be
sent to a client, an exception occurred.
(Bug #31123643)
Conversion of DOUBLE
values to values of type
BIT
, ENUM
, or
SET
could generate Undefined Behavior
Sanitizer warnings.
(Bug #31019130)
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)
When creating a multi-valued or other functional index, a performance drop was seen when executing a query against the table on which the index was defined, even though the index itself was not actually used. This occurred because the hidden virtual column that backs such indexes was evaluated unnecessarily for each row in the query. (Bug #30838749)
References: This issue is a regression of: Bug #28069731.
CMake checks for libcurl
dependencies were improved.
(Bug #30268245)
mysql_config_editor incorrectly treated
#
in password values as a comment character.
(Bug #29861961, Bug #95597)
In some cases, the optimizer attempted to compute the hash value for an empty string. Now a fixed value is always used instead. (Bug #22588319)
The INSERT()
and
RPAD()
functions did not
correctly set the character set of the result.
(Bug #22523946, Bug #79909, Bug #31887870, Bug #100841)
Some corner cases for
were fixed, such as
that val1
BETWEEEN val2
AND
val3
-1 BETWEEN 9223372036854775808 AND 1
returned true.
(Bug #22515857, Bug #79878)
For the Performance Schema
memory_summary_global_by_event_name
table, the low watermark columns could have negative values, and
the high watermark columns had ever-increasing values even when
the server memory usage did not increase.
(Bug #22246001, Bug #79285)
Several issues converting strings to numbers were fixed. (Bug #19186271, Bug #73248)
Certain group by queries that performed correctly did not return
the expected result when WITH ROLLUP
was
added. This was due to the fact that decimal information was not
always correctly piped through rollup group items, causing
functions returning decimal values such as
TRUNCATE()
to receive data of the
wrong type.
(Bug #101684, Bug #32179240)
When creating fields for materializing temporary tables (that
is, when needing to sort a join), the optimizer checks whether
the item needs to be copied or is only a constant. This was not
done correctly in one specific case; when performing an outer
join against a view or derived table containing a constant, the
item was not properly materialized into the table, which could
yield spurious occurrences of NULL
in the
result.
(Bug #101622, Bug #32162862)
References: See also: Bug #31790217.
When REGEXP_REPLACE()
was used in
an SQL statement, the internal function
Regexp_engine::Replace()
did not reset the
error code value after handling a record, which could affect
processing of the next record, which lead to issues.
Our thanks to Hope Lee for the contribution. (Bug #101256, Bug #32050219)
For a query having the following form, the column list sometimes assumed an inconsistent state after temporary tables were created, causing out-of-bounds indexing later:
SELECT * FROM ( SELECT PI() FROM t1 AS table1, t1 AS table2 ORDER BY PI(), table1.a ) AS d1;
(Bug #101012, Bug #31955761, Bug #31978439)
References: This issue is a regression of: Bug #31790217.
When aggregating data that was already sorted (known as performing streaming aggregation, due to no temporary tables being used), it was not possible to determine when a group ended until processing the first row in the next group, by which time the group expressions to be output were often already overwritten.
This is fixed by replacing the complex logic previously used with the much simpler method of saving a representative row for the group when encountering it the first time, so that its columns can easily be retrieved for the output row when needed. (Bug #100791, Bug #27272052, Bug #31073167, Bug #31790217, Bug #31868610)
Subqueries making use of fulltext matching might not perform
properly when
subquery_to_derived
was
enabled, and could lead to an assert in debug builds.
(Bug #100749, Bug #31851600)
When an ALTER TABLE
... CONVERT TO CHARACTER SET
statement is executed,
the character set of every CHAR
,
VARCHAR
, and
TEXT
column in the table is
updated to the new CHARACTER SET
value. This
change was also applied to the hidden CHAR
column used by an ARRAY
column for a
multi-valued index; since the character set of the hidden column
must be one of my_charset_utf8mb4_0900_bin
or
binary
, this led to an assert in debug builds
of the server.
This issue is resolved by no longer setting the character set of
the hidden column to that of the table when executing the
ALTER TABLE
statement referenced previously;
this is similar to what is done for
BLOB
columns in similar
circumstances.
(Bug #99403, Bug #31301101)
In some cases, the server's internal string-conversion routines had problems handling floating-point values which used length specifiers and triggered use of scientific notation. (Bug #92537, Bug #101570, Bug #28691605, Bug #32144265)
References: See also: Bug #88256, Bug #27041543.