This is a milestone release, for use at your own risk. 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
mysqlsystem database are now
InnoDB(transactional) tables. Previously, these were
MyISAM(nontransactional) tables. This change applies to these tables:
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:
SET PASSWORDis 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
If MySQL is upgraded from an older version but the grant tables have not been upgraded from
InnoDB, the server considers them read only and account-management statements produce an error.
Due to the change of storage engine from
ORDER BYon grant tables can produce different row orders than previously. If a query result must have specific row ordering characteristics, include an
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.
For more information, see Using Roles.Note
ROLEis now a reserved word and cannot be used as an identifier without identifier quoting.
libmysqlclientshared library major version number is increased from 20 (used in MySQL 5.7) to 21 for MySQL 8.0. (Bug #77600, Bug #21363863)
utf8mb4Unicode character set has a new general collation named
utf8mb4also has several new language-specific collations with characteristics similar to
utf8mb4_0900_ai_ciexcept 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 4 utf8mb4 UCA 9.0.0 Collations
Language Collation Croatian
German phone book order
utf8mb4_0900_ai_cialso works as an accent insensitive, case insensitive collation for the languages in the following table.
Table 5 Languages for Which utf8mb4_0900_ai_ci is Suitable
Language Name Language Code German (dictionary order) de English en French fr Irish Gaelic ga Indonesian id Italian it Luxembourgian lb Malay ms Dutch nl Portuguese pt Swahili sw Zulu zu
utf8mb4_da_0900_ai_cialso works as an accent insensitive, case insensitive collation for the languages in the following table.
Table 6 Languages for Which utf8mb4_da_0900_ai_ci is Suitable
Language Name Language Code Norwegian no Norwegian Bokmål nb Norwegian Nynorsk nn
utf8mb4_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
_ciin the collation name. Exception:
utf8mb4_la_0900_ai_ciis 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.
Characters in contraction sequences are treated as separate characters.
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)
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)
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.cmakedirectly 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)
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
stlportlibrary is no longer used. This makes the
SUNPRO_CXX_LIBRARYCMake option obsolete, so it has been removed.
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.
A loader service registers installed components in the
mysql.componentsystem table, and installs registered components during the startup sequence for subsequent server restarts.
For general information about the component infrastructure and its SQL-level interface, see Server Components. For information about the internal implementation of components, see http://dev.mysql.com/doc/dev/mysql-server/.
Incompatible Change; InnoDB: Previously, enabling the
innodb_read_onlysystem variable prevented creating and dropping tables only for the
InnoDBstorage. As of MySQL 8.0. enabling
innodb_read_onlyprevents these operations for all storage engines. Table creation and drop operations modify data dictionary tables in the
mysqlsystem database, but those tables use the
InnoDBstorage engine and cannot be modified when
innodb_read_onlyis enabled. The same principle applies to other table operations that require modifying data dictionary tables, and to operations that modify other tables in the
mysqldatabase that use the
InnoDBstorage engine, such as the grant tables and the
plugintables. (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-sizeoption 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 PERSISTvariant of
SETstatement syntax, for making configuration changes at runtime that also persist across server restarts. Like
SET PERSISTis 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.cnfin 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.
To provide information showing how each system variable was most recently set, the Performance Schema now has a
variables_infotable that lists each system variable and the source from which it got its value. See Performance Schema variables_info Table.
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.Note
PERSISTis now a reserved word and cannot be used as an identifier without identifier quoting.
The deprecated mysql_install_db program has been removed from MySQL distributions. Data directory initialization should be performed by invoking mysqld with the
--initialize-insecureoption instead. In addition, the deprecated
--bootstrapoption for mysqld that was used by mysql_install_db has been removed, and the
CMakeoption 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/v1directory of MySQL source distributions. This code used mysql_install_db and has been removed. The
MYSQL_INSTALL_DBenvironment variable and a value of 1 for the
MTR_VERSIONenvironment variable are no longer supported.
Incompatible Change: MySQL Server now incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional system tables.Important
A data dictionary-enabled server entails some general operational differences compared to a server that does not have a data dictionary; see Data Dictionary Usage Differences. Also, for upgrades to MySQL 8.0, the upgrade procedure differs somewhat from previous MySQL releases and requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, see Upgrading MySQL, particularly Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.
InnoDBcontinues to use its own data dictionary in the MySQL 8.0.0 release.
The following list briefly describes the main implications of this change:
.frmmetadata files previously associated with base tables and views no longer exist. Metadata previously stored in
.frmfiles is now stored in data dictionary tables.
Similarly, trigger metadata previously stored in
.TRNfiles is stored in a data dictionary table and those files no longer exist.
With the removal of
.frmfiles, the 64KB table definition size limit imposed by the
.frmfile structure is removed.
With the removal of
VERSIONfield now reports a hardcoded value of
10, which is the last
.frmfile 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.
New internal data dictionary APIs enable the server, internal storage engines, and plugins to access and store data in the MySQL data dictionary. Internal data dictionary APIs are introduced for handling of schemas, tablespaces, tablespace files, tables, partitioned tables, table partition data, triggers, stored routines, events, table objects, views, character sets, and collations.
Data dictionary tables are invisible, but in most cases there are corresponding
INFORMATION_SCHEMAtables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable
INFORMATION_SCHEMAinterface for application use.
INFORMATION_SCHEMAtables have been reimplemented entirely as views on data dictionary tables:
CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS KEY_COLUMN_USAGE SCHEMATA STATISTICS TABLES TABLE_CONSTRAINTS VIEWS
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_SCHEMAtable that is a view on data dictionary tables:
The server no longer must create a temporary table for each query of the
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
INFORMATION_SCHEMAqueries for those values now use table lookups instead. (Additionally, even for a non-view
INFORMATION_SCHEMAtable, 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_SCHEMAtable using a temporary table per query.
The preceding improvements also apply to
SHOWstatements that display information corresponding to the
INFORMATION_SCHEMAtables that are views on data dictionary tables. For example,
SHOW DATABASESdisplays the same information as the
INFORMATION_SCHEMAqueries that retrieve table statistics, the server now can use statistics cached in
INFORMATION_SCHEMAtables, or obtain the latest statistics directly from storage engines. The new
information_schema_statssystem variable controls which statistics source the server uses. See Optimizing INFORMATION_SCHEMA Queries.
foreign_key_column_usagetables now store foreign key information. The standard SQL way to obtain foreign key information is by using the
KEY_COLUMN_USAGEtables; these tables are now implemented as views on the
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_key_column_usagetables 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_dirssystem 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
InnoDBtables. Other examples follow.
functable that stores user-defined function information in the
mysqlsystem database now is an
InnoDB(transactional) table. Previously, it was a
In consequence of this change,
DROP FUNCTIONstatements cause an implicit commit, even when used for user-defined functions (see Statements That Cause an Implicit Commit). Previously, they caused an implicit commit when used for stored functions, but not for user-defined functions.
Previously, information about stored routines and events was stored in the
eventtables of the
mysqlsystem database. Those tables are no longer used. Instead, information about stored routines and events is stored in the
parametersdata dictionary tables in the
mysqlsystem database. The old tables used the
MyISAM(nontransactional) storage engine. The new tables use the
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 tablesand
Opening system tablesthread states rather than the
Opening tablesthread states. See General Thread States.
InnoDBtablespaces now include transactional storage for Serialized Dictionary Information (SDI), which is metadata in serialized form that describes objects such as tables, columns, and indexes. Along with the disappearance of
.frmand trigger metadata files, mentioned previously, you might notice the appearance of
.SDIfiles. These are serialized data 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
InnoDBtablespaces. SDI data is not present in persistent
InnoDBtablespaces in this release. The ibd2sdi utility is reserved for future use.
InnoDBstartup code was refactored to support MySQL initialization changes related to the MySQL data dictionary feature.
Upgrade and downgrade implications:
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)
Bit functions and operators comprise
>>. Prior to MySQL 8.0, bit functions and operators required
BIGINT(64-bit integer) arguments and returned
BIGINTvalues, so they had a maximum range of 64 bits. Non-
BIGINTarguments were converted to
BIGINTprior to performing the operation and truncation could occur. Now bit functions and operators permit binary string type arguments (
VARBINARY, and the
BLOBtypes) 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
BIGINTand 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.
The MySQL source code has been updated to use Doxygen for the internal documentation. The generated content for this milestone is available at http://dev.mysql.com/doc/dev/mysql-server/8.0.0/. This is a work in progress.
It is also possible to generate the Doxygen content locally from a MySQL source distribution using the instructions at Generating MySQL Doxygen Documentation Content.
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.
InnoDBuses this variable-size buffering capability to reduce the overhead of latching and B-tree navigation. Previously,
InnoDBused a small, fixed-size buffer.
The optimizer now supports table-level hints for specifying whether derived tables or views should be merged into the outer query block or materialized using an internal temporary table. Examples:
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt; SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
For more information, see Optimizer Hints. (Bug #79554, Bug #22328100)
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
InnoDBtables, for indexes other than primary keys.
To control whether an index is invisible explicitly for a new index, use a
INVISIBLEkeyword as part of the index definition for
CREATE INDEX, or
ALTER TABLE. To alter the invisibility of an existing index, use a
INVISIBLEkeyword with the
ALTER TABLE ... ALTER INDEXoperation. For more information, see Invisible Indexes.
mysqlsystem database now contains a
column_statstable designed to store statistics about column values. For more information, see Optimizer Statistics.
Development milestone releases in previous MySQL series were numbered using a suffix of
-m, 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
The parser rules for
CREATE TABLEwere refactored to be context independent and improve maintainability and extendibility. Several user-visible effects resulted from this work:
For generated columns, including
NOT NULL NULLresulted in a column that included the
NOT NULLattribute, which differed from nongenerated columns. Such definitions now use the final attribute
NULL, resulting in a nullable column (consistent with nongenerated columns).
CREATE TEMPORARY TABLEno longer permits multiple instances of
PARSE_GCOL_EXPRwas a keyword and could not be used as a label in stored programs. It is no longer a keyword and can be used as a label.
Messages for some syntax errors are more precise with respect to the location of the error within the statement.
The parser rules for
UNIONwere refactored to be more consistent (the same
SELECTsyntax applies uniformly in each such context) and reduce duplication. Several user-visible effects resulted from this work:
NATURAL JOINpermits an optional
NATURAL INNER JOIN), in compliance with standard SQL.
Right-deep joins without parentheses are permitted (for example,
... JOIN ... JOIN ... ON ... ON), in compliance with standard SQL.
The parser accepts parentheses around query expressions. For example,
(SELECT ... UNION SELECT ...)is permitted.
The parser better conforms to the documented permitted placement of the
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;
Incompatible Change: The Performance Schema now instruments server errors (and warnings), and exposes statistical information about them through a set of summary tables:
errorinstrument controls whether error information is collected (enabled by default).
Several tables contain error information, summarized in various ways:
performance_schema_error_sizesystem variable controls the number of instrumented errors.
For more information, see Error Summary Tables
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
In consequence of the preceding changes, two server error symbols were renamed:
Also, several server error codes were found to be no longer used in the server and have been removed as obsolete. Applications that test specifically for any of these errors should be updated:
ER_BINLOG_READ_EVENT_CHECKSUM_FAILURE ER_BINLOG_ROW_RBR_TO_SBR ER_BINLOG_ROW_WRONG_TABLE_DEF ER_CANT_ACTIVATE_LOG ER_CANT_CHANGE_GTID_NEXT_IN_TRANSACTION ER_CANT_CREATE_FEDERATED_TABLE ER_CANT_CREATE_SROUTINE ER_CANT_DELETE_FILE ER_CANT_GET_WD ER_CANT_SET_GTID_PURGED_WHEN_GTID_MODE_IS_OFF ER_CANT_SET_WD ER_CANT_WRITE_LOCK_LOG_TABLE ER_CREATE_DB_WITH_READ_LOCK ER_CYCLIC_REFERENCE ER_DB_DROP_DELETE ER_DELAYED_NOT_SUPPORTED ER_DIFF_GROUPS_PROC ER_DISK_FULL ER_DROP_DB_WITH_READ_LOCK ER_DROP_USER ER_DUMP_NOT_IMPLEMENTED ER_ERROR_DURING_CHECKPOINT ER_ERROR_ON_CLOSE ER_EVENTS_DB_ERROR ER_EVENT_CANNOT_DELETE ER_EVENT_CANT_ALTER ER_EVENT_COMPILE_ERROR ER_EVENT_DATA_TOO_LONG ER_EVENT_DROP_FAILED ER_EVENT_MODIFY_QUEUE_ERROR ER_EVENT_NEITHER_M_EXPR_NOR_M_AT ER_EVENT_OPEN_TABLE_FAILED ER_EVENT_STORE_FAILED ER_EXEC_STMT_WITH_OPEN_CURSOR ER_FAILED_ROUTINE_BREAK_BINLOG ER_FLUSH_MASTER_BINLOG_CLOSED ER_FORM_NOT_FOUND ER_FOUND_GTID_EVENT_WHEN_GTID_MODE_IS_OFF__UNUSED ER_FRM_UNKNOWN_TYPE ER_GOT_SIGNAL ER_GRANT_PLUGIN_USER_EXISTS ER_GTID_MODE_REQUIRES_BINLOG ER_GTID_NEXT_IS_NOT_IN_GTID_NEXT_LIST ER_HASHCHK ER_INDEX_REBUILD ER_INNODB_NO_FT_USES_PARSER ER_LIST_OF_FIELDS_ONLY_IN_HASH_ERROR ER_LOAD_DATA_INVALID_COLUMN_UNUSED ER_LOGGING_PROHIBIT_CHANGING_OF ER_MALFORMED_DEFINER ER_MASTER_KEY_ROTATION_ERROR_BY_SE ER_NDB_CANT_SWITCH_BINLOG_FORMAT ER_NEVER_USED ER_NISAMCHK ER_NO_CONST_EXPR_IN_RANGE_OR_LIST_ERROR ER_NO_FILE_MAPPING ER_NO_GROUP_FOR_PROC ER_NO_RAID_COMPILED ER_NO_SUCH_KEY_VALUE ER_NO_SUCH_PARTITION__UNUSED ER_OBSOLETE_CANNOT_LOAD_FROM_TABLE ER_OBSOLETE_COL_COUNT_DOESNT_MATCH_CORRUPTED ER_ORDER_WITH_PROC ER_PARTITION_SUBPARTITION_ERROR ER_PARTITION_SUBPART_MIX_ERROR ER_PART_STATE_ERROR ER_PASSWD_LENGTH ER_QUERY_ON_MASTER ER_RBR_NOT_AVAILABLE ER_SKIPPING_LOGGED_TRANSACTION ER_SLAVE_CHANNEL_DELETE ER_SLAVE_MULTIPLE_CHANNELS_HOST_PORT ER_SLAVE_MUST_STOP ER_SLAVE_WAS_NOT_RUNNING ER_SLAVE_WAS_RUNNING ER_SP_GOTO_IN_HNDLR ER_SP_PROC_TABLE_CORRUPT ER_SQL_MODE_NO_EFFECT ER_SR_INVALID_CREATION_CTX ER_TABLE_NEEDS_UPG_PART ER_TOO_MUCH_AUTO_TIMESTAMP_COLS ER_UNEXPECTED_EOF ER_UNION_TABLES_IN_DIFFERENT_DIR ER_UNSUPPORTED_BY_REPLICATION_THREAD ER_UNUSED1 ER_UNUSED2 ER_UNUSED3 ER_UNUSED4 ER_UNUSED5 ER_UNUSED6 ER_VIEW_SELECT_DERIVED_UNUSED ER_WRONG_MAGIC ER_WSAS_FAILED
DIGEST_TEXTcolumns in the Performance Schema
events_statements_currenttable 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
sysschema views that use those tables. For more information, see Optimizing Performance Schema Queries.
The size of the
ROLEcolumn of the
setup_actorsPerformance Schema table was increased from 16 to 32 characters.
validate_password_check_user_namesystem variable is now enabled by default rather than disabled. This means that when the
validate_passwordplugin is enabled, by default it now rejects passwords that match the current session user name.
--ssl-verify-server-certoptions have been removed. Use
--ssl-verify-server-certoptions. (The server-side
--ssloption remains unchanged.)
For the C API,
mysql_options()correspond to the client-side
--ssl-verify-server-certoptions and have been removed. Use
MYSQL_OPT_SSL_MODEwith an option value of
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. (Bug #23632147, Bug #81964)
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_Y()with a single argument now are stricter and produce an
ER_UNEXPECTED_GEOMETRY_TYPEerror rather than returning
NULLif the argument is a valid geometry but not a point. For more information, see Point Property Functions.
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
MBRif it performs an operation based on minimum bounding rectangles. The deprecated functions have now been removed to leave only the corresponding
These functions are removed in favor of the
These functions are removed in favor of the
GLength()is removed in favor of
mysql-test-run.pl now supports a
--do-suiteoption, which is similar to
--do-testbut permits specifying entire suites of tests to run. (Bug #24350345)
rmdircommand 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-rmdircommand. (Bug #24316799)
Two new test suite options make it easier to debug test cases:
mysql-test-run.pl supports a
--mysqltest=option that enables options to be passed to mysqltest.
mysqltest supports a
--trace-execoption that causes it to immediately print output from executed programs to
mysql-test-run.pl now recognizes the
MTR_CTEST_TIMEOUTenvironment 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
--errorcommand only for server errors. This is now also possible for client errors. For example:
(Bug #21048973, Bug #76972)
The mysqltest program now has a
copy_files_wildcardcommand that copies all files that match a pattern from a source directory to a destination directory. See mysqltest Commands. (Bug #82111, Bug #23743035)
Protobufdecoder class limited the number of nested objects to 50 (the default value). (Bug #23707238, Bug #82025)
list_objectsincorrectly reported a table as a collection. (Bug #23631240)
create_collectionstatement created a collection table with a unique key index on the
'_id'column instead of on the primary key. (Bug #23284569)
Incompatible Change; Partitioning: The generic partitioning handler has been removed from the MySQL server. As part of this change, mysqld no longer supports the
--skip-partitionoptions, and the server can no longer be built using
partitionis also no longer displayed in the output of
SHOW PLUGINS, or shown in the
In order to support partitioning of a given table, the storage engine used for the table must now provide its own (“native”) partitioning handler.
InnoDBis 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
NDBstorage engine used by MySQL 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
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:
Remove the table's partitioning; you can do this without any data loss by executing an
ALTER TABLE ... REMOVE PARTITIONINGstatement.
Change the storage engine used for the table to
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-
InnoDBtable, prior to upgrading the server to MySQL 8.0. Otherwise, such a table cannot be used following the upgrade.
For information about converting
InnoDB, see Converting Tables from MyISAM to InnoDB.
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 TABLEstatements that use a value for the
STORAGE ENGINEoption other than
InnoDB, or by specifying the storage engine as
InnoDBto be used by default).
For more information, see Partitioning Limitations Relating to Storage Engines.
Important Change; InnoDB: The following
InnoDBfile format configuration parameters were deprecated in MySQL 5.7.7 and are now removed:
File format configuration parameters were necessary for creating tables compatible with earlier versions of
InnoDBin MySQL 5.1. Now that MySQL 5.1 has reached the end of its product lifecycle, the parameters are no longer required.
innodb_buffer_pool_debugoption permits multiple buffer pool instances when the buffer pool is less than 1GB in size, ignoring the 1GB minimum buffer pool size constraint imposed on
innodb_buffer_pool_instances. (Bug #24287290)
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_timeoutsetting for transaction rollback when a deadlock occurs. (Bug #23477773)
libinnodb_zipdecompress.alibrary allows external tools to use the
page_zip_decompress_low()function to decompress
InnoDBpages. (Bug #21405300, Bug #77664)
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)
InnoDBnow avoids intermediate commits that would occur every 10000 rows during
ALTER TABLE ALGORITHM=COPYoperations. The purpose of intermediate commits was to speed up recovery in the case of an aborted
ALTER TABLE ALGORITHM=COPYoperation. If an
ALTER TABLE ALGORITHM=COPYoperation 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=COPYoperations unless there is an
IGNOREclause 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=COPYoperation 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.
InnoDBcode now uses the C++
std::threadlibrary for thread management.
InnoDBmemcached plugin now supports multiple
getoperations (fetching multiple key/value pairs in a single memcached query) and range queries. See InnoDB memcached Multiple get and Range Query Support.
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 = Ntable 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
ROLLBACKoperation no longer results in the reuse of auto-increment values that were allocated to the rolled-back transaction.
If you modify an
AUTO_INCREMENTcolumn value to a value larger than the current maximum auto-increment value (in an
UPDATEoperation, for example), the new value is persisted, and subsequent
INSERToperations allocate auto-increment values starting from the new, larger value.
innodb_stats_sample_pagessystem variable was removed.
innodb_stats_sample_pageswas deprecated in MySQL 5.6.3 and replaced by
InnoDB: When encountering index tree corruption,
InnoDBwrites a corruption flag to the redo log, which makes the corruption flag crash-safe.
InnoDBalso writes in-memory corruption flag data to an engine-private system table on each checkpoint. During recovery,
InnoDBreads corruption flags from both locations and merges results before marking in-memory table and index objects as corrupt.
innodb_locks_unsafe_for_binlogsystem variable was removed.
innodb_locks_unsafe_for_binlogwas deprecated in MySQL 5.6.3. The
READ COMMITTEDisolation level provides similar functionality.
InnoDBno longer creates
InnoDBSymbolic Link files) when creating tablespace data files outside of the MySQL data directory. Redo log records are now used to locate remote tablespaces.
With this change, moving a remote tablespace while the server is offline by manually modifying an
.islfile is no longer supported.
InnoDBno longer supports compressed temporary tables. When
innodb_strict_modeis enabled (the default),
CREATE TEMPORARY TABLEreturns an error if
KEY_BLOCK_SIZEis specified. If
innodb_strict_modeis 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,
IS_COMPRESSEDcolumns were removed from the
InnoDB: A new
INNODB_CACHED_INDEXES, reports the number of index pages cached in the
InnoDBbuffer pool for each index.
innodb_checksumssystem variable was removed.
innodb_checksumswas replaced by
innodb_checksum_algorithmin MySQL 5.6.3.
InnoDBstartup code was refactored.
innodb_flush_methoddefault value is no longer
NULL. On Unix-like systems, the default value is
fsync. On Windows systems, the default value is
On Windows, the
innodb_flush_methodsetting no longer affects the
innodb_use_native_aiosetting. There are now two possible settings for
unbuffered(unbuffered I/O) and
normal(buffered I/O). With this change, you can enable asynchronous I/O with buffered I/O, which is a new combination (
async_unbufferedsetting was removed.
innodb_support_xasystem variable, which enables support for two-phase commit in XA transactions, was removed. As of MySQL 5.7.10,
InnoDBsupport for two-phase commit in XA transactions is always enabled.
InnoDB: The new
INFORMATION_SCHEMA.INNODB_CACHED_INDEXEStable reports the number of index pages cached in the
InnoDBbuffer pool for each index.
InnoDBno longer creates an
.islfile (InnoDB Symbolic Link files) when creating a file-per-table tablespace data file outside of the MySQL data directory.
InnoDBnow uses the redo log to locate remote tablespace data files.
Offline relocation of a file-per-table tablespace data file created outside of the MySQL data directory by modifying the
.islfile is no longer supported.
Replication: There are two improvements to how a CHANGE MASTER TO statement is written into the error log (
Before, no commas were put between the option specifications (for example
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_PASSWORDvalue, 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.
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_executedis 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_executedat the time of taking the backup. The syntax for
SET GTID_PURGEDhas been extended so that
SET GTID_PURGED ="+adds
gtid_setto the existing
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
JSONdocuments stored in MySQL. The new operator is similar to the
->operator, but performs JSON unquoting of the value as well. For a JSON column
mycoland JSON path expression
mypath, the following three expressions are equivalent:
->>operator can be used in SQL statements wherever
JSON_UNQUOTE(JSON_EXTRACT())would be allowed. This includes (but is not limited to)
To produce more accurate estimates, the
MEMORYstorage 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:
libmysqlservices.a. (Bug #22891432)
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
Previously, the Performance Schema was not built for
libmysqld, the embedded server. This prevented use of the
SHOW VARIABLESstatements with
show_compatibility_56=OFFbecause, 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
SHOWstatements can be supported with
show_compatibility_56=OFF. (Bug #22809694)
Several internal functions used by
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 (
.csuffix) to C++ (
.ccsuffix). This enables stricter compilation checks and use of C++ features in the library code. (Bug #22124719)
Source code for the
mysyslibrary now uses C++ rather than C to take advantage of stricter compilation checks and permit use of C++ features. (Bug #21881278)
A new CMake option,
WITH_TSAN, permits enabling ThreadSanitizer for compilers that support it. (Bug #80409, Bug #23171902)
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
MySQL now provides functions to manipulate UUID values and make them easier to work with:
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.
For more information about these functions, see Miscellaneous Functions
The mysql_plugin utility has been removed. Alternatives include loading plugins at server startup using the
--plugin-load-addoption, or at runtime using the
The server now relies on storage engines to clean up temporary tables left from previous server runs.
InnoDBdoes this by discarding the temporary tablespace on restart.
MyISAMand 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.
Work was done to clean up the source code base, including: Removal of unneeded CMake checks; removing unused macros from source files; reorganizing header files to reduce the number of dependencies and make them more modular, removal of function declarations without definitions.
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-table-namesoptions for mysqlcheck, the
UPGRADE DATA DIRECTORY NAMEclause for the
ALTER DATABASEstatement, and the
Com_alter_db_upgradestatus 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.
NDB Cluster: Previously, the
mysql.ndb_binlog_indextable was created even if the server was built without
NDB. Now the table is created only if the server is built with
NDB. (Bug #22874872)
InnoDB: Unnecessary checks were removed from the
ut_cpuid()function which is used to fetch information about the CPU. (Bug #24405292)
InnoDB: An asynchronous read operation on a deleted tablespace raised an error. (Bug #24388498)
dict_col_taccessors were added to the
InnoDBcode. (Bug #24363566)
dict_colfunctions in the
InnoDBcode were replaced by accessors. (Bug #24361098)
dict_index_tfunctions in the
InnoDBcode were replaced by accessors. (Bug #24361023)
InnoDB: Unnecessary code that checked for and released reserved adaptive hash index search latches was removed. (Bug #24300175)
InnoDB: A system tablespace data file size greater than 4G on a 32-bit operating system could result in an overflow condition. (Bug #23753625)
InnoDB: Internal methods for accessing table object data were added to to
dict_table_t. (Bug #23748128)
InnoDB: An unspecified block size resulted in an empty
INFORMATION_SCHEMA.TABLESPACEStable on Windows NTFS with a cluster page size greater than or equal to 8K. (Bug #23598872)
InnoDB: The restriction that required the first undo tablespace to use
space_id1 was removed to avoid
space_idconflicts with existing tablespaces during upgrade. The first undo tablespace can now use a
space_idother than 1.
space_idvalues 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)
mysql.innodb_table_statstable definitions, which were previously created by an SQL script, are now hard-coded. As a result, the
dict_table_schema_checkfunction is longer required and was removed. (Bug #23336079)
ut_snprintfunction was replaced by the C++11
snprintffunction. (Bug #23329353)
InnoDB: For consistency, instances of
InnoDBcode were replaced with
page_no_tdata types. (Bug #23297169)
InnoDB: Use of boost::atomic in
InnoDBcode was replaced with std::atomic. (Bug #23280649)
InnoDB: MySQL binaries were not built with the NUMA feature. (Bug #23259754)
InnoDB: References to
UNIV_MUST_NOT_INLINEwere removed. The
ut0byte.ccfiles, which were only necessary when
UNIV_NONINLwas defined, were also removed. (Bug #23150562)
mutex_own()mapping caused warnings when compiling with Clang or newer GCC compilers. (Bug #23090278)
InnoDB: Compilation of
InnoDBwith Visual Studio 2015 Update 2 returned warnings. (Bug #23056963)
InnoDB: Querying the Performance Schema for
InnoDBmemory allocation event data incorrectly reported values of 0. (Bug #23020280)
DBUG_OFFcompile-time flags were replaced by
UNIV_DEBUGflags. To improve error log output,
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)
SHOW ENGINE INNODB STATUSoutput displayed negative spin rounds per wait values. Thanks to Laurynas Biveinis for the patch. (Bug #22844987, Bug #79703)
innodb_disable_resize_buffer_pool_debugoption was removed. The patch for this change also removed a code variable and simplified the
buf_pool_resize()function. (Bug #22755053)
InnoDB: After a successful
ALTER TABLE ... ALGORITHM=COPYoperation, an assertion was raised while building a previous version of a clustered index record. (Bug #22707367)
InnoDB: A DML operation that updated a counter in a table with a virtual index raised on assertion in
row_parse_int(). (Bug #22650195)
InnoDBmemcached plugin would not load when compiled with libevent 2.0. (Bug #22646919)
InnoDB: Unused calculations for integer-based
rec_per_keyvalues were removed from
rec_per_keyinformation was replaced by floating point index statistics in an earlier release. (Bug #22625348)
InnoDB: On slow shutdown, purge thread shutdown was initiated before the background rollback thread exited, resulting in an assertion failure. (Bug #22561332)
InnoDB: Blocks were lost in
row_vers_old_has_index_entry()due to unfreed heaps. (Bug #22543834, Bug #79973)
InnoDB: A transportable tablespace debug test raised an assertion that was due to a race condition. (Bug #22453668)
InnoDB: The server failed to start due to missing undo tablespaces. (Bug #22452992)
InnoDBrecovery asserted while attempting to close an undo tablespace due to buffered undo tablespace changes introduced by the recovery process. (Bug #22361764)
InnoDB: Reallocation of memcached-referenced memory raised an assertion. (Bug #22304250, Bug #79500)
InnoDBwith 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)
SHOW CREATE TABLEoutput for partitioned tables did not accurately display tablespace assignment information for table partitions. (Bug #22245554)
ALTER TABLE...TRUNCATE PARTITIONoperation ignored the table's
KEY_BLOCK_SIZEattribute and used the default value instead, which is half of the
innodb_page_sizevalue. (Bug #22186558, Bug #79223)
InnoDB: Memory leaks in innochecksum were corrected. (Bug #22179518)
SPACE_IDcolumn was added to the
INDEX_IDvalue 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 generated virtual 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_checkswas disabled. (Bug #22094601, Bug #78955)
References: This issue is a regression of: Bug #16845421.
ha_innobase::m_primary_keyfield was removed. It was redundant. A boolean predicate,
TABLE_SHARE::is_missing_primary_key(), was added. (Bug #21928734, Bug #78662)
InnoDB: A buffer pool load operation that attempted to load an uninitialized page caused a Valgrind failure. (Bug #21747906)
InnoDB: Unused functions introduced by the
InnoDBmemcached plugin were removed. (Bug #21625760)
InnoDB: An internal global variable used by the
innodb_buffer_pool_sizeconfiguration option was removed. (Bug #21512749)
InnoDBpage cleaner thread asserted during a buffer pool resize operation. (Bug #21473497)
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)
libsqlfunctions and variables were removed, and global symbols were converted to static keywords, where possible. (Bug #21153166, Bug #21141390, Bug #77146, Bug #21178589)
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
index_id) now includes a tablespace identifier (
index_id). This change makes index identifiers unique at the tablespace level as well as the
InnoDBinstance 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)
__attribute__((nonnull))was removed from
InnoDBcode. The attribute is no longer permitted by
InnoDBcoding guidelines. (Bug #20468234)
InnoDB: A new struct was added to provide a logical interface for handling and manipulating external
BLOBfield references. (Bug #18195972)
On systems with a large buffer pool and
TRUNCATE TABLEoperations previously caused a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries. The remapping of
CREATE TABLEavoids the problematic LRU scan.
TRUNCATE TABLEis temporarily non-atomic. A server exit during a
TRUNCATE TABLEoperation can result in a dropped table and orphaned foreign key constraints in the
DELETEhas a higher overhead cost than
FLUSH TABLEsince 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.
(Bug #16834993, Bug #68184, Bug #16207919)
InnoDBwasted most pages in extents used for fragment pages. (Bug #16204823, Bug #67963)
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_statstable 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_statsfor each partition or subpartition wherein the value actually used to represent the table name follows the pattern
, respectively. This issue is fixed by changing the definition of the
innodb_table_statsto accommodate the maximum combined length of these attributes plus
#SP#(199 characters). (Bug #72061, Bug #18416479)
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)
Replication: When using
START SLAVE UNTILstatements with a multi-threaded slave the only
UNTILclause available was
SQL_BEFORE_GTIDS. Now multi-threaded slaves are compatible with the
START SLAVE UNTIL MASTER_LOG_FILE = 'and
log_name', MASTER_LOG_POS =
START SLAVE UNTIL RELAY_LOG_FILE = 'statements. (Bug #75843, Bug #20513547)
log_name', RELAY_LOG_POS =
CHECKSUM TABLEcalculated the checksums for
JSONvalues 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
JSONvalue, and not on that value's address. (Bug #23535703)
NULLto a stored procedure expecting a
JSONparameter led to an assertion failure in debug builds. (Bug #23209914)
JSON: Parsing of
JSONpath 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
JSONobjects. (Bug #22782948)
JSON: Queries that executed a
JSONfunction 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)
The client library failed to build on Solaris using the
Cstdlibrary. (Bug #24353920, Bug #82347)
On macOS, stack trace demangling now occurs for builds compiled using Clang, just as for GCC. (Bug #23606094, Bug #81908)
libeventwas built on macOS even when not needed. (Bug #23228287, Bug #81311)
Previously, different values were reported by
SHOW ENGINE PERFORMANCE_SCHEMA STATUSand
SELECT * FROM performance_schema.memory_summary_global_by_event_namefor 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 STATUSoutput. That statement now includes the missing memory, displayed as
(pfs_buffer_scalable_container).memory. (Bug #23104498)
-fexpensive-optimizationsoption to GCC caused ARM64 and PowerPC builds to compute floating-point operations slightly differently from other platforms. This option was enabled by
-O2and higher optimization levels. The option now is disabled on platforms negatively affected by it. (Bug #23046775)
After a failed administrative operation such as
ALTER TABLE ... OPTIMIZE PARTITION, selecting from the Performance Schema in lock-tables mode could hang. (Bug #23044286)
In builds with AddressSanitizer enabled,
CAST(... AS BINARY)could cause a server exit. (Bug #22900560)
Some spatial functions were reported using a different spatial function name in error messages. (Bug #22883056, Bug #80627)
Fixed Valgrind warnings with Clang in optimized mode for the
my_strtod_int()function. (Bug #22839888)
Precision math operations on values with 64 decimals could produce a 0 result. (Bug #22828692)
ST_GeomFromGeoJSON()could return an error with valid arguments. (Bug #22804853)
For debug builds with
STRICT_TRANS_TABLESSQL mode enabled, an assertion could be raised by
REPLACEstatements that had made changes that could not be rolled back and that subsequently generated an
ER_NO_DEFAULT_FOR_FIELDerror. (Bug #22635253)
The optimizer contained a
memcpy()call that did not check for overlapping source and destination. (Bug #22537196)
For debug builds, a missing error check on the result of a subquery that accessed a
JSONvalue could raise an assertion. (Bug #22522073)
CREATE TABLE ... SELECTstatement, then flushing tables (thus closing the table) and executing the prepared statement could cause a server exit. (Bug #22393309)
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)
HANDLERread 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-
NULLvalues could lead to a server exit. (Bug #22281205)
Using a subquery containing a row constructor to set a variable in a
SETstatement could cause a server exit. (Bug #22276843)
If the SQL mode did not include
ALLOW_INVALID_DATES, a query that contained
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,
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)
On Windows, setting the global
log_syslogsystem variable in multiple threads could cause a server exit. (Bug #22180046)
Memory leaks could result if stored routine loading involved temporarily changing the default database and errors occurred restoring the original default database. (Bug #22179795)
CMake configuration was adjusted to check for
-Wcompiler options instead of
-Wno-because the latter produce false positives for GCC. (Bug #21881753)
There could be discrepancies between the values of
mysql.event.last_executed. This no longer occurs. Event information is stored in the
mysql.eventsdata dictionary table, which is invisible, so that
INFORMATION_SCHEMA.EVENTSis 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)
-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-erroroption, mysqld could sent
stderrto the wrong location. (Bug #20609063)
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_modesystem variable in
my.cnf. (Bug #20535729)
Event loading from the
mysql.eventsystem table could fail if the
PAD_CHAR_TO_FULL_LENGTHSQL mode was enabled. (Bug #20073523, Bug #74947)
CREATE TABLE ... SELECTwhere non-
BITdata was selected from the source table into a
BITcolumn in the destination table could cause a server exit. (Bug #19930894)
Compilation failed on OS X when MySQL was configured with
-DMYSQL_MAINTAINER_MODE=1and compiled with clang/Xcode 6.0. (Bug #19694515, Bug #74100)
CHANGE MASTER TOstatements rewritten to filter the password before being written to the general query log, any
MASTER_AUTO_POSITIONclause was lost. (Bug #19622609)
A potential null-pointer dereference and memory leak in table-rename code were corrected. (Bug #18194270)
MATCH ... AGAINSTto 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
Connectionsstatus variable, Performance Schema
SHOW PROCESSLISTstatement. (Bug #17666696)
A query with a subquery containing a set operation with an outer reference might cause a server exit. (Bug #17270896)
GRANTto change a password for an invalid user produced an error, but also updated the
mysql.usersystem table. (Bug #17180985)
The parser for spatial WKT data accepted numbers such as
.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_sizesystem variable, the Performance Schema truncated the attribute data. In addition, the Performance Schema wrote this message to the error log if the
log_warningssystem variable was greater than zero:
[Warning] Connection attributes of length
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
_truncatedattribute 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.
A new status variable,
Performance_schema_session_connect_attrs_longest_seen, indicates the longest connection attribute buffer smaller than 64KB seen by the server. If this value is larger than
performance_schema_session_connect_attrs_size, attribute truncation has occurred, and DBAs may wish to increase the latter value, or, alternatively, investigate which clients are sending large amounts of attribute data.
For more information, see Performance Schema Connection Attribute Tables. (Bug #16576959)
Introduction of the data dictionary enabled several
INFORMATION_SCHEMA8eproblems to be addressed:
INFORMATION_SCHEMA.STATISTICScould return different results depending on the order of columns in the select list.
INFORMATION_SCHEMAtables had suboptimal column types and sizes. Such tables that are now views on data dictionary tables in the
mysqlsystem database have more appropriate column definitions.
INFORMATION_SCHEMAtables 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.
INFORMATION_SCHEMAtables that opened
.frmfiles 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
COLLATEto force a given collation worked only if applied to the
INFORMATION_SCHEMAtable column, but not if applied to the comparison value.
For additional information about performing such comparisons, see Using Collation in INFORMATION_SCHEMA Searches.
(Bug #14017351, Bug #65121, Bug #17559183, Bug #70462, Bug #23259470, Bug #81347, Bug #20372562, Bug #75532, Bug #13878164, Bug #11756519, Bug #48445)
For abnormal server exit on Windows, the server previously created a minidump file named
module_nameis 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
; for example,
mysqld.exe.7296.dmp. (Bug #12779463)
For queries on
INFORMATION_SCHEMAtables, 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_namessystem variable value. Furthermore, it was ineffective to provide a
COLLATEclause to change the comparison properties because that clause was ignored. This has been changed so that
COLLATEis no longer ignored and can be used to obtain the desired comparison properties. (Bug #11748044, Bug #34921)
BIGINT UNSIGNEDarguments. (Bug #80873, Bug #23013359)
ST_AsGeoJSON()failed when geometry arguments were supplied using user-defined variables. (Bug #80697, Bug #22912800)
NULLfor some valid values of
N. (Bug #80630, Bug #22885819)
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_RANGEerror 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
<=operators. (Bug #80244, Bug #22661012)
mf_iocacheunit 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)
SUBSTRING_INDEX(did not properly handle
countvalues larger than 32 bits. (Bug #79978, Bug #22545429)
For calls to
CONVERT(), literal string arguments could be modified during execution, producing incorrect results. (Bug #79924, Bug #22531111)
REPLACE('a', BINARY 'b', NULL)returned
NULL. (Bug #79912, Bug #22523836)
Transaction state tracking now avoids a function that is not 8-bit safe, for enhanced compatibility with nonstandard character sets. (Bug #79905, Bug #22523383)
Lines in the general query log were missing a tab between the timestamp and the thread ID. Thanks to Tsubasa Tanaka for the patch. (Bug #79868, Bug #22508563)
Some comparisons between unsigned values and negative upper limits could return incorrect results (for example,
CAST(100 AS UNSIGNED) BETWEEN 1 AND -1). (Bug #79857, Bug #22501606)
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.hwas 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
--helpis 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_RANGEerror if the result is negative, unless the
NO_UNSIGNED_SUBTRACTIONSQL mode is enabled. (Bug #78914, Bug #22083757)
Handling by the
HEX()function of numbers larger than 264 was improved. (Bug #78828, Bug #22297983)
References: This issue is a regression of: Bug #9854.
The client-side plugin deinitialization function signature was changed from
int (*deinit)(void)to avoid warnings when compiling with
-Wstrict-prototypes. (Bug #78177, Bug #21680094, Bug #81419, Bug #23282498)
CREATE TABLEreported an incorrect error if a very long or incorrect path name was specified for the
INDEX DIRECTORYtable option. Now
ER_WRONG_VALUEare reported for those cases. (Bug #76635, Bug #20857556)
The server now tries to provide more informative messages for these error codes:
ER_GET_ERRNO. (Bug #76298, Bug #20694494)
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
latin1and ignored the
tbl_nameCHARACTER SET utf8, CONVERT TO CHARACTER SET latin1;
Thanks to Daniel Black for the patch. (Bug #75320, Bug #20279241)
abort_loopvariable 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 TABLEcould, 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)
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_USEerror (“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.