MySQL 8.0.23 Major Updates (New Features)

Author: Guan Changlong is a DBA in the Delivery Services Department of Aikesheng. He is mainly responsible for the daily problem handling of MySQL and Redis, and participates in the teaching and research of the company's database training and the operation of the open source community.
Source: Original Contribution
* Produced by the Aikesheng Open Source Community, original content may not be used without authorization. For reprinting, please contact the editor and indicate the source.

The previous article introduced a super detailed tutorial on how to install MySQL 8.0.23. If you are interested, click to view it.

MySQL 8.0.23 was released today. The release frequency is now stable at once every three months. This release is a maintenance version. In addition to fixing some bugs, this version also adds some new features.

1. Invisible columns

Columns can be defined as invisible, for example:

# When creating a table, you can make it invisible (ALTER TABLE also supports this)
mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> INSERT INTO t1 (col1, col2) VALUES (1, 2), (3, 4);

# SQL statement uses the invisible column by explicitly referencing it mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
| 1 |
| 3 |
+------+

# If an invisible column is not referenced, it will not appear in the result mysql> SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+

https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

2. Query attributes

Allows an application to set per-query metadata for its queries.

mysql> query_attributes n1 v1 n2 v2;
mysql> SELECT
     mysql_query_attribute_string('n1') AS 'attr 1',
     mysql_query_attribute_string('n2') AS 'attr 2',
     mysql_query_attribute_string('n3') AS 'attr 3';
+--------+--------+--------+
| attr 1 | attr 2 | attr 3 |
+--------+--------+--------+
| v1 | v2 | NULL |
+--------+--------+--------+

https://dev.mysql.com/doc/refman/8.0/en/query-attribute-udfs.html#udf_mysql-query-attribute-string

3. Safety

Doublewrite file page encryption

InnoDB automatically encrypts Doublewrite file pages that belong to encrypted tablespaces; no action is required. Doublewrite file pages are encrypted using the encryption key of the associated tablespace. The encrypted pages where data is written in the same tablespace are also written to the Doublewrite file. Doublewrite file pages belonging to unencrypted tablespaces remain unencrypted. During recovery, encrypted Doublewrite file pages are unencrypted and checked for corruption.

https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html

Improve account certainty

To make TCP connection matching accounts more deterministic, the host name portion of the matching account is checked in the following order before matching the account specified by the host name: Using the host IP address to specify the account.

# Specify the account with the IP addressmysql> CREATE USER 'user_name'@'127.0.0.1';
mysql> CREATE USER 'user_name'@'198.51.100.44';

# Account specified as an IP address using CIDR notationmysql> CREATE USER 'user_name'@'192.0.2.21/8';
mysql> CREATE USER 'user_name'@'198.51.100.44/16';

# Use an account specified as an IP address in subnet mask format mysql> CREATE USER 'user_name'@'192.0.2.0/255.255.255.0';
mysql> CREATE USER 'user_name'@'198.51.0.0/255.255.0.0';

https://dev.mysql.com/doc/refman/8.0/en/connection-access.html

More precise FLUSH permissions

A user granted the RELOAD privilege can perform various operations. To enable DBAs to avoid granting RELOAD in some cases and to keep user privileges closer to permitted operations, finer-grained privilege control over FLUSH operations has been introduced so that customers can execute FLUSH OPTIMIZER_COSTS, FLUSH STATUS, FLUSH USER_RESOURCES, and FLUSH TABLES statements without the RELOAD privilege.

https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_reload

4. InnoDB

Optimizing TRUNCATE / DROP

When a user performs a TRUNCATE or DROP operation on an InnoDB tablespace:

• Deleting large tables on instances with large buffer pools (>32GB)
• For tablespaces with adaptive hash indexes that reference a large number of pages
• TRUNCATE temporary tablespace

In the above case, MySQL now marks it as deleted and then lazily releases all pages belonging to the deleted tablespace from the buffer pool or reuses them as it frees the pages.

Added tablespace AUTOEXTEND_SIZE attribute

The InnoDB regular table CREATE / ALTER TABLESPACE clause and the independent tablespace CREATE / ALTER TABLE clause have a new auto-extend attribute. The original tablespace growth size is hard-coded in InnoDB internally to 1MB [default] (page_size * number of pages in a range). Once set, the tablespace can grow to a size that is determined by the user.

# Specify the extension space size when creating or modifying a table mysql> CREATE TABLE t1 (c1 INT) AUTOEXTEND_SIZE = 4M;
mysql> ALTER TABLE t1 AUTOEXTEND_SIZE = 4M;

# Query the attribute valuemysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
    WHERE NAME LIKE 'test/t1';
+---------+-----------------+
| NAME | AUTOEXTEND_SIZE |
+---------+-----------------+
| test/t1 | 4194304 |
+---------+-----------------+

https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-autoextend-size.html

Added temptable_max_mmap variable

The new variable defines the maximum amount of memory that the TempTable storage engine is allowed to allocate from memory-mapped files before it starts storing internal temporary table data to InnoDB disk internal temporary tables. The temptable_max_mmap = 0 setting disables allocations from memory mapped files.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_mmap

5. Copy

Term substitution

The CHANGE MASTER TO statement is deprecated. Use the alias CHANGE REPLICATION SOURCE TO instead. The parameters of 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 parameters MASTER_LOG_POS and MASTER_LOG_FILE of the START REPLICA | SLAVE statement now have aliases SOURCE_LOG_POS and SOURCE_LOG_FILE. The statements work the same way as before, only the terminology used for each statement has changed. If an older version is used, a deprecation warning will be issued.

Replicate directly from a GTID-disabled master to a GTID-enabled slave

New option for CHANGE REPLICATION SOURCE TO statement: ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = [OFF,LOCAL,<UUID>]

Allows data to be transferred between non-GTID instances and GTID instances.

https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-assign-anon.html

Including MDL and ACL locks in the MTS deadlock detection infrastructure

Integrates the thread serialization infrastructure required to provide multithreaded REPLICA that maintains the same commit order as SOURCE with the MDL and ACL access serialization infrastructure. The motivation is to be able to execute any client statement on REPLICA while REPLICA is actively processing the change stream. Such statements can create deadlocks, which must be detected and eventually broken to continue execution.

Group Replication

Automatic connection failover for asynchronous replication channels will ensure that the sender list on the receiver side is synchronized with Group Replication membership changes.

6. X Protocol

Classic MySQL protocol, if the SQL query uses metadata locking or sleep functions, the connection to the server will be checked periodically to verify that it is still valid. If not, the query can be stopped so that it does not continue to consume resources. Previously, the X protocol did not perform these checks and assumed that the connection was still valid. Checks have now been added for the X protocol.

Starting with MySQL 8.0.23, the server notifies all clients about whether it has just closed the connection or closed it on its own. The client can use this information to decide whether it makes sense to reconnect and try again.

VII. Others

Optimize hash table implementation for hash joins. The goal is to increase performance, use less memory and improve memory control.

Replaced some old InnoDB code with standard C++11. Enforce the rules and semantics of using atomicity in your code, thus making it more standards-compliant.

8. Deprecation and Removal

Deprecated relay_log_info_repository and master_info_repository. A deprecation warning will appear when a user sets or reads the value of the relay_log_info_repository or master_info_repository variables. Going forward, the only option for storing replication configuration and metadata will be in transactional system tables.

The FLUSH HOSTS statement is deprecated in favor of TRUNCATE performance_schema.host_cache and will be removed in a future MySQL version.

Article reference

MySQL 8.0.23 Release Notes:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html

The MySQL 8.0.23 Maintenance Release is Generally Available

https://mysqlserverteam.com/the-mysql-8-0-23-maintenance-release-is-generally-available/

This is the end of this article about the major updates of MySQL 8.0.23 (interpretation of new features). For more relevant MySQL 8.0.23 updates, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!