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.

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:

For more information, see Using Roles.

(WL #988)

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)

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

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

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

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

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

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:

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)

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

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)

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)

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.

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:

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

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)

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

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)

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)

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)

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:

(WL #8896)

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:

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

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

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)

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

(WL #8157)

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

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)

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:

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:

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

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

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)

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:

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

For more information, see Performance Schema Connection Attribute Tables. (Bug #16576959)

Introduction of the data dictionary enables several INFORMATION_SCHEMA problems to be addressed:

(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 2⁶⁴ 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)