一次MySQL数据库连接超时问题排查与解决转载
导语
对于处理SQL的问题大家肯定不陌生，本文也是一篇实战MySql数据库连接超时的问题，希望能对大家日常工作有所帮助。
正文
操作系统 Windows 10 Enterprise，数据库 MySQL-5.5.16，c3p0-0.9.5.2
关于针对数据库的连接，之前没有特别注意过，直到遇到如下问题：
Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 54,812,410 milliseconds ago. The last packet sent successfully to the server was 54,812,411 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
从上面的信息得知，应用程序与数据库服务器的连接中断了，那么为什么会出现上面的问题呢？不是有连接池吗？里面不是保存有大量的连接吗？比如使用c3p0数据源配置如下：
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setInitialPoolSize(5);
dataSource.setMaxPoolSize(20);
dataSource.setAcquireIncrement(1);
错误信息中已经说得很明白了，上一次客户端和服务的通信已经是54812411ms前了，但是MySQL默认配置的wait_timeout属性确是「28800s」，也就是「8小时」，该参数的意义是「MySQL在没有进行通信的连接上等待的最长时间直到服务器关闭该连接」，上文信息中指出不通信的间隔已经有54812.411s了，所以客户端与数据库服务器的连接已经被服务器强制关闭了，所以才会出现如上的错误。
但是信息中也给出了可以解决的办法，如下：
使连接过期
对连接的有效性进行测试
增加数据库服务器的超时时间
使用**autoReconnect**属性避免
通过捕捉异常来重试建立连接
上述几种方法可以从范围上分成数据库层次、应用层次。
一、数据库层次上解决
1.1 通过增大**wait_timeout**的值
使用这种方式是最直接、最暴力的，通过增加该属性的值可以增大MySQL服务器在不活跃的连接上等待的时间，但是这种方式影响的范围很大，因为数据库的使用面向的应用肯定不止一个，所以如果不在万不得已的情况之下，不要使用这种方式。
1.2 配置autoReconnect属性
表明数据库驱动是否会重新建立不活跃的连接，默认情况下是false。一般是在JDBC的连接URL中使用。如果设置为true，对于在不活跃的连接上进行的查询会抛出异常，这些查询属于当前的事务，事务中进行下一次查询时，驱动将会重新建立连接。这个属性不推荐使用，因为它对于会话状态和数据一致性会造成影响，尤其是当应用不能正确的处理SQLExceptions时。在考虑强一致性的时候，尤其要避免使用该属性。
二、应用层次上解决
2.1 使连接过期
此处使用的是c3p0数据源，在该数据源下配置连接的过期时间是通过**maxIdleTime**属性实现的，该属性的意义是：一个不使用的连接在数据库连接池中存在的最长时间，直到它被连接池抛弃。单位是秒，默认值是0，表示连接池中的连接永远不会过期。
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setMaxIdleTime(1800);
配置的属性值要大于**wait_timeout**的值，以免在MySQL服务器强制断开连接之后，连接还没有过期。
2.2 对连接有效性进行测试
对与数据库服务器的连接进行有效性的测试是一个可行的方式，可以随时确定数据库服务器是否可用，而不仅仅是连接是否可用。这里使用c3p0数据源作为示例进行，其他的数据源，比如DBCP和druid其中也有相对应的方式完成这种操作。
c3p0提供了很多的方式来对连接池中的连接进行测试，通过这种测试来避免在应用程序的层次上看到上文中提到的错误。通过以下几个属性来完成这种测试：
下面三个属性是控制什么时候进行测试连接。
idleConnectionTestPeriod：如果该属性的值大于0，每隔固定的时间段，c3p0会测试所有闲置、保持在连接池中的连接。默认情况下是不进行测试的；
testConnectionOnCheckout：是否在取连接进行测试；
testConnectionOnCheckin：是否在放入连接时进行测试；
下面三个属性是决定如何进行测试连接。
automaticTestTable：c3p0会自动创建一个空表以备查询进行连接测试；
connectionTesterClassName：ConnectionTester接口的全限定名实现， 默认值是com.mchange.v2.c3p0.impl.DefaultConnectionTester，用来定义c3p0数据源如何测试连接，过于灵活，避免过度使用；
preferredTestQuery：定义的被用来执行连接测试的查询；
2.2.1 进行测试的时候要考虑的问题
在进行连接测试的时候，需要考虑很多其他的问题。然后根据这些需要考虑的额外的问题来确定最佳的测试方法。
2.2.1.1 性能问题
性能问题是一个非常重要的话题，尤其是在大型的复杂应用中，在测试有效性时，也不要给连接带来过大的负担。
「首先要考虑连接测试的时机」
从连接池中取出连接时：这种测试是最简单，也是最可靠的，但是会给客户端的性能带来极大的影响，可以通过配置属性testConnectionOnCheckout＝true来开启在此时进行测试；
连接放入连接池时：此时进行测试时，需要同时结合idleConnectionTestPeriod，可以满足较高的可靠性，可以通过设置testConnectionOnCheckin＝true，在这种情况下，在闲时和放入连接时进行异步测试，性能依然不会下降；
「其次要考虑测试的语句」
如果使用的数据库驱动「支持JDBC4「，并且使用的」c3p0是0.9.5或以上」。可以直接调用JDBC的API来处理，其中有一个isValid()方法可以用来实现作为快速且可靠的连接测试。「默认情况下，c3p0使用这个方法进行测试」。如果想要设置一个超时时间，可以通过继承IsValidOnlyConnectionTester来实现，如下：
public class Tester extends IsValidOnlyConnectionTester{
protected int getIsValidTimeout() { return 30; }
}
如果驱动不支持最新的API，默认情况下c3p0通过在连接上调用DatabaseMetaData的getTables()方法。这样可以与任何类型的数据库有效兼容，但是由于返回很大的数据量会对数据库连接池的性能造成显著影响；在JDBC3驱动以下（或者0.9.5版本之前）可以配置preferredTestQuery加速测试，但它优先于数据源进行加载，此时如果数据库中没有对应的表，则会造成错误，如下：
对于这种情况，则可以通过设置automaticTestTable属性来代替，c3p0会自动的创建一个空表来作为测试查询的对象；
2.2.1.2 兼容性问题
另外一个要考虑的是数据库兼容问题，比如需要兼容多种数据库时，比如同时要测试的数据库有MySQL、SQL Server和Oracle，则需要考虑测试语句的通用性。在这种情况下可以通过可以设定与数据库和表无关的语句，比如select 1来测试这个连接。但是针对不同数据库类型，支持的这种轻量级的测试语句并不相同，如下对不同的常见的数据库的支持：
2.2.1.3 灵活性的问题
如果对于数据源提供的测试方法或者需要自定义一些行为时，灵活性的问题就很重要了。
c3p0也提供了这样的支持，通过配置connectionTesterClassName属性，提供给实现类的完全限定名即可。这种情况不需要preferredTestQuery或者automaticTestTable，默认情况下该属性的值是com.mchange.v2.c3p0.impl.DefaultConnectionTester。可以通过继承DefaultConnectionTester的父类AbstractConnectionTester实现。简单实现如下：
public class MyConnectionTester extends AbstractConnectionTester {
public int activeCheckConnection(Connection c, String preferredTestQuery, Throwable[] rootCauseOutParamHolder) {
try {
ResultSet resultset = c.createStatement().executeQuery("SELECT 1");
while(resultset.next()){
int result = resultset.getInt(1);
if(result == 1){
System.out.println("test successfully");
} else{
System.out.println("test failed");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int statusOnException(Connection c, Throwable t, String preferredTestQuery, Throwable[] rootCauseOutParamHolder) {
return 0;
}
}
配置如下：
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setIdleConnectionTestPeriod(3);
dataSource.setConnectionTesterClassName("com.lmy86263.MyConnectionTester");2.3 针对连接断开进行重试
由于目前所有的应用都是直接使用数据源来管理与数据库的连接，而在程序内部只是提供一些必须的配置项，所以如果要重试则需要通过编程手段来完成，如下在建立数据源的时候如果连接失败则会重试指定次数：
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUser("root");
dataSource.setPassword("abc");
int retry = 3;
while(retry > 0){
try {
dataSource.getConnection();
break;
} catch (SQLException e) {
retry--;
}
}
如果对JDBC的超时的详细机制比较感兴趣可以参考官方文章。
更多思考
SQL的性能优化是非常多的，更多关于MySQL相关的内容可以阅读以下内容
