Error executing SQL: Lost connection to MySQL server during query

Chinese Translated
1

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

Original topic: 执行sql报错Lost connection to MySQL server during query

| username: Jolyne

[TiDB Usage Environment] Production Environment / Test / Poc
Test

[TiDB Version]
7.1.0

[Reproduction Path] What operations were performed when the issue occurred
Executing a large SQL query, each execution takes about 100 seconds before an error occurs. There is a panic in the logs, but no OOM. Is there a configuration issue?

[Encountered Problem: Symptoms and Impact]
image

1686292853386
16862928533861376×673 87.3 KB

1686292890285
16862928902851380×540 62 KB

1686292911899
16862929118991380×544 80.4 KB

1686292961480
1686292961480874×192 16.1 KB

[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]

2
| username: tidb狂热爱好者 | Original post link

You see, the monitoring graph you posted shows that it has already OOMed.

3
| username: Jolyne | Original post link

The memory usage is only over 10GB, and the threshold for a single transaction is larger than this. There are no OOM logs on my tidbserverj node either.

4
| username: Billmay表妹 | Original post link

Refer to the documentation for troubleshooting~

5
| username: wzf0072 | Original post link

The memory of the PD node can be reduced a bit, and more memory can be allocated to TiDB.

image
image752×323 14.1 KB

6
| username: Jolyne | Original post link

However, currently, as long as the SQL execution exceeds 100 seconds, this error will occur… It should not be related to the configuration. I don’t know where some parameter is set.

7
| username: wzf0072 | Original post link

What error does executing the SQL report? Please post it.

8
| username: Jolyne | Original post link

1686296421902
1686296421902942×559 27.6 KB

9
| username: wzf0072 | Original post link

Have you set the system variable global.max_execution_time?

image
image990×354 28.3 KB

10
| username: Jolyne | Original post link

Previously, I changed it to 10 minutes, then I reverted it back to the default. The error above still occurs with no limit.

11
| username: wzf0072 | Original post link

I didn’t see any critical error messages…
Can you look for any other information that might be helpful?

Can you try splitting the SQL and executing it in batches to see if it works?

12
| username: Jolyne | Original post link

1686297594822
16862975948221205×429 22.2 KB

13
| username: Jolyne | Original post link

When placed in the command line, after the statement is entered, it reports the error “lost” without executing.

14
| username: wzf0072 | Original post link

show variables like ‘%wait_timeout%’; Check the connection timeout settings?

15
| username: 有猫万事足 | Original post link

I see that when you insert into the new table, you also formatted the time field of the source table. In that case, you should be able to query the source table’s time field with several time interval conditions and insert into the new table in batches, which will likely avoid this issue.

16
| username: Jolyne | Original post link

This is indeed an optimization point.

17
| username: Jolyne | Original post link

No problem, I found that it seems to be an issue with HAProxy. When I directly connect and write using one of the 4000 ports, there is no “lost” error. :sweat_smile:
image

18
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.