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

[25 Jun 2006 15:41] th nb
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
[27 Jun 2006 6:45] Tonci Grgin
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).
[27 Jun 2006 12:31] Mark Matthews
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.
[27 Jun 2006 20:13] Bugs System
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
[27 Jun 2006 20:24] Bugs System
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
[27 Jun 2006 20:31] Bugs System
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