Bug #66947 Calling ServerPreparedStatement.close() twice corrupts cached statements
Submitted: 24 Sep 2012 14:57 Modified: 21 May 2014 14:32
Reporter: Tom Wieczorek Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.21, 5.1.22 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: regression

[24 Sep 2012 14:57] Tom Wieczorek
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).
[14 Dec 2012 18:27] Sveta Smirnova
Thank you for the report.

Verified as described. Version 5.1.18 not affected.
[14 Dec 2012 19:00] Sveta Smirnova
test case for testsuite, add &useServerPrepStmts=true&cachePrepStmts=true to connection properties

Attachment: bug66947.java (text/x-java), 1.49 KiB.

[21 May 2014 14:32] Daniel So
Added an entry to the Connector/J 5.1.31 changelog:

"When closing a server-prepared statement twice and cachePrepStmts=true, the second call closed the statement as expected. However, if a call of Connection.prepareStatement() was made again with exactly same SQL string, Connector/J obtained the closed statement from the cache and failed by throwing an exception. With this fix, ServerPreparedStatement.close() detects and ignores subsequent close() calls on cached statements that have already been closed."