DataSource.getConnection() hanging with offline oracle datab

[ewlyahoocom]

[I'm sorry if this is being covered elsewhere. I'm having trouble with jboss.org's search engine (trying to search on more than one word). If anyone could send me links to the articles I would appreciate it.]

I'm seeing a problem where my JDBC connection pool is getting hung up (see the thread dump below) when Blocking=true and I'm executing updates against an Oracle database when the database goes offline. I'm using the Oracle thin client JDBC driver. This doesn't appear to happen when I go against MySQL.

I'll get a java.sql.SQLException: No more data to read from socket (see the stack trace below the thread dump). I've get backoff/retry code around the call so it calls DataSource.getConnection() after a second and at that point it appears to hang (or that one will fail and the next call to DataSource.getConnection() will hang). Setting GCEnabled=true and IdleTimeoutEnabled=true does not help. My workaround is set Blocking=false and add backoff/retry code around the call the DataSource.getConnection().

Is this a known issue and/or is it fixed?

Any help appreciated.

Thanks,
Emery

Version info:

JBoss-2.4.3_Tomcat-3.2.3

Linux 2.4.9-12 #1 Tue Oct 30 18:33:49 EST 2001 i686 unknown.

java version "1.3.1_03"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_03-b03)
Java HotSpot(TM) Client VM (build 1.3.1_03-b03, mixed mode)

"Thread-14" prio=1 tid=0x844b008 nid=0x2ea1 waiting on monitor [0x4cc6f000..0x4cc6f870]
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:415)
at org.jboss.pool.ObjectPool.getObject(ObjectPool.java:599)
at org.jboss.pool.ObjectPool.getObject(ObjectPool.java:538)
at org.jboss.pool.jdbc.JDBCPoolDataSource.getConnection(JDBCPoolDataSource.java:161)
at com.genscape.db.DataBase0$PooledConnectionManager.getConnection(DataBase0.java:110)
at com.genscape.db.DataBase0.getConnection(DataBase0.java:62)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:570)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:678)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:685)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:692)
at com.genscape.db.DataBase$37.call(DataBase.java:1526)
at com.genscape.db.DataBase0.doCall(DataBase0.java:235)
at com.genscape.db.DataBase.updateUnitLastContact(DataBase.java:1520)
at com.genscape.test.Test.test(Test.java:92)
at java.lang.reflect.Method.invoke(Native Method)
at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl.java:1628)
at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl.java:1523)
at com.sun.jdmk.comm.HtmlInvokePage.buildPage(HtmlInvokePage.java:240)
at com.sun.jdmk.comm.HtmlRequestHandler.processGetRequest(HtmlRequestHandler.java:325)
at com.sun.jdmk.comm.HtmlRequestHandler.processRequest(HtmlRequestHandler.java:152)
at com.sun.jdmk.comm.HtmlRequestHandler.doRun(HtmlRequestHandler.java:79)
at com.sun.jdmk.comm.ClientHandler.run(ClientHandler.java:84)
at java.lang.Thread.run(Thread.java:479)



java.sql.SQLException: No more data to read from socket
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:944)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:741)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:700)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:373)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:753)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1852)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
at com.p6.engine.spy.P6PreparedStatement.executeUpdate(P6PreparedStatement.java:90)
at org.jboss.pool.jdbc.PreparedStatementInPool.executeUpdate(PreparedStatementInPool.java:82)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:605)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:678)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:685)
at com.genscape.db.DataBase0.executeUpdate(DataBase0.java:692)
at com.genscape.db.DataBase$37.call(DataBase.java:1526)
at com.genscape.db.DataBase0.doCall(DataBase0.java:235)
at com.genscape.db.DataBase.updateUnitLastContact(DataBase.java:1520)
at com.genscape.test.Test.test(Test.java:92)
at java.lang.reflect.Method.invoke(Native Method)
at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl.java:1628)
at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl.java:1523)
at com.sun.jdmk.comm.HtmlInvokePage.buildPage(HtmlInvokePage.java:240)
at com.sun.jdmk.comm.HtmlRequestHandler.processGetRequest(HtmlRequestHandler.java:325)
at com.sun.jdmk.comm.HtmlRequestHandler.processRequest(HtmlRequestHandler.java:152)
at com.sun.jdmk.comm.HtmlRequestHandler.doRun(HtmlRequestHandler.java:79)
at com.sun.jdmk.comm.ClientHandler.run(ClientHandler.java:84)
at java.lang.Thread.run(Thread.java:479)

[vad]

Hi,

I'm just a jboss3.0 user, but having run in a similar problem, here is my experience on that matter (I maybe completly wrong).

So you try and connect to a database which is down, and you would expect to get an SQLException "cannot connect to the database" or something like that, but you are blocked in getConnection().

This is because the pool tries and create a new connection, it delegates this to the connectionFactory, but this one gets an exception, catches it and returns null to the pool and you are blocked on those lines of code that you can easily understand :

result = createNewObject(parameters, true);
if(result != null)
return result;

if(shouldBlock) {
log("Pool "+this+" waiting for a free object");
synchronized(this) {
try {
if (blockingTimeout > 0) {
wait(blockingTimeout);
shouldBlock = false; //don't wait again
} else {
wait(); <-- here
}
} catch(InterruptedException e) {}
}
} else {
break;
}
}


Blocking=false is a workaround, but you then have to test for null after getConnection() and guess yourself the reason of this null.

As for me I have reimplemented all the pooling and connection manager, well, paste and copy jboss code, just allow them to throw exception on creation (instead of returning null).

Hope this can help you to understand what's goning on...

Vad