MySQL Bugs: #71966: Mysql connector/j prepared statement second close cаuses trouble.

Bug #71966	Mysql connector/j prepared statement second close cаuses trouble.
Submitted:	7 Mar 2014 10:18	Modified:	22 Apr 2014 11:36
Reporter:	Denis Pimenov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.18/5.1.29	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Problem with connector/j. I created prepared statement and closed it twice. 
Then again  create prepared statement with same query and same connection. It threw exception. 

How to repeat:
This code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class MysqlPreparedStatetementTest
{

	public static void main( String[] args )
	{
		try
		{			
			
			String url = "url to dabase"			
			String user = "user";
			String password = "passwor";
			Connection con = DriverManager.getConnection( url, user, password );
			//any select query
			String query = "Select * from contract";
			
			PreparedStatement ps = con.prepareStatement( query );
			ResultSet rs =  ps.executeQuery();
			
			ps.close();
                        //second close 
			ps.close();
			
			//throws exception
                        ps = con.prepareStatement( query );
			
		}
		catch( Exception e )
		{
			
			e.printStackTrace();
		}
		
		
	}

}

result:
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:526)
	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:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
	at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:464)
	at com.mysql.jdbc.ServerPreparedStatement.checkClosed(ServerPreparedStatement.java:543)
	at com.mysql.jdbc.ServerPreparedStatement.clearParameters(ServerPreparedStatement.java:550)
	at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4570)
	at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4512)
	at bitel.billing.test.mysql.MysqlPreparedStatetementTest.main(MysqlPreparedStatetementTest.java:35)

Suggested fix:
May be clear prepared statement from serverSideStatementCache in realClose method of com.mysql.jdbc.ConnectionImpl. By I am not well understand  this code and why there is different logic after second close. Why it's really closing only after second execute of close() method.

Sorry I forgot to tell about flags useCursorFetch and cachePrepStmts.

Minimum url sample:

String url = "jdbc:mysql://192.168.184.245/bgbilling_test?cachePrepStmts=true&useCursorFetch=true";

Bug is reproduced with those flags only.

From java.sql.Statement javadoc :
"Calling the method close on a Statement object that is already closed has no effect."

Connector/J has wrong implementation.

The code above is only synthetic test to reproduce. In real life application gets connection from dbcp pool and same query may be executed on the same connection throm pool  and if close method was executed twice(by mistake), then bug reproduces.  We have found this bug on our application. We have some double closes in our code and it is difficult to find  them all.

Changed version to 5.1.18/5.1.29. It reproduces on both. OS linux(but a think it doesn't depends on OS).

Hi Denis,

Thank you for this bug report.
This issue is related to Bug#67318 and should be fixed in the same patch. Please follow Bug#67318 report progress for further details.

Ok. But these bugs are different. Here ResulySet.close, where - PreparedStatement.close. Here - exception was thrown in prepareStatemen method, where -exception was silently discarded in  ResulySet.close method. I'll wait for  Connector/J 5.1.30 to check.

Sorry. Vice versa : "Here - PreparedStatement.close, where - ResulySet.close".

I have tested 5.1.30. It's working. Thank you.