(此文档仅供参考)
问题说明
星环数据库执行SQL偶尔会报以下错误：
java.net.SocketException: Broken pipe (Write failed) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1678) at ...(...) at smartbi.freequery.querydata.store.DBSQLResultStore.runRawSQL(DBSQLResultStore.java:3866) Caused by: java.sql.SQLException: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed) at org.apache.hive.jdbc.HiveStatement.executeInternal(HiveStatement.java:325) at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:419) at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:598) at org.apache.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:572) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at smartbi.freequery.querydata.store.DBSQLResultStore.runRawSQL(DBSQLResultStore.java:3866) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1449) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabase(DBSQLResultStore.java:1361) at smartbi.freequery.querydata.store.DBSQLResultStore.ensureGridDataInMemDB(DBSQLResultStore.java:4560) at smartbi.freequery.querydata.store.DBSQLResultStore.getGridDataInternal(DBSQLResultStore.java:3951) at smartbi.freequery.querydata.store.SQLResultStore.getGridData(SQLResultStore.java:156) at smartbi.freequery.report.SimpleReportBO.getGridDataInternal(SimpleReportBO.java:1010) at smartbi.freequery.report.SimpleReportBO.getGridData(SimpleReportBO.java:986) at smartbi.freequery.client.simplereport.ClientReportService.getReportDataInPage(ClientReportService.java:793) at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFutureInPage(ClientReportService.java:870) at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFuture(ClientReportService.java:842) at sun.reflect.GeneratedMethodAccessor774.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at smartbi.framework.rmi.ClientService.execute(Smartbi:118) at smartbi.framework.rmi.RMIServlet.processExecute(RMIServlet.java:204) at smartbi.framework.rmi.RMIServlet.doPost(RMIServlet.java:148) at javax.servlet.http.HttpServlet.service(HttpServlet.java:650) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.extension.ExtensionFilter$2.doFilter(ExtensionFilter.java:138) at smartbi.extension.ExtensionFilter$1.doFilter(ExtensionFilter.java:127) at smartbi.extension.ExtensionFilter.doFilterInternal(ExtensionFilter.java:150) at smartbi.extension.ExtensionFilter.doFilter(ExtensionFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.framework.rmi.TransactionFilter.doFilter(Smartbi:97) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.freequery.filter.GZIPFilter.doFilter(GZIPFilter.java:111) at smartbi.freequery.filter.Filter.doFilter(Filter.java:36) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.freequery.filter.CheckIsLoggedFilter.doFilter(CheckIsLoggedFilter.java:135) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.freequery.filter.TraceFilter.doFilter(TraceFilter.java:59) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:498) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1139) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) at org.apache.tomcat.util.net.AprEndpoint$SocketWithOptionsProcessor.run(AprEndpoint.java:2492) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed) at org.apache.thrift.transport.TIOStreamTransport.write(TIOStreamTransport.java:147) at org.apache.thrift.transport.TSaslTransport.flush(TSaslTransport.java:500) at org.apache.thrift.transport.TSaslClientTransport.flush(TSaslClientTransport.java:37) at org.apache.hadoop.hive.thrift.TFilterTransport.flush(TFilterTransport.java:77) at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:65) at org.apache.hive.service.cli.thrift.TCLIService$Client.send_ExecuteStatement(TCLIService.java:251) at org.apache.hive.service.cli.thrift.TCLIService$Client.ExecuteStatement(TCLIService.java:243) at org.apache.hive.jdbc.HiveStatement.executeInternal(HiveStatement.java:322) ... 63 more Caused by: java.net.SocketException: Broken pipe (Write failed) at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:115) at java.net.SocketOutputStream.write(SocketOutputStream.java:161) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.write(BufferedOutputStream.java:121) at org.apache.thrift.transport.TIOStreamTransport.write(TIOStreamTransport.java:145) ... 70 more
解决方案
此报错是由于数据库服务器端会定时断开连接，而数据源设置"获取连接时测试"、"返还时测试"、"获取、返还连接时测试"方式，并没有设置校验语句导致，关于连接池、校验方式、校验语句的逻辑如下：
（1）当设置校验方式为"获取连接时测试"、"返还时测试"、"获取、返还连接时测试"，但没有设置校验语句时，处理逻辑是直接将数据集执行的SQL拿到数据库中执行，此时因为数据库已将连接断开了，则出现超时的错误。
（2）当设置校验方式为"获取连接时测试"、"返还时测试"、"获取、返还连接时测试"，但设置了校验语句时，会先将校验语句拿到数据库中执行，若执行校验语句报错后，连接池里面的该连接视为失效，会从连接池里删除此失效连接并重新创建连接，新连接会继续执行校验语句，校验语句执行成功后，再将数据集的SQL拿到数据库中去执行。
（3）若设置为"返还时关闭连接"，每次创建连接执行SQL后会将连接关闭。即数据源的校验语句执行是会创建连接，执行完后则将连接关闭，当数据集执行SQL时会创建新的连接去执行。
综上，若需解决目前问题，有两种方案
①设置校验语句
②将校验方法设置为"返还时关闭连接"
注：
1、关于"返还时测试"，若有校验语句时，执行逻辑是先将数据集执行SQL拿到数据库中执行后，再执行校验语句。故当数据库将连接断开后，执行数据集的SQL就会出现超时报错。
2、当第一次执行校验语句失败后(超时是其中一种)，会再创建新的连接去执行校验语句，若执行成功则再执行SQL（此时前端一直是等待状态）；若执行失败，前端返回报错，不会再创建新的连接。
(此文档仅供参考)
问题说明
星环数据库执行SQL偶尔会报以下错误：
java.net.SocketException: Broken pipe (Write failed) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1678) at ...(...) at smartbi.freequery.querydata.store.DBSQLResultStore.runRawSQL(DBSQLResultStore.java:3866) Caused by: java.sql.SQLException: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed) at org.apache.hive.jdbc.HiveStatement.executeInternal(HiveStatement.java:325) at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:419) at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:598) at org.apache.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:572) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at smartbi.freequery.querydata.store.DBSQLResultStore.runRawSQL(DBSQLResultStore.java:3866) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabaseInner(DBSQLResultStore.java:1449) at smartbi.freequery.querydata.store.DBSQLResultStore.executeInDatabase(DBSQLResultStore.java:1361) at smartbi.freequery.querydata.store.DBSQLResultStore.ensureGridDataInMemDB(DBSQLResultStore.java:4560) at smartbi.freequery.querydata.store.DBSQLResultStore.getGridDataInternal(DBSQLResultStore.java:3951) at smartbi.freequery.querydata.store.SQLResultStore.getGridData(SQLResultStore.java:156) at smartbi.freequery.report.SimpleReportBO.getGridDataInternal(SimpleReportBO.java:1010) at smartbi.freequery.report.SimpleReportBO.getGridData(SimpleReportBO.java:986) at smartbi.freequery.client.simplereport.ClientReportService.getReportDataInPage(ClientReportService.java:793) at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFutureInPage(ClientReportService.java:870) at smartbi.freequery.client.simplereport.ClientReportService.getReportDataWithFuture(ClientReportService.java:842) at sun.reflect.GeneratedMethodAccessor774.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at smartbi.framework.rmi.ClientService.execute(Smartbi:118) at smartbi.framework.rmi.RMIServlet.processExecute(RMIServlet.java:204) at smartbi.framework.rmi.RMIServlet.doPost(RMIServlet.java:148) at javax.servlet.http.HttpServlet.service(HttpServlet.java:650) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.extension.ExtensionFilter$2.doFilter(ExtensionFilter.java:138) at smartbi.extension.ExtensionFilter$1.doFilter(ExtensionFilter.java:127) at smartbi.extension.ExtensionFilter.doFilterInternal(ExtensionFilter.java:150) at smartbi.extension.ExtensionFilter.doFilter(ExtensionFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.framework.rmi.TransactionFilter.doFilter(Smartbi:97) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.freequery.filter.GZIPFilter.doFilter(GZIPFilter.java:111) at smartbi.freequery.filter.Filter.doFilter(Filter.java:36) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.freequery.filter.CheckIsLoggedFilter.doFilter(CheckIsLoggedFilter.java:135) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at smartbi.freequery.filter.TraceFilter.doFilter(TraceFilter.java:59) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:498) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1139) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) at org.apache.tomcat.util.net.AprEndpoint$SocketWithOptionsProcessor.run(AprEndpoint.java:2492) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed) at org.apache.thrift.transport.TIOStreamTransport.write(TIOStreamTransport.java:147) at org.apache.thrift.transport.TSaslTransport.flush(TSaslTransport.java:500) at org.apache.thrift.transport.TSaslClientTransport.flush(TSaslClientTransport.java:37) at org.apache.hadoop.hive.thrift.TFilterTransport.flush(TFilterTransport.java:77) at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:65) at org.apache.hive.service.cli.thrift.TCLIService$Client.send_ExecuteStatement(TCLIService.java:251) at org.apache.hive.service.cli.thrift.TCLIService$Client.ExecuteStatement(TCLIService.java:243) at org.apache.hive.jdbc.HiveStatement.executeInternal(HiveStatement.java:322) ... 63 more Caused by: java.net.SocketException: Broken pipe (Write failed) at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:115) at java.net.SocketOutputStream.write(SocketOutputStream.java:161) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.write(BufferedOutputStream.java:121) at org.apache.thrift.transport.TIOStreamTransport.write(TIOStreamTransport.java:145) ... 70 more
解决方案
此报错是由于数据库服务器端会定时断开连接，而数据源设置"获取连接时测试"、"返还时测试"、"获取、返还连接时测试"方式，并没有设置校验语句导致，关于连接池、校验方式、校验语句的逻辑如下：
（1）当设置校验方式为"获取连接时测试"、"返还时测试"、"获取、返还连接时测试"，但没有设置校验语句时，处理逻辑是直接将数据集执行的SQL拿到数据库中执行，此时因为数据库已将连接断开了，则出现超时的错误。
（2）当设置校验方式为"获取连接时测试"、"返还时测试"、"获取、返还连接时测试"，但设置了校验语句时，会先将校验语句拿到数据库中执行，若执行校验语句报错后，连接池里面的该连接视为失效，会从连接池里删除此失效连接并重新创建连接，新连接会继续执行校验语句，校验语句执行成功后，再将数据集的SQL拿到数据库中去执行。
（3）若设置为"返还时关闭连接"，每次创建连接执行SQL后会将连接关闭。即数据源的校验语句执行是会创建连接，执行完后则将连接关闭，当数据集执行SQL时会创建新的连接去执行。
综上，若需解决目前问题，有两种方案
①设置校验语句
②将校验方法设置为"返还时关闭连接"
注：
1、关于"返还时测试"，若有校验语句时，执行逻辑是先将数据集执行SQL拿到数据库中执行后，再执行校验语句。故当数据库将连接断开后，执行数据集的SQL就会出现超时报错。
2、当第一次执行校验语句失败后(超时是其中一种)，会再创建新的连接去执行校验语句，若执行成功则再执行SQL（此时前端一直是等待状态）；若执行失败，前端返回报错，不会再创建新的连接。
