JDBC Execution of SQL Throws Statement Cancelled Due to Client Request

Translated
1

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

Original topic: jdbc 执行 SQL 抛出 statement cancelled due to client request

| username: TiDBer_fpcTOmEt

【Background】
The Java service uses mysql.8.0.13.jar as the driver to execute business SQL.

【Phenomenon】
When adding an index to a large table, the Java program will intermittently throw a “statement cancelled due to client request” error after varying lengths of time (30-60 minutes).
image
However, upon investigation, it was found that the SQL was still running and the index was eventually added successfully.

image
image1380×652 166 KB

【Additional Information】
Apart from the necessary parameters like user and password, no extra operations were set in the connection URL parameters. The result of executing the command SHOW VARIABLES LIKE ‘max_execution_time’; is 0.
【TiDB Version】
image
image1301×82 33.7 KB

Configuration information
image
image694×618 55.8 KB

【Expectation】

  • Why does it timeout?
  • How to set a timeout for any statement (DML/DDL/DQL) through JDBC?
2
| username: tidb菜鸟一只 | Original post link

TiDB 6.5 doesn’t support MySQL 8 yet, so it’s more appropriate to use the 5.7 driver.

3
| username: ShawnYan | Original post link

Try switching to mysql/j 5.1

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
4
| username: 有猫万事足 | Original post link

In addition to the driver version issue mentioned above, you should also check whether load balancers like nginx/haproxy have set connection timeout periods.

5
| username: TIDB-Learner | Original post link

Most likely it’s a driver issue.

6
| username: zhang_2023 | Original post link

Driver incompatibility

7
| username: 双开门变频冰箱 | Original post link

Driver issue, I guess.

8
| username: chris-zhang | Original post link

Driver issue?

9
| username: 呢莫不爱吃鱼 | Original post link

The driver might be incompatible. Try changing the version.

10
| username: TiDBer_fbU009vH | Original post link

The version incompatibility should be.

11
| username: TiDBer_QYr0vohO | Original post link

Client driver version issue

12
| username: Soysauce520 | Original post link

The driver is recommended to be 8.0.30 or later.

13
| username: Soysauce520 | Original post link

Connector is not compatible with server versions below 5.7.5.

14
| username: DBAER | Original post link

It could be due to driver incompatibility or the Java framework. Try modifying the JDBC parameters. You can first try downgrading, and if that doesn’t work, consider comparing it with MySQL to see if the driver works properly.

15
| username: wangkk2024 | Original post link

Choose the recommended driver.

16
| username: TiDBer_21wZg5fm | Original post link

Use the MySQL 5.7 driver.