MySQL Bugs: #99260: statement.setQueryTimeout,creates a database connection and does not close

Bug #99260	statement.setQueryTimeout,creates a database connection and does not close
Submitted:	15 Apr 2020 2:16	Modified:	5 Nov 2022 2:06
Reporter:	beitian wang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	8.0.13-8.0.18, 8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
statement.setQueryTimeout(10),This method creates tasks to establish database connection without closing.
When the query time-out is set and the query out of time, the scheduled task kills the query process, but a new database connection will be generated.
This problem is similar to that of statement.cancel(). A new database connection is created to close the query process, but no new connection is closed

How to repeat:
@Test
    public void testInterupt11() throws  Exception{
        Map<String,Object> map = new HashMap<>();
        map.put("ip","XX.XX.XX.XX");
        map.put("port","3306");
        map.put("readUserName","XX");
        map.put("readUserPassword","XXXX");
        map.put("dbType",1);
        DBUtil2 db = new DBUtil2("dbName",map,null);
        String sql2 ="SELECT SLEEP(300)";
        Connection conn = DBUtil2.getConnection(db);
        PreparedStatement statement = conn.prepareStatement(sql2);
        ResultSet resultSet = null;
        try {
            System.err.println("begin -----------------------.");
            statement.setQueryTimeout(10);
            resultSet = statement.executeQuery(sql2);
        }catch (Exception e){
            System.err.println("close11111111111 .....");
            System.err.println("e: "+e.getMessage()+e);
        }finally {
            closeQuietly2(conn,statement,resultSet);
        }

Suggested fix:
Add the method "newSession.getProtocol().getSocketConnection().forceClose()" in finally{} of CancelQueryTaskImpl.run ()

Hello Beitian,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
Added the following entry to the Connector/J 8.0.28 changelog: 

"After calling Statement.setQueryTimeout(), when a query timeout was reached, a connection to the server was established to terminate the query, but the connection remained open afterward. With this fix, the new connection is closed after the query termination. "

How to avoid connection leakage without upgrading JDBC 8.0.28 ？

I found that configuring the queryTimeoutKilleConnection=true parameter for JDBC 8.0.25 URL can avoid issues,do you also agree ?

88

Yes, that should work too. Just mind that the main connection gets closed as well.