sql-server - Wildfly + SQL Server 连接随机关闭且未恢复
问题描述
我在 Teiid 13.1.0 中随机“连接已关闭”到 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]
最初我在重新启动 SQL Server 时看到了这一点:Teiid 没有验证池中的连接,我必须重新启动 Teiid 才能恢复连接。为了解决这个问题,我添加了
<pool>
<flush-strategy>EntirePool</flush-strategy>
</pool>
我测试并工作过。但是,我仍然随机收到“连接已关闭”错误。
SQL Server 在 10 分钟后将连接标记为空闲。我的<idle-timeout-minutes>
数据源上没有。
我的配置是:
<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>
知道为什么发生这种情况时 Teiid 不验证和重建池吗?如果在我重新启动 SQL Server 时它可以检测到死连接,为什么当这个随机未知事件发生时它不能检测到死连接?
我该如何进一步调查?我不知道为什么连接每隔几天就会随机中断,也不知道 CCM 是否会帮助调试这个问题,或者我是否应该使用 netstat 进行监控。
解决方案
Teiid 不维护连接池,WildFly 服务器会。Teiid 只是请求一个连接并在返回一个连接时使用它,如果池未验证,这可能会关闭连接。
上面的验证检查似乎是正确的。您也可以按照此处定义的类似技术进行验证 [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>
推荐阅读
- node.js - 拦截网络选项卡中的特定 URL(无头)并使用 Playwright 和 Nodejs 打开它
- firebase-cloud-messaging - FCM 错误,SENDER_ID_MISMATCH “PERMISSION_DENIED”
- javascript - 如何为 Actions on Google 使用异步履行
- typescript - 为什么通用约束会产生 TS2417 错误?
- javafx - Gluon mobile - 使用 WebView 时出现编译错误
- angular - ANGULAR - 循环通过 ngFor 复选框并计算已选中的复选框
- c++ - 如何检查向量中元素之间的相等性
- terraform - Terraform:使用模块和 for_each 获取输出
- android - React Native 应用程序不会尝试在调试模式下连接到 Metro Bundler
- typescript - 在 Nativescript Angular 中以编程方式创建组件