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)