Menu ▾ ▴

Testing DB connection before writing a data

Help
Akshay
2014-05-06
2014-05-07

  • Akshay

    Akshay - 2014-05-06

    We have two Linux machines with Tomcat and Postgres running on each Linux node. HAJBC is configured as a DataSource resource in both the Tomcat instance. For testing purpose we executed linux Halt command on one of the linux machines and then tried to do a Database write operation using the Active Tomcat server . The first time the write operation fails with "Caused by: java.sql.SQLException: Already closed." below are the entire stack trace . After this error the subsequent write operation works. We have defined ValidationQuery as "Select 1" and testOnBorrow = true in below JDBC pool configuration. I am assuming that testOnBorrow, testOnCreate, testOnIdle will not check the db connection to both the database as validation query is "select 1" (Database read) . If the validation query was a insert statement both the DB connection would have validated as it a database write from HA-JDBC perspective. and the issue could be resolved ? is my assumption correct or does HA-JDBC test both the DB connection before borrowing from the Pool.

    HAJDBC version: ha-jdbc-2.0.16-rc-1-jdk1.6.jar

    Resource name="jdbc/project" type="javax.sql.DataSource"
    username="test" password="test" driverClassName="net.sf.hajdbc.sql.Driver"
    url="jdbc:ha-jdbc:project"
    maxActive="100"
    maxIdle="10" minIdle="5"
    maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" testWhileIdle="true"
    testOnBorrow="true" validationQuery="select 1" timeBetweenEvictionRunsMillis="300000"

    Exception:

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
    Caused by: javax.persistence.PersistenceException: unexpected error when rollbacking
    at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:110)
    at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:494)
    ... 36 more
    Caused by: org.hibernate.exception.GenericJDBCException: Cannot release connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:478)
    at org.hibernate.jdbc.ConnectionManager.aggressiveRelease(ConnectionManager.java:429)
    at org.hibernate.jdbc.ConnectionManager.afterTransaction(ConnectionManager.java:316)
    at org.hibernate.jdbc.JDBCContext.afterTransactionCompletion(JDBCContext.java:248)
    at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:208)
    at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:107)
    ... 37 more
    Caused by: java.sql.SQLException: Already closed.
    at org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.java:114)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
    at org.hibernate.connection.DatasourceConnectionProvider.closeConnection(DatasourceConnectionProvider.java:97)
    at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:474)

     

    Last edit: Akshay 2014-05-07

Log in to post a comment.