What is the proper way to find the connection ID by the requested query statement in TiDB?

Question

Im creating an alter automation script that executes an alter on the TiDB cluster. In case of an error, I need to kill the running alter using the KILL statement - https://docs.pingcap.com/tidb/stable/sql-statement-kill.

KILL requires a connection ID as an input. The only way to get this connection is to query the INFORMATION_SCHEMA.CLUSTER_PROCESSLIST table (https://docs.pingcap.com/tidb/stable/information-schema-processlist#cluster_processlist) and get find the connection by INFO column (this column has requested query). But TiDB does some formatting before it inserts the query into the INFO column. For example, it removes all comments and probably does something else. So searching by the INFO column might be problematic as I don't know what exact formatting TiDB does.

How can I find the connection ID from the INFORMATION_SCHEMA.CLUSTER_PROCESSLIST by the raw query? Is it possible to normalize the input query in the same manner as TiDB does and query the INFORMATION_SCHEMA.CLUSTER_PROCESSLIST as follows:

SELECT ID 
FROM `INFORMATION_SCHEMA.CLUSTER_PROCESSLIST` 
WHERE INFO = NORMALIZE('/*comment*/ALTER TABLE tbl ADD COLUMN c') 
LIMIT 1;

Or perhaps there is any other way around how I can get the connection ID of an ongoing query so I can kill it?

Thanks!

Answer 1

I think you can get the sql digest first and query the process ID by digest in CLUSTER_PROCESSLIST table.

The "SQL digest" here means the same as used in slow logs, which is a unique identifier calculated through normalized SQL statements. The normalization process ignores constant, blank characters, and is case insensitive. Therefore, statements with consistent syntaxes have the same digest.

https://docs.pingcap.com/tidb/dev/statement-summary-tables#statements_summary

If you use Golang for programing, you can use the tidb-parser get the digest for your SQL.

https://github.com/pingcap/tidb/blob/master/parser/docs/quickstart.md

go.mod:

go 1.18

require github.com/pingcap/tidb/parser v0.0.0-20220923141543-ecd67531f172

example.go:

import (
    "fmt"

    "github.com/pingcap/tidb/parser"
    "github.com/pingcap/tidb/parser/ast"
    _ "github.com/pingcap/tidb/parser/test_driver"
)

sql := "SELECT * FROM github_events;"
normalized, digest := parser.NormalizeDigest(sql)

If you use Node.js, maybe you can use tidb-sql-parser, which is use wasm to build.

Answer 2

You can use the SHOW PROCESSLIST statement to get a list of all currently active client connections. OR get from INFORMATION_SCHEMA.PROCESSLIST.

Here is an example(ID is the connection ID that you are looking for):

mysql> SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

You can also use the INFORMATION_SCHEMA.PROCESSLIST table in a WHERE clause to filter the list of connections.

Answer 3

Do you want to cancel a DDL statement? Kill statement usually use to kill the DML statements. You can refer to Max's reply to get the ID to kill a DML statement.

if you want to Kill a DDL statement, you should use "ADMIN CANCEL DDL" statement: https://docs.pingcap.com/tidb/stable/sql-statement-admin-cancel-ddl#admin-cancel-ddl, this syntax is unique to TiDB.