c3p0 MySQL连接8小时失效问题的官方解决方案

声明 ： 文章转载自CSDN博主   GW_Cheng  ，转载仅供学习

前一段时间在做一个发邮件的程序，程序是用定时器，每晚凌晨定时发邮件，邮件内容需要从数据库中获取，运行了一天就出问题了。

问题信息如下

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was56588 milliseconds ago.
The last packet sent successfully to the server was 56588 milliseconds ago, which 
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.

 
 
1
2
3
4
5
6

大致意思是说超时了，连接不可用，给的提示可以将

autoReconnect=true

 
 
1
2

来避免这一问题的发生

关于超时问题大致有如下几种解决方案

解决方案

1. 将mysql服务器的wait_timeout设置为无穷大，保证永不超时（不知道可以不，不建议采用）像这样

   set global wait_timeout=10;
   
      
      
   1
   2

2. 根据提示自动重连将autoReconnect=true添加到数据库链接的代码中，像这样

   jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
   
      
      
   1
   2

3. 在mysql的wait_timeout时间之内发一次心跳，保证连接的有效性

4. 在使用的时候检测连接的有效性，失效就获取一个新的连接

第一种方案不建议采用，因为有其他更优雅的方式

第二种方案

MySQL官网对于autoReconnect的解释

MySQL官网对autoReconnect的解释

autoReconnect

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don’t handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable “wait_timeout” to a high value, rather than the default of 8 hours.

Default: false

Since version: 1.1

官方不建议使用着一参数因为首先，过时的链接仍然会报错，其次，使用这一参数对session和数据的并发操作具有负面效果，官方说把wait_timeout设置的高一些都比使用这个参数好，还是不要用这种方案了吧，第一种都不采用何况这种呢？

本文介绍一下C3P0官方推荐的方式，这种方式综合了第三和第四种解决方案

C3P0官方的建议

Simple advice on Connection testing

If you don’t know what to do, try this:

1. If you know your driver supports the JDBC 4 Connection.isValid(…) method and you are using c3p0-0.9.5 or above, don’t set a preferredTestQuery. If your driver does not support this method (or if you are not sure), try SELECT 1 for your preferredTestQuery, if you are running MySQL or Postgres. For other databases, look for suggestions here. Leave automatedTestTable undefined.

2. Begin by setting testConnectionOnCheckout to true and get your application to run correctly and stably. If you are happy with your application’s performance, you can stop here! This is the simplest, most reliable form of Connection-testing, but it does have a client-visible performance cost.

3. If you’d like to improve performance by eliminating Connection testing from clients’ code path:

   a. Set testConnectionOnCheckout to false

   b. Set testConnectionOnCheckin to true

   c. Set idleConnectionTestPeriod to 30, fire up you application and observe. This is a pretty robust setting, all Connections will tested on check-in and every 30 seconds thereafter while in the pool. Your application should experience broken or stale Connections only very rarely, and the pool should recover from a database shutdown and restart quickly. But there is some overhead associated with all that Connection testing.

   d. If database restarts will be rare so quick recovery is not an issue, consider reducing the frequency of tests by idleConnectionTestPeriod to, say, 300, and see whether clients are troubled by stale or broken Connections. If not, stick with 300, or try an even bigger number. Consider setting testConnectionOnCheckin back to false to avoid unnecessary tests on checkin. Alternatively, if your application does encounter bad Connections, consider reducing idleConnectionTestPeriod and set testConnectionOnCheckin back to true. There are no correct or incorrect values for these parameters: you are trading off overhead for reliability in deciding how frequently to test. The exact numbers are not so critical. It’s usually easy to find configurations that perform well. It’s rarely worth spending time in pursuit of “optimal” values here.

So, when should you stick with simple and reliable (Step 2 above), and when is it worth going for better performance (Step 3)? In general, it depends on how much work clients typically do with Connections once they check them out. If clients usually make complex queries and/or perform multiple operations, adding the extra cost of one fast test per checkout will not much affect performance. But if your application typically checks out a Connection and performs one simple query with it, throwing in an additional test can really slow things down.

That’s nice in theory, but often people don’t really have a good sense of how much work clients perform on average. The best thing to do is usually to try Step 3, see if it helps (however you measure performance), see if it hurts (is your application troubled by broken Connections? does it recover from database restarts well enough?), and then decide. You can always go back to simple, slow, and robust. Just set testConnectionOnCheckout to true, testConnectionOnCheckin to false, and set idleConnectionTestPeriod to 0.

But do, always, be sure that your tests themselves are performant, either because your JDBC driver supports Connection.isValid(…) or because you have set an efficient preferredTestQuery !!!

实在不想贴这么多，只是为了给出C3P0的本意

官方建议采用第三种方式

将testConnectionOnCheckout 设为 false
将testConnectionOnCheckin 设为 true
将idleConnectionTestPeriod 设为 30，这个数字要根据项目情况设定，比8小时小就好

 
 
1
2
3
4

解释一下这几个参数

testConnectionOnCheckout

If true, an operation will be performed at every connection checkout to verify that the connection is valid.

设置为true，所有的连接都将检测其有效性，会影响性能，所以将其设置为false

testConnectionOnCheckin

If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid.

设置为true，异步检测连接的有效性

idleConnectionTestPeriod（单位是秒，不是毫秒）

If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.

每隔多少秒c3p0检测连接的有效性

示例

#---------------------------------------------------------
# c3p0反空闲设置，防止8小时失效问题28800
#---------------------------------------------------------
#idleConnectionTestPeriod要小于MySQL的wait_timeout
jdbc.c3p0.testConnectionOnCheckout=false
jdbc.c3p0.testConnectionOnCheckin=true
jdbc.c3p0.idleConnectionTestPeriod=3600
#---------------------------------------------------------
# c3p0连接池配置
#---------------------------------------------------------
#initialPoolSize, minPoolSize, maxPoolSize define the number of Connections that will be pooled.
#Please ensure that minPoolSize <= maxPoolSize.
#Unreasonable values of initialPoolSize will be ignored, and minPoolSize will be used instead.
jdbc.c3p0.initialPoolSize=10
jdbc.c3p0.minPoolSize=10
jdbc.c3p0.maxPoolSize=100
#maxIdleTime defines how many seconds a Connection should be permitted to go unused before being culled from the pool.
jdbc.c3p0.maxIdleTime=3600
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

spring中dataSource的配置

<!-- 配置数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close" 
        p:driverClass="${jdbc.driverClassName}"
        p:jdbcUrl="${jdbc.url}" 
        p:user="${jdbc.username}" 
        p:password="${jdbc.password}"
        p:testConnectionOnCheckout="${jdbc.c3p0.testConnectionOnCheckout}"
        p:testConnectionOnCheckin="${jdbc.c3p0.testConnectionOnCheckin}"
        p:idleConnectionTestPeriod="${jdbc.c3p0.idleConnectionTestPeriod}"
        p:initialPoolSize="${jdbc.c3p0.initialPoolSize}" 
        p:minPoolSize="${jdbc.c3p0.minPoolSize}"
        p:maxPoolSize="${jdbc.c3p0.maxPoolSize}" 
        p:maxIdleTime="${jdbc.c3p0.maxIdleTime}" />

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dataSource" />
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

参考文献

C3P0文档 
MySQL文档