How to Query the SQL Statement Corresponding to a Digest

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 怎么查询digest对应的SQL语句

| username: HACK

[TiDB Usage Environment] Online, Testing, Research
[TiDB Version]
[Encountered Problem]
Cluster Version: 4.0.6
An SQL statement hasn’t been committed. By checking INFORMATION_SCHEMA.CLUSTER_PROCESSLIST, I can find the DIGEST. How can I view the SQL statement corresponding to this DIGEST?

[Reproduction Path] What operations were performed to encounter the problem
[Problem Phenomenon and Impact]

[Attachments]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: 啦啦啦啦啦 | Original post link

Try linking the CLUSTER_STATEMENTS_SUMMARY table or the STATEMENTS_SUMMARY_HISTORY table to see.

select QUERY_SAMPLE_TEXT from INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY where DIGEST='xxxxx'

3
| username: 逍遥_猫 | Original post link

You might be able to find the corresponding SQL statements in the slow_query log.

4
| username: HACK | Original post link

Can’t find it in these two tables.

5
| username: HACK | Original post link

Neither txn_start_ts nor digest were found in the cluster_slow_query table.

6
| username: 啦啦啦啦啦 | Original post link

It’s been too long, it must have been cleared.

image
image1040×238 31.5 KB

7
| username: HACK | Original post link

I just did the test.

8
| username: 啦啦啦啦啦 | Original post link

I tested version 6.1 on my side and it works. There are no normal entries in cluster_slow_query; queries that execute quickly but are not committed will not be recorded. You can try increasing the parameters above; it is possible that there are too many types of SQL queries, causing some to be removed.

9
| username: HACK | Original post link

This is my personal test environment.

I feel that the information viewed from the cluster_statements_summary (this view currently has a total of only 15 records) is not accurate. I just executed an SQL, and then I couldn’t see it in this view.

10
| username: 啦啦啦啦啦 | Original post link

select QUERY_SAMPLE_TEXT from INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY a join INFORMATION_SCHEMA.processlist b on a.DIGEST=b.DIGEST;

Does this work? It works fine on my end.

image
image1380×168 23.7 KB

11
| username: HACK | Original post link

Try this and see if the SQL that comes out twice is the same?

SESSION1:

use testdb;

begin;

update t set name=‘aaa’ where id=1;

— Check the SQL corresponding to this DIGEST

commit;

update t set name=‘bbb’ where id=2;

— Check the SQL corresponding to this DIGEST

12
| username: 啦啦啦啦啦 | Original post link

Both are present because their DIGESTs are the same.

image
image1380×108 19.7 KB

13
| username: HACK | Original post link

I tested it several times, and sometimes it’s accurate, sometimes it’s not. It feels like the content in INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY is not updated in a timely or accurate manner.

14
| username: HACK | Original post link

Retested it again, still having issues, the process is as follows:

tidb_stmt_summary_history_size=24

tidb_stmt_summary_max_sql_length=4096

tidb_stmt_summary_max_stmt_count=200

tidb_stmt_summary_refresh_interval=1800

Issue:

Executing SQL statements like update tabname set name=? where id=? multiple times, when checking the recorded SQL content in the table, only the first executed SQL statement can be seen.

Testing process:

SESSION1:

begin;

update zjp set name=‘DDD’ where id=3;

SESSION2:

Check the DIGEST corresponding to this SQL, and check the SQL text corresponding to that DIGEST

select query_sample_text from information_schema.cluster_statements_summary where digest=‘xxx’;

The text seen is the SQL statement just executed.

SESSION1:

commit; commit the previous modification

update zjp set name=‘BBB’ where id=2;

SESSION2:

select query_sample_text from information_schema.cluster_statements_summary where digest=‘xxx’;

The text seen is still the first executed SQL text, and the second one (update zjp set name='BBB' where id=2;) is not seen.

15
| username: h5n1 | Original post link
  • DIGEST: The digest of this type of SQL
  • DIGEST_TEXT: The normalized SQL
  • QUERY_SAMPLE_TEXT: The original SQL statement of this type of SQL, only one statement is taken if there are multiple

The digest is the fingerprint calculated from the normalized SQL.

16
| username: HACK | Original post link

Is there any way to find the actual executed SQL?

17
| username: h5n1 | Original post link

It seems there is no place to check, and the places to record SQL information are just these, which have been basically mentioned before.

18
| username: forever | Original post link

Set the slow log to the minimum and check from the slow log.

19
| username: HACK | Original post link

If the transaction is not committed, there should be no content in the slow log.

20
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.