# オンラインDDL ## 概要 + これらの演習では、TiDBでオンラインDDLを学習します。TiDBは、オンラインDDLを実行する場合、さまざまな要件に合わせてさまざまなオプションを提供することを学びます。 + 演習1では、システム変数`tidb_enable_metadata_lock`が`OFF`に設定され、TiDBが時間のかかるトランザクションを処理するシナリオがあります。 + 演習2では、システム変数`tidb_enable_metadata_lock`が`OFF`に設定され、TiDBは再試行メカニズムを使用してショートトランザクションを処理します。 + 演習3では、システム変数`tidb_enable_metadata_lock`が`ON`に設定され、TiDBが時間のかかるトランザクションを処理するシナリオがあります。 ### 注意 + TiDBサーバーシステム変数`tidb_enable_metadata_lock`は、バージョン6.3.0から使用可能です。バージョン6.3.0より前のバージョンでは、TiDBは`tidb_enable_metadata_lock=OFF`と同じように動作します。 + いくつかのステップでは、2つの異なるターミナルウィンドウでコマンドを入力する必要があります。2つのターミナルを起動し、演習の間ずっと開いておくと便利でしょう。 + この演習で利用する、`IP`、`port`、`username`、`password`、`directory`などは、あなたの環境と異なる場合があります。 + ターミナルウィンドウの"`$`"とはOSプロンプトです。 + ターミナルウィンドウ内の "`tidb>`" または "`mysql>`" は、SQL コマンドプロンプトです。 + これらの演習は、ノートパソコンなどの単一のマシン上で実行することができます。 + これらの演習のすべてのスクリプトとコードには製品保証はありません。自己責任で使用します。 ### 前提条件 + macOSまたはLinuxを使用していること。 + [lab repository](https://github.com/pingcap/tidb-course-201-lab.git)をクローン済みであること。 + 演習「テスト用にTiDBクラスタを作成する」を完了していること。 + [JDK 8 以降](https://docs.oracle.com/javase/8/docs/technotes/guides/install/install_overview.html)がインストールされていること。 ## 演習 1：システム変数`tidb_enable_metadata_lock`が`OFF`になったら、長いトランザクションが実行されている間にテーブルにカラムを追加する ### 概要 + この演習では、システム変数`tidb_enable_metadata_lock`が`OFF`という状況で、TiDBが長いトランザクションを処理します。 ### 所要時間 + この演習は約10分程度かかります。 ### タスク 1. ターミナル1で、長いトランザクションを開始します。 2. ターミナル2で、テーブルにカラムを追加します。 3. ターミナル3と4で、テーブルをクエリします。 ### 手順 1. ターミナル1でTiDBに接続し、システム変数`autocommit`の値を確認して、`OFF`に値を更新します。 ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` ```sql SHOW VARIABLES LIKE "autocommit"; SET autocommit = OFF; SET GLOBAL autocommit = OFF; SHOW VARIABLES LIKE "autocommit"; ``` ``` tidb:4000> SHOW VARIABLES LIKE "autocommit"; -------------- SHOW VARIABLES LIKE "autocommit" -------------- +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec) tidb:4000> SET autocommit = OFF; -------------- SET autocommit = OFF -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> SET GLOBAL autocommit = OFF; -------------- SET GLOBAL autocommit = OFF -------------- Query OK, 0 rows affected (0.01 sec) tidb:4000> SHOW VARIABLES LIKE "autocommit"; -------------- SHOW VARIABLES LIKE "autocommit" -------------- +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) ``` 2. ターミナル2、ターミナル3、ターミナル4の3つの新しいターミナルを開き、ポート4000の`TiDB server`に接続して、システム変数`autocommit`値が`OFF`であることを確認します。 ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ``` ```sql SHOW VARIABLES LIKE "autocommit"; ``` ``` tidb:4000> SHOW VARIABLES LIKE "autocommit"; -------------- SHOW VARIABLES LIKE "autocommit" -------------- +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) ``` 3. ターミナル1で、システム変数`tidb_enable_metadata_lock`の値を確認します。値が`ON`の場合は、値を`OFF`に更新します。 ```sql SHOW VARIABLES LIKE "tidb_enable_metadata_lock"; SET GLOBAL tidb_enable_metadata_lock = OFF; ``` ``` tidb:4000> SHOW VARIABLES LIKE "tidb_enable_metadata_lock"; -------------- SHOW VARIABLES LIKE "tidb_enable_metadata_lock" -------------- +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | tidb_enable_metadata_lock | ON | +---------------------------+-------+ 1 row in set (0.00 sec) tidb:4000> SET GLOBAL tidb_enable_metadata_lock = OFF; -------------- SET GLOBAL tidb_enable_metadata_lock = OFF -------------- Query OK, 0 rows affected (0.01 sec) ``` 4. ターミナル1で、データベース`demo`とテーブル`t1`を作成し、テーブルに2行を挿入します。次に、`COMMIT`または`ROLLBACK`を実行せずに、`BEGIN; INSERT INTO t1(num) VALUES (3)`で新しいトランザクションを開始します。 ```sql DROP DATABASE IF EXISTS demo; CREATE DATABASE demo; USE demo; CREATE TABLE IF NOT EXISTS t1 ( id BIGINT NOT NULL PRIMARY KEY auto_increment, num INT ); INSERT INTO t1(num) VALUES (1); COMMIT; INSERT INTO t1(num) VALUES (2); COMMIT; BEGIN; INSERT INTO t1(num) VALUES (3); ``` ``` ... tidb:4000> BEGIN; -------------- BEGIN -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> INSERT INTO t1(num) VALUES (3); -------------- INSERT INTO t1(num) VALUES (3) -------------- Query OK, 1 row affected (0.00 sec) ``` 5. ターミナル2で、`t1` テーブルにカラムを追加します。 ```sql USE demo; ALTER TABLE t1 ADD COLUMN c1 INT; ``` ``` tidb:4000> USE demo; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed tidb:4000> ALTER TABLE t1 ADD COLUMN c1 INT; -------------- ALTER TABLE t1 ADD COLUMN c1 INT -------------- Query OK, 0 rows affected (0.52 sec) ``` 6. ターミナル3で、`t1`テーブルにレコードを挿入します。 ```sql USE demo; BEGIN; INSERT INTO t1(num) VALUES (4); COMMIT; ``` ``` ... tidb:4000> INSERT INTO t1(num) VALUES (4); -------------- INSERT INTO t1(num) VALUES (4) -------------- Query OK, 1 row affected (0.00 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.01 sec) ``` 7. ターミナル4で、`t1` テーブルをクエリします。 ```sql USE demo; SELECT * FROM t1; COMMIT; ``` ``` tidb:4000> USE demo; ... Database changed tidb:4000> SELECT * FROM t1; -------------- SELECT * FROM t1 -------------- +----+------+------+ | id | num | c1 | +----+------+------+ | 1 | 1 | NULL | | 2 | 2 | NULL | | 4 | 4 | NULL | +----+------+------+ 3 rows in set (0.00 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) ``` 8. ターミナル1でトランザクションをコミットします。コード`8028` エラーが予想されます。トランザクションはロールバックされます。 ```sql COMMIT; ``` ``` tidb:4000> COMMIT; ERROR 8028 (HY000): Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] ``` ### まとめ システム変数`tidb_enable_metadata_ロック`が`OFF`に設定されている場合、オンラインDDL は進行中のトランザクション（ターミナル1）によってブロックされることなくスキーマを変更できます。ただし、トランザクションをコミットしようとすると、コード`8028` `Information schema is changed`というエラーが発生することが予想されます。 ## 演習 2：`tidb_enable_metadata_lock`が`OFF`になり、再試行機能を持ったショートトランザクションがある場合、テーブルにカラムを追加する ### 概要 + この演習では、システム変数`tidb_enable_metadata_lock`が`OFF`という状況で、TiDBがショートトランザクションを処理します。コード`8028`のエラーの処理には、単純な再試行メカニズムを備えたJavaアプリケーションが使用されます。 ### 所要時間 + この演習は約10分程度かかります。 ### タスク 1. ターミナル1で、Javaプログラムを起動します。 2. ターミナル2で、テーブルにカラムを追加します。 3. ターミナル3と4で、テーブルをクエリします。 ### 手順 1. ターミナル1で、TiDBとの接続を終了し、Javaプログラムを起動して`t1`テーブルにいくつかの値を挿入し続けます。 ``` tidb:4000> EXIT ``` ``` $ ./07-demo-online-ddl-add-column-v2.sh ``` ``` $ ./07-demo-online-ddl-add-column-v2.sh Connection established. 0 rows inserted. 1 rows inserted. 2 rows inserted. ... ``` 2. ターミナル2で、`t1` テーブルにカラムを追加します。 ```sql USE demo; ALTER TABLE t1 ADD COLUMN c1 INT; ``` ``` tidb:4000> USE demo; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed tidb:4000> ALTER TABLE t1 ADD COLUMN c1 INT; -------------- ALTER TABLE t1 ADD COLUMN c1 INT -------------- Query OK, 0 rows affected (0.52 sec) ``` 3. ターミナル1を確認します。次のようなエラーメッセージが表示されます。 ``` ... Record 1 to be inserted. Record 2 to be inserted. Main task error. Error: java.sql.SQLException: Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] SQLState: HY000 ErrorCode: 8028 java.sql.SQLException: Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1612) at DemoJdbcPreparedStatement8028v2.main(DemoJdbcPreparedStatement8028v2.java:38) 8028 (schema mutation) encountered, backoff... DO anything in reaction to error, in this example we continue our workload. Record 2 to be inserted. Record 3 to be inserted. ... ``` `tidb_enable_metadata_lock`は`OFF`に設定されているので、ターミナル2のオンラインDDL コマンドは成功します。しかし、ターミナル1では、`Information schema is changed`というコード`8028`エラーが表示されます。Javaコードでは、これに対処するために単純な再試行メカニズムを使用しています。`8028`エラーが発生するたびに、プログラムはしばらく一時停止してから、トランザクションを再試行します。この単純な再試行方法は、私たちのデモンストレーション目的には十分です。ただし、経験豊富な開発者は、ビジネス要件に応じて違ったやり方で対処します。 4. ターミナル3で、レコードをテーブル`t1`に挿入します。エラーは予想されません。 ```sql USE demo; INSERT INTO t1(num) VALUES (4); COMMIT; ``` ``` tidb:4000> USE demo; ... Database changed tidb:4000> INSERT INTO t1(num) VALUES (4); -------------- INSERT INTO t1(num) VALUES (4) -------------- Query OK, 1 row affected (0.00 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) ``` 5. ターミナル4では、クエリテーブル`t1`です。エラーは予想されません。 ```sql USE demo; SELECT * FROM t1; COMMIT; ``` ``` tidb:4000> USE demo; ... Database changed tidb:4000> SELECT * FROM t1; -------------- SELECT * FROM t1 -------------- +----+------+------+ | id | num | c1 | +----+------+------+ | 1 | 1 | NULL | | 2 | 2 | NULL | | 3 | 100 | NULL | | 4 | 100 | NULL | | 5 | 100 | NULL | | 6 | 100 | NULL | | 7 | 100 | NULL | | 8 | 100 | NULL | | 9 | 100 | NULL | | 10 | 4 | NULL | | 11 | 100 | NULL | | 12 | 100 | NULL | +----+------+------+ 12 rows in set (0.00 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) ``` ### まとめ システム変数`tidb_enable_metadata_ロック`が`OFF`に設定されている場合、オンラインDDL は進行中のトランザクション（ターミナル1）によってブロックされることなくスキーマを変更できます。ターミナル1のショートトランザクションで、`Information schema is changed`という`8028`エラーが発生しますが、私たちの場合は簡単に再試行することで問題を解決できます。開発者は、ビジネス要件に基づいて別の方法で処理する場合があります。 ## 演習 3：`tidb_enable_metadata_lock`と`ON`の場合にカラムを追加する ### 概要 + この演習では、システム変数`tidb_enable_metadata_lock`が`ON`という状況になっています。システム変数`tidb_enable_metadata_lock`が有効になっていると、DDL 操作はトランザクションを待ちます。 ### 所要時間 + この演習は約10分程度かかります。 ### タスク 1. ターミナル1で、システム変数`tidb_enable_metadata_lock`を有効にしてから、長いトランザクションを開始します。 2. ターミナル2で、テーブルにカラムを追加します。 3. ターミナル3と4で、テーブルをクエリします。 ### 手順 1. ターミナル1で、システム変数`tidb_enable_metadata_lock`の値を`ON`に変更します。 ``` $ ./connect-4000.sh ``` ```sql SET GLOBAL tidb_enable_metadata_lock = ON; SHOW VARIABLES LIKE "tidb_enable_metadata_lock"; ``` ``` tidb:4000> SET GLOBAL tidb_enable_metadata_lock = ON; -------------- SET GLOBAL tidb_enable_metadata_lock = ON -------------- Query OK, 0 rows affected (0.02 sec) tidb:4000> SHOW VARIABLES LIKE "tidb_enable_metadata_lock"; -------------- SHOW VARIABLES LIKE "tidb_enable_metadata_lock" -------------- +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | tidb_enable_metadata_lock | ON | +---------------------------+-------+ 1 row in set (0.00 sec) ``` 2. ターミナル1で、データベース`demo`とテーブル`t1`を作成し、テーブルに2行を挿入します。次に、`COMMIT`または`ROLLBACK`を実行せずに、`BEGIN; INSERT INTO t1(num) VALUES (3)`で新しいトランザクションを開始します。 ```sql DROP DATABASE IF EXISTS demo; CREATE DATABASE demo; USE demo; CREATE TABLE IF NOT EXISTS t1 ( id BIGINT NOT NULL PRIMARY KEY auto_increment, num INT ); INSERT INTO t1(num) VALUES (1); COMMIT; INSERT INTO t1(num) VALUES (2); COMMIT; BEGIN; INSERT INTO t1(num) VALUES (3); ``` ``` ... tidb:4000> BEGIN; -------------- BEGIN -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> INSERT INTO t1(num) VALUES (3); -------------- INSERT INTO t1(num) VALUES (3) -------------- Query OK, 1 row affected (0.00 sec) ``` 3. ターミナル2で、`t1` テーブルにカラムを追加します。 ```sql USE demo; ALTER TABLE t1 ADD COLUMN c1 INT; ``` ``` tidb:4000> USE demo; ... Database changed tidb:4000> ALTER TABLE t1 ADD COLUMN c1 INT; -------------- ALTER TABLE t1 ADD COLUMN c1 INT -------------- ``` `tidb_enable_metadata_lock`が`ON`なので、このカラムの追加操作はブロックされています。 4. ターミナル3で、テーブル`t1`にレコードを挿入します。 ```sql USE demo; INSERT INTO t1(num) VALUES (4); COMMIT; ``` ``` tidb:4000> USE demo; ... Database changed tidb:4000> INSERT INTO t1(num) VALUES (4); -------------- INSERT INTO t1(num) VALUES (4) -------------- Query OK, 1 row affected (0.00 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) ``` 5. ターミナル4で、テーブルをクエリします。 ```sql USE demo; SELECT * FROM t1; COMMIT; ``` ``` tidb:4000> USE demo; ... Database changed tidb:4000> SELECT * FROM t1; -------------- SELECT * FROM t1 -------------- +----+------+ | id | num | +----+------+ | 1 | 1 | | 2 | 2 | | 4 | 4 | +----+------+ 3 rows in set (0.00 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) ``` ターミナル4の結果は、演習1と演習2のターミナル4の結果とは異なります。新しいカラムはまだ追加されていません。これは、`tidb_enable_metadata_lock`が`ON`で、ターミナル2のカラム追加コマンドがまだ保留中だからです。 6. ターミナル1でトランザクションをコミットします。 ```sql COMMIT; ``` ``` tidb:4000> COMMIT; -------------- commit -------------- Query OK, 0 rows affected (0.00 sec) ``` 7. さて、ターミナル2に行ってみましょう、カラムの追加コマンドは終了するはずです。 ### まとめ システム変数`tidb_enable_metadata_lock`が`ON`に設定されている場合、オンラインDDL はスキーマを変更できません。オンラインDDL 操作は、`metadata lock`を保持するロングトランザクションが終了するまで待ちます。 ## クリーンアップ ターミナル1で、TiDBサーバーのシステム変数`autocommit`と`tidb_enable_metadata_lock`の値が`ON`に戻っていることを確認します。 ```sql SET GLOBAL autocommit = ON; SET GLOBAL tidb_enable_metadata_lock = ON; ``` すべての接続を終了し、すべてのターミナルを閉じます。 ## 演習終わり