MySQL 8.0.23 New Features Overview

MySQL 8.0.23 was released as a maintenance version, fixing bugs and adding several new features.

1. Invisible Columns

Columns can be defined as INVISIBLE, so they are omitted from SELECT * results unless explicitly referenced.

# Create table with invisible column (ALTER TABLE also supports)
mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> INSERT INTO t1 (col1, col2) VALUES(1,2),(3,4);
# Query using explicit reference
mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
| 1    |
| 3    |
+------+
# Column omitted when not referenced
mysql> SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
| 1    | 2    |
| 3    | 4    |
+------+------+

2. Query Attributes

Applications can set metadata for a query using query_attributes and retrieve it with the mysql_query_attribute_string() UDF.

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

3. Security Enhancements

Doublewrite File Page Encryption

InnoDB automatically encrypts doublewrite file pages for encrypted tablespaces; pages from unencrypted tablespaces remain unencrypted.

Improved Account Determinism

Host matching now checks IP‑based accounts before hostname‑based ones, supporting plain IP, CIDR, and IP‑with‑netmask specifications.

# Account with IP address
mysql> CREATE USER 'user_name'@'127.0.0.1';
mysql> CREATE USER 'user_name'@'198.51.100.44';
# CIDR notation
mysql> CREATE USER 'user_name'@'192.0.2.21/8';
mysql> CREATE USER 'user_name'@'198.51.100.44/16';
# IP with netmask
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';

More Precise FLUSH Privileges

Users with specific FLUSH privileges (e.g., FLUSH OPTIMIZER_COSTS) can execute those statements without the global RELOAD privilege.

4. InnoDB Improvements

Optimized TRUNCATE/DROP

Large tables on instances with big buffer pools, tables with many adaptive hash index pages, and temporary tablespaces are now lazily freed or reused after TRUNCATE/DROP.

NEW AUTOEXTEND_SIZE Attribute

CREATE/ALTER TABLESPACE and CREATE/ALTER TABLE now accept an AUTOEXTEND_SIZE clause to control tablespace growth size.

# Specify auto‑extend size when creating or altering a table
mysql> CREATE TABLE t1 (c1 INT) AUTOEXTEND_SIZE = 4M;
mysql> ALTER TABLE t1 AUTOEXTEND_SIZE = 4M;
# Query the attribute
mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'test/t1';

5. New System Variable temptable_max_mmap

This variable limits the amount of memory that can be allocated from memory‑mapped files for internal temporary tables; setting it to 0 disables mmap allocation.

6. Replication Changes

Terminology Replacement

CHANGE MASTER TO is deprecated in favor of CHANGE REPLICATION SOURCE TO, with related terms (MASTER_HOST → SOURCE_HOST, etc.) renamed accordingly.

GTID Assignment from Non‑GTID Masters

The new ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS option allows data transfer between GTID‑enabled and GTID‑disabled servers.

MDL and ACL Locks in MTS Deadlock Detection

Multi‑threaded replica now integrates MDL and ACL lock serialization into its deadlock detection infrastructure.

7. Group Replication

Automatic failover for asynchronous replication channels now keeps sender lists synchronized with group‑replication membership changes.

8. X Protocol Enhancements

The X protocol now checks connection liveness for queries that use metadata locks or sleep, and the server notifies clients when a connection is closed.

9. Other Improvements

Hash join hash table implementation has been optimized for performance and memory usage, and parts of InnoDB have been modernized to C++11 with stronger atomic semantics.

10. Deprecations and Removals

relay_log_info_repository and master_info_repository are deprecated; FLUSH HOSTS is discouraged in favor of TRUNCATE performance_schema.host_cache.

References: MySQL 8.0.23 Release Notes and related documentation links.