下次自动登录
现在的位置:
& 综合 & 正文
解决 c3p0 和 MySQL 集成情况下，连接长时间闲置后重新使用时报错的问题
MySQL 的默认设置下，当一个连接的空闲时间超过8小时后，MySQL 就会断开该连接，而 c3p0 连接池则以为该被断开的连接依然有效。在这种情况下，如果客户端向 c3p0 连接池请求连接的话，连接池就会把已经失效的连接返回给客户端，客户端在使用该失效连接的时候即抛出异常。解决这个问题的办法有三种：1. 增加 MySQL 的 wait_timeout 属性的值。修改 /etc/f 文件，在 [mysqld] 节中设置：
# Set a connection to wait 8 hours in idle status.wait_timeout = 86400
2. 减少连接池内连接的生存周期，使之小于上一项中所设置的 wait_timeout 的值。修改 c3p0 的配置文件，设置：
# How long to keep unused connections around(in seconds)# Note: MySQL times out idle connections after 8 hours(28,800 seconds)# so ensure this value is below MySQL idle timeoutcpool.maxIdleTime=25200
在 Spring 的配置文件中：
&bean id="dataSource"
class="com.mchange.boPooledDataSource"&
&property name="maxIdleTime" value="${cpool.maxIdleTime}" /&
&!-- other properties --&&/bean&
3. 定期使用连接池内的连接，使得它们不会因为闲置超时而被 MySQL 断开。修改 c3p0 的配置文件，设置：
# Prevent MySQL raise exception after a long idle timecpool.preferredTestQuery='SELECT 1'cpool.idleConnectionTestPeriod=18000cpool.testConnectionOnCheckout=true
修改 Spring 的配置文件：
&bean id="dataSource"
class="com.mchange.boPooledDataSource"&
&property name="preferredTestQuery"
value="${cpool.preferredTestQuery}" /&
&property name="idleConnectionTestPeriod"
value="${cpool.idleConnectionTestPeriod}" /&
&property name="testConnectionOnCheckout"
value="${cpool.testConnectionOnCheckout}" /&
&!-- other properties --&&/bean&
附：以下 awk 脚本可以用以将 c3p0.properties 文件中的属性设置转换成为 applicationContext.xml 中 数据库连接池 DataSource 所需的 XML 元素形式。
#!/bin/awkBEGIN {
if (NF == 2) {
if ((x=index($1, ".")) & 0) {
property_name = substr($1, x+1, length($1));
property_name = $1;
printf("&property name="%s" value="${%s}"/& ", property_name, $1);
&&&&推荐文章:
【上篇】【下篇】什么是数据库连接池：
　　用池来管理Connection，这可以重复使用Connection。有了池，所以我们就不用自己来创建Connection，而是通过池来获取Connection对象。
　　当使用完Connection后，调用Connection的close()方法也不会真的关闭Connection，而是把Connection“归还”给池。池就可以再利用这个Connection对象了。
导入DBUtils的工具包：commons-dbutils-1.6.jar
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
导入C3P0的工具包：c3p0-0.9.1.2.jar
首先配置c3p0，目前使用最多的是用c3p0-config.xml文件进行配置：
　　①名字必须是c3p0-config.xml。
　　②必须放在src根目录下，切记不要与web.xml放在一起。
此为c3p0配置：
&default-config&为默认配置，还可以增加&named-config name="mysqlConfig"&，其中mysqlConfig是你自己的配置名字。
& 此时在c3p0工具类中创建&ComboPooledDataSource实例是要加上你的配置名字，即：new&ComboPooledDataSource("mysqlConfig")
#initialPoolSize：连接池初始化时创建的连接数,default : 3，取值应在minPoolSize与maxPoolSize之间&c3p0.initialPoolSize=10
　　#minPoolSize：连接池保持的最小连接数,default : 3 & &c3p0.minPoolSize=10
　　#maxPoolSize：连接池中拥有的最大连接数，如果获得新连接时会使连接总数超过这个值则不会再获取新连接，而是等待其他连接释放，所以这个值有可能会　设计地很大,default : 15 & & &&c3p0.maxPoolSize=50
　　#acquireIncrement：连接池在无空闲连接可用时一次性创建的新数据库连接数,default : 3 &&c3p0.acquireIncrement=5
driverClass 表示你的数据库驱动类，你用的什么数据库
jdbcUrl & 表示你所要连接的数据库，此处automotic_sign为你创建的数据库的名称。
后面的参数 ？useSSl=true 表示是否用SSL连接数据库，此参数不是必须的，mysql版本比较高时，进行连接时会提示该信息。
user和password是 你的数据库的用户名和密码
&?xml version="1.0" encoding="UTF-8"?&
&c3p0-config&
&default-config&
&property name="driverClass"&com.mysql.jdbc.Driver&/property&
&property name="jdbcUrl"&jdbc:mysql://localhost:3306/automotic_sign?useSSL=true&/property&
&property name="user"&root&/property&
&property name="password"&root&/property&
&property name="initialPoolSize"&5&/property&
&property name="minPoolSize"&2&/property&
&property name="acquireIncrement"&3&/property&
&property name="maxPoolSize"&10&/property&
&/default-config&
&/c3p0-config&
创建c3p0工具类与dbutils工具类：
package com.
import java.sql.C
import java.sql.SQLE
import javax.sql.DataS
import mons.dbutils.QueryR
import com.mchange.boPooledDataS
public class Jdbcutils {
private static ComboPooledDataSource dataS//创建c3p0连接，整个项目有一个连接池就可以了，设为static只要实例化一次
dataSource = new ComboPooledDataSource();
public static DataSource getDataSource() {
return dataS
public static QueryRunner getQueryRunner(){//创建DButils常用工具类QueryRunner的对象
return new QueryRunner(dataSource);
阅读(...) 评论()匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。C3P0连接池+MySQL的配置以及wait_timeout问题的解决
一、配置环境
spring4.2.4+mybatis3.2.8+c3p0-0.9.1.2+Mysql5.6.24
二、c3p0的配置详解及spring+c3p0配置
1.配置详解
官方文档 : /projects/c3p0/index.html
& default-config&
& property name=&acquireRetryAttempts&&30
& property name=&acquireRetryDelay&&1000
& property name=&autoCommitOnClose&&false
& property name=&numHelperThreads&&3
& property name=&preferredTestQuery&&select id from test where id=1
2.spring+mybatis+c3p0的基本配置
初始化基本配置信息如下：
Initializing c3p0 pool... com.mchange.boPooledDataSource [ acquireIncrement -& 3, acquireRetryAttempts -& 30, acquireRetryDelay -& 1000, autoCommitOnClose -& false, automaticTestTable -& null, breakAfterAcquireFailure -& false, checkoutTimeout -& 0, connectionCustomizerClassName -& null, connectionTesterClassName -& com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -& 1hge26l9jv0ov961czeg8w|a2f51c, debugUnreturnedConnectionStackTraces -& false, description -& null, driverClass -& com..jdbc.Driver, factoryClassLocation -& null, forceIgnoreUnresolvedTransactions -& false, identityToken -& 1hge26l9jv0ov961czeg8w|a2f51c, idleConnectionTestPeriod -& 0, initialPoolSize -& 3, jdbcUrl -& jdbc:mysql://192.168.6.24:3306/ETeam, maxAdministrativeTaskTime -& 0, maxConnectionAge -& 0, maxIdleTime -& 0, maxIdleTimeExcessConnections -& 0, maxPoolSize -& 15, maxStatements -& 0, maxStatementsPerConnection -& 0, minPoolSize -& 3, numHelperThreads -& 3, numThreadsAwaitingCheckoutDefaultUser -& 0, preferredTestQuery -& null, properties -& {user=******, password=******}, propertyCycle -& 0, testConnectionOnCheckin -& false, testConnectionOnCheckout -& false, unreturnedConnectionTimeout -& 0, usesTraditionalReflectiveProxies -& false ]
三、遇到的问题：
1.问题log：
严重: Servlet.service() for servlet [ETeam] in context with path [/ETeam] threw exception [Reque nested exception is org.springframework.dao.RecoverableDataAccessException:
### Error querying database.
Cause: com.mysql.jdbc.municationsException: The last packet successfully received from the server was 55,518,630 milliseconds ago.
The last packet sent successfully to the server was 55,518,631 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.
### The error may exist in com/mango/mapper/ProductMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM product
### Cause: com.mysql.jdbc.municationsException: The last packet successfully received from the server was 55,518,630 milliseconds ago.
The last packet sent successfully to the server was 55,518,631 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.
; SQL []; The last packet successfully received from the server was 55,518,630 milliseconds ago.
The last packet sent successfully to the server was 55,518,631 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.mysql.jdbc.municationsException: The last packet successfully received from the server was 55,518,630 milliseconds ago.
The last packet sent successfully to the server was 55,518,631 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.] with root cause
java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3634)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2460)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
at sun.reflect.GeneratedMethodAccessor46.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
at com.sun.proxy.$Proxy138.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
at com.sun.proxy.$Proxy357.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy376.getProductIndex(Unknown Source)
at com.mango.service.impl.ProductServiceImpl.getProductIndex(ProductServiceImpl.java:25)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy377.getProductIndex(Unknown Source)
at com.mango.controller.PageController.index(PageController.java:57)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
at org.springframework.web.servlet.m.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:860)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.mango.filter.BaseFilter.doFilter(BaseFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:115)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:169)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:121)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:134)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.sitemesh.webapp.contentfilter.ContentBufferingFilter.bufferAndPostProcess(ContentBufferingFilter.java:169)
at org.sitemesh.webapp.contentfilter.ContentBufferingFilter.doFilter(ContentBufferingFilter.java:126)
at org.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:120)
at org.sitemesh.config.ConfigurableSiteMeshFilter.doFilter(ConfigurableSiteMeshFilter.java:163)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1813)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
从问题log中
The last packet successfully received from the server was 55,518,630 milliseconds ago.
The last packet sent successfully to the server was 55,518,631 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.mysql.jdbc.municationsException: The last packet successfully received from the server was 55,518,630 milliseconds ago.
The last packet sent successfully to the server was 55,518,631 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.] with root cause
很容易看出是由于wait_timeout（服务器关闭非交互连接之前等待活动的秒数）造成的。mysql会根据wait_timeout设置每个空闲连接的超时时间，时间到了就会断开。
2.查看mysql的wait_timeout
mysql& show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set
默认设置28800秒，即8小时，明显连接时间55,518,630 milliseconds超过了my设置的wait_timeout
修改命令：mysql&set global wait_timeout=28800;
3.问题解决
1）log中也给了解决方案：
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.
你应该考虑到期和/或有效性测试连接在应用程序中使用之前,增加服务器为客户机超时配置值,或使用连接器/ J连接属性&autoReconnect = true&来避免这个问题。
2）后两种方法显然不太实用
增加mysql数据库的超时时间，由于最大超时时间是2147483一年，不可无限制增加，再说也不应该随便增加。'autoReconnect=true'如果使用的时候reconnect会影响效率，而且据说mysql5以上无效（本人没试），而且官方也不建议/bug.php?id=5020
3）采用c3p0提供的方案
可参考：/projects/c3p0/index.html#configuring_connection_testing
The most reliable time to test Connections is on check-out. But this is also the most costly choice from a client-performance perspective. Most applications should work quite reliably using a combination of idleConnectionTestPeriod and testConnectionOnCheckin. Both the idle test and the check-in test are performed asynchronously, which can lead to better performance, both perceived and actual.
For some applications, high performance is more important than the risk of an occasional database exception. In its default configuration, c3p0 does no Connection testing at all. Setting a fairly long idleConnectionTestPeriod, and not testing on checkout and check-in at all is an excellent, high-performance approach.
　最可靠的是退出时间测试连接。但这也是最昂贵的从客户端性能的角度选择。大多数应用程序应该使用idleConnectionTestPeriod和testConnectionOnCheckin相当可靠。闲置的测试和登记测试是异步执行的,这可能导致更好的性能,感知和实际。
对于某些应用程序,高性能比偶尔的风险更重要数据库异常。在默认配置中,c3p0没有连接测试。设置一个相当长的idleConnectionTestPeriod,而不是测试是一个很好的检验和登记,高性能的方法。
考虑再三可以如下设置
设置c3p0中连接池内连接的生存周期（idleConnectionTestPeriod）小于数据库中的wait_timeout的值
四、c3p0中用到的定时任务是java中的Timer实现的，实际上是TimerThread的定时执行
BasicResourcePool.java
// this is run by a single-threaded timer, so we don't have
// to worry about multiple threads executing the task at the same
class CheckIdleResourcesTask extends TimerTask
public void run()
//System.err.println(&c3p0-JENNIFER: refurbishing idle resources - & + new Date() + & [& + BasicResourcePool.this + &]&);
if (Debug.DEBUG && Debug.TRACE &= Debug.TRACE_MED && logger.isLoggable(MLevel.FINER))
logger.log(MLevel.FINER, &Refurbishing idle resources - & + new Date() + & [& + BasicResourcePool.this + &]&);
synchronized ( BasicResourcePool.this )
{ checkIdleResources(); }
catch ( ResourceClosedException e ) // one of our async threads died
//e.printStackTrace();
if ( Debug.DEBUG )
if ( logger.isLoggable( MLevel.FINE ) )
logger.log( MLevel.FINE, &a resource pool async thread died.&, e );
unexpectedBreak();
最终测试：
C3P0PooledConnectionPool.java
private void testPooledConnection(Object resc) throws Exception
PooledConnection pc = (PooledConnection)
Throwable[] throwableHolder = EMPTY_THROWABLE_HOLDER;
Connection conn =
Throwable rootCause =
//we don't want any callbacks while we're testing the resource
pc.removeConnectionEventListener( cl );
conn = pc.getConnection(); //checkout proxy connection
// if this is a c3p0 pooled-connection, let's get underneath the
// proxy wrapper, and test the physical connection sometimes.
// this is faster, when the testQuery would not otherwise be cached,
// and it avoids a potential statusOnException() double-check by the
// PooledConnection implementation should the test query provoke an
// Exception
Connection testC
if (scache != null) //when there is a statement cache...
// if it's the slow, default query, faster to test the raw Connection
if (testQuery == null && connectionTesterIsDefault && c3p0PooledConnections)
testConn = ((AbstractC3P0PooledConnection) pc).getPhysicalConnection();
else //test will likely be faster on the proxied Connection, because the test query is probably cached
testConn =
else //where there's no statement cache, better to use the physical connection, if we can get it
if (c3p0PooledConnections)
testConn = ((AbstractC3P0PooledConnection) pc).getPhysicalConnection();
testConn =
if ( testQuery == null )
status = connectionTester.activeCheckConnection( testConn );
if (connectionTester instanceof UnifiedConnectionTester)
throwableHolder = thp.getThrowableHolder();
status = ((UnifiedConnectionTester) connectionTester).activeCheckConnection( testConn, testQuery, throwableHolder );
else if (connectionTester instanceof QueryConnectionTester)
status = ((QueryConnectionTester) connectionTester).activeCheckConnection( testConn, testQuery );
// System.err.println(&[c3p0] WARNING: testQuery '& + testQuery +
// &' ignored. Please set a ConnectionTester that implements & +
// &com.mchange.v2.c3p0.advanced.QueryConnectionTester, or use the & +
// &DefaultConnectionTester, to test with the testQuery.&);
logger.warning(&[c3p0] testQuery '& + testQuery +
&' ignored. Please set a ConnectionTester that implements & +
&com.mchange.v2.c3p0.QueryConnectionTester, or use the & +
&DefaultConnectionTester, to test with the testQuery.&);
status = connectionTester.activeCheckConnection( testConn );
catch (Exception e)
if (Debug.DEBUG)
logger.log(MLevel.FINE, &A Connection test failed with an Exception.&, e);
//e.printStackTrace();
status = ConnectionTester.CONNECTION_IS_INVALID;
System.err.println(&rootCause ------&&);
e.printStackTrace();
rootCause =
if (rootCause == null)
rootCause = throwableHolder[0];
else if (throwableHolder[0] != null && logger.isLoggable(MLevel.FINE))
logger.log(MLevel.FINE, &Internal Connection Test Exception&, throwableHolder[0]);
if (throwableHolder != EMPTY_THROWABLE_HOLDER)
thp.returnThrowableHolder( throwableHolder );
ConnectionUtils.attemptClose( conn ); //invalidate proxy connection
pc.addConnectionEventListener( cl );
//should we move this to CONNECTION_IS_OKAY case? (it should work either way)
switch (status)
case ConnectionTester.CONNECTION_IS_OKAY:
//no problem, babe
case ConnectionTester.DATABASE_IS_INVALID:
rp.resetPool();
//intentional cascade...
case ConnectionTester.CONNECTION_IS_INVALID:
Exception throwMe;
if (rootCause == null)
throwMe = new SQLException(&Connection is invalid&);
throwMe = SqlUtils.toSQLException(&Connection is invalid&, rootCause);
throw throwMe;
throw new Error(&Bad Connection Tester (& +
connectionTester + &) & +
&returned invalid status (& + status + &).&);