程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> C3P0連接池+MySQL的配置及wait_timeout問題的解決方法

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

<c3p0-config>
< default-config>
<!--當連接池中的連接耗盡的時候c3p0一次同時獲取的連接數。Default: 3 -->
<property name="acquireIncrement">3</property>
<!--定義在從數據庫獲取新連接失敗後重復嘗試的次數。Default: 30 --> 
< property name="acquireRetryAttempts">30</property>
<!--兩次連接中間隔時間，單位毫秒。Default: 1000 --> 
< property name="acquireRetryDelay">1000</property>
<!--連接關閉時默認將所有未提交的操作回滾。Default: false --> 
< property name="autoCommitOnClose">false</property>
<!--c3p0將建一張名為Test的空表，並使用其自帶的查詢語句進行測試。如果定義了這個參數 那麼
屬性preferredTestQuery將被忽略。你不能在這張Test表上進行任何操作，它將只供c3p0測試
使用。Default: null-->
<property name="automaticTestTable">Test</property>
<!--獲取連接失敗將會引起所有等待連接池來獲取連接的線程拋出異常。但是數據源仍有效
保留，並在下次調用getConnection()的時候繼續嘗試獲取連接。如果設為true，那麼在嘗試
獲取連接失敗後該數據源將申明已斷開並永久關閉。Default: false-->
<property name="breakAfterAcquireFailure">false</property>
<!--當連接池用完時客戶端調用getConnection()後等待獲取新連接的時間，超時後將 拋出
SQLException,如設為0則無限期等待。單位毫秒。Default: 0 -->
<property name="checkoutTimeout">100</property>
<!--通過實現ConnectionTester或QueryConnectionTester的 類來 測試連接。類名需制定全路徑。
Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester-->
<property name="connectionTesterClassName"></property>
<!--指定c3p0 libraries的路徑，如果（通常都是這樣）在本地即可獲得那麼無需設置，默認null即可
Default: null-->
<property name="factoryClassLocation">null</property>
<!--Strongly disrecommended. Setting this to true may lead to subtle and bizarre bugs.
（文檔原文）作者強烈建議不使用的一個屬性-->
<property name="forceIgnoreUnresolvedTransactions">false</property>
<!--每60秒檢查所有連接池中的空閒連接。Default: 0 不檢測 -->
<property name="idleConnectionTestPeriod">60</property>
<!--初始化時獲取三個連接，取值應在minPoolSize與maxPoolSize之間。 Default: 3 -->
<property name="initialPoolSize">3</property>
<!--最大空閒時間,60秒內未使用則連接被丟棄。若為0則永不丟棄。Default: 0 -->
<property name="maxIdleTime">60</property>
<!--連接池中保留的最大連接數。Default: 15 -->
<property name="maxPoolSize">15</property>
<!--JDBC的標准參數，用以控制數據源內加載的PreparedStatements數量。但 由於預緩存的statements
屬於單個connection而不是整個連接池。所以設置這個參數需要考慮到多方面的因素。
如果maxStatements與maxStatementsPerConnection均為0，則緩存被關閉。Default: 0-->
<property name="maxStatements">100</property>
<!--maxStatementsPerConnection定義了連接池內單個連接所 擁有的最大緩存statements數。Default: 0 -->
<property name="maxStatementsPerConnection"></property>
<!--c3p0是異步操作的，緩慢的JDBC操作通過幫助進程完成。擴展這些操作可以有效的提升性 能
通過多線程實現多個操作同時被執行。Default: 3--> 
< property name="numHelperThreads">3</property>
<!--當用戶調用getConnection()時使root用戶成為去獲取連接的用戶。主要用於 連接池連接非c3p0
的數據源時。Default: null-->
<property name="overrideDefaultUser">root</property>
<!--與overrideDefaultUser參數對應使用的一個參數。Default: null-->
<property name="overrideDefaultPassword">password</property>
<!--密碼。Default: null-->
<property name="password"></property>
<!--定義所有連接測試都執行的測試語句。在使用連接測試的情況下這個一顯著提高測試速度。注意：
測試的表必須在初始數據源的時候就存在。Default: null--> 
< property name="preferredTestQuery">select id from test where id=1</property>
<!--用戶修改系統配置參數執行前最多等待300秒。Default: 300 -->
<property name="propertyCycle">300</property>
<!--因性能消耗大請只在需要的時候使用它。如果設為true那麼在每個connection提交 的
時候都將校驗其有效性。建議使用idleConnectionTestPeriod或automaticTestTable
等方法來提升連接測試的性能。Default: false -->
<property name="testConnectionOnCheckout">false</property>
<!--如果設為true那麼在取得連接的同時將校驗連接的有效性。Default: false -->
<property name="testConnectionOnCheckin">true</property>
<!--用戶名。Default: null-->
<property name="user">root</property>
<!--早期的c3p0版本對JDBC接口采用動態反射代理。在早期版本用途廣泛的情況下這個參數
允許用戶恢復到動態反射代理以解決不穩定的故障。最新的非反射代理更快並且已經開始
廣泛的被使用，所以這個參數未必有用。現在原先的動態反射與新的非反射代理同時受到
支持，但今後可能的版本可能不支持動態反射代理。Default: false-->
<property name="usesTraditionalReflectiveProxies">false</property>

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

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
<property name="driverClass" value="${jdbc.driver}"/> 
<property name="jdbcUrl" value="${jdbc.url}"/> 
<property name="user" value="${jdbc.user}"/> 
<property name="password" value="${jdbc.password}"/> 
</bean>

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

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的值

<!--每5小時檢查所有連接池中的空閒連接。防止mysql wait_timeout（默認的為8小時） --> 
<property name="idleConnectionTestPeriod" value="18000"/>

四、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問題的解決方法，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對網站的支持！