MySQL Bugs: #20687: Caching serverside prepared statements broken

Bug #20687	Caching serverside prepared statements broken
Submitted:	25 Jun 2006 15:41	Modified:	26 Jul 2006 18:17
Reporter:	th nb	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.1.13	OS:	Any (All)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
When attempting to use a cached serverside prepared statement, the following error with statck trace is thrown:
Testcase: testForDebugPurposes(org.thurston.mysql.prep.PreparedStatementTest):	Caused an ERROR
No operations allowed after statement closed.
java.sql.SQLException: No operations allowed after statement closed.
        at com.mysql.jdbc.Statement.checkClosed(Statement.java:274)
        at com.mysql.jdbc.ServerPreparedStatement.checkClosed(ServerPreparedStatement.java:411)
        at com.mysql.jdbc.ServerPreparedStatement.clearParameters(ServerPreparedStatement.java:418)
        at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4327)
        at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4272)
        at org.thurston.mysql.prep.PreparedStatementTest.testForDebugPurposes(PreparedStatementTest.java:60)

This affects any caching of server-side prepared statements (cachePrepStmts in url connection string)

How to repeat:
Use code like the following:
PreparedStatement prepStatementSearch = connectionMySQL.prepareStatement(SQL$SELECTS$PAGINATED);
        assertTrue(prepStatementSearch instanceof ServerPreparedStatement);
        prepStatementSearch.setString(1, "Am%");
        
        ResultSet results = prepStatementSearch.executeQuery();
        assertTrue(results.next());
        
        results.close();
        prepStatementSearch.close();
        
        
        
        
        //Now use the same Connection object (prepared statements are always associated with a particular Connection instance)
        prepStatementSearch = connectionMySQL.prepareStatement(SQL$SELECTS$PAGINATED);

Suggested fix:
The problem is that when close() is invoked on a ServerPreparedStatement instance,
line # 454 sets this.isClosed = true before "returning" the instance to the cache.

Then when prepareStatment("xxxx") (same sql as previous) is called on the same Connection instance:
line 4324 of Connection "retrieves" the ServerPreparedStatement from the cache and then line 4327
invokes clearParameters() on the ServerPreparedStatement instance.
line # 418 invokes checkClosed() which ends up throwing the Exception because the
instance's isClosed flag is true.

Two possible fixes:
remove the setting of isClosed to true when invoking close on a ServerPreparedStatement
or
when invoking close call clearParameters() before setting isClosed to true

Hi and thanks for your problem report. In my opinion this is not a bug but expected behavior since this functionality is missing as can be seen in DatabaseMetaData.java:
	public boolean supportsStatementPooling() throws SQLException {
		return false;
	}
What I'm not sure is wether this is a server side or connector side issue. I will consult on this matter. 
From JDBC specs:
"The connection pool and statement pool are implemented by the application server. However, this functionality could also be implemented by the driver or underlying data source. This discussion of statement pooling is meant to allow for any of these implementations."
The workaround you provided is dengerous since there's no control mechanism to limit number of pooled prepared statements which can lead to slowdown:
"In many cases, reusing statements is a significant optimization. This is especially true for complex prepared statements. However, it should also be noted that leaving large numbers of statements open may have an adverse impact on the use of resources."
In my opinion, you should set this report to severity S3 or S4 (feature request).

There's a bug in that DatabaseMetaData.supportsStatementPooling() returns "false", although we don't yet use the "standard" javax.sql interfaces for limiting the number of pooled statements.

I've verified that the bug with pooled prepared statement does exist.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8349

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8350

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8351