[go: up one dir, main page]
More Web Proxy on the site http://driver.im/

GA

ラベル 8.0 の投稿を表示しています。 すべての投稿を表示
ラベル 8.0 の投稿を表示しています。 すべての投稿を表示

2024/11/15

アカウント名を間違った時にも java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed が出る

TL;DR

  • caching_sha2_password で作られたアカウントに初回接続する時は useSSL=true または allowPublicKeyRetrieval=trueが必要

  • にもかかわらず、 default_authentication_plugin=mysql_native_password を指定していて、アカウントにも mysql_native_password を指定しているはずなのに Public Key Retrieval is not allowed のエラーが出たら、接続しようとしているアカウントを間違えているだけかもしれない

  • allowPublicKeyRetrieval=trueは悪意を持ったMySQL Serverに接続すると何を食わされるかわからないけど、自分たちが構築したやつならtrue決め打ちでも良いんじゃないか（通信は平文のままなのでオーバーヘッドはほとんどない）

JavaのコードはChatGPTに生成してもらったものをちょっと編集しただけ。ユーザー名、パスワード、useSSL、allowPublicKeyRetrievalを引数に取ってMySQLに接続するだけ。

MySQL Serverは8.0.40, Connector/Jは9.1.0。

MySQLの環境はこう。パスワードはどっちも “a” の1文字なので、引数の2つ目が “a” の時は正しいパスワードで “b” の時は間違ったパスワード。


mysql80 16> SELECT @@default_authentication_plugin;

+---------------------------------+

| @@default_authentication_plugin |

+---------------------------------+

| mysql_native_password           |

+---------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql80 16> SELECT user, host, plugin FROM mysql.user WHERE host <> 'localhost';
+------------------+------+-----------------------+
| user             | host | plugin                |
+------------------+------+-----------------------+
| caching_password | %    | caching_sha2_password |
| native_password  | %    | mysql_native_password |
+------------------+------+-----------------------+
2 rows in set (0.00 sec)
1. mysql_native_passwordの実在するユーザー + 正しいパスワード

当然useSSL, allowPublicKeyRetrievalの値に関わらず接続に成功する。

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password a false false
Connected to the database successfully!

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password a true false
Connected to the database successfully!

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password a false true
Connected to the database successfully!

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password a true true
Connected to the database successfully!
2. mysql_native_passwordの実在するユーザー + 間違ったパスワード

当然全部 Access denied for user ? で失敗する。

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password b false false
java.sql.SQLException: Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password b true false
java.sql.SQLException: Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password b false true
java.sql.SQLException: Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample native_password b true true
java.sql.SQLException: Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

..
3. caching_sha2_passwordの実在するユーザー + 正しいパスワード

useSSL=false, allowPublicKeyRetrieval=falseの場合はエラーになる。
ただし、一度でもログインに成功したらその後はfalse, falseでも成功するようになる。

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password a false false
java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password a true false
Connected to the database successfully!

$ mysql80 -e "FLUSH PRIVILEGES"

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password a false true
Connected to the database successfully!

$ mysql80 -e "FLUSH PRIVILEGES"

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password a true true
Connected to the database successfully!

### false, falseでも↑の後にFLUSH PRIVILEGESでキャッシュをリセットしないと結果が違う
$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password a false false
Connected to the database successfully!

caching_sha2_password のキャッシュをリセットして再び Public Key Retrieval is not allowed を出したい場合は FLUSH PRIVILEGES する。

4. caching_sha2_passwordの実在するユーザー + 間違ったパスワード

当然全部転けるが、false, falseの場合のエラーは Public Key Retrieval is not allowed .

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password b false false
java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password b true false
java.sql.SQLException: Access denied for user 'caching_password'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password b false true
java.sql.SQLException: Access denied for user 'caching_password'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password b true true
java.sql.SQLException: Access denied for user 'caching_password'@'127.0.0.1' (using password: YES)

..

先に一度正しいパスワードで接続してキャッシュを作ってもエラーは相変わらず

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password a true true
Connected to the database successfully!

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample caching_password b false false
java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

..
5. 存在しないアカウントを指定する

存在しないアカウントを指定すると default_authentication_plugin の値に関わらず caching_sha2_password でパスワードを間違った時と同じ組み合わせでエラーになるぽい。

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample invalid_user a false false
java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample invalid_user a true false
java.sql.SQLException: Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample invalid_user a false true
java.sql.SQLException: Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

..

$ java -cp .:/usr/share/java/mysql-connector-java.jar MySQLConnectionExample invalid_user a true true
java.sql.SQLException: Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

..

おまけにmysqlコマンドラインクライアント。バージョンは8.4.2。
すぐquitしてるからエラーが出なければ接続に成功して終わってる。Warningは無視する。
存在しないアカウントを指定した時の動作がConnector/Jとは違う。

$ mysql -h127.0.0.1 -P64080 -unative_password -pa --ssl-mode=DISABLED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql -h127.0.0.1 -P64080 -unative_password -pa --ssl-mode=REQUIRED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql -h127.0.0.1 -P64080 -unative_password -pa --ssl-mode=DISABLED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql -h127.0.0.1 -P64080 -unative_password -pa --ssl-mode=REQUIRED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql80 -e "FLUSH PRIVILEGES"

$ mysql -h127.0.0.1 -P64080 -unative_password -pb --ssl-mode=DISABLED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -unative_password -pb --ssl-mode=REQUIRED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -unative_password -pb --ssl-mode=DISABLED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -unative_password -pb --ssl-mode=REQUIRED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'native_password'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -ucaching_password -pa --ssl-mode=DISABLED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

$ mysql -h127.0.0.1 -P64080 -ucaching_password -pa --ssl-mode=REQUIRED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql80 -e "FLUSH PRIVILEGES"

$ mysql -h127.0.0.1 -P64080 -ucaching_password -pa --ssl-mode=DISABLED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql80 -e "FLUSH PRIVILEGES"

$ mysql -h127.0.0.1 -P64080 -ucaching_password -pa --ssl-mode=REQUIRED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.

$ mysql80 -e "FLUSH PRIVILEGES"

$ mysql -h127.0.0.1 -P64080 -uinvalid_user -pa --ssl-mode=DISABLED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -uinvalid_user -pa --ssl-mode=REQUIRED --skip-get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -uinvalid_user -pa --ssl-mode=DISABLED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

$ mysql -h127.0.0.1 -P64080 -uinvalid_user -pa --ssl-mode=REQUIRED --get-server-public-key -e quit
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

これ、なんか CHANGE REPLICATION SOURCE TO でもアカウント間違った時に Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. って怒られた気がするんだけど気のせいだったかな…（再現方法がわかっていない…）


【2024/11/15 19:00】

余談。MySQLのエラーログにはこんな風に出る。

2024-11-15T18:26:29.999904+09:00 25 [Note] [MY-010914] [Server] Got an error reading communication packets         ＜-- 間違ったパスワードで Public Key Retrieval is not allowed 

2024-11-15T18:26:45.095375+09:00 26 [Note] [MY-010926] [Server] Access denied for user 'caching_password'@'127.0.0.1' (using password: YES)

2024-11-15T18:26:58.924712+09:00 27 [Note] [MY-010926] [Server] Access denied for user 'caching_password'@'127.0.0.1' (using password: YES)

2024-11-15T18:27:14.245506+09:00 28 [Note] [MY-010926] [Server] Access denied for user 'caching_password'@'127.0.0.1' (using password: YES)

..

2024-11-15T18:34:32.642543+09:00 39 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-11-15T18:34:32.652304+09:00 39 [Note] [MY-010914] [Server] Got an error reading communication packets

## ↑ 存在しないアカウントで Public Key Retrieval is not allowed 

2024-11-15T18:34:46.348753+09:00 40 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-11-15T18:34:46.350837+09:00 40 [Note] [MY-010926] [Server] Access denied for user 'invalid_user'@'127.0.0.1' (using password: YES)

## ↑存在しないアカウントでAcces denied


存在しないアカウントを指定した時はConnector/J側で他のプラグインもネゴシエーションしようとしているぽい。
投稿者 時刻: 0 件のコメント :
ラベル: ,

2024/09/17

Percona XtraBackup 8.0.23 vs MySQL 8.0でバイナリログがパージされずに残り続ける

TL;DR

  • XtraBackupは8.0.23で --lock-ddl がデフォルトでONになった

  • xtrabackup --lock-ddl は LOCK INSTANCE FOR BACKUP を呼び出す

  • xtrabackup --backup の最中に FLUSH NO_WRITE_TO_BINLOG BINARY LOGS が実行されるが、 LOCK INSTANCE FOR BACKUP の最中にbinlogがローテーションしてもパージが走らない。

    • よって、バックアップ間隔の間に max_binlog_size 未満のバイナリログしか吐かない = スイッチされるのがxtrabackupのタイミングだけのノードはバイナリログがパージされずに残り続けるように見える

    • max_binlog_size を小さくしてxb以外のタイミングでもローテーションが走るようにしてやればOK

    • それか、バックアップスクリプトの中ででもxbが終了した後に FLUSH BINARY LOGS を呼んでやればOK

  • 前提知識として、「バイナリログのパージはバイナリログがスイッチした時にだけ `binlog_expire_logs_seconds` が評価される

どういうことかというと、


mysql80 8> SET GLOBAL binlog_expire_logs_seconds = 1;

Query OK, 0 rows affected (0.13 sec)

こうやってバイナリログのパージを1秒にしてやっても

$ xtrabackup -S /usr/mysql/8.0.39/data/mysql.sock -uroot --stream=xbstream --backup > /dev/null

$ ll bin.*
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:42 bin.000339
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:43 bin.000340
-rw-r----- 1 yoku0825 yoku0825 197 Sep 17 15:43 bin.000341
-rw-r----- 1 yoku0825 yoku0825  39 Sep 17 15:43 bin.index

バイナリログが消えていないし

$ xtrabackup -S /usr/mysql/8.0.39/data/mysql.sock -uroot --stream=xbstream --backup > /dev/null

$ ll bin.*
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:42 bin.000339
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:43 bin.000340
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:44 bin.000341
-rw-r----- 1 yoku0825 yoku0825 197 Sep 17 15:44 bin.000342
-rw-r----- 1 yoku0825 yoku0825  52 Sep 17 15:44 bin.index

もう一度やっても消えない。むしろ FLUSH BINARY LOGS で新しいのができるのでファイルの数は増える。

手打ちでも再現できる。

$ mysql80

mysql80 15> LOCK INSTANCE FOR BACKUP;
Query OK, 0 rows affected (0.10 sec)

mysql80 15> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.02 sec)

$ ll bin.*
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:42 bin.000339
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:43 bin.000340
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:44 bin.000341
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:46 bin.000342
-rw-r----- 1 yoku0825 yoku0825 197 Sep 17 15:46 bin.000343
-rw-r----- 1 yoku0825 yoku0825  65 Sep 17 15:46 bin.index

エラーログをよく見るとワーニングが出ているはず。

2024-09-17T15:43:40.409504+09:00 10 [Warning] [MY-014054] [Server] Could not purge binary logs since another session is executing LOCK INSTANCE FOR BACKUP. Wait for that session to release the lock.

LOCK INSTANCE FOR BACKUP の外で FLUSH BINARY LOGS すればすぐに消える。

mysql80 15> UNLOCK INSTANCE;
Query OK, 0 rows affected (0.11 sec)

mysql80 15> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.22 sec)

$ ll bin.*
-rw-r----- 1 yoku0825 yoku0825 238 Sep 17 15:48 bin.000343
-rw-r----- 1 yoku0825 yoku0825 197 Sep 17 15:48 bin.000344
-rw-r----- 1 yoku0825 yoku0825  26 Sep 17 15:48 bin.index

デフォルトのmax_binlog_sizeは1GBなので、ワーストケース（binlogがパージされずに残り続けて容量が食い切られるケース）では、

  • 今朝のバックアップで binlog.000001 からbinlog.000002にスイッチする
  • 今日いちにちでbinlog.000002が800MBまで書き込まれる
  • 明日の朝のバックアップでbinlog.000002がbinlog.000003にスイッチされる。 LOCK INSTANCE FOR BACKUP で保護されているのでbinlog.000001はexpireしていても削除されない
  • 明日いちにちでbinlog.000003が800MBまで（略

となって、binlogがいつまでもパージされずに残り続ける。

という訳で、

  • max_binlog_size を小さくしてxb以外のタイミングでもローテーションが走るようにしてやればOK（binlog.000003がxtrabackup以外のタイミングでスイッチすれば、binlog.000001~000002はパージ対象になる）

  • それか、バックアップスクリプトの中ででもxbが終了した後に FLUSH BINARY LOGS を呼んでやればOK（xbが終了した後にスイッチすれば過去のbinlogはパージ対象になる）

参考までに。

​
投稿者 時刻: 0 件のコメント :
ラベル: ,

2024/08/21

CREATE DATABASE/CREATE TABLEでCHARSETを指定してCOLLATEを指定しない

TL;DR

CREATE DATABASE の時にCHARSETやCOLLATEを明示したりしなかったりする場合(指定するCOLLATEとCHARSETはちゃんと対応している前提で)、

CHARSET指定 COLLATE指定 実際に使われるCOLLATE
していない していない (その時点の)collation_server
していない している COLLATEで指定した値
している していない (その時点の)default_collation_for_utf8mb4
している している COLLATEで指定した値


同じく CREATE TABLE の場合は

CHARSET指定 COLLATE指定 実際に使われるCOLLATE
していない していない (その時点の)スキーマのCOLLATE
していない している COLLATEで指定した値
している していない (その時点の)default_collation_for_utf8mb4
している している COLLATEで指定した値

【2024/08/22 09:49】 更にカラムは

CHARSET指定COLLATE指定実際に使われるCOLLATE
していないしていない(その時点の)テーブルののCOLLATE
していないしているCOLLATEで指定した値
しているしていない(その時点の)default_collation_for_utf8mb4
しているしているCOLLATEで指定した値



というわけで、「そもそもCOLLATEまで完全指定した方が間違いがない」「どうしても省略したいならスキーマのCOLLATEに注意する」「少なくともCHARSETだけ書いてCOLLATEだけ省略すると意図しないことになる可能性がある」のでした。

mysql80 17> SELECT @@session.default_collation_for_utf8mb4, @@session.character_set_server, @@session.collation_server, @@session.collation_connection;
+-----------------------------------------+--------------------------------+----------------------------+--------------------------------+
| @@session.default_collation_for_utf8mb4 | @@session.character_set_server | @@session.collation_server | @@session.collation_connection |
+-----------------------------------------+--------------------------------+----------------------------+--------------------------------+
| utf8mb4_0900_ai_ci                      | utf8mb4                        | utf8mb4_general_ci         | latin1_swedish_ci              |
+-----------------------------------------+--------------------------------+----------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql80 17> CREATE DATABASE no_specified;
Query OK, 1 row affected (0.01 sec)

mysql80 17> SHOW CREATE DATABASE no_specified;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                                                        |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
| no_specified | CREATE DATABASE `no_specified` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 17> CREATE DATABASE only_charset_specified CHARSET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql80 17> SHOW CREATE DATABASE only_charset_specified;
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Database               | Create Database                                                                                                                                  |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| only_charset_specified | CREATE DATABASE `only_charset_specified` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 17> CREATE DATABASE fully_specified CHARSET utf8mb4 COLLATE utf8mb4_0900_bin;
Query OK, 1 row affected (0.01 sec)

mysql80 17> SHOW CREATE DATABASE fully_specified;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Database        | Create Database                                                                                                                         |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| fully_specified | CREATE DATABASE `fully_specified` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 17> use fully_specified
Database changed

mysql80 17> CREATE TABLE no_specified_table_and_column (val varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql80 17> SHOW CREATE TABLE no_specified_table_and_column\G
*************************** 1. row ***************************
       Table: no_specified_table_and_column
Create Table: CREATE TABLE `no_specified_table_and_column` (
  `val` varchar(32) COLLATE utf8mb4_0900_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_table_and_no_specified_column (val varchar(32)) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_table_and_no_specified_column\G
*************************** 1. row ***************************
       Table: specified_table_and_no_specified_column
Create Table: CREATE TABLE `specified_table_and_no_specified_column` (
  `val` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_only_charset_table_and_no_specified_column (val varchar(32)) CHARSET utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_only_charset_table_and_no_specified_column\G
*************************** 1. row ***************************
       Table: specified_only_charset_table_and_no_specified_column
Create Table: CREATE TABLE `specified_only_charset_table_and_no_specified_column` (
  `val` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_table_and_specified_column (val varchar(32) CHARSET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_table_and_specified_column\G
*************************** 1. row ***************************
       Table: specified_table_and_specified_column
Create Table: CREATE TABLE `specified_table_and_specified_column` (
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_table_and_specified_only_charset_column (val varchar(32) CHARSET utf8mb4) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_table_and_specified_only_charset_column\G
*************************** 1. row ***************************
       Table: specified_table_and_specified_only_charset_column
Create Table: CREATE TABLE `specified_table_and_specified_only_charset_column` (
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql80 17> CREATE TABLE specified_only_charset_table_and_specified_only_charset_column (val varchar(32) CHARSET utf8mb4) CHARSET utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql80 17> SHOW CREATE TABLE specified_only_charset_table_and_specified_only_charset_column\G
*************************** 1. row ***************************
       Table: specified_only_charset_table_and_specified_only_charset_column
Create Table: CREATE TABLE `specified_only_charset_table_and_specified_only_charset_column` (
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
​
投稿者 時刻: 0 件のコメント :
ラベル:

2024/03/15

MySQL 8.0からMySQL 8.3までの変更点まとめ

 【2024/03/15 09:19】

現在までの8.0～8.3の新機能/Removal/Deperecatedまとめ。8.4が出たらたぶん足す。

What Is New in .. のページを軸にしているので細かいリリースノートは見てない。8.2で変わったのが8.3にもリストされていたり、8.1で変わったのは8.2にリストされていなかったり、たぶん直前のInnovation Releaseとの差だけ記載しているっぽいのでUNION DISTINCTしてある。

Enterprise限定機能はパス。記載基準は独断と偏見。

VersionFeatureComment
8.0New Data Dictionary(InnoDB)使おうと思っても嫌だと思っても選択肢はない
8.0Atomic DDL(クラッシュリカバリ可能DDL)Good
8.0mysql_upgrade deprecated8.0.16とそれ以降, 特に影響ないはずだけどupgrade処理が走った直後はmysqld再起動推奨(これはmysql_upgrade時代も俺は推奨していた)
8.0Atomic CREATE USER / GRANT(クラッシュリカバリ可能)可もなく不可もなく…
8.0default_authentication_plugin=caching_sha2_password仕様を知らないと開発環境でハマるかも
8.0sha2_password deprecatedConnector/Jとかからつないでると試すだけ試そうとしてエラーログがうるさくなる
8.0ROLENew, ..けど地雷は埋まってる
8.0deprecated SUPER priv以後、色々分割されてる
8.0partial_revokesまあ便利だけどハマった
8.0password_history使ったことない
8.0ALTER INSTANCE RELOAD TLS使ったことない
8.0Support TLS1.38.0.16とそれ以降, ただしリンクされてるOpenSSLのバージョンによる
8.0Remove TLS1, TLS1.18.0.28とそれ以降
8.0Add Resource Group自分でSETしないといけなくて使いにくい
8.0default_table_encryptionGood
8.0auto_incrementの値の永続化5.7までは最後の番号を歯抜けにしておくと再利用されちゃった
8.0innodb_memcachedがmgetサポート誰が喜ぶのか謎
8.0deprecated innodb_memcached↑とこれが一緒にリストされているのが8.0っぽい
8.0innodb_deadlock_detect=OFFで高負荷環境のCPUがマシになることもただしデッドロック検出エラーがなくなるのでinnodb_lock_wait_timeoutで制御が必要
8.0InnoDB Temporary Tableがibtmp1から*.ibtファイルに(8.0.13とそれ以降)これで「あふれたら必ず再起動」ではなくなった
8.0REDOログ, UNDOログ, binlogの暗号化サポートGood
8.0SELECT .. FOR {SHAREUPDATE} SKIP LOCKED 追加Good
8.0パーティション操作のいくつかがオンラインALTER TABLEに対応もともとADDとDROPしかしないのであんまり実感ない
8.0UNDOテーブルスペースの切り出しがデフォルト化innodb_undo_truncationもデフォルトON
8.0innodb_autoinc_lock_mode=2がデフォルト化binlog_format=MIXED以下の場合、旧デフォルトのinnodb_autoinc_lock_mode=1にしないとおかしなことになるかも
8.0innodb_dedicated_server support使ってない…
8.0.frm replaced .SDI files慣れれば見やすい、InnoDBの場合は ibd2sdiコマンドで見る
8.0innodb_directoriesサポート.ibdファイルをdatadirの外に置けるようにはなったけどもうそういうケースってどれだけあるんだろう
8.0Log writer threadが分離よほどCPUが強いマシンでない限り性能が劣化するアレ。innodb_log_writer_threadでOFFにできるのは8.0.22とそれ以降
8.0ALGORITHM=INSTANT, 第1弾(末尾のADDのみ)は8.0.12, 第2弾(どこでもADD + DROP)は8.0.29これをまたいだinplace-updateは鬼門
8.0暗黙のテンポラリテーブルに対するTempTableストレージエンジン結構パフォーマンス問題が大きく取り上げられた。tmptable_max_mmap導入の8.0.23で落ち着いたかと思いきや、8.0.28でtmp_table_sizeもこの動作に影響するように変更されてまたひと悶着
8.0innodb_buffer_pool_in_core_file supportedGood
8.0Parallel Table ScanMySQLがパラレルって言いだすとろくなことがない…
8.0HistgramのサポートMySQLerはこれよりも複合インデックス作りがち
8.0Separated Doublewrite bufferibdata1から切り離された
8.0Transaction algorithm FIFO to CATSあんまり差を感じたことはない
8.0ALTER INSTANCE DISABLE INNODB REDO_LOGインポートはちょっぱや。ENABLEに戻さずにmysqldが異常終了すると起動しなくなる
8.0innodb_validate_tablepace_paths8.0.21とそれ以降で大量に.ibdを持っている環境だと起動が遅くなった。なんか今はパラレル化されたみたいでマシになった
8.0CREATE TABLE .. SELECT ..がGTID環境下でサポートこれができてgtid_mode=ONにできるようになった環境がある。Good
8.0Add innodb_doublewrite=DETECT_ONLY8.0.30とそれ以降
8.0innodb_redo_log_capacity8.0.30とそれ以降。さよならib_logfile
8.0default_character_set latin1 to utf8mb4日本人は困らないけど英語圏の人たちがパフォーマンスに悩まされたらしい
8.0Add JSON_TABLE自分で使うぶんには便利だけど、いざ誰かが使っているのを(パフォーマンス含め)トラブルシュートするのは結構大変
8.0Add INVISIBLE INDEXDROP INDEX前の切り戻しが楽
8.0降順INDEX昇順のままでもパフォーマンスはそんなに変わらない。ORDER BY .. ASC, .. DESCのケースはこれでしか対応できない
8.0Functional Index地味にオプティマイザがちゃんと選んでくれないケースが多発, 個人的にはgenerated column + indexでgenerated columnを指定する方が安定する
8.0全体的なsemijionの改良思ったより賢くなっててびっくり
8.0HASH JOINおとなしくインデックスを貼りましょう
8.0WITH (RECURSIVE) Clause見やすくなるけど別に速いとは言ってない
8.0Window Function速くなるとは言ってない
8.0ICU Regexp support便利になったけど遅くはなってるし地味に非互換
8.0MY-xxxxx in error-logORA-xxxxx 的な感じになった
8.0LOCK INSTANCE FOR BACKUP知らずに使うと不思議な感じになるかも主にxtrabackupでお目にかかる
8.0JSON系関数が増えた。JSON_SCHEMA_VALIDでバリデーションすらできる~それならカラムに切り出した方が
8.0Multi-valued indexes1対多のインデックスが作れる。JSON型専用。鬼門
8.0Clone Pluginいいぞ便利だ
8.0EXPLAIN ANALYZEいまいち情報が足りないと思ってしまうけど慣れてる人には便利らしい
8.0タイムゾーンオフセット記法。 2024-03-15:10:32:00+0900とかUTCで入れてるとなじみがない
8.0SELECT .. INTO ..のINTOが変なところに入るとエラー一応非互換
8.0オプティマイザヒントの拡充便利なのが増えた
8.0Dual password地味に便利
8.0optimizer_switchの拡充賢くなったのはこれが一端
8.0TIMESTAMPのなんちゃって64bit拡張timestampデータ型じゃなくてTIMESTAMP関数の話
8.0expire_logs_days -> binlog_expire_logs_secondsでも日付単位 24 60 * 60で設定しちゃう
8.0GIPK特定のケースで便利だったけどふとauto_incrementを足そうと思った時にぶつかる可能性あり
8.0INTERSECTとEXPECTのサポートついに！！！ (速いくなるとは言ってない)
8.0performance_schema.error_log直近5MBぶんのエラーログがシェルなしでも確認可能
8.0GRANT .. ON d% のワイルドカード非推奨partial_grantsと合わせると8.0時点でも死ぬ
8.0deprecate character_set_client_handshakeutf8系に統一してれば怖くない
8.0deprecated mysql_native_passwordここで非推奨ということはいつか消えるってことだ…
8.0FLOATまたはDOUBLE型でのauto_incrementの非推奨むしろこんなことできることを知っていた人が少ない気がする
8.0int系の桁数指定およびZEROFILLまあ整数型をそういうことに使ってはいけない…
8.0SQL_CALC_FOUND_ROWS deprecatedORDER BY .. LIMITの最適化を殺すやつ。意外と現役は多い気がする
8.0FLUSH HOSTSの非推奨TRUNCATE performance_schema.host_cacheに変えろ殿こと。skip_name_resolve環境ではそもそも関係なし
8.0deprecated relay_log_info_repository = FILE / master_info_repository = FILE既にFILEを使用する必要は全くないはず
8.0depreacted MYSQL_PWD env variablemysqlコマンドラインクライアントのワーニングを黙らせるのに有効だった手順が一つ消えた。 .mylogin.cnf使えってさ
8.0deprecate default_authentication_plugin消えて指定できなくなるとは言ってないけど代わりにauthentication_policyというので指定しろと
8.0replica_parallel_type deprecatedいずれLOGICAL_CLOCKオンリーに
8.0replica_parallel_workers=0 deprecateddeprecatedになったのは=0だけ。変わらずシングルスレッドでやらせたいなら=1
8.0innodb_log_files_in_group, innodb_log_file_size deprecatedinnodb_redo_log_capacityに取って変わられた。お馴染みのオプションなのでちょっと寂しい
8.0予約語が増えたMySQL Shellのアップグレードチェッカー使いましょう。binlog_format=ROWだと「レプリケーションつないでるから更新クエリが通ることは保証される」ということはないです
8.0binlog_format deprecated今後はROWしか選ばせてくれないつもりらしい
8.0mysqlpump deprecatedmysqldumpじゃないよ、mysqlpumpだよ
8.0binlog_transaction_dependency_trackingGroupReplication経験者は記憶にあるかもなアレ。WRITESETがデフォルトになるらしいけどCASCADEのFKと相性が悪いバグがある（直ったのかどうか不明）
8.0InnoDB系のinformation_schemaのテーブル名が変わってる自分でユーティリティーを作っているタイプの人がハマるやつ
8.0GRANT USAGE ON . TO ..でCREATE USERの代わりにするできなくなった
8.0IDENTIFIED BY PASSWORD ‘*..’できなくなった。代替は IDENTIFIED WITH mysql_native_password AS ‘*..’
8.0PASSWORD function removed代替は SELECT CONCAT(‘*’, UPPER(SHA1(UNHEX(SHA1(‘mypass’)))))
8.0Query cache removedさよならクエリキャッシュ
8.0tx_isolation -> transaction_isolationクライアントのバージョンによって死ぬことgああったやつ
8.0log_warnings -> log_error_verbosity5.7でお馴染みの
8.0GROUP BY c1 ASCこの謎記法が消えた
8.0EXPLAIN EXTENDED removedデフォルトがEXTENDED相当になったので記法が消えた。実質困らないけど手癖で打ってエラることが
8.0GIS系の関数の名前がごっそり入れ替わる大変だったらしい
8.0—ssl removed代わりに —ssl-mode
8.0datadirの中の #mysql50# から始まるディレクトリ5.0とそれ以降に変な文字が入ったデータベースをCREATEすると出来上がる
8.0mysql_install_db代わりにmysqld —initialize(-insecure). 5.7で非推奨になったのが削除された
8.0SELECT .. INTO @val 記法の非推奨これからは SET @val := ..を使うらしい
8.1EXPLAIN format=JSON INTO @valNew
8.1mysql —comment made defaultLittle
8.1source_retry_count 86400 -> 10Little
8.1group_replication_set_as_primary vs DDLMiddle?
8.1Extend “version-specific comment”-
8.1More logging during shutdown sequenceNew
8.1SHOW PARSE_TREE statementNew
8.1tls_certificates_enforced_validationNew
8.1Group Replication status variables addedNew
8.1—some-option=NULL is prohibitedLittle
8.2More logging startup/shutdown sequenceNew
8.2optimizer_switch: hash_set_operationsNew
8.2SET_USER_ID is devided to SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINERLittle
8.2EXPLAIN FOR SCHEMANew
8.2START REPLICA UNTIL SQL_AFTER_GTIDS in MultiThreadApplierNew
8.2mysqldump —output-as-versionNew
8.2Remove WAIT_UNTIL_SQL_THREAD_AFTER_GTIDSMiddle
8.2Remove expire_logs_daysLittle
8.3SET gtid_next = ::New
8.3Another EXPLAIN format, SET explain_json_format_version = 2New
8.3Default binlog_transaction_dependency_tracking = WRITESETMiddle
8.3SHOW STATUS LIKE ‘Deprecated_use_i_s_processlist%’New
8.3Incompatible changes in libmysqlclient.soHigh
8.3Remove FLUSH HOSTSLittle
8.3Remove slave_rows_search_algorithms, relay_log_info_file, relay_log_info_repository, master_info_file, master_info_repositoryLittle
8.3Remove group_replication_ip_whitelistLittle
8.3Remove skip_host_cacheLittle
8.3Remove skip_character_set_client_handshakeMiddle?
8.3Unsupport binlog_format=MIXED vs binlog_transaction_dependency_tracking=WRITESETMiddle?
投稿者 時刻: 0 件のコメント :
ラベル: , , , ,
登録: 投稿 ( Atom )