TiDB 7.4：MySQL 8.0互換性対応の紹介

ウェビナー開催日：2024年12月21日

本スライドでは、TiDB 7.4 DMRで実現しているMySQL 8.0互換性対応を紹介いたします。

TiDBはMySQLと互換性のあるデータベースですが、MySQLのフォークではなく、TiDB独自の実装を行っています。本スライドではTiDBのMySQL互換とは何なのかについて、背景の説明と、2023年10月に発表されたTiDB 7.4 DMRまでに実現しているMySQL 8.0互換機能を紹介します。既存のMySQLデータベースからTiDBへの移行を検討しているが、互換性について興味や疑問がある場合の解消にお役立てください。

トピック
・TiDBのMySQL互換が意味すること
・TiDB 7.3まで対応していたMySQL 8.0互換機能の紹介
・TiDB 7.4 DMRで追加されたMySQL互換機能の紹介

アーカイブ動画
https://youtu.be/TgZIVs9h9No

Transcript

1. TiDB 7.4：MySQL 8.0互換性対応の紹介 本多康夫 Technical support engineer at PingCAP Japan
2. これまでのウェビナースケジュール https://pingcap.co.jp/event/ 
 
 2023年3月24日(木) 14:00 - 15:00 TiFlashの紹介 2023年4月13日(木) 14:00 - 15:00 TiDBにおけるSQLチューニング　SQL bindingのご紹介 2023年5月18日(木) 14:00 - 15:00 TiDB 7.0 DMR新機能の紹介 2023年6月15日(木) 14:00 - 15:00 【TiDB Serverless GA直前】TiDB新章。Serverless + Data API + AIによる新 たな活用 2023年7月27日(木) 14:00-15:00 TiDB Cloud最新状況の紹介 2023年9月14日(木) 14:00-15:00 TiDBとMySQLの互換性改善アップデート 2023年11月1日(木) 14:00-15:00 TiDB Operatorの紹介 2023年12月21日(木) 14:00-15:00 TiDB 7.4：MySQL 8.0互換性対応の紹介 ※過去開催アーカイブ：https://pingcap.co.jp/event-video/
3. 次回以降のウェビナースケジュール https://pingcap.co.jp/event/ 
 
 2024年1月25日(木) 14:00 - 15:00 TiDB 7.5 LTS新機能の紹介
4. おことわり • 本ウェビナー開催のアナウンス後、 2023年12月1日にTiDB 7.5.0 LTSがリリースされました ◦ 本ウェビナーは”TiDB 7.4：MySQL 8.0互換性対応の紹介”となっておりますが、 TiDB 7.5でも有用 な内容となっております • TiDB 7.5 LTSの紹介は2024年1月21日のウェビナーで実施いたします ◦ TiDB 7.5にはMySQL 8.0互換の他に、下記の多くの新機能が追加されています ▪ 複数インデックス作成の高速化 ▪ 分散実行フレームワークの導入 ▪ リソースコントロール機能の拡充
5. アジェンダ • TiDBのMySQL互換が意味すること • TiDB 7.3までに対応していた MySQL 8.0互換機能の紹介 • TIDB 7.4 で追加されたMySQL 8.0互換機能の紹介 • 既知の問題 • 互換性に関する詳細情報 • Q&A, アンケートのお願い • お知らせ
6. TiDBのMySQL互換が意味すること
7. TiDBの”MySQL互換”とは • データベースの”互換”を保つための主な2つの方法 • 1. オリジナルのソースコードの fork ◦ オリジナルのソースコードを元に独自の変更、改良を加える ◦ 例: MariaDB server https://github.com/MariaDB/server > MariaDB server is a community developed fork of MySQL server. 
 
 • 2. MySQLプロトコル互換のデータベースサーバー再実装 ◦ MySQLクライアントからみて、 ”MySQLサーバーのように”動くソフトウェア ◦ 例: TiDB ◦ https://www.pingcap.com/ > TiDB is wire compatible with MySQL ◦ TiDBはMySQLサーバーのforkではなく、実装する言語も異なる (Go, Rust)
8. MySQL 8.0のバージョンについて • MySQL 8.0 (MySQL 8.0.11から8.0.33まで) ◦ 「継続的デリバリ・モデル」 ◦ パッチリリース(8.0.xのx部分)にバグ修正と新機能を含む • MySQL 8.1以降 ◦ 「Innovation リリース」と「 Long-Term Support (LTS) リリース」 ◦ MySQL 8.0.34以降はバグ修正のみに移行 • 情報の正確性に努めましたが、正式な情報は下記をご確認ください ◦ https://blogs.oracle.com/mysql-jp/post/introducing-mysql-innovation-and-longterm-support -lts-versions-jp
9. TiDBのバージョンについて • X.Y.Z (例: 7.1.2) ◦ X : メジャーリリース ▪ 1年に1ずつ増加 ▪ 新機能と改善を含む ◦ Y : マイナーリリース ▪ Xの増加後は0にリセットし、DMR(約2ヶ月)またはLTS(約6ヶ月)のリリース毎に1増加 ▪ 新機能と改善を含む ◦ Z : パッチリリース ▪ XまたはYの増加後は0にリセット ▪ DMRでは常に0，LTSではパッチリリース毎に増加 • バージョニング詳細 ◦ https://docs.pingcap.com/tidb/stable/versioning • MySQLのバージョニングとTiDBのバージョニングは1:1では対応していない ◦ TiDB 7.3まででもMySQL 8.0の新機能を「先取り」している部分があります
10. TiDB LTS、DMRとは何か(補足) • 新機能を頻繁にリリースするサイクルと、比較的安定したリリースするサイクルの両立を図る • LTS - Long Term Supportの略 ◦ 約6ヶ月間隔でリリースされます ◦ 新機能が追加されます ◦ 不具合の修正を含むパッチリリース (例: TiDB v7.1.2)も提供されます • DMR - Development Milestone Releaseの略 • 約2ヶ月間隔でリリースされます ◦ 新機能が追加されます ◦ 不具合の修正を含むパッチリリース (例 v7.4.1)は提供されません ◦ DMRで発生した不具合の修正は次の DMRまたはLTSに含まれます ◦ DMRのパッチリリースバージョンは常に 0となります(v7.2.0, v7.3.0, v7.4.0)
11. MySQL 8.0互換が求められる背景 • MySQL 5.7が2023年10月にEOLを迎えた ◦ https://www.mysql.com/support/eol-notice.html > MySQL 5.7 is covered under Oracle Lifetime Sustaining Support > Per Oracle's Lifetime Support policy, as of October 25, 2023, MySQL 5.7 is covered under Oracle Sustaining Support. • MySQL 5.7を提供するDBaaS ◦ Amazon RDS for MySQLの例 ◦ https://aws.amazon.com/jp/about-aws/whats-new/2023/11/amazon-rds-mysql-new-minor-v ersion-5-7-44/ ◦ > MySQL 5.7 は 2023 年 10 月にコミュニティによるサポートが終了し、 2024 年 2 月 29 日に RDS による標準サポートが終了する予定です
12. • 2023年10月12日にリリース • MySQL 8.0互換を謳った初のTiDB • https://docs.pingcap.com/tidb/dev/release-6.5.0 TiDB 7.4 DMR
13. TiDB 7.3までに対応していた MySQL 8.0互換機能の紹介
14. TiDB 7.3までに対応済みのMySQL 8.0互換機能カテゴリ • TiDB 7.4より前のバージョンで以下の MySQL 8.0機能を追加しております • 認証・認可関連 • CTE (Common Table Expressions) / ウィンドウ関数 • 文字セット • インデックス • 関数 • チェック制約 • 数値型の表示幅
15. 認証・認可関連 • caching_sha2_password プラグイン対応 ◦ MySQL 8.0.4 ◦ TiDB 5.2.0 ◦ 従来の mysql_native_password も対応しています • password_reuse_interval ◦ 以前利用したパスワードを再利用可能になるための日数 ◦ MySQL 8.0.3 ◦ TiDB 6.5.0 ◦ デフォルト値は0 ( パスワード再利用可能の制限はない ) • ロール ( CREATE ROLE ) ◦ MySQL 8.0.0 ◦ TiDB 3.0.0
16. 認証・認可関連 • failed login attempts ◦ 正しくない認証情報で、一定回数ログインを試みた場合アカウントを一時的にロックする ◦ MySQL 8.0.19 ◦ TiDB 6.5.0 ◦ CREATE USER 'test1'@'localhost' IDENTIFIED BY 'password' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3; • ユーザーコメント ◦ データベースユーザーにコメントを追加できる ◦ MySQL 8.0.21 ◦ TiDB 6.4.0 ◦ CREATE USER 'bill'@'localhost' COMMENT 'This is Bill\'s user account';
17. Common Table Expression(CTE) • Common Table Expression(CTE)対応 ◦ MySQL 8.0.1 ◦ TiDB 5.1.0 `WITH <query_name> AS ( <query_definition> ) SELECT ... FROM <query_name>;` • WITHの後に一時的に名前をつけ、そのクエリーを再利用する
18. 文字セット • データベースデフォルト文字セット utf8mb4 ◦ 絵文字などに対応した 4バイトのutf8文字セット ◦ MySQL 8.0.1 ◦ TiDB 3.0.0 • utf8mb4以前のデフォルト文字セットセット ◦ MySQL < 8.0.1 latin1 ◦ TiDB < 3.0.0 utf8 ▪ 3バイトのutf8
19. 文字セット • TiDB 7.2以降、utf8mb3は、utf8の別名として扱われます mysql> CREATE TABLE t (s1 CHAR(1)) CHARACTER SET utf8mb3; Query OK, 0 rows affected (0.19 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `s1` char(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec)
20. インデックス • sql_require_primary_key ◦ 主キーなしでのcreate tableを許可するかどうか (デフォルトで無効) ◦ MySQL 8.0.13 ◦ TiDB 6.3.0 • Invisible index ◦ 不可視インデックス / オプティマイザがそのインデックスを考慮しないようにできる ◦ MySQL 8.0.0 ◦ TiDB 5.0 • multi valued index ◦ JSON型の列にインデックスを追加 ◦ MySQL 8.0.17 ◦ TiDB 6.6.0
21. 関数 • REGEXP_INSTR ◦ MySQL 8.0.4 ◦ TiDB 5.4.0 • intersect / except ◦ MySQL 8.0.31 ◦ TiDB 5.0 • ウインドウ関数対応 ◦ MySQL 8.0.2 ◦ TiDB 3.0.0 SELECT val, ROW_NUMBER() OVER w AS 'row_number', RANK() OVER w AS 'rank', DENSE_RANK() OVER w AS 'dense_rank' FROM numbers WINDOW w AS (ORDER BY val); • https://docs.pingcap.com/tidb/stable/window-functions
22. チェック制約 • MySQL 8.0.15 • TiDB 7.2.0 • 例 ◦ CREATE TABLE t(a INT CHECK(a > 10) NOT ENFORCED, b INT, c INT, CONSTRAINT c1 CHECK (b > c));
23. チェック制約 • チェック制約関する設定 ◦ tidb_enable_check_constraint ▪ チェック制約による整合性の確認を行うかどうか ▪ デフォルトは`OFF`(無効) • tidb_enable_check_constraint = off の動作 ◦ DDL(create table)では警告が出力され、チェック制約は作成されません mysql> select @@global.tidb_enable_check_constraint; +---------------------------------------+ | @@global.tidb_enable_check_constraint | +---------------------------------------+ | 0 | +---------------------------------------+ mysql> CREATE TABLE users ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(60) NOT NULL, -> UNIQUE KEY (username), -> CONSTRAINT min_username_length CHECK (CHARACTER_LENGTH(username) >=4) -> ); Query OK, 0 rows affected, 1 warning (0.18 sec) mysql> show warnings; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1105 | the switch of check constraint is off | +---------+------+---------------------------------------+ 1 row in set (0.00 sec) ◦ チェック制約は存在しないないため、違反しうる SQLも成功します mysql> INSERT INTO users (username) VALUES ('a'); Query OK, 1 row affected (0.01 sec)
24. チェック制約 • tidb_enable_check_constraint = on の動作 ◦ DDLは警告なしに終了し、チェック制約が作成されます mysql> set global tidb_enable_check_constraint = on; mysql> select @@global.tidb_enable_check_constraint; +---------------------------------------+ | @@global.tidb_enable_check_constraint | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> CREATE TABLE users ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(60) NOT NULL, -> UNIQUE KEY (username), -> CONSTRAINT min_username_length CHECK (CHARACTER_LENGTH(username) >=4) -> ); Query OK, 0 rows affected (0.16 sec) ◦ チェック制約に違反する SQLは`ERROR 3819 (HY000)`が返ります mysql> INSERT INTO users (username) VALUES ('a'); ERROR 3819 (HY000): Check constraint 'min_username_length' is violated. mysql>
25. 数値型の表示幅 • 数値型の表示幅 ◦ MySQL 8.0.19で非表示 ◦ TiDB 5.0.0 • MySQL 8.0.19以降、integer型への明示的な幅の指定はdeprecateになり、警告を出力します • TiDBでは、deprecate-integer-display-length変数によって動作を切り替えています ◦ デフォルトは false ◦ create table時に警告を出さず、show create tableで幅を出力する mysql> CREATE TABLE t1 (id int(10) not null); Query OK, 0 rows affected (0.13 sec) mysql> show warnings; Empty set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) mysql>
26. 数値型の表示幅 • deprecate-integer-display-length変数によっての動作の違い ◦ tidb.toml ファイルに以下の内容を記述します deprecate-integer-display-length = true • trueの場合の動作 ◦ create table時に警告を出し、show create tableで幅を出力しない)の動作例 mysql> CREATE TABLE t1 (id int(10) not null); Query OK, 0 rows affected, 1 warning (0.17 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1681 Message: Integer display width is deprecated and will be removed in a future release. 1 row in set (0.01 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec)
27. TIDB 7.4 で追加された MySQL 8.0互換機能の紹介
28. MySQL 互換のカテゴリー • MySQL 8.0の機能を下記に分類しています ◦ https://github.com/pingcap/tidb/issues/7968 ◦ Essential / 必須 ◦ Nice to Have / あると良い ◦ Not feasible / 実現不可能 • TiDBがMySQL 8.0互換を謳うため、”Essential(必須)”の機能が実装されていることとした ◦ Nice to Haveな機能で実装していないものもあります
29. 参考: Not feasible / 実現不可能 • 主にTiDBとMySQLのアーキテクチャの違いにより、実現不可能 (実現不要)なMySQL 8.0の新機能があり ます • FRMファイルの廃止とMySQLデータディクショナリの導入 ◦ https://dev.mysql.com/doc/refman/8.0/ja/data-dictionary.html ◦ https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0093 ◦ 理由 ▪ TiDBではテーブル定義を TiKVに保存しており、FRMファイルを利用していない • SET PERSIST ◦ システム変数の変更を永続化 ◦ https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0094 ◦ 理由 ▪ TiDBの SET GLOBAL がTiDBサーバー再起動後も変更を永続するため、 SET PERSISTENTと 同様であることから
30. Essential : 照合順序 • TiDB 7.4.0で下記2つの照合順序が追加されました • utf8mb4_0900_bin ◦ MySQL 8.0.17 ◦ TiDB 7.4.0 • utf8mb4_0900_ai_ci ◦ MySQL 8.0.0 ◦ TiDB 7.4.0 • utf8mb4デフォルト照合順序 ◦ MySQL: utf8mb4_0900_ai_ci ◦ TiDB: utf8mb4_bin
31. Essential : 照合順序 • デフォルトの照合順序を変更する方法 • システム変数 default_collation_for_utf8mb4 ◦ 文字セットutf8mb4のデフォルト照合順序を utf8_bin以外に変更可能です mysql> set global default_collation_for_utf8mb4 = 'utf8mb4_0900_ai_ci'; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> show warnings; | Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. | ◦ 設定可能な照合順序は utf8mb4_0900_ai_ci utf8mb4_general_ci utf8mb4_bin mysql> set global default_collation_for_utf8mb4 = 'utf8mb4_0900_bin'; ERROR 3721 (HY000): Invalid default collation utf8mb4_0900_bin: utf8mb4_0900_ai_ci or utf8mb4_general_ci or utf8mb4_bin expected mysql> ◦ show collationの出力は変更されません
32. Nice to Have : SET_VARヒント対応 • SET_VARヒント ◦ MySQL 8.0.3 ◦ TiDB 7.4.0 • SET_VAR ヒントの使用例 ◦ 単一のSQL文に対して一時的にシステム変数を変更する場合に有効 ◦ 以前 : 変更前の値を変数に一時的に格納後、 set文で変更し、SQL実行後値を戻す set @original_value = @@tidb_opt_distinct_agg_push_down; set @@tidb_opt_distinct_agg_push_down = 1; -- enables aggregate pushdown through join select ...; set @@tidb_opt_distinct_agg_push_down = @original_value; ◦ SET_VAR ヒント以降 select /*+ set_var(tidb_opt_distinct_agg_push_down=1) */ ...
33. Nice to Have : 関数 • TiDB 7.4.0で下記のUUID関連の関数が追加されました • uuid_to_bin / bin_to_uuid ◦ MySQL 8.0.0 ◦ TiDB 5.2.0 • is_uuid ◦ MySQL 8.0.0 ◦ TiDB 5.4.0
34. 既知の問題
35. Caused by: java.sql.SQLSyntaxErrorException: Table 'information_schema.KEYWORDS' doesn't exist • 環境 ◦ TiDB 7.4.0, TiDB 7.5.0 ◦ MySQL Connector/J 8.0.12以降 • 現象 ◦ Caused by: java.sql.SQLSyntaxErrorException: Table 'information_schema.KEYWORDS' doesn't exist 例外が発生する • 発生理由 ◦ MySQL 8.0.11でINFORMATION_SCHEMA.KEYWORDS テーブルを追加しキーワードと予約語を保存 ▪ https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-informat ion-schema ◦ MySQL Connector/JがMySQLバージョン8.0.11以上の時、キーワード取得を INFORAMATION_SCHEMA.KEYWORDS から行う ▪ https://github.com/mysql/mysql-connector-j/commit/ae4f8a373 ◦ TiDB 7.5には該当テーブルがない
36. Caused by: java.sql.SQLSyntaxErrorException: Table 'information_schema.KEYWORDS' doesn't exist • 回避策 (tiup playgroundの例) ◦ tidb.toml ファイルに下記エントリを追加する ▪ server-version = "5.7.25-TiDB-v7.5.0" ◦ 作成した `tidb.toml` ファイルを利用して tiup playground を起動 ▪ % tiup playground --db.config tidb.toml mysql> select version(); +--------------------+ | version() | +--------------------+ | 5.7.25-TiDB-v7.5.0 | +--------------------+ • 修正 ◦ TiDB 7.6 でinformation_schema.keywordsテーブルが追加されます ▪ https://github.com/pingcap/tidb/pull/48807 ◦ TiDB 7.4はDMRのため修正は行われません ◦ TiDB 7.5での修正は未定です
37. 参考 : TiDBのversion() 関数遍歴 • TiDB 7.4 DMR以降 ◦ 8.0.11 ◦ 最初のMySQL 8.0 General Availabilityバージョン ◦ https://github.com/pingcap/tidb/pull/46155 • TiDB 3.0.0からTiDB 7.3.0 ◦ 5.7.25 ◦ parser がTiDBから独立していた時代、 2019年5月に変更 ▪ https://github.com/pingcap/parser/pull/231
38. 互換性に関する詳細情報
39. TiDBのMySQLとの互換性について詳細 • https://docs.pingcap.com/tidb/stable/mysql-compatibility ◦ バージョン固有ではない MySQLとの互換性に関する情報 • MySQL 8.0 Compatibility #7968 https://github.com/pingcap/tidb/issues/7968 • Nice to Haveに乗っているが実装されていない機能が必要な場合 ◦ リンク先のissueに対して、必要性のコメントなどのフィードバックをお願いします ◦ お客様のビジネスに直結するご要望は弊社担当までお知らせください
40. Q&A