CREATE USER now permits a DEFAULT ROLE clause enabling the account default roles to be specified.

SHOW CREATE USER now displays the account default roles if the default is not NONE. (Bug #24670738, Bug #82987)

MySQL now maintains information about password history, which makes it possible to enable restrictions on reuse of previous passwords. DBAs can require that new passwords not be selected from previous passwords for some number of password changes or period of time. It is possible to establish password-reuse policy globally using the password_history and password_reuse_interval system variables, as well as on a per-account basis using the CREATE USER and ALTER USER statements. Together with existing password-expiration capabilities to require that passwords be changed periodically, the new reuse-restriction capabilities provide DBAs more complete control over password management. For more information, see Password Management.

(WL #6595)

MySQL now supports atomic data definition statements (atomic DDL). An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, crash-safe, transaction that is either fully committed or rolled back.

Both table and nontable DDL statements are supported. Table-related DDL operations require storage engine support, whereas nontable DDL operations do not. Currently, the InnoDB storage engine supports atomic DDL.

For table-related DDL operations, InnoDB writes DDL logs to the mysql.innodb_ddl_log data dictionary table. Enabling the innodb_print_ddl_logs configuration option prints DDL recovery logs to stderr.

The atomic DDL feature changes the behavior of some statements:

Changes to DROP TABLE, DROP VIEW, and account management statement behavior have implications for cross-version replication configurations.

For more information, see Atomic Data Definition Statement Support. (Bug #24620918, WL #9173, WL #9536, WL #7896, WL #9045, WL #7743)

The MySQL C API now enables clients to specify that metadata transfer for result sets is optional. Suppression of metadata transfer can improve performance, particularly for sessions that execute many queries that return few rows each. For more information, see Optional Result Set Metadata. (WL #8134)

MySQL now supports Russian collations for the utf8mb4 Unicode character set:

(WL #10753)

For debug builds, the SAFE_MUTEX compilation flag was disabled if the memcached plugin was included in the build. This no longer occurs; SAFE_MUTEX is always enabled for debug builds. Some code issues found as a result of this change were corrected. (Bug #26442367, Bug #87068)

Binary packages on EL6 and EL7 now are compiled using Devtoolset 6 rather than Devtoolset3 and GCC 6.2.1 rather than 4.9.2. (Bug #26436968, Bug #87061)

MySQL now compiles for SPARC on Oracle Linux. (Bug #26306331, Bug #86745)

MySQL compilation on macOS using Clang now requires a Clang version different from 8.0, which has problems with certain inline constructs. (Bug #26279510, Bug #86711)

Work was done to clean up the source code base, including: Removing unneeded CMake checks; removing unused macros from source files; reorganizing header files to reduce the number of dependencies and make them more modular, removing function declarations without definitions, replacing locally written functions with equivalent functions from industry-standard libraries.

The performance_schema_max_mutex_classes system variable default value has been increased from 220 to 250. The performance_schema_max_thread_classes system variable default value has been increased from 50 to 100. (Bug #26193630)

The new cte_max_recursion_depth system variable implements a common table expression (CTE) maximum recursion depth. The server terminates execution of any CTE that recurses more levels than the value of this variable. For more information, see Limiting Common Table Expression Recursion. (Bug #26136509, Bug #86444, WL #10972)

The back_log system variable default value is now the value of max_connections, which enables the permitted backlog to adjust to the maximum permitted number of connections. (WL #9704)

To enable the Event Scheduler by default, the event_scheduler system variable default value was changed from OFF to ON. (WL #9644)

The max_allowed_packet system variable default value has been increased from 4194304 (4M) to 67108864 (64M). (WL #8393)

The max_error_count system variable default variable has been increased from 64 to 1024. (WL #9686)

These INFORMATION_SCHEMA tables have been reimplemented as views on data dictionary tables:

FILES
PARTITIONS
REFERENTIAL_CONSTRAINTS

Queries on those tables are now more efficient because they obtain information from data dictionary tables rather than by other, slower means. For example, the server no longer must create a temporary table for each query of the INFORMATION_SCHEMA table.

If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes. (WL #9814, WL #11059)

Replication: The following obsolete mysqlbinlog options are now deprecated and will be removed in a future MySQL version:

The deprecation warnings for these options are sent to standard error, rather than to standard output, so that they do not interfere with the operation of tools that use the output of mysqlbinlog. (WL #9632, WL #9633)

Replication: The deprecated global scope for the sql_log_bin system variable has been removed. sql_log_bin now has session scope only. Applications that rely on accessing @@GLOBAL.sql_log_bin should be adjusted. (WL #10922)

These encryption-related deprecated items have been removed:

In place of the removed encryption functions: For ENCRYPT(), consider using SHA2() instead for one-way hashing. For the others, consider using AES_ENCRYPT() and AES_DECRYPT() instead. (Bug #26493987, WL #10788, WL #10789)

The deprecated tx_isolation and tx_read_only system variables have been removed. Use transaction_isolation and transaction_read_only instead. (WL #9636)

The deprecated query cache has been removed. Removal includes these items:

These deprecated query cache items remain deprecated, but have no effect, and will be removed in a future MySQL version:

The have_query_cache system variable remains deprecated, always has a value of NO, and will be removed in a future MySQL version. (WL #10824)

The deprecated EXTENDED and PARTITIONS keywords for the EXPLAIN statement have been removed. These keywords are unnecessary because their effect is always enabled. (WL #9678)

The unused date_format, datetime_format, time_format, and max_tmp_tables system variables have been removed. (WL #9680)

The deprecated multi_range_count system variable has been removed. (WL #10908)

The deprecated log_warnings system variable and --log-warnings server option have been removed. Use the log_error_verbosity system variable instead. (WL #9676)

The deprecated secure_auth system variable and --secure-auth client option have been removed. The MYSQL_SECURE_AUTH option for the mysql_options() C API function was removed. (WL #9674)

The deprecated ignore_builtin_innodb system variable has been removed. (WL #9675)

In MySQL 8.0.2, the system variables for the slave status logs, master_info_repository and relay_log_info_repository, were set to TABLE instead of FILE by default. In MySQL 8.0.3, the FILE setting for both these system variables is deprecated, and a warning is issued if it is used. The FILE setting will be removed in a future MySQL version.

The TABLE setting ensures that replication repository information is stored in InnoDB tables, rather than in files in the data directory. The use of tables makes replication resilient to unexpected halts.

The default names for the slave status logs when stored as files were master.info and relay-log.info. The names could be changed using the --master-info-file and --relay-log-info-file options, respectively. As InnoDB tables, the slave status logs are named mysql.slave_master_info and mysql.slave_relay_log_info.

To modify an existing replication slave that is using a FILE repository for the slave status logs to use TABLE repositories, convert the existing replication repositories dynamically by running the following commands:

STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';

The master info log table mysql.slave_master_info should be protected because it contains the password for connecting to the master. When you back up the replication slave's data, ensure that you back up the mysql.slave_master_info and mysql.slave_relay_log_info tables containing the slave status logs, because they are needed to resume replication after you restore the data from the slave. (WL #6959)

Renaming of columns in a parent foreign key is temporarily disabled due to ongoing work on foreign key locking. This restriction will be lifted in MySQL 8.0.4. (Bug #26334071)

References: See also: Bug #26659110.

The binary log is now enabled by default at server startup. The log_bin system variable is set to ON by default, instead of OFF, even if the --log-bin option has not been specified. Binary logging is standard practice for production installations, so enabling it by default removes configuration and planning steps that were usually required.

To disable binary logging, you can specify the --skip-log-bin or --disable-log-bin option at startup.

The server_id system variable is now set to 1 by default, instead of 0. For servers in a replication topology, you must still change this setting to specify a unique server ID for each replication server. Previously, the server could not start with log_bin=ON if no server ID was specified. Now, the server can start, but a warning message is issued if you did not set an explicit server ID. (From MySQL 8.0.4, the message is only informational.)

With binary logging enabled for a server, all statements that change data are logged to the server's binary log, which is a sequence of files with a base name and numeric extension. By default, the server creates binary log files and an index file in the data directory. In MySQL 8.0.3, the default base name of these files is host_name-bin, using the name of the host machine. From MySQL 8.0.4, the default base name is binlog if you do not supply the --log-bin option, and host_name-bin if you supply the option with no string or an empty string.

You can choose the names and locations of the binary log files and index file by specifying the --log-bin and --log-bin-index options. You are recommended to specify a base name explicitly, so that if the host name changes, you can easily continue to use the same binary log file names. The log_bin_basename system variable holds the base name and any specified path for the binary log files.

The relay log and relay log index on a replication slave, whose names are specified by the --relay-log and --relay-log-index options, cannot be given the same names as the binary log and binary log index. From MySQL 8.0.3, the server issues an error message and does not start if the binary log and relay log file base names would be the same.

The server creates a new binary log file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current file's size reaches max_binlog_size, which defaults to the maximum permitted value of 1GB. In MySQL 8.0.3, binary log files expire by default after 30 days, and can then be automatically removed at startup or when the binary log is flushed. You can purge binary log files manually using the PURGE BINARY LOGS statement, or specify a different binary log expiration period using the binlog_expire_logs_seconds system variable.

Many other options are available to modify the behavior of binary logging. For more information, see The Binary Log and Binary Logging Options and Variables. (WL #10470)

References: See also: Bug #26730000.

The optimizer now supports a SET_VAR hint that sets the session value of a system variable for the duration of a single statement. Examples:

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

For more information, see Optimizer Hints. (Bug #22906815, WL #681)

The optimizer now uses column-value histogram statistics stored in the column_statistics data dictionary table to construct query execution plans. Histogram use applies to predicates involving comparison of a column to a constant. See Optimizer Statistics. (WL #9223)

Previously, there was no way of skipping the use of index dives to estimate index usefulness, except by using the eq_range_index_dive_limit system variable. Now index dive skipping is possible for single-table queries under certain query conditions (see Range Optimization). (WL #6526)

The optimizer_switch system variable has a new flag named use_invisible_indexes to control whether the optimizer uses invisible indexes for query execution plan construction. If the flag is off (the default), the optimizer ignores invisible indexes (the same behavior as prior to the introduction of this flag). If the flag is on, invisible indexes remain invisible but the optimizer takes them into account for execution plan construction. (WL #10891)

mysqlcheck was missing in the MySQL Server Docker image, which prevented mysql_upgrade from running. (Bug #26400146, Bug #86968)

For Debian, non-debug binaries were moved from the mysql-server package to the mysql-server-core package. (Bug #26382333, Bug #86899)

The Debian/Ubuntu mysql-community-source package is no longer produced because the MySQL source tarball it contained is provided by other packages at dev.mysql.com. (Bug #26201482)

The zlib library bundled with MySQL has been upgraded from version 1.2.3 to version 1.2.11. MySQL implements compression with the help of the zlib library.

The zlib compressBound() function in zlib 1.2.11 returns a slightly higher estimate of the buffer size required to compress a given length of bytes than it did in zlib version 1.2.3. The compressBound() function is called by InnoDB functions that determine the maximum row size permitted when creating compressed InnoDB tables or inserting and updating rows in compressed InnoDB tables. As a result, CREATE TABLE ... ROW_FORMAT=COMPRESSED, INSERT, and UPDATE operations with row sizes very close to the maximum row size that were successful in earlier releases could now fail. To avoid this issue, test CREATE TABLE statements for compressed InnoDB tables with large rows on a MySQL 8.0 test instance prior to upgrading. (WL #10551)

As of MySQL 8.0.2, Performance Schema table definitions are maintained internally to the server. In consequence of that change, CREATE TABLE and DROP TABLE are no longer possible for Performance Schema tables. (Bug #26136994)

The events_statements_summary_by_digest table now provides, for each row, a sample statement that produces the digest value in the row. Applications can use this information as a more efficient means of capturing statement samples than alternatives such as probing the xxx_history_long tables. The latter approach requires enabling the corresponding xxx_history_long consumers, which is additional overhead for applications that do not otherwise need those tables. For more information, see Performance Schema Statement Digests and Sampling, and Statement Summary Tables.

Additionally, the FIRST_SEEN and LAST_SEEN timestamp columns of the events_statements_summary_by_digest table now have a fractional seconds part. (WL #9830)

The Performance Schema setup_instruments table now has columns for instrument metadata: Instrument properties, instrument volatility, and a documentation string describing the instrument purpose. Also, the TIMED column now can be NULL, indicating that the instrument does not support timing. See The setup_instruments Table.

The new Performance Schema setup_threads table exposes instrumented thread class names and attributes. See The setup_threads Table. (WL #7801)

The new get_sysvar_source plugin service enables plugins to retrieve the source of system variable settings.

(WL #9424)

A new caching_sha2_password authentication plugin is available. Like the sha256_password plugin, caching_sha2_password implements SHA-256 password hashing, but uses caching to address latency issues at connect time. It also supports more connection protocols and does not require linking against OpenSSL for RSA password-exchange capabilities. See Caching SHA-2 Pluggable Authentication. (WL #9591)

Because starting the server with --skip-grant-tables disables authentication checks, the server also disables remote connections in that case by enabling skip_networking. (WL #4321)

MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group. Group attributes enable control over its resources, to enable or restrict resource consumption by threads in the group. DBAs can modify these attributes as appropriate for different workloads. Currently, CPU time is a manageable resource, represented by the concept of “virtual CPU” as a term that includes CPU cores, hyperthreads, hardware threads, and so forth. The server determines at startup how many virtual CPUs are available, and database administrators with appropriate privileges can associate these CPUs with resource groups and assign threads to groups. For more information, see Resource Groups. (WL #9467)

Incompatible Change: Previously, these functions that test geometry relationships supported only Cartesian spatial reference systems (SRSs): ST_Contains(), ST_Crosses(), ST_Disjoint(), ST_Equals(), ST_Intersects(), ST_Overlaps(), ST_Touches(), ST_Within(), MBRContains(), MBRCoveredBy(), MBRCovers(), MBRDisjoint(), MBREquals(), MBRIntersects(), MBROverlaps(), MBRTouches(), MBRWithin().

These functions now detect geometry arguments in a geographic SRS and return geographic results. Calculations for projected SRSs and SRID 0 remain the same. For more information, see Spatial Relation Functions That Use Object Shapes, and Spatial Relation Functions That Use Minimum Bounding Rectangles.

(WL #10827)

InnoDB: Spatial reference identifier (SRID) support was added for InnoDB spatial indexes. (WL #10439)

Spatial data types now permit an SRID attribute, to explicitly indicate the spatial reference system (SRS) for values stored in the column. See Spatial Data Types.

To indicate each column's SRID attribute value, if there is one, the INFORMATION_SCHEMA.COLUMNS table now has an SRS_ID column.

A spatial column with an explicit SRID attribute is SRID-restricted: The column takes only values with that ID, and SPATIAL indexes on the column become subject to use by the optimizer. The optimizer ignores SPATIAL indexes on spatial columns with no SRID attribute. See SPATIAL Index Optimization. (WL #8592)

ALTER TABLE now supports easier column renaming using RENAME COLUMN old_name TO new_name syntax. See ALTER TABLE Statement. For changing a column name but not its definition, RENAME COLUMN is more convenient than CHANGE, which requires respecifying the current column definition. With CHANGE, you must look up the definition if you do not know it, and if you do not respecify it exactly, there is a possibility of data change or loss. (Bug #11746522, Bug #26949, Bug #11747473, Bug #32497, Bug #11765084, Bug #58006, Bug #14031617, WL #10761)

The X Plugin could not be installed when the server was started with the --skip-grant-tables option. (Bug #26516678)

X Plugin socket connections were not working correctly. (Bug #26427112, Bug #87019)

When compiling MySQL from source, certain infrequently used CMake arguments caused issues for the X Plugin build. (Bug #26141933)

A Mysqlx.Connection.CapabilitiesGet request using X Protocol did not return the complete list of available authentication mechanisms. (Bug #26044113)

For mixed case or uppercase schema names, the statement list_objects could incorrectly report a collection as a table. (Bug #25769683)

The X Plugin was omitted from the list of plugins to include for testing data directory permissions. (Bug #24823999)

InnoDB: The new --innodb-dedicated-server server option, can be used to have InnoDB automatically set the following options according to the amount of memory detected on the server:

This option is intended for MySQL server instances that run on a dedicated server. For more information, see Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server. (WL #9193)

InnoDB: Renaming a general tablespace is now supported by ALTER TABLESPACE ... RENAME TO syntax.

The ALTER TABLESPACE and DROP TABLESPACE ENGINE clause is deprecated and will be removed in a future MySQL version. (WL #8972)

InnoDB: Code related to obsoleted InnoDB system tables was removed. INFORMATION_SCHEMA views based on InnoDB system tables were replaced by internal system views on data dictionary tables. Affected InnoDB INFORMATION_SCHEMA views were renamed:

After upgrading to MySQL 8.0.3 or later, update any scripts that reference previous InnoDB INFORMATION_SCHEMA view names.

The new INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF view provides space, name, path, flag, and space type data for InnoDB tablespaces. (WL #9535)

InnoDB: When InnoDB was integrated with the global data dictionary, file-per-table tablespace names in the data dictionary were created in the form of innodb_file_per_table_x, where x is the InnoDB tablespace ID. For ease of use, file-per-table tablespace names in the data dictionary are once again the same as the table name.

Upgrading from MySQL 5.7 to MySQL 8.0 appends MySQL 5.7 innodb_table_stats and innodb_index_stats tablespace names in the data dictionary with “_backup57” to differentiate them from their MySQL 8.0 counterparts. (WL #10436)

InnoDB: The default innodb_autoinc_lock_mode setting was changed from 1 (consecutive) to 2 (interleaved). Interleaved lock mode permits the execution of multi-row inserts in parallel, which improves concurrency and scalability. The new innodb_autoinc_lock_mode default setting reflects the change from statement-based replication to row based replication as the default replication type in MySQL 5.7. Statement-based replication requires the consecutive auto-increment lock mode to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of SQL statements, whereas row-based replication is not sensitive to the execution order of SQL statements. For more information, see InnoDB AUTO_INCREMENT Lock Modes.

For systems that use statement-based replication, the new innodb_autoinc_lock_mode default setting may break applications that depend on sequential auto-increment values. To restore the previous default, set innodb_autoinc_lock_mode to 1. (WL #9699)

InnoDB: Serialized dictionary information (SDI) is now present in all InnoDB tablespace files except for temporary tablespace and undo tablespace files. SDI is serialized metadata for table and tablespace objects. The presence of SDI data provides metadata redundancy. For example, dictionary object metadata may be extracted from tablespace files if the data dictionary becomes unavailable. SDI extraction is performed using the ibd2sdi tool. SDI data is stored in JSON format.

The inclusion of SDI data in tablespace files increases tablespace file size. An SDI record requires a single index page, which is 16KB in size by default. However, SDI data is compressed when it is stored to reduce the storage footprint. (WL #9538)

InnoDB: The innodb_flush_neighbors default value was changed from 1 to 0, which disables flushing of neighboring pages from the buffer pool. A setting of 0 is optimal for non-rotational storage (SSD) devices where seek time is not a significant factor. For systems that use rotational storage (HDD), it is recommended to change the setting back to the previous default value of 1. (WL #9631)

InnoDB: Default values for configuration options that affect buffer pool preflushing and flushing behavior were modified:

(WL #9707, WL #9630)

InnoDB: The minimum innodb_undo_tablespaces value changed from 0 to 2. In previous releases, the system tablespace is used for rollback segments if innodb_undo_tablespaces is set to 0. A minimum value of 2 ensures that rollback segments are created in undo tablespaces instead of the system tablespace. For more information, see Undo Tablespaces. (WL #10583)

Replication; JSON: Added the binlog_row_value_options system variable. Currently this variable can be unset, or set to the value PARTIAL_JSON. This causes MySQL's row-based replication to use a compact binary log format for each update modifying only a small portion of a JSON document and using any combination of JSON_SET(), JSON_REPLACE(), and JSON_REMOVE(). The compact format includes only the modified parts of the JSON document, not the full document, in the after-image used for the update in the binary log. If the modification requires more space than the full document, or if it is not possible to generate a partial update, the full document is used instead.

See the description of the variable as well as Partial Updates of JSON Values, for more information. (WL #2955)

Replication: The IGNORE_SERVER_IDS option of the CHANGE MASTER TO statement is now deprecated when using GTID-based replication (gtid_mode=ON). With GTIDs, transactions that have already been applied are automatically ignored, so this function is not needed.

Before starting GTID-based replication, check for and clear all ignored server ID lists that have previously been set on the servers involved. The SHOW_SLAVE_STATUS statement, which can be issued for individual channels, displays the list of ignored server IDs if there is one. If there is no list, the Replicate_Ignore_Server_Ids field is blank.

If gtid_mode=ON is set for the server, a deprecation warning is now issued if you include the IGNORE_SERVER_IDS option in a CHANGE MASTER TO statement. A deprecation warning is also issued if you issue a SET GTID_MODE=ON statement when any channel has existing server IDs set with IGNORE_SERVER_IDS. If you do receive the deprecation warning, you can still clear a list after gtid_mode=ON is set by issuing a CHANGE MASTER TO statement containing the IGNORE_SERVER_IDS option with an empty list. (WL #10963)

Replication: The log_slave_updates system variable is now set to ON by default, so you do not need to specify --log-slave-updates explicitly when you start a replication slave.

The log_slave_updates system variable is read-only. If you need to prevent a replication slave from logging the updates performed by its SQL thread to its own binary log, specify --log-slave-updates=OFF at slave server startup. (WL #10479)

Group Replication: This release adds the group_replication_communication_debug_options server system variable, making it possible to filter out debugging and tracing messages dynamically, per Group Replication component. (WL #10200)

Group Replication: Group Replication thread states are now shown in the MySQL Performance Schema. (WL #10622)

JSON: The JSON_MERGE() function is renamed to JSON_MERGE_PRESERVE().

This release also adds the JSON_MERGE_PATCH() function, an RFC 7396 compliant version of JSON_MERGE_PRESERVE(); its behavior is the same as that of JSON_MERGE_PRESERVE(), with the following two exceptions:

This example compares the results of merging the same 3 JSON objects, each having a matching key "a", with each of these functions:

mysql> SET @x = '{ "a": 1, "b": 2 }',
     >     @y = '{ "a": 3, "c": 4 }',
     >     @z = '{ "a": 5, "d": 6 }';

mysql> SELECT  JSON_MERGE_PATCH(@x, @y, @z)    AS Patch,
    ->         JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
*************************** 1. row ***************************
   Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

JSON_MERGE() is still supported as an alias of JSON_MERGE_PRESERVE(), but is now deprecated and subject to removal in a future MySQL version.

See Functions That Modify JSON Values, for more information. (Bug #81283, Bug #23255346, WL #9692)

InnoDB now uses Variance-Aware Transaction Scheduling (VATS) for scheduling the release of transaction locks when the system is highly loaded, which helps reduce lock sys wait mutex contention. Lock scheduling uses VATS when >= 32 threads are suspended in the lock wait queue.

For more information about VATS, see Identifying the Major Sources of Variance in Transaction Latencies: Towards More Predictable Databases.

Thanks to Jiamin Huang for the contribution. (Bug #25290971, Bug #84266, WL #10793)

MySQL now extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. Previously, foreign key metadata, which is owned by the child table, could not be updated safely.

If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are now opened and locked implicitly. For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation.

If LOCK TABLES is active for a table in a foreign key relationship, ALTER TABLE ... RENAME is not permitted for that table. This is a temporary restriction, lifted in MySQL 8.0.4 by the patch for Bug #26647340. (WL #6049)

The expire_logs_days system variable, which specifies the binary log expiration period in days, is now deprecated and will be removed in a future MySQL version. expire_logs_days does not provide sufficient flexibility for defining the binary log expiration period.

binlog_expire_logs_seconds can be used to set the binary log expiration period in seconds. In MySQL 8.0.3, the effects of the two variables are currently cumulative. For example, if expire_logs_days is 1 and binlog_expire_logs_seconds is 43200, then the binary log is purged every 1.5 days. This produces the same result as setting binlog_expire_logs_seconds to 129600 and expire_logs_days to 0. Note that the default expire_logs_days setting of 30 days is currently added to the binary log expiration period if expire_logs_days is not specified. To use binlog_expire_logs_seconds alone, set expire_logs_days=0 explicitly.

To disable automatic purging of the binary log, you must set both expire_logs_days and binlog_expire_logs_seconds explicitly to 0. (WL #10924)

References: See also: Bug #26483363.

A new type of backup lock permits DML during an online backup while preventing operations that could result in an inconsistent snapshot. The new backup lock is supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE syntax. The BACKUP_ADMIN privilege is required to use these statements. (WL #9451)

Incompatible Change; JSON: If a JSON object contained multiple members with the same key name, MySQL kept the first member and discarded the remainder. This contradicts RFC 7159, which suggests that duplicate key names can be handled in one of the ways listed here:

When a JSON text is evaluated in JavaScript, the last name-value pair is kept if multiple pairs with the same name are specified. MySQL now does likewise, and implements the last of the three options just listed, as shown here:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": [3, 5, 7]} |
+------------------+

The fix for this issue also corrects a failure in the MySQL 8.0 server to handle insertion into a JSON column of data containing JSON arrays as the values for multiple identical keys. (Bug #86620, Bug #86866, Bug #26238736, Bug #26369555)

Performance; JSON: Creating a representation of a JSON string now optimizes for the most common case—that the string to be processed contains no special characters that need to be escaped—scanning for the first special character in the string, and copying each sequence of characters which do not require escaping in a single memcpy() call, rather than checking each character in turn to determine whether it needed to be escaped, escaping it if so, and then copying it, one by one, as was done previously.

This fix also corrects a failure to escape the control character \u001f, or unit separator character. (Bug #86898, Bug #26388690, Bug #87722, Bug #26780307)

References: See also: Bug #25977595.

InnoDB: The default value for ON UPDATE and ON DELETE foreign key clauses was changed from RESTRICT to NO ACTION. NO ACTION is a standard SQL keyword that is equivalent to the RESTRICT keyword in MySQL. (Bug #30186407, Bug #96466)

InnoDB: Foreign keys records were missing from the INFORMATION_SCHEMA.INNODB_SYS_FOREIGN table due to file name encoding of the internal parent and child table names, which caused the names to exceed the permitted length. (Bug #27020089)

InnoDB: A long semaphore wait occurred when executing ALTER TABLE, DROP TABLE, and DROP DATABASE operations. (Bug #26779650)

InnoDB: Invalid error handling code was removed from a function related to tablespace import. (Bug #26595476)

InnoDB: File-per-table tablespaces created prior to MySQL 5.6 caused a failure during an in-place upgrade to MySQL 8.0.2. The tablespaces were not registered with the InnoDB SYS_TABLESPACES system table, as required.

Tables with decimal columns created prior to MySQL 5.5 also caused a failure during an in-place upgrade to MySQL 8.0.2, due to a precision type mismatch. (Bug #26542296, Bug #87229)

InnoDB: During MySQL installation, an unnecessary warning was reported about creation of foreign key constraint system tables. (Bug #26483335)

InnoDB: A query was interrupted during concurrent ALTER TABLE operations due to a secondary index entry count mismatch. (Bug #26381213)

InnoDB: A segmentation fault occurred when attempting to open a table that was altered while strict mode was disabled to include conflicting TABLESPACE and COMPRESSION attributes. (Bug #26375851)

InnoDB: A segmentation fault occurred during a DML operation that used the TempTable storage engine. (Bug #26363837)

InnoDB: Implicit row format conversion during an ALTER TABLE ... REORGANIZE PARTITION operation raised an invalid assertion. (Bug #26326611)

InnoDB: An ALTER TABLE operation that rebuilt an encrypted table did not set the encryption attribute properly. (Bug #26243264)

InnoDB: A memory leak was encountered on Windows when using the TempTable storage engine for in-memory internal temporary tables. (Bug #26237680)

InnoDB: Misleading errors were produced when running Valgrind tests on a server build that was not enabled for Valgrind testing. (Bug #26037206)

InnoDB: Problematic code related to dropping orphan full-text search tables caused an invalid object ID assertion failure on startup. (Bug #25998362)

InnoDB: A check for discarded partitions during a DML operation only checked the first partition. Failure to check for other discarded partitions caused an assertion failure. (Bug #25942592)

InnoDB: Allocated memory was not initialized before it was written to a file, resulting in a Valgrind error. (Bug #25913151, Bug #85986)

InnoDB: Adding a virtual column and index in the same statement caused an error. (Bug #25899959)

InnoDB: Replication lag occurred on slave instances during large update operations on tables with many partitions. (Bug #25687813, Bug #85352)

InnoDB: A failure occurred during an end range comparison. (Bug #25669686)

InnoDB: Enabling the innodb_buffer_pool_load_now setting failed in read-only mode. The event that signals the buffer pool load thread was not initialized. (Bug #25586766)

InnoDB: A cursor position check by a multiversion concurrency control row search function raised an assertion. (Bug #25377592)

InnoDB: The wrong variable was passed to the row_mysql_handle_errors routine causing an assertion failure. (Bug #25183130)

InnoDB: A long wait for a dictionary operation lock held by a full-text search synchronization operation caused a server exit. (Bug #24938374, Bug #26376681, Bug #26376239)

InnoDB: Assertion code was modified to account for the possibility of a transaction attempting to acquire an explicit lock on a record while another transaction converts an implicit lock to an explicit lock on the same record prior to a commit operation. (Bug #24344131)

InnoDB: A FLUSH TABLES ... FOR EXPORT operation on an encrypted or page-compressed table raised an assertion. (Bug #22916982)

InnoDB: A DROP TABLE operation was not permitted with an innodb_force_recovery setting greater than 0. (Bug #22392152)

InnoDB: A CREATE TABLE ... SELECT operation raised an assertion failure when the newly created table was dropped before the transaction was committed. (Bug #22154768)

InnoDB: An in-place ALTER TABLE operation that rebuilt the table and added a foreign key without specifying a foreign key constraint name failed due to a duplicate constraint name. InnoDB did not account for existing foreign key constraint names. This issue was addressed in MySQL 8.0.3, when foreign key metadata was moved to the data dictionary, and foreign key constraint name generation was moved to the SQL layer. (Bug #18199504, Bug #71616)

InnoDB: A misplaced function call that locks the InnoDB data dictionary during a foreign key check was removed. (Bug #12917178, Bug #62221)

InnoDB: The error messages reported when attempting to use an existing foreign key constraint name were inconsistent and did not always provide sufficient information. (Bug #11925430, Bug #60633)

Partitioning: In certain cases when fetching heap records a partition ID could be set to zero. (Bug #86255, Bug #26034430)

Partitioning: It was possible for a CREATE TABLE statement that failed to create a partitioned InnoDB table not to be rolled back correctly. This was due to an extraneous commit made while performing a check of foreign key information. Since partitioned tables do not presently support foreign keys, this check is unnecessary, and so is no longer made in such cases. (Bug #85299, Bug #25667278)

Partitioning: Queries involving NULL were not always handled correctly on tables that were partitioned by LIST. (Bug #76418, Bug #20748521)

References: See also: Bug #86255, Bug #26034430.

Replication; Group Replication: On a member which had both Group Replication and asynchronous replication running simultaneously, asynchronous replication was not respecting restrictions imposed by Group Replication, such as using only the InnoDB storage engine, tables requiring primary keys, and so on. This could also be encountered when running mysqlbinlog against the member. Now, members running Group Replication and asynchronous replication do not allow any Group Replication requirements to be broken. (Bug #85164, Bug #25609945)

References: See also: Bug #85781, Bug #25828806.

Replication: On a multithreaded slave, it was possible for a deadlock state to occur due to the timing of updates to the record of disk space used by the relay log. The timing of the update has now been changed so that the deadlock cannot occur. (Bug #26729635)

Replication: With slave_preserve_commit_order=1 set, a deadlock could occur between a transaction holding a shared write lock on a table, and a transaction earlier in the commit order that also required a shared write lock. (Bug #26666609)

Replication: The unused variable opt_reckless_slave was removed. (Bug #26500285)

Replication: FLUSH LOGS attempted to send an OK message after having already sent an error response during the commit phase. Thanks to Laurynas Biveinis for the patch. (Bug #26272158, Bug #25363745, Bug #84437)

Replication: With GTIDs generated for incident log events, MySQL error code 1590 (ER_SLAVE_INCIDENT) could not be skipped using the --slave-skip-errors=1590 startup option on a replication slave. (Bug #26266758)

Replication: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE was set to an incorrect value when group_replication_recovery_complete_at="transactions_certified" on a recovering member. (Bug #26180350)

Replication: When replicating a partitioned table with an index, on a replication slave where HASH_SCAN was specified as part of the slave_rows_search_algorithms setting, the slave I/O thread sometimes stopped with an error HA_ERR_KEY_NOT_FOUND. (Bug #26137159)

Replication: The system variable pseudo_slave_mode, which is for internal server use, sometimes raised an assertion when it was changed inside a transaction. The server no longer changes this variable inside a transaction. (Bug #26034192, Bug #86250)

Replication: When write sets are used for parallelization by a replication slave (as specified by the binlog_transaction_dependency_tracking system variable), empty transactions are now ignored, and the handling of relay log rotation has been optimized. (Bug #25982097)

Replication: The Performance Schema replication_applier_status_by_worker table sometimes incorrectly displayed a value for APPLYING_TRANSACTION for an inactive worker, because the table was being populated before the worker thread stopped. (Bug #25896166, Bug #85951)

Replication: Attempting to uninstall the plugin while START GROUP_REPLICATION executed could result in unexpected behavior. (Bug #25423650, Bug #91042, Bug #28088177)

Replication: In case of a failure while creating multiple slave applier worker threads, some threads would be left orphaned and their resources would not be collected. Thanks to Laurynas Biveinis for his contribution to fixing this bug. (Bug #24679056, Bug #82980)

Replication: The binlog_checksum option cannot be changed within a transaction. MySQL cannot log this statement, as would be required inside a transaction, while the requested function is being performed on the binary log. (Bug #22914463)

Replication: group_replication_force_members could be used in situations where the group was working properly, in other words a majority was reachable. This incorrect use could cause instability in the group. Therefore, its use has been restricted to the scenario for which it was created, for forming a new membership from a subset of a previous group's membership when a majority of the members are unreachable. (Bug #86359, Bug #26093967)

Replication: Joining a member running a lower version to a group running a higher version resulted in the members running the higher version becoming unreachable. (Bug #85026, Bug #25568493)

Group Replication: Group Replication flow control variables now correctly permit you to have some members in a group that do not affect the minimum throughput of the flow-control mechanism, effectively ignoring those members in case they become blocked. (Bug #26537497)

Group Replication: When the Group Replication plugin delayed initialization thread failed to start due to unavailable resources, a locked mutex was left behind. Now we ensure that this mutex is unlocked when the thread does not start. (Bug #26394678)

Group Replication: In the case of delayed initialization of the Group Replication plugin deployed in single-primary mode, secondaries were able to get writes through an asynchronous replication channel, which is not allowed in normal initialization of the Group Replication plugin. (Bug #26314756)

Group Replication: If the options file contained Group Replication related settings the server could stop unexpectedly on startup. (Bug #26314472)

Group Replication: The values for group_replication_recovery_use_ssl and group_replication_recovery_ssl_verify_server_cert were not updated when configured for the Group Replication recovery channel. (Bug #26142801)

Group Replication: It was possible to set server_uuid to the same value as group_replication_group_name, which resulted in unexpected behavior because GTIDs are identified by UUIDs. To fix this problem, setting server_uuid to the same value as group_replication_group_name is no longer allowed. (Bug #26035931)

Group Replication: It was possible to start the server with invalid values for Group Replication flow control options. Now, the --group-replication-flow-control-min-quota, --group-replication-flow-control-max-quota, and --group-replication-flow-control-min-recovery-quota options are validated on server startup. (Bug #87206, Bug #26531899)

Group Replication: If hostname resolution did not work for a Group Replication member, a misleading error returned when attempting to connect referred to credentials rather than the actual problem with hostname resolution. (Bug #86858, Bug #26368004)

Group Replication: The Group Replication plugin no longer sets auto_increment_increment and auto_increment_offset when single primary mode is active. (Bug #86669, Bug #26263155)

Group Replication: Group Replication partition threads were not visible in the Performance Schema. (Bug #86626, Bug #26241008)

JSON: Containers in the internal representations of JSON objects and arrays (Json_object and Json_array) have been changed to use smart pointers rather than raw pointers to Json_dom, so that orphaned DOM objects are now automatically destroyed. (Bug #26161264)

JSON: ASCII character 31 (\u001f, the unit separator) in a string literal within a JSON document was not quoted when the JSON document was formatted as a string (for example, by CAST('"\u001f"' AS JSON)). (Bug #25977959)

JSON: When a path_expression identified a nonarray value, the JSON_INSERT() and JSON_ARRAY_INSERT() functions failed to evaluate path_expression[0] as equal to path_expression. (Bug #86213, Bug #26022576)

JSON: Searches with JSON_EXTRACT() that used wildcards took an inordinate amount of time. (Bug #84523, Bug #25418534)

References: See also: Bug #83959, Bug #25151440.

Under heavy load, an infinite loop occurred in Performance Schema buffer container code. (Bug #26666274)

MySQL-specific typedefs such as uchar and my_bool were inadvertently reintroduced into the client namespace if the mysql.h header file was included. (Bug #26588846, Bug #26582752, Bug #87337)

uint8korr() and related macros were fixed so that they explicitly do unaligned accesses, even on x86. (Bug #26568748, Bug #87298)

The main.mysql_upgrade_grant, main.roles-upgrade, and auth_sec.secure_file_priv_warnings, test cases mishandled the error log. The sys_vars.innodb_redo_log_encrypt_basic test case output was unstable. Thanks to Laurynas Biveinis for the patches. (Bug #26562401, Bug #87279, Bug #26575150, Bug #87313, Bug #26575142, Bug #87314, Bug #26582158, Bug #87303)

For debug builds, with sql_buffer_result enabled, recursive common table expressions caused a server exit. (Bug #26556025)

Incorrect resolution of a window function as a constant function could result in a server exit. (Bug #26500442)

For window functions, use of GROUP BY ... WITH ROLLUP could cause a server exit. (Bug #26497353, Bug #26497247)

For window functions, JSON columns raised an assertion. (Bug #26496733)

For window functions, a value less than the argument for a RANGE frame caused a server exit. (Bug #26496645)

Compiling with -DWITHOUT_SERVER=1 resulted in my_symlink.c compilation failure due to missing #include for my_dir.h. Thanks to Christian Hesse for the patch. (Bug #26495816, Bug #87137)

Compiling with -DWITH_SSL=system -DWITH_ZLIB=system assumed that the system openssl zlib command was available, which might not be the case. Now availability of that command is checked, and if unavailable, the zlib_decompress utility is built. (Bug #26494495, Bug #87123)

yaSSL could incorrectly perform TLS cipher negotiation. (Bug #26482173)

Some thread_stack settings could result in a server exit. (Bug #26438067)

For window functions, a ROW frame accepted noninteger arguments for the row count. (Bug #26411055, Bug #86990)

REPLACE(UUID(),...) expressions could be cached (improperly) and return the same value for each row of a result set. (Bug #26395601)

When building MySQL within the source tree, make install installed some CMake files into the mysql-test directory within the tree. (Bug #26385175, Bug #86905)

The PROCESS_ID column in the Performance Schema session_connect_attrs and session_account_connect_attrs tables was changed from INT to BIGINT UNSIGNED to accommodate larger process ID values. Thanks to Daniël van Eeden for the patch. (Bug #26357806, Bug #86835)

The index on a generated column, whose value was generated from JSON_EXTRACT(), was sometimes not used by the optimizer when it should have been. (Bug #26352119)

Setting the log_error_services system variable to NULL caused a server exit. (Bug #26331795)

For tar file packages, some test suite shared libraries were installed in the server package rather than the test package. (Bug #26329850)

SHOW COLUMNS for a valid view could fail. (Bug #26322203, Bug #86778)

An operation that caused renaming or removal of histogram statistics could cause a server exit. (Bug #26303972)

For a VARCHAR column, sorting using an explicit collation (ORDER BY col_name COLLATE collation_name) was much slower than with an implicit collation (no COLLATE clause), even if the explicit collation was the same as the implicit collation. (Bug #26286790, Bug #86710)

SET binlog_format = ROW produced a syntax error because ROW is now a reserved word. This syntax is now recognized specially to preserve backward compatibility. (Bug #26269280)

SET PERSIST_ONLY, should be permitted only to users who have the SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges, but was incorrectly also permitted to users with the SUPER privilege. (Bug #26247864)

The information_schema_stats configuration option, introduced in MySQL 8.0.0, was removed and replaced by information_schema_stats_expiry.

information_schema_stats_expiry defines an expiration setting for cached INFORMATION_SCHEMA table statistics. For more information, see Optimizing INFORMATION_SCHEMA Queries.

The TABLES_DYNAMIC and STATISTICS_DYNAMIC internal system views were removed. (Bug #26203731, Bug #83957)

Source packages for Debian platforms contained prebuilt debug binaries, causing build failures on any architectures other than the one on which those binaries were built. (Bug #26186911)

The loadable function registration service did not work if used during server startup. (Bug #26173244)

Disabling table instrumentation by changes to the Performance Schema setup_objects table could cause incorrect index names in index statistics. (Bug #26162562)

A misleading error message was returned when attempting to drop a nonexistent tablespace file. (Bug #26133507, Bug #86438)

When running mysqlbinlog with the --read-from-remote-server option, rewrite rules specified using the --rewrite-db option were ignored, so data was not written to the target database. (Bug #26117735, Bug #86288)

Timestamp data copied from the data dictionary cache during a DDL operation was converted using a time_zone value that was no longer valid. The resulting timestamp data was incorrect, causing an error in release builds and an assertion failure in debug builds. (Bug #26091333, Bug #86290)

Successful data dictionary updates but failure to write the binary log event could result in an inconsistent state. (Bug #26037355)

mysqlbinlog now prints the full metadata for the event type Table_map_log_event. (Bug #26020990)

Some string functions in an ALTER EVENT statement could cause a server exit. (Bug #25942505)

Uninstalling the daemon_memcached plugin caused a serious error. (Bug #25909540)

Prepared statements that used a common table expression and many ? parameters could be slow. (Bug #25903274, Bug #85933)

For UPDATE or DELETE statements with an ORDER BY ... LIMIT clause, the optimizer sometimes failed to identify a cheaper ordering method than filesort. (Bug #25899921)

The rpl_diff.inc test case file did not find the data difference between servers. Thanks to Yura Sorokin for the patch. (Bug #25860138, Bug #85838)

An ngram fulltext parser search query returned incorrect results and raised an assertion. (Bug #25851975)

The combination of an index virtual generated column, a foreign key, and a trigger could cause an assertion to be raised. (Bug #25817660, Bug #85757)

Selecting from a view could yield different results with materialization enabled versus materialization disabled. (Bug #25782811, Bug #85622)

After using SET PERSIST to set the event_scheduler system variable, the server failed to restart if started with the --skip-grant-tables option. (Bug #25776940)

For debug builds, adding an index to a table that had a foreign key relationship could raise an assertion. (Bug #25739983)

An assertion could be raised for MIN()/MAX() access to system tables. (Bug #25738624)

The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table requested foreign key information from the InnoDB storage engine instead of the data dictionary. (Bug #25730513)

A failed DROP VIEW could be written to the binary log. (Bug #25680097)

SHOW CREATE VIEW sometimes added a database name prefix to table names that was not present in the original view definition. (Bug #25634576, Bug #85176)

The Performance Schema variables_info table displayed incorrect VARIABLE_SOURCE and VARIABLE_PATH values for variables set within option files specified by !include or !includedir directives. (Bug #25563891)

Constant string propagation could fail for UCA-based collations. (Bug #25503965, Bug #84837)

mysqlpump displayed incorrect progress information about the number of tables dumped. (Bug #25432850)

Calculations for UCA 9.0.0 collations were inefficient for tailoring rules containing contraction characters. (Bug #25426632, Bug #84577, Bug #25426632, Bug #84577)

GROUP BY DESC on DECIMAL values could incorrectly group NULL with non-NULL values. (Bug #25407964, Bug #84537)

Some mysqldump warnings went to the standard output rather than the standard error output and consequently were written to the dump file. (Bug #25380000, Bug #82992)

NULL values generated as a result of WITH ROLLUP were replaced with the previous row's value when executing a prepared statement on a view having the GROUP BY .. WITH ROLLUP clause. (Bug #25174118)

A server error occurred when a full text search result exceeded the innodb_ft_result_cache_limit setting. The patch for this bug also backports a related patch (Bug #21140111). (Bug #25033538)

A parser refactoring in MySQL 8.0.1 resulted in incorrect handling of some INSERT ... ON DUPLICATE KEY UPDATE statements. These problems have been corrected. (Bug #24716127, Bug #25526439, Bug #25071305)

For debug builds, a CREATE TABLE statement with a VARBINARY or BINARY column having a default value in hexadecimal format caused a server exit. (Bug #24679166, Bug #83020)

If a stored function was considered a constant by the optimizer, calling it from a subquery in a NOT IN condition in the WHERE clause could cause a server exit. (Bug #23577867)

A mysqldump memory leak was fixed. Thanks to Yura Sorokin for the patch. (Bug #23531150, Bug #81714)

Incorrect results or a server exit could result when a query used Batched Key Access optimization and a virtual generated column was part of the join buffer. (Bug #23169112)

If a session rolled back to a savepoint and then was killed, the statements up to the point of the savepoint could be committed. (Bug #22350047, Bug #79596)

MySQL accepted a reference to an alias of an aggregated expression defined in an outer query block even when the reference occurred within a GROUP BY subquery where the reference was meaningless. (Bug #21974346, Bug #78785)

For clients that used Connector/Python and authenticated using the sha256_password plugin, the server could handle connections incorrectly. (Bug #21421642)

Some SELECT DISTINCT queries with GROUP BY could return incorrect results. (Bug #20692219, Bug #76283)