MySQL Bugs: #96900: statement.cancel()create a database connection but does not close the connection

Bug #96900	statement.cancel()create a database connection but does not close the connection
Submitted:	17 Sep 2019 8:43	Modified:	11 Dec 2021 16:27
Reporter:	beitian wang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	Versions 8.0.13-8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
statement.cancel(),In this method, one thread interrupts another thread that is executing and interrupts by creating a database connection, but the connection is not closed.

How to repeat:
@Test
    public void testInterupt() throws  Exception{
        Map<String,Object> map = new HashMap<>();
        map.put("ip","XXXXX");
        map.put("port","3306");
        map.put("readUserName","XXXX");
        map.put("readUserPassword","XXXXX");
        map.put("dbType",1);
        //连接 目标数据库，批量执行sql
        DBUtil2 db = new DBUtil2("webdb",map,null);
        String sql2 ="SELECT SLEEP(60)";
       Connection conn = db.getConnection(db);
        PreparedStatement statement = conn.prepareStatement(sql2);

        new Thread(()->{
            try {
                System.out.println("begin -----------------------.");
               statement.executeQuery(sql2);
            }catch (Exception e){
                System.err.println("查询报错啦");
                System.out.println("close11111111111 .....");
                e.printStackTrace();
            }

        }).start();

        Thread.sleep(2000);

        new Thread(()->{
            try {
                statement.cancel();
                System.out.println("close22222222222222222 .....");
                conn.close();
            }catch (Exception e){
                System.err.println("报错啦");
            }

        }).start();

        Thread.sleep(60 * 1000 +100);

    }

Suggested fix:
After executing the newSession.sendCommand() method, the newSession.forceClose () is finally called.

Hello Beitian,

Thank you for the report.

regards,
Umesh

Add the method "newSession.getProtocol().getSocketConnection().forceClose()" in finally{} of StatementImpl.cancel()

Add the method "newSession.getProtocol().getSocketConnection().forceClose()" in finally{} of StatementImpl.cancel()

The issue is that the two objects that are created to, theoretically, run the KILL command on MySQL are never used:
```        if (!this.isClosed && this.connection != null) {
            JdbcConnection cancelConn = null;
            java.sql.Statement cancelStmt = null;
<...>
            } finally {
                if (cancelStmt != null) {
                    cancelStmt.close();
                }

                if (cancelConn != null) {
                    cancelConn.close();
                }
            }
```

But the object that is used is a "NativeSession" one:
```
                NativeSession newSession = new NativeSession(this.session.getHostInfo(), this.session.getPropertySet());
                newSession.connect(hostInfo, user, password, database, 30000, new TransactionEventHandler() {
                    @Override
                    public void transactionCompleted() {
                    }

                    @Override
                    public void transactionBegun() {
                    }
                });
                newSession.sendCommand(new NativeMessageBuilder().buildComQuery(newSession.getSharedSendPacket(), "KILL QUERY " + this.session.getThreadId()),
                        false, 0);
                setCancelStatus(CancelStatus.CANCELED_BY_USER);
```

It should either use the "JdbcConnection" and "Statement" objects or remove them and do the correct test and cleanup on the finally section.

---
Charly

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

"A new session created for executing Statement.cancel() remained open after the Statement had been cancelled. With this fix, the session is closed after the Statement cancellation."