MySQL - 8小时连接闲置超时
问题与分析
最近发现之前部署在阿里云的一个web项目，每过一段时间就会报错，但是刷新下页面就会显示正常；在过了比较长的一段时间后，又会报同样的错误，如下：
1 2 3 4 5 6 7 8 | com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet sent successfully to the server was *,*** milliseconds ago. The driver has not received any packets from the server. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137) |
|---|
在网上查了下资料，原来是因为项目中使用了连接池，由于连接池里的连接长时间闲置着，而MySQL默认的非交互式连接的闲置时间是8小时；也就是说，当连接池里的连接闲置超过8小时后就会被MySQL数据库自动断开而失效。
由于连接池并不知道连接已经失效了，依然保持着这些失效的连接，这导致web项目在一段时间后访问页面时报错，而在刷新页面后连接池重新获取了有效的连接，所以项目又可以正常访问了。
这里涉及到MySQL关于交互式连接和非交互式连接的概念。
交互式连接
通俗的说，在cmd里直接和MySQL进行各种sql操作的连接方式就是交互式连接，这里走的是TCP协议。
非交互式连接
而直接在项目中对MySQL进行sql操作的方式则是非交互式连接，我们的应用服务器通过Hibernate或者JDBC来实现和数据库的通信。
怎么解决连接闲置超时的问题？
这两种连接方式都有各自对应的一个超时时间属性，交互式连接是interactive_timeout；非交互式连接是wait_timeout。
既然是闲置超时，那么解决的办法也很简单，就是直接将这个时间设置得更长些；在MySQL中最多可以设置到365天(即31536000，默认单位是s)，有两种设置的方法。
第一种设置方式：修改配置文件my.ini文件
该配置文件在MySQL的安装目录下，如果没有此文件，可以复制my-default.ini文件，将生成的复件重命名成my.ini；然后在文件中添加如下语句：
1 2 | wait_timeout=31536000 interactive_timeout=31536000 |
|---|
如果没有这两个语句则表示默认值是8小时(60608=28800)；需要注意的是，wait_timeout的最大值分别是24天/365天(Windows/Linux)。
第二种设置方式：使用mysql命令进行修改
1 2 | set global interactive_timeout=设置值 set global wait_timeout=设置值 |
|---|
不过闲置时间设置得过大并不好，MySQL里大量的SLEEP连接无法及时释放，拖累系统性能；设置得过小又容易产生如上所述的错误；由于我们的web项目中经常会使用到连接池技术，所以我们有更好的解决方法，那就是在项目中设置连接池的属性。
在项目中设置连接池的属性
我的项目是使用的c3p0，所以这里只介绍c3p0的设置方法，如下：
方法一：减少连接池内连接的生存周期
既然MySQL连接的默认闲置时间是8小时，那么只要将连接池内连接的生产周期设置得比8小时短就行了。
1 2 3 4 | <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <!-- 连接的最大空闲时间，若超过该时间还没被使用过则会自动断开，单位为s，默认为0(即永远不会断开) --> <property name="maxIdleTime" value="1800"/> </bean> |
|---|
方法二：定期使用连接池内的连接
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <!-- 定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个可以显著提高测试速度。 注意：测试的表必须在初始数据源的时候就存在。Default:null --> <property name="preferredTestQuery" value="SELECT 1"/> <!-- 每1800秒检查所有连接池中的空闲连接 --> <property name="idleConnectionTestPeriod" value="1800"/> <!-- 如果设置为true，则每次从池中取一个连接就做一下测试，使用automaticTestTable或者preferredTestQuery来做一条查询语句。 看看连接好不好用，如果不好用就关闭它，接着重新从池中拿一个。 --> <property name="testConnectionOnCheckout" value="true"/> </bean> |
|---|
警告
本文最后更新于 October 22, 2017，文中内容可能已过时，请谨慎使用。
腾讯云开发者
