[jira] Commented: (DBCP-23) [dbcp] SQLException When PoolablePreparedStatement Already Closed

["Akom (JIRA)" <ji...@apache.org>]

    [ http://issues.apache.org/jira/browse/DBCP-23?page=comments#action_12458824 ] 
            
Akom commented on DBCP-23:
--------------------------

This may perhaps qualify as a separate bug but here goes anyway:

This has much bigger impact (than I've seen indicated so far) when used with
mysql connector 5.0.4 (and perhaps other versions), and commons-pool 1.3 .  
Mysql Connection's have the annoying ability to close themselves (specifically
when there is a communication breakdown with the server and they are not in High
Availability mode).  

Since the isClosed method checks both the local flag AND the underlying
connection's, it returns true, meaning that the mentioned code in close() throws
instead of returning the object to the pool!!!   This quickly starves the object
pool and causes the developer a lot of grief.

I am currently patching DBCP for internal use, replacing the close() method
in PoolableConnection with:

public synchronized void close() throws SQLException {
        boolean isClosed = false;
//        try {
            //isClosed = isClosed();
        	isClosed = _closed;
//        } catch (SQLException e) {
//            try {
//                _pool.invalidateObject(this);
//            } catch (Exception ie) {
//                // DO NOTHING the original exception will be rethrown
//            }
//            throw new SQLNestedException("Cannot close connection (isClosed check failed)", e);
//        }
        if (isClosed) {
            throw new SQLException("Already closed.");
        } else {
            try {
                _pool.returnObject(this);
            } catch(SQLException e) {
                throw e;
            } catch(RuntimeException e) {
                throw e;
            } catch(Exception e) {
                throw new SQLNestedException("Cannot close connection (return to pool failed)", e);
            }
        }
    }

While I acknowledge that the check of the wrapped connections' closed state may
be useful for something else, it is not compatible with the current close()
method - the two unmatched strategies cause a disastrous situation.

As for how to reproduce this behavior (if desired), the application I own is a
very high throughput multithreaded server running on high powered boxes which
writes medium sized chunks (20K) of data to MySQL using 50 threads concurrently,
some of the writing is done via stored procedures.  Generally the "mysql
self-closing" behavior occurs only when mysql server is overwhelmed, or if I
slow it down intentionally by doing a dump, etc.  I've also seen this problem
happen when mysql server crashes naturally or via "kill -9", or when disk is full.

References:

com.mysql.jdbc.Connection:Line 3200:
------------------------
if ((sqlState != null) &&
sqlState.equals(SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE)) 
  cleanup(sqlE);
------------------------
(Cleanup method sets it to closed)

> [dbcp] SQLException When PoolablePreparedStatement Already Closed
> -----------------------------------------------------------------
>
>                 Key: DBCP-23
>                 URL: http://issues.apache.org/jira/browse/DBCP-23
>             Project: Commons Dbcp
>          Issue Type: Bug
>    Affects Versions: 1.2
>         Environment: Operating System: All
> Platform: All
>            Reporter: JZ
>             Fix For: 1.3
>
>         Attachments: issue32441.patch
>
>
> When closing an already closed
> org.apache.commons.dbcp.PoolablePreparedStatement, a SQLException is thrown when
> the isClosed() method returns true.
> This seems to violate the contract of java.sql.Statement (super interface of
> implemented PreparedStatement) whose javadoc reads " Calling the method close on
> a Statement  object that is already closed has no effect." 
> Work around exists -- when ever closing a statement, also null out.  Then,
> before closing, check that it's non-null.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org