ぼくたちはMySQL 8.1とどう生きるか 2023/08/28 yoku0825 MySQL Innovation Day Tokyo 2023

おことわり このセッションは個人の見解を述べたものであり、所属する組織または所属しない組織の意見と 同じかもしれませんし違うかもしれません 2023/08/28時点の推測、想像を多分に含みます。未来から見るとトンチンカンなことを言って る可能性があります。 1/59

TL;DR https://twitter.com/yoku0825/status/1686258400135655424 2/59

MySQL 8.1 リリースおめでとう ございます 3/59

MySQL 8.1 ほぼ7年ぶりの新 (?)バージョン 4/59

＼こんにちわ／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会副代表 ‐ MySQL Casual ‐ Oracle ACE Pro ‐ 5/59

MySQL 8.0 継続的リリース（） 2016/09 8.0.0, 2018/04 GA GAクオリティ（） メンテナンスリリースで機能が増える ‐ バグFIXを受け入れるために新機能の受け入れも必要 ‐ 新機能どころかSQLレベルの非互換があるバージョンもあった(予約語だったりワーニングだったり) ‐ MySQL 8.0.34とそれ以降ではもう新機能は(サーバーには)入らないはず 6/59

「サーバーには」 A new password-validation system variable now permits the configuration and .. .. This new capability is one several that provide DBAs more complete control over password management. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html 7/59

MySQL 8.1 2023/07 Innovation Release メンテナンスリリースは存在しない（8.1.0が唯一の8.1系のリリース） リリース系列が上がっているので8.0と互換性がなくても文句は言わない ‐ 新機能(?)が入る 8/59

余談 MySQL 5.1までは先頭の1文字がファイルフォーマットを示していたらしいんだけど5.5とそれ以降では 消えてた 9/59

余談 8.0の The series number describes the stable feature set は嘘だと思う https://dev.mysql.com/doc/refman/8.0/en/which-version.html 10/59

MySQL 8.0 vs MySQL 8.1 単にrelease noteのバイト数だけで比較 11/59

Innovation Releaseの意義？ 8.0に慣れすぎたおれ「これくらいの非互換もない機能追加だったらまだ8.0に入れても良かったん では？」 目を覚ます必要がある ちなみに5.7.0(リリースなし)もそうでしたね 12/59

Before 8.0 開発初期 開発後期 機能凍結(安定版) 5.0 alpha, beta Release Candidate Production 5.1 (名前はない) Release Candidate General Availability 5.5~5.7 Development Milestone Release Candidate General Availability 8.0 Development Milestone Release Candidate, General Availability N/A 13/59

After 8.0 開発初期 開発後期 機能凍結(安定版) 8.0 Development Milestone Release Candidate, General Availability(~8.0.33) General Availability (8.0.34~) 8.1 N/A N/A Innovation Release is GA(No maintenance release after GA) 8.2(maybe) N/A N/A Innovation Release is GA(No maintenance release after GA) 8.4(maybe) N/A N/A General Availability(Long Term Support) 14/59

つまりは 開発初期 開発後期 機能凍結(安定版) 5.0 alpha, beta Release Candidate Production 5.5~5.7 Development Milestone Release Candidate General Availability 8.0 Development Milestone Release Candidate, General Availability(~8.0.33) General Availability (8.0.34~) Next LTS(ex.8.4) Innovation Release(ex. 8.0, 8.1, 8.2) Innovation Release (ex. 8.3), Release Candidate? General Availability(Long Term Support) 15/59

つまりは Innovation Release = Development Milestone Release, Release Candidate の ような立ち位置 2023/08/25現在、yumリポジトリは mysql80-community-release であって8.1は勝手 にはアップグレード先にならない(aptリポジトリも名前は違うけれど同様) https://dev.mysql.com/downloads/repo/yum/ ‐ https://dev.mysql.com/downloads/repo/apt/ ‐ というよりはMySQL 8.1のビルド済みパッケージが置かれている気配がない http://repo.mysql.com/yum/ ‐ http://repo.mysql.com/apt/debian/dists/buster/ ‐ ダウンロードページにはある https://dev.mysql.com/downloads/mysql/ ‐ 16/59

の割には 17/59

メンテナンス期間 Introducing MySQL Innovation and Long-Term Support (LTS) versions 18/59

メンテナンス期間 LTS Releases will follow the Oracle Lifetime Support Policy, which includes 5 years of premier and 3 years of e xtended support. Innovation releases will be supported until the next major & minor release. - About every 2 years a new Long Term Support version will be released (blue). An exception is the 8.x LTS rel ease which will happen well before EOL of 8.0 Introducing MySQL Innovation and Long-Term Support (LTS) versions 19/59

メンテナンス期間 一応8年間はサポート(=セキュリティFixしてくれることは期待しているが彼らがどう思っているかは 謎) 8.4が2024/04にリリースされて(この日付は推測です) 即乗り換えるとすると、EOLは 2032/04 ただし「隣接しないLTS」は直接バージョンアップをサポートしないらしいので、あんまり塩漬けにするとその後大変 そう(後述) ‐ 本当に2年で次のLTSが出るなら、2026/04～2034/04と2028/04～2036/04 2027年くらいに次のLTSに行って、2030年くらいにまた次のLTSに行くくらいが据わりが良さそう(3年スパンか) ‐ ちなみに8.0のEOLは2026/04 その時点でのアップグレード先候補が8.4, 9.7, ただし9.7はギリギリすぎるのでやっぱり2025年中に8.4(1年経過)に上げるくらいが良 いのか ‐ 20/59

アップグレードパス Introducing MySQL Innovation and Long-Term Support (LTS) versions 21/59

アップグレードパス インプレースアップグレード(datadirをそのまま、バイナリだけパッケージを入れ替える)とレプリケー ションが公式にサポートされるのは 隣接するLTS(ex.8.4 -> 9.7) ‐ 同じLTS系列同士(ex. 8.4.11 -> 8.4.20) ‐ LTSをまたぐ前のInnovation Release同士(ex. 8.1 -> 8.2, 8.1 -> 8.3) ‐ LTSをまたぐ前のInnovation ReleaseからLTS(ex. 9.1 -> 9.7) ‐ 22/59

アップグレードパス - Upgrades from MySQL 8.0 to 8.1 are supported between General Availability (GA) releases only. For MySQ L 8.1, it is required that you upgrade from a MySQL 8.0 GA release (8.0.11 or higher). Upgrades from non-GA releases of MySQL 8.0 are not supported. - Upgrading to the latest release in the series is recommended before upgrading to the next release series. For ex ample, upgrade to the latest MySQL 8.0 release before upgrading to MySQL 8.1. https://dev.mysql.com/doc/refman/8.1/en/upgrade-paths.html 8.0は8.0.11とそれ以降って書いてあるけど、8.0.30へのアップグレードも結構クラッシュしたみた いだから避けて8.0最新から行った方が良いと思う 本当に8.0.34とそれ以降で変な変更を入れないなら、8.0.34まで行っておけばあとは8.0.40だろうが8.0.41 だろうが少ないインパクトで行けるはず ‐ 23/59

開発ツリーのイメージ 8.0 └── 8.0.32 └── 8.0.33 ├── 8.0.34 │ └── 8.0.35 │ └── 8.0.36 └── 8.1.0 └── 8.2.0 └── 8.3.0 24/59

開発ツリーのイメージ 8.0.34と8.1.0は兄弟 8.0.34と8.1.0のRelease Noteは非常に似通っている https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html ‐ What Is New in MySQL 8.1の内容は8.1.0のリリースノートとほぼ同じ ‐ 今後乖離が広がっていった時にWhat Is Newはどこから比べてNewになるのか？？ MySQL Bugs: #112039: Request for What Is New in the next LTS release ‐ 日本MySQLユーザ会 とみたさん のMySQL Parametersは8.1.0対応済 https://mysql-params.tmtms.net/mysqld/ ‐ 25/59

まとめ 8.1とそれ以降のInnovation Releaseは強いモチベーションがない限り考えなくて良い 次のLTSに軟着陸するために、今のうちに8.0.34とそれ以降あたりに着地をしておきたい (2026/04までには) 次のLTSが出る頃にはまた知見がインターネットにも出てくると思うので、心配しすぎるよりはその 時ちゃんと情報収集すれば良いのかなと 知見をインターネットに出す側の人大歓迎 ‐ 26/59

( ﾟдﾟ) 27/59

( ﾟдﾟ ) 28/59

(ﾟдﾟ ) 29/59

もしちゃんと時間内に 収められたらここから自 分へのご褒美タイム 30/59

MySQL 8.1につ いてしゃべっていい よ！ 31/59

その前に8.0にも 8.1にも入ってし まった何か 32/59

Both 8.1 and 8.0.34 Account Management Notes A new password-validation system variable now permits the configuration and enforcement of a minimum number of characters that users must change when attempting to replace their own MySQL account passwords. ‐ 33/59

Both 8.1 and 8.0.34 Binary Logging Several functions now are added to the libmysqlclient.so shared library that enable developers to access a MySQL server binary log: mysql_binlog_open(), mysql_binlog_fetch(), and mysql_binlog_close(). ‐ 34/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes Important Change: Since MySQL provides other means of performing database dumps and backups with the same or additional functionality, including mysqldump and MySQL Shell Utilities, the mysqlpump client utility program has become redundant, and is now deprecated ‐ 35/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes Replication: The sync_relay_log_info server system variable is deprecated in this release, and getting or setting this variable or its equivalent startup option –sync-relay- log-info now raises a warning. ‐ 36/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes Replication: The binlog_format server system variable is now deprecated, and subject to removal in a future version of MySQL. The functionality associated with this variable, that of changing the binary logging format, is also deprecated. ‐ 37/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes Group Replication: The group_replication_recovery_complete_at server system variable is now deprecated, and setting it produces a warning. You should expect its removal in a future release of MySQL. (WL #15460) ‐ 38/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes The mysql_native_password authentication plugin now is deprecated and subject to removal in a future version of MySQL. ‐ 39/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes The MySQL client library currently supports performing an automatic reconnection to the server if it finds that the connection is down and an application attempts to send a statement to the server to be executed. Now, this feature is deprecated and subject to removal in a future release of MySQL. ‐ The related MYSQL_OPT_RECONNECT option is still available but it is also deprecated. C API functions mysql_get_option() and mysql_options() now write a deprecation warning to the standard error output when an application specifies MYSQL_OPT_RECONNECT. (WL #15766) ‐ As of MySQL 8.0.34 and 8.1.0 ‘s libmysqlclients deprecates `MYSQL_OPT_RECONNECT` · Issue #354 · perl5-dbi/DBD-mysql 40/59

Both 8.1 and 8.0.34 Deprecation and Removal Notes CURRENT_USER() can now be used as a default value for VARCHAR and TEXT columns in CREATE TABLE and ALTER TABLE … ADD COLUMN statements. ‐ 41/59

バグ修正だけ #と はなんだったのか 42/59

MySQL 8.1 vs MySQL 8.0.34 こっちにリストしてるってことは8.0のツリーには入れなかったということなので、それは良いこと 43/59

MySQL 8.1 vs MySQL 8.0.34 C API Notes Added the new mysql_reset_connection_nonblocking() C API function. It is the counterpart of the mysql_reset_connection() synchronous function, for use by applications that require asynchronous communication with the server. Our thanks to Meta for the contribution. (Bug #32202058, WL #15633) ‐ The new mysql_get_connect_nonblocking_stage() C API function permits applications to monitor the progress of asynchronous connections for the purpose of taking appropriate actions based on the progress. Our thanks to Meta for the contribution. (Bug #32202053, WL #15651) ‐ 44/59

MySQL 8.1 vs MySQL 8.0.34 Deprecation and Removal Notes The use of the dollar sign ($) as the initial character of an unquoted identifier was deprecated in MySQL 8.0.32. In this release, the use of an unquoted identifier starting with the dollar sign and containing one or more dollar signs in addition to the first one generates a syntax error. ‐ 45/59

MySQL 8.1 vs MySQL 8.0.34 Logging Notes To aid in troubleshooting in the event of an excessively long server shutdown, this release introduces a number of new messages that are written to the MySQL error log during shutdown ‐ 46/59

MySQL 8.1 vs MySQL 8.0.34 SQL Syntax Notes JSON: It is now possible to capture EXPLAIN FORMAT=JSON output in a user variable using a syntax extension added in this release. EXPLAIN FORMAT=JSON INTO var_name stmt works with any explainable statement stmt to store the output in the user variable var_name ‐ 47/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Important Change; Replication: The default value for the SOURCE_RETRY_COUNT option of the CHANGE REPLICATION SOURCE TO statement has been changed to 10. ‐ 48/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Important Change: MySQL version numbers used in versioned comments now support a major version consisting of one or two digits (previously, only a single digit was supported for this value). ‐ 49/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Important Change: Dropped support for Enterprise Linux 6 (and associated glibc 2.12 generic), SUSE 12, Debian 10, MacOS 12, Ubuntu 18.04 and 20.04, Windows 10 and Server 2012R2; and 32-bit versions are no longer built. ‐ 50/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Group Replication: Any statement that fetches values of system status variables fetches them all, and acquires a read lock on them all as well. This meant that a SHOW STATUS started after one of the operations just listed was required to wait until the operation was complete before returning. Now in such cases, the statement fetching status variables immediately returns their cached values instead of waiting. (Bug #35373030) ‐ 51/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Group Replication: Before it elects a new primary, group_replication_set_as_primary() waits for all transactions to finish, including all DML operations that are currently being processed. ‐ 52/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Group Replication: For better diagnosis and troubleshooting of network instabilities, MySQL Group replication adds a number of variables in this release providing network, control message, and data message statistics for each member of Group Replication. ‐ 53/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed MySQL now implements client-side Server Name Indication (SNI), which is an extension to the TLS protocol. ‐ 54/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Comments in the mysql client are now enabled by default. To disable them, start mysql with the –skip-comments option. ‐ 55/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Implemented a SHOW PARSE_TREE statement in debug builds to display the JSON- formatted parse tree for a SELECT statement. ‐ This statement is not supported in release builds, and is available only in debug builds, or by compiling the server using -DWITH_SHOW_PARSE_TREE. (WL #15426) ‐ 56/59

MySQL 8.1 vs MySQL 8.0.34 Functionality Added or Changed Previously, invalid SSL server and CA certificates were not identified as problematic until after the server started or after an invalid certificate was loaded at runtime. ‐ 57/59

MySQL 8.1 vs MySQL 8.0.34 Bugs Fixed Incompatible Change; Replication: Setting server variables equal to SQL NULL as options on the command line should not be possible and is not supported. ‐ 58/59

Any Question and/or Suggestion? 59/59