## Module 4: Modifying Session and Global System Variables

+ [`auto_increment_offset`](https://docs.pingcap.com/tidb/stable/system-variables#auto_increment_offset) is one of the session and global system variables, modify it at the session level and the global level, then observe the results.

### Step 1. Check terminal connections

+ Verify that you have three terminals with database connections. Two terminals ([Terminal 1](ssh://monitor1-ssh#1) and [Terminal 3](ssh://monitor1-ssh#3)) connected to TiDB server 2, and one terminal ([Terminal 2](ssh://monitor1-ssh#2)) connected to TiDB server 1.

### Step 2. In terminal 1, update `auto_increment_offset` from `1` to `99`

- Switch to [Terminal 1](ssh://monitor1-ssh#1).

+ Check and modify the system variable `auto_increment_offset` from `1` to `99` at the session level in [Terminal 1](ssh://monitor1-ssh#1). Confirm that the variable had been updated at the session level only.

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SET SESSION auto_increment_offset=99;
  ```

  ```output
  tidb:db2> SET SESSION auto_increment_offset=99;
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### Step 3. In terminal 2 and 3, check the value of `auto_increment_offset`

+ Check the variable value in [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3) from both session and global levels. Both should remain unchanged, which is `1`. Here, we use [Terminal 2](ssh://monitor1-ssh#2) as an example.

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.01 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### Step 4. In terminal 1, change `auto_increment_offset` back to `1`

- Switch to [Terminal 1](ssh://monitor1-ssh#1).

  ```sql
  SET SESSION auto_increment_offset=1;
  ```

### Step 5. In terminal 1, update `auto_increment_offset` at the global level

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SET GLOBAL auto_increment_offset=99;
  ```

  ```output
  tidb:db2> SET GLOBAL auto_increment_offset=99;
  Query OK, 0 rows affected (0.05 sec)
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### Step 6. In terminal 2 and 3, check `auto_increment_offset`

+ Check the value of `auto_increment_offset` in [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3) from both session and global levels. Confirm that only the variable is updated at the global level only. We use the result from [Terminal 2](ssh://monitor1-ssh#2) as an example.

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### Step 7. Restart the TiDB cluster

+ In any terminal, exit the connections with TiDB, and then restart the TiDB cluster.

  ```bash
  tiup cluster restart tidb-test --yes
  ```

  ```output
  ...
  Restarted cluster `tidb-test` successfully
  ```

### Step 8. In terminal 1, 2 and 3, check the value of `auto_increment_offset`

+ Connect to [Terminal 1](ssh://monitor1-ssh#1), [Terminal 2](ssh://monitor1-ssh#2), and [Terminal 3](ssh://monitor1-ssh#3), and check the value of `auto_increment_offset` from both session and global levels. Confirm that the variable is updated at both the session and the global level. We use [Terminal 1](ssh://monitor1-ssh#1) as an example.

  ```bash
  ./connect-db1.sh
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```

  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### Step 9. In any terminal, change `auto_increment_offset` back to `1`

  ```sql
  SET GLOBAL auto_increment_offset=1;
  ```

  ```output
  tidb:db1> SET GLOBAL auto_increment_offset=1;
  Query OK, 0 rows affected (0.07 sec)
  ```

### Step 10. Exit and close all terminals

### Summary

If you change the session and global variable using `SET SESSION`, the effective scope will be the current session only.

If you change the session and global variables using `SET GLOBAL`, the effective scope will be all TiDB server instances and new sessions.