java.sql.SQLException: Query execution was interrupted

最新推荐文章于 2025-08-01 18:04:25 发布

原创最新推荐文章于 2025-08-01 18:04:25 发布 · 4.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#query #exception #jdbc #null #server #mysql

sql 专栏收录该内容

22 篇文章

订阅专栏

本文探讨了在使用MySQL-JDBC驱动连接MySQL数据库时遇到的长时间查询超时问题。通过设置短查询超时时间来尝试解决因服务器连接超时导致的问题，并讨论了如何正确配置和检测无效连接。

try {

int TIMEOUT = 5;
String url =
"jdbc:mysql://111.111.111.111/dbname?dontTrackOpenResources=true&useTimezone=true&serverTimezone=PST&zeroDateTimeBehavior=convertToNull&characterEncoding=utf-8&user=test&password=*****&connectTimeout=5000&socketTimeout=30000";

Statement st = null;
ResultSet rs = null;
try {
	if (con==null)
	{
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		con = DriverManager.getConnection(url,"test", "****");
		DatabaseMetaData md = con.getMetaData();
		out.println("opened connection...");
		out.println(md.getDriverName()+" "+md.getDriverVersion());
		out.println(md.getDatabaseProductName()+" "+md.getDatabaseProductVersion());
	}
	st = con.createStatement();
	st.setQueryTimeout(TIMEOUT);
	rs = st.executeQuery("SELECT NOW()");
	rs.next();
	out.println(rs.getString(1));
} catch (Exception e) {
	out.println("mysql excepttion: "+e);
} finally {
	if (rs!=null)
		rs.close();
	if (st!=null)
		st.close();
}

} catch (Throwable e) {out.println("general exception: "+e);}

Set a short timeout on a long query.

This problem still exists in MySQL-AB JDBC Driver mysql-connector-java-5.1.6 connecting to
MySQL 5.0.27. The following code (JSP for convenience) will illustrate the problem after a
duration longer than the server's connection timeout.

After the timeout the query hangs indefinitely; instead I believe it should be possible
to detect that the connection is invalid by setting the Socket.setSoTimeout(int timeout)
parameter (e.g read-timeout) to be equal (or slightly larger) to the
Statement.getQueryTimeout()*1000 value. However Statement.setQueryTimeout(int secs) does
not result in the throwing of an SQLException (only an indefinate wait).

The server indeed appears not to have correctly closed the connection (netstat show
connected); however the driver should still be able to dtect the response timeout (for
the incorrect socket shutdown in addition to normal query timeouts)