Oracle connection errors (ConnectionValidator) Destroying connection that is not valid, java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected, J000305: Connection error occured rg.jboss.jca.core.connectionmanager.listener

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal

Issue/Introduction

After creating a custom datasource for reference from an Xpress Policy there are connections errors when the number of connections to the Oracle database have been max'd out:

08:32:24,336 WARN  [org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory] (ConnectionValidator) Destroying connection that is not valid, due to the following exception: oracle.jdbc.driver.T4CConnection@32dc9902: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
    at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
    at org.jboss.jca.adapters.jdbc.CheckValidConnectionSQL.isValidConnection(CheckValidConnectionSQL.java:74)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:1100)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:513)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.getInvalidConnections(BaseWrapperManagedConnectionFactory.java:921)
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.validateConnections(SemaphoreArrayListManagedConnectionPool.java:948) [ironjacamar-core-impl-1.0.31.Final-redhat-1.jar:1.0.31.Final-redhat-1]
    at org.jboss.jca.core.connectionmanager.pool.validator.ConnectionValidator$ConnectionValidatorRunner.run(ConnectionValidator.java:277) [ironjacamar-core-impl-1.0.31.Final-redhat-1.jar:1.0.31.Final-redhat-1]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_131]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_131]
    at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_131]

08:32:24,336 WARN  [org.jboss.jca.core.connectionmanager.listener.NoTxConnectionListener] (ConnectionValidator) IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.NoTxConnectionListener@#####[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@###### connection handles=0 lastUse=1525793512385 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@2928a42c pool internal context=SemaphoreArrayListManagedConnectionPool@4074304[pool=iam_im-oristore-ds]]: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
    at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
    at org.jboss.jca.adapters.jdbc.CheckValidConnectionSQL.isValidConnection(CheckValidConnectionSQL.java:74)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:1100)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:513)
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.getInvalidConnections(BaseWrapperManagedConnectionFactory.java:921)
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.validateConnections(SemaphoreArrayListManagedConnectionPool.java:948) [ironjacamar-core-impl-1.0.31.Final-redhat-1.jar:1.0.31.Final-redhat-1]
    at org.jboss.jca.core.connectionmanager.pool.validator.ConnectionValidator$ConnectionValidatorRunner.run(ConnectionValidator.java:277) [ironjacamar-core-impl-1.0.31.Final-redhat-1.jar:1.0.31.Final-redhat-1]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_131]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_131]
    at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_131]

 

Environment

JBoss/Wildfly
Oracle DB

Resolution

Replace the existing validation section  in standalone_full-ha.xml with the below settings

<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"></valid-connection-checker>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"></stale-connection-checker>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"></exception-sorter>
</validation>