Bug #107107 Redundant "Reset stmt" when setting useServerPrepStmts&cachePrepStmts to true
Submitted: 23 Apr 2022 4:00 Modified: 23 Apr 2022 16:09
Reporter: Yuankai Xue Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: prepared statement caches

[23 Apr 2022 4:00] Yuankai Xue
Description:
When setting both useServerPrepStmts and cachePrepStmts to true, my general log looks like:
7 Prepare select * from test where id=?
7 Execute select * from test where id=1
7 Reset stmt
7 Execute select * from test where id=2
7 Reset stmt
7 Execute select * from test where id=3

It seems like the prepared statement cache does take effect but there're unexpected "Reset stmt" which cause extra round-trips.

My original question can be found here which contains more details like related source code.
https://forums.mysql.com/read.php?39,704080,704080#msg-704080

How to repeat:
I'm using MySQL Connector/J (both version 8.0.28 and 8.0.13 have been tested) as the driver on client side. Following is part of my code for test purpose:
PreparedStatement ps = null;
String url = "jdbc:mysql://10.86.49.16:3306/test?useServerPrepStmts=true&cachePrepStmts=true";
...
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "select * from test where id=?";
for (int i = 1; i <= 3; i++) {
ps = conn.prepareStatement(sql);
ps.setInt(1, i);
ps.execute();
ps.close();
}

When setting "useServerPrepStmts=true" alone, my general log looks like this:
6 Prepare select * from test where id=?
6 Execute select * from test where id=1
6 Close stmt
6 Prepare select * from test where id=?
6 Execute select * from test where id=2
6 Close stmt
6 Prepare select * from test where id=?
6 Execute select * from test where id=3
6 Close stmt

However after enabling "cachePrepStmts" together, the log becomes:
7 Prepare select * from test where id=?
7 Execute select * from test where id=1
7 Reset stmt
7 Execute select * from test where id=2
7 Reset stmt
7 Execute select * from test where id=3

Suggested fix:
I've checked the following code pieces in github and seems like the logic has been changed five years ago. In my case, when closing the prepared statement, the clearServerParameters=true and hadLongData=false which results in this.queryBindings.setLongParameterSwitchDetected(true). I think it should be set to false instead of true in this case.

com.mysql.cj.ServerPreparedQuery#clearParameters

/**
 * @param clearServerParameters
 *            flag indicating whether we need an additional clean up
 */
public void clearParameters(boolean clearServerParameters) {
    boolean hadLongData = false;
    if (this.queryBindings != null) {
        hadLongData = this.queryBindings.clearBindValues();
        this.queryBindings.setLongParameterSwitchDetected(clearServerParameters && hadLongData ? false : true);
    }
    ...
}
[23 Apr 2022 16:09] MySQL Verification Team
Hello Yuankai Xue,

Thank you for the report and test case.
Verified as described.

regards,
Umesh