MySQL8.0.11からMySQL8.0.31で気になった部分だけ新機能と変更点をまとめました。(約150位)
ただ、変更点が多く、全てを網羅出来てません。確認が必要な場合は適宜リリースノートやマニュアルを確認して下さい。Bugに関しては、必要に応じてbugs.mysql.comを確認するので今回はリリースノートでは追っていません。機能追加が多く、それに比例してBugも多いので。
確認した機能追加・変更点
Summary of those changes
| Category | Version | Type | Change |
| SQL_MODE | 8.0.11 | Incompatible Change | These deprecated compatibility SQL modes have been removed: DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS. They can no longer be assigned to the sql_mode system variable or used as permitted values for the mysqldump –compatible option. |
| AUTH | 8.0.11 | Deprecate | 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. |
| AUTH | 8.0.11 | Deprecate | The old_passwords system variable is no longer available. |
| Information_schema | 8.0.11 | Newly Add | The new INFORMATION_SCHEMA.KEYWORDS table lists the words considered keywords by MySQL and, for each one, indicates whether it is reserved. T |
| Logging | 8.0.11 | Newly Add | Messages written to the error log now indicate the subsystem in which the event occurred. Possible subsystem values are InnoDB (the InnoDB storage engine), Repl (the replication subsystem), Server (otherwise). |
| Performance Schema | 8.0.11 | Newly Add | A new Performance Schema table log_status provides information that enables an online backup tool to copy the required log files without locking those resources for the duration of the copy process. When the log_status table is queried, the server blocks logging and related administrative changes for just long enough to populate the table, then releases the resources. The log_status table informs the online backup which point it should copy up to in the master’s binary log and gtid_executed record, and the relay log for each replication channel. |
| Buffer Pool | 8.0.11 | Newly Add | To improve startup performance on systems with large buffer pools, buffer pool initialization is now multithreaded. |
| SHOW CREATE TABLE | 8.0.11 | Newly Add | SHOW CREATE TABLE normally does not show the ROW_FORMAT table option if the row format is the default format. This can cause problems during table import and export operations for transportable tablespaces. MySQL now supports a show_create_table_verbosity system variable that, when enabled, causes SHOW CREATE TABLE to display ROW_FORMAT regardless of whether it is the default format. |
| ALTER TABLE | 8.0.12 | Newly Add | InnoDB now supports ALGORITHM=INSTANT for the following ALTER TABLE operations. |
| Account Management | 8.0.13 | Newly Add | It is now possible to require that attempts to change an account password be verified by specifying the current password to be replaced. This enables DBAs to prevent users from changing a password without proving that they know the current password. It is possible to establish password-verification policy globally using the password_require_current system variable, as well as on a per-account basis using the PASSWORD REQUIRE option of the CREATE USER and ALTER USER statements. |
| SQL_MODE | 8.0.13 | Newly Add | The new sql_require_primary_key system variable makes it possible to have statements that create new tables or alter the structure of existing tables enforce the requirement that tables have a primary key. Enabling this variable helps avoid performance problems in row-based replication that can occur when tables have no primary key. |
| InnoDB; Partitioning: | 8.0.13 | Incompatible Change | InnoDB; Partitioning: Support for placing table partitions in shared tablespaces was removed. Shared tablespaces include the system tablespace and general tablespaces. For information about identifying partitions in shared tablespaces and moving them to file-per-table tablespaces, see Preparing Your Installation for Upgrade. |
| TEMPORARY TABLE | 8.0.13 | Deprecate | CREATE TEMPORARY TABLE での TABLESPACE = innodb_file_per_table 句および TABLESPACE = innodb_temporary 句のサポートは、MySQL 8.0.13 で非推奨になりました。MySQL の将来のバージョンで削除される予定です。 |
| character | 8.0.13 | Deprecate | The utf8mb3 character set is deprecated and will be removed in a future MySQL version. Please use utf8mb4 instead. |
| INFORMATION_SCHEMA | 8.0.13 | Newly Add | select * from information_schema.VIEW_ROUTINE_USAGE; select * from information_schema.VIEW_TABLE_USAGE; |
| Replication | 8.0.13 | Incompatible Change | Previously, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements were not supported inside transactions, procedures, functions, or triggers when using GTIDs (that is, when the enforce_gtid_consistency system variable is set to ON). It was possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1. From MySQL 8.0.13, this restriction has been removed when binlog_format is set to ROW or MIXED. With row-based logging in use, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements can now be used inside transactions, procedures, functions, or triggers when GTIDs are enabled. When binlog_format is set to STATEMENT, the restriction remains. Because of this difference in behavior, some additional restrictions now apply to changing the binlog_format setting at runtime: |
| Replication | 8.0.13 | Incompatible Change | Trying to switch the replication format in any of these cases (or attempting to set the current replication format) results in an error. You can, however, use PERSIST_ONLY (SET @@PERSIST_ONLY.binlog_format) to change the replication format at any time, because this action does not modify the runtime global system variable value, and takes effect only after a server restart. |
| TEMPORARY TABLE | 8.0.13 | Deprecate | The TempTable storage engine now 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. |
| RENAME TABLE | 8.0.13 | Newly Add | Previously, executing RENAME TABLE required that there be no tables locked with LOCK TABLES. Now it is possible to rename tables that are locked with a WRITE lock or that are the product of renaming WRITE-locked tables from earlier steps in a multiple-table rename operation. |
| PARAMETER | 8.0.13 | Newly Add | innodb_fsync_threshold Specifying a threshold to force smaller, periodic flushes may be beneficial in cases where multiple MySQL instances use the same storage devices. For example, creating a new MySQL instance and its associated data files could cause large surges of disk write activity, impeding the performance of other MySQL instances that use the same storage devices. Configuring a threshold helps avoid such surges in write activity. (Bug #27724600) |
| TEMPORARY TABLE | 8.0.13 | Improvement | InnoDB: User-created temporary tables and internal temporary tables created by the optimizer are now 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. |
| ENCRYPTION | 8.0.13 | Improvement | InnoDB: The InnoDB data-at-rest encryption feature now 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 |
| TEMPORARY TABLE | 8.0.13 | Improvement | Previously, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements were not supported inside transactions, procedures, functions, or triggers when using GTIDs (that is, when the enforce_gtid_consistency system variable is set to ON). It was possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1. From MySQL 8.0.13, this restriction has been removed when binlog_format is set to ROW or MIXED. With row-based logging in use, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements can now be used inside transactions, procedures, functions, or triggers when GTIDs are enabled. When binlog_format is set to STATEMENT, the restriction remains. Because of this difference in behavior, some additional restrictions now apply to changing the binlog_format setting at runtime: If a session has open temporary tables, the replication format cannot be changed for the session (SET @@SESSION.binlog_format). If any replication channel has open temporary tables, the replication format cannot be changed globally (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format). If any replication channel applier thread is currently running, the replication format cannot be changed globally (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format). Trying to switch the replication format in any of these cases (or attempting to set the current replication format) results in an error. You can, however, use PERSIST_ONLY (SET @@PERSIST_ONLY.binlog_format) to change the replication format at any time, because this action does not modify the runtime global system variable value, and takes effect only after a server restart. |
| AUTH | 8.0.14 | Newly Add | Previously, each MySQL user account was permitted to have a single password. MySQL now permits an account to have dual passwords, designated as primary and secondary passwords. This capability enables phased password changes to be performed seamlessly in complex multiple-server systems, without downtime. To support dual-password capability, the ALTER USER and SET PASSWORD statements now have a RETAIN CURRENT PASSWORD clause that saves the current password as the secondary password when you assign an account a new primary password. ALTER USER also has a DISCARD OLD PASSWORD clause to discard a secondary password that is no longer needed. See Password Management. |
| AUTH | 8.0.14 | Newly Add | MySQL Server now permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established. The administrative network interface has these characteristics: The interface is enabled only if the admin_address system variable is set at startup to indicate the IP address for it. If admin_address is not set, the server maintains no administrative interface. The admin_port system variable specifies the interface TCP/IP port number (default 33062). There is no limit on the number of administrative connections, but connections are permitted only for users who have the SERVICE_CONNECTION_ADMIN privilege. The create_admin_listener_thread system variable enables DBAs to choose at startup whether the administrative interface has its own separate thread. The default is OFF; that is, the manager thread for ordinary connections on the main interface also handles connections for the administrative interface. |
| UNDO | 8.0.14 | Newly Add | InnoDB: 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 Excluding Buffer Pool Pages from Core Files. Thanks to Facebook for the contribution. (Bug #27724476, Bug #90144) InnoDB: By default, undo logs reside in two undo tablespaces that are created when the MySQL instance is initialized. Additional undo tablespaces can be created in a chosen location at runtime using CREATE UNDO TABLESPACE syntax. CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE ‘file_name.ibu’; Undo tablespaces created using CREATE UNDO TABLESPACE syntax can be dropped at runtime using DROP UNDO TABLESPACE syntax. DROP UNDO TABLESPACE tablespace_name; ALTER UNDO TABLESPACE syntax can be used to mark an undo tablespace as active or inactive. ALTER UNDO TABLESPACE tablespace_name SET {ACTIVE|INACTIVE}; A STATE column that shows the state of a tablespace was added to the INFORMATION_SCHEMA.INNODB_TABLESPACES table. An undo tablespace must be in an empty state before it can be dropped. The previously deprecated innodb_undo_tablespaces variable is no longer configurable and will be removed in a future MySQL version. For more information, see Undo Tablespaces. |
| INNODB | 8.0.14 | Newly Add | InnoDB: InnoDB now 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. |
| INNODB | 8.0.14 | Deprecate | The previously deprecated innodb_undo_tablespaces variable is no longer configurable and will be removed in a future MySQL version. |
| ALTER TABLE | 8.0.14 | Newly Add | ALTER TABLE now can be used to change a column character set in place (without a table rebuild), when these conditions apply: The column data type is CHAR, VARCHAR, a TEXT type, or ENUM. The character set change is from utf8mb3 to utf8mb4, or any character set to binary. There is no index on the column. |
| Account Management | 8.0.16 | Newly Add | Previously, it was not possible to grant privileges that apply globally except for certain schemas. This is now possible if the new partial_revokes system variable is enabled. For example, the following statements enable an account to select from or insert into any table except those in the mysql system schema: |
| TEMPORARY TABLE | 8.0.16 | Deprecate | The TempTable storage engine now always uses InnoDB to manage internal temporary tables on disk, and the choice of storage engine employed for this task is no longer user-configurable. The internal_tmp_disk_storage_engine system variable has been removed. (Bug #91377, Bug #28234637) |
| DATA DICTIONARY | 8.0.16 | Newly Add | mysql_upgrade is deprecated because it is no longer necessary. The –no-dd-upgrade server option is deprecated because the –upgrade option supersedes it. Previously, after installation of a new version of MySQL, the MySQL server automatically upgraded the data dictionary tables at the next startup, after which the DBA was expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the sys schema and user schemas. The server now performs the tasks previously handled by mysql_upgrade. After installation of a new MySQL version, the server now automatically performs all necessary upgrade tasks at the next startup and is not dependent on the DBA invoking mysql_upgrade. In addition, the server updates the contents of the help tables (something mysql_upgrade did not do). A new –upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations. For more information, see Upgrading MySQL. |
| PLUGIN | 8.0.16 | Newly Add | MySQL now includes a ddl_rewriter plugin that modifies CREATE TABLE statements received by the server before it parses and executes them. The plugin removes ENCRYPTION, DATA DIRECTORY, and INDEX DIRECTORY clauses, which may be helpful when restoring tables from SQL dump files created from databases that are encrypted or that have their tables stored outside the data directory. For example, the plugin may enable restoring such dump files into an unencrypted instance or in an environment where the paths outside the data directory are not accessible. When installed, ddl_rewriter exposes the Performance Schema memory/rewriter/ddl_rewriter instrument for tracking plugin memory use. For more information, see The ddl_rewriter Plugin. |
| ALTER INSTANCE | 8.0.16 8.0.21 | Newly Add | The ALTER INSTANCE statement supports a RELOAD TLS action that reconfigures the TLS context from the current values of the system variables that define the context. |
| INFORMATION_SCHEMA | 8.0.16 | Newly Add | Previously, MySQL permitted a limited form of CHECK constraint syntax, but parsed and ignored it. MySQL now implements the core features of table and column CHECK constraints, for all storage engines. Constraints are defined using CREATE TABLE and ALTER TABLE statements. The new INFORMATION_SCHEMA.CHECK_CONSTRAINTS table provides information about CHECK constraints defined on tables. For more information, see CHECK Constraints. (Bug #11744849, Bug #3464, Bug #3465, Bug #11746042, Bug #22759) |
| FUNCTION | 8.0.16 | Newly Add | FORMAT_BYTES(): Converts a byte count to a value with units. Similar to sys.format_bytes(). FORMAT_PICO_TIME(): Converts a time in picoseconds to a value with units. Similar to sys.format_time(). PS_THREAD_ID(): Returns the Performance Schema thread ID for a given thread. Similar to sys.ps_thread_id() invoked with a non-NULL argument. PS_CURRENT_THREAD_ID(): Returns the Performance Schema thread ID for the current thread. Shortcut for sys.ps_thread_id() invoked with a NULL argument. |
| TEMPORARY TABLE | 8.0.16 | Newly Add | InnoDB: When the amount of memory occupied by the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable, the TempTable storage engine allocates space for internal in-memory temporary tables as memory-mapped temporary files. This behavior is now controlled by the temptable_use_mmap variable, which can be disabled to have the TempTable storage engine use InnoDB on-disk internal temporary tables instead. For more information, see Internal Temporary Table Use in MySQL. (Bug #28944457) |
| FUNCTION | 8.0.16 | Deprecate | The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are now deprecated and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows. For example, instead of these queries: SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10; SELECT FOUND_ROWS(); |
| JSON | 8.0.16 | Newly Add | InnoDB; JSON: InnoDB now supports multi-valued indexes on JSON arrays. A multi-valued index is an index in which multiple index records can point to the same data record. This can be useful for indexing JSON documents such as {“user”:”Bob”,”zipcode”:[94477,94536]} in which, if we wish to search all zip codes, it is necessary to have two index records for each zip code in the document. We can create such an index on the zipcode array using a CREATE INDEX statement such as this one: |
| VARIABLES | 8.0.17 | Newly Add | In MySQL 8.0, the lower_case_table_names variable can only be configured when the MySQL server is initialized. Because a MySQL server installation on Debian and Ubuntu performed using APT initializes the MySQL server for you, there was no opportunity to enable lower_case_table_names. To work around this issue, you can now use the debconf-set-selection utility to enable lower_case_table_names (set lower_case_table_names=1) prior to installing MySQL using APT. To enable lower_case_table_names prior to installing MySQL using APT, execute the following command: shell> sudo debconf-set-selections <<< “mysql-server mysql-server/lowercase-table-names select Enabled |
| AUTH | 8.0.17 | CHANGED | The umask for files created using SELECT … INTO OUTFILE or SELECT … INTO DUMPFILE was changed from 0666 to 0640. The LOAD_FILE() function no longer requires files to be world-readable, just readable by the server. (Bug #24513720) |
| Replication | 8.0.17 | CHANGED | The mysqldump option –set-gtid-purged controls whether or not a SET @@GLOBAL.gtid_purged statement is added to the mysqldump output. The statement updates the value of gtid_purged on a server where the dump file is reloaded, to add the GTID set from the source server’s gtid_executed system variable. A new choice –set-gtid-purged=COMMENTED is now available. When this value is set, if GTIDs are enabled on the server you are backing up, SET @@GLOBAL.gtid_purged is added to the output (unless gtid_executed is empty), but it is commented out. This means that the value of gtid_executed is available in the output, but no action is taken automatically when the dump file is reloaded. With COMMENTED, you can control the use of the gtid_executed set manually or through automation. For example, you might prefer to do this if you are migrating data to another server that already has different active databases. Thanks to Facebook for this contribution. (Bug #94332, Bug #29357665) |
| FUNCTION | 8.0.17 | Newly Add | MySQL now supports explicit casts to DOUBLE, FLOAT, and REAL using either of the functions CAST() or CONVERT(). For more information, see Cast Functions and Operators. (Bug #30524, Bug #11747058) |
| TOOL | 8.0.17 | Newly Add | MySQL now provides a clone plugin that permits cloning InnoDB data locally or from a remote MySQL server instance. A local cloning operation stores cloned data on the same server or node where the MySQL instance runs. A remote cloning operation transfers cloned data over the network from a donor MySQL server instance to the recipient server or node where the cloning operation was initiated. |
| ACCount Management | 8.0.18 | Newly Add | The CREATE USER, ALTER USER, and SET PASSWORD statements now have the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords. See Password Management. |
| VARIABLES | 8.0.18 | Deprecate | Use of the MYSQL_PWD environment variable to specify a MySQL password is considered insecure because its value may be visible to other system users. MYSQL_PWD is now deprecated and will be removed in a future MySQL version. |
| HASH JOIN | 8.0.18 | Newly Add | Hash joins have been implemented as a way of executing inner equi-joins in MySQL. For example, a query such as this one can be executed as a hash join beginning with this release: Multi-table joins using equi-joins can also take advantage of this optimization. A hash join requires no index for execution. In most cases, a hash join is more efficient than the block-nested loop algorithm previously used for equi-joins without indexes. By default, beginning with this release, a hash join is used whenever a join includes at least one equi-join condition, and no indexes can be applied to the join condition. This preference can be overridden by setting the hash_join optimizer switch to off, or by using the NO_HASH_JOIN optimizer hint. In addition, you can control the amount of memory used by a hash join by setting join_buffer_size. A join whose memory requirement exceeds this amount is executed on disk; an on-disk hash join uses a number of disk files and may not be executable if this number exceeds open_files_limit. |
| SYS | 8.0.18 | Newly Add | The sys.schema_unused_indexes view now filters out unique indexes. Thanks to Gillian Gunson for the contribution. (Bug #24798995, Bug #83257) |
| SYS | 8.0.18 | Newly Add | The sys.ps_is_consumer_enabled() function now produces an error rather than returning NULL if the argument is an unknown non-NULL consumer name. (Bug #24760317) |
| AUTH | 8.0.19 | Newly Add | MySQL now enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. See Password Management. (Bug #27733694, Bug #90169) |
| VARIABLES | 8.0.19 | Newly Add | Innodb_system_rows_read, Innodb_system_rows_inserted, Innodb_system_rows_deleted status variables were added for counting row operations on InnoDB tables that belong to system-created schemas. The new status variables are similar to the existing Innodb_rows_read, Innodb_rows_inserted, Innodb_rows_deleted status variables, which count operations on InnoDB tables that belong to both user-created and system-created schemas. |
| HASH JOIN | 8.0.19 | Deprecate | Setting the hash_join optimizer switch (see optimizer_switch system variable) no longer has any effect. The same applies with respect to the HASH_JOIN and NO_HASH_JOIN optimizer hints. Both the optimizer switch and the optimizer hint are now deprecated, and subject to removal in a future release of MySQL. mysql> show variables like ‘optimizer_switch’\G *************************** 1. row *************************** Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on 1 row in set (0.01 sec) mysql> |
| STATEMENT | 8.0.19 | Newly Add | Important Change: MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the TABLE statement and the VALUES statement, each described in brief here: |
| STATEMENT | 8.0.19 | Newly Add | VALUES consists of the VALUES keyword followed by a series of row constructors (ROW()), separated by commas. It can be used to supply row values in an SQL-compliant fashion to an INSERT statement or REPLACE statement. For example, the following two statements are equivalent: |
| STATEMENT | 8.0.19 | Newly Add | Previously, it was not possible to use LIMIT in the recursive SELECT part of a recursive common table expression (CTE). LIMIT is now supported in such cases, along with an optional OFFSET clause. An example of such a recursive CTE is shown here: |
| FUNCTION | 8.0.19 | Deprecate | sys.format_bytes(), sys.format_time(), and sys.ps_thread_id() will be removed in a future MySQL version, so applications that use them should be adjusted to use the built-in functions instead, keeping in mind some minor differences between the sys functions and the built-in functions. |
| Shell | 8.0.19 | Newly Add | From MySQL 8.0.19, compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. |
| PARTITION | 8.0.19 | CHANGED | Historically, delimiter strings have been uppercase (#P# and #SP#) on case-sensitive file systems such as Linux, and lowercase (#p# and #sp#) on case-insensitive file systems such as Windows. To avoid issues when migrating data directories between case-sensitive and case-insensitive file systems, delimiter strings are now lowercase on all file systems. Uppercase delimiter strings are no longer used. Additionally, partition tablespace names and file names generated based on user-specified partition or subpartition names, which can be specified in uppercase or lowercase, are now generated (and stored internally) in lowercase regardless of the lower_case_table_names setting to ensure case-insensitivity. For example, if a table partition is created with the name PART_1, the tablespace name and file name are generated in lowercase: |
| STATEMENT | 8.0.20 | Deprecate | The use of VALUES() to access new row values in INSERT … ON DUPLICATE KEY UPDATE statements is now deprecated, and is subject to removal in a future MySQL release. Instead, you should use aliases for the new row and its columns as implemented in MySQL 8.0.19 and later. |
| HINT | 8.0.20 | Newly Add | This release implements several new index-level optimizer hints, which function much like existing index hints that employ SQL keywords such as FORCE INDEX and IGNORE INDEX. These are intended to replace the equivalent index hints, which will be deprecated in a future MySQL release (and eventually removed). The new hints are listed here, along with a brief description of each: |
| STATEMENT | 8.0.20 | Newly Add | Previously, the INTO clause for SELECT statements could appear at either of two positions: INTO now can appear in a third position, at the end of SELECT statements: |
| InnoDB | 8.0.20 | Newly Add | A TRX_SCHEDULE_WEIGHT column was added to the INFORMATION_SCHEMA.INNODB_TRX table, which permits querying transaction scheduling weights assigned by the CATS algorithm. The following INNODB_METRICS counters were added for monitoring code-level transaction scheduling events: lock_rec_release_attempts The number of attempts to release record locks. lock_rec_grant_attempts The number of attempts to grant record locks. lock_schedule_refreshes The number of times the wait-for graph was analyzed to update transaction schedule weights. |
| InnoDB | 8.0.20 | CHANGED | InnoDB: The storage area for the doublewrite buffer was moved from the system tablespace to doublewrite files. Moving the doublewrite buffer storage area out of the system tablespace reduces write latency, increases throughput, and provides flexibility with respect to placement of doublewrite buffer pages. The following system variables were introduced for advanced doublewrite buffer configuration: innodb_doublewrite_dir Defines the doublewrite buffer file directory. innodb_doublewrite_files Defines the number of doublewrite files. innodb_doublewrite_pages Defines the maximum number of doublewrite pages per thread for a batch write. innodb_doublewrite_batch_size Defines the number of doublewrite pages to write in a batch. |
| AUTH | 8.0.21 | Newly Add | You can now set per-user comments and attributes when creating or updating MySQL user accounts. A user comment consists of arbitrary text passed as the argument to a COMMENT clause used with a CREATE USER or ALTER USER statement. A user attribute consists of data in the form of a JSON object passed as the argument to an ATTRIBUTE clause used with either of these two statements. The attribute can contain any valid key-value pairs in JSON object notation. For example, the first of the following two statements creates a user account bill@localhost with the comment text This is Bill’s user account. The second statement adds a user attribute to this account, using the key email, with the value bill@example.com. |
| AUTH | 8.0.21 | Newly Add | There are new configuration parameters that apply specifically to the administrative interface. The ALTER INSTANCE RELOAD TLS statement is extended with a FOR CHANNEL clause that enables specifying the channel (interface) for which to reload the TLS context. The new Performance Schema tls_channel_status table exposes TLS context properties for the main and administrative interfaces. For backward compatibility, the administrative interface uses the same TLS context as the main interface unless some nondefault TLS parameter value is configured for the administrative interface. |
| PARTITION | 8.0.21 | Deprecate | When one or more columns using index prefixes are specified as part of the partitioning key, a warning is now generated for each such column. In addition, when a CREATE TABLE or ALTER TABLE statement is rejected because all columns specified in the proposed partitioning key employ index prefixes, the error message returned now makes clear the reason the statement did not succeed. |
| JSON | 8.0.21 | Newly Add | Added the JSON_VALUE() function, which simplifies creating indexes on JSON columns. A call to JSON_VALUE(json_doc, path RETURNING type) is equivalent to calling CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type), where json_doc is a JSON document, path is a JSON path expression pointing to a single value within the document, and type is a data type compatible with CAST(). RETURNING type is optional; if no return type is specified, JSON_VALUE() returns VARCHAR(512). JSON_VALUE() also supports ON EMPTY and ON ERROR clauses similar to those used with JSON_TABLE(). |
| OPTIMIZER | 8.0.21 | Newly Add | MySQL attempts to use an ordered index for any ORDER BY or GROUP BY query that has a LIMIT clause, overriding any other choices made by the optimizer, whenever it determines that this would result in faster execution. Because the algorithm for making this determination makes certain assumptions about data distribution and other conditions, it may not always be completely correct, and it is possible in some cases that choosing a different optimization for such queries can provide better performance. To handle such occurrences, it is now possible to disable this optimization by setting the optimizer_switch system variable’s prefer_ordering_index flag to off. |
| OPTIMIZER | 8.0.21 | Newly Add | Multi-Range Read Flags mrr (default on) Controls the Multi-Range Read strategy. mrr_cost_based (default on) Controls use of cost-based MRR if mrr=on. For more information, see Section 8.2.1.11, “Multi-Range Read Optimization”. Semijoin Flags duplicateweedout (default on) Controls the semijoin Duplicate Weedout strategy. firstmatch (default on) Controls the semijoin FirstMatch strategy. loosescan (default on) Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for GROUP BY). semijoin (default on) Controls all semijoin strategies. In MySQL 8.0.17 and later, this also applies to the antijoin optimization. The semijoin, firstmatch, loosescan, and duplicateweedout flags enable control over semijoin strategies. The semijoin flag controls whether semijoins are used. If it is set to on, the firstmatch and loosescan flags enable finer control over the permitted semijoin strategies. If the duplicateweedout semijoin strategy is disabled, it is not used unless all other applicable strategies are also disabled. If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default. For more information, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”. Skip Scan Flags skip_scan (default on) Controls use of Skip Scan access method. For more information, see Skip Scan Range Access Method. Subquery Materialization Flags materialization (default on) Controls materialization (including semijoin materialization). subquery_materialization_cost_based (default on) Use cost-based materialization choice. The materialization flag controls whether subquery materialization is used. If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default. The subquery_materialization_cost_based flag enables control over the choice between subquery materialization and IN-to-EXISTS subquery transformation. If the flag is on (the default), the optimizer performs a cost-based choice between subquery materialization and IN-to-EXISTS subquery transformation if either method could be used. If the flag is off, the optimizer chooses subquery materialization over IN-to-EXISTS subquery transformation. For more information, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”. Subquery Transformation Flags subquery_to_derived (default off) Beginning with MySQL 8.0.21, the optimizer is able in many cases to transform a scalar subquery in a SELECT, WHERE, JOIN, or HAVING clause into a left outer joins on a derived table. (Depending on the nullability of the derived table, this can sometimes be simplified further to an inner join.) This can be done for a subquery which meets the following conditions: The subquery does not make use of any nondeterministic functions, such as RAND(). The subquery is not an ANY or ALL subquery which can be rewritten to use MIN() or MAX(). The parent query does not set a user variable, since rewriting it may affect the order of execution, which could lead to unexpected results if the variable is accessed more than once in the same query. The subquery should not be correlated, that is, it should not reference a column from a table in the outer query, or contain an aggregate that is evaluated in the outer query. |
| OPTIMIZER | 8.0.21 | Newly Add | Prior to MySQL 8.0.22, the subquery could not contain a GROUP BY clause. This optimization can also be applied to a table subquery which is the argument to IN, NOT IN, EXISTS, or NOT EXISTS, that does not contain a GROUP BY. The default value for this flag is off, since, in most cases, enabling this optimization does not produce any noticeable improvement in performance (and in many cases can even make queries run more slowly), but you can enable the optimization by setting the subquery_to_derived flag to on. It is primarily intended for use in testing. |
| Replication | 8.0.21 | Newly Add | variable binlog_checksum, which defaults to the setting CRC32. Previously, Group Replication did not support the presence of checksums in the binary log, so binlog_checksum had to be set to NONE when configuring a server instance that would become a group member. This requirement is now removed, and the default can be used. The setting for binlog_checksum does not have to be the same for all members of a group. |
| REDO | 8.0.21 | Newly Add | InnoDB: Redo logging can now be enabled and disabled using ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG syntax. This functionality is intended for loading data into a new MySQL instance. Disabling redo logging helps speed up data loading by avoiding redo log writes. The new INNODB_REDO_LOG_ENABLE privilege permits enabling and disabling redo logging. The new Innodb_redo_log_enabled status variable permits monitoring redo logging status. |
| InnoDB | 8.0.21 | Newly Add | At startup, InnoDB validates the paths of known tablespace files against tablespace file paths stored in the data dictionary in case tablespace files have been moved to a different location. The new innodb_validate_tablespace_paths variable permits disabling tablespace path validation. This feature is intended for environments where tablespaces files are not moved. Disabling tablespace path validation improves startup time on systems with a large number of tablespace files. |
| TRUNCATE | 8.0.21 | Change | Truncating an InnoDB table that resides in a file-per-table tablespace drops the existing tablespace and creates a new one. As of MySQL 8.0.21, InnoDB creates the new tablespace in the default location and writes a warning to the error log if the tablespace was created with an earlier version and the current tablespace directory is unknown. To have TRUNCATE TABLE create the tablespace in its current location, add the directory to the innodb_directories setting before running TRUNCATE TABLE. |
| Docker | 8.0.21 | Newly Add | MySQL Server Docker containers now support server restart within a client session (which happens, for example, when the RESTART statement is executed by a client or during the configuration of an InnoDB Cluster instance). To enable this important feature, containers should be started with the docker run option –restart set to the value on-failure. See Starting a MySQL Server Instance for details. (Bug #30750730) |
| EXPLAIN | 8.0.21 | Change | EXPLAIN ANALYZE now supports the FORMAT option. Currently, TREE is the only supported format. (Bug #30315224) |
| Replication | 8.0.21 | Change | On storage engines that support atomic DDL, the CREATE TABLE … SELECT statement is now logged as one transaction in the binary log when row-based replication is in use. Previously, it was logged as two transactions, one to create the table, and the other to insert data. With this change, CREATE TABLE … SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication. For more information, see Atomic Data Definition Statement Support. (Bug #11756034, Bug #47899) |
| Replication | 8.0.22 | Change | Deprecation and Removal Notes From MySQL 8.0.22, the group_replication_ip_whitelist system variable is deprecated, and the system variable group_replication_ip_allowlist has been added to replace it. The system variable works in the same way as before, only the terminology has changed. |
| Replication | 8.0.22 | Change | From MySQL 8.0.22, the statements START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET SLAVE are deprecated. The following aliases should be used instead: |
| Memcached | 8.0.22 | Deprecate | The InnoDB memcached plugin is deprecated and support for it will be removed in a future MySQL version. |
| INFORMATION_SCHEMA | 8.0.22 | Deprecate | The INFORMATION_SCHEMA.TABLESPACES table is unused. It is now deprecated and will be removed in a future MySQL version. Other INFORMATION_SCHEMA tables may provide related information, as described in The INFORMATION_SCHEMA TABLESPACES Table. |
| TEMPORARY TABLE | 8.0.22 | Change | The filesort algorithm now supports sorting a join on multiple tables, and not just a single table. (Bug #31310238, Bug #31559978, Bug #31563876) |
| DUMP | 8.0.22 | Add | Added support for periodic synchronization when writing to files with SELECT INTO DUMPFILE and SELECT INTO OUTFILE statements. This feature can be enabled by setting the select_into_disk_sync system variable to ON; the size of the write buffer cn be set using the server system variable select_into_buffer_size; the default buffer size is 131072 (217) bytes. An optional delay following synchronization to disk can also be set using the select_into_disk_sync_delay system variable; the default behaviour is not to allow any delay (that is, a delay time of 0 milliseconds). |
| OPTIMIZER | 8.0.22 | Add | To enable derived condition pushdown, the optimizer_switch system variable’s derived_condition_pushdown flag (added in this release) must be set to on. This is the default setting. If this optimization is disabled by the optimizer switch setting, you can enable it for a specific query using the DERIVED_CONDITION_PUSHDOWN optimizer hint (also added in this release). Use the NO_DERIVED_CONDITION_PUSHDOWN optimizer hint to disable the optimization for a given query. |
| Performance Schema | 8.0.22 | Add | An alternative SHOW PROCESSLIST implementation is now available based on the new Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex To enable the alternative implementation, enable the performance_schema_show_processlist system variable. The alternative implementation of SHOW PROCESSLIST also applies to the mysqladmin processlist command. The alternative implementation does not apply to the INFORMATION_SCHEMA PROCESSLIST table or the COM_PROCESS_INFO command of the MySQL client/server protocol. To ensure that the default and alternative implementations yield the same information, certain configuration requirements must be met; see The processlist Table. |
| Logging | 8.0.22 | Add | An SQL interface to the most recent events written to the MySQL server error log is now available by means of queries on the new Performance Schema error_log table. This table has a fixed size, with old events automatically discarded as necessary to make room for new ones. The table is populated if error log configuration includes a log sink component that supports this capability (currently the traditional-format log_sink_internal and JSON-format log_sink_json sinks). Several new status variables provide information about error_log table operation. See The error_log Table. |
| CAST | 8.0.22 | CHANGE | It is now possible to cast values of other types to YEAR, using either the CAST() function or the CONVERT() function. These functions now support YEAR values of one or two digits in the range 0-99, and four-digit values in the range 1901-2155. Integer 0 is converted to Year 0; a string consisting of one or more zeroes (following possible truncation) is converted to the year 2000. Casting adds 2000 to values in the range 1-69 inclusive, and 1900 to values in the range 70-99 inclusive. Strings beginning with one, two, or four digits followed by at least one non-digit character (and possibly other digit or non-digit characters) are truncated prior to conversion to YEAR; in such cases, the server emits a truncation warning. Floating-point values are rounded prior to conversion; CAST(1944.5 AS YEAR) returns 1945 due to rounding, and CAST(“1944.5” AS YEAR) returns 1944 (with a warning) due to truncation. DATE, DATETIME, and TIMESTAMP are cast to the YEAR portion of the value. A TIME value is cast to the current year. Not specifying the value to be cast as a TIME value may yield a different result from what is expected; CAST(“13:47” AS YEAR) returns 2013 due to truncation of the string value, and CAST(TIME “13:47” AS YEAR) returns 2020 as of the year of this release. |
| CAST | 8.0.22 | CHANGE | When selecting a TIMESTAMP column value, it is now possible to convert it from the system time zone to a UTC DATETIME when retrieving it, using the AT TIME ZONE operator which is implemented for the CAST() function in this release. |
| InnoDB | 8.0.22 | Newly Add | The new innodb_extend_and_initialize variable permits configuring how InnoDB allocates space to file-per-table and general tablespaces on Linux. By default, when an operation requires additional space in a tablespace, InnoDB allocates pages to the tablespace and physically writes NULLs to those pages. This behavior affects performance if new pages are allocated frequently. As of MySQL 8.0.22, you can disable innodb_extend_and_initialize on Linux systems to avoid physically writing NULLs to newly allocated tablespace pages. When innodb_extend_and_initialize is disabled, space is allocated using posix_fallocate() calls, which reserve space without physically writing NULLs. |
| Auth | 8.0.23 | Newly Add | Granting the RELOAD privilege enables a user to perform a wide variety of operations. In some cases, it may be desirable for a user to be able to perform only some of these operations. To enable DBAs to avoid granting RELOAD and tailor user privileges more closely to the operations permitted, these new privileges of more limited scope are available: FLUSH_OPTIMIZER_COSTS: Enables use of the FLUSH OPTIMIZER_COSTS statement. FLUSH_STATUS: Enables use of the FLUSH STATUS statement. FLUSH_TABLES: Enables use of the FLUSH TABLES statement. FLUSH_USER_RESOURCES: Enables use of the FLUSH USER_RESOURCES statement. |
| Replication | 8.0.23 | Change | From MySQL 8.0.23, the statement CHANGE MASTER TO is deprecated. The alias CHANGE REPLICATION SOURCE TO should be used instead. The parameters for the statement also have aliases that replace the term MASTER with the term SOURCE. For example, MASTER_HOST and MASTER_PORT can now be entered as SOURCE_HOST and SOURCE_PORT. The START REPLICA | SLAVE statement’s parameters MASTER_LOG_POS and MASTER_LOG_FILE now have aliases SOURCE_LOG_POS and SOURCE_LOG_FILE. The statements work in the same way as before, only the terminology used for each statement has changed. A deprecation warning is issued if the old versions are used. A new status variable, Com_change_replication_source, has been added as an alias for the Com_change_master status variable. Both the old and new version of the statement update both the old and new version of the status variable. |
| Replication | 8.0.23 | Deprecate | The use of the system variables master_info_repository and relay_log_info_repository is now deprecated, and a warning message is issued if you attempt to set them or read their values. The system variables will be removed in a future MySQL version. These system variables were used to specify whether the replica’s connection metadata repository and applier metadata repository were written to an InnoDB table in the mysql system database, or to a file in the data directory. The FILE setting was already deprecated in a previous release, and tables are the default for the replication metadata repositories in MySQL 8.0. |
| Auth | 8.0.23 | Deprecate | Flushing the host cache can be done using any of these methods: Execute a TRUNCATE TABLE statement that truncates the Performance Schema host_cache table. This requires the DROP privilege for the table. Execute a FLUSH HOSTS statement. This requires the RELOAD privilege. Execute a mysqladmin flush-hosts command. This requires the RELOAD privilege. Although those methods are equivalent in effect, granting the RELOAD privilege enables a number of other operations in addition to host cache flushing, which is undesirable from a security standpoint. Granting the DROP privilege for the host_cache table is preferable because it has a more limited scope. Therefore, the FLUSH HOSTS statement is deprecated and will be removed in a future MySQL version. Instead, truncate the host_cache table. |
| Performance | 8.0.23 | Change | Functionality Added or Changed InnoDB: Performance was improved for the following operations: Dropping a large tablespace on a MySQL instance with a large buffer pool (>32GBs). Dropping a tablespace with a significant number of pages referenced from the adaptive hash index. Truncating temporary tablespaces. The pages of dropped or truncated tablespaces and associated AHI entries are now removed from the buffer pool passively as pages are encountered during normal operations. Previously, dropping or truncating tablespaces initiated a full list scan to remove pages from the buffer pool immediately, which negatively impacted performance. (Bug #31008942, Bug #98869) |
| InnoDB | 8.0.23 | Change | InnoDB: The new AUTOEXTEND_SIZE option defines the amount by which InnoDB extends the size of a tablespace when it becomes full, making it possible to extend tablespace size in larger increments. Allocating space in larger increments helps to avoid fragmentation and facilitates ingestion of large amounts of data. The AUTOEXTEND_SIZE option is supported with the CREATE TABLE, ALTER TABLE, CREATE TABLESPACE, and ALTER TABLESPACE statements. For more information, see Tablespace AUTOEXTEND_SIZE Configuration. |
| Replication | 8.0.23 | Newly Add | For a multithreaded replica (where slave_parallel_workers is greater than 0), setting slave_preserve_commit_order=1 ensures that transactions are executed and committed on the replica in the same order as they appear in the replica’s relay log. Each executing worker thread waits until all previous transactions are committed before committing. If a worker thread fails to execute a transaction because a possible deadlock was detected, or because the transaction’s execution time exceeded a relevant wait timeout, it automatically retries the number of times specified by slave_transaction_retries before stopping with an error. Transactions with a non-temporary error are not retried. The replication applier on a multithreaded replica has always handled data access deadlocks that were identified by the storage engines involved. However, some other types of lock were not detected by the replication applier, such as locks involving access control lists (ACLs) or metadata locking (for example, FLUSH TABLES WITH READ LOCK statements). This could lead to three-actor deadlocks with the commit order locking, which could not be resolved by the replication applier, and caused replication to hang indefinitely. From MySQL 8.0.23, deadlock handling on multithreaded replicas that preserve the commit order has been enhanced to mitigate these types of deadlocks. The deadlocks are not specifically resolved by the replication applier, but the applier is aware of them and initiates automatic retries for the transaction, rather than hanging. If the retries are exhausted, replication stops in a controlled manner so that the deadlock can be resolved manually. (Bug #107574, Bug #34291887) |
| Performance | 8.0.23 | Change | InnoDB: Performance was improved for the following operations: Dropping a large tablespace on a MySQL instance with a large buffer pool (>32GBs). Dropping a tablespace with a significant number of pages referenced from the adaptive hash index. Truncating temporary tablespaces. The pages of dropped or truncated tablespaces and associated AHI entries are now removed from the buffer pool passively as pages are encountered during normal operations. Previously, dropping or truncating tablespaces initiated a full list scan to remove pages from the buffer pool immediately, which negatively impacted performance. (Bug #31008942, Bug #98869) |
| OPTIMIZER | 8.0.24 | Newly Add | The MySQL query optimizer can now apply the derived table optimization to correlated scalar subqueries, whenever the subquery_to_derived flag of the optimizer_switch variable is enabled. This is done by applying an extra grouping and then an outer join on the lifted predicate. For example, a query such as SELECT * FROM t1 WHERE (SELECT a FROM t2 WHERE t2.a=t1.a) > 0 can be rewritten as SELECT t1.* FROM t1 LEFT OUTER JOIN (SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived ON t1.a = derived.a WHERE derived.a > 0. |
| InnoDB | 8.0.24 | CHANE | InnoDB: The AUTOEXTEND_SIZE maximum setting was increased from 64M to 4GB. The AUTOEXTEND_SIZE option, introduced in MySQL 8.0.23, defines the amount by which InnoDB extends the size of a tablespace when it becomes full. The option is supported with the CREATE TABLE, ALTER TABLE, CREATE TABLESPACE, and ALTER TABLESPACE statements. For more information, see Tablespace AUTOEXTEND_SIZE Configuration. (Bug #32438606) |
| TLS | 8.0.26 | Deprecate | The TLSv1 and TLSv1.1 connection protocols now are deprecated and support for them is subject to removal in a future MySQL version. (For background, refer to the IETF memo Deprecating TLSv1.0 and TLSv1.1.) It is recommended that connections be made using the more-secure TLSv1.2 and TLSv1.3 protocols. TLSv1.3 requires that both the MySQL server and the client application be compiled with OpenSSL 1.1.1 or higher. |
| EVENT | 8.0.26 | CHANGE | If the Event Scheduler is enabled, enabling the super_read_only system variable prevents it from updating event “last executed” timestamps in the events data dictionary table. This causes the Event Scheduler to stop the next time it tries to execute a scheduled event, after writing a message to the server error log. |
| VARIABLES | 8.0.26 | Newly Add | InnoDB: The new innodb_segment_reserve_factor system variable permits configuring the percentage of tablespace file segment pages that are reserved as empty pages. For more information, see Configuring the Percentage of Reserved File Segment Pages.Thanks to Facebook for the contribution. (Bug #32312743, Bug #102044) Configuring the Percentage of Reserved File Segment Pages The innodb_segment_reserve_factor variable, introduced in MySQL 8.0.26, is an advanced feature that permits defining the percentage of tablespace file segment pages reserved as empty pages. A percentage of pages are reserved for future growth so that pages in the B-tree can be allocated contiguously. The ability to modify the percentage of reserved pages permits fine-tuning InnoDB to address issues of data fragmentation or inefficient use of storage space. The setting is applicable to file-per-table and general tablespaces. The innodb_segment_reserve_factor default setting is 12.5 percent, which is the same percentage of pages reserved in previous MySQL releases. The innodb_segment_reserve_factor variable is dynamic and can be configured using a SET statement. For example: mysql> SET GLOBAL innodb_segment_reserve_factor=10; |
| VARIABLES | 8.0.26 | Newly Add | On platforms that support fdatasync() system calls, the new innodb_use_fdatasync variable permits using fdatasync() instead of fsync() for operating system flushes. An fdatasync() system call does not flush changes to file metadata unless required for subsequent data retrieval, providing a potential performance benefit. The innodb_use_fdatasync variable can be set dynamically using a SET statement. |
| VARIABLES | 8.0.27 | Deprecate | Important Change: The default_authentication_plugin variable is deprecated as of MySQL 8.0.27; expect support for it to be removed in a future version of MySQL. The default_authentication_plugin variable is still used in MySQL 8.0.27, but in conjunction with and at a lower precedence than the new authentication_policy system variable, which is introduced in MySQL 8.0.27 with the multifactor authentication feature. For details, see The Default Authentication Plugin. (Bug #27515356) |
| BLACKHOLE | 8.0.27 | Change | The BLACKHOLE storage engine maximum key length has been increased from 1000 to 3072 bytes (the same as InnoDB). Thanks to Adam Cable for the contribution. (Bug #32788749, Bug #103371) |
| EXPLAIN | 8.0.27 | Change | EXPLAIN FORMAT=TREE now shows more precise information than displayed previously about scans generated by the range optimizer. In particular, sub-iterators are now displayed explicitly, and are properly timed with EXPLAIN ANALYZE; index range scans now show the actual ranges being scanned. Descriptions in the output are also more user-friendly than before; for example, index_for_group_by shown for a query using DISTINCT is replaced by index skip scan for deduplication. |
| Performance_Schema | 8.0.27 | Newly Add | To assist monitoring and troubleshooting, the Performance Schema instrumentation is now used to export names of instrumented threads to the operating system. This enables utilities that display thread names, such as debuggers and the Unix ps command, to display distinct mysqld thread names rather than “mysqld”. This feature is supported only on Linux, macOS, and Windows. For more information, see The setup_instruments Table. |
| Replication | 8.0.27 | Change | Replication: Multithreading is now enabled by default for replica servers. A multithreaded applier has a number of applier threads that execute transactions in parallel. This behavior can avoid many cases of unwanted replication lag that can cause temporary divergence between the source and replicas. The following default server settings are used to produce the multithreading behavior: replica_parallel_workers=4. This setting enables multithreading and creates four applier threads on the replica, plus a coordinator thread to manage them. If you are using multiple replication channels, each channel has this number of threads. Four applier threads provide a base level of parallelism, and you can change the setting to specify up to 1024 applier threads. replica_preserve_commit_order=1. This setting ensures that transactions are externalized on the replica in the same order as they appear in the replica’s relay log, so the replica never enters a state that the master was not in, and there are no gaps in the sequence of transactions that have been executed from the relay log. replica_parallel_type=LOGICAL_CLOCK. This setting specifies that transactions that are part of the same binary log group commit on a replication source server are applied in parallel on a replica. It is required when replica_preserve_commit_order=1 is set. |
| Docker | 8.0.27 | Change | A default time zone can now be set for a server by using the server option –default-time-zone while starting a MySQL Server Docker container. Before, the container failed to start if the option was used. |
| VARIABLES | 8.0.27 | Newly Add | For online DDL operations, storage is usually the bottleneck. To address this issue, CPU utilization and index building has been improved. Indexes can now be built simultaneously instead of serially. Memory management has also been tightened to respect memory The number of parallel threads that can be used to scan clustered index is defined by the innodb_parallel_read_threads variable. The default setting is 4. The maximum setting is 256, which is the maximum number for all sessions. The actual number of threads that scan the clustered index is the number defined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller. If the thread limit is reached, sessions fall back to using a single thread. The number of parallel threads that sort and load data is controlled by the innodb_ddl_threads variable, introduced in MySQL 8.0.27. The default setting is 4. Prior to MySQL 8.0.27, sort and load operations are single-threaded. The following limitations apply: Parallel threads are not supported for building indexes that include virtual columns. Parallel threads are not supported for full-text index creation. Parallel threads are not supported for spatial index creation. Parallel scan is not supported on tables defined with virtual columns. Parallel scan is not supported on tables defined with a full-text index. Parallel scan is not supported on tables defined with a spatial index. |
| character | 8.0.28 | Deprecate | CONVERT(string USING charset) did not compute the correct maximum length for its return value, which should be the same as that calculated for CAST(string AS charset). This meant that some conversions of strings from BINARY to nonbinary character sets which should have been rejected as invalid returned values instead. Prior to upgrading, applications that may rely on the previous CONVERT() behavior should be checked and updated as necessary. In particular, for indexes on generated columns using CONVERT() with invalid values, you should correct such values, drop the index, then re-create it before upgrading to this release. In some cases, it may be simpler to rebuild the table using ALTER TABLE table FORCE, rather than dropping and re-creating the index. See SQL Changes, for more information. (Bug #33199145) |
| Buffer Pool | 8.0.28 | Change | Sorts of some column types, including JSON and TEXT, sometimes exhausted the sort buffer if its size was not at least 15 times that of the largest row in the sort. Now the sort buffer need only be only 15 times as large as the largest sort key. (Bug #103325, Bug #105532, Bug #32738705, Bug #33501541) |
| SSL | 8.0.28 | Change | Support for the TLSv1 and TLSv1.1 connection protocols is removed as of MySQL 8.0.28. The protocols were deprecated from MySQL 8.0.26. For background, refer to the IETF memo Deprecating TLSv1.0 and TLSv1.1. Make connections using the more-secure TLSv1.2 and TLSv1.3 protocols. TLSv1.3 requires that both the MySQL Server software and the client application were compiled with OpenSSL 1.1.1 or higher. |
| DDL | 8.0.28 | Change | InnoDB: InnoDB now supports ALTER TABLE … RENAME COLUMN operations using ALGORITHM=INSTANT. Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. Table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by DDL operations that support it. |
| TEMPORARY TABLE | 8.0.28 | Change | The tmp_table_size variable now defines the maximum size of individual in-memory internal temporary tables created by the TempTable storage engine. An appropriate size limit prevents individual queries from consuming an inordinate amount global TempTable resources. See Internal Temporary Table Storage Engine. |
| VARIABLES | 8.0.28 | Change | The innodb_open_files variable, which defines the number of files InnoDB can have open at one time, can now be set at runtime using a SELECT innodb_set_open_files_limit(N) statement. The statement executes a stored procedure that sets the new limit. To prevent non-LRU manged files from consuming the entire innodb_open_files limit, non-LRU managed files are now limited to 90 percent of the innodb_open_files limit, which reserves 10 percent of the innodb_open_files limit for LRU managed files. The innodb_open_files limit now includes temporary tablespace files, which were not counted toward the limit previously. |
| TIME | 8.0.28 | Change | The functions FROM_UNIXTIME(), UNIX_TIMESTAMP(), and CONVERT_TZ() now handle 64-bit values on platforms that support them, including 64-bit versions of Linux, MacOS, and Windows. On compatible platforms, FROM_UNIXTIME() now accepts a maximum argument of 32536771199.999999 seconds, corresponding to ‘3001-01-18 23:59:59.999999’ UTC (including the optional fraction of up to 6 digits). If the argument is larger than this, the function returns NULL. |
| Monitoring | 8.0.28 | Newly Add | This release introduces monitoring and limiting of memory allocation on a global and per-user basis. You can now observe the total memory consumed by all user connections by checking the value of the Global_connection_memory status variable, which must be enabled by setting global_connection_memory_tracking = 1. The total includes memory used by system processes, or by the MySQL root user, although these users are not subject to disconnection due to memory usage. |
| character | 8.0.29 | Change | Important Note: The server now uses utf8mb3 rather than utf8 in the following cases: In the output of SHOW SQL statements (SHOW CREATE TABLE, SHOW CREATE VIEW, SHOW CREATE DATABASE) When reporting invalid strings. (Bug #33385252, Bug #33395007) The server now uses utf8mb3 in place of the alias utf8 for character set names when populating data dictionary tables from built-in character sets. This affects the display of character set and related information in the MySQL Information Schema tables listed here: CHARACTER_SETS COLLATIONS COLUMNS COLLATION_CHARACTER_SET_APPLICABILITY PARAMETERS ROUTINES SCHEMATA This change also affects the output of the SQL SHOW CHARACTER SET, SHOW COLLATION, SHOW CREATE DATABASE, and SHOW CREATE TABLE statements. (Bug #30624990) |
| TIMESTAMP | 8.0.29 | Deprecate | Important Change: Previously, MySQL allowed arbitrary delimiters and an arbitrary number of them in TIME, DATE, DATETIME, and TIMESTAMP literals, as well as an arbitrary number of whitespaces before, after, and between the date and time values in DATETIME and TIMESTAMP literals. This behavior is now deprecated, and you should expect it to be removed in a future version of MySQL. |
| REPLICATION | 8.0.29 | CHANGE | From MySQL 8.0.26, use replica_parallel_type in place of slave_parallel_type, which is deprecated from that release. In releases before MySQL 8.0.26, use slave_parallel_type. For multithreaded replicas (replicas on which replica_parallel_workers or slave_parallel_workers is set to a value greater than 0), replica_parallel_type specifies the policy used to decide which transactions are allowed to execute in parallel on the replica. replica_parallel_type is deprecated beginning with MySQL 8.0.29, as is support for parallelization of transactions using database partitioning. Expect support for these to be removed in a future release, and for LOGICAL_CLOCK to be used exclusively thereafter. Beginning with MySQL 8.0.30, setting this variable to 0 is deprecated, and doing so raises a warning; 0 as a permitted value for replica_parallel_workers is subject to removal in a future MySQL release; set it to 1 instead, which has the same effect. |
| STORAGE ENGINE | 8.0.29 | Deprecate | The myisam_repair_threads system variable and myisamchk –parallel-recover option are deprecated; expect support for both to be removed in a future release of MySQL. |
| Buffer Pool | 8.0.29 | Deprecate | The server system variables query_prealloc_size and transaction_prealloc_size are now deprecated, and setting either or both of these no longer has any effect in the MySQL server. Expect them to be removed in a future MySQL release. |
| NULL | 8.0.29 | BugFix | Aggregate functions based on expressions comparing values with a NULL were not ignoring the NULL correctly. (Bug #33624777, Bug #105762) https://bugs.mysql.com/bug.php?id=105762 |
| DDL | 8.0.29 | CHANGE | An IF NOT EXISTS option is now supported for the statements CREATE FUNCTION, CREATE PROCEDURE, and CREATE TRIGGER. |
| XA | 8.0.29 | CHANGE | Group replication in some scenarios faced problems because it was not possible to commit an XA transaction prepared on another connection. To address such issues, MySQL now supports detached XA transactions; once prepared, an XA transaction is no longer connected to the current session. This happens as part of executing XA PREPARE. The prepared XA transaction can be committed or rolled back by another connection, and the current session can then initiate another XA or local transaction without waiting for the prepared XA transaction to complete. |
| DDL | 8.0.29 | Newly Add | InnoDB: InnoDB now supports ALTER TABLE … DROP COLUMN operations using ALGORITHM=INSTANT. Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. Table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by DDL operations that support it. Prior to MySQL 8.0.29, an instantly added column could only be added as the last column of the table. From MySQL 8.0.29, an instantly added column can be added to any position in the table. |
| DDL | 8.0.29 | Change | INSTANT is the default algorithm as of MySQL 8.0.29, and INPLACE before that. |
| DDL | 8.0.29 | Change | Prior to MySQL 8.0.29, an instantly added column could only be added as the last column of the table. From MySQL 8.0.29, an instantly added column can be added to any position in the table. |
| DDL | 8.0.29 | Newly Add | Instantly added or dropped columns create a new row version. Up to 64 row versions are permitted. A new TOTAL_ROW_VERSIONS column was added to the INFORMATION_SCHEMA.INNODB_TABLES table to track the number of row versions. |
| BINLOG | 8.0.29 | Change | Automatic purging of binary log files by the server is now controlled using the binlog_expire_logs_auto_purge system variable introduced in this release. By default, automatic purging is enabled (binlog_expire_logs_auto_purge set to ON); to disable it, set the value of this variable to OFF. |
| BINLOG | 8.0.29 | Change | The interval to wait before purging is controlled by binlog_expire_logs_seconds and expire_logs_days. Setting both of these system variables to 0 stops automatic purging from taking place, even when binlog_expire_logs_auto_purge is set to ON. |
| character | 8.0.30 | Change | Important Change: A previous change renamed character sets having deprecated names prefixed with utf8_ to use utf8mb3_ instead. In this release, we rename the utf8_ collations as well, using the utf8mb3_ prefix; this is to make the collation names consistent with those of the character sets, not to rely any longer on the deprecated collation names, and to clarify the distinction between utf8mb3 and utf8mb4. |
| REPLICATION | 8.0.30 | Deprecate | Setting the replica_parallel_workers system variable (or the equivalent server option –replica-parallel-workers) to 0 is now deprecated, and doing so now raises a warning. To achieve the same result (that is, use single threading) without the warning, set replica_parallel_workers=1 instead. |
| Cache | 8.0.30 | Deprecate | The –skip-host-cache server option is now deprecated, and subject to removal in a future release. Use of –skip-host-cache is similar to setting the host_cache_size system variable to 0, but host_cache_size is more flexible because it can also be used to resize, enable, or disable the host cache at runtime, not just at server startup. |
| PK | 8.0.30 | Newly Add | Generated Invisible Primary Keys (GIPKs) MySQL 8.0.30 now supports GIPK mode, which causes a generated invisible primary key (GIPK) to be added to any InnoDB table that is created without an explicit primary key. This enhancement applies to InnoDB tables only. The definition of the generated key column added to an InnoDB table by GIPK mode is is shown here: my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY The name of the generated primary key is always my_row_id; you cannot, while GIPK mode is in effect, use this as a column name in a CREATE TABLE statement that creates a new InnoDB table unless it includes an explicit primary key. You cannot alter a generated invisible primary key while GIPKs are in effect, with one exception: You can toggle the visibility of the GIPK using ALTER TABLE tbl CHANGE COLUMN my_row_id SET VISIBLE and ALTER TABLE tbl CHANGE COLUMN my_row_id SET INVISIBLE. You can exclude generated invisible primary keys from the output of mysqldump using the –skip-generated-invisible-primary-key option added in this release. mysqlpump also now supports a –skip-generated-invisible-primary-key option which excludes GIPKs from its output. |
| SSL | 8.0.30 | Newly Add | It is now possible to compile the MySQL server package (mysqld + libmysql + client tools) using OpenSSL 3.0 on supported platforms, which should not change the behavior of the server or client programs. For additional information, see https://wiki.openssl.org/index.php/OpenSSL_3.0. |
| double write buffer | 8.0.30 | Newly Add | InnoDB: The innodb_doublewrite system variable, which enables or disables the doublewrite buffer, has two new settings, DETECT_ONLY and DETECT_AND_RECOVER. With the DETECT_ONLY setting, database page content is not written to the doublewrite buffer, and recovery does not use the doublewrite buffer to fix incomplete page writes. This lightweight setting is intended for detecting incomplete page writes only. The DETECT_AND_RECOVER setting is equivalent to the existing ON setting. For more information, see Doublewrite Buffer. |
| double write buffer | 8.0.30 | Change | MySQL 8.0.30 onwards supports dynamic changes to the innodb_doublewrite setting that enables the doublewrite buffer, between ON, DETECT_AND_RECOVER, and DETECT_ONLY. MySQL does not support dynamic changes between a setting that enables the doublewrite buffer and OFF or vice versa. If the doublewrite buffer is located on a Fusion-io device that supports atomic writes, the doublewrite buffer is automatically disabled and data file writes are performed using Fusion-io atomic writes instead. However, be aware that the innodb_doublewrite setting is global. When the doublewrite buffer is disabled, it is disabled for all data files including those that do not reside on Fusion-io hardware. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, an innodb_flush_method setting of O_DIRECT is recommended. |
| REDO LOG | 8.0.30 | Change and Deplicated | InnoDB: InnoDB now supports dynamic configuration of redo log capacity. The innodb_redo_log_capacity system variable can be set at runtime to increase or decrease the total amount of disk space occupied by redo log files. With this change, the number of redo log files and their default location has also changed. From MySQL 8.0.30, InnoDB maintains 32 redo log files in the #innodb_redo directory in the data directory. Previously, InnoDB created two redo log files in the data directory by default, and the number and size of redo log files were controlled by the innodb_log_files_in_group and innodb_log_file_size variables. These two variables are now deprecated. |
| Upgrade | 8.0.30 | BugFix | The changed tables are mysql.db, mysql.tables_priv, mysql.columns_priv and mysql.procs_priv. When you upgrade to MySQL 8.0.30 or later, these tables are modified in the second step of the MySQL upgrade process. Use the –upgrade=FORCE option when performing logical upgrades using a backup or export utility such as mysqldump or mysqlpump, which ensures that the table structures are checked and rebuilt with the new column order. (Bug #33644645, Bug #33637244) |
| myisam_repair_threads | 8.0.30 | Deprecate | The myisam_repair_threads system variable and myisamchk –parallel-recover option were removed. (Bug #31052408) |
| Keyword | 8.0.31 | Change | Important Change: Previously, MySQL supported the use of “full” as the name of a table, column, view, stored procedure, or stored function, as well as for the alias of a table, view, or column. Beginning with this release, using “full” (regardless of letter case) in this fashion as an unquoted identifier is now deprecated, and raises a warning. This is to align more closely with the SQL standard, in which FULL is reserved as a keyword. |
| OPTIMIZER | 8.0.31 | Newly Add | It is now possible to set a column histogram to a user-specified JSON value. This can be useful when sampling leaves out important values. This also means that a secondary (replica) MySQL server can assume the work of sampling the data and building the histogram, which can then be used on the primary (source) without impacting its performance. |
| VARIABLES | 8.0.31 | Newly Add | InnoDB: Two new status variables are provided for monitoring online buffer pool resizing operations. The Innodb_buffer_pool_resize_status_code status variable reports a status code indicating the stage of an online buffer pool resizing operation. The Innodb_buffer_pool_resize_status_progress status variable reports a percentage value indicating the progress of each stage. |
| THREADS | 8.0.31 | Change | InnoDB: InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded. Previously, this action was performed by a single thread. |
| Replication | 8.0.31 | Change | Replication: When replication filtering is in use, a replica no longer raises replication errors related to privilege checks or require_row_format validation for events which are filtered out. Previously, all privileges were checked in the applier, with some being checked before applying filters and others not until after; with this release, privilege checks are now deferred until after all replication filters have been applied. In addition, prior to this release, checks for require_row_format equal to 1 took place on both the receiver and the applier; now the applier alone performs this check, before any filters are evaluated. In addition, prior to this release, checks for require_row_format equal to 1 took place on both the receiver and the applier; now the applier alone performs this check, before any filters are evaluated. |