ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3
CategoryVersionTypeChangeNote
4
SQL_MODE8.0.11Incompatible ChangeThese 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.For MySQL 5.7 applications that use SQL modes removed in MySQL 8.0,
statements may fail when replicated from a MySQL 5.7 master to a MySQL 8.0 slave,
or may have different effects on master and slave. To avoid such problems,
applications that use modes removed in MySQL 8.0 should be revised to avoid them.
5
AUTH8.0.11DeprecateUsing 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.Additionally, because IDENTIFIED BY PASSWORD syntax has been removed,
the log_builtin_as_identified_by_password system variable is superfluous and has been removed.
6
AUTH8.0.11DeprecateThe old_passwords system variable is no longer available.MySQL 5.7.2 よりも前では、Password カラム内のパスワードハッシュ形式に応じて、
サーバーは暗黙的に mysql_native_password または mysql_old_password プラグインを使用します。
Password 値が空または 4.1 のパスワードハッシュ (41 文字) である場合、サーバーは mysql_native_passwordを使用します。
パスワード値が 4.1 より前のパスワードハッシュ (16 文字) の場合、サーバーは mysql_old_password を使用します

https://dev.mysql.com/doc/refman/5.6/ja/account-upgrades.html
7
Information_schema8.0.11Newly AddThe new INFORMATION_SCHEMA.KEYWORDS table lists the words considered keywords by MySQL and, for each one, indicates whether it is reserved. TKEYWORDS テーブルには、MySQL で考慮されるキーワードがリストされ、各キーワードについて予約されているかどうかが示されます。 予約済キーワードは、識別子として使用される場合の特別な引用符など、一部のコンテキストで特別な処理が必要になることがあります (セクション9.3「キーワードと予約語」 を参照)。

https://dev.mysql.com/doc/refman/8.0/ja/information-schema-keywords-table.html
8
Logging8.0.11Newly AddMessages 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).[root@data]# tail -n 10 MySQL8.err
2022-10-16T23:57:22.549555Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2022-10-16T23:57:22.549573Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) starting as process 2282
2022-10-16T23:57:22.609000Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-16T23:57:23.282852Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-16T23:57:23.883260Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-10-16T23:57:23.883312Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-10-16T23:57:23.952189Z 6 [Warning] [MY-013140] [Server] Error in diagnostics area: MY-001681 - Updating 'collation_database' is deprecated. It will be made read-only in a future release.
2022-10-16T23:57:23.952224Z 6 [Warning] [MY-013140] [Server] Error in diagnostics area: MY-001681 - Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
2022-10-16T23:57:23.953089Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2022-10-16T23:57:23.953176Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.30' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
[root@]#
9
Performance Schema8.0.11Newly AddA 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.mysql> select * from log_status limit 1\G
*************************** 1. row ***************************
SERVER_UUID: 5e3c1de7-5cc1-11ec-8b25-0242ac120002
LOCAL: {"gtid_executed": "", "binary_log_file": "binlog.000033", "binary_log_position": 17823}
REPLICATION: {"channels": []}
STORAGE_ENGINES: {"InnoDB": {"LSN": 134345815, "LSN_checkpoint": 134345815}}
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-log-status-table.html
10
Buffer Pool8.0.11Newly AddTo improve startup performance on systems with large buffer pools, buffer pool initialization is now multithreaded.
11
SHOW CREATE TABLE8.0.11Newly AddSHOW 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. mysql> show variables like "show_create_table_verbosity";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| show_create_table_verbosity | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.03 sec)

mysql> set show_create_table_verbosity=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_show_create_table_verbosity
12
ALTER TABLE8.0.12Newly AddInnoDB now supports ALGORITHM=INSTANT for the following ALTER TABLE operations.Adding a column. This feature is referred to as “Instant ADD COLUMN”.
Adding or dropping a virtual column.
Adding or dropping a column default value.
Modifying the definition of an ENUM or SET column.
Changing the index type.
Renaming a table.

INSTANT: Operations only modify metadata in the data dictionary. An exclusive metadata lock on the table may be taken briefly during the execution phase of the operation. Table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12) Thanks to the Tencent Games DBA team for the contribution.

https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html

mysql> desc T1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| note | varchar(10) | YES | | NULL | |
| who | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> alter table T1 add column instant varchar(100), algorithm=inplace, lock=none;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc T1;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| note | varchar(10) | YES | | NULL | |
| who | varchar(100) | YES | | NULL | |
| instant | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql>
13
Account Management8.0.13Newly AddIt 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.mysql> select Host,user,Password_require_current from mysql.user limit 1;
+------+-------+--------------------------+
| Host | user | Password_require_current |
+------+-------+--------------------------+
| % | admin | NULL |
+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> show global variables like "password_require_current";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_require_current | OFF |
+--------------------------+-------+
1 row in set (0.04 sec)
14
SQL_MODE8.0.13Newly AddThe 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. mysql> show global variables like "sql_require_primary_key";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| sql_require_primary_key | OFF |
+-------------------------+-------+
1 row in set (0.00 sec)

新しいテーブルを作成するステートメントまたは既存のテーブルの構造を変更するステートメントが、テーブルに主キーがあるという要件を強制するかどうか。sql_require_primary_key は実テーブルと TEMPORARY テーブルの両方に適用され、その値に対する変更はレプリカサーバーにレプリケートされます。 MySQL 8.0.18 では、レプリケーションに参加できるストレージエンジンにのみ適用されます。

https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html
15
InnoDB; Partitioning:8.0.13Incompatible ChangeInnoDB; 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.2.11.5 アップグレード用のインストールの準備
https://dev.mysql.com/doc/refman/8.0/ja/upgrade-prerequisites.html
16
TEMPORARY TABLE8.0.13DeprecateCREATE TEMPORARY TABLE での TABLESPACE = innodb_file_per_table 句および TABLESPACE = innodb_temporary 句のサポートは、MySQL 8.0.13 で非推奨になりました。MySQL の将来のバージョンで削除される予定です。mysql> CREATE TEMPORARY TABLE new_tbl SELECT * FROM t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TEMPORARY TABLE new_tbl2 TABLESPACE = innodb_file_per_table SELECT * FROM t2 ;
ERROR 1478 (HY000): InnoDB: TABLESPACE=innodb_file_per_table option is disallowed for temporary tables with INNODB_STRICT_MODE=ON. This option is deprecated and will be removed in a future release

https://dev.mysql.com/doc/refman/8.0/ja/create-temporary-table.html
17
character8.0.13DeprecateThe utf8mb3 character set is deprecated and will be removed in a future MySQL version. Please use utf8mb4 instead.mysql> CREATE TABLE `t_utf8mb4` (
-> `c1` int NOT NULL,
PRI -> PRIMARY KEY (`c1`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `t_utf8mb3` (
-> `c1` int NOT NULL,
-> PRIMARY KEY (`c1`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1287 | 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `t_utf8` (
`c1` int NO -> `c1` int NOT NULL,
RIMARY K -> PRIMARY KEY (`c1`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
18
INFORMATION_SCHEMA8.0.13Newly Addselect * from information_schema.VIEW_ROUTINE_USAGE;
select * from information_schema.VIEW_TABLE_USAGE;		provides information about stored functions used in view definitions.
provides information about tables and views used in view definitions.
19
Replication8.0.13Incompatible ChangePreviously, 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).
20
Replication8.0.13Incompatible ChangeTrying 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.SET PERSIST および SET PERSIST_ONLY を使用すると、グローバルシステム変数をデータディレクトリ内の mysqld-auto.cnf オプションファイルに永続化できます (セクション13.7.6.1「変数代入の SET 構文」 を参照)。 ただし、すべてのシステム変数を永続化できるわけではありません。または、特定の制限条件下でのみ永続化できるわけではありません。 システム変数が永続的または永続的に制限されない理由を次に示します:

セッションシステム変数は永続化できません。 セッション変数はサーバーの起動時に設定できないため、永続化する理由はありません。

グローバルシステム変数には、サーバーホストへの直接アクセス権を持つユーザーのみが設定できるような機密データが含まれる場合があります。

グローバルシステム変数は読取り専用の場合があります (つまり、サーバーによってのみ設定されます)。 この場合、サーバーの起動時でも実行時でも、ユーザーが設定することはできません。

グローバルシステム変数は、内部使用のみを目的としている場合があります。

永続的でないシステム変数は、どのような状況でも永続化できません。 MySQL 8.0.14 の時点では、永続制限付きシステム変数は SET PERSIST_ONLY で永続化できますが、次の条件を満たすユーザーのみが永続化できます:

persist_only_admin_x509_subject システム変数は、SSL 証明書の X.509 サブジェクト値に設定されます。

ユーザーは暗号化された接続を使用してサーバーに接続し、指定されたサブジェクト値で SSL 証明書を提供します。

ユーザーには、SET PERSIST_ONLY を使用するための十分な権限があります (セクション5.1.9.1「システム変数権限」 を参照)。

たとえば、protocol_version は読取り専用であり、サーバーによってのみ設定されるため、どのような状況でも永続化できません。 一方、bind_address は永続的に制限されるため、前述の条件を満たすユーザーが設定できます。

https://dev.mysql.com/doc/refman/8.0/ja/nonpersistible-system-variables.html
21
TEMPORARY TABLE8.0.13DeprecateThe 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.internal_tmp_disk_storage_engine
Prior to MySQL 8.0.16, this variable determines the storage engine used for on-disk internal temporary tables (see Storage Engine for On-Disk Internal Temporary Tables). Permitted values are MYISAM and INNODB (the default).
In MySQL 8.0.16 and later, on-disk internal temporary tables always use the InnoDB storage engine; as of MySQL 8.0.16, this variable has been removed and is thus no longer supported.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine
22
RENAME TABLE8.0.13Newly AddPreviously, 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.As of MySQL 8.0.13, you can rename tables locked with a LOCK TABLES statement, provided that they are locked with a WRITE lock or are the product of renaming WRITE-locked tables from earlier steps in a multiple-table rename operation.

mysql> LOCK TABLE t1 WRITE;
Query OK, 0 rows affected (0.01 sec)
mysql> RENAME TABLE t1 TO t2;
Query OK, 0 rows affected (0.10 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/rename-table.html


--------------------------------

mysql> set session lock_wait_timeout = 10;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%lock_wait_timeout%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 10 |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> LOCK TABLE t1 WRITE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
23
PARAMETER8.0.13Newly Addinnodb_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)		デフォルトでは、InnoDB が新しいログファイルやテーブルスペースファイルなどの新しいデータファイルを作成すると、ファイルはディスクにフラッシュされる前にオペレーティングシステムキャッシュに完全に書き込まれるため、大量のディスク書込みアクティビティが一度に発生する可能性があります。 オペレーティングシステムキャッシュから定期的にデータを強制的に小さいフラッシュするには、innodb_fsync_threshold 変数を使用してしきい値をバイト単位で定義します。 バイトしきい値に達すると、オペレーティングシステムキャッシュの内容がディスクにフラッシュされます。 デフォルト値の 0 では、デフォルトの動作が強制されます。つまり、ファイルがキャッシュに完全に書き込まれた後にのみ、データがディスクにフラッシュされます。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-parameters.html#sysvar_innodb_fsync_threshold
24
TEMPORARY TABLE8.0.13ImprovementInnoDB: 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.The innodb_temp_tablespaces_dir variable defines the location where session temporary tablespaces are created. The default location is the #innodb_temp directory in the data directory.
The INNODB_SESSION_TEMP_TABLESPACES table provides metadata about session temporary tablespaces.
The global temporary tablespace (ibtmp1) now stores rollback segments for changes made to user-created temporary tables.

mysql> show variables like 'innodb_temp_tablespaces_dir';
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
+-----------------------------+-----------------+
1 row in set (0.08 sec)

mysql> desc information_schema.INNODB_SESSION_TEMP_TABLESPACES;
+---------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| ID | int unsigned | NO | | | |
| SPACE | int unsigned | NO | | | |
| PATH | varchar(4001) | NO | | | |
| SIZE | bigint unsigned | NO | | | |
| STATE | varchar(192) | NO | | | |
| PURPOSE | varchar(192) | NO | | | |
+---------+-----------------+------+-----+---------+-------+
6 rows in set (0.09 sec)

mysql> select * from information_schema.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+-----------+
| 14 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767289 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+-----------+
10 rows in set (0.01 sec)
25
ENCRYPTION8.0.13ImprovementInnoDB: 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		mysql> select SPACE,NAME,ROW_FORMAT,ZIP_PAGE_SIZE,SPACE_TYPE,FS_BLOCK_SIZE,SERVER_VERSION,ENCRYPTION,STATE from INFORMATION_SCHEMA.INNODB_TABLESPACES limit 1\G
*************************** 1. row ***************************
SPACE: 4294967294
NAME: mysql
ROW_FORMAT: Any
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General
FS_BLOCK_SIZE: 4096
SERVER_VERSION: 8.0.27
ENCRYPTION: N
STATE: normal
1 row in set (0.00 sec)
26
TEMPORARY TABLE8.0.13ImprovementPreviously, 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.		mysql> START TRANSACTION;CREATE TEMPORARY TABLE CONFIRM.item_genres(`genre_id` INT PRIMARY KEY, `genre_name` TEXT);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+------------+--------+------------+
| TABLE_ID | NAME | N_COLS | SPACE |
+----------+------------+--------+------------+
| 1220 | #sql1_e_28 | 5 | 4243767289 |
+----------+------------+--------+------------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------+
| Warning | 1751 | The creation of some temporary tables could not be rolled back. |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE CONFIRM.item_genres;
Query OK, 0 rows affected (0.00 sec)

mysql>
27
AUTH8.0.14Newly AddPreviously, 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.レプリカではない各サーバーで、'password_b'を新しい appuser1 プライマリパスワードとして確立し、現在のパスワードをセカンダリパスワードとして保持します:

ALTER USER 'appuser1'@'host1.example.com'
IDENTIFIED BY 'password_b'
RETAIN CURRENT PASSWORD;
パスワード変更がシステム全体ですべてのレプリカにレプリケートされるのを待ちます。

'password_a'ではなく'password_b'のパスワードを使用してサーバーに接続するように、appuser1 アカウントを使用する各アプリケーションを変更します。

この時点で、セカンダリパスワードは不要になりました。 レプリカではない各サーバーで、セカンダリパスワードを破棄します:

ALTER USER 'appuser1'@'host1.example.com'
DISCARD OLD PASSWORD;

https://dev.mysql.com/doc/refman/8.0/ja/password-management.html
28
AUTH8.0.14Newly AddMySQL 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.		管理ネットワークインタフェースで TCP/IP 接続をリスニングする IP アドレス (セクション5.1.12.1「接続インタフェース」 を参照)。 デフォルトの admin_address 値はありません。 この変数が起動時に指定されない場合、サーバーは管理インタフェースを維持しません。 サーバーには、通常の (非管理) クライアント TCP/IP 接続を構成するための bind_address システム変数もあります。 セクション5.1.12.1「接続インタフェース」を参照してください。


https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_admin_address
29
UNDO8.0.14Newly AddInnoDB: 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.		mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
-> WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.01 sec)

mysql> CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'innodb_undo_manual001.ibu';
Query OK, 0 rows affected (0.42 sec)

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+-----------------------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+-----------------------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
| tablespace_name | ./innodb_undo_manual001.ibu |
+-----------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_TABLESPACES where SPACE_TYPE = 'Undo';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 6213632 | 0 | 8.0.27 | 1 | N | active |
| 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 9117696 | 0 | 8.0.27 | 1 | N | active |
| 4294967277 | tablespace_name | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.27 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.00 sec)
30
INNODB8.0.14Newly AddInnoDB: 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.mysql> show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_parallel_read_threads | 4 |
+------------------------------+-------+
1 row in set (0.00 sec)

パラレルクラスタインデックス読取りに使用できるスレッドの数を定義します。 パーティションのパラレルスキャンは、MySQL 8.0.17 でサポートされています。 パラレル読取りスレッドを使用すると、CHECK TABLE のパフォーマンスを向上できます。 InnoDB は、CHECK TABLE 操作中にクラスタ化されたインデックスを 2 回読み取ります。 2 番目の読取りはパラレルで実行できます。 この機能は、セカンダリインデックススキャンには適用されません。 パラレルクラスタインデックス読取りを実行するには、innodb_parallel_read_threads セッション変数を 1 より大きい値に設定する必要があります。 パラレルクラスタインデックス読取りの実行に使用されるスレッドの実際の数は、innodb_parallel_read_threads 設定またはスキャンするインデックスサブツリーの数 (いずれか小さい方) によって決まります。 スキャン中にバッファープールに読み取られたページは、空きバッファープールページが必要なときにすぐに破棄できるように、バッファープール LRU リストの末尾に保持されます。

MySQL 8.0.17 では、パラレル読取りスレッドの最大数 (256) は、すべてのクライアント接続のスレッドの合計数です。 スレッド制限に達すると、接続は単一スレッドの使用にフォールバックします。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-parameters.html#sysvar_innodb_parallel_read_threads
31
INNODB8.0.14DeprecateThe previously deprecated innodb_undo_tablespaces variable is no longer configurable and will be removed in a future MySQL version.mysql> show variables like 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 2 |
+-------------------------+-------+
1 row in set (0.00 sec)
32
ALTER TABLE8.0.14Newly AddALTER 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.		mysql> show create table T1\G
*************************** 1. row ***************************
Table: T1
Create Table: CREATE TABLE `T1` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '-' COMMENT '8.0.27',
`who` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> insert into T1(note,who) values('10xutf8mb3','Size 100 with utf8mb4');
Query OK, 1 row affected (0.02 sec)

mysql> select *,version() from T1;
+----+------------+-----------------------+-----------+
| id | note | who | version() |
+----+------------+-----------------------+-----------+
| 1 | 10xutf8mb3 | Size 100 with utf8mb4 | 8.0.27 |
+----+------------+-----------------------+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE T1 MODIFY note varchar(10) CHARACTER SET utf8mb4, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table T1\G
*************************** 1. row ***************************
Table: T1
Create Table: CREATE TABLE `T1` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`who` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE T1 MODIFY note varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table T1\G
*************************** 1. row ***************************
Table: T1
Create Table: CREATE TABLE `T1` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`who` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysql>



33
Account Management8.0.16Newly AddPreviously, 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:mysql> CREATE USER u1@'%' identified by 'Password2022';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT user,host from mysql.user where user = 'u1';
+------+------+
| user | host |
+------+------+
| u1 | % |
+------+------+
1 row in set (0.00 sec)

mysql> GRANT SELECT, INSERT ON *.* TO u1@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1@'%';
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
mysql> SET PERSIST partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> show grants for u1@'%'\G
*************************** 1. row ***************************
Grants for u1@%: GRANT SELECT, INSERT ON *.* TO `u1`@`%`
*************************** 2. row ***************************
Grants for u1@%: REVOKE SELECT, INSERT ON `mysql`.* FROM `u1`@`%`
2 rows in set (0.00 sec)
34
TEMPORARY TABLE8.0.16DeprecateThe 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)mysql> show variables like 'internal_tmp_disk_storage_engine';
Empty set (0.00 sec)

オンディスク内部一時テーブルのストレージエンジン
MySQL 8.0.16 以降、サーバーは常に InnoDB ストレージエンジンを使用してディスク上の内部一時テーブルを管理します。

MySQL 8.0.15 以前では、internal_tmp_disk_storage_engine 変数を使用して、ディスク上の内部一時テーブルに使用されるストレージエンジンを定義していました。 この変数は MySQL 8.0.16 で削除され、この目的に使用されるストレージエンジンはユーザーが構成できなくなりました。

MySQL 8.0.15 以前: 共通テーブル式 (CTE) の場合、ディスク上の内部一時テーブルに使用されるストレージエンジンを MyISAM にすることはできません。 internal_tmp_disk_storage_engine=MYISAM の場合、ディスク上の一時テーブルを使用して CTE を実体化しようとすると、エラーが発生します。

MySQL 8.0.15 以前: internal_tmp_disk_storage_engine=INNODB を使用している場合、InnoDB row or column limits を超えるディスク上の内部一時テーブルを生成するクエリーは、「行サイズが大きすぎます」または「カラムが多すぎます」エラーを返します。 回避策は、internal_tmp_disk_storage_engine を MYISAM に設定することです。

https://dev.mysql.com/doc/refman/8.0/ja/internal-temporary-tables.html
35
DATA DICTIONARY8.0.16Newly Addmysql_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.		This option controls whether and how the server performs an automatic upgrade at startup. Automatic upgrade involves two steps:

Step 1: Data dictionary upgrade.
This step upgrades:
The data dictionary tables in the mysql schema. If the actual data dictionary version is lower than the current expected version, the server upgrades the data dictionary. If it cannot, or is prevented from doing so, the server cannot run.

The Performance Schema and INFORMATION_SCHEMA.
Step 2: Server upgrade.
This step comprises all other upgrade tasks. If the existing installation data has a lower MySQL version than the server expects, it must be upgraded:
The system tables in the mysql schema (the remaining non-data dictionary tables).
The sys schema.
User schemas.
For details about upgrade steps 1 and 2, see Section 2.11.3, “What the MySQL Upgrade Process Upgrades”.
36
PLUGIN8.0.16Newly AddMySQL 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.mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ddl%';
Empty set (0.00 sec)

mysql> install plugin ddl_rewriter soname 'ddl_rewriter.so';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ddl%';
+--------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+---------------+-------------+
| ddl_rewriter | ACTIVE | AUDIT |
+--------------+---------------+-------------+
1 row in set (0.01 sec)

mysql> use POC
Database changed
mysql> CREATE TABLE `T_ENCRYPTION` (
-> `id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
-> `note` varchar(10) DEFAULT NULL,
-> `who` varchar(100) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENCRYPTION='Y';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1105
Message: Query 'CREATE TABLE `T_ENCRYPTION` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) DEFAULT NULL,
`who` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENCRYPTION='Y'' rewritten to 'CREATE TABLE `T_ENCRYPTION` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) DEFAULT NULL,
`who` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=I
1 row in set (0.00 sec)

mysql> UNINSTALL PLUGIN ddl_rewriter;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1620
Message: Plugin is busy and will be uninstalled on shutdown
1 row in set (0.00 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ddl%';
+--------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+---------------+-------------+
| ddl_rewriter | DELETED | AUDIT |
+--------------+---------------+-------------+
1 row in set (0.00 sec)

mysql>
37
ALTER INSTANCE8.0.16
8.0.21		Newly AddThe 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.ALTER INSTANCE instance_action

instance_action: {
| {ENABLE|DISABLE} INNODB REDO_LOG
| ROTATE INNODB MASTER KEY
| ROTATE BINLOG MASTER KEY
| RELOAD TLS
[FOR CHANNEL {mysql_main | mysql_admin}]
[NO ROLLBACK ON ERROR]
}


ALTER INSTANCE {ENABLE | DISABLE} INNODB REDO_LOG

このアクションは、InnoDB redo ロギングを有効または無効にします。 redo ロギングはデフォルトで有効になっています。 この機能は、新しい MySQL インスタンスへのデータのロードのみを目的としています。 ステートメントはバイナリログに書き込まれません。 MySQL 8.0.21 で導入されました。

ALTER INSTANCE RELOAD TLS

このアクションは、コンテキストを定義するシステム変数の現在の値から TLS コンテキストを再構成します。 また、アクティブなコンテキスト値を反映するステータス変数も更新されます。 このアクションには、CONNECTION_ADMIN 権限が必要です。 TLS コンテキストの再構成の詳細 (コンテキスト関連のシステム変数やステータス変数など) は、サーバー側のランタイム構成および暗号化された接続の監視 を参照してください。

デフォルトでは、ステートメントはメイン接続インタフェースの TLS コンテキストをリロードします。 (MySQL 8.0.21 で使用可能な) FOR CHANNEL 句が指定されている場合、ステートメントは指定されたチャネルの TLS コンテキストをリロード: メイン接続インタフェースの場合は mysql_main、管理接続インタフェースの場合は mysql_admin。 様々なインタフェースの詳細は、セクション5.1.12.1「接続インタフェース」 を参照してください。 更新された TLS コンテキストプロパティーは、パフォーマンススキーマ tls_channel_status テーブルで公開されます。 セクション27.12.19.11「tls_channel_status テーブル」を参照してください。
https://dev.mysql.com/doc/refman/8.0/ja/alter-instance.html
38
INFORMATION_SCHEMA8.0.16Newly AddPreviously, 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)mysql> CREATE TABLE T_CHECK (
-> `c1` int(11) DEFAULT NULL,
-> `c2` int(11) DEFAULT NULL,
-> `c3` int(11) DEFAULT NULL,
-> CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
-> CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
-> CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
-> CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),
-> CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),
-> CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected, 3 warnings (0.05 sec)

mysql> select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+----------------+
| def | POC | c1_nonzero | (`c1` <> 0) |
| def | POC | c2_positive | (`c2` > 0) |
| def | POC | t1_chk_1 | (`c1` <> `c2`) |
| def | POC | t1_chk_2 | (`c1` > 10) |
| def | POC | t1_chk_3 | (`c3` < 100) |
| def | POC | t1_chk_4 | (`c1` > `c3`) |
+--------------------+-------------------+-----------------+----------------+
6 rows in set (0.01 sec)

mysql>
39
FUNCTION8.0.16Newly AddFORMAT_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.		mysql> select sys.format_bytes(1024),FORMAT_BYTES(1024);
+------------------------+--------------------+
| sys.format_bytes(1024) | FORMAT_BYTES(1024) |
+------------------------+--------------------+
| 1.00 KiB | 1.00 KiB |
+------------------------+--------------------+
1 row in set (0.00 sec)

mysql> select sys.format_bytes(1024000),FORMAT_BYTES(1024000);
+---------------------------+-----------------------+
| sys.format_bytes(1024000) | FORMAT_BYTES(1024000) |
+---------------------------+-----------------------+
| 1000.00 KiB | 1000.00 KiB |
+---------------------------+-----------------------+
1 row in set (0.00 sec)

mysql>
40
TEMPORARY TABLE8.0.16Newly AddInnoDB: 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)mysql> show variables like 'temptable_use_mmap';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| temptable_use_mmap | ON |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'temptable_max_ram';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| temptable_max_ram | 1073741824 |
+-------------------+------------+
1 row in set (0.00 sec)

mysql> select 1073741824/1024/1024;
+----------------------+
| 1073741824/1024/1024 |
+----------------------+
| 1024.00000000 |
+----------------------+
1 row in set (0.00 sec)

mysql>
41
FUNCTION8.0.16DeprecateThe 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();		mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 3 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------+
| Warning | 1287 | FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. |
+---------+------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
42
JSON8.0.16Newly AddInnoDB; 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:mysql> select * from T_JSON where 94477 MEMBER OF(doc->'$.zipcode');
+----+--------------------------------------------+
| id | doc |
+----+--------------------------------------------+
| 1 | {"user": "Bob", "zipcode": [94477, 94536]} |
+----+--------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from T_JSON where 94477 MEMBER OF(doc->'$.zipcode');
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | T_JSON | NULL | ref | idx_zips | idx_zips | 9 | const | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from T_JSON where JSON_CONTAINS(doc->'$.zipcode', CAST('[94477, 94536]' AS JSON));
+----+--------------------------------------------+
| id | doc |
+----+--------------------------------------------+
| 1 | {"user": "Bob", "zipcode": [94477, 94536]} |
+----+--------------------------------------------+
1 row in set (0.00 sec)


mysql> select * from T_JSON where JSON_OVERLAPS(doc->'$.zipcode', CAST('[94477, 94536]' AS JSON));
+----+--------------------------------------------+
| id | doc |
+----+--------------------------------------------+
| 1 | {"user": "Bob", "zipcode": [94477, 94536]} |
+----+--------------------------------------------+
1 row in set (0.01 sec)

43
VARIABLES8.0.17Newly AddIn 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		mysql> show variables like 'lower_case_table_%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.01 sec)

shinya@DESKTOP-8BDL7KA:/usr/bin$ ls -l debconf*
-rwxr-xr-x 1 root root 2859 Aug 3 2019 debconf
-rwxr-xr-x 1 root root 11541 Aug 3 2019 debconf-apt-progress
-rwxr-xr-x 1 root root 608 Aug 3 2019 debconf-communicate
-rwxr-xr-x 1 root root 1719 Aug 3 2019 debconf-copydb
-rwxr-xr-x 1 root root 647 Aug 3 2019 debconf-escape
-rwxr-xr-x 1 root root 2935 Aug 3 2019 debconf-set-selections
-rwxr-xr-x 1 root root 1827 Aug 3 2019 debconf-show


shell> sudo debconf-set-selections <<< "mysql-server mysql-server/lowercase-table-names select Enabled
44
AUTH8.0.17CHANGEDThe 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)0666 to 0640
45
Replication8.0.17CHANGEDThe 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)SET @@GLOBAL.gtid_purged statement is added to the mysqldump output.
補足：UUIDはMacアドレスとタイムスタンプからUUIDを生成している。

root@localhost [mysql]> show variables like 'gtid_purged'\G
*************************** 1. row ***************************
Variable_name: gtid_purged
Value: 50fca08e-5a35-11e8-a4f2-06db798d79c8:1-6625843
1 row in set (0.01 sec)


gtid_purged システム変数 (@@GLOBAL.gtid_purged) 内の GTID のセットには、サーバー上でコミットされたが、サーバー上のバイナリログファイルには存在しないすべてのトランザクションの GTID が含まれています。gtid_purged は、gtid_executed のサブセットです。 GTID の次のカテゴリが gtid_purged にあります:

- レプリカでバイナリロギングを無効にしてコミットされたレプリケートされたトランザクションの GTID。
- 現在パージされているバイナリログファイルに書き込まれたトランザクションの GTID。
- ステートメント SET @@GLOBAL.gtid_purged によってセットに明示的に追加された GTID。
46
FUNCTION8.0.17Newly AddMySQL 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)

mysql> create table t2 as select Cast(0 as Double);
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> desc t2;
+-------------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------+------+-----+---------+-------+
| Cast(0 as Double) | double | NO | | 0 | |
+-------------------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table t as select 0.00 as rowd;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> desc t;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| rowd | decimal(3,2) | NO | | 0.00 | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)
47
TOOL8.0.17Newly AddMySQL 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.5.6.7 クローンプラグイン
https://dev.mysql.com/doc/refman/8.0/ja/clone-plugin.html
48
ACCount Management8.0.18Newly AddThe 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.mysql> CREATE USER 'RANDOM_PASSWORD_USER_TEST'@'%' IDENTIFIED BY RANDOM PASSWORD;
+---------------------------+------+----------------------+-------------+
| user | host | generated password | auth_factor |
+---------------------------+------+----------------------+-------------+
| RANDOM_PASSWORD_USER_TEST | % | HwAO9}FQgwK3c<M}}CK3 | 1 |
+---------------------------+------+----------------------+-------------+
1 row in set (0.05 sec)
49
VARIABLES8.0.18DeprecateUse 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.

MYSQL_PWD: mysqld に接続する際のデフォルトのパスワード。 これの使用はセキュアではありません。 表のあとにある注釈を参照してください。

https://dev.mysql.com/doc/refman/8.0/ja/environment-variables.html
50
HASH JOIN8.0.18Newly AddHash 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.		HASH_JOIN, NO_HASH_JOINヒント: 指定したテーブルに対するハッシュ結合の使用を有効または無効にします (MySQL 8.0.18 のみ。MySQL 8.0.19 以降では無効です)。

mysql> explain select * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | T_CHECK | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | T_CHECK2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (hash join) |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql> explain select /*+ NO_HASH_JOIN(T_CHECK,T_CHECK2) */ * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | T_CHECK | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | T_CHECK2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (hash join) |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN FORMAT=TREE select * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (T_CHECK2.c1 = T_CHECK.c1) (cost=1.70 rows=3)
-> Table scan on T_CHECK2 (cost=0.12 rows=3)
-> Hash
-> Table scan on T_CHECK (cost=0.55 rows=3)

1 row in set (0.00 sec)

mysql> EXPLAIN ANALYZE select * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (T_CHECK2.c1 = T_CHECK.c1) (cost=1.70 rows=3) (actual time=0.496..0.504 rows=3 loops=1)
-> Table scan on T_CHECK2 (cost=0.12 rows=3) (actual time=0.078..0.084 rows=3 loops=1)
-> Hash
-> Table scan on T_CHECK (cost=0.55 rows=3) (actual time=0.295..0.338 rows=3 loops=1)

1 row in set (0.00 sec)

mysql>
51
SYS8.0.18Newly AddThe sys.schema_unused_indexes view now filters out unique indexes. Thanks to Gillian Gunson for the contribution. (Bug #24798995, Bug #83257)mysql> select * from sys.schema_unused_indexes limit 10;
+--------------------+-----------------+----------------------------------+
| object_schema | object_name | index_name |
+--------------------+-----------------+----------------------------------+
| performance_schema | cond_instances | NAME |
| performance_schema | data_lock_waits | BLOCKING_THREAD_ID |
| performance_schema | data_lock_waits | REQUESTING_ENGINE_LOCK_ID |
| performance_schema | data_lock_waits | BLOCKING_ENGINE_LOCK_ID |
| performance_schema | data_lock_waits | REQUESTING_ENGINE_TRANSACTION_ID |
| performance_schema | data_lock_waits | BLOCKING_ENGINE_TRANSACTION_ID |
| performance_schema | data_lock_waits | REQUESTING_THREAD_ID |
| performance_schema | data_locks | ENGINE_TRANSACTION_ID |
| performance_schema | data_locks | OBJECT_SCHEMA |
| performance_schema | data_locks | THREAD_ID |
+--------------------+-----------------+----------------------------------+
10 rows in set (0.04 sec)
52
SYS8.0.18Newly AddThe 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)指定されたパフォーマンススキーマコンシューマが有効かどうかを示す YES または NO を返します。引数が NULL の場合は NULL を返します。 引数が有効なコンシューマ名でない場合は、エラーが発生します。 (MySQL 8.0.18 より前では、引数が有効なコンシューマ名でない場合、この関数は NULL を戻します。)
この関数はコンシューマ階層を考慮しているため、依存するすべてのコンシューマも有効になっていないかぎり、コンシューマは有効とみなされません。

mysql> SELECT sys.ps_is_consumer_enabled('thread_instrumentation');
+------------------------------------------------------+
| sys.ps_is_consumer_enabled('thread_instrumentation') |
+------------------------------------------------------+
| YES |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT sys.ps_is_consumer_enabled('events_stages_current');
+-----------------------------------------------------+
| sys.ps_is_consumer_enabled('events_stages_current') |
+-----------------------------------------------------+
| NO |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_cpu | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+
16 rows in set (0.00 sec)

mysql> SELECT sys.ps_is_consumer_enabled('events_statements_current');
+---------------------------------------------------------+
| sys.ps_is_consumer_enabled('events_statements_current') |
+---------------------------------------------------------+
| YES |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
53
AUTH8.0.19Newly AddMySQL 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)mysql> CREATE USER 'non-official-user'@'%' IDENTIFIED BY RANDOM PASSWORD FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;
+-------------------+------+----------------------+-------------+
| user | host | generated password | auth_factor |
+-------------------+------+----------------------+-------------+
| non-official-user | % | mhn2sP-[k93v/<NneI0A | 1 |
+-------------------+------+----------------------+-------------+
1 row in set (0.03 sec)

shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -P 13306 -u non-official-user -p"mhn2sP-[k93v/<NneI0A"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+------------------------------+
| user() |
+------------------------------+
| non-official-user@172.18.0.1 |
+------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -P 13306 -u non-official-user -p"mhn2sP-[k93v/<NneI0A?"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'non-official-user'@'172.18.0.1' (using password: YES)
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -P 13306 -u non-official-user -p"mhn2sP-[k93v/<NneI0A?"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'non-official-user'@'172.18.0.1' (using password: YES)
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -P 13306 -u non-official-user -p"mhn2sP-[k93v/<NneI0A?"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'non-official-user'@'172.18.0.1'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 3 consecutive failed logins.
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$
54
VARIABLES8.0.19Newly AddInnodb_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.mysql> show status like 'Innodb_system_%';
+-----------------------------+--------+
| Variable_name | Value |
+-----------------------------+--------+
| Innodb_system_rows_deleted | 22292 |
| Innodb_system_rows_inserted | 26494 |
| Innodb_system_rows_read | 129879 |
| Innodb_system_rows_updated | 9092 |
+-----------------------------+--------+
4 rows in set (0.00 sec)
55
HASH JOIN8.0.19DeprecateSetting 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>		mysql> explain format=tree select * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (T_CHECK2.c1 = T_CHECK.c1) (cost=1.70 rows=3)
-> Table scan on T_CHECK2 (cost=0.12 rows=3)
-> Hash
-> Table scan on T_CHECK (cost=0.55 rows=3)

1 row in set (0.01 sec)

mysql> explain analyze select * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (T_CHECK2.c1 = T_CHECK.c1) (cost=1.70 rows=3) (actual time=0.356..0.399 rows=3 loops=1)
-> Table scan on T_CHECK2 (cost=0.12 rows=3) (actual time=0.130..0.170 rows=3 loops=1)
-> Hash
-> Table scan on T_CHECK (cost=0.55 rows=3) (actual time=0.129..0.174 rows=3 loops=1)

1 row in set (0.00 sec)

mysql> explain select /*+ NO_HASH_JOIN(T_CHECK,T_CHECK2) */ * from T_CHECK INNER JOIN T_CHECK2 ON T_CHECK.c1 = T_CHECK2.c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: T_CHECK
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: T_CHECK2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)

mysql>
56
STATEMENT8.0.19Newly AddImportant 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:MySQL 8.0.19 で導入された DML ステートメントで、指定されたテーブルの行とカラムを返します

mysql> TABLE T_CHECK;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1000 | 2000 | 10 |
| 1001 | 2001 | 11 |
| 1002 | 2002 | 12 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from T_CHECK;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1000 | 2000 | 10 |
| 1001 | 2001 | 11 |
| 1002 | 2002 | 12 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from T_CHECK where c3 >=11;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1001 | 2001 | 11 |
| 1002 | 2002 | 12 |
+------+------+------+
2 rows in set (0.01 sec)

mysql> TABLE T_CHECK where c3 >=11;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where c3 >=11' at line 1
mysql>
57
STATEMENT8.0.19Newly AddVALUES 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:mysql> SELECT a,b,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c);
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+---+---+---+
2 rows in set (0.00 sec)

mysql> SELECT a,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c);
+---+---+
| a | c |
+---+---+
| 1 | 3 |
| 4 | 6 |
+---+---+
2 rows in set (0.00 sec)

mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
| 1 | -2 | 3 |
| 5 | 7 | 9 |
| 4 | 6 | 8 |
+----------+----------+----------+
3 rows in set (0.00 sec)

mysql>

https://dev.mysql.com/doc/refman/8.0/en/values.html
58
STATEMENT8.0.19Newly AddPreviously, 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:mysql> WITH RECURSIVE cte AS (
-> SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL
-> UNION ALL
-> SELECT CONCAT("x",cte.a) FROM cte
-> WHERE LENGTH(cte.a) < 20
-> LIMIT 3 OFFSET 2
-> )
-> SELECT * FROM cte;
+-------+
| a |
+-------+
| xxx |
| xxxx |
| xxxxx |
+-------+
3 rows in set (0.00 sec)

mysql>

mysql> WITH RECURSIVE cte AS (
-> SELECT a,b,c FROM (VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9)) AS t(a,b,c)
-> LIMIT 1 OFFSET 2
-> )
-> select * from cte;
+---+---+---+
| a | b | c |
+---+---+---+
| 7 | 8 | 9 |
+---+---+---+
1 row in set (0.00 sec)
59
FUNCTION8.0.19Deprecatesys.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.mysql> select FORMAT_PICO_TIME(188732396662000),FORMAT_BYTES(512);
+-----------------------------------+-------------------+
| FORMAT_PICO_TIME(188732396662000) | FORMAT_BYTES(512) |
+-----------------------------------+-------------------+
| 3.15 min | 512 bytes |
+-----------------------------------+-------------------+
1 row in set (0.00 sec)
60
Shell8.0.19Newly AddFrom 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.mysqlx_compression_algorithms system variable to include only the ones you permit.
61
PARTITION8.0.19CHANGEDHistorically, 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:		mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES
E FILE_NAME LIKE -> WHERE FILE_NAME LIKE '%#p#%' OR FILE_NAME LIKE '%#sp#%';
+-----------------------------+
| FILE_NAME |
+-----------------------------+
| ./POC/T_RANGE#p#p202112.ibd |
| ./POC/T_RANGE#p#p202201.ibd |
| ./POC/T_RANGE#p#p202202.ibd |
| ./POC/T_RANGE#p#p202203.ibd |
| ./POC/T_RANGE#p#p999999.ibd |
+-----------------------------+
5 rows in set (0.01 sec)
62
STATEMENT8.0.20DeprecateThe 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.INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

新しい行とカラムを参照するための VALUES() の使用は、MySQL 8.0.20 以降非推奨になり、将来のバージョンの MySQL で削除される予定です。 かわりに、このセクションの次のいくつかの段落で説明するように、行およびカラムのエイリアスを使用します。

https://dev.mysql.com/doc/refman/8.0/ja/insert-on-duplicate.html
63
HINT8.0.20Newly AddThis 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:

mysql> SELECT id,name FROM members USE INDEX FOR ORDER BY (idx_uq_members_name) ORDER BY name;
+----+------+
| id | name |
+----+------+
| 4 | Mr.S |
| 1 | Mr.T |
| 2 | Mr.U |
| 3 | Mr.V |
| 5 | Mr.Z |
+----+------+
5 rows in set (0.00 sec)

mysql> explain SELECT id,name FROM members USE INDEX FOR ORDER BY (idx_uq_members_name) ORDER BY name;
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | members | NULL | index | NULL | idx_uq_members_name | 1022 | NULL | 4 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT /*+ ORDER_INDEX(members idx_uq_members_name) */ id,name FROM members ORDER BY name;
+----+------+
| id | name |
+----+------+
| 4 | Mr.S |
| 1 | Mr.T |
| 2 | Mr.U |
| 3 | Mr.V |
| 5 | Mr.Z |
+----+------+
5 rows in set (0.00 sec)

mysql> explain SELECT /*+ ORDER_INDEX(members idx_uq_members_name) */ id,name FROM members ORDER BY name;
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | members | NULL | index | NULL | idx_uq_members_name | 1022 | NULL | 4 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level
64
STATEMENT8.0.20Newly AddPreviously, 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:		SELECT * INTO OUTFILE 'file_name' FROM table_name;
SELECT * FROM table_name INTO OUTFILE 'file_name' FOR UPDATE;
SELECT * FROM table_name FOR UPDATE INTO OUTFILE 'file_name';

mysql> select * INTO OUTFILE '/var/lib/mysql-files/T1-0.sql' from T1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from T1 INTO OUTFILE '/var/lib/mysql-files/T1-1.sql';
Query OK, 1 row affected (0.00 sec)

mysql> select * from T1 INTO OUTFILE '/var/lib/mysql-files/T1-2.sql' FOR UPDATE;
Query OK, 1 row affected, 1 warning (0.00 sec)
65
InnoDB8.0.20Newly AddA 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.		mysql> select * from INFORMATION_SCHEMA.INNODB_TRX;
Empty set (0.01 sec)

mysql> desc INFORMATION_SCHEMA.INNODB_TRX;
+----------------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-----------------+------+-----+---------+-------+
| trx_id | bigint unsigned | NO | | | |
| trx_state | varchar(13) | NO | | | |
| trx_started | datetime | NO | | | |
| trx_requested_lock_id | varchar(105) | YES | | | |
| trx_wait_started | datetime | YES | | | |
| trx_weight | bigint unsigned | NO | | | |
| trx_mysql_thread_id | bigint unsigned | NO | | | |
| trx_query | varchar(1024) | YES | | | |
| trx_operation_state | varchar(64) | YES | | | |
| trx_tables_in_use | bigint unsigned | NO | | | |
| trx_tables_locked | bigint unsigned | NO | | | |
| trx_lock_structs | bigint unsigned | NO | | | |
| trx_lock_memory_bytes | bigint unsigned | NO | | | |
| trx_rows_locked | bigint unsigned | NO | | | |
| trx_rows_modified | bigint unsigned | NO | | | |
| trx_concurrency_tickets | bigint unsigned | NO | | | |
| trx_isolation_level | varchar(16) | NO | | | |
| trx_unique_checks | int | NO | | | |
| trx_foreign_key_checks | int | NO | | | |
| trx_last_foreign_key_error | varchar(256) | YES | | | |
| trx_adaptive_hash_latched | int | NO | | | |
| trx_adaptive_hash_timeout | bigint unsigned | NO | | | |
| trx_is_read_only | int | NO | | | |
| trx_autocommit_non_locking | int | NO | | | |
| trx_schedule_weight | bigint unsigned | YES | | | |
+----------------------------+-----------------+------+-----+---------+-------+
66
InnoDB8.0.20CHANGEDInnoDB: 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.		MySQL 8.0.20 より前は、二重書込みバッファ記憶域は InnoDB システムテーブルスペースにありました。 MySQL 8.0.20 では、二重書込みバッファ記憶域は二重書込みファイルにあります。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-doublewrite-buffer.html
67
AUTH8.0.21Newly AddYou 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.		mysql> select user,host,User_attributes from mysql.user where User_attributes is not null;
+-------------------+-----------+----------------------------------------------------------------------------------+
| user | host | User_attributes |
+-------------------+-----------+----------------------------------------------------------------------------------+
| admin | % | {"metadata": {"description": "Admin Account for WSL on docker"}} |
| non-official-user | % | {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 1}} |
| bill | localhost | {"metadata": {"comment": "This is Bill's user account"}} |
+-------------------+-----------+----------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
68
AUTH8.0.21Newly AddThere 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.		mysql> select * from performance_schema.tls_channel_status;
+-------------+--------------------------------+--------------------------+
| CHANNEL | PROPERTY | VALUE |
+-------------+--------------------------------+--------------------------+
| mysql_main | Enabled | Yes |
| mysql_main | Ssl_accept_renegotiates | 0 |
| mysql_main | Ssl_accepts | 25 |
| mysql_main | Ssl_callback_cache_hits | 0 |
| mysql_main | Ssl_client_connects | 0 |
| mysql_main | Ssl_connect_renegotiates | 0 |
| mysql_main | Ssl_ctx_verify_depth | -1 |
| mysql_main | Ssl_ctx_verify_mode | 5 |
| mysql_main | Current_tls_ca | ca.pem |
| mysql_main | Current_tls_capath | |
| mysql_main | Current_tls_cert | server-cert.pem |
| mysql_main | Current_tls_cipher | |
| mysql_main | Current_tls_ciphersuites | |
| mysql_main | Current_tls_crl | |
| mysql_main | Current_tls_crlpath | |
| mysql_main | Current_tls_key | server-key.pem |
| mysql_main | Current_tls_version | TLSv1.2,TLSv1.3 |
| mysql_main | Ssl_finished_accepts | 25 |
| mysql_main | Ssl_finished_connects | 0 |
| mysql_main | Ssl_server_not_after | Nov 1 07:23:33 2031 GMT |
| mysql_main | Ssl_server_not_before | Nov 3 07:23:33 2021 GMT |
| mysql_main | Ssl_session_cache_hits | 0 |
| mysql_main | Ssl_session_cache_misses | 0 |
| mysql_main | Ssl_session_cache_mode | SERVER |
| mysql_main | Ssl_session_cache_overflows | 0 |
| mysql_main | Ssl_session_cache_size | 128 |
| mysql_main | Ssl_session_cache_timeouts | 0 |
| mysql_main | Ssl_used_session_cache_entries | 0 |
| mysql_main | Ssl_session_cache_timeout | 300 |
| mysql_admin | Enabled | No |
| mysql_admin | Ssl_accept_renegotiates | 0 |
| mysql_admin | Ssl_accepts | 0 |
| mysql_admin | Ssl_callback_cache_hits | 0 |
| mysql_admin | Ssl_client_connects | 0 |
| mysql_admin | Ssl_connect_renegotiates | 0 |
| mysql_admin | Ssl_ctx_verify_depth | 0 |
| mysql_admin | Ssl_ctx_verify_mode | 0 |
| mysql_admin | Current_tls_ca | |
| mysql_admin | Current_tls_capath | |
| mysql_admin | Current_tls_cert | |
| mysql_admin | Current_tls_cipher | |
| mysql_admin | Current_tls_ciphersuites | |
| mysql_admin | Current_tls_crl | |
| mysql_admin | Current_tls_crlpath | |
| mysql_admin | Current_tls_key | |
| mysql_admin | Current_tls_version | TLSv1.2,TLSv1.3 |
| mysql_admin | Ssl_finished_accepts | 0 |
| mysql_admin | Ssl_finished_connects | 0 |
| mysql_admin | Ssl_server_not_after | |
| mysql_admin | Ssl_server_not_before | |
| mysql_admin | Ssl_session_cache_hits | 0 |
| mysql_admin | Ssl_session_cache_misses | 0 |
| mysql_admin | Ssl_session_cache_mode | NONE |
| mysql_admin | Ssl_session_cache_overflows | 0 |
| mysql_admin | Ssl_session_cache_size | 0 |
| mysql_admin | Ssl_session_cache_timeouts | 0 |
| mysql_admin | Ssl_used_session_cache_entries | 0 |
| mysql_admin | Ssl_session_cache_timeout | 0 |
+-------------+--------------------------------+--------------------------+
58 rows in set (0.04 sec)
69
PARTITION8.0.21DeprecateWhen 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. ■ ﾊﾟｰﾃｨｼｮﾝ作成のvalidationの様なので別途確認

Partitioning: Columns with index prefixes are not supported as part of a table's partitioning key; previously such columns were simply omitted by the server when referenced in creating, altering, or upgrading a table that was partitioned by key, with no indication that this omission had taken place, except when the proposed partitioning function used only columns with prefixes, in which case the statement failed with an error message that did not identify the actual source of the problem.

mysql> show create table T_RANGE\G
*************************** 1. row ***************************
Table: T_RANGE
Create Table: CREATE TABLE `T_RANGE` (
`id` int NOT NULL,
`note` varchar(1024) NOT NULL DEFAULT '',
`updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (unix_timestamp(`updated_date`))
(PARTITION p202112 VALUES LESS THAN (1638316800) ENGINE = InnoDB,
PARTITION p202201 VALUES LESS THAN (1640995200) ENGINE = InnoDB,
PARTITION p202202 VALUES LESS THAN (1643673600) ENGINE = InnoDB,
PARTITION p202203 VALUES LESS THAN (1646092800) ENGINE = InnoDB,
PARTITION p999999 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table T_RANGE add index idx_note(note(10));
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
70
JSON8.0.21Newly AddAdded 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().		mysql> CREATE DATABASE `POC8021` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ ;
Query OK, 1 row affected (0.02 sec)

mysql> use POC8021
Database changed

mysql> CREATE TABLE inventory(
items JSON,
INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),
INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
);

Query OK, 0 rows affected (0.07 sec)

mysql> insert into inventory(items) values('{"name": "hat", "price": "22.95", "quantity": "17"}');
Query OK, 1 row affected (0.03 sec)

mysql> select * from inventory;
+-----------------------------------------------------+
| items |
+-----------------------------------------------------+
| {"name": "hat", "price": "22.95", "quantity": "17"} |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT items->"$.price" FROM inventory
WHERE JSON_VALUE -> WHERE JSON_VALUE(items, '$.name' RETURNING CHAR(50)) = "hat";
+------------------+
| items->"$.price" |
+------------------+
| "22.95" |
+------------------+
1 row in set (0.01 sec)

mysql> explain SELECT items->"$.price" FROM inventory WHERE JSON_VALUE(items, '$.name' RETURNING CHAR(50)) = "hat";
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | inventory | NULL | ref | i1 | i1 | 203 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
71
OPTIMIZER8.0.21Newly AddMySQL 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.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.00 sec)

mysql>
72
OPTIMIZER8.0.21Newly AddMulti-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.		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.00 sec)

mysql>
73
OPTIMIZER8.0.21Newly AddPrior 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.		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.00 sec)

mysql>
74
Replication8.0.21Newly Addvariable 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.CHECK SUM (Group Replication)
75
REDO8.0.21Newly AddInnoDB: 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.		mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.03 sec)

mysql>
76
InnoDB8.0.21Newly AddAt 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.mysql> show variables like 'innodb_validate_tablespace_paths';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| innodb_validate_tablespace_paths | ON |
+----------------------------------+-------+
1 row in set (0.04 sec)
77
TRUNCATE8.0.21ChangeTruncating 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.mysql> show variables like 'innodb_directories';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_directories | |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>
78
Docker8.0.21Newly AddMySQL 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)mysql> restart;
ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
79
EXPLAIN8.0.21ChangeEXPLAIN ANALYZE now supports the FORMAT option. Currently, TREE is the only supported format. (Bug #30315224)mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT a, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP\G
*************************** 1. row ***************************
EXPLAIN: -> Group aggregate with rollup: count(0) (cost=1.05 rows=4) (actual time=0.056..0.057 rows=5 loops=1)
-> Sort: a (cost=0.65 rows=4) (actual time=0.051..0.052 rows=4 loops=1)
-> Table scan on t1 (cost=0.65 rows=4) (actual time=0.029..0.038 rows=4 loops=1)

1 row in set (0.00 sec)

mysql>
80
Replication8.0.21ChangeOn 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)mysql> create table T3 select * from t1;
Query OK, 4 rows affected (0.16 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql>
81
Replication8.0.22ChangeDeprecation 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.		mysql> show variables like 'group%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| group_concat_max_len | 1024 |
| group_replication_consistency | EVENTUAL |
+-------------------------------+----------+
2 rows in set (0.02 sec)

mysql>
82
Replication8.0.22ChangeFrom 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:Instead of START SLAVE use START REPLICA
Instead of STOP SLAVE use STOP REPLICA
Instead of SHOW SLAVE STATUS use SHOW REPLICA STATUS
Instead of SHOW SLAVE HOSTS use SHOW REPLICAS
Instead of RESET SLAVE use RESET REPLICA
83
Memcached8.0.22DeprecateThe InnoDB memcached plugin is deprecated and support for it will be removed in a future MySQL version.memcached
84
INFORMATION_SCHEMA8.0.22DeprecateThe 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.mysql> select * from INFORMATION_SCHEMA.TABLESPACES;
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.TABLESPACES' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
85
TEMPORARY TABLE8.0.22ChangeThe filesort algorithm now supports sorting a join on multiple tables, and not just a single table. (Bug #31310238, Bug #31559978, Bug #31563876)mysql> explain select gid,name,category,type,geohash from tourism_and_busstop order by geohash;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| 1 | SIMPLE | tourism_and_busstop | NULL | ALL | NULL | NULL | NULL | NULL | 29950 | 100.00 | Using filesort |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
86
DUMP8.0.22AddAdded 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).mysql> show variables like 'select_into_%';
+-----------------------------+--------+
| Variable_name | Value |
+-----------------------------+--------+
| select_into_buffer_size | 131072 |
| select_into_disk_sync | OFF |
| select_into_disk_sync_delay | 0 |
+-----------------------------+--------+
3 rows in set (0.01 sec)
87
OPTIMIZER8.0.22AddTo 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.mysql> SELECT @@optimizer_switch LIKE '%derived_condition_pushdown=off%';
+------------------------------------------------------------+
| @@optimizer_switch LIKE '%derived_condition_pushdown=off%' |
+------------------------------------------------------------+
| 0 |
+------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT @@optimizer_switch LIKE '%derived_condition_pushdown=on%';
+-----------------------------------------------------------+
| @@optimizer_switch LIKE '%derived_condition_pushdown=on%' |
+-----------------------------------------------------------+
| 1 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
88
Performance Schema8.0.22AddAn 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.		mysql> show processlist;
+----+-----------------+------------------+---------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+---------+---------+--------+------------------------+------------------+
| 7 | event_scheduler | localhost | NULL | Daemon | 127500 | Waiting on empty queue | NULL |
| 35 | root | 172.18.0.1:51622 | POC8021 | Query | 0 | init | show processlist |
+----+-----------------+------------------+---------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)

mysql> select * from performance_schema.processlist;
+----+-----------------+------------------+---------+---------+--------+------------------------+----------------------------------------------+------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | EXECUTION_ENGINE |
+----+-----------------+------------------+---------+---------+--------+------------------------+----------------------------------------------+------------------+
| 7 | event_scheduler | localhost | NULL | Daemon | 127514 | Waiting on empty queue | NULL | PRIMARY |
| 35 | root | 172.18.0.1:51622 | POC8021 | Query | 0 | executing | select * from performance_schema.processlist | PRIMARY |
+----+-----------------+------------------+---------+---------+--------+------------------------+----------------------------------------------+------------------+
2 rows in set (0.03 sec)

*************************** 1. row ***************************
thd_id: 13
conn_id: NULL
user: innodb/page_flush_coordinator_thread
db: NULL
command: NULL
state: NULL
time: 127593
current_statement: NULL
execution_engine: PRIMARY
statement_latency: NULL
progress: NULL
lock_latency: NULL
cpu_latency: NULL
rows_examined: NULL
rows_sent: NULL
rows_affected: NULL
tmp_tables: NULL
tmp_disk_tables: NULL
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 1.93 KiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: NULL
program_name: NULL
1 row in set (0.04 sec)

mysql>

mysql> show variables like 'performance_schema_show_processlist';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| performance_schema_show_processlist | OFF |
+-------------------------------------+-------+
1 row in set (0.01 sec)

mysql>
89
Logging8.0.22AddAn 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.mysql> select * from performance_schema.error_log limit 2 \G
*************************** 1. row ***************************
LOGGED: 2022-10-27 16:09:42.730414
THREAD_ID: 0
PRIO: Warning
ERROR_CODE: MY-011068
SUBSYSTEM: Server
DATA: The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
*************************** 2. row ***************************
LOGGED: 2022-10-27 16:09:42.730423
THREAD_ID: 0
PRIO: Warning
ERROR_CODE: MY-010918
SUBSYSTEM: Server
DATA: 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2 rows in set (0.01 sec)

mysql>
90
CAST8.0.22CHANGEIt 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.		mysql> select CAST("1944.5" AS YEAR);
+------------------------+
| CAST("1944.5" AS YEAR) |
+------------------------+
| 1944 |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select CAST(now() AS YEAR);
+---------------------+
| CAST(now() AS YEAR) |
+---------------------+
| 2022 |
+---------------------+
1 row in set (0.00 sec)

mysql> select CAST("13:47" AS YEAR);
+-----------------------+
| CAST("13:47" AS YEAR) |
+-----------------------+
| 2013 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select CAST(TIME "13:47" AS YEAR);
+----------------------------+
| CAST(TIME "13:47" AS YEAR) |
+----------------------------+
| 2022 |
+----------------------------+
1 row in set (0.00 sec)

mysql>
91
CAST8.0.22CHANGEWhen 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.mysql> TABLE ex;
+---------------------+
| ts |
+---------------------+
| 2022-10-29 03:54:07 |
| 2020-08-01 05:44:30 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT ts, CAST(ts AT TIME ZONE 'UTC' AS DATETIME) AS ut FROM ex;
+---------------------+---------------------+
| ts | ut |
+---------------------+---------------------+
| 2022-10-29 03:54:07 | 2022-10-29 03:54:07 |
| 2020-08-01 05:44:30 | 2020-08-01 05:44:30 |
+---------------------+---------------------+
2 rows in set (0.01 sec)
92
InnoDB8.0.22Newly AddThe 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.mysql> show variables like 'innodb_extend_and_initialize';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_extend_and_initialize | ON |
+------------------------------+-------+
1 row in set (0.01 sec)

mysql>
93
Auth8.0.23Newly AddGranting 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.		mysql> FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name | cost_value | last_update | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost | NULL | 2021-11-03 07:23:34 | NULL | 20 |
| disk_temptable_row_cost | NULL | 2021-11-03 07:23:34 | NULL | 0.5 |
| key_compare_cost | NULL | 2021-11-03 07:23:34 | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 2021-11-03 07:23:34 | NULL | 1 |
| memory_temptable_row_cost | NULL | 2021-11-03 07:23:34 | NULL | 0.1 |
| row_evaluate_cost | NULL | 2021-11-03 07:23:34 | NULL | 0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.01 sec)

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name | cost_value | last_update | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default | 0 | io_block_read_cost | NULL | 2021-11-03 07:23:34 | NULL | 1 |
| default | 0 | memory_block_read_cost | NULL | 2021-11-03 07:23:34 | NULL | 0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>
94
Replication8.0.23ChangeFrom 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.		root@localhost [mysql]> show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: xxx.xxx.xxx.xxx
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: ip-xxx.xxx.xxx.xxx.001189
Read_Source_Log_Pos: 25307285
Relay_Log_File: xxx.xxx.xxx.xxx-relay-bin.000285
Relay_Log_Pos: 25306702
Relay_Source_Log_File: ip-xxx.xxx.xxx.xxx-bin.001189
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: APP.sessions
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 25306468
Relay_Log_Space: 25307835
Until_Condition: None
95
Replication8.0.23DeprecateThe 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.mysql> show variables like 'master_info_repository';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| master_info_repository | TABLE |
+------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'relay_log_info_repository';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| relay_log_info_repository | TABLE |
+---------------------------+-------+
1 row in set (0.01 sec)
96
Auth8.0.23DeprecateFlushing 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.		mysql> FLUSH HOSTS;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'FLUSH HOSTS' is deprecated and will be removed in a future release. Please use TRUNCATE TABLE performance_schema.host_cache instead |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc performance_schema.host_cache;
+--------------------------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------------------+------------------+------+-----+---------+-------+
| IP | varchar(64) | NO | PRI | NULL | |
| HOST | varchar(255) | YES | MUL | NULL | |
| HOST_VALIDATED | enum('YES','NO') | NO | | NULL | |
| SUM_CONNECT_ERRORS | bigint | NO | | NULL | |
| COUNT_HOST_BLOCKED_ERRORS | bigint | NO | | NULL | |
| COUNT_NAMEINFO_TRANSIENT_ERRORS | bigint | NO | | NULL | |
| COUNT_NAMEINFO_PERMANENT_ERRORS | bigint | NO | | NULL | |
| COUNT_FORMAT_ERRORS | bigint | NO | | NULL | |
| COUNT_ADDRINFO_TRANSIENT_ERRORS | bigint | NO | | NULL | |
| COUNT_ADDRINFO_PERMANENT_ERRORS | bigint | NO | | NULL | |
| COUNT_FCRDNS_ERRORS | bigint | NO | | NULL | |
| COUNT_HOST_ACL_ERRORS | bigint | NO | | NULL | |
| COUNT_NO_AUTH_PLUGIN_ERRORS | bigint | NO | | NULL | |
| COUNT_AUTH_PLUGIN_ERRORS | bigint | NO | | NULL | |
| COUNT_HANDSHAKE_ERRORS | bigint | NO | | NULL | |
| COUNT_PROXY_USER_ERRORS | bigint | NO | | NULL | |
| COUNT_PROXY_USER_ACL_ERRORS | bigint | NO | | NULL | |
| COUNT_AUTHENTICATION_ERRORS | bigint | NO | | NULL | |
| COUNT_SSL_ERRORS | bigint | NO | | NULL | |
| COUNT_MAX_USER_CONNECTIONS_ERRORS | bigint | NO | | NULL | |
| COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS | bigint | NO | | NULL | |
| COUNT_DEFAULT_DATABASE_ERRORS | bigint | NO | | NULL | |
| COUNT_INIT_CONNECT_ERRORS | bigint | NO | | NULL | |
| COUNT_LOCAL_ERRORS | bigint | NO | | NULL | |
| COUNT_UNKNOWN_ERRORS | bigint | NO | | NULL | |
| FIRST_SEEN | timestamp | NO | | NULL | |
| LAST_SEEN | timestamp | NO | | NULL | |
| FIRST_ERROR_SEEN | timestamp | YES | | NULL | |
| LAST_ERROR_SEEN | timestamp | YES | | NULL | |
+--------------------------------------------+------------------+------+-----+---------+-------+
29 rows in set (0.01 sec)
97
Performance8.0.23ChangeFunctionality 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)		mysql> show variables like '%hash%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
+----------------------------------+-------+
2 rows in set (0.00 sec)
98
InnoDB8.0.23ChangeInnoDB: 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.mysql> use POC8023
Database changed
mysql> CREATE TABLE t1 (c1 INT) AUTOEXTEND_SIZE = 4M;
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER TABLE t1 AUTOEXTEND_SIZE = 8M;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TABLESPACE ts1 AUTOEXTEND_SIZE = 4M;
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLESPACE ts1 AUTOEXTEND_SIZE = 8M;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int DEFAULT NULL
) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

https://dev.mysql.com/doc/refman/8.0/ja/innodb-tablespace-autoextend-size.html
99
Replication8.0.23Newly AddFor 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)		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
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| slave_allow_batching | ON |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
| slave_pending_jobs_size_max | 134217728 |
| slave_preserve_commit_order | ON |
| slave_rows_search_algorithms | INDEX_SCAN,HASH_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
+------------------------------+----------------------+
17 rows in set (0.00 sec)

https://bugs.mysql.com/bug.php?id=107574
https://dev.mysql.com/worklog/task/?id=13574
100
Performance8.0.23ChangeInnoDB: 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)		root@localhost [world]> show variables like '%adaptive%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
+----------------------------------+--------+