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);
}
...
}
