SQL execution exceeds 10 seconds, client proactively disconnects

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

Original topic: Sql执行超过10秒,客户端主动断开连接

| username: TiDBer_rrt4FOef

[TiDB Usage Environment] Testing
[TiDB Version] V6.1.3
[Encountered Problem: Problem Phenomenon and Impact]

MySQL driver version: 8.0.31

Executing a time-consuming SQL statement, after more than 10 seconds, the client actively disconnects.

SQL:

insert into TableX (a,b,c,d,..) (   SELECT a,b,c,d,..    from TablxY  where ...)

Client error:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10,016 milliseconds ago. The last packet sent successfully to the server was 10,017 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)

JDBC connection string:

url: jdbc:mysql://11.139.35.194:4000/busiData?tcpKeepalive=true&autoReconnect=true&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=1000&prepStmtCacheSqlLimit=2147483647&rewriteBatchedStatements=true&useConfigs=maxPerformance&allowMultiQueries=true&useSSL=false

[Investigation Process]
Checked the official documentation
Connection Pool and Connection Parameters | PingCAP Docs

The situation I encountered is not quite the same, it is not a connection keep-alive configuration issue.
During testing, I only executed this one SQL statement, using only one database connection, and the SQL executed successfully on the server side, data was inserted successfully, and TiDB did not report any errors, only found a prompt that the connection was closed.
It is determined that the client did not receive a response for more than 10 seconds and actively closed the database connection.
Checked MySQL documentation
MySQL :: MySQL Connector/J 8.0 Developer Guide :: 15 Troubleshooting Connector/J Applications
Item 15.8, adding tcpKeepalive=true to the connection string did not help, in fact, this value is true by default.

TiDB’s max-txn-ttl, wait_timeout, interactive_timeout, max_execution_time parameters are all default values, definitely longer than 10 seconds.

[Excluding haproxy issues]
Skipped haproxy and connected directly to the TiDB instance, the same issue occurred.

Has anyone encountered the same problem?

| username: weixiaobing | Original post link

Check if there are any timeout parameters configured on the application side. The connection pool also has some default parameters. You can refer to the following link:

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

Try using an officially recommended version of the MySQL connector.

| username: Billmay表妹 | Original post link

Currently, the versions compatible with TiDB are 5.7 and 8.0, but there are still quite a few incompatibilities.
Reference: 与 MySQL 兼容性对比 | PingCAP 文档中心

TiDB is highly compatible with the MySQL 5.7 protocol, commonly used features, and syntax of MySQL 5.7.
You can use MySQL 5.7 for testing~

| username: TiDBer_rrt4FOef | Original post link

After changing the MySQL driver to 5.1.49, it worked fine.

The connectTimeout and socketTimeout in the connection string were not set, and setting them actually caused issues…

| username: system | Original post link

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