The grant tables in the mysql system database are now InnoDB (transactional) tables. Previously, these were MyISAM (nontransactional) tables. The change of grant table storage engine underlies an accompanying change to the behavior of account-management statements. Previously, an account-management statement (such as CREATE USER or DROP USER) 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. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.

A new caching_sha2_password authentication plugin is available. Like the sha256_password plugin, caching_sha2_password implements SHA-256 password hashing, but uses caching to address latency issues at connect time. It also supports more connection protocols and does not require linking against OpenSSL for RSA key pair-based password-exchange capabilities. See Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”.

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is now the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. For information about the implications of this change of default plugin for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

MySQL now supports roles, which are named collections of privileges. 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 applicable 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 Section 6.2.10, “Using Roles”.

MySQL now incorporates the concept of user account categories, with system and regular users distinguished according to whether they have the SYSTEM_USER privilege. See Section 6.2.11, “Account Categories”.

Previously, it was not possible to grant privileges that apply globally except for certain schemas. This is now possible if the partial_revokes system variable is enabled. See Section 6.2.12, “Privilege Restriction Using Partial Revokes”.

The GRANT statement has an AS user [WITH ROLE] clause that specifies additional information about the privilege context to use for statement execution. This syntax is visible at the SQL level, although its primary purpose is to enable uniform replication across all nodes of grantor privilege restrictions imposed by partial revokes, by causing those restrictions to appear in the binary log. See Section 13.7.1.6, “GRANT Syntax”.

MySQL now maintains information about password history, enabling restrictions on reuse of previous passwords. DBAs can require that new passwords not be selected from previous passwords for some number of password changes or period of time. It is possible to establish password-reuse policy globally as well as on a per-account basis.

It is now possible to require that attempts to change account passwords be verified by specifying the current password to be replaced. This enables DBAs to prevent users from changing password without proving that they know the current password. It is possible to establish password-verification policy globally as well as on a per-account basis.

Accounts are now permitted to have dual passwords, which enables phased password changes to be performed seamlessly in complex multiple-server systems, without downtime.

These new capabilities provide DBAs more complete control over password management. For more information, see Section 6.2.15, “Password Management”.

MySQL now supports FIPS mode, if compiled using OpenSSL, and an OpenSSL library and FIPS Object Module are available at runtime. FIPS mode imposes conditions on cryptographic operations such as restrictions on acceptable encryption algorithms or requirements for longer key lengths. See Section 6.5, “FIPS Support”.

The SSL context the server uses for new connections now is reconfigurable at runtime. This capability may be useful, for example, to avoid restarting a MySQL server that has been running so long that its SSL certificate has expired. See Server-Side Runtime Configuration for Encrypted Connections.

OpenSSL 1.1.1 supports the TLS v1.3 protocol for encrypted connections, and MySQL now supports TLS v1.3 as well, if compiled using OpenSSL 1.1.1 or higher. See Section 6.3.5, “Encrypted Connection Protocols and Ciphers”.

MySQL now sets the access control granted to clients on the named pipe to the minimum necessary for successful communication on Windows. Newer MySQL client software can open named pipe connections without any additional configuration. If older client software cannot be upgraded immediately, the new named_pipe_full_access_group system variable can be used to give a Windows group the necessary permissions to open a named pipe connection. Membership in the full-access group should be restricted and temporary.

The current maximum auto-increment counter value is 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 Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB”, and InnoDB AUTO_INCREMENT Counter Initialization.

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.

The InnoDB memcached plugin supports multiple get operations (fetching multiple key-value pairs in a single memcached query) and range queries. See Section 15.19.4, “InnoDB memcached Multiple get and Range Query Support”.

A new dynamic variables, 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.

The new INFORMATION_SCHEMA.INNODB_CACHED_INDEXES table reports the number of index pages cached in the InnoDB buffer pool for each index.

InnoDB temporary tables are now created in the shared temporary tablespace, ibtmp1.

The InnoDB tablespace encryption feature supports encryption of redo log and undo log data. See Redo Log Encryption, and Undo Log Encryption.

InnoDB supports NOWAIT and SKIP LOCKED options with SELECT ... FOR SHARE and SELECT ... FOR UPDATE locking read statements. NOWAIT causes the statement to return immediately if a requested row is locked by another transaction. SKIP LOCKED removes locked rows from the result set. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.

SELECT ... FOR SHARE replaces SELECT ... LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However, FOR UPDATE and FOR SHARE support NOWAIT, SKIP LOCKED, and OF tbl_name options. See Section 13.2.10, “SELECT Syntax”.

OF tbl_name applies locking queries to named tables.

ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, and REBUILD PARTITION ALTER TABLE options are supported by native partitioning in-place APIs and may be used with ALGORITHM={COPY|INPLACE} and LOCK clauses.

DROP PARTITION with ALGORITHM=INPLACE deletes data stored in the partition and drops the partition. However, DROP PARTITION with ALGORITHM=COPY or old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION ... VALUES definition. Data that cannot be moved to another partition is deleted.

The InnoDB storage engine now uses the MySQL data dictionary rather than its own storage engine-specific data dictionary. For information about the data dictionary, see Chapter 14, MySQL Data Dictionary.

mysql system tables and data dictionary tables are now created in a single InnoDB tablespace file named mysql.ibd in the MySQL data directory. Previously, these tables were created in individual InnoDB tablespace files in the mysql database directory.

The following undo tablespace changes are introduced in MySQL 8.0:

Default values for variables that affect buffer pool preflushing and flushing behavior were modified:

The default innodb_autoinc_lock_mode setting is now 2 (interleaved). Interleaved lock mode permits the execution of multi-row inserts in parallel, which improves concurrency and scalability. The new innodb_autoinc_lock_mode default setting reflects the change from statement-based replication to row based replication as the default replication type in MySQL 5.7. Statement-based replication requires the consecutive auto-increment lock mode (the previous default) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of SQL statements, whereas row-based replication is not sensitive to the execution order of SQL statements. For more information, see InnoDB AUTO_INCREMENT Lock Modes.

For systems that use statement-based replication, the new innodb_autoinc_lock_mode default setting may break applications that depend on sequential auto-increment values. To restore the previous default, set innodb_autoinc_lock_mode to 1.

Renaming a general tablespace is supported by ALTER TABLESPACE ... RENAME TO syntax.

The new innodb_dedicated_server variable, which is disabled by default, can be used to have InnoDB automatically configure the following options according to the amount of memory detected on the server:

This option is intended for MySQL server instances that run on a dedicated server. For more information, see Section 15.8.12, “Enabling Automatic Configuration for a Dedicated MySQL Server”.

The new INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF view provides space, name, path, flag, and space type data for InnoDB tablespaces.

The zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11. MySQL implements compression with the help of the zlib library.

If you use InnoDB compressed tables, see Section 2.11.4, “Changes in MySQL 8.0” for related upgrade implications.

Serialized Dictionary Information (SDI) is present in all InnoDB tablespace files except for global temporary tablespace and undo tablespace files. SDI is serialized metadata for table and tablespace objects. The presence of SDI data provides metadata redundancy. For example, dictionary object metadata may be extracted from tablespace files if the data dictionary becomes unavailable. SDI extraction is performed using the ibd2sdi tool. SDI data is stored in JSON format.

The inclusion of SDI data in tablespace files increases tablespace file size. An SDI record requires a single index page, which is 16KB in size by default. However, SDI data is compressed when it is stored to reduce the storage footprint.

The InnoDB storage engine now supports atomic DDL, which ensures that DDL operations are either fully committed or rolled back, even if the server halts during the operation. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.

Tablespace files can be moved or restored to a new location while the server is offline using the innodb_directories option. For more information, see Section 15.6.3.8, “Moving Tablespace Files While the Server is Offline”.

The following redo logging optimizations were implemented:

For more information, see Section 8.5.4, “Optimizing InnoDB Redo Logging”.

As of MySQL 8.0.12, undo logging is supported for small updates to large object (LOB) data, which improves performance of LOB updates that are 100 bytes in size or less. Previously, LOB updates were a minimum of one LOB page in size, which is less than optimal for updates that might only modify a few bytes. This enhancement builds upon support added in MySQL 8.0.4 for partial update of LOB data.

As of MySQL 8.0.12, ALGORITHM=INSTANT is supported for the following ALTER TABLE operations:

Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. No metadata locks are taken on the table, and table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by operations that support it. If ALGORITHM=INSTANT is specified but not supported, the operation fails immediately with an error.

For more information about operations that support ALGORITHM=INSTANT, see Section 15.12.1, “Online DDL Operations”.

As of MySQL 8.0.13, the TempTable storage engine supports storage of binary large object (BLOB) type columns. This enhancement improves performance for queries that use temporary tables containing BLOB data. Previously, temporary tables that contained BLOB data were stored in the on-disk storage engine defined by internal_tmp_disk_storage_engine. For more information, see Section 8.4.4, “Internal Temporary Table Use in MySQL”.

As of MySQL 8.0.13, the InnoDB data-at-rest encryption feature supports general tablespaces. Previously, only file-per-table tablespaces could be encrypted. To support encryption of general tablespaces, CREATE TABLESPACE and ALTER TABLESPACE syntax was extended to include an ENCRYPTION clause.

The INFORMATION_SCHEMA.INNODB_TABLESPACES table now includes an ENCRYPTION column that indicates whether or not a tablespace is encrypted.

The stage/innodb/alter tablespace (encryption) Performance Schema stage instrument was added to permit monitoring of general tablespace encryption operations.

Disabling the innodb_buffer_pool_in_core_file variable reduces the size of core files by excluding InnoDB buffer pool pages. To use this variable, the core_file variable must be enabled and the operating system must support the MADV_DONTDUMP non-POSIX extension to madvise(), which is supported in Linux 3.4 and later. For more information, see Section 15.8.3.8, “Excluding Buffer Pool Pages from Core Files”.

As of MySQL 8.0.13, user-created temporary tables and internal temporary tables created by the optimizer are stored in session temporary tablespaces that are allocated to a session from a pool of temporary tablespaces. When a session disconnects, its temporary tablespaces are truncated and released back to the pool. In previous releases, temporary tables were created in the global temporary tablespace (ibtmp1), which did not return disk space to the operating system after temporary tables were dropped.

The innodb_temp_tablespaces_dir variable defines the location where session temporary tablespaces are created. The default location is the #innodb_temp directory in the data directory.

The INNODB_SESSION_TEMP_TABLESPACES table provides metadata about session temporary tablespaces.

The global temporary tablespace (ibtmp1) now stores rollback segments for changes made to user-created temporary tables.

As of MySQL 8.0.14, InnoDB supports parallel clustered index reads, which can improve CHECK TABLE performance. This feature does not apply to secondary index scans. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller.

As of 8.0.14, when the innodb_dedicated_server variable is enabled, the size and number of log files are configured according to the automatically configured buffer pool size. Previously, log file size was configured according to the amount of memory detected on the server, and the number of log files was not configured automatically.

As of 8.0.14, the ADD DATAFILE clause of the CREATE TABLESPACE statement is optional, which permits users without the FILE privilege to create tablespaces. A CREATE TABLESPACE statement executed without an ADD DATAFILE clause implicitly creates a tablespace data file with a unique file name.

By default, when the amount of memory occupied by the TempTable storage engine exceeds the memory limit defined by the temptable_max_ram variable, the TempTable storage engine begins allocating memory-mapped temporary files from disk. As of MySQL 8.0.16, this behavior is controlled by the temptable_use_mmap variable. Disabling temptable_use_mmap causes the TempTable storage engine to use InnoDB on-disk internal temporary tables instead of memory-mapped files as its overflow mechanism. For more information, see Internal Temporary Table Storage Engine.

As of MySQL 8.0.16, the InnoDB data-at-rest encryption feature supports encryption of the mysql system tablespace. The mysql system tablespace contains the mysql system database and the MySQL data dictionary tables. For more information, see Section 15.6.3.9, “InnoDB Data-at-Rest Encryption”.

The innodb_spin_wait_pause_multiplier variable, introduced in MySQL 8.0.16, provides greater control over the duration of spin-lock polling delays that occur when a thread waits to acquire a mutex or rw-lock. Delays can be tuned more finely to account for differences in PAUSE instruction duration on different processor architectures. For more information, see Section 15.8.8, “Configuring Spin Lock Polling”.

Added the ->> (inline path) operator, which is equivalent to calling JSON_UNQUOTE() on the result of JSON_EXTRACT().

This is a refinement of the column path operator -> introduced in MySQL 5.7; col->>"$.path" is equivalent to JSON_UNQUOTE(col->"$.path"). The inline path operator can be used wherever you can use JSON_UNQUOTE(JSON_EXTRACT()), such SELECT column lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses. For more information, see the description of the operator, as well as JSON Path Syntax.

Added two JSON aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG(). JSON_ARRAYAGG() takes a column or expression as its argument, and aggregates the result as a single JSON array. The expression can evaluate to any MySQL data type; this does not have to be a JSON value. JSON_OBJECTAGG() takes two columns or expressions which it interprets as a key and a value; it returns the result as a single JSON object. For more information and examples, see Section 12.20, “Aggregate (GROUP BY) Functions”.

Added the JSON utility function JSON_PRETTY(), which outputs an existing JSON value in an easy-to-read format; each JSON object member or array value is printed on a separate line, and a child object or array is intended 2 spaces with respect to its parent.

This function also works with a string that can be parsed as a JSON value.

For more detailed information and examples, see Section 12.17.8, “JSON Utility Functions”.

When sorting JSON values in a query using ORDER BY, each value is now represented by a variable-length part of the sort key, rather than a part of a fixed 1K in size. In many cases this can reduce excessive usage. For example, a scalar INT or even BIGINT value actually requires very few bytes, so that the remainder of this space (up to 90% or more) was taken up by padding. This change has the following benefits for performance:

Added support in MySQL 8.0.2 for partial, in-place updates of JSON column values, which is more efficient than completely removing an existing JSON value and writing a new one in its place, as was done previously when updating any JSON column. For this optimization to be applied, the update must be applied using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(). New elements cannot be added to the JSON document being updated; values within the document cannot take more space than they did before the update. See Partial Updates of JSON Values, for a detailed discussion of the requirements.

Partial updates of JSON documents can be written to the binary log, taking up less space than logging complete JSON documents. Partial updates are always logged as such when statement-based replication is in use. For this to work with row-based replication, you must first set binlog_row_value_options=PARTIAL_JSON; see this variable's description for more information.

Added the JSON utility functions JSON_STORAGE_SIZE() and JSON_STORAGE_FREE(). JSON_STORAGE_SIZE() returns the storage space in bytes used for the binary representation of a JSON document prior to any partial update (see previous item). JSON_STORAGE_FREE() shows the amount of space remaining in a table column of type JSON after it has been partially updated using JSON_SET() or JSON_REPLACE(); this is greater than zero if the binary representation of the new value is less than that of the previous value.

Each of these functions also accepts a valid string representation of a JSON document. For such a value, JSON_STORAGE_SIZE() returns the space used by its binary representation following its conversion to a JSON document. For a variable containing the string representation of a JSON document, JSON_STORAGE_FREE() returns zero. Either function produces an error if its (non-null) argument cannot be parsed as a valid JSON document, and NULL if the argument is NULL.

For more information and examples, see Section 12.17.8, “JSON Utility Functions”.

JSON_STORAGE_SIZE() and JSON_STORAGE_FREE() were implemented in MySQL 8.0.2.

Added support in MySQL 8.0.2 for ranges such as $[1 to 5] in XPath expressions. Also added support in this version for the last keyword and relative addressing, such that $[last] always selects the last (highest-numbered) element in the array and $[last-1] the next to last element. last and expressions using it can also be included in range definitions. For example, $[last-2 to last-1] returns the last two elements but one from an array. See Searching and Modifying JSON Values, for additional information and examples.

Added a JSON merge function intended to conform to RFC 7396. JSON_MERGE_PATCH(), when used on 2 JSON objects, merges them into a single JSON object that has as members a union of the following sets:

As part of this work, the JSON_MERGE() function has been renamed JSON_MERGE_PRESERVE(). JSON_MERGE() continues to be recognized as an alias for JSON_MERGE_PRESERVE() in MySQL 8.0, but is now deprecated and is subject to removal in a future version of MySQL.

For more information and examples, see Section 12.17.4, “Functions That Modify JSON Values”.

Implemented “last duplicate key wins” normalization of duplicate keys, consistent with RFC 7159 and most JavaScript parsers. An example of this behavior is shown here, where only the rightmost member having the key x is preserved:

mysql> SELECT JSON_OBJECT('x', '32', 'y', '[true, false]', 
     >                     'x', '"abc"', 'x', '100') AS Result;
+------------------------------------+
| Result                             |
+------------------------------------+
| {"x": "100", "y": "[true, false]"} |
+------------------------------------+
1 row in set (0.00 sec)

Values inserted into MySQL JSON columns are also normalized in this way, as shown in this example:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": [3, 5, 7]} |
+------------------+

This is an incompatible change from previous versions of MySQL, where a “first duplicate key wins” algorithm was used in such cases.

See Normalization, Merging, and Autowrapping of JSON Values, for more information and examples.

Added the JSON_TABLE() function in MySQL 8.0.4. This function accepts JSON data and returns it as a relational table having the specified columns.

This function has the syntax JSON_TABLE(expr, path COLUMNS column_list) [AS] alias), where expr is an expression that returns JSON data, path is a JSON path applied to the source, and column_list is a list of column definitions. An example is shown here:

mysql> SELECT * 
    -> FROM  
    ->   JSON_TABLE(
    ->     '[{"a":3,"b":"0"},{"a":"3","b":"1"},{"a":2,"b":1},{"a":0},{"b":[1,2]}]',
    ->     "$[*]" COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->   
    ->       xa INT EXISTS PATH "$.a",
    ->       xb INT EXISTS PATH "$.b",
    ->       
    ->       sa VARCHAR(100) PATH "$.a",
    ->       sb VARCHAR(100) PATH "$.b",
    ->       
    ->       ja JSON PATH "$.a",       
    ->       jb JSON PATH "$.b"
    ->     )   
    ->   ) AS  jt1;
+-------+------+------+------+------+------+--------+
| rowid | xa   | xb   | sa   | sb   | ja   | jb     |
+-------+------+------+------+------+------+--------+
|     1 |    1 |    1 | 3    | 0    | 3    | "0"    |
|     2 |    1 |    1 | 3    | 1    | "3"  | "1"    |
|     3 |    1 |    1 | 2    | 1    | 2    | 1      |
|     4 |    1 |    0 | 0    | NULL | 0    | NULL   |
|     5 |    0 |    1 | NULL | NULL | NULL | [1, 2] |
+-------+------+------+------+------+------+--------+

The JSON source expression can be any expression that yields a valid JSON document, including a JSON literal, a table column, or a function call that returns JSON such as JSON_EXTRACT(t1, data, '$.post.comments'). For more information, see Section 12.17.6, “JSON Table Functions”.

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. See Section 8.3.12, “Invisible Indexes”.

MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others. See Section 8.3.13, “Descending Indexes”.

MySQL now supports creation of functional index key parts that index expression values rather than column values. Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values. For details, see Section 13.1.15, “CREATE INDEX Syntax”.

In MySQL 8.0.14 and later, trivial WHERE conditions arising from constant literal expressions are removed during preparation, rather than later on during optimization. Removal of the condition earlier in the process makes it possible to simplify joins for queries with outer joins having trivial conditions, such as this one:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1

The optimizer now sees during preparation that 0 = 1 is always false, making OR 0 = 1 redundant, and removes it, leaving this:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2

Now the optimizer can rewrite the query as an inner join, like this:

SELECT * FROM t1 LEFT JOIN t2 WHERE condition_1 AND condition_2

For more information, see Section 8.2.1.8, “Outer Join Optimization”.

In MySQL 8.0.16 and later, MySQL can use constant folding at optimization time to handle comparisons between a column and a constant value where the constant is out of range or on a range boundary with respect to the type of the column, rather than doing so for each row at execution time. For example, given a table t with a TINYINT UNSIGNED column c, the optimizer can rewrite a condition such as WHERE c < 256 to WHERE 1 (and optimize the condition away altogether), or WHERE c >= 255 to WHERE c = 255.

See Section 8.2.1.13, “Constant-Folding Optimization”, for more information.

Beginning with MySQL 8.0.16, the semi-join optimizations used with IN subqueries can now be applied to EXISTS subqueries as well. In addition, the optimizer now decorrelates trivially-correlated equality predicates in the WHERE condition attached to the subquery, so that they can be treated similarly to expressions in IN subqueries; this applies to both EXISTS and IN subqueries.

For more information, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery predicates with Semi-Join Transformations”.

MySQL Replication now supports binary logging of partial updates to JSON documents using a compact binary format, saving space in the log over logging complete JSON documents. Such compact logging is done automatically when statement-based logging is in use, and can be enabled by setting the new binlog_row_value_options system variable to PARTIAL_JSON. For more information, see Partial Updates of JSON Values, as well as the description of binlog_row_value_options.

The increase in permitted host name length can affect tables with indexes on host name columns. For example, tables in the mysql system schema that index host names now have an explicit ROW_FORMAT attribute of DYNAMIC to accommodate longer index values.

Some file name-valued configuration settings might be constructed based on the server host name. The permitted values are constrained by the underlying operating system, which may not permit file names long enough to include 255-character host names. This affects the general_log_file, log_error, pid_file, relay_log, and slow_query_log_file system variables and corresponding options. If host name-based values are too long for the OS, explicit shorter values must be provided.

Although the server now supports 255-character host names, connections to the server established using the --ssl-mode=VERIFY_IDENTITY option are constrained by maximum host name length supported by OpenSSL. Host name matches pertain to two fields of SSL certificates, which have maximum lengths as follows: Common Name: maximum length 64; Subject Alternative Name: maximum length as per RFC#1034.

The schema must conform to Draft 4 of the JSON Schema specification.

required attributes are supported.

External resources and the $ref keyword are not supported.

Regular expression patterns are supported; invalid patterns are silently ignored.

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

IDENTIFIED BY PASSWORD 'auth_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS 'auth_string' for CREATE USER and ALTER USER, where the 'auth_string' value is in a format compatible with the named plugin.

Additionally, because IDENTIFIED BY PASSWORD syntax was removed, the log_builtin_as_identified_by_password system variable is superfluous and was removed.

The PASSWORD() function. Additionally, PASSWORD() removal means that SET PASSWORD ... = PASSWORD('auth_string') syntax is no longer available.

The old_passwords system variable.

The FLUSH QUERY CACHE and RESET QUERY CACHE statements.

These system variables: query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate.

These status variables: Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts, Qcache_lowmem_prunes, Qcache_not_cached, Qcache_queries_in_cache, Qcache_total_blocks.

These thread states: checking privileges on cached query, checking query cache for query, invalidating query cache entries, sending cached result to client, storing result in query cache, Waiting for query cache lock.

The SQL_CACHE SELECT modifier.

SQL_NO_CACHE SELECT modifier.

The ndb_cache_check_time system variable.

The ENCODE() and DECODE() functions.

The ENCRYPT() function.

The DES_ENCRYPT(), and DES_DECRYPT() functions, the --des-key-file option, the have_crypt system variable, the DES_KEY_FILE option for the FLUSH statement, and the HAVE_CRYPT CMake option.

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

Remove the table's partitioning, using ALTER TABLE ... REMOVE PARTITIONING.

Change the storage engine used for the table to InnoDB, with ALTER TABLE ... ENGINE=INNODB.

Remove any references to partitioning from CREATE TABLE statements that use a value for the STORAGE ENGINE option other than InnoDB.

Specifying the storage engine as InnoDB, or allow InnoDB to be used as the table's storage engine by default.

The mysql_options() MYSQL_OPT_GUESS_CONNECTION, MYSQL_OPT_USE_EMBEDDED_CONNECTION, MYSQL_OPT_USE_REMOTE_CONNECTION, and MYSQL_SET_CLIENT_IP options

The mysql_config --libmysqld-libs, --embedded-libs, and --embedded options

The CMake WITH_EMBEDDED_SERVER, WITH_EMBEDDED_SHARED_LIBRARY, and INSTALL_SECURE_FILE_PRIV_EMBEDDEDDIR options

The (undocumented) mysql --server-arg option

The mysqltest --embedded-server, --server-arg, and --server-file options

The mysqltest_embedded and mysql_client_test_embedded test programs

innodb_file_format

innodb_file_format_check

innodb_file_format_max

innodb_large_prefix