MySQL (64-bit)

What's new in this version:

Fixed:
- InnoDB: A GROUP BY operation on a JSON array column caused failures in an UBSan build of MySQL due to incorrect type casting
- InnoDB: Several InnoDB error log messages were defined without symbolic values
- InnoDB: The file segment for a single page write was not released after a data file write failure associated with a doublewrite flush and sync operation
- InnoDB: Code that was accessed when undo tablespace truncation used the same space ID before and after a truncate operation was removed. That scenario no longer occurs. The truncated undo tablespace is replaced by a new undo tablespace datafile with a different space ID
- InnoDB: The range of reserved space IDs for undo tablespaces was increased from 512 per undo tablespace to 400000
- InnoDB: An error that occurred while inserting a log into the ddl_log table was not returned making it appear as though the operation was successful, and a transaction was not registered while performing a tablespace encryption operation
- InnoDB: The lob::purge() function did not free LOBs correctly for an undo log record type (TRX_UNDO_UPD_DEL_REC) that is generated when an insert operation modifies a delete-marked record
- InnoDB: A shutdown error occurred following an attempt to rebuild a discarded partition
- InnoDB: The internal get_real_path() function, responsible for retrieving directory or a file path, was modified to strip trailing separators before determining if a path is a file or directory. Additionally, if a path does not exist or cannot be identified as a file or subdirectory, the function now assumes the path is a file if the basename has a three letter suffix
- InnoDB: Tablespace-related error messages were revised
- InnoDB: To avoid potential compilation issues, __attribute__((const)) and __attribute__((pure)) attributes were removed from internal InnoDB functions
- InnoDB: The parallel read thread limit was not observed when spawning read threads for histogram sampling, causing an assertion failure
- InnoDB: The transaction read view was not checked when sampling records for generation of histogram statistics
- InnoDB: An I/O completion routine was not able acquire an LRU list mutex due to a latch held by another thread
- InnoDB: An attachable transaction thread requested an InnoDB ticket that was already reserved by the main thread, causing a deadlock. Additionally, the server failed to respond to KILL statements in this deadlock scenario
- InnoDB: The INNODB_METRICS table AVG_COUNT_RESET value for a counter defined as a module owner reported NULL. The METRIC_AVG_VALUE_RESET field was incorrectly marked as NULL
- Thanks to Fungo Wang for the contribution
- InnoDB: At startup, following an unexpected stoppage during an undo tablespace truncate operation, some rollback segment header pages were found to be corrupted. Encryption of rollback segment header pages was initiated while the header pages were being written, resulting in some header pages not being encrypted, as expected
- InnoDB: Various aspects of the lock system (lock_sys) code were refactored, and issues with lock_sys lock_rec_block_validate() and lock_test_prdt_pacge_lock() functions were fixed. The lock_rec_block_validate() function called another function repeatedly, which could result in locks not being validated under certain circumstances. The implementation also had a potential quadratic time complexity. The lock_test_prdt_page_lock() function did not iterate over all locks as intended
- InnoDB: Use of memory-mapped files after exceeding the temptable_max_ram threshold caused a performance degradation
- InnoDB: In debug mode, a DROP TABLE operation on a table with an incorrectly defined COMPRESSION clause caused a failure. InnoDB did not return an error to the caller for proper handling
- InnoDB: Purge thread activity was excessive when the history list length approached zero, wasting CPU resource and causing mutex contention
- InnoDB: A regression introduced in MySQL 8.0.18 affected INFORMATION_SCHEMA.INNODB_COLUMNS query performance. Schema and table data dictionary objects were fetched repeatedly to retrieve partition column information
- InnoDB: An ALTER TABLE ... IMPORT TABLESPACE operation with a .cfg file failed with an “Incorrect key file for table” error. The row_import::m_flags member was not initialized
- InnoDB: A DROP TABLE operation performed after discarding a partition did not remove the associated data files, and DROP DATABASE failed with an error indicating that the database directory could not be removed. Upgrade from MySQL 5.7 to MySQL 8.0 also failed if a partitioned table with a discarded partition was present. The DISCARD attribute was applied to the table object instead of the partition object in the data dictionary, which made it appear that all partitions were discarded
- InnoDB: The server failed intermittently with an “ibuf cursor restoration fails” error
- InnoDB: An ALTER TABLE operation that copied data from one table to another returned an “Out of range value for column” error. The counter that tracks the number of AUTO_INCREMENT rows required for a multi-row insert operation was not always set back to zero after a bulk insert operation
- InnoDB: The internal TempTable records_in_range() handler function contained a DBUG_ABORT() call that caused assertion failures in debug builds, and empty result sets in regular builds for some queries
- InnoDB: The btr_cur_pessimistic_update() function failed to handle a cursor position change caused by a lob::purge() call
- InnoDB: A type conversion failure during a DELETE IGNORE operation caused an assertion failure. A JSON value was not converted to the expected value
- InnoDB: A purge operation encountered a null LOB reference, causing an assertion failure
- InnoDB: Chunk size was not calculated correctly when deallocating memory from the TempTable storage engine, causing a regression in SELECT DISTINCT query performance
- InnoDB: A segmentation fault occurred in the TempTable storage engine while using the thread pool plugin. TempTable thread-local variables were not compatible with the use of different threads for statements issued by a single client connection. Use of thread local variables also lead to excessive memory consumption due to the memory used by thread-local variables remaining allocated for the life of the thread. To address these issues, thread-local variables were replaced by a caching mechanism
- InnoDB: A fatal “page still fixed or dirty” error occurred during shutdown
- Partitioning: A query against a partitioned table, which used an ORDER BY, returned unordered results under the following conditions:
- The table had a composite index with a prefix on one of the columns
- The query's WHERE clause contained an equality condition on the prefixed column
- The column with the prefix was the leftmost column in the index
- The column used in the ORDER BY was the rightmost column in the index
- The index was used for handling the ORDER BY
- Replication: A global value that is set for the group_replication_consistency system variable, which controls all user connections, is applied on Group Replication's internal connections to MySQL Server modules using the SQL API, which are handled in a similar way to user connections. This could sometimes lead to Group Replication reporting the use of group_replication_consistency as an error, for example when checking the clone plugin status during distributed recovery. Group Replication's internal connections using the SQL API are now configured to use the consistency level EVENTUAL, which matches the behavior before the group_replication_consistency option was available, and does not cause an error message
- Replication: If a group's consistency level (set by the group_replication_consistency system variable) was set to BEFORE or BEFORE_AND_AFTER, it was possible for a deadlock to occur in the event of a primary failover. The primary failover is now registered differently to avoid this situation
- Replication: On Windows, Group Replication's use of the Windows API function SleepConditionVariableCS to wait for new write events caused noticeably high CPU usage by this function after Group Replication had been running for two days or more, which could be corrected by restarting the MySQL server instance, but then increased again over time as before. This was caused by the use of two clock functions to calculate the timeout after which the SleepConditionVariableCS function was called, which drifted relative to each other over time, making the timeout progressively shorter and the calls to the function more frequent. The issue has been corrected on Windows by using the current time from a single clock to calculate the timeout
- Replication: If Group Replication was stopped while distributed recovery was in progress, memory issues could result from an attempt to access the record of the member that was selected as the donor. This record is now kept locally with the distributed recovery state
- Replication: When distributed recovery for Group Replication involves a remote cloning operation, the flag set on the server to indicate this remains set until the server instance is restarted. Previously, if Group Replication was stopped and restarted on the server, that flag caused Group Replication to purge the relay log files for the group_replication_applier channel, as is required on starting after a remote cloning operation to ensure that there is no mismatch with the cloned data tables. If there were any unapplied transactions in the purged relay log files, the member could not subsequently be used to bootstrap a group, although it could successfully join a group by retrieving the transactions from another member. Group Replication now ignores the flag on its second or subsequent starts, and only purges the relay log files the first time it is started after a remote cloning operation
- Replication: To avoid the possibility of data inconsistency, Group Replication blocks a new incarnation of the same server (with the same address but a new identifier) from joining the group while its old incarnation is still listed as a member. Previously, Group Replication's Group Communication System (GCS) treated the connection to the old incarnation of a server as active while it was attempting to send messages to the server, and only recognized that the connection was inactive when the socket returned an error, which might take a significant amount of time. During that period, the new incarnation of the server was unable to join the group because the existing members did not connect to it, as they were still waiting on the connection to the old incarnation. Now, GCS only treats a connection to a server as active while messages can be sent to it successfully. If the socket is no longer writeable, the server connection is treated as inactive and is proactively closed. The connection close triggers the group member to attempt reconnection to that server address, upon which a connection is established to the new incarnation of the server, enabling the new incarnation to join the group
- Replication: Group Replication did not broadcast a notification when switching from single-primary mode to multi-primary mode. The change is now notified for use in routing
- Replication: When a replication source server shuts down and restarts, its MEMORY tables become empty. To replicate this effect to replicas, the first time that the source uses a given MEMORY table after startup, it notifies replicas that the table must be emptied by writing a DELETE statement for that table to the binary log. Previously, the generated DELETE statement was written to the binary log statement cache for the current session, which could result in it being logged together with other statements under the same GTID, or logged without BEGIN and COMMIT statements. Also, in some situations, the generated DELETE statement could consume the GTID intended for the transaction that triggered it. The generated DELETE statement is now logged with accompanying BEGIN and COMMIT statements, and the resulting transaction is flushed to the binary log immediately after it is written to the statement cache, so that it always receives its own GTID and is kept separate from other transactions
- Replication: Following a patch in MySQL 8.0.14, if a function call contained operations on temporary tables, it could be written to the binary log in statement format when binlog_format = MIXED was set. This led to CREATE TEMPORARY TABLE statements being incorrectly written to the binary log if they contained a function call. Following further analysis, operations on temporary tables in stored functions and triggers are now marked as unsafe for binary logging in statement format, as they have a strong chance of causing issues with replication. When binlog_format = MIXED is set, these operations are now logged in row format
- Replication: Setting the group_replication_force_members system variable to force a specified membership for a group could fail if another member had already requested the expulsion of the member that was driving the group_replication_force_members operation. The operation to implement the configuration specified by the group_replication_force_members system variable forced any pending group reconfigurations to take place first. If one of those successfully expelled the member where the system variable had been set, because the expel timeout that was set on the member had expired, the operation timed out and failed to complete. To avoid this situation, Group Replication now proceeds directly to implementing the new configuration specified by the group_replication_force_members system variable, and ignores any other pending group reconfigurations
- Replication: A fix made in MySQL 8.0.14 and MySQL 5.7.25 for a deadlock scenario involving the system variables binlog_transaction_dependency_tracking and binlog_transaction_dependency_history_size had the side effect of leaving the writeset history used for transaction dependency tracking unprotected from concurrent update. The writeset history and tracking mode are now locked correctly whenever they are accessed
- Replication: If a CHANGE MASTER TO statement was issued with MASTER_USER specified as empty (MASTER_USER=''), the statement succeeded and cleared any previously specified user name in the replication metadata repositories. However, if information was subsequently read from the repositories, for example during an automatic restart of a Group Replication channel, a default user name could be substituted for the channel. This issue has now been fixed, so from MySQL 8.0.21, it is a valid approach to set an empty MASTER_USER user name if you always provide user credentials using the START SLAVE statement or START GROUP_REPLICATION statement that starts the replication channel. This approach means that the replication channel always needs operator intervention to restart, but the user credentials are not recorded in the replication metadata repositories.
- The documentation for the CHANGE MASTER TO statement has also been corrected to clarify that it is possible to specify MASTER_USER='', and the resulting error occurs only if you attempt to start the replication channel with the empty credentials
- Replication: Group Replication's tracking of connections to other group members only took into account the incoming connections, not the outgoing connections. This meant if the outgoing connection from member A to member B was broken, for example by a firewall configuration issue, but the incoming connection from member B to member A was intact, member A would display member B's status as ONLINE, although member A's messages were not reaching member B. Member B would display member A's status as UNREACHABLE. Now, if a group member starts to receive pings from another group member to which it has an active connection (in this case, if member A received pings from member B), this is treated as an indicator of an issue with the connection. If sufficient pings are received, the connection is shut down by the recipient of the pings (in this case, member A), so that the status of the connection is consistent for both members
- JSON: When the expression and path passed to JSON_TABLE() yielded a JSON null, the function raised an error instead of returning SQL NULL as required
- JSON: In MySQL 5.7, and in MySQL 8.0 prior to 8.0.17, the server attempted to convert JSON boolean values to their SQL counterparts when testing them directly with IS TRUE
- JSON: A GROUP BY query against a table having a multi-valued index was not always handled correctly by the server
- If log_error_services was persisted, in some cases it could take effect at the wrong time during startup
- SHOW CREATE USER after certain manual grant table modifications could cause a server exit
- Some in-memory updates of partial revokes could produce incorrect privileges
- If log_error_verbosity was set using SET PERSIST, it did not take effect early enough during server startup to affect InnoDB initialization
- The parser incorrectly raised an assertion before rejecting subqueries in generated column expressions
- This release makes the following two micro-optimizations for degenerate hash joins (that is, those with no join conditions):
- For a degenerate hash antijoin or semijoin, add LIMIT 1 when building the hash table, since having more rows than this cannot change the result
- For a degenerate hash antijoin with a nonempty hash table, avoid scanning the outer side
- Together, these changes handle a performance regression whereby a rewrite to a hash antijoin caused a NOT EXISTS query which was not rewritten to be executed by the optimizer and be replaced with “zero rows found”. To handle the case in which a nested loop is used instead, a non-correlated subquery inside NOT EXISTS is no longer transformed to an antijoin.
- This fix also applies to subqueries using constant NOT IN (non_correlated_subquery)
- Configuring with -DWITH_EDITLINE=system resulted in compilation failures for older library versions
- The upgrade of the bundled libedit library in the previous MySQL distribution caused a problem for builds using that library such that CTRL+C (SIGINT) in the mysql client required a following Enter to take effect in some circumstances
- Columns declared with both AUTO_INCREMENT and DEFAULT value expressions are a nonpermitted combination, but ALTER TABLE failed to produce an error for SET DEFAULT (expr) operations on AUTO_INCREMENT columns
- It was possible to set the protocol_compression_algorithms system variable to the empty string. This is no longer permitted
- A lookup function used internally in the MySQL server returns integer -1 when the argument is ambiguous; this resulted in undefined behavior when this value was converted to an unsigned value prior to use as an argument in subsequent calculations. Now when the function returns -1, this is handled as an error and the value is not used further
- Negation of a signed value in certain cases led to undefined behavior; to prevent this from occurring, the value to be negated is now treated as unsigned
- The WEIGHT_STRING() function did not always return the correct result for an integer argument
- Assigning CONCAT('') or CONCAT_WS('') to a variable set the variable to NULL, not the empty string
- Corrected problems where under some circumstances privilege restrictions could be ignored
- Certain SELECT statement privileges to lock rows were not checked properly and could block other users incorrectly
- When performing a filesort, an internal function could sometimes return NULL on failure, even if the subselect being sorted was not nullable
- Statement rewriting for the binary log was inefficient on Windows
- References: This issue is a regression of:
- An inconsistency in representing anonymous users in memory could cause issues while performing privilege-granting operations
- If the administrative connection interface was enabled, a race condition could lead to problems accepting Unix socket file connections on the main connection interface
- When a role was granted with WITH ADMIN OPTION, the grantee was able to manage the role only after activating it
- Invalid rows in the default_roles or role_edges system tables could cause server misbehavior
- Component deinitialization failure at runtime could lead to repeated messages written to the error log at shutdown
- The prohibition on granting roles to anonymous users was incompletely enforced
- A privilege-escalation issue was corrected
- The keyring_hashicorp keyring plugin did not perform sufficient validity checking on the values of its configuration parameters
- The keyring_hashicorp keyring plugin did not permit binary log encryption to be enabled (by setting the binlog_encryption system variable)
- The keyring_hashicorp keyring plugin did not permit an encryption password to be set by the audit_log plugin
- Some queries using REGEXP_SUBSTR() with an ORDER BY clause were not handled correctly by the server
- Some instances where pointer arithmetic was applied to nullptr were corrected
- If the available file descriptors were exhausted, mysql_real_connect() caused the client to exit
- Using the killall command to initiate a mysqld shutdown resulted in no message being logged to indicate the start of shutdown. This has been corrected
- Calling mysql_real_connect_nonblocking() with an invalid host could cause the client to exit upon calling mysql_close()
- For Debian packages, Python 2 dependencies that could cause installation failures were removed
- A potential memory leak in lf_hash_insert() was fixed
- Within the LDAP SASL authentication plugins, multiple calls to sasl_client_done() could cause undefined behavior in some cases
- With the thread pool plugin enabled, high concurrency conditions could cause loss of client context resulting in a server exit
- For result sets processed using mysql_use_result(), mysql_fetch_row_nonblocking() did not increment the number of rows, so that after all the rows were fetched, mysql_num_rows() returned an incorrect number of rows
- Removed an unneeded optimization for EXISTS() that was never actually evaluated
- For a server started with the --skip-grant-tables option, enabling the partial_revokes system variable caused a server exit
- Queries that used a recursive common table expression with an outer reference could return incorrect results
- The parser could fail for multibyte character sets with a minimum character length greater than 1 byte
- In some cases, the LEAST() function could return NULL for non-nullable input
- mysql_real_connect_nonblocking() blocked if the MYSQL_OPT_CONNECT_TIMEOUT option was set
- The last call to the mysql_fetch_row_nonblocking() C API function to return the null row was setting an error when it should not have
- On Windows, the default connection type uses a named pipe. The nonblocking C API, which is intended for TCP/SSL connections, did not take that into account and caused the client to exit. It now produces an error message indicating the problem
- X Plugin connections that failed to authenticate due to nonexistence of the user modified the global audit_log_filter_id system variable
- LOAD DATA did not ignore hidden generated columns when parsing input file rows
- Pinbox exhaustion in the metadata locking subsystem could produce a misleading error message
- CREATE TABLE ... SELECT failed if it included a functional index
- For X Protocol connections, checking the global session mutex was improved to eliminate a minor performance degradation as the number of threads increased
- In certain cases, executing a query containing multiple subqueries could lead to an unplanned shutdown of the server
- SHOW CREATE TRIGGER failed if FLUSH TABLES WITH READ LOCK was in effect
- Excessive access checks were performed on certain of the data dictionary tables that underlie INFORMATION_SCHEMA views, resulting in slow SHOW COLUMNS performance. These checks were reduced to improve performance
- In addition, several SHOW statements implemented as INFORMATION_SCHEMA queries were found to benefit from enabling the derived_merge flag for the optimizer_switch system variable. Such queries now internally enable that flag temporarily for better performance, regardless of the flag session value
- Two otherwise identical queries executed separately returned one row when using a case-sensitive collation and two rows with a case-insensitive collation. When the same two predicates were combined in a single query using AND, two rows were returned when only one row should have been
- ALTER TABLE on a SET column that had a display width greater than 255 was not done in place, even if otherwise possible
- The server checked whether a number in yottabytes was too large to print by comparing the value as a double to ULLONG_MAX, which cannot be represented as a double. This caused the double value immediately above ULLONG_MAX yottabytes to be printed as 0Y, the erroneous conversion being reported by Clang 10
- Resource group SQL statements such as CREATE RESOURCE GROUP did not work over connections that use X Protocol
- SHOW GRANTS could display function privileges as procedure privileges
- The audit_log plugin mishandled connection events when multiple clients connected simultaneously
- The LOCK_ORDER tool reported a syntax error for empty dependency graphs. Empty graphs are now permitted
- The LOCK_ORDER tool could exhibit unexpected behavior due to mishandling thread list maintenance
- Upgrades from MySQL 5.7 did not grant the REPLICATION_APPLIER privilege to root
- The gen_range() user-defined function could mishandle its arguments, causing a server exit
- During UPDATE processing, conversion of an internal in-memory table to InnoDB could result in a key-length error
- Attempts to grant dynamic privileges (which are always global) at the procedure or function level did not produce an error
- Table value constructors ignored the LIMIT clause. The clause is now taken into account. For example: VALUES ROW(1), ROW(2), ROW(3) LIMIT 2 outputs 1 and 2
- It is possible to define a column named * (single asterisk character), but SELECT `*` was treated identically to SELECT *, making it impossible to select only this column in a query; in other words, the asterisk character was expanded to a list of all table columns even when it was surrounded by backticks
- The FROM_DAYS() function could produce results that were out of range (with the year > 9999)
- Privileges on the INFORMATION_SCHEMA.FILES table were being checked incorrectly
- For debug builds, altering the mysql.func table to MyISAM (not a recommended operation in any case) caused a server exit. Now this operation is prohibited
- Queries on the INFORMATION_SCHEMA KEY_COLUMN_USAGE and TABLE_CONSTRAINTS views could be slow due to UNION use in their definitions. These were rewritten to move the UNION into a LATERAL table to enable the optimizer to better use indexes
- In certain cases, a LIMIT clause incorrectly caused the optimizer to estimate that zero rows needed to be read from a table
- An internal packet-length function returned values of the wrong integer type
- Calculations by mysqldump for the length of INSERT statements did not take into account the _binary character set introducer used for VARBINARY strings
- The messages printed to the error log during upgrade of partitioned tables defined with prefix keys did not provide sufficient details. Detailed warnings that indicate the schema, table, column, and prefix length are now printed
- mysql_store_result() could fail to detect invalid data packets
- An assertion was raised if creating a child table in a foreign key relation caused an engine substitution
- mysqltest and mysql-test-run.pl no longer support the --sleep command-line option. mysqltest no longer supports the real_sleep command
- The server permitted connections for hosts with names longer than the maximum permitted length (255 characters)
- In a multiple-table UPDATE that updated the key of the first table, if a temporary table strategy was used, duplicate entries could be written to the temporary table, followed by occurrence of a Can't find record error
- The server sometimes mistakenly removed a subquery with a GROUP BY when optimizing a query, even in some cases when this subquery was used by an outer select. This could occur when the subquery also used an aggregate function
- Coercibility of the NAME_CONST() function was assessed incorrectly
- When reading rows from a storage engine, errors other than “no more records” could be ignored, leading to problems later
- When a multi-table update used a temporary table, this was not shown in the output of EXPLAIN FORMAT=TREE, even though such use could have an impact on the performance of the UPDATE statement for which this was done
- When performing a filesort for removing duplicates, such as when executing SELECT DISTINCT, it may be necessary to perform another sort afterwards to satisfy an ORDER BY. In cases where such an ORDER BY had been pushed down into the first table of a join, as opposed to the join as a whole, this final sort was not actually performed
- Refactoring work done in MySQL 8.0.20 caused single-row buffering for GROUP BY of non-nullable columns not to function correctly, not taking into account that such a column could be the inner table for an outer join, and thus would have a NULL flag that would need to be copied. In a GROUP BY without a temporary table, this would cause the NULL flag to come from the next output row instead of the previous one, and the data returned to be inconsistent
- A logical error in the constant folding code for the case in which a constant of type DECIMAL or FLOAT was the left-hand operand and an integer column value was the right-hand operand yielded an incorrect result
- A query whose predicate compared 0 with -0 where at least one of these was a floating-point value returned incorrect results
- SELECT DISTINCT( HEX( WEIGHT_STRING(varchar_column) ) ) returned a truncated result
- Problems with error handling in queries with MAX(), MIN(), or both, combined with a GROUP BY clause, meant that such a query continued executing until it went through all possible iterations even when an error should have caused it to terminate immediately
- Reimplemented rollups without using slices. This fixes the following known issues:
- A repeating column in a GROUP BY ... WITH ROLLUP yielded the wrong result; that is, a GROUP BY of the form GROUP BY a, b, a WITH ROLLUP erroneously produced NULL for some of the column names in the result.
- A GROUP BY ... WITH ROLLUP that did not require a temporary table to print the result also produced an erroneous NULL in place of at least one of the expected column names in the output.
- After refactoring the type propagation code for LEAST(), GREATEST(), and other functions, as well as UNION, an adjustment of the result type for data types like ENUM also replaced the calculated integer data type with a type that could not accommodate both signed and unsigned values