JBoss Connection Socket usage in error scenarios

[dkilley]

> JBoss 4.2.1.GA

We have a HA datasource configured with both new-connection-sql and check-valid-connection-sql during a recent problem we noticed less than ideal behaviour, I was hoping someone with deeper knowledge could confirm there is nothing we can configure/control around this (and thus perhaps a bug?).

Firstly our hypothesis, when a connection is successfully made to the DB but the subsequent new-connection-sql FAILS, the object is discarded before closing the socket. Thus the socket will remain open until the next GC.

In our environment, 4k+ sockets can easily be accumilated under heavy load when the DB is down before GC's occur, thus depending on the systems file handle limit you can run out of file handles. Increasing the filehandles seems like cheating and the real solution would be to close the socket before discarding the object.


So here is what we do know, why we are thinking the above:
- Primary DB failed, secondary DB connection was switched to, life is good.
- Sockets to the secondary DB were just fine, but the SQL level check (like select 1) failed, due a permissions issue. The application is not database critical, so functionality could of continued, however each user would go through this same process, each time a socket would be left open, eventually a starvation of file handles happened.


Datasource file
<?xml version="1.0" encoding="UTF-8"?>

<ha-local-tx-datasource>
<jndi-name>blahDS</jndi-name>
<connection-url>database.url</connection-url>
<url-delimiter>|</url-delimiter>
<driver-class>database.driver[JTDS]</driver-class>
<user-name>database.user</user-name>
database.password
<check-valid-connection-sql>SELECT * FROM MonitorTable</check-valid-connection-sql>
<new-connection-sql>SELECT * FROM MonitorTable</new-connection-sql>
<min-pool-size>20</min-pool-size>
<max-pool-size>40</max-pool-size>
<track-statements>warning</track-statements>
<track-connection-by-tx>true</track-connection-by-tx>
<statement-cache-size>20</statement-cache-size>
<set-tx-query-timeout>true</set-tx-query-timeout>
<query-timeout>30</query-timeout>
<background-validation>true</background-validation>
<background-validation-minutes>15</background-validation-minutes>
<idle-timeout-minutes>13</idle-timeout-minutes>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<use-fast-fail>true</use-fast-fail>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
</ha-local-tx-datasource>


Any assistance would be appreciated, just let me know what other information may be appropriate.

Thanks
Dan

[vickyk]

"dkilley" wrote:
>
- Sockets to the secondary DB were just fine, but the SQL level check (like select 1) failed, due a permissions issue. The application is not database critical, so functionality could of continued, however each user would go through this same process, each time a socket would be left open, eventually a starvation of file handles happened.

If the match fails the Jboss Pool implementation does call the destroy() on the related ManagedConnection object , the destroy implementation basically closes the SQL connection , have a look at the code

public void destroy() throws ResourceException
 {
 synchronized (stateLock)
 {
 destroyed = true;
 }

 cleanup();
 try
 {
 con.close();
 }
 catch (SQLException ignored)
 {
 getLog().trace("Ignored error during close: ", ignored);
 }
 }

http://anonsvn.jboss.org/repos/jbossas/branches/Branch_4_2/connector/src/main/org/jboss/resource/adapter/jdbc/BaseWrapperManagedConnection.java

You can enable the log trace to see if the SQL connection close() in getting called if the match connection fails .
We certainly need more details/evidents to sort this out .

[vickyk]

Enable the log trace on InternalManagedConnectionPool to find out if the connection is destroyed when matching fails .
http://anonsvn.jboss.org/repos/jbossas/branches/Branch_4_2/connector/src/main/org/jboss/resource/connectionmanager/InternalManagedConnectionPool.java

Check this warn message

log.warn("Destroying connection that could not be successfully matched: " + cl);

[dkilley]

Ok, I will try to gather the log data and report back.

Thanks Vicky!

Dan

[dkilley]

Hi Vickyk,

I've enabled said debugging and will provide the actual log entries below, unfortunatly it did not reveal any meaningful exception/data, imho.

What I did notice with the information you gave me, browsing the Web Source Code, that perhaps I noticed something based ony our suggestion...


> BaseWrapperManagedConnection.java

if (mcf.getNewConnectionSQL() != null)
 {
 Statement s = con.createStatement();
 try
 {
 s.execute(mcf.getNewConnectionSQL());
 }
 finally
 {
 s.close();
 }
 }

In this case the statement is closed, but nothing is done to the connection object, up in HALocalManagedConnectionFactory we have the following

try
 {
 Driver d = getDriver(url);
 Connection con = d.connect(url, copy);
 if(con == null)
 {
 log.warn("Wrong driver class for this connection URL: " + url);
 urlSelector.failedUrl(url);
 }
 else
 {
 return new LocalManagedConnection(this, con, props, transactionIsolation, preparedStatementCacheSize);
 }
 }
 catch(Exception e)
 {
 log.warn("Failed to create connection for " + url + ": " + e.getMessage());
 urlSelector.failedUrl(url);
 }

In this case, where new LocalManagedConnection throws an exception, no call to con.close() is called, and it would only be called on the destroy.

What I am suggesting is either a new try/finally around the New LocalManagedConnection which closes the connection if it fails, or perhaps a finally added to the existing try, which may try to con.close when connections were not successful, thus a new try inside is better?

I am not a coder so I am way out of my depth on the specific suggestions for fixing, I am just providing some suggestions ;)

Thoughts?

[adrian.brock]

I've fixed this bug http://jira.jboss.com/jira/browse/JBAS-5526