Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)

Note

This is a milestone release, for
use at your own risk. Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. Significant development changes take place in
milestone releases and you may encounter compatibility issues, such
as data format changes that require attention in addition to the usual
procedure of running mysql_upgrade. For example, you
may find it necessary to dump your data with mysqldump
before the upgrade and reload it afterward.

Incompatible Change:
The grant tables in the mysql system database
are now InnoDB (transactional) tables.
Previously, these were MyISAM
(nontransactional) tables. This change applies to these tables:
user, db,
tables_priv, columns_priv,
procs_priv, proxies_priv.

The change of grant table storage engine underlies an
accompanying change to the behavior of account-management
statements. Previously, an account-management statement that
named multiple users could succeed for some users and fail for
others. Now, each statement is transactional and either succeeds
for all named users or rolls back and has no effect if any error
occurs. The statement is written to the binary log if it
succeeds, but not if it fails; in that case, rollback occurs and
no changes are made. The preceding behavior applies to these
statements: ALTER USER,
CREATE ROLE,
CREATE USER,
DROP ROLE,
DROP USER,
GRANT,
RENAME USER,
REVOKE. (SET
PASSWORD is not listed because it applies to at most
one user and is effectively transactional already.)

If you upgrade to this MySQL release from an earlier version,
you must run mysql_upgrade (and restart the
server) to incorporate these changes into the
mysql system database.

Note

If MySQL is upgraded from an older version but the grant
tables have not been upgraded from MyISAM
to InnoDB, the server considers them read
only and account-management statements produce an error.

Due to the change of storage engine from
MyISAM to InnoDB,
SELECT without ORDER BY on
grant tables can produce different row orders than previously.
If a query result must have specific row ordering
characteristics, include an ORDER BY clause.

MySQL now supports roles, which are named collections of
privileges. Roles enable assignment of sets of privileges to
accounts and provide a convenient alternative to granting
individual privileges, both for conceptualizing desired
privilege assignments and implementing them:

Roles can be created and dropped.

Roles can have privileges granted to and revoked from them.

Roles can be granted to and revoked from user accounts.

The active roles for an account can be selected from among
those granted to the account, and can be changed during
sessions for that account.

ROLE now is a reserved word and cannot be
used as an identifier without identifier quoting.

C API Notes

The libmysqlclient shared library major
version number is increased from 20 (used in MySQL 5.7) to 21
for MySQL 8.0.
(Bug #77600, Bug #21363863)

Character Set Support

The utf8mb4 Unicode character set has a new
general collation named utf8mb4_0900_ai_ci.
utf8mb4 also has several new
language-specific collations with characteristics similar to
utf8mb4_0900_ai_ci except that
language-specific rules take precedence where applicable. The
language-specific collations are indicated by ISO 639-1 language
codes in the collation name, as shown in the following table. In
two cases the language code has an additional item that denotes
a variant (German phone book order, Traditional Spanish).

Table 5 utf8mb4 UCA 9.0.0 Language-Specific Collations

Language

Collation

Croatian

utf8mb4_hr_0900_ai_ci

Czech

utf8mb4_cs_0900_ai_ci

Danish

utf8mb4_da_0900_ai_ci

Esperanto

utf8mb4_eo_0900_ai_ci

Estonian

utf8mb4_et_0900_ai_ci

German phone book order

utf8mb4_de_pb_0900_ai_ci

Hungarian

utf8mb4_hu_0900_ai_ci

Icelandic

utf8mb4_is_0900_ai_ci

Latvian

utf8mb4_lv_0900_ai_ci

Lithuanian

utf8mb4_lt_0900_ai_ci

Polish

utf8mb4_pl_0900_ai_ci

Classical Latin

utf8mb4_la_0900_ai_ci

Romanian

utf8mb4_ro_0900_ai_ci

Slovak

utf8mb4_sk_0900_ai_ci

Slovenian

utf8mb4_sl_0900_ai_ci

Modern Spanish

utf8mb4_es_0900_ai_ci

Traditional Spanish

utf8mb4_es_trad_0900_ai_ci

Swedish

utf8mb4_sv_0900_ai_ci

Turkish

utf8mb4_tr_0900_ai_ci

Vietnamese

utf8mb4_vi_0900_ai_ci

utf8mb4_0900_ai_ci also works as an accent-
insensitive, case-insensitive collation for the languages in the
following table.

Table 6 Languages for Which utf8mb4_0900_ai_ci is Suitable

Language Name

Language Code

German (dictionary order)

de

English

en

Canadian French (locale fr_CA)

fr

Irish Gaelic

ga

Indonesian

id

Italian

it

Luxembourgian

lb

Malay

ms

Dutch

nl

Portuguese

pt

Swahili

sw

Zulu

zu

utf8mb4_da_0900_ai_ci also works as an
accent-insensitive, case-insensitive collation for the languages
in the following table.

Table 7 Languages for Which utf8mb4_da_0900_ai_ci is Suitable

Language Name

Language Code

Norwegian

no

Norwegian Bokmål

nb

Norwegian Nynorsk

nn

The nonlanguage-specific utf8mb4_0900_ai_ci
and language-specific
utf8mb4_LANG_0900_ai_ci
Unicode collations each have these characteristics:

The collation is based on Unicode Collation Algorithm (UCA)
9.0.0 and Common Locale Data Repository (CLDR) v30, is
accent insensitive, and case insensitive. These
characteristics are indicated by _0900,
_ai, and _ci in the
collation name. Exception:
utf8mb4_la_0900_ai_ci is not based on
CLDR because Classical Latin is not defined in CLDR.

The collation works for all characters in the range [U+0,
U+10FFFF].

If the collation is not language specific, it sorts all
characters, including supplemental characters, in default
order (described following). If the collation is language
specific, it sorts characters of the language correctly
according to language-specific rules, and characters not in
the language in default order.

By default, the collation sorts characters having a code
point listed in the DUCET table (Default Unicode Collation
Element Table) according to the weight value assigned in the
table. The collation sorts characters not having a code
point listed in the DUCET table using their implicit weight
value, which is constructed according to the UCA.

Microsoft Windows:
For building MySQL on Windows, the toolchain now prefers 64-bit
tools when possible (previously 32-bit). This speeds up linking
and avoids issues related to limited address space with the
32-bit linker.
(Bug #80675, Bug #22900585)

CMake now causes the build process to link
with the GNU gold linker if it is available.
To suppress use of this linker, specify the
-DUSE_LD_GOLD=0CMake option.
(Bug #23759968, Bug #82163)

The WITH_EXTRA_CHARSETSCMake option has been removed. MySQL builds
are configured with all character sets by default now. Users who
want fewer character sets can edit
cmake/character_sets.cmake directly and
recompile the server.
(Bug #80005, Bug #22552125)

The required version of the Boost library for server builds has
been raised from 1.59.0 to 1.60.0.
(Bug #79380, Bug #22253921)

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.

MySQL source code now permits and uses C++11 features. To enable
a good level of C++11 support across all supported platforms,
the following minimum compiler versions now apply:

GCC: 4.8 or higher

Clang: 3.4 or higher (Xcode 7 on OS X)

Solaris Studio: 12.4 or higher (Solaris client build only)

Visual Studio: 2015

CMake: On Windows, the required Visual Studio version
results in a required CMake version of 3.2.3 or higher

On Solaris, the stlport library is no longer
used. This makes the SUNPRO_CXX_LIBRARYCMake option obsolete, so it has been
removed.

Component Notes

MySQL Server now includes a component-based infrastructure for
improving server extensibility:

A component provides services that are available to the
server and other components. (With respect to service use,
the server is a component, equal to other components.)
Components interact with each other only through the
services they provide.

Incompatible Change; InnoDB:
Previously, enabling the
innodb_read_only system
variable prevented creating and dropping tables only for the
InnoDB storage. As of MySQL 8.0. enabling
innodb_read_only prevents these
operations for all storage engines. Table creation and drop
operations modify data dictionary tables in the
mysql system database, but those tables use
the InnoDB storage engine and cannot be
modified when innodb_read_only
is enabled. The same principle applies to other table operations
that require modifying data dictionary tables, and to operations
that modify other tables in the mysql
database that use the InnoDB storage engine,
such as the grant tables and the func and
plugin tables.
(Bug #21611899)

The hardcoded memory page size of 8KB for the memory-mapped
transaction coordinator was too small for platforms such as
ARM64 and PowerPC where the page size is much larger. The server
now invokes a system call to get the page size of the current
platform rather than using a hardcoded value. A consequence for
the --log-tc-size option is that
the minimum and default values are now 6 times the page size.
Also, the value must be a multiple of the page size. Thanks to
Alexey Kopytov for the patch.
(Bug #23014086, Bug #80818)

MySQL now supports a
SET
PERSIST variant of SET statement
syntax, for making configuration changes at runtime that also
persist across server restarts. Like
SET
GLOBAL,
SET
PERSIST is permitted for any global system variable
that is dynamic (settable at runtime). The statement changes the
runtime variable value, but also writes the variable setting to
an option file named mysqld-auto.cnf in the
data directory. At startup, the server processes this file after
all other option files. For more information, see
Using Option Files, and
SET Syntax for Variable Assignment.

If you upgrade to this MySQL release from an earlier version,
you must run mysql_upgrade (and restart the
server) to incorporate this change into the Performance Schema.

The deprecated mysql_install_db program has
been removed from MySQL distributions. Data directory
initialization should be performed by invoking
mysqld with the
--initialize or
--initialize-insecure option
instead. In addition, the deprecated
--bootstrap option for
mysqld that was used by
mysql_install_db has been removed, and the
INSTALL_SCRIPTDIRCMake
option that controlled the installation location for
mysql_install_db has been removed.

Version 1 test suite code previously was located in the
mysql-test/lib/v1 directory of MySQL source
distributions. This code used
mysql_install_db and has been removed. The
MYSQL_INSTALL_DB environment variable and a
value of 1 for the MTR_VERSION environment
variable are no longer supported.

Similarly, trigger metadata previously stored in
.TRG and .TRN
files is stored in a data dictionary table and those files
no longer exist.

With the removal of .frm files, the
64KB table definition size limit imposed by the
.frm file structure is removed.

With the removal of .frm files, the
INFORMATION_SCHEMA.TABLESVERSION field now reports a hardcoded
value of 10, which is the last
.frm file version used in MySQL 5.7.

A new dictionary object cache that serves the MySQL data
dictionary stores previously accessed data dictionary
objects in memory to enable object reuse and minimize disk
I/O. An LRU-based eviction strategy is used to evict least
recently used objects from memory. The cache comprises
several partitions that store different object types. For
more information, see
Dictionary Object Cache.

Data dictionary tables are invisible, but in most cases
there are corresponding
INFORMATION_SCHEMA tables that can be
queried instead. This enables the underlying data dictionary
tables to be changed as server development proceeds, while
maintaining a stable INFORMATION_SCHEMA
interface for application use.

Some INFORMATION_SCHEMA tables have been
reimplemented entirely as views on data dictionary tables:

Queries on those tables are now more efficient because they
obtain information from data dictionary tables rather than
by other, slower means. In particular, for each
INFORMATION_SCHEMA table that is a view
on data dictionary tables:

The server no longer must create a temporary table for
each query of the INFORMATION_SCHEMA
table.

When the underlying data dictionary tables store values
previously obtained by directory scans (for example, to
enumerate database names or table names within
databases) or file-opening operations (for example, to
read information from .frm files),
INFORMATION_SCHEMA queries for those
values now use table lookups instead. (Additionally,
even for a non-view
INFORMATION_SCHEMA table, values such
as database and table names are retrieved by lookups
from the data dictionary and do not require directory or
file scans.)

Indexes on the underlying data dictionary tables permit
the optimizer to construct efficient query execution
plans, something not true for the previous
implementation that processed the
INFORMATION_SCHEMA table using a
temporary table per query.

The preceding improvements also apply to
SHOW statements that display
information corresponding to the
INFORMATION_SCHEMA tables that are views
on data dictionary tables. For example,
SHOW DATABASES displays the
same information as the
SCHEMATA table.

For INFORMATION_SCHEMA queries that
retrieve table statistics, the server now can use statistics
cached in INFORMATION_SCHEMA tables, or
obtain the latest statistics directly from storage engines.
The information_schema_stats system
variable controls which statistics source the server uses.

When information_schema_stats is
CACHED (the default), the server uses
cached statistics stored in the
STATISTICS and
TABLES tables.

When information_schema_stats is
LATEST, the server obtains statistics
directly from storage engines. In this case, the server
treats queries on
STATISTICS and
TABLES as queries for the
latest statistics stored in the
STATISTICS_DYNAMIC and
TABLES_DYNAMIC tables.

The foreign_keys and
foreign_key_column_usage tables now store
foreign key information. The standard SQL way to obtain
foreign key information is by using the
INFORMATION_SCHEMAREFERENTIAL_CONSTRAINTS and
KEY_COLUMN_USAGE tables; these
tables are now implemented as views on the
foreign_keys,
foreign_key_column_usage, and other data
dictionary tables.

For some foreign key errors, the server now produces more
appropriate and more informative error messages.

Note

Incompatibility:
Previously, MySQL supported foreign key names longer than
64 characters. Foreign key names as stored in the
foreign_keys and
foreign_key_column_usage tables are a
maximum of 64 characters, per the SQL standard, so longer
foreign key names are no longer permitted.

Because the data dictionary provides information about
database objects, the server no longer checks directory
names in the data directory to find databases. Consequently,
the --ignore-db-dir option and
ignore_db_dirs system variable are
extraneous and have been removed. Update system
configurations and application programs accordingly.

System table changes:

Many system tables have been converted from
MyISAM (nontransactional) tables to
InnoDB (transactional) tables. For
example, as discussed elsewhere in these release notes,
the grant tables are now InnoDB
tables. Other examples follow.

The func table that stores
user-defined function information in the
mysql system database now is an
InnoDB (transactional) table.
Previously, it was a MyISAM
(nontransactional) table.

Previously, information about stored routines and events
was stored in the proc and
event tables of the
mysql system database. Those tables
are no longer used. Instead, information about stored
routines and events is stored in the
routines, events,
and parameters data dictionary tables
in the mysql system database. The old
tables used the MyISAM
(nontransactional) storage engine. The new tables use
the InnoDB (transactional) engine.

Previously, creating a stored routine that contained
illegal characters produced a warning. This is now an
error.

To permit access to system tables (for example, time
zone or log tables) to be distinguished from access to
nonsystem tables, the server uses the Locking
system tables and Opening system
tables thread states rather than the
System lock and Opening
tables thread states. See
General Thread States.

InnoDB changes:

Persistent InnoDB tablespaces now
include transactional storage for Serialized
Dictionary Information (SDI), which is
dictionary object data in serialized form. Along with
the disappearance of .frm and
trigger metadata files, mentioned previously, you might
notice the appearance of .SDI
files. These are serialized dictionary information
files. SDI transactional storage is reserved for an
in-progress feature not yet fully implemented.

A new command-line utility, ibd2sdi,
is used to extract serialized dictionary information
(SDI) from persistent InnoDB
tablespaces. SDI data is not present in persistent
InnoDB tablespaces in this release.
The ibd2sdi utility is reserved for
future use.

To upgrade to MySQL 8.0 from MySQL 5.7, you must perform
the upgrade procedure described at
Upgrading MySQL.

Downgrading from MySQL 8.0 to MySQL 5.7 is only
supported using the logical downgrade method (a
mysqldump downgrade). In-place
downgrades are not supported.

(Bug #80481, Bug #22811659)

Data Type Notes

Bit functions and operators comprise
BIT_COUNT(),
BIT_AND(),
BIT_OR(),
BIT_XOR(),
&,
|,
^,
~,
<<,
and
>>.
Prior to MySQL 8.0, bit functions and operators required
BIGINT (64-bit integer) arguments
and returned BIGINT values, so
they had a maximum range of 64 bits.
Non-BIGINT arguments were
converted to BIGINT prior to
performing the operation and truncation could occur. Now bit
functions and operators permit binary string type arguments
(BINARY,
VARBINARY, and the
BLOB types) and return a value of
like type, which enables them to take arguments and produce
return values larger than 64 bits. Nonbinary string arguments
are converted to BIGINT and
processed as such, as before.

Permitting binary string arguments for bit functions and
operators makes it easier not only to manipulate larger values,
but to perform bit operations not easily done previously on
certain types of data, such as UUID and IPv6 values. For
examples, see Bit Functions and Operators.

An implication of this change in behavior is that bit operations
on binary string arguments might produce a different result in
MySQL 8.0 than in 5.7. For information about how to prepare in
MySQL 5.7 for potential incompatibilities between MySQL 5.7 and
8.0, see Bit Functions and Operators, in
MySQL 5.7 Reference Manual.

InnoDB:
The storage engine interface now enables the optimizer to
provide information about the size of the record buffer to be
used for scans that the optimizer estimates will read multiple
rows. The buffer size can vary based on the size of the
estimate. InnoDB uses this variable-size
buffering capability to take advantage of row prefetching, and
to reduce the overhead of latching and B-tree navigation.
Previously, InnoDB used a small, fixed-size
buffer.

The optimizer now supports table-level MERGE
and NO_MERGE hints for specifying whether
derived tables or views should be merged into the outer query
block or materialized using an internal temporary table.
Examples:

MySQL now supports invisible indexes. An invisible index is not
used by the optimizer at all, but is otherwise maintained
normally. Indexes are visible by default. Invisible indexes make
it possible to test the effect of removing an index on query
performance, without making a destructive change that must be
undone should the index turn out to be required. This feature
applies to InnoDB tables, for indexes other
than primary keys.

To control whether an index is invisible explicitly for a new
index, use a VISIBLE or
INVISIBLE keyword as part of the index
definition for CREATE TABLE,
CREATE INDEX, or
ALTER TABLE. To alter the
invisibility of an existing index, use a
VISIBLE or INVISIBLE
keyword with the ALTER TABLE ... ALTER INDEX
operation. For more information, see
Invisible Indexes.

The mysql system database now contains a
column_stats table designed to store
statistics about column values. For more information, see
Optimizer Statistics.

Packaging Notes

Development milestone releases in previous MySQL series were
numbered using a suffix of
-mN, to indicate
development milestone N. In MySQL
8.0, development releases use the suffix
-dmr. For example, this release of MySQL is
numbered 8.0.0-dmr.
(Bug #80408, Bug #22748154)

As a consequence of the use of C++11 features described
elsewhere in these release notes, the following packaging
changes have been made:

Support for Red Hat Enterprise Linux 5 and Oracle Linux 5
RPMs has been dropped

Generic binary tarball builds have been moved to Red Hat
Enterprise Linux 6

Parser Notes

The parser rules for SELECT and
UNION were refactored to be more
consistent (the same SELECT
syntax applies uniformly in each such context) and reduce
duplication. Several user-visible effects resulted from this
work:

The parser better conforms to the documented permitted
placement of the SQL_CACHE and
SQL_NO_CACHE query modifiers.

Left-hand nesting of unions, previously permitted only in
subqueries, is now permitted in top-level statements. For
example, this statement is now accepted as valid:

(SELECT 1 UNION SELECT 1) UNION SELECT 1;

(Bug #11746363, Bug #25734)

The parser rules for CREATE TABLE
were refactored to be context independent and improve
maintainability and extensibility. Several user-visible effects
resulted from this work:

For generated columns, including NOT NULL
NULL resulted in a column that included the
NOT NULL attribute, which differed from
nongenerated columns. Such definitions now use the final
attribute NULL, resulting in a nullable
column (consistent with nongenerated columns).

Previously, the DIGEST and
DIGEST_TEXT columns in the Performance Schema
events_statements_current table
were populated only after statement execution ended. Now, the
columns are populated just after parsing and before statement
execution begins. This enables monitoring applications to access
statement digest information during statement execution.
(Bug #23336542)

Previously, Performance Schema optimizations focused on reducing
the overhead involved in collecting monitoring data.
Complementing that earlier work, overhead now is also reduced
for Performance Schema queries that retrieve that data. This is
achieved by the addition of indexes to most Performance Schema
tables, which gives the optimizer access to execution plans
other than full table scans. These indexes also improve
performance for related objects, such as
sys schema views that use those
tables. For more information, see
Optimizing Performance Schema Queries.

The size of the ROLE column of the
setup_actors Performance Schema
table was increased from 16 to 32 characters.

Security Notes

The
validate_password_check_user_name
system variable is now enabled by default rather than disabled.
This means that when the validate_password
plugin is enabled, by default it now rejects passwords that
match the current session user name.

For the C API, MYSQL_OPT_SSL_ENFORCE and
MYSQL_OPT_SSL_VERIFY_SERVER_CERT options for
mysql_options() correspond to
the client-side --ssl and
--ssl-verify-server-cert options and have been
removed. Use MYSQL_OPT_SSL_MODE with an
option value of SSL_MODE_REQUIRED or
SSL_MODE_VERIFY_IDENTITY instead.

Spatial Data Support

Spatial functions for import and export of Well-Known Text (WKT)
values used MySQL 'GEOMETRYCOLLECTION()'
nonstandard syntax rather than OpenGIS
'GEOMETRYCOLLECTION EMPTY' standard syntax.
Now both syntaxes are understood for import and the standard
syntax is used for export. See
Functions That Create Geometry Values from WKT Values.
(Bug #23632147, Bug #81964)

The ST_X() and
ST_Y() spatial functions now
permit an optional second argument that specifies an X or Y
coordinate value, respectively. With two arguments, the function
result is the point value from the first argument with the
appropriate coordinate modified. In addition,
ST_X() and
ST_Y() with a single argument now
are stricter and produce an
ER_UNEXPECTED_GEOMETRY_TYPE
error rather than returning NULL if the
argument is a valid geometry but not a point. For more
information, see Point Property Functions.

The ST_SRID() spatial function
now permits an optional second argument that specifies a SRID
value. With two arguments, the function result is the geometry
value from the first argument with its SRID modified according
to the second argument. For more information, see
General Geometry Property Functions.

In MySQL 5.7, several spatial functions available under multiple
names were deprecated to move in the direction of making the
spatial function namespace more consistent, the goal being that
each spatial function name begin with ST_ if
it performs an exact operation, or with MBR
if it performs an operation based on minimum bounding
rectangles. The deprecated functions have now been removed to
leave only the corresponding ST_ and
MBR functions:

These functions are removed in favor of the
MBR names: Contains(),
Disjoint(), Equals(),
Intersects(),
Overlaps(), Within().

mysql-test-run.pl now supports a
--do-suite option, which is similar to
--do-test but permits specifying entire suites
of tests to run.
(Bug #24350345)

The mysqltestrmdir
command fails if the directory to be removed contains any files
or directories. To enable recursive removal of a directory as
well as its contents, if any, mysqltest now
supports a force-rmdir command.
(Bug #24316799)

Two new test suite options make it easier to debug test cases:

mysql-test-run.pl supports a
--mysqltest=options
option that enables options to be passed to
mysqltest.

mysqltest supports a
--trace-exec option that causes it to
immediately print output from executed programs to
stdout.

mysql-test-run.pl now recognizes the
MTR_CTEST_TIMEOUT environment variable. If
set, the value is a timeout in seconds to pass to
ctest unit test commands.
(Bug #21821049, Bug #21278845)

For test cases in the MySQL test suite, it was previously
possible to use symbolic error names for the
--error command only for server errors. This
is now also possible for client errors. For example:

The Protobuf decoder class limited the number
of nested objects to 50 (the default value).
(Bug #23707238, Bug #82025)

The statement list_objects incorrectly
reported a table as a collection.
(Bug #23631240)

The create_collection statement created a
collection table with a unique key index on the
'_id' column instead of on the primary key.
(Bug #23284569)

Functionality Added or Changed

Incompatible Change; Partitioning:
The generic partitioning handler has been removed from the MySQL
server. As part of this change, mysqld no
longer supports the --partition and
--skip-partition options, and the server can no
longer be built using
-DWITH_PARTITION_STORAGE_ENGINE.
partition is also no longer displayed in the
output of SHOW PLUGINS, or shown
in the INFORMATION_SCHEMA.PLUGINS
table.

In order to support partitioning of a given table, the storage
engine used for the table must now provide its own
(“native”) partitioning handler.
InnoDB is the only storage engine
supported in MySQL 8.0 which includes a native partitioning
handler. An attempt to create partitioned tables in MySQL 8.0
using any other storage engine fails. (The
NDB storage engine used by MySQL
NDB Cluster also provides its own partitioning handler, but is
currently not supported by MySQL 8.0.)

Effects on upgrades.
The direct upgrade of a partitioned table using a storage
engine other than InnoDB (such as
MyISAM) from MySQL 5.7 (or
earlier) to MySQL 8.0 is not supported. There are two options
for upgrading such a table to be compatible with MySQL 8.0,
listed here:

Change the storage engine used for the table to
InnoDB, using
ALTER TABLE ...
ENGINE=INNODB; this leaves the table's
partitioning in place. At least one of these operations must
be performed for any partitioned
non-InnoDB table, prior to upgrading the
server to MySQL 8.0. Otherwise, such a table cannot be used
following the upgrade.

An analogous situation is met when importing databases from a
dump file that was created in MySQL 5.7 or earlier using
mysqldump into a MySQL 8.0 server, due to the
fact that table creation statements that would result in a
partitioned table using a storage engine without such support
fail with an error in MySQL 8.0. For this reason you must ensure
that any statements in the dump file creating partitioned tables
do not also specify an unsupported storage engine. You can do
this either by removing any references to partitioning from
CREATE TABLE statements that use a value for
the STORAGE ENGINE option other than
InnoDB, or by specifying the storage engine
as InnoDB (or allowing
InnoDB to be used by default).

Important Change; InnoDB:
The following InnoDB file format
configuration parameters were deprecated in MySQL 5.7.7 and are
now removed:

innodb_file_format

innodb_file_format_check

innodb_file_format_max

innodb_large_prefix

File format configuration parameters were necessary for creating
tables compatible with earlier versions of
InnoDB in MySQL 5.1. Now that MySQL 5.1 has
reached the end of its product lifecycle, the parameters are no
longer required.

InnoDB:
A new dynamic configuration option,
innodb_deadlock_detect, can be
used to disable deadlock detection. On high concurrency systems,
deadlock detection can cause a slowdown when numerous threads
wait for the same lock. At times, it may be more efficient to
disable deadlock detection and rely on the
innodb_lock_wait_timeout
setting for transaction rollback when a deadlock occurs.
(Bug #23477773)

InnoDB:
To address contention that could occur under some workloads, the
buffer pool mutex was removed and replaced by several list and
hash protecting mutexes. Also, several buffer pool related
variables no longer require buffer pool mutex protection. Thanks
to Yasufumi Kinoshita and Laurynas Biveinis for the patch.
(Bug #20381905, Bug #75534)

InnoDB:InnoDB now avoids intermediate commits that
would occur every 10000 rows during
ALTER TABLE
ALGORITHM=COPY operations. The purpose of intermediate
commits was to speed up recovery in the case of an aborted
ALTER TABLE
ALGORITHM=COPY operation. If an
ALTER TABLE
ALGORITHM=COPY operation is aborted, the new,
uncommitted table is now dropped during DDL log recovery before
the undo log is rolled back, thereby avoiding time-consuming
data rollback for the uncommitted table. Undo logging is now
suppressed for
ALTER TABLE
ALGORITHM=COPY operations unless there is an
IGNORE clause or something else that requires
rollback capability.

If there is full-text index on the table being altered,
full-text data is inserted into full-text auxiliary tables as
the ALTER TABLE
ALGORITHM=COPY operation inserts rows into the new,
uncommitted table. Previously, full-text data was only processed
on transaction commit.
(Bug #17479594)

InnoDB:
To reduce read-write lock contention that can result from
multiple purge threads purging rows from the same table, undo
records are now grouped and assigned to different purge threads
by table ID.

InnoDB:
The innodb_stats_sample_pages system variable
was removed. innodb_stats_sample_pages was
deprecated in MySQL 5.6.3 and replaced by
innodb_stats_transient_sample_pages.

InnoDB:
When encountering index tree corruption,
InnoDB writes a corruption flag to the redo
log, which makes the corruption flag crash-safe.
InnoDB also writes in-memory corruption flag
data to an engine-private system table on each checkpoint.
During recovery, InnoDB reads corruption
flags from both locations and merges results before marking
in-memory table and index objects as corrupt.

InnoDB:
The innodb_locks_unsafe_for_binlog system
variable was removed.
innodb_locks_unsafe_for_binlog was deprecated
in MySQL 5.6.3. The READ
COMMITTED isolation level provides similar
functionality.

With this change, moving a remote tablespace while the server is
offline by manually modifying an .isl file
is no longer supported.

InnoDB:InnoDB no longer supports compressed
temporary tables. When
innodb_strict_mode is enabled
(the default),
CREATE TEMPORARY
TABLE returns an error if
ROW_FORMAT=COMPRESSED or
KEY_BLOCK_SIZE is specified. If
innodb_strict_mode is disabled,
warnings are issued and the temporary table is created using a
non-compressed row format.

With this change, all temporary tables are created in the shared
temporary tablespace, ibtmp1.

InnoDB:
The innodb_support_xa system variable, which
enables support for two-phase commit in XA transactions, was
removed. As of MySQL 5.7.10, InnoDB support
for two-phase commit in XA transactions is always enabled.

Offline relocation of a file-per-table tablespace data file
created outside of the MySQL data directory by modifying the
.isl file is no longer supported.

InnoDB:
The current maximum auto-increment counter value is now written
to the redo log each time the value changes, and it is saved to
an engine-private system table on each checkpoint. These changes
make the current maximum auto-increment counter value persistent
across server restarts. Additionally:

A server restart no longer cancels the effect of the
AUTO_INCREMENT = N table option. If you
initialize the auto-increment counter to a specific value,
or if you alter the auto-increment counter value to a larger
value, the new value is persisted across server restarts.

A server restart immediately following a
ROLLBACK
operation no longer results in the reuse of auto-increment
values that were allocated to the rolled-back transaction.

If you modify an AUTO_INCREMENT column
value to a value larger than the current maximum
auto-increment value (in an
UPDATE operation, for
example), the new value is persisted, and subsequent
INSERT operations allocate
auto-increment values starting from the new, larger value.

Replication:
There are two improvements to how a
CHANGE MASTER TO
statement is written into the error log
(mysqld.log):

Before, no commas were put between the option specifications
(for example MASTER_USER = and
MASTER_PASSWORD = ), so users who wanted
to use the statement by copy and paste had to insert the
commas manually. Commas are now inserted when the statement
is written to the error log.

When the literal “<secret>” is inserted
as a placeholder for the MASTER_PASSWORD
value, no quotes are used now, so users who forget to
replace the literal with the real password before a copy and
paste gets a syntax error immediately, instead of running
into other issues.

(Bug #18194384)

Replication:
It is now possible to restore a backup of a GTID-based
replication server because you can add GTIDs to
gtid_purged, regardless of
whether gtid_executed is empty
or not. This enables you to restore backups from GTID-based
replication servers without losing existing GTID information and
binary logs. The GTIDs to add are those which existed in
gtid_executed at the time of
taking the backup. The syntax for SET
GTID_PURGED has been extended so that SET
GTID_PURGED ="+gtid_set"
adds gtid_set to the existing
gtid_purged GTID set.

Replication:
New Performance Schema stages have been added to show the
progress of row-based replication. You can use these stages to
check the progress of slow operations in row-based replication.
Additionally you can find out which database the changes are
being applied to. This assists in troubleshooting row-based
replication issues and provides more information for performance
tuning. For more information see
Monitoring Row-based Replication

JSON:
This release adds an unquoting extraction operator
->>,
sometimes also referred to as an inline path operator, for use
with JSON documents stored in
MySQL. The new operator is similar to the
->
operator, but performs JSON unquoting of the value as well. For
a JSON column mycol and JSON path expression
mypath, the following three expressions are
equivalent:

The ->> operator can be used in SQL
statements wherever
JSON_UNQUOTE(JSON_EXTRACT()) would be
allowed. This includes (but is not limited to)
SELECT lists, WHERE and
HAVING clauses, and ORDER
BY and GROUP BY clauses.

To produce more accurate estimates, the
MEMORY storage engine now
calculates index statistics (records per key estimates) using
floating-point rather than integer arithmetic.
(Bug #23024059)

A new CMake option,
INSTALL_STATIC_LIBRARIES, enables
control over whether to install static libraries. The default is
ON. If set to OFF, these
libraries are not installed:
libmysqlclient.a,
libmysqld.a,
libmysqlservices.a.
(Bug #22891432)

The internal mysql_prepare_create_table()
server function has been refactored for improved code
maintainability and clarity. This code revision results in the
following minor changes of behavior for
CREATE TABLE and
ALTER TABLE:

Previously, the Performance Schema was not built for
libmysqld, the embedded server. This
prevented use of the SHOW STATUS
and SHOW VARIABLES statements
with show_compatibility_56=OFF
because, with that setting, those statements take their results
from Performance Schema tables. Now for
libmysqld, the required Performance Schema
tables are built (with no instrumentation collected), so that
those SHOW statements can be supported with
show_compatibility_56=OFF.
(Bug #22809694)

Several internal functions used by
JSON_CONTAINS(),
JSON_SEARCH(), and other MySQL
JSON functions created excessive numbers of local copies of
keys, values, or both, when performing inspections of JSON
objects. Such copying has been eliminated or reduced in many
cases. In addition, the lifetimes of temporary objects used by
some of these functions have been reduced. These changes should
make these and related JSON functions perform more efficiently
than previously, and with fewer resources required.
(Bug #22602142)

If the system lz4 and openssl
zlib commands are available, the
lz4_decompress and
zlib_decompress utilities are unneeded. Two
changes enable those utilities not to be built: If the new
WITH_LZ4CMake
option is set to system,
lz4_decompress is not built or installed. If
the WITH_ZLIBCMake option is set to
system, zlib_decompress is
not built or installed.
(Bug #22329851)

Source files for the MySQL strings library have been converted
from C (.c suffix) to C++
(.cc suffix). This enables stricter
compilation checks and use of C++ features in the library code.
(Bug #22124719)

Source code for the mysys library now uses
C++ rather than C to take advantage of stricter compilation
checks and permit use of C++ features.
(Bug #21881278)

The global list of connections, previously protected by a single
mutex, has been partitioned into eight parts, each protected by
its own instance of the mutex. The result is a reduction of
overhead and improved performance for connection processing. An
implication of this change for monitoring purposes is that the
Performance Schema now exposes eight different instances each of
the LOCK_thd_list mutex,
LOCK_thd_remove mutex, and
COND_thd_list condition variable.

MySQL now provides functions to manipulate UUID values and make
them easier to work with:

UUID_TO_BIN() and
BIN_TO_UUID() convert between
UUID values in string and binary formats (represented as
hexadecimal characters and
VARBINARY(16), respectively).
This permits conversion of string UUID values to binary
values that take less storage space. UUID values converted
to binary can be represented in a way that permits improved
indexing efficiency.

IS_UUID() returns 1 or 0 to
indicate whether its argument is a valid string-format UUID
value.

The server now relies on storage engines to clean up temporary
tables left from previous server runs. InnoDB
does this by discarding the temporary tablespace on restart.
MyISAM and other similar storage engines
still rely on scanning the temporary directory to detect
leftover tables, by looking for files belonging to these engines
with a certain name pattern.

The deprecated mysql_shutdown() C API
function and corresponding COM_SHUTDOWN
client/server protocol command have been removed. Instead, use
mysql_query() to execute a
SHUTDOWN statement.

The server no longer performs conversion of pre-MySQL 5.1
database names containing special characters to 5.1 format with
the addition of a #mysql50# prefix. Because
these conversions are no longer performed, the
--fix-db-names and
--fix-table-names options for
mysqlcheck, the UPGRADE DATA
DIRECTORY NAME clause for the
ALTER DATABASE statement, and the
Com_alter_db_upgrade status variable have
been removed.

Upgrades are supported only from one major version to another
(for example, 5.0 to 5.1, or 5.1 to 5.5), so there should be
little remaining need for conversion of older 5.0 database names
to current versions of MySQL. As a workaround, upgrade a MySQL
5.0 installation to MySQL 5.1 before upgrading to a more recent
release.

InnoDB:
The restriction that required the first undo tablespace to use
space_id 1 was removed to avoid
space_id conflicts with existing tablespaces
during upgrade. The first undo tablespace can now use a
space_id other than 1.
space_id values for undo tablespaces are
still assigned in a consecutive sequence.
(Bug #23517560)

InnoDB:
Internal accessor functions for iterating the indexes of a table
were replaced with accessor methods. Dead code was removed.
(Bug #23336108)

InnoDB:
The mysql.innodb_index_stats and
mysql.innodb_table_stats table definitions,
which were previously created by an SQL script, are now
hard-coded. As a result, the
dict_table_schema_check function is longer
required and was removed.
(Bug #23336079)

InnoDB:
The ut_snprint function was replaced by the
C++11 snprintf function.
(Bug #23329353)

InnoDB:
For consistency, instances of ulint in
InnoDB code were replaced with
space_id_t and page_no_t
data types.
(Bug #23297169)

InnoDB:
Use of boost::atomic in InnoDB code was
replaced with std::atomic.
(Bug #23280649)

InnoDB:
MySQL binaries were not built with the NUMA feature.
(Bug #23259754)

InnoDB:
References to UNIV_NONINL and
UNIV_MUST_NOT_INLINE were removed. The
fut0fut.cc and
ut0byte.cc files, which were only necessary
when UNIV_NONINL was defined, were also
removed.
(Bug #23150562)

InnoDB:DBUG_OFF compile-time flags were replaced by
UNIV_DEBUG flags. To improve error log
output, ut_dbg_assertion_failed() now uses
sql_print_error() to display the file name,
line number, and message in a single line. The thread ID is
displayed in a subsequent line.
(Bug #22996442, Bug #23028144)

InnoDB:
Building InnoDB with C++11 returned
“register” deprecation warnings. Handling of
“register” deprecation warnings remained in the
code after the deprecated “register” keyword was
removed. Also, an unused declaration of
yyset_extra() was removed.
(Bug #22292704)

InnoDB:
A SPACE_ID column was added to the
INNODB_CACHED_INDEXES table. The
INDEX_ID value is no longer a global unique
identifier.
(Bug #22172026)

InnoDB:
A purge thread open table callback for virtual columns raised an
assertion due to an unexpected data dictionary table latch. As a
temporary workaround, purge is temporarily disabled for virtual
generated columns. This temporary workaround may cause b-tree
expansion due to unpurged delete-marked records for indexes on
virtual columns.
(Bug #22153217)

InnoDB:
Creating a table with a full-text index and a foreign key
constraint failed when
foreign_key_checks was
disabled.
(Bug #22094601, Bug #78955)

References: This issue is a regression of: Bug #16845421.

InnoDB:
The ha_innobase::m_primary_key field was
removed. It was redundant. A boolean predicate,
TABLE_SHARE::is_missing_primary_key(), was
added.
(Bug #21928734, Bug #78662)

InnoDB:
Log buffer contention was reduced with the addition of a second
buffer, allowing for concurrent log buffer writing and flushing.
A new mutex was added to protect log buffer flushing. Thanks to
Zhai Weixiang for the patch.
(Bug #21352937, Bug #77094)

InnoDB:
The SysTablespace::parse_units() function now
returns the number of pages in a file instead of the number of
megabytes. The
SysTablespace::normalize_size() function was
removed. Error messages in
SysTablespace::parse_params() were revised.
(Bug #21040199, Bug #76949)

InnoDB:
For persistent tables, the internal unique identifier for
InnoDB indexes (index_id)
now includes a tablespace identifier
(space_id,index_id). This
change makes index identifiers unique at the tablespace level as
well as the InnoDB instance level, and
supports future work related to index identifier allocation.
(Bug #20737524, Bug #76392)

InnoDB:
Code related to innochecksum was cleaned up
and reorganized. Checksum functionality is now located in
buf0checksum.cc.
(Bug #20518099)

InnoDB:__attribute__((nonnull)) was removed from
InnoDB code. The attribute is no longer
permitted by InnoDB coding guidelines.
(Bug #20468234)

InnoDB:
A new struct was added to provide a logical interface for
handling and manipulating external BLOB field
references.
(Bug #18195972)

TRUNCATE TABLE is temporarily
non-atomic. A server exit during a
TRUNCATE TABLE operation can
result in a dropped table and orphaned foreign key
constraints in the InnoDBSYS_FOREIGN and
SYS_FOREIGN_COLS system tables.

The InnoDBmemcached
plugin flush_all command invokes
DELETE instead of
TRUNCATE TABLE.
DELETE has a higher overhead
cost than FLUSH TABLES since
it involves undo-logging, delete-marking, and eventually
purging each deleted row.

A log checkpoint that occurred for internal truncate table
operations on file-per-table tablespaces was replaced by a
log flush.

Partitioning:
In some cases, an issue with partition pruning being attempted a
second time during optimization after all partitions had already
been pruned at parsing time led to an assert.
(Bug #23194259)

Partitioning:
A partitioned table whose table name and any partition name had
a combined length in excess of 61 characters could not be
imported from a backup created using
mysqldump. When the table also employed
subpartitioning, then the combined length of the table name, any
partition name, and the name of any subpartition of this
partition could not exceed 57 characters without triggering the
same issue.

This was due to the fact that the internal
mysql.innodb_table_stats table allowed a
maximum of 64 characters for the column used to store the table
name, even though InnoDB stores, for a partitioned or
subpartitioned table, a row in
innodb_table_stats for each partition or
subpartition wherein the value actually used to represent the
table name follows the pattern
table_name#P#partition_name
or
table_name#P#partition_name#SP#subpartition_name,
respectively. This issue is fixed by changing the definition of
the innodb_table_stats to accommodate the
maximum combined length of these attributes plus
#P# and #SP# (199
characters).
(Bug #72061, Bug #18416479)

Replication:
In Slave_worker::write_info(),
DBUG_ENTER() had
“Master_info::write_info” as its
argument instead of
“Slave_worker::write_info”. This
fix corrects the argument. Thanks to Stewart Smith for the
patch.
(Bug #21658067, Bug #78133)

JSON:CHECKSUM TABLE calculated the
checksums for JSON values using
the memory addresses of the values rather than the values
themselves, which made the checksum vary. Now in such cases the
calculation is based on the actual JSON
value, and not on that value's address.
(Bug #23535703)

JSON:
Passing NULL to a stored procedure expecting
a JSON parameter led to an
assertion failure in debug builds.
(Bug #23209914)

JSON:
Parsing of JSON path arguments
failed to distinguish between a NULL path and one that was
syntactically invalid.

This has been changed so that parsing of these paths now clearly
distinguishes between valid non-NULL paths, NULL paths, and
invalid paths.
(Bug #22816576)

JSON:
For debug builds, an assertion could be raised when the server
created a temporary table to hold
JSON objects.
(Bug #22782948)

JSON:
Queries that executed a JSON
function that raised an error could cause a server exit.
(Bug #22253965)

Renaming a table to be part of a nonexistent database failed
(correctly), but with an Unknown error
message. A proper error message is now produced; this was
corrected as part of the data dictionary implementation.
(Bug #25167507, Bug #84000)

For segmentation faults on FreeBSD, the server did not generate
a stack trace.
(Bug #24566529, Bug #23575445, Bug #81827)

On macOS, stack trace demangling now occurs for builds compiled
using Clang, just as for GCC.
(Bug #23606094, Bug #81908)

libevent was built on macOS even when not
needed.
(Bug #23228287, Bug #81311)

A function that returns a JSON
value could cause a server exit if called as part of a
CASE statement in a stored
procedure.
(Bug #23212765)

Previously, different values were reported by
SHOW ENGINE
PERFORMANCE_SCHEMA STATUS and SELECT * FROM
performance_schema.memory_summary_global_by_event_name
for total memory used in the Performance Schema. The memory for
scalable buffer pages, instrumented as
memory/performance_schema/scalable_buffer,
was missing from the
SHOW ENGINE
STATUS output. That statement now includes the missing
memory, displayed as
(pfs_buffer_scalable_container).memory.
(Bug #23104498)

The -fexpensive-optimizations option to GCC
caused ARM64 and PowerPC builds to compute floating-point
operations slightly differently from other platforms. This
option was enabled by -O2 and higher
optimization levels. The option now is disabled on platforms
negatively affected by it.
(Bug #23046775)

A prepared statement that used a parameter in the select list of
a derived table that was part of a join could cause a server
exit.
(Bug #22392374, Bug #24380263)

Some grant tables did not account for the increase in maximum
user name length from 16 to 32 characters in MySQL 5.7.8.
(Bug #22379607, Bug #79680)

Re-evaluation of a generated column expression could cause
access to previously freed memory and a server exit.
(Bug #22346120)

HANDLER read statements that
searched an index when the target index value was not stored
into the row buffer successfully could cause a server exit.
(Bug #22321965)

Improper handling of numeric-to-ZEROFILL
conversion for NULL values could lead to a
server exit.
(Bug #22281205)

Using a subquery containing a row constructor to set a variable
in a SET
statement could cause a server exit.
(Bug #22276843)

If the SQL mode did not include
ALLOW_INVALID_DATES, a query
that contained invalid_date
IN (subquery) and was
handled by subquery materialization could cause a server exit.
(Bug #22262843)

For the embedded server, the code following the check for
invalid arguments was invoked with missing or incorrect
arguments, which could lead to an improper exit.
(Bug #22262706)

On OS X, vio_io_wait() used
select(), limiting the number of file
descriptors to 1024. Now kqueue event notification is used
instead to avoid this limit. FreeBSD was changed to use kqueue
as well.
(Bug #22244911)

CMake configuration was adjusted to check for
-Wxxx compiler
options instead of
-Wno-xxx because the
latter produce false positives for GCC.
(Bug #21881753)

There could be discrepancies between the values of
INFORMATION_SCHEMA.EVENTS.LAST_EXECUTED and
mysql.event.last_executed. This no longer
occurs. Event information is stored in the
mysql.events data dictionary table, which is
invisible, so that INFORMATION_SCHEMA.EVENTS
is the sole interface to event metadata.
(Bug #21374010)

Views could evaluate user-defined or SQL functions before
evaluating restrictions from the view definition.
(Bug #20933307)

With -DENABLE_DTRACE=ON,
CMake did not check whether a working DTrace
installation was present. Now it checks and aborts if DTrace
cannot be found.
(Bug #20671056)

If given a relative path name for the
--log-error option,
mysqld could sent stdout
and stderr to the wrong location.
(Bug #20609063)

Evaluation of LEAST() and
GREATEST() could use too small a
sort buffer for datetime and string literals, causing an
assertion to be raised.
(Bug #20565160)

The range of error numbers for errors that are new in MySQL 8.0
has been designated to begin with 3500.
(Bug #20538173)

Debian packaging was updated not to set the
sql_mode system variable in
my.cnf.
(Bug #20535729)

Event loading from the mysql.event system
table could fail if the
PAD_CHAR_TO_FULL_LENGTH SQL
mode was enabled.
(Bug #20073523, Bug #74947)

Statements such as INSERT and
LOAD DATA that use the
REPLACE or IGNORE keyword
to handle duplicate records could affect subsequent operations.
(Bug #20017428)

CREATE TABLE ... SELECT where
non-BIT data was selected from the source
table into a BIT column in the destination
table could cause a server exit.
(Bug #19930894)

Compilation failed on OS X when MySQL was configured with
-DMYSQL_MAINTAINER_MODE=1 and
compiled with clang/Xcode 6.0.
(Bug #19694515, Bug #74100)

For CHANGE MASTER TO statements
rewritten to filter the password before being written to the
general query log, any MASTER_AUTO_POSITION
clause was lost.
(Bug #19622609)

Use of the VALUES() function in a
SELECT clause could result in a
server exit.
(Bug #19601973)

Using MATCH ... AGAINST to compare a
character column and an aggregate function could cause a server
exit.
(Bug #17865492)

Slightly different values for the number of connections could be
reported in various information sources, such as the
Connections status variable,
Performance Schema threads and
global_status tables, and
SHOW PROCESSLIST statement.
(Bug #17666696)

A query with a subquery containing a set operation with an outer
reference might cause a server exit.
(Bug #17270896)

Using GRANT to change a password
for an invalid user produced an error, but also updated the
mysql.user system table.
(Bug #17180985)

The parser for spatial WKT data accepted numbers such as
0.23 but not .23, the
equivalent value without the leading zero. Now both formats are
accepted.
(Bug #17167633)

Previously, if a client attempted to send connection attribute
key/value pairs that in aggregate had a size larger than the
value of the
performance_schema_session_connect_attrs_size
system variable, the Performance Schema truncated the attribute
data. In addition, the Performance Schema wrote this message to
the error log if the
log_warnings system variable
was greater than zero:

[Warning] Connection attributes of length N were truncated

This message was not helpful to a DBA attempting to determine
the problematic client, so several changes have been made to
connection attribute handling:

Truncation of connection attributes still occurs for
excessive data, but the log message is more informative. It
includes the number of bytes lost, the connection
identifier, and information about the client user. The
additional information should enable DBAs to more easily
identify clients for which attribute truncation occurred.

When truncation occurs, a _truncated
attribute is added to the session attributes with a value
indicating how many bytes were lost, if the attribute buffer
has sufficient space. This enables the Performance Schema to
expose per-connection truncation information in the
connection attribute tables.

Some INFORMATION_SCHEMA tables had
suboptimal column types and sizes. Such tables that are now
views on data dictionary tables in the
mysql system database have more
appropriate column definitions.

Queries on INFORMATION_SCHEMA tables that
resulted in directory scans to determine database or file
names no longer do so, but instead read database and table
names from the data dictionary.

Queries on INFORMATION_SCHEMA tables that
opened .frm files to obtain table
metadata no longer do so, but instead read this information
from the data dictionary.

For comparisons of database or table names in
INFORMATION_SCHEMA queries, using
COLLATE to force a given collation worked
only if applied to the INFORMATION_SCHEMA
table column, but not if applied to the comparison value.

For abnormal server exit on Windows, the server previously
created a minidump file named
module_name.dmp,
where module_name is the name of the
server executable file. To prevent earlier minidump files from
being overwritten, minidump file names now include the process
ID and have the form
module_name..piddmp;
for example, mysqld.exe.7296.dmp.
(Bug #12779463)

For queries on INFORMATION_SCHEMA
tables, comparisons of schema and table names could be case
sensitive or insensitive, depending on the characteristics of
the underlying file system and the
lower_case_table_names system
variable value. Furthermore, it was ineffective to provide a
COLLATE clause to change the comparison
properties because that clause was ignored. This has been
changed so that COLLATE is no longer ignored
and can be used to obtain the desired comparison properties.
(Bug #11748044, Bug #34921)

Geometry import functions that took an SRID parameter cast it to
an unsigned 32-bit integer without warning or error, so negative
values or values larger than unsigned 32-bit integer range were
silently converted to a number within the range. Now, all
geometry functions that take the SRID as a parameter check that
it is within unsigned 32-bit integer range and produce an
ER_DATA_OUT_OF_RANGE error if
not. This also applies to GeoJSON and GeoHash functions that
previously checked that the parameter was within range but
returned a different error code.
(Bug #80499, Bug #22819614)

If rounding occurred while storing a predicate value, the range
optimizer might not return correct results for the
< and <= operators.
(Bug #80244, Bug #22661012)

For the mf_iocache unit test, add a missing
va_end(), fix a memory leak by calling
my_end(), and add a target for the test.
Thanks to Daniel Black for the patch on which these changes were
based.
(Bug #80085, Bug #22578670)

SELECT DISTINCT SUBSTR() could incorrectly
discard values as duplicates for large position or length
arguments. The same issue also affected
LEFT() and
RIGHT().
(Bug #80047, Bug #22565155)

The REPEAT() function did not
properly handle output from the
SUBSTR() function.
(Bug #79695, Bug #22391186)

The JSON_TYPE() function now
shows the type of BIT literals
cast to JSON as BLOB, rather than
BIT.
(Bug #79308, Bug #22297987)

Configuring MySQL with the
-DWITH_UBSAN=ONCMake option produced a server that was not
fully functional.
(Bug #79238, Bug #22194071)

sql_common.h, a header file included in
MySQL distributions, included and was therefore dependent on
hash.h, a header file not included in MySQL
distributions. This resulted in compilation failures. To
eliminate this dependency, sql_common.h was
modified to no longer include hash.h.
(Bug #79237, Bug #22187997, Bug #70672, Bug #17633467)

Timers used for checking maximum statement execution time were
initialized even when the server was started with the
--help option. If
--help is given, this is no
longer done.
(Bug #79182, Bug #22172389)

The optimizer failed when trying to optimize away expressions of
the form IF(true, '2015-01-01', '2015-01-01') IS NOT
NULL.
(Bug #79114, Bug #22148586)

Subtraction of an unsigned decimal could return a negative
value, but with metadata type information of UNSIGNED
BINARY. Subtraction for unsigned decimal subtraction
now is handled the same way as for unsigned integer: Produce an
ER_DATA_OUT_OF_RANGE error if
the result is negative, unless the
NO_UNSIGNED_SUBTRACTION SQL
mode is enabled.
(Bug #78914, Bug #22083757)

Handling by the HEX() function of
numbers larger than 264 was improved.
(Bug #78828, Bug #22297983)

CREATE TABLE reported an
incorrect error if a very long or incorrect path name was
specified for the DATA DIRECTORY or
INDEX DIRECTORY table option. Now
ER_PATH_LENGTH or
ER_WRONG_VALUE are reported for
those cases.
(Bug #76635, Bug #20857556)

For some instances of failure to prepare an XA transaction,
incomplete transaction cleanup could raise an assertion.
(Bug #75809, Bug #20488921)

mysqld could attempt to close an invalid
socket file descriptor. Thanks to Zhai Weixiang for the patch.
(Bug #75778, Bug #20504513)

A statement of the following form converted the table data to
latin1, but also changed the table default
character set to latin1 and ignored the
utf8 clause:

ALTER TABLE tbl_name CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1;

Thanks to Daniel Black for the patch.
(Bug #75320, Bug #20279241)

In mysqld.cc, the
abort_loop variable was quantified with
volatile, which on some platforms could
result in changes not being seen immediately in threads running
on different cores. Thanks to Stewart Smith for the patch.
(Bug #74846, Bug #20134637)

Calling a procedure which created a view from a trigger, or
creating a function that called a procedure that executed
RENAME TABLE could, under certain
circumstances, raise an assertion.
(Bug #74740, Bug #19988193, Bug #21198646)

Timestamps for server-side prepared statements could be written
to the binary log up to a second behind timestamps for the
corresponding nonprepared statements, leading to time value
differences between master and slave servers.
(Bug #74550, Bug #19894382, Bug #25187670)

For dynamic storage engine plugins, DROP
TABLE, TRUNCATE TABLE,
and RENAME TABLE did not work due
to incorrectly determining the engine from the
.frm file.
(Bug #74277, Bug #19902868)

Executed prepared statements are logged with
? parameter markers replaced by data values.
Construction of the logged string was inefficient and has been
improved.
(Bug #73056, Bug #20955496)

Assignment by a plugin to its thread variables of string type
could leak memory.
(Bug #71759, Bug #19917521)

Grouping with a view could produce an
ER_INVALID_GROUP_FUNC_USE error
(“Invalid use of group function”) when selecting
from the base table did not.
(Bug #70220, Bug #17406425)

Test cases that were intended to be storage engine-agnostic but
were actually using a specific engine were corrected.