MySQL-JDBC连接超时问题分析
MySQL JDBC 连接超时问题分析
TL;DR
遇到 MySQL JDBC 连接超时问题？这里是快速解决方案：传送门
运行环境
- 服务端: CentOS 7.9, MySQL 5.7 (Docker)
- 客户端: Windows 10, Java 21, mysql-connector 5.1.45
hints :
- MySQL8 以及 mysql-connector-java 8 都是不能触发这个问题(至少我的测试案例是这个样子).
Mysql创建以及初始化
centos7.9
docker
初始化, 省略
mysql 5.7
mkdir /root/servers/mysql
mkdir /root/servers/mysql/conf
vim docker-compose.yml
vim /conf/my.conf docke-compose.yml
version: '3'
services:
mysql:
image: mysql:5.7
container_name: test-mysql
environment:
MYSQL_ROOT_PASSWORD: aa12321.
MYSQL_USER: test
MYSQL_PASSWORD: test123
ports:
- "13306:3306"
volumes:
- ./data:/var/lib/mysql
- ./conf/my.cnf:/etc/mysql/my.cnf
command:
--character-set-server=utf8mb4
--collation-server=utf8mb4_unicode_ci
restart: always
./conf/my.conf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_connections=1000
net_write_timeout=60
net_read_timeout=1
wait_timeout=28800
interactive_timeout=28800
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4Java测试代码:
package com.example.demo;
import java.sql.*;
import java.util.Properties;
public class Test {
// private static final String URL = "jdbc:mysql://win-vm:3306/fun_demo?serverTimezone=UTC";
private static final String IP = "win-vm";
private static final String PORT = "13306";
private static final String DBNAME = "testdb";
private static final String USERNAME = "root";
private static final String PASSWORD = "aa12321.";
private static String url = "jdbc:mysql://" + IP + ":" + PORT + "/" + DBNAME + "?serverTimezone=UTC&useSSL=false";
private static final String sql = "SELECT timestamp, id, count FROM data_table ORDER BY timestamp, id";
//
public static void main(String args[]) throws NumberFormatException, InterruptedException, ClassNotFoundException {
generateTestData();
testNetWriteTimeout();
// testSocketTimeout();
}
private static void generateTestData() {
try {
Properties props = new Properties();
props.setProperty("user", USERNAME);
props.setProperty("password", PASSWORD);
try (Connection conn = DriverManager.getConnection(url, props)) {
// 创建测试表
try (Statement stmt = conn.createStatement()) {
stmt.execute("DROP TABLE IF EXISTS data_table");
stmt.execute("CREATE TABLE data_table (" +
"timestamp BIGINT NOT NULL, " +
"id BIGINT NOT NULL, " +
"count BIGINT NOT NULL, " +
"data TEXT, " + // 添加大字段
"PRIMARY KEY (timestamp, id))");
// 插入大量数据
System.out.println("开始插入测试数据...");
for (int i = 0; i < 100000; i++) {
String data = "A".repeat(1000); // 每行插入1KB的数据
stmt.execute(String.format(
"INSERT INTO data_table VALUES (%d, %d, %d, '%s')",
1734084150084L + i, i, 60798 + i, data
));
if (i % 1000 == 0) {
System.out.println("已插入 " + i + " 条记录");
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static void testNetWriteTimeout() throws ClassNotFoundException, InterruptedException {
// Class.forName("com.mysql.cj.jdbc.Driver");
Class.forName("com.mysql.jdbc.Driver");
// url = args[0];
String user = USERNAME;
String pass = PASSWORD;
// String sql = args[3];
String interval = "3000";
Properties props = new Properties();
props.setProperty("user", USERNAME);
props.setProperty("password", PASSWORD);
props.setProperty("socketTimeout","1459");
props.setProperty("netTimeoutForStreamingResults", "1"); // 设置为1秒
try (Connection conn = DriverManager.getConnection(url, props)) {
// try {
// Connection conn = DriverManager.getConnection(url, user, pass);
while (true) {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.setFetchSize(Integer.MIN_VALUE);
long start = System.currentTimeMillis();
ResultSet rs = stmt.executeQuery(sql);
int count = 0;
while (rs.next()) {
System.out.println("id:" + rs.getInt("id") + " count:" + count);
count++;
if (count < 3) //1 秒后数据库端连接就已经关闭了，但是因为客户端读得慢，需要不 sleep 后才能读到 fin 然后报错，所以报错可以比实际晚很久
Thread.sleep(1500);
}
rs.close();
stmt.close();
Thread.sleep(Long.valueOf(interval));
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}hint:
如果你也想用这段代码进行测试, socketTimeout 以及 'net_write_timeout,
更换
异常信息
id:37633 count:37633
id:37634 count:37634
id:37635 count:37635
id:37636 count:37636
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout' on the server.
at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3459)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3900)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1996)
at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:374)
at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:354)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6312)
at com.example.demo.Test.testNetWriteTimeout(Test.java:97)
at com.example.demo.Test.main(Test.java:28)
Suppressed: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@5450ce0d is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:869)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:865)
at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3214)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2450)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)
at com.mysql.jdbc.ConnectionImpl.rollbackNoChecks(ConnectionImpl.java:4672)
at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4564)
at com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4202)
at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1462)
at com.example.demo.Test.testNetWriteTimeout(Test.java:83)
... 1 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/sun.nio.ch.NioSocketImpl.timedRead(NioSocketImpl.java:278)
at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:304)
at java.base/sun.nio.ch.NioSocketImpl.read(NioSocketImpl.java:346)
at java.base/sun.nio.ch.NioSocketImpl$1.read(NioSocketImpl.java:796)
at java.base/java.net.Socket$SocketInputStream.read(Socket.java:1099)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3008)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3519)
... 9 more
产生原因分析
‘net_write_timeout’
详解 请见[plantegg-一个历时5年的问题分析
流程:
sequenceDiagram
participant Client as JDBC客户端
participant Server as MySQL服务器
Note over Client,Server: 初始化阶段
Client->>Server: 建立数据库连接
Server-->>Client: 连接成功
Note over Client: 配置参数<br/>socketTimeout=1459ms<br/>netTimeoutForStreamingResults=1s
Note over Client: 设置流式查询<br/>setAutoCommit(false)<br/>setFetchSize(Integer.MIN_VALUE)
Client->>Server: 执行查询: SELECT * FROM data_table
Note over Server: 开始准备结果集
Server-->>Client: 返回第一批数据
Note over Client: 开始处理第一条数据
Client->>Client: Thread.sleep(1500ms)
Note over Server: net_write_timeout计时开始(1s)
Note over Server: 等待客户端处理...
Note over Server: 1秒后超时
Server->>Server: 关闭连接
Note over Client: 仍在sleep(1500ms)
Client->>Server: 尝试读取下一条数据
Server-->>Client: 连接已关闭
Note over Client: 抛出CommunicationsException原因:
- MySQL服务端:
- 见
.conf/my.conf中net_read_timeout=1超时时间过低.
- 见
- JDBC client 端:
-
Connection关闭自动提交 Statement设置的FetchSize() 过小.
-
props.setProperty("netTimeoutForStreamingResults", "1"); // 设置为1秒
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.setFetchSize(Integer.MIN_VALUE);
排查问题思路建议:
优先判断MySQL 端
net_write_timeout设置是否过小(将数值调大,判断问题是否会重复出现).其次检查JDBC 连接配置是否存在异常.
判断
Connection是否配置自动提交, 以及FetchSize 大小
SocketTimeoutException: Read timed out
原因:
JDBC client连接时配置的超时时间过低, 会在大量慢查询过程时出现连接超时.
props.setProperty("socketTimeout","1459");
props.setProperty("netTimeoutForStreamingResults", "1"); // 设置为1秒题外话 - AIAnalysis(🙈)
**Using cursor and claude 3.5 sonnet **
在你的代码中，SocketTimeoutException: Read timed out 产生的原因分析如下：
关键配置和代码：
// 关键配置
props.setProperty("socketTimeout","1459"); // 设置socket读取超时为1.459秒
props.setProperty("netTimeoutForStreamingResults", "1"); // 设置流式结果集超时为1秒
// 关键代码
stmt.setFetchSize(Integer.MIN_VALUE); // 启用流式处理
Thread.sleep(1500); // 每条记录处理耗时1.5秒超时发生的过程：
客户端 服务器端
| |
|--- 发送查询 --------------------------->|
|<-- 返回第一批结果 ---------------------|
| 处理数据(sleep 1.5秒) | net_write_timeout=1秒
| | 等待客户端读取
| | 超时关闭连接
| 继续尝试读取 ---------------------->|
| 收到连接关闭通知 |
| 抛出SocketTimeoutException |具体原因：
- 使用了流式处理（stmt.setFetchSize(Integer.MIN_VALUE)）
- 客户端处理每条记录耗时1.5秒（Thread.sleep(1500)）
- 服务器端net_write_timeout设置为1秒
- 当服务器等待客户端读取数据超过1秒时，服务器关闭连接
- 客户端尝试继续读取数据时，发现连接已关闭，抛出超时异常
验证方法：
// 修改后的测试代码
private static void testNetWriteTimeout() throws ClassNotFoundException {
Properties props = new Properties();
props.setProperty("user", USERNAME);
props.setProperty("password", PASSWORD);
props.setProperty("socketTimeout", "1459");
props.setProperty("netTimeoutForStreamingResults", "1");
try (Connection conn = DriverManager.getConnection(url, props)) {
conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
stmt.setFetchSize(Integer.MIN_VALUE);
System.out.println("开始查询...");
long startTime = System.currentTimeMillis();
try (ResultSet rs = stmt.executeQuery(sql)) {
int count = 0;
while (rs.next()) {
long currentTime = System.currentTimeMillis();
System.out.printf("处理第%d条记录, 耗时：%d毫秒%n",
count++, (currentTime - startTime));
if (count < 3) {
System.out.println("开始等待1.5秒...");
Thread.sleep(1500);
System.out.println("等待结束");
}
}
}
}
} catch (Exception e) {
System.out.println("异常发生时间：" + System.currentTimeMillis());
e.printStackTrace();
}
}解决方案：
a. 增加服务器超时时间：
SET GLOBAL net_write_timeout = 600;b. 使用分页查询替代流式查询：
private static void testWithPagination() {
try (Connection conn = DriverManager.getConnection(url, props)) {
String sql = "SELECT * FROM data_table ORDER BY timestamp, id LIMIT ?, ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
int pageSize = 1000;
int offset = 0;
while (true) {
stmt.setInt(1, offset);
stmt.setInt(2, pageSize);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) break;
do {
// 处理数据
Thread.sleep(100);
} while (rs.next());
offset += pageSize;
}
}
}
}c. 增加客户端超时时间：
props.setProperty("socketTimeout", "30000"); // 30秒
props.setProperty("netTimeoutForStreamingResults", "30"); // 30秒d. 优化数据处理速度：
// 使用批处理
while (rs.next()) {
// 批量处理数据，避免每条记录都sleep
if (batchCount++ % 100 == 0) {
// 每100条处理一次
processBatch();
}
}这个问题的本质是流式处理时客户端处理速度跟不上服务器发送数据的速度，导致服务器端超时关闭连接。在实际应用中，应该根据具体场景选择合适的查询方式和超时设置。
总结:
net_write_timeout超时原因:- MySQL配置
net_write_timeout时间过小 - JDBC 配置存在问题,
connection没有设置自动提交, 以及statement对应的fetchsize过小
- MySQL配置
net_write_timeout解决方案:- MySQL 中
my.conf增大net_write_timeout超时时间, 默认值600 - 检查JDBC, 设置自动提交, 以及 增大
fetchSize, 以及最重要的, 优化查询代码,避免慢查询.
- MySQL 中
SocketTimeoutException原因:- JDBC 配置的超时时间过低
- 必要情况需要一并检查MySQL的配置信息
SocketTimeoutException解决方案:- 修改JDBC配置, 对超时时间设置一个合理值.(+++++)
Reference:
- plantegg-一个历时5年的问题分析