Redundant "Reset stmt" before "Execute" when setting useServerPrepStmts and cachePrepStmts to true in MySQL Connector/J
Posted by: yuankai xue
Date: April 22, 2022 06:30AM

I'm using MySQL Connector/J (version 8.0.28) 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

Looks like the prepared statement cache does take effect but there're unexpected "Reset stmt" which cause extra round-trips. I've checked the source code of MySQL Connector/J and found a strange logic executed when closing the prepared statement. It will always set longParameterSwitchDetected to true in my 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);
}
...
}

Then when next "Execute" comes, it will check this flag and decide whether to send "Reset stmt" or not as shown below:
com.mysql.cj.ServerPreparedQuery#prepareExecutePacket

public NativePacketPayload prepareExecutePacket() {
ServerPreparedQueryBindValue[] parameterBindings = this.que
if (this.queryBindings.isLongParameterSwitchDetected()) {
...
// Okay, we've got all "newly"-bound streams, so reset server-side state to clear out previous bindings
serverResetStatement();
}
...
}

I've tried to set this longParameterSwitchDetected manually to false in debug mode and the statement executed successfully without sending the "Reset stmt". So here're my questions:
1. Are there any configurations for removing this "Reset stmt"?
2. What's the exact meaning/logic of this "longParameterSwitchDetected"?

Options: ReplyQuote


Subject
Written By
Posted
Redundant "Reset stmt" before "Execute" when setting useServerPrepStmts and cachePrepStmts to true in MySQL Connector/J
April 22, 2022 06:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.