（本文档仅供参考）
问题描述
基于跨库联合数据源新建原生sql数据集，第一次正常，再查询一次却报错Closed Connection。业务库和跨库的测试连接一直是通过的。
| 代码块 | ||
|---|---|---|
| ||
获取数据SQL错误:SELECT
c."CC"
FROM erp.erpdb.table1 a
LEFT JOIN erp.erpdb.table2 b ON b."AA" = a."AA"
LEFT JOIN erp.erpdb.table3 c ON c."BB" = a."BB";Query failed (#20240809_074810_00296_qe2d3): Closed Connection
at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1408)
at ...(...)
at smartbi.freequery.querydata.store.DBSQLResultStore.insertIntoHsql(DBSQLResultStore.java:1653)
Caused by: java.sql.SQLException: Query failed (#20240809_074810_00296_qe2d3): Closed Connection
at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1841)
at com.facebook.presto.jdbc.PrestoResultSet$ResultsPageIterator.computeNext(PrestoResultSet.java:1821)
at com.facebook.presto.jdbc.PrestoResultSet$ResultsPageIterator.computeNext(PrestoResultSet.java:1760)
at com.facebook.presto.jdbc.internal.guava.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141)
at com.facebook.presto.jdbc.internal.guava.collect.AbstractIterator.hasNext(AbstractIterator.java:136)
at com.facebook.presto.jdbc.internal.guava.collect.TransformedIterator.hasNext(TransformedIterator.java:42)
at com.facebook.presto.jdbc.internal.guava.collect.Iterators$ConcatenatedIterator.getTopMetaIterator(Iterators.java:1311)
at com.facebook.presto.jdbc.internal.guava.collect.Iterators$ConcatenatedIterator.hasNext(Iterators.java:1327)
at com.facebook.presto.jdbc.LengthLimitedIterator.hasNext(LengthLimitedIterator.java:42)
at com.facebook.presto.jdbc.PrestoResultSet.next(PrestoResultSet.java:146)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at smartbi.freequery.querydata.store.DBSQLResultStore.insertIntoHsql(DBSQLResultStore.java:1653)
at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1400)
at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabase(DBSQLResultStore.java:1269)
at smartbi.freequery.querydata.store.DBSQLResultStore.ensureGridDataInMemDB(DBSQLResultStore.java:6425)
at smartbi.freequery.querydata.store.DBSQLResultStore.getGridDataInternal(DBSQLResultStore.java:5746)
at smartbi.freequery.querydata.store.SQLResultStore.getGridData(SQLResultStore.java:280)
at smartbi.freequery.report.SimpleReportBO.getGridDataInternal(SimpleReportBO.java:2452)
at smartbi.freequery.report.SimpleReportBO.getGridDataInternal(SimpleReportBO.java:2335)
at smartbi.freequery.report.SimpleReportBO.getGridDataInternal(SimpleReportBO.java:2305)
at smartbi.freequery.report.SimpleReportBO.getGridData(SimpleReportBO.java:2092)
at smartbi.freequery.client.simplereport.ClientReportService.getRefreshDataInner(ClientReportService.java:1151)
at smartbi.freequery.client.simplereport.ClientReportService.getReportDataInPage(ClientReportService.java:1135)
at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFutureInPage(ClientReportService.java:1412)
at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFutureInPage(ClientReportService.java:1386)
at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFuture(ClientReportService.java:1329)
at jdk.internal.reflect.GeneratedMethodAccessor1578.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at smartbi.framework.rmi.ClientService.executeInternal(ClientService.java:180)
at smartbi.framework.rmi.ClientService.execute(ClientService.java:158)
at smartbi.framework.rmi.RMIServlet.processExecute(RMIServlet.java:235)
at smartbi.framework.rmi.RMIServlet.doPost(RMIServlet.java:146)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.extension.ExtensionFilter$1.doFilter(ExtensionFilter.java:249)
at smartbi.extension.ExtensionFilter$2.doFilter(ExtensionFilter.java:273)
at smartbi.aichat.AIChatFilter.doFilter(AIChatFilter.java:56)
at smartbi.extension.ExtensionFilter$2.doFilter(ExtensionFilter.java:273)
at smartbi.security.patch.PatchFilter.doFilter(PatchFilter.java:77)
at smartbi.extension.ExtensionFilter$2.doFilter(ExtensionFilter.java:273)
at smartbi.extension.ExtensionFilter.doFilterInternal(ExtensionFilter.java:276)
at smartbi.extension.ExtensionFilter.doFilter(ExtensionFilter.java:127)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.framework.rmi.TransactionFilter.doFilter(TransactionFilter.java:87)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.GZIPFilter.doFilter(GZIPFilter.java:249)
at smartbi.freequery.filter.Filter.doFilter(Filter.java:33)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.ExceptionResponseFilter.doFilter(ExceptionResponseFilter.java:92)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.CheckIsLoggedFilter.doFilter(CheckIsLoggedFilter.java:188)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.CheckRefererFilter.doFilter(CheckRefererFilter.java:45)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.CheckHttpMethodFilter.doFilter(CheckHttpMethodFilter.java:62)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.TraceFilter.doFilter(TraceFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.framework.RedisSessionFilter.doFilter(RedisSessionFilter.java:146)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.LogFilter.doFilter(LogFilter.java:110)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.freequery.filter.DisableUrlSessionFilter.doFilter(DisableUrlSessionFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at smartbi.framework.SmartbiApplicationFilter.doFilter(SmartbiApplicationFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:673)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1736)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: com.facebook.presto.spi.PrestoException: Closed Connection
at com.facebook.presto.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:236)
at com.facebook.presto.plugin.jdbc.JdbcRecordCursor.<init>(JdbcRecordCursor.java:95)
at com.facebook.presto.plugin.jdbc.JdbcRecordSet.cursor(JdbcRecordSet.java:59)
at com.facebook.presto.spi.RecordPageSource.<init>(RecordPageSource.java:40)
at com.facebook.presto.split.RecordPageSourceProvider.createPageSource(RecordPageSourceProvider.java:48)
at com.facebook.presto.spi.connector.ConnectorPageSourceProvider.createPageSource(ConnectorPageSourceProvider.java:52)
at com.facebook.presto.split.PageSourceManager.createPageSource(PageSourceManager.java:80)
at com.facebook.presto.operator.ScanFilterAndProjectOperator.getOutput(ScanFilterAndProjectOperator.java:248)
at com.facebook.presto.operator.Driver.processInternal(Driver.java:426)
at com.facebook.presto.operator.Driver.lambda$processFor$9(Driver.java:309)
at com.facebook.presto.operator.Driver.tryWithLock(Driver.java:730)
at com.facebook.presto.operator.Driver.processFor(Driver.java:302)
at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1079)
at com.facebook.presto.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:166)
at com.facebook.presto.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:599)
at com.facebook.presto.$gen.Presto_0_275_$_git_commit_id_abbrev_____20240809_055422_1.run(Unknown Source)
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: java.sql.SQLRecoverableException: Closed Connection
at oracle.jdbc.driver.PhysicalConnection.requireOpenConnection(PhysicalConnection.java:11385)
at oracle.jdbc.driver.PhysicalConnection.prepareStatementInternal(PhysicalConnection.java:2303)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:2258)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:2249)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:2160)
at sun.reflect.GeneratedMethodAccessor686.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:131)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
at com.sun.proxy.$Proxy248.prepareStatement(Unknown Source)
at com.facebook.presto.plugin.jdbc.BaseJdbcClient.getPreparedStatement(BaseJdbcClient.java:597)
at com.facebook.presto.plugin.jdbc.QueryBuilder.buildSql(QueryBuilder.java:150)
at com.facebook.presto.plugin.jdbc.BaseJdbcClient.buildSql(BaseJdbcClient.java:300)
at com.facebook.presto.plugin.jdbc.JdbcRecordCursor.<init>(JdbcRecordCursor.java:90)
... 17 more
|
解决方案
相关报错是跨库自身的连接池因未知原因抛出的异常，可在自身业务库开启数据库连接池的情况下，在业务库-高级-自定义参数设置添加uniondb-validate-query-method=3，以跳过跨库那边的连接池机制。
...
若是V95及以下版本暂无自定义参数设置，可在跨库目录/SmartbiUnionServer/etc/catalog/smartbix/下对应业务库的properties文件添加该配置项（无需重启）：
注：在配置文件手动添加后，若将业务库从跨库移除后重新添加、同步到跨库，则该手工添加的配置项会失效，需重新添加。
