Search code examples
javaspringapache-tomeetomee-7

Getting occasional "Failed to validate a newly established connection" SQL Exception on a Spring JDBCTemplate in TomEE

I have a small Spring (4.3.5) app running in TomEE (7.0.1), talking to an Oracle (11) db.

I have a single DAO class that can run two different queries.

I have two query processor tasks running these queries, on a scheduler, one every two minutes, one every three minutes. When I first saw the problem I'm going to describe, I had a single scheduled task running both queries.

Both queries are similar, just returning different columns and aggregates on the same table. The two queries are assembled in code, but they aren't really dynamic. The components are relatively static. I've verified that both queries are fine by running in the debugger and pasting the generated query into my SQL browser, and they both work fine.

When I start up the app, I get results from both queries a couple of times. Then, I started to see the following in the log:

Jan 19, 2017 9:21:05 AM org.apache.tomcat.jdbc.pool.PooledConnection validate
WARNING: SQL Validation error
java.lang.NullPointerException
    at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:973)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1041)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
    at org.apache.tomcat.jdbc.pool.PooledConnection.validate(PooledConnection.java:509)
    at org.apache.tomcat.jdbc.pool.PooledConnection.validate(PooledConnection.java:443)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.testAllIdle(ConnectionPool.java:1049)
    at org.apache.tomcat.jdbc.pool.ConnectionPool$PoolCleaner.run(ConnectionPool.java:1371)
    at java.util.TimerThread.mainLoop(Timer.java:555)
    at java.util.TimerThread.run(Timer.java:505)

Jan 19, 2017 9:21:05 AM org.apache.tomcat.jdbc.pool.PooledConnection validate
WARNING: SQL Validation error
java.sql.SQLException: Invalid SQL type: sqlKind = UNINITIALIZED
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:63)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
    at org.apache.tomcat.jdbc.pool.PooledConnection.validate(PooledConnection.java:509)
    at org.apache.tomcat.jdbc.pool.PooledConnection.validate(PooledConnection.java:443)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.testAllIdle(ConnectionPool.java:1049)
    at org.apache.tomcat.jdbc.pool.ConnectionPool$PoolCleaner.run(ConnectionPool.java:1371)
    at java.util.TimerThread.mainLoop(Timer.java:555)
    at java.util.TimerThread.run(Timer.java:505)

This is clearly in pool validation, not in running my query.

My Datasource in tomee.xml looks like this:

      <Resource id="tst36" type="DataSource">
        testOnBorrow = true
        testOnReturn = true
        testWhileIdle = true
        logValidationErrors = true
        validationQuery = "select 1 from dual"
        JdbcDriver = oracle.jdbc.OracleDriver
        MaxActive = 10
        MinIdle = 2
        MaxIdle = 2 
        MaxWait = 10000
        JdbcUrl = jdbc:...
        UserName = ...
        Password = ...
  </Resource>

What can I do here?

Solution

  • are you sure

    validationQuery = "select 1 from dual"

    is what you meant? Isn't it

    validationQuery = select 1 from dual