Wildfly + SQL Server connections closing randomly and not recovering

Question

I'm getting random "connection is closed" in Teiid 13.1.0 to SQL Server:

2021-01-08 10:20:23,949 DEBUG [org.teiid.COMMAND_LOG.SOURCE] (Worker513_QueryProcessorQueue9800) Cz9nti5G/vUr   ERROR SRC COMMAND:      endTime=2021-01-08 10:20:23.949 requestID=Cz9nti5G/vUr.0        sourceCommandID=0       executionID=9632        txID=null       modelName=customer      translatorName=sqlserver        sessionID=Cz9nti5G/vUr  principal=sforce-app-user
2021-01-08 10:20:23,949 WARN  [org.teiid.CONNECTOR] (Worker513_QueryProcessorQueue9800) Cz9nti5G/vUr Connector worker process failed for atomic-request=Cz9nti5G/vUr.0.0.9632: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['(111)111-1111'] SQL: SELECT g_0.Id AS c_0, g_0.Email AS c_1, g_0.Phone AS c_2, g_0.parent AS c_3 FROM Customer g_0 WHERE g_0.Phone = ? ORDER BY c_0 OFFSET 0 ROWS FETCH NEXT 2001 ROWS ONLY]
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)
        at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)
        at com.sun.proxy.$Proxy44.execute(Unknown Source)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:1130)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:3536)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:758)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:744)
        at org.jboss.jca.adapters.jdbc.WrappedConnection$4.produce(WrappedConnection.java:478)
        at org.jboss.jca.adapters.jdbc.WrappedConnection$4.produce(WrappedConnection.java:476)
        at org.jboss.jca.adapters.jdbc.SecurityActions.executeInTccl(SecurityActions.java:97)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:476)
        at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:198)
        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
        ... 17 more
 
2021-01-08 10:20:23,949 DEBUG [jboss.jdbc.spy] (default task-88) Cz9nti5G/vUr java:/datasources/DATASOURCE [Connection] close()
2021-01-08 10:20:23,949 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (default task-88) Cz9nti5G/vUr DATASOURCE: returnConnection(714b1b5a, false) [1/20]

Originally I was seeing this when the SQL Server was restarted: Teiid was not validating the connections in the pool and I'd have to restart Teiid to get connections back. To fix this I added

<pool>
    <flush-strategy>EntirePool</flush-strategy>
</pool>

which I tested and worked. However I am still getting "The connection is closed" errors at random times.

SQL Server marks the connections as idle after 10 minutes. I do not have a <idle-timeout-minutes> on my data source.

My configuration is:

<datasource jta="true" jndi-name="java:/datasources/DATASOURCE" pool-name="DATASOURCE" enabled="true" spy="true" use-ccm="false" statistics-enabled="true">
    <connection-url>jdbc:sqlserver://1.1.1.1:1433;DatabaseName=DATABASE</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <driver>mssql-jdbc-8.2.0.jre8.jar</driver>
    <pool>
        <flush-strategy>EntirePool</flush-strategy>
    </pool>
    <security>
        <user-name>USERNAME</user-name>
        <password>PASSWORD</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
        <background-validation>false</background-validation>
    </validation>
</datasource>

Any idea why Teiid isn't validating and rebuilding the pool when this happens? If it can detect dead connections when I reboot the SQL Server, why can it not detect the dead connections when this random unknown event happens?

How can I investigate further? I'm blind to why the connections die randomly every few days and do not know if CCM would help debug this or if I should be monitoring with netstat.

Answer 1

Teiid does not maintain the connection pools, the WildFly server does. Teiid just requests a connection and uses it when one is returned, which could a closed connection if the pool is not validated.

Validation checks seem correct above. You can alternatively follow similar techniques for validation defined here [1]

  <validation>
      <check-valid-connection-sql>select 1</check-valid-connection-sql>
      <validate-on-match>false</validate-on-match>
      <background-validation>true</background-validation>
      <background-validation-millis>10000</background-validation-millis>
  </validation>

[1] http://www.mastertheboss.com/jboss-server/jboss-datasource/how-to-automatically-reconnect-to-the-database-in-wildfly