MySQL Community Server 8.0.16 has been released [part 1]

Bjorn Munch <[email protected]> Thu, 25 Apr 2019 13:27:53 +0200

Newsgroups	gmane.comp.db.mysql.announce
Organization	Oracle Corp.
Message-ID	<20190425112753.GA3049__28820.1673816488$1556191879$gmane$org@atum31.no.oracle.com>

[Due to size limitation, this announcement is split in two. This is part 1]

Dear MySQL users,

MySQL Server 8.0.16, a new version of the popular Open Source
Database Management System, has been released. MySQL 8.0.16 is
recommended for use on production systems.

For an overview of what's new in MySQL 8.0, please see

http://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

For information on installing MySQL 8.0.16 on new servers, please see
the MySQL installation documentation at

http://dev.mysql.com/doc/refman/8.0/en/installing.html

MySQL Server 8.0.16 is available in source and binary form for a number of
platforms from our download pages at

http://dev.mysql.com/downloads/mysql/

MySQL Server 8.0.16 is also available from our repository for Linux
platforms, go here for details:

http://dev.mysql.com/downloads/repo/

Windows packages are available via the Installer for Windows:

http://dev.mysql.com/downloads/installer/

along with .ZIP (no-install) packages for more advanced needs.

8.0.16 also comes with a web installer as an alternative to the full
installer.

The web installer doesn't come bundled with any actual products
and instead relies on download-on-demand to fetch only the
products you choose to install. This makes the initial download
much smaller but increases install time as the individual products
will need to be downloaded.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches, etc.:

http://bugs.mysql.com/report.php

The following link lists the changes in the MySQL 8.0 since
the release of MySQL 8.0.15. It may also be viewed
online at

http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html

Enjoy!

==============================================================================
Changes in MySQL 8.0.16 (2019-04-25, General Availability)

* Account Management Notes

* C API Notes

* Character Set Support

* Compilation Notes

* Configuration Notes

* Deprecation and Removal Notes

* Installation Notes

* Packaging Notes

* Parser Notes

* Performance Schema Notes

* Plugin Notes

* Security Notes

* Spatial Data Support

* SQL Syntax Notes

* Test Suite Notes

* X Plugin Notes

* Functionality Added or Changed

* Bugs Fixed

Account Management Notes

* Previously, users who had the DROP ROLE privilege could
use the DROP ROLE statement to drop locked or unlocked
accounts. Now, users who have the DROP ROLE privilege can
use DROP ROLE only to drop accounts that are locked
(unlocked accounts are presumably user accounts used to
log in to the server and not just as roles). Users who
have the CREATE USER privilege can use DROP ROLE to drop
accounts that are locked or unlocked. (Bug #28953158, Bug
#93263)

* Several changes have been made to MySQL
account-management capabilities:

+ MySQL now incorporates the concept of user account
categories, with system and regular users
distinguished according to whether they have the new
SYSTEM_USER privilege:
o System users are users who possess the
SYSTEM_USER privilege. A system user can
perform operations on both system and regular
accounts.
o Regular users are ordinary users who do not
possess the SYSTEM_USER privilege. A regular
user can perform operations on regular
accounts, but not system accounts.
If a user has the appropriate privileges to perform
a given operation on regular accounts, SYSTEM_USER
enables the user to also perform the operation on
system accounts. SYSTEM_USER does not imply any
other privilege, so the ability to perform a given
account operation remains predicated on possession
of any other required privileges. For example, if a
user can grant the SELECT and UPDATE privileges to
regular accounts, then with SYSTEM_USER the user can
also grant SELECT and UPDATE to system accounts.
The distinction between system and regular accounts
enables better control over certain account
administration issues by protecting accounts that
have the SYSTEM_USER privilege from accounts that do
not have the privilege. For example, the CREATE USER
privilege enables not only creation of new accounts,
but modification and removal of existing accounts.
Without the system user concept, a user who has the
CREATE USER privilege can modify or drop any
existing account, including the root account. The
concept of system user enables restricting
modifications to the root account (itself a system
account) so they can be made only by system users.
Regular users with the CREATE USER privilege can
still modify or drop existing accounts, but only
regular accounts.
Other operational implications of the SYSTEM_USER
privilege:
o A session that has the SYSTEM_USER privilege
can be killed only by users who have the
SYSTEM_USER privilege, in addition to any other
required privileges.
o An account that has the SYSTEM_USER privilege
can be specified as the DEFINER for a stored
object only by users who have the SYSTEM_USER
privilege, in addition to any other required
privileges.
o A role that has the SYSTEM_USER privilege
cannot be listed in the value of the
mandatory_roles system variable.
For more information, see Account Categories
(http://dev.mysql.com/doc/refman/8.0/en/account-categories.html).

+ Previously, it was not possible to grant privileges
that apply globally except for certain schemas. This
is now possible if the new partial_revokes system
variable is enabled. For example, the following
statements enable an account to select from or
insert into any table except those in the mysql
system schema:
SET PERSIST partial_revokes = ON;
GRANT SELECT, INSERT ON *.* TO u1;
REVOKE SELECT, INSERT ON mysql.* FROM u1;

The server records partial revokes by adding a
Restrictions attribute to the User_attributes column
of the mysql.user system table. SHOW GRANTS includes
REVOKE statements in its output to indicate partial
revokes.
For more information, see Privilege Restriction
Using Partial Revokes
(http://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html).

+ The GRANT statement has a new AS user [WITH ROLE]
clause that specifies additional information about
the privilege context to use for statement
execution. This syntax is visible at the SQL level,
although its primary purpose is to enable uniform
replication across all nodes of grantor privilege
restrictions imposed by partial revokes, by causing
those restrictions to appear in the binary log.
For more information, see GRANT Syntax
(http://dev.mysql.com/doc/refman/8.0/en/grant.html).

C API Notes

* The MySQL C API now supports asynchronous functions for
nonblocking communication with the MySQL server:

+ mysql_real_connect_nonblocking()

+ mysql_real_query_nonblocking()

+ mysql_store_result_nonblocking()

+ mysql_next_result_nonblocking()

+ mysql_fetch_row_nonblocking()

+ mysql_free_result_nonblocking()
Each function is the asynchronous counterpart to a
synchronous function that has the same name without a
_nonblocking suffix. The synchronous functions block if
reads from or writes to the server connection must wait.
The asynchronous functions enable an application to check
whether work on the server connection is ready to
proceed. If not, the application can perform other work
before checking again later. See C API Asynchronous
Interface
(http://dev.mysql.com/doc/refman/8.0/en/c-api-asynchronous-interface.html).

Character Set Support

* MySQL now supports a new Chinese collation,
utf8mb4_zh_0900_as_cs, for the utf8mb4 Unicode character
set. utf8mb4_zh_0900_as_cs is the first Chinese
language-specific collation available for Unicode in
MySQL. This collation is accent sensitive and case
sensitive. Its characteristics are similar to
utf8mb4_0900_as_cs except that language-specific rules
take precedence where applicable. For more information,
see Unicode Character Sets
(http://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html).

Compilation Notes

* CMake now causes the build process to link with the llvm
lld linker for Clang if it is available and not
explicitly disabled. To disable use of this linker,
specify the -DUSE_LD_LLD=OFF option. (Bug #29264211)

* Builds on EL6 and EL7 now try to use the compiler in
devtoolset-8 rather than devtoolset-7. (Bug #29198846)

* The minimum version of the Boost library for server
builds is now 1.69.0. (Bug #29114233)

* The configuration-time check for Visual Studio 2017 was
not specific enough. The check for MySQL compilation now
requires at least Visual Studio update 15.8, which is
version number 1915. (Bug #28970895)

* MySQL now can be compiled using C++14. The following
minimum version requirements apply for compiler support:

+ GCC 5.3 (Linux)

+ Clang 4.0 (FreeBSD)

+ XCode 9 (MacOS)

+ Developer Studio 12.6 (Solaris)

+ Visual Studio 2017 (Windows)

Configuration Notes

* MySQL configuration now requires a minimum CMake version
of 3.4.3. This requires the use of cmake3 rather than
cmake on some Red Hat and Oracle Linux platforms. (Bug
#29246216)

* The WITH_LZMA CMake option was removed. (Bug #29153932,
Bug #93755)

* The EXCLUDE_FROM_ALL option is now used in CMake
configuration as appropriate so that libraries are built
only if they are actually used by any executable. (Bug
#29052599)

* The new WITH_JEMALLOC CMake option indicates whether to
link with -ljemalloc. If enabled, built-in malloc(),
calloc(), realloc(), and free() routines are disabled.
The default is OFF. (Bug #29027974)

* The new WITH_LSAN CMake option indicates whether to run
LeakSanitizer, without AddressSanitizer. The default is
OFF. (Bug #28936574)

* The new WITH_ROUTER CMake option indicates whether to
build MySQL Router. The default is ON. (Bug #28759234)

* MySQL Server now supports a --validate-config option that
enables the startup configuration to be checked for
problems without running the server in normal operational
mode. For more information, see Server Configuration
Validation
(http://dev.mysql.com/doc/refman/8.0/en/server-configuration-validation.html).

Deprecation and Removal Notes

* The TempTable storage engine now always uses InnoDB to
manage internal temporary tables on disk, and the choice
of storage engine employed for this task is no longer
user-configurable. The internal_tmp_disk_storage_engine
system variable has been removed. (Bug #91377, Bug
#28234637)
References: See also: Bug #28081038, Bug #82556, Bug
#27408352.

Installation Notes

* Previously, after installation of a new version of MySQL,
the MySQL server automatically upgrades the data
dictionary tables at the next startup, after which the
DBA is expected to invoke mysql_upgrade manually to
upgrade the system tables in the mysql schema, as well as
objects in other schemas such as the sys schema and user
schemas.
The server now performs the tasks previously handled by
mysql_upgrade. After installation of a new MySQL version,
the server now automatically performs all necessary
upgrade tasks at the next startup and is not dependent on
the DBA invoking mysql_upgrade. In addition, the server
updates the contents of the help tables (something
mysql_upgrade did not do). A new --upgrade server option
provides control over how the server performs automatic
data dictionary and server upgrade operations. For more
information, see Upgrading MySQL
(http://dev.mysql.com/doc/refman/8.0/en/upgrading.html).
This change to the upgrade procedure results in some
deprecations:

+ mysql_upgrade is deprecated because it is no longer
necessary.

+ The --no-dd-upgrade server option is deprecated
because the --upgrade option supersedes it.
mysql_upgrade and the --no-dd-upgrade option will be
removed in a future MySQL version. (Bug #28146052, Bug
#28162609, Bug #91205, Bug #29185739, Bug #27740692, Bug
#28547424, Bug #91961)

Packaging Notes

* Binary packages that include curl rather than linking to
the system curl library now use curl 7.64.0. (Bug
#29357198)

* The Henry Spencer regex library (extra/regex) is no
longer used by MySQL 8.0 and is no longer present in
source distributions. (Bug #29192306)

* RPM packages now have dependencies on libtirpc and rpcgen
because newer versions of glibc do not include Sun RPC.
(Bug #28995257)

* The support-files/magic file was removed from the MySQL
source tree. Most MySQL file formats are covered by
operating system file type capabilities. (Bug #18335080,
Bug #71898)

Parser Notes

* The parser no longer accepts the undocumented and
nonstandard = alias_name syntax for specifying table
aliases. (Bug #29205289)

* The parser is now permits several additional nonreserved
keywords to be used as labels within stored programs that
previously were restricted from such use: ACCOUNT,
ALWAYS, BACKUP, CLOSE, FORMAT, GROUP_REPLICATION, HOST,
INVISIBLE, OPEN, OPTIONS, OWNER, PARSER, PORT, REMOVE,
RESTORE, ROLE, SECONDARY, SECONDARY_ENGINE,
SECONDARY_LOAD, SECONDARY_UNLOAD, SECURITY, SERVER,
SOCKET, SONAME, UPGRADE, VISIBLE, WRAPPER. (Bug
#29033659)

* The parser accepted ODBC escape syntax for outer joins ({
OJ outer_join }), but also accepted identifiers other
than OJ. The parser now accepts only OJ.
Note
OJ now is a nonreserved keyword.
(Bug #22320942)

Performance Schema Notes

* MySQL now includes built-in SQL functions that format or
retrieve Performance Schema data, and that may be used as
equivalents for existing sys schema stored functions:

+ FORMAT_BYTES(): Converts a byte count to a value
with units. Similar to sys.format_bytes().

+ FORMAT_PICO_TIME(): Converts a time in picoseconds
to a value with units. Similar to sys.format_time().

+ PS_THREAD_ID(): Returns the Performance Schema
thread ID for a given thread. Similar to
sys.ps_thread_id() invoked with a non-NULL argument.

+ PS_CURRENT_THREAD_ID(): Returns the Performance
Schema thread ID for the current thread. Shortcut
for sys.ps_thread_id() invoked with a NULL argument.
The built-in functions can be invoked in any schema and
require no qualifier, unlike the sys functions, which
require either a sys. schema qualifier or that sys be the
current schema.
The built-in functions supersede the corresponding sys
functions, which now are deprecated and will be removed
in a future MySQL version. Applications that use the sys
functions should be adjusted to use the built-in
functions instead, keeping in mind some minor differences
between the sys functions and the built-in functions. See
Performance Schema Functions
(http://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html).

* The new Performance Schema keyring_keys table exposes
metadata for keys in the MySQL Keyring. Key metadata
includes key IDs, key owners, and backend key IDs. The
keyring_keys table does not expose any sensitive keyring
data such as key contents. See The keyring_keys table
(http://dev.mysql.com/doc/refman/8.0/en/keyring-keys-table.html).

Plugin Notes

* MySQL now includes a ddl_rewriter plugin that modifies
CREATE TABLE statements received by the server before it
parses and executes them. The plugin removes ENCRYPTION,
DATA DIRECTORY, and INDEX DIRECTORY clauses, which may be
helpful when restoring tables from SQL dump files created
from databases that are encrypted or that have their
tables stored outside the data directory. For example,
the plugin may enable restoring such dump files into an
unencrypted instance or in an environment where the paths
outside the data directory are not accessible. When
installed, ddl_rewriter exposes the Performance Schema
memory/rewriter/ddl_rewriter instrument for tracking
plugin memory use. For more information, see The
ddl_rewriter Plugin
(http://dev.mysql.com/doc/refman/8.0/en/ddl-rewriter.html).

Security Notes

* Previously, if the grant tables were corrupted, the MySQL
server wrote a message to the error log but continued as
if the --skip-grant-tables option had been specified.
This resulted in the server operating in an unexpected
state unless --skip-grant-tables had in fact been
specified. Now, the server stops after writing a message
to the error log unless started with --skip-grant-tables.
(Starting the server with that option enables you to
connect to perform diagnostic operations.) (Bug
#29394501, Bug #94394)

* The OpenSSL libraries bundled with MySQL on some
platforms (Windows, macOS, and Generic Linux) have been
upgraded to version 1.0.2r. On all other platforms, MySQL
uses the system installed OpenSSL. Issues fixed in the
new OpenSSL version are described at
http://www.openssl.org/news/vulnerabilities.html. (Bug
#28988091)

* Granting roles to anonymous users is no longer supported
because such roles caused problematic behavior. (Bug
#28910120)

* OpenSSL 1.1.1 supports the TLS v1.3 protocol for
encrypted connections, and MySQL now supports TLS v1.3 as
well, if compiled using OpenSSL 1.1.1 or higher:

+ Some TLSv1.3 ciphersuites are enabled by default.
The tls_ciphersuites system variable enables
explicitly specifying which TLSv1.3 ciphersuites the
server permits.

+ The --tls-ciphersuites client option enables
specifying which TLSv1.3 ciphersuites a client
permits. This option applies to these programs:
mysql, mysqladmin, mysqlbinlog, mysqlcheck,
mysqldump, mysqlimport, mysqlpump, mysqlshow,
mysqlslap, mysqltest, mysql_secure_installation, and
mysql_upgrade.

+ The mysql_options() C API function has a new
MYSQL_OPT_TLS_CIPHERSUITES option that enables
specifying from within the client library which
TLSv1.3 ciphersuites a client program permits.
For more information, see Encrypted Connection Protocols
and Ciphers
(http://dev.mysql.com/doc/refman/8.0/en/encrypted-connection-protocols-ciphers.html).
Note
Currently, Group Replication does not support TLSv1.3.

* The SSL context the server uses for new connections now
is reconfigurable at runtime. This capability may be
useful, for example, to avoid restarting a MySQL server
that has been running so long that its SSL certificate
has expired. Dynamic SSL reconfigurability is based on
these changes:

+ The system variables that define the SSL context are
dynamic and can be changed at runtime: ssl_ca,
ssl_capath, ssl_cert, ssl_cipher, ssl_crl,
ssl_crlpath, ssl_key, tls_ciphersuites. tls_version.

+ The ALTER INSTANCE statement supports a RELOAD TLS
action that reconfigures the SSL context from the
current values of the system variables that define
the context.

+ These status variables reflect the SSL context the
server uses for new connections: Current_tls_ca,
Current_tls_capath, Current_tls_cert,
Current_tls_cipher, Current_tls_ciphersuites,
Current_tls_crl, Current_tls_crlpath,
Current_tls_key, Current_tls_version.
ALTER INSTANCE RELOAD TLS updates those status
variables from the corresponding system variable
values when it reconfigures the SSL context.
For more information, see Server-Side Runtime
Configuration for Encrypted Connections
(http://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html#using-encrypted-connections-server-side-runtime-configuration).
Thanks to Facebook for submitting code for a similar
feature (although the code was not used).
References: See also: Bug #27980097.

* In MySQL 8.0, the default authentication plugin was
changed from mysql_native_password. to
caching_sha2_password. Because caching_sha2_password
provides a superset of the capabilities of the
sha256_password authentication plugin, sha256_password is
now deprecated and will be removed in a future MySQL
version. MySQL accounts that authenticate using
sha256_password should be migrated to use
caching_sha2_password instead.

Spatial Data Support

* The ST_Length() function now takes an optional second
argument enabling the unit for the return value to be
specified. Permitted units are those listed in the new
INFORMATION_SCHEMA ST_UNITS_OF_MEASURE table. See
LineString and MultiLineString Property Functions
(http://dev.mysql.com/doc/refman/8.0/en/gis-linestring-property-functions.html),
and The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table
(http://dev.mysql.com/doc/refman/8.0/en/st-units-of-measure-table.html).

SQL Syntax Notes

* Incompatible Change: In MySQL 5.7, specifying a FOREIGN
KEY definition for an InnoDB table without a CONSTRAINT
symbol clause, or specifying the CONSTRAINT keyword
without a symbol, causes InnoDB to use a generated
constraint name. That behavior changed in MySQL 8.0, with
InnoDB using the FOREIGN KEY index_name value instead of
a generated name. Because constraint names must be unique
per schema (database), the change caused errors due to
foreign key index names that were not unique per schema.
To avoid such errors, the new constraint naming behavior
has been reverted, and InnoDB once again uses a generated
constraint name.
For consistency with InnoDB, the NDB storage engine now
uses a generated constraint name if the CONSTRAINT symbol
clause is not specified, or the CONSTRAINT keyword is
specified without a symbol. In NDB releases based on
MySQL 5.7 and earlier MySQL 8.0 releases, NDB used the
FOREIGN KEY index_name value.
The changes described above may introduce
incompatibilities for applications that depend on the
previous foreign key constraint naming behavior. (Bug
#29173134)

* Previously, MySQL permitted a limited form of CHECK
constraint syntax, but parsed and ignored it. MySQL now
implements the core features of table and column CHECK
constraints, for all storage engines. Constraints are
defined using CREATE TABLE and ALTER TABLE statements.
For more information, see CHECK Constraints
(http://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html).
(Bug #11744849, Bug #3464, Bug #3465, Bug #11746042, Bug #22759)

Test Suite Notes

* mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR
environment variable, which may be set to specify a
unique-IDs directory to be used as the common location
for all chroot environments by multiple simultaneous
mysql-test-run.pl instances. This enables those instances
to avoid conflicts when reserving port numbers. Thanks to
Facebook for the contribution. (Bug #29221085, Bug
#93950)

* The my_safe_process program was renamed to
mysqltest_safe_process and now is installed with other
binaries such as mysqltest rather than with test suite
files. (Bug #29198969)

* These changes to the all_persisted_variables test were
implemented:

+ It limits the number of hardcoded values in the test
output by putting them into local variables. New
patches that are then rebased on top that add new
system variables do not need to change as many lines
of the original test case, making it easier on the
rebase process.

+ It removes entries for bugs that were fixed and
modifies the queries to include the system variables
that were not tested due to open bugs.
Thanks to the Facebook team for the contribution. (Bug
#29013375, Bug #93478)

X Plugin Notes

* X Plugin previously returned a StmtExecuteOk message to
the client after query cleanup had ended and the session
had been deactivated. The message is now returned as soon
as the result is known and before query cleanup, which
gives a noticeable improvement in performance. (Bug
#28997370)

* X Plugin logged the system message "X Plugin ready for
connections" when user connections were not available
because preparation of I/O interfaces had failed. (Bug
#28906360)

* Some items in the X Plugin code were not instrumented for
the Performance Schema by default. (Bug #28898155)

* X Protocol now supports the COM_RESET_CONNECTION utility
command to reset the session state without
re-authenticating or closing the connection. (Bug
#28732455)

* X Plugin produced compilation warnings when the MySQL
Server source code was built using the Clang 8 compiler.
(Bug #28732158)

Functionality Added or Changed

* InnoDB: When the amount of memory occupied by the
TempTable storage engine exceeds the limit defined by the
temptable_max_ram variable, the TempTable storage engine
allocates space for internal in-memory temporary tables
as memory-mapped temporary files. This behavior is now
controlled by the temptable_use_mmap variable, which can
be disabled to have the TempTable storage engine use
InnoDB on-disk internal temporary tables instead. For
more information, see Internal Temporary Table Use in
MySQL
(http://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html).
(Bug #28944457)

* InnoDB: undo and purge subsystem counters were added for
monitoring background activities associated with undo log
truncation. For counter names and descriptions, query the
INFORMATION_SCHEMA.INNODB_METRICS table.
SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE NAME LIKE '%truncate%';

For information about enabling counters and querying
counter data, see InnoDB INFORMATION_SCHEMA Metrics Table
(http://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-metrics-table.html).
(Bug #28813526)

* InnoDB: The new innodb_spin_wait_pause_multiplier
variable provides greater control over the duration of
spin-lock polling delays that occur when a thread waits
to acquire a mutex or rw-lock. Delays can now be tuned
more finely to account for differences in PAUSE
instruction duration on different processor
architectures. For more information, see Configuring Spin
Lock Polling
(http://dev.mysql.com/doc/refman/8.0/en/innodb-performance-spin_lock_polling.html).

* InnoDB: An internal service interface was added to
support tracking of modified pages.

* InnoDB: The InnoDB data-at-rest encryption feature now
supports encryption of the mysql system tablespace. The
mysql system tablespace contains the mysql system
database and the MySQL data dictionary tables.

* Some InnoDB memory allocation functions that previously
were evaluated at runtime now are evaluated at compile
time, resulting in performance improvements. (Bug
#29370811, Bug #94380)

* The semi-join optimizations for IN subqueries have been
extended to work with EXISTS subqueries as well; these
can now be handled with the same semi-join strategies as
IN subqueries, including first-match, materialization,
duplicate weedout and loose index scan.
In addition, the optimizer decorrelates
trivially-correlated equality predicates in the WHERE
condition attached to the subquery, so that they can be
treated similarly to expressions in IN subqueries. The
decorrelation is now also performed for IN subqueries as
well as EXISTS subqueries.
All hints and optimizer switches applicable to IN
subqueries which are transformed into semi-join
operations are also applicable to transformed EXISTS
subqueries. All limitations on such optimization of IN
subqueries also apply to transformed EXISTS subqueries,
so that, for example, aggregate EXISTS subqueries cannot
be transformed.
For more information, see Optimizing Subqueries, Derived
Tables, View References, and Common Table Expressions
with Semi-Join Transformations
(http://dev.mysql.com/doc/refman/8.0/en/semi-joins.html).

* For consistency with the SQL standard and other RDBMS,
table aliases are now supported in single-table as well
as multi-table DELETE statements. (Bug #27455809)

* The group communication engine for Group Replication
(XCom, a Paxos variant) includes a cache for messages
(and their metadata) that are exchanged between the group
members as a part of the consensus protocol. Among other
functions, the message cache is used for recovery by
members that return to the group after a period where
they were unable to communicate with the other group
members.
Previously, the size limit for the message cache was
fixed at 1GB of memory, and the maximum number of
messages in the cache was also fixed. However, now that
the group_replication_member_expel_timeout system
variable (introduced in MySQL 8.0.13) can be set to allow
up to an hour for members to return to the group rather
than being expelled, having a fixed 1GB limit on the size
of the cache could cause such nodes to be unable to
recover the messages they missed on re-establishing
communication.
For this reason, from MySQL 8.0.16, XCom's message cache
has no fixed limit on the number of messages it can
contain, and is only bounded by the limit set for the
amount of memory it can use. The cache size limit can be
set using the new group_replication_message_cache_size
system variable, which has a default and minimum setting
of 1GB, as used in previous MySQL Server versions. If the
cache size limit is reached, XCom removes the oldest
entries that have been decided and delivered. The cache
size limit can be increased or reduced dynamically at
runtime. If you reduce the cache size limit, XCom removes
the oldest entries that have been decided and delivered
until the current size is below the limit. Group
Replication's Group Communication System (GCS) alerts
you, by a warning message, when a message that is likely
to be needed for recovery by a member that is currently
unreachable is removed from the message cache. (Bug
#26482507)

* Large messages sent between Group Replication group
members can now be split into multiple messages when they
exceed a user-defined threshold size. Sending an
abnormally large message can result in some group members
being reported as failed and expelled from the group.
This is because the single thread used by the group
communication engine (XCom, a Paxos variant) is occupied
processing the message for too long, so some of the group
members might report the receiver as failed.
The new system variable
group_replication_communication_max_message_size can be
used to specify the maximum message size for Group
Replication communications. Messages greater than this
size are automatically split into fragments that are sent
separately and reassembled by the recipients. Message
delivery for a fragmented message is considered complete
when all the fragments of the message have been received
and reassembled by all the group members. Fragmentation
is applied by default, and can be switched off by
specifying a zero value for the system variable.
Because older MySQL Server releases do not support
message fragmentation, in order to ensure backward
compatibility, Group Replication now has the concept of a
communication protocol for the group. The communication
protocol version is set to accommodate the oldest MySQL
Server version that you want the group to support. A
MySQL server at version X can only join and reach ONLINE
status in a replication group if the group's
communication protocol version is less than or equal to
X.
You can inspect the communication protocol in use by a
group by using the new
group_replication_get_communication_protocol() UDF, which
returns the oldest MySQL Server version that the group
supports. Versions from MySQL 5.7.14 allow compression of
messages, and versions from MySQL 8.0.16 also allow
fragmentation of messages. When a new member joins a
replication group, it checks the communication protocol
version that is announced by the existing members of the
group. If the joining member supports that version, it
joins the group and uses the communication protocol that
the group has announced, even if the member supports
additional communication capabilities. If the joining
member does not support the communication protocol
version, it is expelled from the group.
If you need to change the communication protocol version
of a group so that members at earlier releases can join,
use the new
group_replication_set_communication_protocol() UDF to
specify the MySQL Server version of the oldest member
that you want to allow. This makes the group fall back to
a compatible communication protocol version if possible.
If you upgrade all the members of a replication group to
a new MySQL Server release, the group's communication
protocol version is not automatically upgraded to match.
If you no longer need to support members at earlier
releases, use the
group_replication_set_communication_protocol() UDF to set
the communication protocol version to the new MySQL
Server version to which you have upgraded the members.
(Bug #26438884, Bug #23240361, Bug #28474580, Bug #91830,
Bug #28642504, Bug #26941977, Bug #29240931)

* For Group Replication, the new system variable
group_replication_autorejoin_tries lets you specify the
number of tries that a member makes to automatically
rejoin the group if it is expelled, or if it is unable to
contact a majority of the group before the
group_replication_unreachable_majority_timeout setting is
reached. The default setting, 0, means that the member
does not try to rejoin, and proceeds to the action
specified by the group_replication_exit_state_action
system variable.
Activate auto-rejoin if you can tolerate the possibility
of stale reads and want to minimize the need for manual
intervention, especially where transient network issues
fairly often result in the expulsion of members. If you
specify a number of tries, when the member's expulsion or
unreachable majority timeout is reached, it makes an
attempt to rejoin (using the current plugin option
values), then continues to make further auto-rejoin
attempts up to the specified number of tries. After an
unsuccessful auto-rejoin attempt, the member waits 5
minutes before the next try. During the auto-rejoin
procedure, the member remains in super read only mode and
displays an ERROR state on its view of the replication
group. The member can be stopped manually at any time by
using a STOP GROUP_REPLICATION statement or shutting down
the server. If the specified number of tries is exhausted
without the member rejoining or being stopped, the member
proceeds to the action specified by the
group_replication_exit_state_action system variable,
which can be either remaining in super read only mode or
shutting down. (Bug #25673350, Bug #84784, Bug #28732174)

* WHERE conditions making comparisons between constants and
column values in which the constant value is out of range
or of the wrong type with respect to the column type are
now handled during optimization rather than during
execution. For example, given a table t with a column c
whose type is TINYINT UNSIGNED, the condition in the
query SELECT * FROM t WHERE c < 256 can be folded to
SELECT * FROM t WHERE TRUE because 256 is out of range
for a column of this type. Comparisons with NULL columns
can also be optimized; if the column c is nullable, the
same query can be optimized as SELECT * FROM t WHERE c IS
NOT NULL.
The comparisons that can be treated in this manner are >,
>=, <, <=, =, <>/!=, and <=>. (BETWEEN and IN are not
currently supported.) Types for which comparisons can be
folded based on range and type include integer,
floating-point, and fixed-point numeric types. BIT is not
supported by this optimization, nor are columns of date
and time types.
For more information, see Constant-Folding Optimization
(http://dev.mysql.com/doc/refman/8.0/en/constant-folding-optimization.html).
(Bug #90100, Bug #25484743, Bug #29048682, Bug #27703371)
References: See also: Bug #28172538.

* When binary log and relay log encryption is in use on a
MySQL server (binlog_encryption=ON), you can now rotate
the binary log master key at any time while the server is
running by issuing ALTER INSTANCE ROTATE BINLOG MASTER
KEY. You can do this on a regular basis to comply with
your organization's security policy, and also if you
suspect that the current or any of the previous binary
log master keys might have been compromised.
When you rotate the binary log master key, the new master
key is used to encrypt the file passwords for the new
binary log and relay log files, and subsequent files
until the key is changed again. The file passwords for
existing encrypted binary log files and relay log files
on the server are also re-encrypted in turn using the new
binary log master key, starting with the most recent
files. Any unencrypted files are skipped. Finally, all
binary log encryption keys that no longer apply to any
retained binary log files or relay log files are cleaned
up from the keyring.

* Table encryption can now be managed globally by defining
and enforcing encryption defaults. The
default_table_encryption variable defines an encryption
default for newly created schemas and general
tablespaces. The encryption default for a schema can also
be defined using the DEFAULT ENCRYPTION clause when
creating a schema. By default, a table inherits the
encryption of the schema or general tablespace it is
created in.
Encryption defaults are enforced by enabling the
table_encryption_privilege_check variable. The privilege
check occurs when creating or altering a schema or
general tablespace with an encryption setting that
differs from the default_table_encryption setting, or
when creating or altering a table with an encryption
setting that differs from the default schema encryption.
The TABLE_ENCRYPTION_ADMIN privilege permits overriding
default encryption settings when
table_encryption_privilege_check is enabled.
For more information, see Defining an Encryption Default
for Schemas and General Tablespaces
(http://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html#innodb-schema-tablespace-encryption-default).

* The system variable group_replication_exit_state_action
specifies how Group Replication behaves when a server
instance leaves the group unintentionally, for example
after encountering an applier error, or in the case of a
loss of majority, or when another member of the group
expels it due to a suspicion timing out. Before the
system variable was introduced, the server's response in
these situations was to switch MySQL to super read only
mode by setting the system variable super_read_only to
ON. group_replication_exit_state_action provided the
alternative option for the server to shut itself down,
which minimizes the possibility of stale reads and means
that servers do not need to be pro-actively monitored for
failures. When the system variable was introduced in
MySQL 5.7.24 and MySQL 8.0.12, the default was set to
READ_ONLY in 5.7 for compatibility with the existing
behavior, and to ABORT_SERVER in 8.0. Following user
feedback, the default has now been changed to READ_ONLY
from MySQL 8.0.16.

* When insertions, deletions, or updates are made to
partitioned tables, the binary log now records
information about the partition and (if any) the
subpartition in which the row event took place. A new row
event is created for a modification that takes place in a
different partition or subpartition, even if the table
involved is the same. So if a transaction involves three
partitions or subpartitions, three row events are
generated. For an update event, the partition information
is recorded for both the "before" image and the "after"
image. The partition information is displayed if you
specify the -v or --verbose option when viewing the
binary log using mysqlbinlog. Partition information is
only recorded when row-based logging is in use
(binlog_format=ROW).

Bugs Fixed

* NDB Cluster: EXPLAIN of a query executed with table
access type eq_ref could also show a condition being
pushed down, even when condition pushdown was not
supported for the query. Now the access type is checked
before NDB checks the condition for a possible pushdown
optimization.
This fix does not affect handling of tables which are
part of a pushed join, for which NDB continues to support
pushed conditions as before. (Bug #27429615)
References: See also: Bug #27397802, Bug #27808758, Bug
#90301.

* InnoDB: Undo tablespaces remained unencrypted after
enabling undo tablespace encryption at startup. (Bug
#29477795)

* InnoDB: Problematic macros introduced with undo
tablespace DDL support in MySQL 8.0.14 were revised. (Bug
#29324132, Bug #94243)

* InnoDB: Static thread local variables defined at the
wrong scope were not released at thread exit. (Bug
#29305186)

* InnoDB: The performance_schema.data_locks LOCK_DATA
column only showed the secondary index values of the
locked record for a lock placed on a unique secondary
index, which was not sufficient to ensure the uniqueness
of identified records. The clustered index column values
of the locked record are now appended. (Bug #29296645)

* InnoDB: An incorrect count of transactions using a
rollback segment for recovery of an XA transaction
prevented an undo tablespace truncation operation from
proceeding and left the purge thread busy checking for
the undo tablespace to become empty. (Bug #29273194)
References: This issue is a regression of: Bug #29273194.

* InnoDB: Invalid assertions were raised at startup after a
failure to retrieve the space ID of a compressed
file-per-table tablespace. The invalid assertion code was
removed. (Bug #29221385, Bug #93760)

* InnoDB: Optimized InnoDB internal temporary tables did
not support in-place UPDATE operations, which caused the
number of delete-marked records to increase continuously.
The large number of delete-marked records could cause
longer than expected query execution times. (Bug
#29207450)

* InnoDB: The std::sort function in the Variance-Aware
Transaction Scheduling (VATS) algorithm was replaced by
the std::stable_sort function to preserve the original
FIFO order for transactions of equal weight. (Bug
#29058967)

[To be continued]

--
MySQL Announce Mailing List
For list archives: http://lists.mysql.com/announce
To unsubscribe: http://lists.mysql.com/announce