问题描述
项目高访问量的时候，基本上线不到三天就会一直输出下面的错误
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select TO_CHAR(captured_time, 'yyyy-MM-dd HH:mm:ss'),camera_id from ST_PEOPLE_FEATURE_WIFI where captured_time>=CAST(TO_DATE('2019-07-31 17:51:55') AS DATE) and captured_time<=CAST(TO_DATE('2019-09-29 17:51:55') AS DATE) and cluster_id=13137900 order by captured_time desc limit 20]; SQL state [null]; error code [0]; java.lang.IllegalArgumentException: Connection is null or closed.; nested exception is java.sql.SQLException: java.lang.IllegalArgumentException: Connection is null or closed.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:452)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:462)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:490)
at com.suncreate.query.jdbc.service.impl.GeneralSQLServiceImpl.getLists(GeneralSQLServiceImpl.java:40)
at com.suncreate.service.impl.GeneralRestPhoenixServiceImpl.ResultForQuery(GeneralRestPhoenixServiceImpl.java:33)
at com.suncreate.controller.PlatlogGeneralRestApi.ResultForSql(PlatlogGeneralRestApi.java:119)
at sun.reflect.GeneratedMethodAccessor69.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:189)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:90)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:117)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:106)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: java.lang.IllegalArgumentException: Connection is null or closed.
at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1295)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1257)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.getTable(ConnectionQueryServicesImpl.java:1474)
at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:597)
at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:518)
at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:573)
at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.(FromCompiler.java:391)
at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:228)
at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:206)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:468)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:442)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:290)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:289)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:1706)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2363)
at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2481)
at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2360)
at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:211)
at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:140)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:439)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
... 68 common frames omitted
Caused by: java.lang.IllegalArgumentException: Connection is null or closed.
at org.apache.hadoop.hbase.client.HTable.(HTable.java:307)
at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.getTable(ConnectionManager.java:768)
at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.getTable(ConnectionManager.java:763)
at org.apache.phoenix.query.HTableFactory$HTableFactoryImpl.getTable(HTableFactory.java:51)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.getTable(ConnectionQueryServicesImpl.java:428)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1274)
... 92 common frames omitted
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: java.lang.IllegalArgumentException: Connection is null or closed.
at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1295)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1257)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.getTable(ConnectionQueryServicesImpl.java:1474)
at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:597)
at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:518)
at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:573)
at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.(FromCompiler.java:391)
at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:228)
at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:206)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:468)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:442)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:290)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:289)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:1706)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2363)
at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2481)
at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2360)
at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:211)
at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:140)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:439)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
... 68 common frames omitted
Caused by: java.lang.IllegalArgumentException: Connection is null or closed.
at org.apache.hadoop.hbase.client.HTable.(HTable.java:307)
at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.getTable(ConnectionManager.java:786)
at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.getTable(ConnectionManager.java:768)
at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.getTable(ConnectionManager.java:763)
at org.apache.phoenix.query.HTableFactory$HTableFactoryImpl.getTable(HTableFactory.java:51)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.getTable(ConnectionQueryServicesImpl.java:428)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1274)
... 92 common frames omitted
问题排查
源码排查原因
数据库连接立方图如下：
druid连接池内部有很多phoenix连接，phoenix取查询hbase的时候，通过hconnection访问hbase，一个进程内部只有一个实例。
上述追溯源码
HTable报错的方法
@InterfaceAudience.Private
public HTable(TableName tableName, final ClusterConnection connection,
final ConnectionConfiguration tableConfig,
final RpcRetryingCallerFactory rpcCallerFactory,
final RpcControllerFactory rpcControllerFactory,
final ExecutorService pool) throws IOException {
if (connection == null || connection.isClosed()) {
throw new IllegalArgumentException("Connection is null or closed.");
}
.........................省略
this.finishSetup();
}
ConnectionManager$HConnectionImplementation报错代码
@Override
public HTableInterface getTable(TableName tableName, ExecutorService pool) throws IOException {
if (managed) {
throw new NeedUnmanagedConnectionException();
}
return new HTable(tableName, this, connectionConfig, rpcCallerFactory, rpcControllerFactory, pool);
}
通过上述源码发现Connection is null or closed错误，是由于上述图第三层ConnectionManager维护的hbase连接关闭，或者为null导致。
ConnectionManager问题排查
这个时候，通常开始上jmap了，内存查看工具，查看HConnection的实例
[[email protected] ~]# jmap -histo:live 219470 | grep HConnection
1264: 2 320 org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation
3063: 2 48 org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation$MasterServiceState
6851: 1 16 org.apache.phoenix.query.HConnectionFactory$HConnectionFactoryImpl
通过命令居然发现了两个，不符合科学啊？？？？？？？
具体两个实例的内容，很想看到，怎么办，，，有两种方式,这里采用jvisualvm，查看具体实例的值
上图中的左边的#1,#2代表这两个实例，查看第一个发现closed已经被关闭了，所以才会有第二个实例。
但是为什么关闭的连接没有被垃圾回收？？？？，查看一下实例的引用，如下图：
原来还被phoenixConnection引用，没有被释放啊。那druid为什么没有删除这个不可用的phoenix连接呢？？？
druid如何剔除异常连接
druid有剔除异常连接的机制：https://www.bookstack.cn/read/Druid/452caf873b3a56bf.md
但是默认的实现并没有phoneix数据库，druid的监控页面也不支持phoneix，看来druid还没有开始支持phoneix啊。
自己实现一下
class PhoenixExceptionSorter implements ExceptionSorter {
@Override
public boolean isExceptionFatal(SQLException e) {
if (e.getMessage().contains("Connection is null or closed")) {
LOG.error("剔除phoenix不可用的连接", e);
return true;
}
return false;
}
@Override
public void configFromProperties(Properties properties) {
}
}
这样，项目就稳定运行了,如下图是上线一段时间后日志的采集效果：
说明druid的异常处理机制生效了。
完整的集成代码
package com.suncreate.query.jdbc.config;
import java.sql.SQLException;
import java.util.Properties;
import javax.annotation.PostConstruct;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.ExceptionSorter;
import com.alibaba.druid.util.StringUtils;
import com.suncreate.query.jdbc.service.impl.GeneralSQLServiceImpl;
import com.suncreate.utils.FusionInsightLogin;
@Component
public class PhoenixDataSourceConfig {
private static final Logger LOG = LoggerFactory.getLogger(PhoenixDataSourceConfig.class);
@Autowired
private FusionInsightLogin login;
@Value("${spring.datasource.phoenix.driver-class-name:org.apache.phoenix.jdbc.PhoenixDriver}")
private String driverClassName;
@Value("${spring.datasource.phoenix.password:passwd}")
private String password;
@Value("${spring.datasource.phoenix.username:zx_test}")
private String username;
@Value("${spring.datasource.phoenix.url:phoenix}")
private String url;
@PostConstruct
public void init() {
if (url.equals("phoenix") || StringUtils.isEmpty(url)) {
url = "jdbc:phoenix:" + login.getConf().get("hbase.zookeeper.quorum");
}
if (StringUtils.isEmpty(driverClassName)) {
driverClassName = "org.apache.phoenix.jdbc.PhoenixDriver";
}
LOG.info("目前使用的jdbc-phoenix的driver：" + driverClassName);
LOG.info("目前使用的jdbc-phoenix的url：" + url);
LOG.info("目前使用的jdbc-phoenix的用户名：" + username);
LOG.info("目前使用的jdbc-phoenix的密码：" + password);
}
@Bean(name = "phoenixDruidDataSource")
public DruidDataSource druidDataSource() {
DruidDataSource dds = new DruidDataSource();
try {
dds.setDriverClassName(driverClassName);
dds.setUrl(url);
dds.setUsername(username);
dds.setPassword(password);
dds.setInitialSize(10);
dds.setMaxActive(60);
dds.setMinIdle(10);
dds.setMaxWait(6000);
dds.setQueryTimeout(60);
dds.setPoolPreparedStatements(true);
dds.setMaxOpenPreparedStatements(50);
dds.setValidationQuery("SELECT 1");
dds.setTestOnBorrow(false);
dds.setTestOnReturn(false);
dds.setTestWhileIdle(true);
dds.setTimeBetweenEvictionRunsMillis(60000);
dds.setMinEvictableIdleTimeMillis(25200000);
dds.setRemoveAbandoned(true);
dds.setRemoveAbandonedTimeout(24 * 3600);
dds.setLogAbandoned(false);
dds.setFilters("stat");
// 设置剔除异常连接机制
dds.setExceptionSorter(new PhoenixExceptionSorter());
} catch (SQLException e) {
e.printStackTrace();
}
return dds;
}
@Bean(name = "phoenixJdbcTemplate")
public JdbcTemplate phoenixJdbcTemplate(@Qualifier("phoenixDruidDataSource") DruidDataSource dataSource) {
return new JdbcTemplate(dataSource);
}
class PhoenixExceptionSorter implements ExceptionSorter {
@Override
public boolean isExceptionFatal(SQLException e) {
if (e.getMessage().contains("Connection is null or closed")) {
LOG.error("剔除phoenix不可用的连接", e);
return true;
}
return false;
}
@Override
public void configFromProperties(Properties properties) {
}
}
}