Server session

This topic describes several general operations performed on a server session, which is established between OceanBase Database Proxy (ODP) and an OBServer node.

View a server session

At present, it is not supported to view a server session through ODP. You can log in to the sys tenant of the OceanBase cluster as the root user and view all OBServer nodes in the cluster. Then, you can directly connect to the target OBServer node and view the server session.

1. Log in to the sys tenant of the OceanBase cluster as the root user and view all OBServer nodes in the cluster.

   obclient> select * from oceanbase.__all_server;
   +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+
   | gmt_create                 | gmt_modified               | svr_ip     | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version                                                            | stop_time | start_service_time | first_sessid | with_partition |
   +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+
   | 2023-02-28 15:45:53.230044 | 2023-02-28 15:46:25.577180 | 10.10.10.1 |     2882 |  3 | z3   |       2881 |               1 | ACTIVE |                     0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) |         0 |   1677570376568330 |            0 |              1 |
   | 2023-02-28 15:45:53.197477 | 2023-02-28 15:46:25.534448 | 10.10.10.2 |     2882 |  2 | z2   |       2881 |               0 | ACTIVE |                     0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) |         0 |   1677570376522994 |            0 |              1 |
   | 2023-02-28 15:45:53.113870 | 2023-02-28 15:46:25.098607 | 10.10.10.3 |     2882 |  1 | z1   |       2881 |               0 | ACTIVE |                     0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) |         0 |   1677570378084150 |            0 |              1 |
   +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+
   3 rows in set
   

2. Select the target OBServer node and directly connect to it. The following sample code directly connects to the OBServer node with the IP address 10.10.10.1 and the SQL port 2881.

   [admin@test001 ~]$ obclient -h10.10.10.1 -P2881 -uroot@sys -p -Doceanbase -A
   

3. Run the show processlist; command to view all sessions of the current OBServer node.

   obclient> show processlist;
   

   Returned result:

   +------------+---------+------------------+-----------+---------+------+--------+------------------+
   | Id         | User    | Host             | db        | Command | Time | State  | Info             |
   +------------+---------+------------------+-----------+---------+------+--------+------------------+
   | 3221812197 | root    | 10.10.10.1:48563 | NULL      | Query   |    0 | ACTIVE | show processlist |
   | 3222117829 | proxyro | 10.10.10.1:37876 | oceanbase | Sleep   |    6 | SLEEP  | NULL             |
   | 3221709618 | root    | 10.10.10.1:51390 | NULL      | Sleep   |  831 | SLEEP  | NULL             |
   +------------+---------+------------------+-----------+---------+------+--------+------------------+
   3 rows in set
   

Terminate a server session

At present, ODP does not provide a command to directly terminate a server session. You can terminate a server session by directly connecting to the target OBServer node.

1. Log in to the sys tenant of the OceanBase cluster as the root user and view all OBServer nodes in the cluster.

   obclient> select * from oceanbase.__all_server;
   +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+
   | gmt_create                 | gmt_modified               | svr_ip     | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version                                                            | stop_time | start_service_time | first_sessid | with_partition |
   +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+
   | 2023-02-28 15:45:53.230044 | 2023-02-28 15:46:25.577180 | 10.10.10.1 |     2882 |  3 | z3   |       2881 |               1 | ACTIVE |                     0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) |         0 |   1677570376568330 |            0 |              1 |
   | 2023-02-28 15:45:53.197477 | 2023-02-28 15:46:25.534448 | 10.10.10.2 |     2882 |  2 | z2   |       2881 |               0 | ACTIVE |                     0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) |         0 |   1677570376522994 |            0 |              1 |
   | 2023-02-28 15:45:53.113870 | 2023-02-28 15:46:25.098607 | 10.10.10.3 |     2882 |  1 | z1   |       2881 |               0 | ACTIVE |                     0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) |         0 |   1677570378084150 |            0 |              1 |
   +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+
   3 rows in set
   

2. Directly connect to the target OBServer node. The following sample code directly connects to the OBServer node with the IP address 10.10.10.1 and SQL port 2881.

   [admin@test001 ~]$ obclient -h10.10.10.1 -P2881 -uroot@sys -p -Doceanbase -A
   

3. Run the show processlist; command to view all sessions of the current OBServer node.

   obclient> show processlist;
   

   Returned result:

   +------------+---------+------------------+-----------+---------+------+--------+------------------+
   | Id         | User    | Host             | db        | Command | Time | State  | Info             |
   +------------+---------+------------------+-----------+---------+------+--------+------------------+
   | 3221812197 | root    | 10.10.10.1:48563 | NULL      | Query   |    0 | ACTIVE | show processlist |
   | 3222117829 | proxyro | 10.10.10.1:37876 | oceanbase | Sleep   |    6 | SLEEP  | NULL             |
   | 3221709618 | root    | 10.10.10.1:51390 | NULL      | Sleep   |  831 | SLEEP  | NULL             |
   +------------+---------+------------------+-----------+---------+------+--------+------------------+
   3 rows in set
   

4. Run the kill <id> command to terminate the current session whose Id is 3221812197.

   obclient> kill 3221812197;
   ERROR 2013 (HY000): Lost connection to MySQL server during query