TiDBとMySQLの互換性改善アップデート
 本多康夫 Technical support engineer at PingCAP Japan

これまでのウェビナースケジュール https://pingcap.co.jp/event/ 
 
 2023年2月17日(金) 14:00-15:00 PingCAP Education: TiDB CloudのIntegration機能の紹介 - Datadog、 Prometheus、Vercelとの統合 2023年3月2日(木) 14:00-15:00 ハンズオン TiDB Cloud Serverless TierとChatGPTのSQL生成を試してみ よう! 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の互換性改善アップデート ※過去開催アーカイブ：https://pingcap.co.jp/event-video/

次回以降のウェビナースケジュール https://pingcap.co.jp/event/ 
 
 2023年11月予定 詳細未定

アジェンダ ● 以前のMySQL互換状況(おさらい) ● TiDBとMySQLの互換性改善 ○ AUTO_INCREMENT、セーブポイント、外部キー、アドバイザリーロックの追加 ● MySQL 8互換性対応のアップデート ● MySQLとの非互換があった場合の対応方法 ● Q&A, アンケートのお願い ● お知らせ

以前のMySQL互換状況(おさらい)

2022年4月時点でのMySQL互換性について ● 2022年4月21日ウェビナー “アプリケーション開発観点からのTiDB - Ruby編” ○ TiDB 6.0.0-DMR リリース(2022年4月7日)直後の実施 ● https://pingcap.co.jp/event-webinar-tidb-dev-lang-ruby-thank-you/ ● 当時は、制限事項としてユーザーに多くの回避策や制限を強いる状況でした

TiDBリリースタイムライン ● 約半年毎にLTS、約2ヶ月おきにDMRとリリースし、新機能の追加に限らず、 現在多数の互換性の改善 ● 2022年4月 6.0.0 DMR ● 2022年6月 6.1.0 LTS ● 2022年8月 6.2.0 DMR ● 2022年9月 6.3.0 DMR ● 2022年11月 6.4.0 DMR ● 2022年12月 6.5.0 LTS ● 2023年2月 6.6.0 DMR ● 2023年3月 7.0.0 RMR ● 2023年5月 7.1.0 LTS ● 2023年6月 7.2.0 DMR ● 2023年8月 7.3.0 DMR

MySQLとの過去の非互換性 ● Ruby on Railsなどのフレームワークの機能という観点から紹介します ○ AUTO_INCREMENT ○ セーブポイント ○ 外部キー ○ bulk DDL ○ Advisory lock

Active Record TiDB Adapterとは ● Active Recordのmysql2アダプターを継承して作成 ○ 主な役割 MySQLとの互換性がない部分でメソッドの戻り値の変更 ● MySQL(mysql2 adapter)ではTrueだがTiDBではFalse(対応していない)機能 ○ supports_savepoints? ■ セーブポイントに対応しているか ○ supports_advisory_locks? ■ アドバイザリーロック(GET_LOCK())に対応しているか ○ supports_foreign_keys? ■ 外部キー(foreign key)に対応しているか ○ supports_bulk_after? ■ 1つのALTER文で複数のカラムを変更できるか

例 - 暗黙的なソートとAUTO_INCREMENT ● Railsでの.firstや.lastではカラムを指定せず暗黙的なソートを行う、そのときのデフォルトはidを利用する ● TiDBのauto_incrementでの発番はTiDBサーバーごとにキャッシュされるため、下記のコードは正しくない結果を返す場合 がある irb(main):001:0> first_user = User.first User Load (0.2ms) SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1 ● 対応方法: 暗黙的なソートのカラムを変更可能 `updated_at` でソートする class User < ApplicationRecord self.implicit_order_column = "updated_at" end User Load (0.3ms) SELECT `users`.* FROM `users` ORDER BY `users`.`updated_at` ASC, `users`.`id` ASC LIMIT 1

例: ユニットテストとセーブポイント ● Rails(Minitest/RSpec)でのテストがセーブポイントを実行する ○ テスト終了時にseedデータの状態を元に戻すためsavepointを利用している ● 対応方法 ○ Minitestの場合 ■ self.use_transactional_tests = falseにして、毎回seedデータを再作成する ○ RSpecの場合 ■ config.use_transactional_fixtures = false ■ Database Cleaner の DatabaseCleaner.strategy = :truncation または :deletion を設定する

例: マイグレーションのbulk: true ● マイグレーションでbulk: trueであっても非対応として分割して実行されます class AddColumnsToUser < ActiveRecord::Migration[7.0] def change change_table :users, bulk: true do |t| add_column :users, :email, :string add_column :users, :nickname, :string end end end ● bulk: trueであってもsupports_bulk_after?がfalseを返すため、下記のように分割して実行されます ALTER TABLE `users` ADD `email` varchar(255) ALTER TABLE `users` ADD `nickname` varchar(255)

例: マイグレーションと外部キー ● TiDBはforeign keyのSQLを理解し、テーブル定義として保持するが、実際には外部キーは作成されない (効果がない) create_table "users", charset: "utf8mb4", collation: "utf8mb4_bin", force: :cascade do |t| end create_table "projects", charset: "utf8mb4", collation: "utf8mb4_bin", force: :cascade do |t| t.bigint "user_id" end add_foreign_key "projects", "users" ● テーブル定義 mysql> show create table projects\G *************************** 1. row *************************** Table: projects Create Table: CREATE TABLE `projects` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, KEY `index_projects_on_user_id` (`user_id`), CONSTRAINT `fk_rails_b872a6760a` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

例: マイグレーションとアドバイザリーロック ● RailsのマイグレーションAdvisory lock(GET_LOCK(), RELEASE_LOCK())を活用している ○ マイグレーションが同時に実行されないため SELECT GET_LOCK('2946838989811228680', 0) SELECT RELEASE_LOCK('2946838989811228680') ○ JavaScriptでのPrisma、elixirのEctoでも同様にマイグレーション時にAdvisory lockを利用します ● 現状の回避策 ○ `tidb_enable_noop_functions`を`ON`にして、get_lock()実行がエラーを返さないようにする ■ ロックはしないのでユーザー側で同時にマイグレーションを実行しないようにする

TiDBとMySQLの互換性改善アップデート

● TiDB 6.4 DMRで、グローバル単調増加する AUTO_INCREMENT動作が改善されました ○ デフォルトの動作は従来通り、各 TiDBサーバー毎に単調増加する ○ AUTO_ID_CACHE 1の内部的な動作が変更され、単一の TiDBサーバーがauto_incrementの中央 集権的な割り当てサービスとして値を発番し、各 TiDBサーバーは中央集権的な TiDBサーバーから 値を取得します ● 従来の設定 ○ AUTO_INCREMENT は単一TiDBサーバー内で単調増加 ○ 各TiDBサーバーがAUTO_INCREMENTのキャッシュ(デフォルト30000) ○ 複数のTiDBサーバーをまたがる場合は、一意性は保たれるが、順序は保証されない グローバルに単調増加するAUTO_INCREMENT

セーブポイント ● TiDB 6.2 DMRでセーブポイントが利用可能になりました ● https://pingcap.co.jp/announcing-tidb-6-2-with-bette-observability-faster-execution-better-disast er-recovery-and-more/ ○ “SAVEPOINTでは、セーブポイントを指定することで、トランザクション内のフォールバックノードを柔 軟に制御することができます。この機能により、複雑なトランザクションやより多様なビジネス設計を 管理することができます。 ” ● セーブポイントの主な利用用途 ○ アプリケーション開発者が直接 savepoint 文を実行することは少ないと想定される ○ MySQL/TiDBでは対応していない nested transactionの代替として ○ フレームワークでのユニットテスト終了後、データベースの状態を元に戻す目的として

外部キー ● TiDB 6.5で外部キーが利用可能になりました ● 外部キーに関する設定 ○ foreign_key_checks ■ 外部キーによる整合性の確認を行うかどうか ■ デフォルトは`YES`(有効) ● Note ○ TiDB 6.5以前は、create tableでのforeign keyは構文レベルではサポートされていましたが、実際の整合性の確認は 行っていませんでした ○ TiDB 6.5以前からTiDB 6.6以降にアップグレードをおこなっても /* FOREIGN KEY INVALID */ のコメントが付与され、外部 キーによる整合性の確認は行われません ○ 一度外部キーを削除し、再度外部キーを作成することで、実際の整合性の確認を行うようになります mysql> SHOW CREATE TABLE child\G *************************** [ 1. row ]*************************** Table | child Create Table | CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `pid` int(11) DEFAULT NULL, KEY `idx_pid` (`pid`), CONSTRAINT `fk_1` FOREIGN KEY (`pid`) REFERENCES `test`.`parent` (`id`) ON DELETE CASCADE /* FOREIGN KEY INVALID */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

アドバイザリーロック ● TiDB 6.1 LTSでアドバイザリーロック GET_LOCK() RELEASE_LOCK() が利用可能になりました ○ https://pingcap.co.jp/announcing-tidb-6-2-with-bette-observability-faster-execution-better- disaster-recovery-and-more/ ■ “SAVEPOINTでは、セーブポイントを指定することで、トランザクション内のフォールバックノー ドを柔軟に制御することができます。この機能により、複雑なトランザクションやより多様なビ ジネス設計を管理することができます。 ” ● アドバイザリーロックの主な利用用途 ○ テーブル定義の変更を行う処理 (マイグレーションと呼ばれる )の排他制御のため ○ Flyway, Prisma Migrate, elixir Ecto, Ruby on Rails など

ALTER TABLEによる複数のスキーマ変更 ● TiDB 6.2 DMRで単一のALTER TABLE文で複数のカラムまたはインデックス追加が可能になりました

ALTER TABLEによる複数のスキーマ変更 ● TiDB 6.2 DMRで単一のALTER TABLE文で複数のカラムまたはインデックス追加が可能になりました

注意点 : MySQLとの動作の違い ● セーブポイント ○ https://docs.pingcap.com/ja/tidb/stable/sql-statement-savepoint#mysql-compatibility ○ “ROLLBACK TO SAVEPOINT が使用される場合、 MySQL は指定されたセーブポイントの後にのみ保持されているロックを解放しま すが、...中略... 代わりに、TiDB はトランザクションがコミットまたはロールバックされるときにすべてのロックを解放します。 ” ● アドバイザリーロック ○ https://docs.pingcap.com/ja/tidb/stable/locking-functions ○ “TiDB で許可される最小タイムアウトは 1 秒、最大タイムアウトは 1 時間 (3600 秒) です。これは、0 秒と無制限のタイムアウト ( timeout=-1 ) の両方が許可される MySQL とは異なります。 TiDB は範囲外の値を最も近い許可値に自動的に変換し、 timeout=-1 秒を 3600 秒に変換します。 ” ● ALTER TABLEによる複数のスキーマ変更 ○ https://docs.pingcap.com/ja/tidb/stable/sql-statement-alter-table ○ “TiDB は、実行前にテーブル スキーマに従ってステートメントを検証します。たとえば、列 c1がテーブルに存在しないため、 ALTER TABLE t ADD COLUMN c1 INT, ADD COLUMN c2 INT AFTER c1;実行するとエラーが返されます。 ” mysql>create table t (a int, b int); Query OK, 0 rows affected mysql>alter table t change b bb int, add index idx(bb); (1072, 'column does not exist: bb')

Active Record TiDB Adapterの現状 ● Active Recordのmysql2アダプターを継承して作成 ○ 主な役割 MySQLとの互換性がない部分でメソッドの戻り値の変更 ● MySQL(mysql2 adapter)ではTrueだがTiDBではFalse(対応していない )機能 ○ supports_savepoints? ■ セーブポイントに対応しているか →セーブポイントが追加された ○ supports_advisory_locks? ■ アドバイザリーロック (GET_LOCK())に対応しているか → アドバイザリーロックが追加された ○ supports_foreign_keys? ■ 外部キー(foreign key)に対応しているか → 外部キーが追加された ○ supports_bulk_after? ■ 1つのALTER文で複数のカラムを変更できるか → 1つのALTER文で複数のカラムを変更可能になった ● Active Record TiDB Adapterはその役割をおえ、メンテナンスが終了しました ○ “Since this Gem is a solution for the compatibility issues left by the older versions (prior to 6.0) of TiDB, TiDB Cloud and TiDB serverless will always use the latest versions, so there is no need for additional adapters for integration. You can directly use ActiveRecord or the mysql2 gem.”

MySQL 8互換性対応のアップデート

注意点 ● 本セクションの内容は開発中の TiDBバージョンに基づく説明となります ● 本バージョンのリリース時までに予告なく内容が変更される可能性があります

バージョン表示 ● mysql cliで接続した場合のServer version % mysql --comments --host 127.0.0.1 --port 4000 -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 585105414 Server version: 8.0.11-TiDB-v7.4.0-alpha TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible ● version() 関数の戻り値 mysql> select version()\G *************************** 1. row *************************** version(): 8.0.11-TiDB-v7.4.0-alpha ● tidb_version() 関数の戻り値(参考) mysql> select tidb_version()\G *************************** 1. row *************************** tidb_version(): Release Version: v7.4.0-alpha Edition: Community Git Commit Hash: 9cb19af5b1f840c838b213a9dc3858a9165b944d Git Branch: heads/refs/tags/v7.4.0-alpha UTC Build Time: 2023-09-12 14:25:46 GoVersion: go1.21.0 Race Enabled: false Check Table Before Drop: false Store: tikv

照合順序 ● 利用可能な照合順序の追加 ○ utf8mb4_0900_ai_ciと utf8mb4_0900_binが追加されました mysql> show collation; +--------------------+---------+------+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------+---------+------+---------+----------+---------+ | ascii_bin | ascii | 65 | Yes | Yes | 1 | | binary | binary | 63 | Yes | Yes | 1 | | gbk_bin | gbk | 87 | | Yes | 1 | | gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 | | latin1_bin | latin1 | 47 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | Yes | Yes | 1 | | utf8_general_ci | utf8 | 33 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 1 | | utf8mb4_0900_ai_ci | utf8mb4 | 255 | | Yes | 1 | | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | Yes | Yes | 1 | | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 1 | +--------------------+---------+------+---------+----------+---------+ 13 rows in set (0.01 sec)

照合順序 ● システム変数 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の出力は変更されません

チェック制約 ● チェック制約関する設定 ○ 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)

チェック制約 ● 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>

その他 : SET_VARヒント対応 ● 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) */ ...

その他 : integer型への明示的な幅の指定 ● 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>

その他 : integer型への明示的な幅の指定 ● 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)

MySQLとの非互換があった場合の対応方法

互換性についてのmeta issue ● MySQL 8.0 Compatibility #7968 https://github.com/pingcap/tidb/issues/7968 ○ Essential ■ 必須 : TiDBがMySQL 8互換として必須と考える機能 ○ Nice to Have ■ あった方が良い機能 ○ Not Applicable ■ TiDBとMySQLの仕組みの違いにより、 TiDBでは適用できないもの ● 例: MySQL 8のデータディクショナリ (MySQL 5.7以前のfrmファイルから) ● https://dev.mysql.com/doc/refman/8.0/ja/system-schema.html#system-schema- data-dictionary-tables ● Nice to Haveに乗っているが実装されていない機能が必要な場合 ○ リンク先のissueに対して、必要性のコメントなどのフィードバックをお願いします ○ お客様のビジネスに直結するご要望は弊社担当までお知らせください

Q&A

Thank You!
 https://www.pingcap.com/
 info@pingcap.com