C3P0连接池+MySQL的配置及wait_timeout问题的解决方法_MySQL

一、配置环境

spring4.2.4+mybatis3.2.8+c3p0-0.9.1.2+Mysql5.6.24

二、c3p0的配置详解及spring+c3p0配置

1.配置详解

官方文档 : http://www.mchange.com/projects/c3p0/index.html

330
 
1000
 
false

Testfalse100nullfalse60360151003

rootpasswordselect id from test where id=1

300falsetruerootfalse

2.spring+mybatis+c3p0的基本配置

初始化基本配置信息如下：

Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ 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.mysql.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 [Request processing failed; nested exception is org.springframework.dao.RecoverableDataAccessException: 
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 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.exceptions.jdbc4.CommunicationsException: 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.exceptions.jdbc4.CommunicationsException: 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.mvc.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.exceptions.jdbc4.CommunicationsException: 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超过了mysql数据库设置的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以上无效（本人没试），而且官方也不建议http://bugs.mysql.com/bug.php?id=5020

3）采用c3p0提供的方案

可参考：http://www.mchange.com/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的定时执行

checkidle源码

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 
// time 
class CheckIdleResourcesTask extends TimerTask 
{ 
public void run() 
{ 
try 
{ 
//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) resc; 
Throwable[] throwableHolder = EMPTY_THROWABLE_HOLDER; 
int status; 
Connection conn = null; 
Throwable rootCause = null; 
try 
{ 
//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 testConn; 
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 = conn; 
} 
else //where there's no statement cache, better to use the physical connection, if we can get it 
{ 
if (c3p0PooledConnections) 
testConn = ((AbstractC3P0PooledConnection) pc).getPhysicalConnection(); 
else 
testConn = conn; 
} 
if ( testQuery == null ) 
status = connectionTester.activeCheckConnection( testConn ); 
else 
{ 
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 ); 
else 
{ 
// 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 = e; 
} 
finally 
{ 
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: 
break; //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"); 
else 
throwMe = SqlUtils.toSQLException("Connection is invalid", rootCause); 
throw throwMe; 
default: 
throw new Error("Bad Connection Tester (" + 
connectionTester + ") " + 
"returned invalid status (" + status + ")."); 
} 
}

以上所述是小编给大家介绍的C3P0连接池+MySQL的配置及wait_timeout问题的解决方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！