Re: SQLException Socket Closed with Oracle 9 and RC5
My mail's been screwy, FWIW, I've fixed it. it was my fault. On a select statement, I had not opened and closed the transaction properly and it left orphaned connections out there, which got closed at some point, however it seems OJB was not aware of it, and it tried to reuse them and it failed. Thanks R [EMAIL PROTECTED] said: For some reason, it looks like OJB doesn't know when a connection has been closed, or there is something wrong with my setup. I'm using OJB rc5, with Oracle 9.2.0, and ojdbc4.jar (for JDK 1.4.x). It seems that when the app is not used and left idle, that connections are closed, but then when it is hit again, it tries to use the connections, throws the exception, and then notes the socket as closed. here is my repository_database entry: !-- Datasource for Acronyms -- jdbc-connection-descriptor jcd-alias=acronyms default-connection=true platform=Oracle jdbc-level=2.0 driver=oracle.jdbc.OracleDriver protocol=jdbc subprotocol=oracle dbalias=thin:@hpspdb1:1521:HPSPDB username=uname password=upass eager-release=true batch-mode=false useAutoCommit=0 ignoreAutoCommitExceptions=true connection-pool maxActive=1 maxIdle=2 maxWait=3 minEvictableIdleTimeMillis=4 numTestsPerEvictionRun=5 testOnBorrow=true testOnReturn=true testWhileIdle=true timeBetweenEvictionRunsMillis=6 whenExhaustedAction=2 validationQuery= logAbandoned=true removeAbandoned=true removeAbandonedTimeout=8/ sequence-manager className=org.apache.ojb.broker.util.sequence.SequenceManagerHighLowImpl attribute attribute-name=grabSize attribute-value=100/ attribute attribute-name=autoNaming attribute-value=true/ attribute attribute-name=globalSequenceId attribute-value=false/ attribute attribute-name=globalSequenceStart attribute-value=1/ /sequence-manager /jdbc-connection-descriptor here is the exception: java.sql.SQLException: Io exception: Socket closed at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:389) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:413) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:119) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:92) at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950) at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsIterator.init(Unknown Source) at org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at gov.hhs.acronyms.sql.AcronymDAO.selectAcronyms(AcronymDAO.java:80) at gov.hhs.acronyms.actions.SearchAction.execute(SearchAction.java:68) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:165) at
RE: SQLException Socket Closed with Oracle 9 and RC5
The only way OJB can know whether the connection has been closed is if you set it up to test the connections in the pool. Your connection descriptor is set up halfway to do that, it specifies testOnBorrow, testOnReturn and testWhileIdle but you haven't provided a validationQuery for it to test with. I'd make the following changes: validationQuery=SELECT 1 FROM dual testOnReturn=false (Having the pool test both when the connection is returned and when it's borrowed is just way overkill.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 9:57 AM To: [EMAIL PROTECTED] Subject: SQLException Socket Closed with Oracle 9 and RC5 For some reason, it looks like OJB doesn't know when a connection has been closed, or there is something wrong with my setup. I'm using OJB rc5, with Oracle 9.2.0, and ojdbc4.jar (for JDK 1.4.x). It seems that when the app is not used and left idle, that connections are closed, but then when it is hit again, it tries to use the connections, throws the exception, and then notes the socket as closed. here is my repository_database entry: !-- Datasource for Acronyms -- jdbc-connection-descriptor jcd-alias=acronyms default-connection=true platform=Oracle jdbc-level=2.0 driver=oracle.jdbc.OracleDriver protocol=jdbc subprotocol=oracle dbalias=thin:@hpspdb1:1521:HPSPDB username=uname password=upass eager-release=true batch-mode=false useAutoCommit=0 ignoreAutoCommitExceptions=true connection-pool maxActive=1 maxIdle=2 maxWait=3 minEvictableIdleTimeMillis=4 numTestsPerEvictionRun=5 testOnBorrow=true testOnReturn=true testWhileIdle=true timeBetweenEvictionRunsMillis=6 whenExhaustedAction=2 validationQuery= logAbandoned=true removeAbandoned=true removeAbandonedTimeout=8/ sequence-manager className=org.apache.ojb.broker.util.sequence.SequenceManagerHighLowImpl attribute attribute-name=grabSize attribute-value=100/ attribute attribute-name=autoNaming attribute-value=true/ attribute attribute-name=globalSequenceId attribute-value=false/ attribute attribute-name=globalSequenceStart attribute-value=1/ /sequence-manager /jdbc-connection-descriptor here is the exception: java.sql.SQLException: Io exception: Socket closed at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:389) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:413) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:119) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:92) at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950) at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsIterator.init(Unknown Source) at org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at gov.hhs.acronyms.sql.AcronymDAO.selectAcronyms(AcronymDAO.java:80) at gov.hhs.acronyms.actions.SearchAction.execute(SearchAction.java:68) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
Re: SQLException Socket Closed with Oracle 9 and RC5
Lance, thanks for answer. Seems my post (this morning) was lost in space ;-) regards, Armin Lance Eason wrote: The only way OJB can know whether the connection has been closed is if you set it up to test the connections in the pool. Your connection descriptor is set up halfway to do that, it specifies testOnBorrow, testOnReturn and testWhileIdle but you haven't provided a validationQuery for it to test with. I'd make the following changes: validationQuery=SELECT 1 FROM dual testOnReturn=false (Having the pool test both when the connection is returned and when it's borrowed is just way overkill.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 9:57 AM To: [EMAIL PROTECTED] Subject: SQLException Socket Closed with Oracle 9 and RC5 For some reason, it looks like OJB doesn't know when a connection has been closed, or there is something wrong with my setup. I'm using OJB rc5, with Oracle 9.2.0, and ojdbc4.jar (for JDK 1.4.x). It seems that when the app is not used and left idle, that connections are closed, but then when it is hit again, it tries to use the connections, throws the exception, and then notes the socket as closed. here is my repository_database entry: !-- Datasource for Acronyms -- jdbc-connection-descriptor jcd-alias=acronyms default-connection=true platform=Oracle jdbc-level=2.0 driver=oracle.jdbc.OracleDriver protocol=jdbc subprotocol=oracle dbalias=thin:@hpspdb1:1521:HPSPDB username=uname password=upass eager-release=true batch-mode=false useAutoCommit=0 ignoreAutoCommitExceptions=true connection-pool maxActive=1 maxIdle=2 maxWait=3 minEvictableIdleTimeMillis=4 numTestsPerEvictionRun=5 testOnBorrow=true testOnReturn=true testWhileIdle=true timeBetweenEvictionRunsMillis=6 whenExhaustedAction=2 validationQuery= logAbandoned=true removeAbandoned=true removeAbandonedTimeout=8/ sequence-manager className=org.apache.ojb.broker.util.sequence.SequenceManagerHighLowImpl attribute attribute-name=grabSize attribute-value=100/ attribute attribute-name=autoNaming attribute-value=true/ attribute attribute-name=globalSequenceId attribute-value=false/ attribute attribute-name=globalSequenceStart attribute-value=1/ /sequence-manager /jdbc-connection-descriptor here is the exception: java.sql.SQLException: Io exception: Socket closed at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:389) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:413) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:119) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:92) at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950) at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsIterator.init(Unknown Source) at org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at gov.hhs.acronyms.sql.AcronymDAO.selectAcronyms(AcronymDAO.java:80) at gov.hhs.acronyms.actions.SearchAction.execute(SearchAction.java:68) at
Re: SQLException Socket Closed with Oracle 9 and RC5
Hi Robert, OJB checks per default with connection.isClosed() before return a connection from the pool. But most jdbcDrivers don't recognize timed out connections closed by the server. To do a more serious check you can use attribute 'validationQuery' (e.g. SELECT 1 FROM DUAL) if you don't have success to allow re-connect of timed out connection. regards, Armin [EMAIL PROTECTED] wrote: For some reason, it looks like OJB doesn't know when a connection has been closed, or there is something wrong with my setup. I'm using OJB rc5, with Oracle 9.2.0, and ojdbc4.jar (for JDK 1.4.x). It seems that when the app is not used and left idle, that connections are closed, but then when it is hit again, it tries to use the connections, throws the exception, and then notes the socket as closed. here is my repository_database entry: !-- Datasource for Acronyms -- jdbc-connection-descriptor jcd-alias=acronyms default-connection=true platform=Oracle jdbc-level=2.0 driver=oracle.jdbc.OracleDriver protocol=jdbc subprotocol=oracle dbalias=thin:@hpspdb1:1521:HPSPDB username=uname password=upass eager-release=true batch-mode=false useAutoCommit=0 ignoreAutoCommitExceptions=true connection-pool maxActive=1 maxIdle=2 maxWait=3 minEvictableIdleTimeMillis=4 numTestsPerEvictionRun=5 testOnBorrow=true testOnReturn=true testWhileIdle=true timeBetweenEvictionRunsMillis=6 whenExhaustedAction=2 validationQuery= logAbandoned=true removeAbandoned=true removeAbandonedTimeout=8/ sequence-manager className=org.apache.ojb.broker.util.sequence.SequenceManagerHighLowImpl attribute attribute-name=grabSize attribute-value=100/ attribute attribute-name=autoNaming attribute-value=true/ attribute attribute-name=globalSequenceId attribute-value=false/ attribute attribute-name=globalSequenceStart attribute-value=1/ /sequence-manager /jdbc-connection-descriptor here is the exception: java.sql.SQLException: Io exception: Socket closed at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:389) at oracle.jdbc.driver.OracleStatement.init(OracleStatement.java:413) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:119) at oracle.jdbc.driver.OraclePreparedStatement.init(OraclePreparedStatement.java:92) at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950) at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.prepareStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementsForClassImpl.getPreparedStmt(Unknown Source) at org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(Unknown Source) at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown Source) at org.apache.ojb.broker.accesslayer.RsIterator.init(Unknown Source) at org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(Unknown Source) at gov.hhs.acronyms.sql.AcronymDAO.selectAcronyms(AcronymDAO.java:80) at gov.hhs.acronyms.actions.SearchAction.execute(SearchAction.java:68) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:165) at javax.servlet.http.HttpServlet.service(HttpServlet.java:103) at
