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