SQL Execution Failure When Querying with TiDB

Translated
1

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

Original topic: 使用TIDB查询sql时候 sql的执行失败

| username: Hacker_AkXHVl7a

SQL state [HY000]; error code [1105]; other error: [src/coprocessor/endpoint.rs:167]: WireError(OverRecursionLimit); nested exception is java.sql.SQLException: other error: [src/coprocessor/endpoint.rs:167]: WireError(OverRecursionLimit)

image
image1380×638 557 KB

2
| username: Kongdom | Original post link

Could you provide the statement and the TiDB version?

3
| username: Hacker_AkXHVl7a | Original post link

Could you provide the statement and the TiDB version?

4
| username: Hacker_AkXHVl7a | Original post link

Could you provide the statement and the TiDB version?

5
| username: Kongdom | Original post link

The image is not visible. Please provide the text you need translated.

6
| username: Kongdom | Original post link

To be honest, this is probably the longest SQL statement I’ve ever seen. Can the application layer optimize it?

7
| username: wisdom | Original post link

There might be some issues with the sentence; please optimize it.

8
| username: Hacker_AkXHVl7a | Original post link

Can it be modified from the configuration?

9
| username: Hacker_AkXHVl7a | Original post link

There is no problem with the SQL, it just seems to be too long, which causes the issue. However, this is the business scenario; I wonder if it can be modified from the TIDB configuration side.

10
| username: Kongdom | Original post link

:rofl: In what business scenario would you need to write an SQL statement with a length of 120,000 characters?

11
| username: Hacker_AkXHVl7a | Original post link

One reason is that the table has too many fields, more than 1600. The business scenario is: using SQL to query the fields with null values in this table, which involves function calculations, and it adds up to a lot. Plus, there are some judgment conditions. Can this only be optimized at the SQL level? Can the size given by TiDB be modified?

12
| username: Kongdom | Original post link

Did you write such a long code just to retrieve non-empty records? :rofl: Can’t you control it from the source to disallow empty values?

13
| username: Hacker_AkXHVl7a | Original post link

Because the data source is connected to the merchants, the source data cannot be modified.

14
| username: Kongdom | Original post link

This statement is to retrieve the count of records where all fields are not empty, right?

15
| username: Kongdom | Original post link

Try this variable and see if it works:

16
| username: Hacker_AkXHVl7a | Original post link

Is this something that needs to be handled and modified in the configuration file?

17
| username: forever | Original post link

Session level:
SET cte_max_recursion_depth = 1000;

Global level:
SET GLOBAL cte_max_recursion_depth = 1000;

18
| username: alfred | Original post link

This SQL is too complex, and the database may not handle it efficiently or appropriately. It is recommended to optimize it from the application layer.

19
| username: system | Original post link

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