描述

    对于kill query proceselist_id语句：

    • 可以kill query掉指定proceselist_id的session
    • 普通用户默认只能kill query当前用户下所有session， 其他租户下session对其不可见，相同租户下其他用户的session的kill query与否根据其当前租户权限确定.
    • observer的SYS租户可kill query本租户内任意一个session

    • 以下三种情况时将不支持kill query，返回 ERROR 1235 (0A000): Not supported feature or function：

    • 处于事务中时，例如，begin。

    • 当前sql依赖上一条sql执行结果时，如select found_rows()/ select row_count()/ show errors/ show warnings等。
    • 已经指定下一条transaction特性时，如set transaction xxx。

    示例

    1. OceanBase (admin@oceanbase)> show processlist;
    2. +-------+--------+-------+--------------------+-----------+-------------+-------------------+-------------------+------+------+
    3. | Id    | Tenant | User  | Host               | db        | trans_count | svr_session_count | state             | tid  | pid  |
    4. +-------+--------+-------+--------------------+-----------+-------------+-------------------+-------------------+------+------+
    5. | 10017 | sys    | admin | 10.125.111.4:34628 | oceanbase |          67 |                 1 | MCS_ACTIVE_READER | 2891 | 2891 |
    6. +-------+--------+-------+--------------------+-----------+-------------+-------------------+-------------------+------+------+
    7. 1 row in set (0.01 sec)
    9. OceanBase (admin@oceanbase)> select * from __all_core_table join ob_all_proxy_config;
    10. ^Ccatch sig: 2
    11. Ctrl-C -- sending "KILL QUERY 10017" to server ...
    12. Ctrl-C -- query aborted.
    13. ERROR 1317 (70100): Query execution was interrupted
    14. OceanBase (admin@oceanbase)> show processlist;
    15. +-------+--------+-------+--------------------+-----------+-------------+-------------------+-------------------+------+------+
    16. | Id    | Tenant | User  | Host               | db        | trans_count | svr_session_count | state             | tid  | pid  |
    17. +-------+--------+-------+--------------------+-----------+-------------+-------------------+-------------------+------+------+
    18. | 10017 | sys    | admin | 10.125.111.4:34628 | oceanbase |          69 |                 1 | MCS_ACTIVE_READER | 2891 | 2891 |
    19. +-------+--------+-------+--------------------+-----------+-------------+-------------------+-------------------+------+------+
    20. 1 row in set (0.01 sec)
    23. OceanBase (sqluser01@(none))> show processlist;
    24. +-------+--------+-----------+----------------------+------+-------------+-------------------+-------------------+------+------+
    25. | Id    | Tenant | User      | Host                 | db   | trans_count | svr_session_count | state             | tid  | pid  |
    26. +-------+--------+-----------+----------------------+------+-------------+-------------------+-------------------+------+------+
    27. | 10020 | sys    | sqluser01 | 100.69.197.111:56934 | NULL |           5 |                 1 | MCS_ACTIVE_READER | 2900 | 2891 |
    28. +-------+--------+-----------+----------------------+------+-------------+-------------------+-------------------+------+------+
    29. 1 row in set (0.01 sec)
    31. OceanBase (sqluser01@(none))> kill query 10017;
    32. ERROR 1227 (42501): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
    1. OceanBase (admin@oceanbase)> begin;
    2. Query OK, 0 rows affected (0.00 sec)
    4. OceanBase (admin@oceanbase)> show tables;
    5. +------------------------------------+
    6. | Tables_in_oceanbase                |
    7. +------------------------------------+
    8. | ob_all_proxy                       |
    9. | ob_all_proxy_config                |
    10. | ob_all_proxy_kv_table              |
    11. | ob_all_proxy_stat                  |
    12. | ob_all_proxy_vip_tenant_table      |
    13. +------------------------------------+
    14. 5 rows in set (0.01 sec)
    16. OceanBase (admin@oceanbase)> kill query 10021;
    17. ERROR 1235 (0A000): Not supported feature or function
    18. OceanBase (admin@oceanbase)> commit;
    19. Query OK, 0 rows affected (0.01 sec)
    21. OceanBase (admin@oceanbase)> set transaction read only;
    22. Query OK, 0 rows affected (0.00 sec)
    24. OceanBase (admin@oceanbase)> kill query 10021;
    25. ERROR 1235 (0A000): Not supported feature or function