There is an issue for MySQL 8.0.19 installed using MySQL Installer that prevents the server from starting if MySQL Enterprise Firewall is selected during the server configuration steps. If the server startup operation fails, clickto end the configuration process and return to the dashboard. You must uninstall the server.
The workaround is to run MySQL Installer without MySQL Enterprise Firewall selected. Then install MySQL Enterprise Firewall afterward using the instructions for manual installation (see Installing or Uninstalling MySQL Enterprise Firewall). This problem is corrected in MySQL 8.0.20.
MySQL now enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the
PASSWORD_LOCK_TIMEoptions of the
ALTER USERstatements. See Password Management. (Bug #27733694, Bug #90169)
ANALYZE TABLEstatements now produce
readaudit events. (Bug #29625461)
Audit log connect events now include any connection attributes passed by the client. Connection attribute logging is supported for new-style XML log file format and JSON format, but not old-style XML format. See Audit Log File Formats.
Microsoft Windows: On Windows, the minimum version of CMake for builds from the command line is now 3.15. (Bug #30332632, Bug #96954)
FPROFILE_USECMake options are available for experimenting with profile guided optimization (PGO) with GCC. See the
cmake/fprofile.cmakein a MySQL source distribution for information about using them. These options have been tested with GCC 8 and 9, and with Clang.
Innodb_system_rows_deletedstatus variables were added for counting row operations on
InnoDBtables that belong to system-created schemas. The new status variables are similar to the existing
Innodb_rows_deletedstatus variables, which count operations on
InnoDBtables that belong to both user-created and system-created schemas.
The new status variables are useful in replication environments where
master_info_repositoryvariables are set to
TABLE, resulting in higher row operation counts on slaves due to operations performed on the
slave_worker_infotables, which belong to the system-created
mysqlschema. For a valid comparison of master and slave row operation counts, operations on tables in system-created schemas can now be excluded using the count data provided by the new status variables.
Thanks to Facebook for the contribution. (Bug #27724674)
hash_joinoptimizer switch (see
optimizer_switchsystem variable) no longer has any effect. The same applies with respect to the
NO_HASH_JOINoptimizer hints. Both the optimizer switch and the optimizer hint are now deprecated, and subject to removal in a future release of MySQL.
This also fixes an issue whereby
SELECT DISTINCT ... WITH ROLLUPdid not always return all distinct rows. (Bug #27549694, Bug #30471809)
Support for the
YEAR(2)data type was removed in MySQL 5.7.5, leaving only
YEAR(4)as valid specifications for year-valued data. Because
YEAR(4)are semantically identical, specifying a display width is unnecessary, so
YEAR(4)is now deprecated and support for it will be removed in a future MySQL version. Statements that include data type definitions in their output no longer show the display width for
YEAR. This change applies to tables, views, and stored routines, and affects the output from
DESCRIBEstatements, and from
INFORMATION_SCHEMAqueries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width.
YEARis also now deprecated and support for it will be removed in a future MySQL version.
Error messages regarding crash recovery for XA were revised to indicate XA context to distinguish them from non-XA crash recovery messages. (Bug #30578290, Bug #97743)
Previously, the server returned this error message for attempts to use
LOAD DATA LOCALwith
LOCALcapability disabled: The used command is not allowed with this MySQL version. This was misleading because the error condition is not related to the MySQL version. The server now returns an error code of
ER_CLIENT_LOCAL_FILES_DISABLEDand this message: Loading local data is disabled; this must be enabled on both the client and server side. (Bug #30375698, Bug #29377985, Bug #94396)
Previously, user-defined functions (UDFs) took no account of the character set or collation of string arguments or return values. In effect, string arguments and return values were treated as binary strings, with the implication that only string arguments containing single-byte characters could be handled reliably.
UDF behavior is still the same by default, but the interface for writing UDFs has been extended to enable UDFs to determine the character set and collation of string arguments, and to return strings that have a particular character set and collation. These capabilities are optional for UDF writers, who may take advantage of them as desired. See User-Defined Function Character Set Handling
Of the UDFs distributed with MySQL, those associated with the following features and extensions have been modified to take advantage of the new capabilities: MySQL Enterprise Audit, MySQL Enterprise Firewall, MySQL Enterprise Data Masking and De-Identification, MySQL Keyring (the general-purpose keyring UDFs only), and Group Replication. The modification applies only where it make sense. For example, a UDF that returns encrypted data is intended to return a binary string, not a character string.
Character-set capabilities for UDFs are implemented using the
mysql_udf_metadataserver component service. For information about this service, see the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html (search for
udf_metadata_imp). Source code for the MySQL Keyring UDFs is available in Community source distributions and may be examined as examples for third-party UDF writers who wish to modify their own UDFs to be character set-aware.
INFORMATION_SCHEMAcontains several new tables that expose role information:
ADMINISTRABLE_ROLE_AUTHORIZATIONS: Roles the current user can grant; see The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table.
APPLICABLE_ROLES: Roles applicable for the current user; see The INFORMATION_SCHEMA APPLICABLE_ROLES Table.
ENABLED_ROLES: Roles enabled within the current session; see The INFORMATION_SCHEMA ENABLED_ROLES Table.
ROLE_COLUMN_GRANTS: Column privileges for roles for the current user; see The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table.
ROLE_ROUTINE_GRANTS: Routine privileges for roles for the current user; see The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table.
ROLE_TABLE_GRANTS: Table privileges for roles for the current user; see The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table.
SECRETkey type is available that is intended for general-purpose storage of sensitive data using the MySQL keyring. The keyring encrypts and decrypts
SECRETdata as a byte stream upon storage and retrieval. The
SECRETkey type is supported by all keyring plugins. See Supported Keyring Key Types and Lengths.
SIGUSR1signal now causes the server to flush the error log, general query log, and slow query log. One use for
SIGUSR1is to implement log rotation without having to connect to the server (which to flush logs requires an account that has the
RELOADprivilege). The server response to
SIGUSR1is a subset of the response to
SIGUSR1to be used as a more “lightweight” signal that flushes certain logs without the other
SIGHUPeffects such as flushing the thread and host caches and writing a status report to the error log. See Unix Signal Handling in MySQL.
zstdlibrary bundled with MySQL has been upgraded from version 1.3.3 to 1.4.3. MySQL uses the
zstdlibrary to support connection compression. (Bug #30236685)
For package types for which OpenSSL shared libraries are included, they are now also included under
lib/privateif the package has private-to-MySQL libraries located there that need OpenSSL. (Bug #29966296)
Important Change: MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the
TABLEstatement and the
VALUESstatement, each described in brief here:
TABLEis equivalent to
SELECT * FROM, and can be used anywhere that the equivalent
SELECTstatement would be accepted; this includes joins, unions,
INSERT ... SELECTstatements,
CREATE TABLE ... SELECTstatements, and subqueries.
You can use
TABLE, which also supports
OFFSET; these clauses function in the same way in a
TABLEstatement as they do with
SELECT. The following two statements produce the same result:
TABLE t ORDER BY c LIMIT 10 OFFSET 3; SELECT * FROM t ORDER BY c LIMIT 10 OFFSET 3;
VALUESconsists of the
VALUESkeyword followed by a series of row constructors (
ROW()), separated by commas. It can be used to supply row values in an SQL-compliant fashion to an
REPLACEstatement. For example, the following two statements are equivalent:
INSERT INTO t1 VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9); INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9);
You can also select from a
VALUEStable value constructor just as you would a table, bearing in mind that you must supply a table alias when doing so. Using column aliases, you can also select individual columns, like this:
mysql> SELECT a,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c); +---+---+ | a | c | +---+---+ | 1 | 3 | | 4 | 6 | +---+---+
You can employ such
SELECTstatements in joins, unions, subqueries, and other constructs in which you normally expect to be able to use such statements.
For more information and examples, see TABLE Statement, and VALUES Statement, as well as INSERT ... SELECT Statement, CREATE TABLE ... SELECT Statement, JOIN Clause, UNION Clause, and Subqueries. (Bug #77639)
Previously, it was not possible to use
LIMITin the recursive
SELECTpart of a recursive common table expression (CTE).
LIMITis now supported in such cases, along with an optional
OFFSETclause. An example of such a recursive CTE is shown here:
WITH RECURSIVE cte AS ( SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL UNION ALL SELECT CONCAT("x",cte.a) FROM cte WHERE LENGTH(cte.a) < 10 LIMIT 3 OFFSET 2 ) SELECT * FROM cte;
This statement produces the following output in the mysql client:
+-------+ | a | +-------+ | xxx | | xxxx | | xxxxx | +-------+
LIMITin this fashion can make execution of the CTE more efficient than doing so in the outermost
SELECT, since only the requested number of rows is generated.
For more information, see Recursive Common Table Expressions. (Bug #92857, Bug #28816906)
CHECKconstraints were implemented in MySQL 8.0.16,
ALTER CHECKsyntax as MySQL extensions to standard SQL for modifying check constraints, but did not support the more general (and SQL standard)
ALTER CONSTRAINTsyntax for modifying existing constraints of any type. That syntax is now supported; the constraint type is determined from the constraint name.
MySQL now supports aliases in the
INSERT INTO ... ON DUPLICATE KEY UPDATEstatement for the row to be inserted and its columns. Consider a statement such as this one:
INSERT INTO t VALUES (9,5), (7,7), (11,-1) ON DUPLICATE KEY UPDATE a = a + VALUES(a) - VALUES(b);
Using the alias
newfor the inserted row, you can now rewrite the statement, referring back to the row alias in the
ON DUPLICATE KEY UPDATEclause, like this:
INSERT INTO t VALUES (9,5), (7,7), (11,-1) AS new ON DUPLICATE KEY UPDATE a = a + new.a - new.b;
Using the same row alias, and, additionally, the column aliases
nfor the columns of the inserted row, you can omit the row alias and use only the column aliases, as shown here:
INSERT INTO t VALUES (9,5), (7,7), (11,-1) AS new(m,n) ON DUPLICATE KEY UPDATE a = a + m - n;
The row alias must be distinct from the table name; column aliases must be distinct from one another.
See INSERT ... ON DUPLICATE KEY UPDATE Statement, for more information and examples.
sysschema objects have been reimplemented not to invoke the deprecated
sys.ps_thread_id()stored functions. Instead, they invoke the equivalent built-in SQL functions implemented in MySQL 8.0.16 that format or retrieve Performance Schema data (see Changes in MySQL 8.0.16 (2019-04-25, General Availability)).
sys.ps_thread_id()will be removed in a future MySQL version, so applications that use them should be adjusted to use the built-in functions instead, keeping in mind some minor differences between the
sysfunctions and the built-in functions. See Performance Schema Functions.
By default, the thread pool plugin tries to ensure a maximum of one thread executing in each group at any time. The default algorithm takes stalled threads into account and may temporarily permit more active threads. The plugin now implements a new
thread_pool_max_active_query_threadssystem variable for controlling number of active threads per group. If
thread_pool_max_active_query_threadsis 0, the default algorithm applies. If
thread_pool_max_active_query_threadsis greater than 0, it places a limit on the number of active threads per group. See Thread Pool Operation.
X Plugin could not be compiled on Debian with GCC 9. The
--no-as-neededlinker option was added to provide a workaround for the issue. (Bug #30445201)
Using X Protocol to query the Information Schema table
TRIGGERScould result in errors being returned or some rows not being returned. (Bug #30318917)
In MySQL 5.7.14, the
mysqlxnamespace parameter was introduced for X Protocol's
StmtExecuterequest, replacing the
xpluginparameter, which was therefore deprecated. X Plugin continued to support the deprecated
xpluginnamespace for backward compatibility. In MySQL 8.0.19, the
xpluginnamespace has now been removed. If the
xpluginnamespace is used from this release on, an error message is returned as for an unknown namespace. X Plugin's
Mysqlx_stmt_execute_xpluginstatus variable, which counted the number of
StmtExecuterequests received for the
xpluginnamespace, is no longer used from MySQL 8.0.19.
Microsoft Windows: Previously, the
\!) command for the mysql command-line client worked only for Unix systems. It now works on Windows as well. For example,
\! clsmay be used to clear the screen. (Bug #11765690, Bug #58680)
JSON: When using
JSON_SCHEMA_VALID()to specify a
CHECKconstraint on a table containing one or more JSON columns and experiencing a validation failure, MySQL now provides detailed information about the reasons for such failures. A new error
ER_JSON_SCHEMA_VALIDATION_ERROR_WITH_DETAILED_REPORTis implemented containing this information, which can be viewed in the mysql client by issuing
SHOW WARNINGSwhen an
INSERTstatement is rejected by the server.
Display width specification for integer data types was deprecated in MySQL 8.0.17, and now statements that include data type definitions in their output no longer show the display width for integer types, with these exceptions:
The type is
TINYINT(1). MySQL Connectors make the assumption that
TINYINT(1)columns originated as
BOOLEANcolumns; this exception enables them to continue to make that assumption.
The type includes the
This change applies to tables, views, and stored routines, and affects the output from
DESCRIBEstatements, and from
INFORMATION_SCHEMAqueries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width. (Bug #30556657, Bug #97680)
Replication connections to a replication slave, and Group Replication connections for distributed recovery, now have full client side configuration options for the TLSv1.3 protocol. In MySQL releases where TLSv1.3 support was available but these configuration options were not available, if TLSv1.3 was used for these connection types, the client in the connection (the replication slave or the Group Replication joining member that initiated distributed recovery) could not be configured. This meant that the server in the connection (the replication master or the Group Replication existing member that was the donor for distributed recovery) had to permit the use of at least one TLSv1.3 ciphersuite that is enabled by default. From MySQL 8.0.19, you can use the configuration options to specify any selection of ciphersuites for these connections, including only non-default ciphersuites if you want.
The new configuration options are as follows:
Group Replication system variables
group_replication_recovery_tls_versionspecifies a list of permitted TLS protocols for connection encryption for the client instance (the joining member) in the distributed recovery connection.
group_replication_recovery_tls_ciphersuitesspecifies a list of permitted ciphersuites when TLSv1.3 is used for that connection.
MASTER_TLS_CIPHERSUITESoption on the
CHANGE MASTER TOcommand, to specify a list of TLSv1.3 ciphersuites permitted by the replication slave for the connection to the replication master. (The
CHANGE MASTER TOcommand already had a
MASTER_TLS_VERSIONoption to specify the permitted TLS protocol versions for the connection.)
Debian packages now contain more general systemd support that better supports manual mysqld execution. (Bug #29702050, Bug #95163)
The Group Replication plugin interacts with MySQL Server using internal sessions to perform SQL API operations. Previously, these sessions counted towards the client connections limit specified by the
max_connectionsserver system variable. If the server had reached this limit when Group Replication was started or attempted to perform an operation, the operation was unsuccessful and Group Replication or the server itself might stop. From MySQL 8.0.19, Group Replication's interactions with MySQL Server use a new component service that handles the internal sessions separately, which means that they do not count towards the
max_connectionslimit and are not refused if the server has reached this limit. (Bug #29635001)
Duplicate key error information was extended to include the table name of the key. Previously, duplicate key error information included only the key value and key name. Thanks to Facebook for the contribution. (Bug #28686224, Bug #925308)
When the mysql client operates in interactive mode, the
--binary-as-hexoption now is enabled by default. In addition, output from the
\s) command includes this line when the option is enabled implicitly or explicitly:
Binary data as: Hexadecimal
To disable hexadecimal notation, use
MySQL now supports datetime literals with time zone offsets, such as
'2003-04-14 03:30:00+10:00', and
'2020-01-01 15:35:45+05:30'; these offsets are respected but not stored when inserting such values into
DATETIMEcolumns; that is, offsets are not displayed when retrieving the values.
The supported range for a timezone offset is
+14:00, inclusive. Time zone names such as
'America/Argentina/Buenos_Aires', including the special value
'SYSTEM', are not supported in datetime literals. In addition, in this context, a leading zero is required for an hour value less than 10, and MySQL rejects the offset
Datetime literals with time zone offsets can also be used as parameter values in prepared statements.
As part of this work, the allowed range of numeric values for the
time_zonesystem variable has been changed, so that it is now also
From MySQL 8.0.19, compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. By default, X Protocol announces support for the
zstdcompression algorithms. You can disallow any of these algorithms by setting the new
mysqlx_compression_algorithmssystem variable to include only the ones you permit. X Protocol always allows uncompressed connections if the client does not request compression during capability negotiation. Note that X Protocol's list of permitted compression algorithms operates independently of the list of compression algorithms announced by MySQL Server, and X Protocol does not fall back to using MySQL Server's compression settings. You can monitor the effects of message compression for X Protocol using new X Plugin status variables.
For multithreaded slaves (replication slaves on which
slave_parallel_workersis set to a value greater than 0), setting
slave_preserve_commit_order=1ensures that transactions are executed and committed on the slave in the same order as they appear in the slave's relay log, preserving the same transaction history on the slave as on the master. Previously, this setting required binary logging and slave update logging to be enabled on the slave, with the associated execution costs and disk space requirements. Now,
slave_preserve_commit_order=1can be set on a slave with no binary log and no slave update logging. This enables you to preserve commit order on the slave, and avoid gaps in the sequence of transactions, without the overhead of binary logging.
A limitation to preserving the commit order on the slave can occur if statement-based replication is in use, and both transactional and non-transactional storage engines participate in a non-XA transaction that is rolled back on the master. Normally, non-XA transactions that are rolled back on the master are not replicated to the slave, but in this particular situation, the transaction might be replicated to the slave. If this does happen, a multithreaded slave without binary logging does not handle the transaction rollback, so the commit order on the slave diverges from the relay log order of the transactions in that case.
The MySQL 8.0.18 release introduced the ability to specify a
PRIVILEGE_CHECKS_USERaccount for a replication channel (using a
CHANGE MASTER TOstatement), against which MySQL makes privilege checks when replicated transactions are applied. The use of a
PRIVILEGE_CHECKS_USERaccount helps secure a replication channel against the unauthorized or accidental use of privileged or unwanted operations. The use of row-based binary logging is strongly recommended when replication channels are secured with privilege checks.
In MySQL 8.0.19, a new setting
REQUIRE_ROW_FORMATis added for replication channels, which makes the channel accept only row-based replication events. You can specify
CHANGE MASTER TOstatement to enforce row-based binary logging for a replication channel that is secured with privilege checks, or to increase the security of a channel that is not secured in this way. By allowing only row-based replication events,
REQUIRE_ROW_FORMATprevents the replication applier from taking actions such as creating temporary tables and executing
LOAD DATA INFILErequests, which protects the replication channel against some known attack vectors. Row-based binary logging (
binlog_format=ROW) must be used on the replication master when
Group Replication already requires row-based binary logging, so from MySQL 8.0.19, Group Replication's channels are automatically created with
REQUIRE_ROW_FORMATset, and you cannot change the option for those channels. The setting is also applied to all Group Replication channels on upgrade.
mysqlbinlog has a new
--require-row-formatoption, which enforces row-based replication events for mysqlbinlog's output. The stream of events produced with this option would be accepted by a replication channel that is secured using the
MySQL uses delimiter strings when constructing tablespace names and file names for table partitions. A “
#p#” delimiter string precedes partition names, and an “
#sp#” delimiter string precedes subpartition names, as shown:
Historically, delimiter strings have been uppercase (
#SP#) on case-sensitive file systems such as Linux, and lowercase (
#sp#) on case-insensitive file systems such as Windows. To avoid issues when migrating data directories between case-sensitive and case-insensitive file systems, delimiter strings are now lowercase on all file systems. Uppercase delimiter strings are no longer used.
Additionally, partition tablespace names and file names generated based on user-specified partition or subpartition names, which can be specified in uppercase or lowercase, are now generated (and stored internally) in lowercase regardless of the
lower_case_table_namessetting to ensure case-insensitivity. For example, if a table partition is created with the name
PART_1, the tablespace name and file name are generated in lowercase:
During upgrade, MySQL now checks and modifies if necessary:
Partition file names on disk and in the data dictionary to ensure lowercase delimiters and partition names.
Partition metadata in the data dictionary for related issues introduced by previous bug fixes.
InnoDBstatistics data for related issues introduced by previous bug fixes.
During tablespace import operations, partition tablespace file names on disk are checked and modified if necessary to ensure lowercase delimiters and partition names.
References: See also: Bug #26925260, Bug #29823032, Bug #30012621, Bug #29426720, Bug #30024653.
Support was added for efficient sampling of
InnoDBdata for the purpose of generating histogram statistics. The default sampling implementation used by MySQL when storage engines do not provide their own requires a full table scan, which is costly for large tables. The
InnoDBsampling implementation improves sampling performance by avoiding full table scans. The
INNODB_METRICScounters can be used to monitor sampling of
InnoDBdata pages. See Histogram Statistics Analysis.
Important Change: Character set resolution has been changed for the following string functions:
Previously, character set information for all arguments to these functions was aggregated, which could lead to results that were not well formed. This also caused issues with
LPAD(), which assumes that both input and output are well formed. Now each of the three listed functions always uses the character set employed by
str, and converts all other arguments to this character set at execution time; if any such conversion fails, the function returns an error. (Bug #30114420)
References: This issue is a regression of: Bug #28197977.
Important Change: Subquery materialization no longer requires strict matching of inner and outer types. Different types can now be materialized when one of the following conditions is true:
The inner type is numeric (since there is always a way to cast the outer type to a number)
The inner type is temporal (since there is always a way to cast the outer type to a temporal)
Both types are strings
NDB Cluster: Password masking was incomplete for some NDB logging options. (Bug #97335, Bug #30453137)
InnoDB: Initialization of certain internal data structures at startup depend on internal variables derived from the
InnoDBfailed to resize the internal data structures when the
max_connectionssetting was modified after startup using
SET PERSIST. (Bug #30628872)
os_file_get_parent_dirwarnings were encountered when compiling MySQL with GCC 9.2.0. (Bug #30499288, Bug #97466)
InnoDB: An attempt to access a large object (LOB) value using a null reference raised an assertion failure. To prevent this issue form occurring, a check was added to determine if LOB references are null before they are accessed. (Bug #30499064)
InnoDB: An assertion failure occurred after upgrading the data directory. Prepared XA transaction were still present, which prevented undo tablespaces from being upgraded. Undo tablespaces containing prepared transaction changes must remain active until all prepared XA transactions are committed or rolled back.
Prepared XA transactions also prevented the completion of an explicit undo tablespace truncation operation after a restart. (Bug #30489497)
InnoDB: Attempting to upgrade a MySQL 5.7 instance on Linux with uppercase table names (partitioned or otherwise) to MySQL 8.0 on macOS raised an assertion failure. Partition file format changes in MySQL 8.0 prevented migration of the data directory to a different platform, and the
lower_case_table_namessetting was changed at upgrade time, which can cause an upgrade failure. Instead of a failure occurring under these circumstances, an error is now reported. (Bug #30450968, Bug #30450979)
InnoDB: On macOS, a failure occurred when attempting to upgrade a MySQL 5.7 instance with uppercase table names to MySQL 8.0. Uppercase table names were not normalized to lowercase. The following errors were reported: Table is not found in InnoDB dictionary and Error in fixing SE data errors. (Bug #30450944)
InnoDB: On Windows, a failure occurred when attempting to upgrade a MySQL 5.7 instance with uppercase partitioned table names to MySQL 8.0. Opening the table returned a null pointer, which caused a segmentation fault when closing the table. (Bug #30450918)
InnoDB: On Windows, a mysqld exception was raised when attempting to upgrade a MySQL 5.7 instance with uppercase partitioned table names to MySQL 8.0. (Bug #30447790)
InnoDB: On Windows, a failure occurred when attempting to upgrade a MySQL 5.7 instance containing general tablespace defined with an uppercase name to MySQL 8.0. The following errors were reported: Error in fixing SE data and Failed to Populate DD. (Bug #30446798)
InnoDB: Introduction of local minitransactions (mtrs) in LOB-related code resulted in an assertion failure during recovery. (Bug #30417719)
InnoDB: A failure occurred when attempting to upgrade a MySQL 5.7 instance on Windows with uppercase partitioned table names to MySQL 8.0 on Linux. Partition file format changes in MySQL 8.0 prevented migration of the data directory to a different platform. Instead of a failure, an error is now reported. (Bug #30411118)
InnoDB: Updating the same compressed LOB data repeatedly caused the tablespace file to increase in size. (Bug #30353812)
InnoDB: When the
temptable_max_ramlimit was reached, the TempTable storage engine incorrectly reported an out-of-memory error instead of falling back to disk-based storage. (Bug #30314972, Bug #96893)
InnoDB: After importing an encrypted table and restarting the server, the following error was returned when attempting to access the table: ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully. The tablespace key was not written to disk after it was encrypted with the destination master key. (Bug #30313734)
InnoDB: The internal
dict_create_foreign_constraints()function that parsed SQL statements and performed foreign key related DDL checks was removed. The function became redundant with introduction of the data dictionary in MySQL 8.0 and the subsequent relocation of foreign key related DDL checks to the SQL layer.
Removal of the
dict_create_foreign_constraints()function also addressed the following foreign key issues:
Spaces around dots (“.”) in a fully qualified referenced table name were not permitted by the
Adding a foreign key and removing partitioning in the same
ALTER TABLEstatement was not permitted. The
InnoDBparser did not detect that the new table version was no longer partitioned.
A foreign key constraint could not reference a table inside a schema named “AUX”. The function that parsed referenced table names did not recognize that special names such as AUX are encoded.
Conditional comments in foreign key definitions were ignored.
Additionally, a check was added to the SQL layer to detect attempts to create multiple foreign keys of the same name on a table at an early stage in the execution of an
ALTER TABLEstatement. (Bug #30287895, Bug #22364336, Bug #28486106, Bug #28703793, Bug #16904122, Bug #92567, Bug #11754659, Bug #46293)
InnoDB: A comparison function found two records to be equal when attempting to merge non-leaf pages of a spatial index. The function was unable to handle this unexpected condition, which resulted in a long semaphore wait and an eventual assertion failure. (Bug #30287668)
InnoDB: A locally acquired latch required for freeing a large object (LOB) page could have caused a deadlock if a subsequent caller attempted to acquire a latch for the same page before the page was freed. Similarly, a latch taken on a compressed or uncompressed LOB during a rollback related operation could have caused a deadlock due to a latching order issue. (Bug #30258536)
References: This issue is a regression of: Bug #29846292.
InnoDB: A race condition between a purge thread that was purging a compressed LOB page and an update thread that is using a delete-marked record caused an assertion failure. (Bug #30197056)
InnoDB: A tablespace import operation that failed due to the source and destination tables being defined with different
DATA DIRECTORYclauses reported an insufficiently descriptive schema mismatch error. Moreover, if a
.cfgfile was not present, the same operation would raise an assertion failure. A more informative error message is now reported in both cases before the import operation is terminated due to the data directory mismatch. (Bug #30190199, Bug #30190227, Bug #20644698, Bug #76142)
InnoDB: A purge operation failed when attempting to purge a LOB value larger than the buffer pool. (Bug #30183982)
InnoDB: Update operations that moved externally stored LOB data to inline storage failed to mark the old LOB data as purgeable. (Bug #30178056, Bug #96466)
InnoDB: Index key part sort order information was not stored to the
.cfgmetadata file used by
ALTER TABLE ... IMPORT TABLESPACEoperations. The index key part sort order was therefore assumed to be ascending, which is the default. As a result, records could be sorted in an unintended order if one table involved in the import operation is defined with a
DESCindex key part sort order and the other table is not. To address this issue, the
.cfgfile format was updated to include index key part sort order information. (Bug #30128418)
InnoDB: Criteria used by the
btr_cur_will_modify_tree()function, which detects whether a modifying record needs a modifying tree structure, was insufficient. (Bug #30113362)
InnoDB: Startup was slow on instances with a large number of tables due the tablespace file scan that occurs at startup to retrieve space IDs. A multithreaded scan was only initiated if the number of tablespace files exceed 50,000, and three tablespace pages were read to retrieve a space ID. To improve startup times, additional threads are now allocated for the tablespace file scan, and only the first tablespace page is read to retrieve a space ID. If a space ID is not found on the first page of the tablespace, three pages are read to determine the space ID, as before. (Bug #30108154, Bug #96340)
InnoDB: Startup failed on a case insensitive file system with an error indicating that multiple files were found for the same tablespace ID. A file path comparison did not recognize that
datadirpaths were the same due to the paths having different lettercases. (Bug #30040815)
InnoDB: A storage engine error occurred when accessing the
mysql.innodb_table_statspersistent optimizer statistics tables after upgrading a MySQL 8.0.13 instance on Linux with partitioned tables and a
lower_case_table_names=1setting to MySQL 8.0.14 or MySQL 8.0.15. The persistent optimizer statistics tables contained duplicate entries. (Bug #30012621)
References: This issue is a regression of: Bug #26925260.
CREATE TABLESPACEfailed with an error indicating that the tablespace already exists. The error was due to the failure of a preceding
CREATE TABLESPACEoperation where the DDL failed but related changes were not rolled back due to rollback being disabled prior to transaction commit. Rollback is now disabled after the transaction commits successfully. (Bug #29959193, Bug #95994)
InnoDB: Changed pages belonging to imported tablespaces were not being tracked. (Bug #29917343)
InnoDB: Renaming of full-text search auxiliary tables during upgrade failed due to a tablespace name conflict when upgrading from MySQL 5.7 to MySQL 8.0 on a case-insensitive file system. (Bug #29906115)
InnoDB: Rollback of an
INSERToperation that inserted a LOB value larger than a buffer pool caused a deadlock. (Bug #29846292, Bug #95572)
InnoDB: A code regression was addressed by prohibiting unnecessary implicit to explicit secondary index lock conversions for session temporary tables. (Bug #29718243)
InnoDB: A tablespace import operation raised an assertion when the cursor was positioned on a corrupted page while purging delete-marked records. Instead of asserting when encountering a corrupted page, the import operation is now terminated and an error is reported. (Bug #29454828, Bug #94541)
InnoDB: Delete marked rows were able to acquire an external read lock before a partial rollback was completed. The external read lock prevented conversion of an implicit lock to an explicit lock during the partial rollback, causing an assertion failure. (Bug #29195848)
InnoDB: Throughput stalled under a heavy workload with a small
innodb_io_capacity_maxsetting, a single page cleaner thread, and multiple buffer pool instances. (Bug #29029294)
InnoDB: After a server exit that occurred while an undo tablespace truncation operation was in progress, warning messages were printed at startup stating that doublewrite pages could not be restored for undo tablespace pages. The warning messages are no longer printed for undo tablespaces that are being truncated. (Bug #28590016)
Partitioning: When upgrading a database with a subpartitioned table from MySQL 8.0.16 or lower and then executing
ALTER TABLE ADD COLUMN, an assertion or error would occur. (Bug #30360695, Bug #97054)
Partitioning: During upgrade of partitioned tables from MySQL 5.7 to 8.0, when a prefix key was used by the partitioning function, the prefix length was ignored, and the full column length was considered instead. Consequently, the table might incorrectly be rejected from being upgraded because its partition field length was found to be too large. (Bug #29941988, Bug #95921)
ALTER TABLE ... EXCHANGE PARTITIONcould cause indexes to become corrupted. This was due to the fact that the server assumed that the order in which an index is created in a partitioned table is the same as that of the table which is not partitioned. This led to the wrong index data being exchanged. (Bug #29706669)
Replication: When a member is joining or rejoining a replication group, if Group Replication detects an error in the distributed recovery process (during which the joining member receives state transfer from an existing online member), it automatically switches over to a new donor, and retries the state transfer. The number of times the joining member retries before giving up is set by the
group_replication_recovery_retry_countsystem variable. The Performance Schema table
replication_applier_status_by_workerdisplays the error that caused the last retry. Previously, this error was only shown if the group member was configured with parallel replication applier threads (as set by the
slave_parallel_workerssystem variable). If the group member was configured with a single applier thread, the error was cleared after each retry by an internal
RESET SLAVEoperation, so it could not be viewed. This was also the case for the output of the
SHOW SLAVE STATUScommand whether there were single or multiple applier threads. The
RESET SLAVEoperation is now no longer carried out after retrying distributed recovery, so the error that caused the last retry can always be viewed. (Bug #30517160, Bug #30517172, Bug #97540)
Replication: An assertion was raised when privilege checks were carried out for a replication channel if the slave had more columns in the relevant table than the master. The check now references the number of columns in the event, rather than in the table definition. (Bug #30343310)
Replication: When a replication group member leaves a group, either because
STOP GROUP_REPLICATIONwas issued or due to an error, Group Replication now stops the binary log dump thread so that the former group member cannot send unwanted binary log data to the members that have remained in the group. (Bug #30315614)
Replication: Replication connection parameters that are held in the
mysql.slave_relay_log_infotable are now preserved in the event of a server crash or deliberate restart after issuing
RESET SLAVEbut before issuing
START SLAVE. This action applies to the
PRIVILEGE_CHECKS_USERaccount setting for replication privilege checks (introduced in MySQL 8.0.18) and the
REQUIRE_ROW_FORMATsetting (introduced in MySQL 8.0.19). Note that if
relay_log_info_repository=FILEis set on the server (which is not the default and is deprecated), replication connection parameters are not preserved in this situation. (Bug #30311908)
Replication: When a replication channel is secured by specifying a
PRIVILEGE_CHECKS_USERaccount, which should not have ACL privileges, a
GRANTstatement that is replicated to the channel causes the replication applier to stop. In this situation, the behavior was correct but an assertion was being raised. The assertion has now been removed. (Bug #30273684)
Replication: When Group Replication was started following either provisioning with a cloning operation, execution of
RESET MASTER, or removal of a partial transaction from the relay log,
RESET SLAVE ALLwas used internally to clear any unwanted state on the server. However, in MySQL 8.0.18, this caused any
PRIVILEGE_CHECKS_USERaccount that was specified for a Group Replication channel to be removed.
RESET SLAVEis now used instead, which does not remove the account. (Bug #30262225)
Replication: For multithreaded replication slaves, setting
slave_preserve_commit_order=1now preserves the order of statements with an
IF EXISTSclause when the object concerned does not exist. Previously, these updates might have committed before transactions that preceded them in the relay log, which might have resulted in gaps in the sequence of transactions that have been executed from the slave's relay log. (Bug #30262096)
Replication: When privilege checks were carried out for a replication channel, the permissions required for setting the session value of the
sql_require_primary_keysystem variable were not being checked. The check is now carried out. (Bug #30254917)
Replication: A memory leak could occur when a failed replication group member tried to rejoin a minority group and was disallowed from doing so. (Bug #30162547, Bug #96471)
Replication: When a group member rejoins a replication group, it begins the distributed recovery process by checking the relay log for its
group_replication_applierchannel for any transactions that it already received from the group, and applying these. The joining member then initiates state transfer from an existing online member, which might begin with a remote cloning operation. Previously, the
group_replication_applierchannel was not explicitly stopped when a remote cloning operation was started, so it was possible that the applier might still be applying existing transactions at that time, which might lead to errors. The
group_replication_applierchannel is now stopped before a remote cloning operation is requested, and restarted when the distributed recovery process moves on to state transfer from a donor's binary log. (Bug #30152028, Bug #96447)
STOP GROUP_REPLICATIONwas issued while the member's XCom port was blocked, the XCom thread hung and the shutdown did not complete. XCom is now terminated in this situation. (Bug #30139794)
Replication: When Group Replication is running in single-primary mode, and a new primary server is elected, the messages logged at this time now provide the newly elected primary server's
gtid_executedset, and the set of GTIDs retrieved by the replication applier. (Bug #30049310)
Replication: The slave status logs
mysql.slave_relay_log_info(the relay log info log) and
mysql.slave_worker_info(the slave worker log) are now copied from the donor to the recipient during a local or remote cloning operation. The slave status logs hold information that can be used to resume replication correctly after the cloning operation, including the relay log position from which to restart replication, the
PRIVILEGE_CHECKS_USERaccount setting, and the new
REQUIRE_ROW_FORMATsetting. Note that the relay logs themselves are not copied from the donor to the recipient, only the information about them that is held in these tables. Also note that if
relay_log_info_repository=FILEis set on the server (which is not the default and is deprecated), the slave status logs are not cloned; they are only cloned if
Before this patch, the following replication-related behaviors occurred on a replication slave that had been provisioned by a cloning operation:
The default replication channel would fail to start if it was the only channel on the slave, because it was considered to be not initialized due to the missing relay log information.
PRIVILEGE_CHECKS_USERaccount setting that had been applied to replication channels on the donor was absent and had to be respecified.
Replication channels that used GTID auto-positioning (as specified by the
MASTER_AUTO_POSITIONoption on the
CHANGE MASTER TOstatement) were able to resume replication automatically.
Replication channels that used binary log file position based replication (as specified by the
MASTER_LOG_POSoptions on the
CHANGE MASTER TOstatement) had to have the
MASTER_LOG_POSoptions reapplied manually before restarting replication in order to resume correctly. If the channels were configured to start replication automatically at server startup, without the options reapplied they would attempt to start replication from the beginning. They were therefore likely to attempt to replicate data that had already been copied to the slave by the cloning operation, causing replication to stop and possibly corrupting the data on the slave.
With this patch, the following replication-related behaviors now occur on a replication slave that has been provisioned by a cloning operation:
The default replication channel can now always start after the cloning operation if it is configured to do so.
All channels now have the donor's
PRIVILEGE_CHECKS_USERaccount setting and
Replication channels that use GTID auto-positioning (as specified by the
MASTER_AUTO_POSITIONoption on the
CHANGE MASTER TOstatement) are still able to resume replication automatically. For Group Replication channels, which use GTID auto-positioning, an internal equivalent of the
RESET MASTERstatement is now used to ensure that replication resumes optimally.
Replication channels that use binary log file position based replication now have the correct
MASTER_LOG_POSoptions in place after cloning. Because the relay logs themselves are not cloned, these channels now attempt to carry out the relay log recovery process, using the cloned relay log information, before restarting replication. For a single-threaded slave (
slave_parallel_workersis set to 0), relay log recovery should succeed in the absence of any other issues, enabling the channel to resume replication correctly. For a multithreaded slave (
slave_parallel_workersis greater than 0), relay log recovery is likely to fail because it cannot usually be completed automatically, but an informative error message is issued, and the data will not be corrupted.
(Bug #29995256, Bug #30510766)
Replication: An internal deadlock could occur on a multi-threaded replication slave when the
relay_log_space_limitsystem variable was set to limit the size of relay logs on the slave, and the coordinator thread acquired locks related to this limit and to the end position of the log. (Bug #29842426)
Replication: If a replication group member stops unexpectedly and is immediately restarted (for example, because it was started with
mysqld_safe), it automatically attempts to rejoin the group if
group_replication_start_on_boot=onis set. Previously, if the restart and rejoin attempt took place before the member's previous incarnation had been expelled from the group, the member could not rejoin. Now in this scenario, Group Replication automatically uses a Group Communication System (GCS) feature to retry the rejoin attempt for the member 10 times, with a 5-second interval between each retry. This should cover most cases and allow enough time for the previous incarnation to be expelled from the group, letting the member rejoin. Note that if the
group_replication_member_expel_timeoutsystem variable is set to specify a longer waiting period before the member is expelled, the automatic rejoin attempts might still not succeed. (Bug #29801773)
Replication: If a replication slave was set up using a
CHANGE MASTER TOstatement that did not specify the master log file name and master log position, then shut down before
START SLAVEwas issued, then restarted with the option
--relay-log-recoveryset, replication did not start. This happened because the receiver thread had not been started before relay log recovery was attempted, so no log rotation event was available in the relay log to provide the master log file name and master log position. In this situation, the slave now skips relay log recovery and logs a warning, then proceeds to start replication. (Bug #28996606, Bug #93397)
macOS: On macOS, configuring MySQL with
-DWITH_SSL=systemcaused mysql_config output to incorrectly include internal CMake names for the static SSL libraries. (Bug #30541879, Bug #97632)
macOS: Builds on macOS with Ninja could fail with an error trying to create a symbolic link multiple times. (Bug #30368985)
Microsoft Windows; JSON: On Windows platforms, memory used for a multi-valued index was not released after the table containing it was dropped. (Bug #30227756)
Microsoft Windows: On Windows,
-DWITH_SSL=systemfailed to find the installed OpenSSL headers if Strawberry Perl was installed. (Bug #30359287)
Microsoft Windows: On Windows, the
-DWITH_SSL=systemoption did not work if the path name leading to the system OpenSSL libraries contained a space. This is now handled. Also,
-DWITH_SSL=yesis treated like
-DWITH_SSL=system, as on other platforms. (Bug #30261942, Bug #96739)
Microsoft Windows: MSVC 2019 produced garbled source file names for compilation errors. A workaround in the
CMakeconfiguration was implemented to correct for this. (Bug #30255096, Bug #96720)
JSON: Updating a value in a
JSONcolumn by replacing a character string element with a binary string containing the same byte sequence as the
utf8mb4representation of the character string had no effect.
The root cause of this issue was a change in the behavior of comparisons between JSON strings and JSON opaque values introduced by the implementation of multi-valued indexes in MySQL 8.0.17, previous to which, JSON strings and JSON opaque values were never considered equal. After the change, they were considered equal if their binary data matched.
An analysis of this change showed that it was not needed; in addition, the new behavior conflicted with the existing documentation for comparisons of JSON values. This issue is fixed by restoring the original behavior. (Bug #30348554)
JSON: A view that used
JSON_TABLE()did not preserve the character set in which JSON path arguments were encoded. This meant that, if the view was evaluated with a different character set in effect from the one in which it was defined, it could produce wrong results. This is fixed by ensuring that
JSON_TABLE()preserves the original character set in such cases. (Bug #30310265)
JSON: Adding a functional index on a JSON column changed the collation used for comparing strings, causing the result returned by the same query selecting the column to differ from that obtained without the index. (Bug #29723353)
JSON: If the first argument to
constduring the execution of a stored procedure, but not during preparation, it was not re-evaluated when a statement was subsequently executed again, causing an empty result to be returned each time following the first execution of the procedure. (Bug #97097, Bug #30382156)
JSON: In some cases, such as when a query uses
FORCE INDEX, the cost of reading the table is
DBL_MAX; this was rounded up to
2e308when printed, which is too large for the JSON parser, so that it was not possible to extract parts of the optimizer trace using a query such as
SELECT JSON_EXTRACT(trace, '$**.table_scan') FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE. Now in such cases, values greater than
1.5e308are rounded down and printed as
1e308instead. (Bug #96751, Bug #30226767)
After upgrading from MySQL 5.7 to MySQL 8.0, a
CLONE INSTANCEoperation failed with the following error: ERROR 3862 (HY000): Clone Donor Error: 1016 : Can't open file: './undo001'. The upgrade process left behind orphaned in-memory undo tablespaces.
Thanks to Satya Bodapati for the contribution. (Bug #30602218, Bug #97784, Bug #30239255, Bug #96637)
thread_poolplugin used display widths in definitions for integer columns of Performance Schema tables. This resulted in warnings written to the error log because integer column display widths are now deprecated. (Bug #30597673)
The MySQL optimizer's hash join algorithm uses the join buffer to store intermediate results. If this buffer overflows, the server uses a spill-to-disk algorithm, which writes one of the hash join operands to a temporary file, to handle this gracefully. If one of the operands was a table that was a member of a pushed join operation, this strategy conflicted with the pushed join requirement for all child result rows to use nested-loop reads whenever one of their pushed join ancestors was the current row in the join evaluation, which could in some cases result in incorrect query results being returned. (Bug #30573733)
Access to the
INFORMATION_SCHEMA.VIEWStable was not properly restricted to the correct user. (Bug #30542333)
When creating hash values used for lookups during a hash join, the server did not respect the
PAD SPACEattribute, meaning that
'foo 'did not match when using a
PAD SPACEcollation. This is fixed by padding all strings up to the same length as the longest possible string, where the longest possible string is deduced from the data type length specifier
VARCHAR(. (Bug #30535541)
When retrieving large result sets containing
DECIMALcolumns from a secondary engine, conversion of the column values to strings for transport over the text protocol acted as a bottleneck. The performance of the functions responsible for such conversions has been improved in some cases by as much as 50%, as reflected in internal testing. (Bug #30528427)
FORMAT_PICO_TIME()function was invoked to process several rows, once a
NULLargument was found in a row, every result after that was set to
NULL. (Bug #30525561)
When a Performance Schema event was timed, the event duration reported in
events_tables could be
NULLinstead of 0 for events where the timer start and end values are equal. (Bug #30525560)
LIMITclause to a parenthesized query suppressed locking clauses within the parentheses. For example, this query would not lock the table:
(SELECT ... FOR UPDATE) LIMIT ...;
LIMITclause outside of a parenthesized query is intended to override a
LIMITclause within the parentheses. However, the outer
ORDER BYwithin the parentheses as well. For example, for this query, the
ORDER BYwas suppressed:
(SELECT ... ORDER BY ... LIMIT a) LIMIT b;
Now inner locking and
ORDER BYclauses are not suppressed by an outer
LIMITclause. (Bug #30521098, Bug #30521803)
When optimizer extracts conditions on constant tables for early evaluation, it does not include
WHEREconditions that are expensive to evaluate, including conditions involving stored functions. When the extracted condition evaluated to true because it involved only const tables, the entire
WHEREcondition was incorrectly removed. Now in such cases, a check for expensive conditions is performed prior to any removal of the
WHEREcondition. (Bug #30520714)
When a lateral materialized derived table used
DISTINCT, the derived table was not rematerialized for each outer row as expected. (Bug #30515233)
EXPLAIN ANALYZEdid not work correctly with a common table expression using
WITH RECURSIVE. (Bug #30509580)
The GNU gold loader could cause memory exhaustion on some platforms. Now it is used by default only on Intel 64-bit platforms. (Bug #30504760, Bug #96698)
Some Linux platforms experienced high overhead with
EXPLAIN ANALYZEdue to use of a system call by
clock_gettime(). (Bug #30483025)
On Solaris 11.4, the LDAP authentication plugins could not be built. (Bug #30482553)
Queries that used the
MEMBER OF()operator were not always handled correctly. (Bug #30477993)
Boost compilation failed under Visual Studio due to a Boost workaround for a VC++ 2013 bug that has since been fixed. The workaround is now patched for Boost compilation with MySQL. (Bug #30474056, Bug #97391)
When retrieving large result sets containing many integers from a secondary engine, conversion of the integers to strings for sending over the text protocol could act as a bottleneck. To avoid this problem, the performance of internal functions performing such conversions has been improved. (Bug #30472888)
Docker packages were missing the LDAP authentication plugins. (Bug #30465247)
Corrected a typo in a
mysys/my_handler_errors.herror message. Thanks to Nikolai Kostrigin for the contribution. (Bug #30462329, Bug #97361)
A GTID table update while
innodb_force_recoverywas enabled caused a debug assertion failure. (Bug #30449531, Bug #97312)
MySQL failed to compile against Protobuf 3.10. (Bug #30428543, Bug #97246)
Buffered log lines during system startup could be lost. (Bug #30422941, Bug #97225)
mysql.usersystem table was renamed, the server could exit. (Bug #30418070)
Revoking a role specified with no host name could cause a server exit. (Bug #30416389)
When determining whether to pull out a semijoin table when other tables inside the semijoin depended on this table, only those semijoin tables which were base tables were considered; those in nested joins were ignored. (Bug #30406241)
References: See also: Bug #12714094, Bug #11752543, Bug #43768.
The AppArmor profile on Ubuntu platforms was not able to read the OpenSSL configuration. (Bug #30375723)
Some Fedora 30 packages had missing obsoletes information that could cause problems upgrading an existing MySQL installation. (Bug #30348549, Bug #96969)
Altering only the default encryption in an
ALTER SCHEMAstatement caused the schema default character set and collation to be reset to the system defaults. (Bug #30344462, Bug #96994)
Columns declared with both
DEFAULTvalue expressions (a nonpermitted combination) could raise an assertion or cause a server exit. (Bug #30331053)
SHOW GRANTSfor an anonymous user could result in a server exit under some conditions. (Bug #30329114)
References: This issue is a regression of: Bug #25123839.
The list of subpartitions in partition objects was not serialized and therefore not included in serialized dictionary information (SDI). To address this issue, support was added for serialization and deserialization of subpartition dictionary information. The patch for this bug also includes minor SDI code refactoring and format changes. Due to the format changes, the SDI version number was incremented. (Bug #30326020, Bug #96943)
Following execution of
ANALYZE TABLE, the optimizer trace for a given query differed when another query was executed previously to it, but also after the
ANALYZE TABLE. (Bug #30321546)
max_allowed_packetvalue caused the following error: ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes. The error message was revised to indicate the minimum required
max_allowed_packetvalue for cloning operations. (Bug #30315486, Bug #96891)
An assertion could be raised when server code tried to send to clients an error code intended to be written to the error log. These instances are fixed by sending a code intended to be sent to clients. (Bug #30312874)
CREATE VIEWdid not always succeed when the body of the view definition contained a join and multiple subselects. (Bug #30309982)
References: This issue is a regression of: Bug #25466100.
Dependency information for SLES 12 RPM packages was incorrect, causing MySQL installation failure. (Bug #30308305)
GEOMETRYdata from hash join chunk files to a
GEOMETRYcolumn, the server did not copy the data to the column, but instead stored a pointer to the data, which resided in a temporary buffer, meaning that the
GEOMETRYcolumn pointed to random data as soon as this buffer was reused. Now, the server always copies the data from this buffer into the
GEOMETRYcolumn when executing a hash join. (Bug #30306279)
ALTER TABLEoperations using the
COPYalgorithm did not handle columns with expression default values properly. (Bug #30302907, Bug #96864)
CONV()function did not always handle returning the proper number of characters correctly. (Bug #30301543)
Parser recursion checks were insufficient to prevent stack overflow. (Bug #30299881)
The removal of a subquery because the condition in which it occurred was always false was expected to be performed during resolution, but when the subquery did not involve any tables, the server executed it while resolving it. This resulted in the failure of a subsequent check to confirm that the subquery was only being resolved and not yet optimized. Now in such cases, the server also checks to see whether the subquery was already executed. (Bug #30273827)
For debug builds, attempts to add to an empty temporary table a column with an expression default that was not valid raised an assertion. (Bug #30271792)
Construction of the iterator tree may yield a non-hierarchical structure; this can happen when, for example,
a LEFT JOIN b LEFT JOIN calso make up the right side of a semijoin. The iterator executor solves this by adding a weedout on top of the entire query, which means that is is also necessary to iterators interacting with row IDs that they need to store and restore them. This was not done in all such cases, causing wrong results. Now the addition of a top-level weedout is always communicated to the iterators as soon as it is known that this is being done, before any affected iterators are constructed. (Bug #30267889)
Foreign key-handling code duplication between the SQL layer and the data dictionary was eliminated. A side effect is that some error messages now are more informative and clear. (Bug #30267236, Bug #96765)
During startup, the server could handle incorrect option values for persisted variables improperly, resulting in a server exit. (Bug #30263773)
In some queries involving materialized semijoins, when using the iterator executor, conditions were evaluated outside the materialization, causing inefficient query plans to be used and sometimes also producing wrong results. (Bug #30250091)
ALTER TABLEstatements that renamed a column used in
CHECKconstraints could result in an incorrect error message. (Bug #30239721)
INTOclause prior to a locking clause is legal but the parser rejected it. (Bug #30237291, Bug #96677)
FLUSH TABLES WITH READ LOCKcaused a deadlock when a
LOCK INSTANCE FOR BACKUPstatement was previously executed within the same session and there was a concurrent
ALTER DATABASEstatement running in another session against the same database specified (implicitly or explicitly) for the
FLUSH TABLES WITH READ LOCKstatement. (Bug #30226264)
Slow query logging could result in a server exit for connections that did not use the classic client/server protocol. (Bug #30221187)
A statement that added a foreign key without an explicit name failed when re-executed as a prepared statement or in a stored program with an unwarranted duplicate foreign key name error. (Bug #30214965, Bug #96611)
References: This issue is a regression of: Bug #30171959.
With multiple sessions executing concurrent
INSERT ... ON DUPLICATE KEY UPDATEstatements into a table with an
AUTO_INCREMENTcolumn but not specifying the
AUTO_INCREMENTvalue, inserts could fail with a unique index violation. (Bug #30194841, Bug #96578)
Client programs could load authentication plugins from outside the plugin library. (Bug #30191834, Bug #30644258)
When switching between table scans and index lookups,
AlternativeIteratordid not reset the handler, which could lead to assertion failures. (Bug #30191394)
open_files_limitto a large value, or setting it when the operating system rlimit had a value that was large but not equal to
RLIM_INFcould cause the server to run out of memory. As part of this fix, the server now caps the effective
open_files_limitvalue to the the maximum unsigned integer value. (Bug #30183865, Bug #96525)
References to fully qualified
INFORMATION_SCHEMAtables could fail depending on the lettercase in which
INFORMATION_SCHEMAwas specified. (Bug #30158484)
Slow queries with an execution time greater than 35 days could cause corruption of the
mysql.slow_logsystem table requiring a
REPAIR TABLEoperation. (Bug #30113119, Bug #96373)
MySQL did not support sending systemd notification messages to a socket specified using the
NOTIFY_SOCKETenvironment variable, if the variable named an abstract namespace socket. (Bug #30102279)
SET PERSIST_ONLYto set a boolean system variable to a numeric value resulted in the server being unable to restart. (Bug #30094645, Bug #30298191, Bug #96848)
A fix for a previous issue combined two
TABLE_LISTconstructors in an unfortunate way. One of these created a
TABLE_LISTobject from a
TABLEobject representing a temporary table. Previously, the table name was made the same as the alias; this was changed to copying the name from the
TABLEobject. Due to the fact that, for a temporary table, the table name is a file path, it was possible to exceed the limit for
MDL_keynames, leading to a failed assertion. Fixed by reintroducing dedicated constructors which behave in the manner that they did prior to the fix. (Bug #30083125)
References: This issue is a regression of: Bug #27482976.
UNIX_TIMESTAMP()errors occurring within stored functions, the number of fractional seconds for subsequent function invocations could be incorrect. (Bug #30034972, Bug #96166)
When a common table expression contained a nondeterministic expression (such one that used
RAND()) and the common table expression was referenced more than once in the outer query, it was merged in some cases. This caused the common table expression to return a different result for each reference. Now in such cases, the common table expression is not merged, but rather is materialized instead. (Bug #30026353)
In debug build of MySQL started on Linux with a
lower_case_table_names=1setting, discarding a tablespace for a partitioned table after an in-place upgrade from MySQL 8.0.16 caused a serious error. The partition tablespace name stored in the data dictionary was invalid, and the metadata lock key prepared for the partition tablespace in MySQL 8.0.17 did not match the key stored in the
mysql.tablespacestable. (Bug #30024653)
KILL QUERYcould kill the statement subsequent to the one intended. (Bug #29969769)
The error message reported when attempting to upgrade tables with invalid expressions for generated columns did not provided sufficient information. The error message now includes the generated column name and the expression used to create the generated column. (Bug #29941887, Bug #95918)
Attempting to display an unresolvable view could result in a server exit rather than an error. (Bug #29939279)
Incorrect checking of temporal literals for
CREATE TABLEstatements could lead to a server exit. (Bug #29906966, Bug #95794)
Writing unexpected values to the
mysql.global_grantssystem table could cause a server exit. (Bug #29873343)
LAST_EXECUTEDvalue in the
INFORMATION_SCHEMA.EVENTStable was incorrectly reported in UTC, not in the event time zone. (Bug #29871530, Bug #95649)
keyring_encrypted_file_passwordset on the command line at server startup, the password value could be visible to system utilities. (Bug #29848634)
lower_case_table_namessetting when upgrading from MySQL 5.7 to MySQL 8.0 could cause a failure due to a schema or table name lettercase mismatch. If
lower_case_table_names=1, table and schema names are now checked by the upgrade process to ensure that all characters are lowercase. If table or schema names are found to contain uppercase characters, the upgrade process fails with an error. For related information, see Preparing Your Installation for Upgrade. (Bug #29842749, Bug #95559)
Attempting to spawn a thread for a parallel read operation while system resources were temporary unavailable raised system error. (Bug #29842749, Bug #95559)
LOCK TABLESstatement in effect, a metadata change for the locked table could cause Performance Schema or
SHOWqueries for session variables to hang in the
opening_tablesstate. (Bug #29836204, Bug #92387)
WHEREcondition of the form
resulting in an impossible range led to an unplanned exit of the server. (Bug #29770705)
For JSON-format audit logging, the
idfield now may contain values larger than 65535. Previously, with heaving logging activity, more than 65536 queries per second could be executed, exceeding the 16 bits permitted for
idvalues. (Bug #29661920)
An incomplete connection packet could cause clients not to properly initialize the authentication plugin name. (Bug #29630767)
Out-of-memory errors from the parser could be ignored, resulting in a server exit. (Bug #29614521)
On Linux, an assertion could be raised when the Performance Schema file instrumentation was disabled and re-enabled. (Bug #29607570)
For a column defined as a
PRIMARY KEYin a
CREATE TABLEstatement, a default value given as an expression was ignored. (Bug #29596969, Bug #94668)
TABLE_ENCRYPTION_ADMINprivilege, added in MySQL 8.0.16, was incorrectly granted to the system-defined
mysql.sessionuser during upgrade. (Bug #29596053, Bug #94888)
For connections encrypted with OpenSSL, network I/O at the socket level was not reported by the Performance Schema. Also, network I/O performed while the server was in an
IDLEstate was not reported by the Performance Schema. (Bug #29205129, Bug #30535558, Bug #97600)
When a query used a subquery that was merged into the outer query block (due to a semijoin transformation or merge of a derived table), and the subquery itself contained a subquery with an aggregate function with an aggregation query block that differed from its base query block, the query could sometimes fail to return any rows unless executed a second time or preceded with
FLUSH TABLES. This was because, when merging, the information regarded tables used and the aggregation information for the aggregate function was not updated properly. In the case which raised this bug report, this meant that the comparison operation containing a scalar subquery was regarded as const-for-execution and therefore the range optimizer attempted to evaluate it, and the scalar subquery contained a
MIN()function referring to an outer reference which had not yet been read. Thus, when the aggregator object was populated, it was based on uninitialized data, leading to unpredictable results. (Bug #28941154)
keyring_awsinitialization caused failure of SSL socket initialization. (Bug #28591098)
For slow query logging, the
Slow_querieswas not implemented unless the slow query log was enabled, contrary to the documentation. (Bug #28268680, Bug #91496)
GROUP BYplan is improved so that every gap attribute is allowed to have a disjunction of equality predicates. Predicates from different attributes must still be conjunctive to each other in order to take advantage of this enhancement.
Our thanks to Facebook for this contribution. (Bug #28056998, Bug #15947433)
mysqlpump exits rather than dumping databases that contain an invalid view, by design, but it also failed if an invalid view existed but was not in any of the databases to be dumped. (Bug #27096081)
Foreign key information is now retrieved from the data dictionary, not from
InnoDB. (Bug #25583288)
Foreign key definitions used in
ALTER TABLEstatements for
InnoDBtables were ignored if the statements were wrapped in conditional comments (such as
/*!50101 ... */or
/*! ... */). (Bug #21919887, Bug #78631)
--log-rawoption is now available at runtime as the
log_rawsystem variable. The system variable is set at startup to the option value, and may be set at runtime to change password masking behavior. (Bug #16636373, Bug #68936)
An inner scalar subquery containing an outer reference did not return the same result using a nested set of
SELECTexpressions on the right hand side as when using a single
SELECTthat was equivalent. (Bug #97063, Bug #30381092)
A materialized subquery could yield different results depending on whether it used an index. (Bug #96823, Bug #30289052)
When a query terminated due to exceeding the time specified using the
MAX_EXECUTION_TIMEhint, the error produced differed depending on the stage of the query. In particular, if the query terminated during a filesort, the error raised was
ER_FILSORT_ABORT, even though in such cases the query should always exit with
ER_QUERY_TIMEOUT. This made it unnecessarily difficult to trap such errors and to handle them correctly.
This fix removes the error codes
ER_FILESORT_TERMINATED. (Bug #96537, Bug #30186874)
If a stored procedure had a parameter named member or array, and it had been defined without quoting the parameter names, the database in which it was defined could not be upgraded to 8.0.17 or 8.0.18. (Bug #96288, Bug #30084237)
References: See also: Bug #96350, Bug #30103640.
Our thanks to Oleksandr Peresypkin for this contribution. (Bug #95954, Bug #29952066)
Select tables optimized awayfor a query using
MAX()on an indexed column, but if
MAX()on the same column was called in a user function, it showed
Using indexinstead. (Bug #94862, Bug #29596977)