Description:
When closing a server-prepared statement (useServerPrepStmts=true) twice when cachePrepStmts=true, the second call to close() will really close the statement. The next call to Connection.prepareStatement() with the exact same SQL string obtains the cached - but closed - statement from the cache and fails by throwing an exception (trace below).
The second call to ServerPreparedStatement.close() should have no effect at all, even if the statement is cached.
-------
Stack Trace thrown by Connection.prepareStatement():
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:463)
at com.mysql.jdbc.ServerPreparedStatement.checkClosed(ServerPreparedStatement.java:542)
at com.mysql.jdbc.ServerPreparedStatement.clearParameters(ServerPreparedStatement.java:549)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4269)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4212)
...
How to repeat:
Java snippet to reproduce the problem:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class PrepStmtCacheTest {
public static void main(String[] args) throws Exception {
final String jdbcUrl = "jdbc:mysql://127.0.0.1";
final Properties p = new Properties();
p.setProperty("useServerPrepStmts", "true");
p.setProperty("cachePrepStmts", "true");
final Connection c = DriverManager.getConnection(jdbcUrl, p);
try {
executeStmt(c);
executeStmt(c); // <--- the second call will fail
} finally {
c.close();
}
}
private static void executeStmt(Connection c) throws SQLException {
final PreparedStatement stmt = c.prepareStatement("select now()");
try {
final ResultSet rs = stmt.executeQuery();
try {
while (rs.next()) {
System.out.println(rs.getString(1));
}
} finally {
rs.close();
}
} finally {
stmt.close();
stmt.close(); // <--- should have no effect, but corrupts cached stmt
}
}
}
Suggested fix:
Alter ServerPreparedStatement.close() in a way that it detects and ignores subsequent close calls on cached statements that have already been closed (and re-added to the statement cache).