In some scenarios, querying the mysql.stats_top_n table results in garbled text

Translated
1

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

Original topic: 某些场景下查询mysql.stats_top_n表导致乱码

| username: 人如其名

Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the issue can help the development team address it promptly.
【TiDB Version】6.3.0
【Impact of the Bug】Causes client garbled text

【Possible Steps to Reproduce the Issue】

mysql> show variables like 'tidb_analyze_version';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| tidb_analyze_version | 2     |
+----------------------+-------+
1 row in set (0.00 sec)

Start the tpch bench, wait for the automatic statistics to complete, and then query the topN statistics view
select * from mysql.stats_top_n where table_id=125 and hist_id=2;

【Observed Unexpected Behavior】

mysql> select * from mysql.stats_top_n where table_id=125 and hist_id=2;
+----------+----------+---------+-----------+-------+
| table_id | is_index | hist_id | value     | count |
+----------+----------+---------+-----------+-------+
|      125 |        1 |       2 |      | 60099 |
|      125 |        1 |       2 |      | 58500 |
|      125 |        1 |       2 |    
                                       | 58357 |
|      125 |        1 |       2 |      | 58344 |
|      125 |        1 |       2 |      | 58032 |
|      125 |        1 |       2 |    
                                       | 58019 |
|      125 |        1 |       2 |     | 57993 |
|      125 |        1 |       2 |      | 57915 |
|      125 |        1 |       2 |      | 57863 |
|      125 |        1 |       2 |      | 57850 |
|      125 |        1 |       2 |      | 57421 |
|      125 |        1 |       2 |       | 57408 |
|      125 |        1 |       2 |         | 57382 |
|      125 |        1 |       2 |      | 57369 |
  | 57148 ||        1 |       2 |    
|      125 |        1 |       2 |      | 57018 |
|      125 |        1 |       2 |      | 56927 |
|      125 |        1 |       2 |    
  | 56706 |
|      125 |        1 |       2 |      | 56576 |
|      125 |        1 |       2 |      | 56563 |
|      125 |        1 |       2 |      | 56524 |
|      125 |        1 |       2 |      | 56459 |
|      125 |        1 |       2 |      | 56329 |
|      125 |        1 |       2 |         | 56225 |
|      125 |        1 |       2 |         | 56056 |
+----------+----------+---------+-----------+-------+
25 _-+_ i+ _e+ (0.01 _ec)

+y_-+> 
+y_-+> 
+y_-+> 
+y_-+>

【Expected Behavior】
Normal output
【Related Components and Specific Versions】

【Other Background Information or Screenshots】

image
image1028×1000 68.6 KB

2
| username: Lucien-卢西恩 | Original post link

Hello, I did not encounter this situation during my tests. It might be an issue with your session window settings or client compatibility configuration. Please provide the minimal reproduction steps. Thank you!

3
| username: 人如其名 | Original post link

Okay, I’ll send the detailed information when I get home tonight.

4
| username: 人如其名 | Original post link

Hello, you can create the relevant tables according to the script and then perform the query to reproduce the issue.

5
| username: 善若水 | Original post link

I’ve also encountered similar issues.

6
| username: Lucien-卢西恩 | Original post link

Hello, I couldn’t reproduce the issue on my side. Please confirm your client’s operating system, character set, and client app.

7
| username: 近墨者zyl | Original post link

Study, study… I’ll test it too.

8
| username: 人如其名 | Original post link

Based on your screenshot, the content also contains garbled characters, but it hasn’t disrupted the display of the window. The original content does not have garbled characters; you can check the data of the imported table. The display issue I encountered should be caused by the garbled content. When I later wrote a program to read this topn statistics table, I found that the content was garbled. In fact, there are no garbled characters in the actual business table.

9
| username: h5n1 | Original post link

Mine is completely messed up 6.3.0

10
| username: 近墨者zyl | Original post link

Could it be due to the character set?

11
| username: Hacker007 | Original post link

Check the character set of Xshell.

12
| username: Lucien-卢西恩 | Original post link

@人如其名 It should be an issue with my client character set. I tested it with version v5.4.2 and the output result is the same. It should not be related to the TiDB version. I suggest you try adjusting the character set configuration from TiDB → Client → OS.

13
| username: 裤衩儿飞上天 | Original post link

It’s because there is a longblob field in the queried table, and the binary display caused garbled text.

14
| username: 人如其名 | Original post link

It might be as you said, but I feel that using varchar for this field should be sufficient.

15
| username: Tank001 | Original post link

I feel it’s a character set issue; the string should be fine.

16
| username: 人如其名 | Original post link

How could it be a character set issue? Look at how clearly h5n1 bro has set it up.

17
| username: 裤衩儿飞上天 | Original post link

In iTerm2, it’s not garbled, but the longblob field returns a bunch of irregular strings. In other terminal tools like SecureCRT, Xshell, and Ubuntu, it’s completely garbled.