Bug #113509 closeOnCompletion cause no statement reuse and server memory leak
Submitted: 25 Dec 2023 8:13 Modified: 7 Oct 2024 22:17
Reporter: wanyun zhou Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.33 8.1.0 8.2.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: closeOnCompletion, no statement reuse, server memory leak

[25 Dec 2023 8:13] wanyun zhou
Description:
select by cursor and call closeOnCompletion with useServerPrepStmts=true cause no statement reuse and server memory leak

please ref to the test code, same sql query 10 times
1. if call s.closeOnCompletion(), it will send COM_STMT_PREPARE everytime and not send COM_STMT_CLOSE, then will result of server memory leak
2. do not call s.closeOnCompletion(), it only send COM_STMT_PREPARE at the fist query and use statement cache the next 9 query

after the fix of https://github.com/mybatis/mybatis-3/issues/1351 (mybatis 3.5+)
SimpleExecutor.doQueryCursor will call statement.closeOnCompletion every time

How to repeat:
public class Test {
    private void Main() throws SQLException {
        String url = "jdbc:mysql://ip:port/db?useCursorFetch=true" 
                + "&useServerPrepStmts=true&cachePrepStmts=true"
                + "&profileSQL=true";
        String user = "";
        String pass = "";
        Connection c = DriverManager.getConnection(url, user, pass);
        String sql = "select * from test_table";
        for (int i = 0; i < 10; i++) {
            PreparedStatement s = c.prepareStatement(sql);
            
            s.execute();
            ResultSet rs = s.getResultSet();
            
            s.closeOnCompletion();
            
            // trigger closeOnCompletion
            while(rs.next()) {
                System.out.println("fetch 1");
            }
            rs.close();
            s.close();
        }
    }
}

if not set s.closeOnCompletion(); run well
[4 Jan 2024 12:02] Filipe Silva
Hi Wanyun Zhou,

Thank you for taking the time to report this issue and for the test case.

Verified as described. Statement close is being triggered but neither the statement is effectively closed nor it is being cached.
[7 Oct 2024 22:17] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 9.1.0 changelog:

"Setting closeOnCompletion() on a PreparedStatement made statement reuse failed in some cases, as the closing and caching of the statement were not performed correctly."
[12 Oct 2024 0:38] Daniel So
Posted by developer:
 
Updated the changelog entry to the following: 

"Setting closeOnCompletion() on a PreparedStatement made statement reuse fail in some cases, as the closing and caching of the statement were not performed correctly."