Documentation Home
MySQL 8.0 Release Notes
Related Documentation
MySQL 8.0 Reference Manual
Download these Release Notes
PDF (US Ltr) - 4.2Mb
PDF (A4) - 4.3Mb
MySQL 8.0 Release Notes  /  Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)

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

For general information about upgrades, downgrades, platform support, etc., please visit https://dev.mysql.com/doc/relnotes/mysql/8.0/en/.

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. (Making a backup before the upgrade is a prudent precaution in any case.)

Account Management Notes

  • 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.) A side effect of this change in behavior is that partially completed account management statements on a MySQL 5.7 master fail when replicated on a MySQL 8.0 slave. For more information, see Atomic Data Definition Statement Support.

    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. (WL #7158, WL #9045)

  • 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

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

    (WL #988)

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.

    • For non-language-specific collations, characters in contraction sequences are treated as separate characters. For language-specific collations, contractions might change character sorting order.

    For more information, see Unicode Character Sets. (WL #9125, WL #9108, WL #9479)

Compilation Notes

  • 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 and not explicitly disabled. To disable use of this linker, specify the -DUSE_LD_GOLD=OFF CMake option. (Bug #23759968, Bug #82163)

  • The WITH_EXTRA_CHARSETS CMake 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 minimum version of the Boost library for server builds is now 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_LIBRARY CMake option obsolete, so it has been removed. (WL #8896)

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.

    • The INSTALL COMPONENT and UNINSTALL COMPONENT statements provide an SQL interface for component manipulation at runtime.

    • A loader service registers installed components in the mysql.component system 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 MySQL Components. For information about the internal implementation of components, see the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html. (WL #4102)

Configuration Notes

  • 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, Bug #26931470, Bug #87995)

  • 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 Persisted System Variables.

    To provide information showing how each system variable was most recently set, the Performance Schema now has a variables_info table 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. (WL #8688)

Data Dictionary Notes

  • 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 Preparing Your Installation for Upgrade.

    InnoDB continues to use its own data dictionary in the MySQL 8.0.0 release.

    The following list briefly describes the main implications of this change:

    • The .frm metadata files previously associated with base tables and views no longer exist. Metadata previously stored in .frm files is now stored in data dictionary tables.

      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.TABLES VERSION field now reports a hardcoded value of 10, which is the last .frm file version used in MySQL 5.7.

    • With the removal of .frm files, the sync_frm system variable is removed.

    • 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.

      With this change, data dictionary updates and binary log writes for CREATE TRIGGER and DROP TRIGGER operations are combined into a single, atomic transaction.

    • 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: 

      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_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.

        Affected INFORMATION_SCHEMA table statistic columns include: 

        STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME

      For more information, see Optimizing INFORMATION_SCHEMA Queries.

    • 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_SCHEMA REFERENTIAL_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.

    • Previously, this was possible to use CREATE TEMPORARY TABLE to create a table in a nonexistent database by qualifying the table name with the name of a nonexistent database. This is no longer permitted.

    • 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 loadable function information in the mysql system database now is an InnoDB (transactional) table. Previously, it was a MyISAM (nontransactional) table.

        In consequence of this change, CREATE FUNCTION and DROP FUNCTION statements cause an implicit commit, even when used for loadable functions (see Statements That Cause an Implicit Commit). Previously, they caused an implicit commit when used for stored functions, but not for loadable functions.

      • 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.

      • InnoDB startup 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, WL #6378, WL #6383, WL #7896, WL #6384, WL #7897, WL #6388, WL #7898, WL #7284, WL #7630, WL #7836, WL #6390, WL #6382, WL #6389, WL #6387, WL #6385, WL #7053, WL #7158, WL #8980, WL #8150, WL #7488, WL #7066, WL #6929, WL #6391, WL #6392, WL #6599)

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. (WL #8699)

Deprecation and Removal Notes

  • Important Change; InnoDB: The following InnoDB file format configuration options 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 options 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, these options are no longer required.

    The FILE_FORMAT column was removed from the INNODB_SYS_TABLES and INNODB_SYS_TABLESPACES Information Schema tables. (WL #7704)

  • 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. (WL #8903)

  • 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. (WL #8894)

  • 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. (WL #8843)

  • 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_SCRIPTDIR CMake 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. (WL #9071)

  • The mysql_plugin utility has been removed. Alternatives include loading plugins at server startup using the --plugin-load or --plugin-load-add option, or at runtime using the INSTALL PLUGIN statement. (WL #8927)

  • 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. (WL #9014)

Doxygen Notes

  • The MySQL source code has been updated to use Doxygen for the internal documentation. This is a work in progress. As new MySQL versions are distributed, the Doxygen documentation will be updated, with the latest version always available at https://dev.mysql.com/doc/index-other.html.

    It is also possible to generate the Doxygen content locally from a MySQL source distribution using the instructions at Generating MySQL Doxygen Documentation Content. (WL #8493)

Optimizer Notes

  • 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. (WL #7093)

  • 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: 

    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, WL #9307)

  • 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. (WL #8697)

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

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

    (WL #8896)

Parser Notes

  • Incompatible Change: 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, which may require rewriting of certain statements:

    • NATURAL JOIN permits an optional INNER keyword (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.

    • STRAIGHT_JOIN now permits a USING clause, similar to other inner joins.

    • 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 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;

    • Locking clauses (FOR UPDATE, LOCK IN SHARE MODE) are allowed only in non-UNION queries. This means that parentheses must be used for SELECT statements containing locking clauses. This statement is no longer accepted as valid: 

      SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;

      Instead, write the statement like this: 

      (SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);

    (Bug #11746363, Bug #25734, WL #8083, WL #8907)

  • 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).

    • CREATE TEMPORARY TABLE no longer permits multiple instances of TEMPORARY.

    • Previously, PARSE_GCOL_EXPR was 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.

    (WL #7840, WL #8067, WL #8433, WL #8434, WL #8345)

Performance Schema Notes

  • Incompatible Change: The Performance Schema now instruments server errors (and warnings), and exposes statistical information about them through a set of summary tables:

    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 performance_schema database.

    In consequence of the preceding changes, two server error symbols were renamed: ER_CANT_SET_ENFORCE_GTID_CONSISTENCY_ON_WITH_ONGOING_GTID_VIOLATING_TRANSACTIONS is now ER_CANT_ENFORCE_GTID_CONSISTENCY_WITH_ONGOING_GTID_VIOLATING_TX and ER_SET_ENFORCE_GTID_CONSISTENCY_WARN_WITH_ONGOING_GTID_VIOLATING_TRANSACTIONS is now ER_ENFORCE_GTID_CONSISTENCY_WARN_WITH_ONGOING_GTID_VIOLATING_TX.

    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

    (WL #8058)

  • 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. (WL #6616)

  • The size of the ROLE column of the setup_actors Performance Schema table was increased from 16 to 32 characters. (WL #9262)

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. (WL #9480)

  • The client-side --ssl and --ssl-verify-server-cert options have been removed. Use --ssl-mode=REQUIRED instead of --ssl=1 or --enable-ssl. Use --ssl-mode=DISABLED instead of --ssl=0, --skip-ssl, or --disable-ssl. Use --ssl-mode=VERIFY_IDENTITY instead of --ssl-verify-server-cert options. (The server-side --ssl option remains unchanged.)

    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. (WL #9091)

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. (WL #8606)

  • 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. (WL #8543)

  • MySQL now stores information about spatial reference systems other than SRID 0, for use with spatial data. This information is stored in the st_spatial_reference_systems data dictionary table and is based on EPSG Dataset 8.7. For information about spatial reference systems, see Spatial Reference System Support.

    Previously, the ST_IsValid(), ST_MakeEnvelope(), and ST_Validate() functions required geometry arguments with SRID 0. They now accept geometry arguments with an SRID for a projected spatial reference system. (WL #8579)

  • 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().

    • These functions are removed in favor of the ST_ names: Area(), AsBinary(), AsText(), AsWKB(), AsWKT(), Buffer(), Centroid(), ConvexHull(), Crosses(), Dimension(), Distance(), EndPoint(), Envelope(), ExteriorRing(), GeomCollFromText(), GeomCollFromWKB(), GeomFromText(), GeomFromWKB(), GeometryCollectionFromText(), GeometryCollectionFromWKB(), GeometryFromText(), GeometryFromWKB(), GeometryN(), GeometryType(), InteriorRingN(), IsClosed(), IsEmpty(), IsSimple(), LineFromText(), LineFromWKB(), LineStringFromText(), LineStringFromWKB(), MLineFromText(), MLineFromWKB(), MPointFromText(), MPointFromWKB(), MPolyFromText(), MPolyFromWKB(), MultiLineStringFromText(), MultiLineStringFromWKB(), MultiPointFromText(), MultiPointFromWKB(), MultiPolygonFromText(), MultiPolygonFromWKB(), NumGeometries(), NumInteriorRings(), NumPoints(), PointFromText(), PointFromWKB(), PointN(), PolyFromText(), PolyFromWKB(), PolygonFromText(), PolygonFromWKB(), SRID(), StartPoint(), Touches(), X(), Y().

    • GLength() is removed in favor of ST_Length().

    (WL #8157)

Test Suite Notes

  • 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 mysqltest rmdir 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: 

    --error CR_SERVER_GONE_ERROR

    (Bug #21048973, Bug #76972)

  • The mysqltest program now has a copy_files_wildcard command that copies all files that match a pattern from a source directory to a destination directory. See the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html. (Bug #82111, Bug #23743035)

X Plugin Notes

  • 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:

    • Remove the table's partitioning; you can do this without any data loss by executing an ALTER TABLE ... REMOVE PARTITIONING statement.

    • 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.

      For information about converting MyISAM tables to 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 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).

    For more information, see Partitioning Limitations Relating to Storage Engines. (WL #8971, WL #9457)

  • InnoDB: The innodb_buffer_pool_debug option 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, may 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, WL #9383)

  • InnoDB: The libinnodb_zipdecompress.a library allows external tools to use the page_zip_decompress_low() function to decompress InnoDB pages. (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, WL #8423)

  • 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. (WL #9387)

  • InnoDB: InnoDB code now uses the C++ std::thread library for thread management. (WL #9359)

  • InnoDB: BLOB code was refactored to provide an internal C++ interface for operations on compressed and uncompressed BLOB data. (WL #8985, WL #9141)

  • InnoDB: The InnoDB memcached plugin now supports multiple get operations (fetching multiple key/value pairs in a single memcached query) and range queries. See InnoDB memcached Multiple get and Range Query Support. (WL #6650)

  • 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. (WL #7816)

  • InnoDB: InnoDB no longer creates .isl files (InnoDB Symbolic Link files) when creating tablespace data files outside of the MySQL data directory.

    With this change, moving a remote tablespace while the server is offline by manually modifying an .isl file is not supported. (WL #6416)

  • 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.

    The PER_TABLE_TABLESPACE and IS_COMPRESSED columns were removed from the INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO. (WL #7899)

  • InnoDB: The new INFORMATION_SCHEMA.INNODB_CACHED_INDEXES table reports the number of index pages cached in the InnoDB buffer pool for each index. (WL #7170)

  • InnoDB: The innodb_checksums system variable was removed. innodb_checksums was replaced by innodb_checksum_algorithm in MySQL 5.6.3. (WL #8893)

  • InnoDB: InnoDB startup code was refactored. (WL #7488)

  • InnoDB: The innodb_flush_method default value is no longer NULL. On Unix-like systems, the default value is fsync. On Windows systems, the default value is unbuffered.

    On Windows, the innodb_flush_method setting no longer affects the innodb_use_native_aio setting. There are now two possible settings for innodb_flush_method on Windows, 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 (innodb_use_native_aio=ON and innodb_flush_method=normal). The async_unbuffered setting was removed.

    You can now set innodb_flush_method and innodb_change_buffering configuration options using numeric values. (WL #7488)

  • InnoDB: InnoDB no longer creates an .isl file (InnoDB Symbolic Link files) when creating a file-per-table tablespace data file outside of the MySQL data directory. InnoDB now 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 .isl file is no longer supported. (WL #6416)

  • InnoDB: The current maximum auto-increment counter value is now written to the redo log each time the value changes, and 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.

    For more information, see AUTO_INCREMENT Handling in InnoDB, and InnoDB AUTO_INCREMENT Counter Initialization. (Bug #199, Bug #13726455, WL #6204)

  • 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. (WL #6591)

  • 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 (WL #7364)

  • 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.

    For more information, see Functions That Search JSON Values, and JSON Path Syntax. (Bug #78736, Bug #21980346, WL #9124)

  • 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:

    (Bug #22884886)

  • 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_LZ4 CMake option is set to system, lz4_decompress is not built or installed. If the WITH_ZLIB CMake 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)

  • For improved performance and better compatibility with other platforms, my_sync() on OS X now uses fsync() rather than fcntl() with the F_FULLSYNC flag. (Bug #20742269)

  • 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 LOCK_thd_list mutex, LOCK_thd_remove mutex, and COND_thd_list condition variable. (WL #9250)

  • 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.

    For more information about these functions, see Miscellaneous Functions (WL #8920)

  • 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. (WL #7784)

  • 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. (WL #8186)

Bugs Fixed

  • Incompatible Change: Concatenation of spatial values makes little sense, so the CONCAT() and CONCAT_WS() functions now produce an error for spatial arguments. (Bug #22893669)

  • Important Change; JSON: The empty string value is now accepted as a key when used with JSON functions such as JSON_EXTRACT(). In such cases, it must be quoted. (Bug #79643, Bug #22366102)

  • NDB Cluster: Previously, the mysql.ndb_binlog_index table 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; Microsoft Windows: An unspecified block size resulted in an empty INFORMATION_SCHEMA.TABLESPACES table on Windows NTFS with a cluster page size greater than or equal to 8K. (Bug #23598872)

  • InnoDB; Microsoft Windows: Compilation of InnoDB with Visual Studio 2015 Update 2 returned warnings. (Bug #23056963)

  • InnoDB: An ALTER TABLE ... ALGORITHM=COPY operation that added a foreign key constraint failed after due to an intermediate commit that occurred after 10000 rows were copied. The intermediate commit reset the foreign key checks flag, causing the operation to fail. The intermediate commit no longer occurs. (Bug #28662255, Bug #92471)

    References: See also: Bug #17479594.

  • 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)

  • InnoDB: dict_col_t accessors were added to the InnoDB code. (Bug #24363566)

  • InnoDB: dict_col functions in the InnoDB code were replaced by accessors. (Bug #24361098)

  • InnoDB: dict_index_t functions in the InnoDB code 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 dict_table_t. (Bug #23748128)

  • 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: The mutex_own() mapping caused warnings when compiling with Clang or newer GCC compilers. (Bug #23090278)

  • InnoDB: Querying the Performance Schema for InnoDB memory allocation event data incorrectly reported values of 0. (Bug #23020280)

  • 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: SHOW ENGINE INNODB STATUS output displayed negative spin rounds per wait values. Thanks to Laurynas Biveinis for the patch. (Bug #22844987, Bug #79703)

  • InnoDB: The innodb_disable_resize_buffer_pool_debug option was removed. The patch for this change also removed a code variable and simplified the buf_pool_resize() function. (Bug #22755053)

  • InnoDB: A global counter (ut_rnd_ulint_counter) was changed to a thread-local counter to make it scalable on multi-core systems. (Bug #22733635, Bug #80354)

  • InnoDB: After a successful ALTER TABLE ... ALGORITHM=COPY operation, 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)

  • InnoDB: The InnoDB memcached plugin would not load when compiled with libevent 2.0. (Bug #22646919)

  • InnoDB: Unused calculations for integer-based rec_per_key values were removed from InnoDB. Integer-based rec_per_key information 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)

  • InnoDB: InnoDB recovery 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)

  • 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: SHOW CREATE TABLE output for partitioned tables did not accurately display tablespace assignment information for table partitions. (Bug #22245554)

  • InnoDB: An ALTER TABLE...TRUNCATE PARTITION operation ignored the table's KEY_BLOCK_SIZE attribute and used the default value instead, which is half of the innodb_page_size value. (Bug #22186558, Bug #79223)

  • InnoDB: Memory leaks in innochecksum were corrected. (Bug #22179518)

  • 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: A buffer pool load operation that attempted to load an uninitialized page caused a Valgrind failure. (Bug #21747906)

  • InnoDB: Unused functions introduced by the InnoDB memcached plugin were removed. (Bug #21625760)

  • InnoDB: An internal global variable used by the innodb_buffer_pool_size configuration option was removed. (Bug #21512749)

  • InnoDB: An InnoDB page 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)

  • InnoDB: Unused InnoDB and libsql functions and variables were removed, and global symbols were converted to static keywords, where possible. (Bug #21153166, Bug #21141390, Bug #77146, Bug #21178589)

  • InnoDB: A number of unused predefined functions were removed from an internal SQL parser that is used for implementing full-text indexes, updating the data dictionary, and updating persistent statistics. (Bug #21126390, Bug #77111)

  • 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: In mtr0mtr.cc, a redundant function was removed, and the ReleaseBlocks function was renamed to AddDirtyBlocksToFlushList. (Bug #20735882, Bug #76343)

  • 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)

  • InnoDB: TRUNCATE TABLE is now mapped to DROP TABLE and CREATE TABLE. This change has the following implications:

    • On systems with a large buffer pool and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations 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 TRUNCATE TABLE to DROP TABLE and CREATE TABLE avoids the problematic LRU scan.

    • 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 InnoDB SYS_FOREIGN and SYS_FOREIGN_COLS system tables.

    • The InnoDB memcached 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.

    (Bug #16834993, Bug #68184, Bug #16207919, WL #6795)

  • InnoDB: InnoDB wasted 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_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)

  • Replication: When using START SLAVE UNTIL position statements with a multithreaded slave the only UNTIL clause available was SQL_BEFORE_GTIDS. Now multithreaded slaves are compatible with the START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_position and START SLAVE UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_position statements. (Bug #75843, Bug #20513547)

  • Microsoft Windows: On Windows, setting the global log_syslog system variable in multiple threads could cause a server exit. (Bug #22180046)

  • Solaris: The client library failed to build on Solaris using the Cstd library. (Bug #24353920, Bug #82347)

  • 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)

  • 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_TABLES SQL mode enabled, an assertion could be raised by INSERT or REPLACE statements that had made changes that could not be rolled back and that subsequently generated an ER_NO_DEFAULT_FOR_FIELD error. (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 JSON value could raise an assertion. (Bug #22522073)

  • Preparing a CREATE TABLE ... SELECT statement, 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)

  • 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)

  • 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 -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)

  • SHOW CREATE USER returned unexpected results. (Bug #20625566)

  • 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)

  • A potential null-pointer dereference and memory leak in table-rename code were corrected. (Bug #18194270)

  • 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.

    • 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 enables several INFORMATION_SCHEMA problems to be addressed:

    • Queries on INFORMATION_SCHEMA.STATISTICS could return different results depending on the order of columns in the select list.

    • 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 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_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)

  • FLOOR(CEIL()) truncated large BIGINT UNSIGNED arguments. (Bug #80873, Bug #23013359)

  • Manipulation of a value returned by the JSON_MERGE() function using JSON_SET() sometimes produced an invalid result. (Bug #80787, Bug #22961128)

  • ST_AsGeoJSON() failed when geometry arguments were supplied using user-defined variables. (Bug #80697, Bug #22912800)

  • CAST(expr AS BINARY(N) ) unexpectedly returned NULL for 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_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)

  • SUBSTRING_INDEX(str, delim, count) did not properly handle count values 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 'a' rather than 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)

  • GREATEST() and LEAST() treated all integer input as signed. (Bug #79902, Bug #22523685)

  • 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)

  • 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=ON CMake 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)

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

  • The client-side plugin deinitialization function signature was changed from int (*deinit)() to int (*deinit)(void) to avoid warnings when compiling with -Wstrict-prototypes. (Bug #78177, Bug #21680094, Bug #81419, Bug #23282498)

  • 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)

  • The server now tries to provide more informative messages for these error codes: ER_CANT_CREATE_DB, ER_CANT_CREATE_TABLE, ER_DB_DROP_RMDIR, ER_ERROR_DURING_COMMIT, ER_ERROR_DURING_ROLLBACK, 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 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)

  • Queries with multiple LIMIT clauses were not always interpreted in accordance with the SQL standard.

    For example, each of the following queries now returns two rows, as expected: 

    SELECT * FROM t LIMIT 2;

(SELECT * FROM t LIMIT 2) LIMIT 4;

((SELECT * FROM t LIMIT 2) LIMIT 4) LIMIT 3;

    This also fixes an issue with UNION subselects using FROM DUAL; for example, the following query raised an error: 

    SELECT 'A' AS `x` FROM DUAL
  UNION
SELECT 'B' FROM DUAL
  UNION
SELECT 'C' FROM DUAL
  ORDER BY `x` ASC;

    (Bug #50510, Bug #74251, Bug #11758322, Bug #19774083)

  • Test cases that were intended to be storage engine-agnostic but were actually using a specific engine were corrected. (WL #7264)


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.0 Reference Manual
Download these Release Notes
PDF (US Ltr) - 4.2Mb
PDF (A4) - 4.3Mb