1，线上监控人员报错，tomcat下的应用连接数据库问题：
|
--- Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 99,634,779 milliseconds ago. The last packet sent successfully to the server was 99,634,781 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in META-INF/sqlmap/IB_BRAND_sqlmap.xml. --- The error occurred while applying a result map. --- Check the IB_BRAND.IbBrandResult. --- Check the result mapping for the 'ibBrandDetail' property. --- Cause: com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in META-INF/sqlmap/IB_BRAND_DETAIL_sqlmap.xml. --- The error occurred while applying a parameter map. --- Check the IB_BRAND_DETAIL.selectByBrandId-InlineParameterMap. --- Check the statement (query failed). --- Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 99,634,779 milliseconds ago. The last packet sent successfully to the server was 99,634,781 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
|
2，去检查数据库，看到有8小时timeout
|
mysql> show variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 20 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 30 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 30 | | wait_timeout | 28800 | +-----------------------------+----------+ 11 rows in set (0.01 sec)
mysql>
|
3，分析问题
问题的核心是在于，数据库有8小时失效时间，所以要么就保证数据库连接永远不失效，要么就在应用从连接池获取连接的时候，每次都判断连接是否有效，如果无效就重新获取或者创建一个新的连接。
设置数据库永远生效
# 在线设置下：
mysql>set global wait_timeout=31536000;
QueryOK, 0 rows affected (0.00 sec)
mysql>
# 然后在my.cnf里面设置添加，再以后db重启的时候生效：
wait_timeout=31536000
interactive_timeout=31536000
4，使用异步的validateObject来判断
实际情况：
（1）目前网站的应用大部分的瓶颈还是在I/O这一块，大部分的I/O还是在数据库的这一层面上，每一个请求可能会调用10来次SQL查询，如果不走事务，一个请求会重复获取链接，如果每次获取链接都进行validateObject，性能开销不是很能接受，可以假定一次SQL操作消毫0.5~1ms(一般走了网络请求基本就这数)
（2）网站异常数据库重启，网络异常断开的频率是非常低的，一般也就在数据库升级，演习维护时才会进行，而且一般也是选在晚上，访问量相对比较低的请求，而且一般会有人员值班关注，所以异步的validateObject是可以接受，但一个前提需要确保能保证在一个合理的时间段内，数据库能完成自动重联。
所以采用异步判断：
在spring的连接池配置文件里面配置：
<?xml version="1.0"encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC"-//SPRING//DTD BEAN//EN""http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<beanid="dataSource"class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close">
<propertyname="driverClassName">
<value>${enrolment.datasource.driverClassName}</value>
</property>
<propertyname="url">
<value>${enrolment.datasource.url}</value>
</property>
<propertyname="username">
<value>${enrolment.datasource.username}</value>
</property>
<propertyname="password">
<value>${enrolment.datasource.password}</value>
</property>
<propertyname="maxActive" >
<value>${enrolment.datasource.maxActive}</value>
</property>
<propertyname="maxIdle" >
<value>${enrolment.datasource.maxIdle}</value>
</property>
<propertyname="maxWait" >
<value>${enrolment.datasource.maxWait}</value>
</property>
<!-- 打开检查,用异步线程evict进行检查 -->
<propertyname="testWhileIdle"><value>true</value></property>
<propertyname="validationQuery"><value>select now() fromdual</value></property>
<propertyname="validationQueryTimeout"><value>1</value></property>
<propertyname="timeBetweenEvictionRunsMillis"><value>30000</value></property>
<propertyname="numTestsPerEvictionRun"><value>${enrolment.datasource.maxActive}</value></property>
</bean>
<bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="transactionService"class="com.plocc.framework.transaction.TransactionService">
<constructor-argindex="0" ref="transactionManager"/>
</bean>
</beans>
在数据配置文件里面配置：
netcomment.datasource.url=jdbc:mysql://192.168.13.11:3307/enrolment_db?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&preferredTestQuery=selectnow()
netcomment.datasource.driverClassName=com.mysql.jdbc.Driver
netcomment.datasource.username=zhi_web
netcomment.datasource.password=z$ywx18
netcomment.datasource.maxActive=50
netcomment.datasource.maxIdle=5
netcomment.datasource.maxWait=120000
最好重启tomcat应用即可生效了，运行一段时间，没有再发生过类似问题。
参考文章：http://agapple.iteye.com/blog/772507