EAP 6/7 JDBC datasource not fault tolerant in JBoss and needs connection validation enabled

Solution Verified - Updated -

Issue

  • How do I enable connection validation for a datasource connection pool?
  • After performing database restart for maintenance or an outage is unable to reconnect to the database.
    • Is a restart required to re-establish database connections after a service interruption?
  • How can we gracefully handle database server initiated timeout of connections?
  • Getting stale connections from the datasource pool.
    • Is a "flush" of the pool required to create fresh connections?
  • Is there a mechanism to test connections to ensure they are valid before use?
  • How do background-validation and validate-on-match differ?
  • Do we need SELECT 1 if we have the valid-connection-checker configured?
  • What is the role of the exception-sorter class in the connection validation process?
  • Is it necessary to specify a stale-connection-checker and an exception-sorter?
  • Seeing Connection is closed errors in our server.log:

    ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/:8443-8) Closed Connection
    ERROR [controller.SearchController] (http-/:8443-8) javax.persistence.PersistenceException org.hibernate.exception.GenericJDBCException: Closed Connection
    
  • Evidence in the logs of failed Oracle connections

    java.lang.RuntimeException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1157)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
    at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:107)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
    
  • Seeing the following error when using MySQL:

    ... com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 59,828,176 milliseconds ago.  The last packet sent successfully to the server was 59,828,176 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP)
    • 6
    • 7

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase, tools, and much more.

Current Customers and Partners

Log in for full access

Log In

New to Red Hat?

Learn more about Red Hat subscriptions

Using a Red Hat product through a public cloud?

How to access this content