《Mysql應(yīng)用C3P0連接池+MySQL的配置及wait_timeout問題的解決方法》要點(diǎn)：
本文介紹了Mysql應(yīng)用C3P0連接池+MySQL的配置及wait_timeout問題的解決方法，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
?一、配置環(huán)境MYSQL數(shù)據(jù)庫
spring4.2.4+mybatis3.2.8+c3p0-0.9.1.2+Mysql5.6.24MYSQL數(shù)據(jù)庫
二、c3p0的配置詳解及spring+c3p0配置MYSQL數(shù)據(jù)庫
1.配置詳解MYSQL數(shù)據(jù)庫
官方文檔 : http://www.mchange.com/projects/c3p0/index.htmlMYSQL數(shù)據(jù)庫
<c3p0-config> < default-config> <!--當(dāng)連接池中的連接耗盡的時(shí)候c3p0一次同時(shí)獲取的連接數(shù).Default: 3 --> <property name="acquireIncrement">3</property> <!--定義在從數(shù)據(jù)庫獲取新連接失敗后重復(fù)嘗試的次數(shù).Default: 30 --> < property name="acquireRetryAttempts">30</property> <!--兩次連接中間隔時(shí)間,單位毫秒.Default: 1000 --> < property name="acquireRetryDelay">1000</property> <!--連接關(guān)閉時(shí)默認(rèn)將所有未提交的操作回滾.Default: false --> < property name="autoCommitOnClose">false</property> <!--c3p0將建一張名為Test的空表,并使用其自帶的查詢語句進(jìn)行測(cè)試.如果定義了這個(gè)參數(shù) 那么 屬性preferredTestQuery將被忽略.你不能在這張Test表上進(jìn)行任何操作,它將只供c3p0測(cè)試 使用.Default: null--> <property name="automaticTestTable">Test</property> <!--獲取連接失敗將會(huì)引起所有等待連接池來獲取連接的線程拋出異常.但是數(shù)據(jù)源仍有效 保留,并在下次調(diào)用getConnection()的時(shí)候繼續(xù)嘗試獲取連接.如果設(shè)為true,那么在嘗試 獲取連接失敗后該數(shù)據(jù)源將申明已斷開并永久關(guān)閉.Default: false--> <property name="breakAfterAcquireFailure">false</property> <!--當(dāng)連接池用完時(shí)客戶端調(diào)用getConnection()后等待獲取新連接的時(shí)間,超時(shí)后將 拋出 SQLException,如設(shè)為0則無限期等待.單位毫秒.Default: 0 --> <property name="checkoutTimeout">100</property> <!--通過實(shí)現(xiàn)ConnectionTester或QueryConnectionTester的 類來 測(cè)試連接.類名需制定全路徑. Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester--> <property name="connectionTesterClassName"></property> <!--指定c3p0 libraries的路徑,如果(通常都是這樣)在本地即可獲得那么無需設(shè)置,默認(rèn)null即可 Default: null--> <property name="factoryClassLocation">null</property> <!--Strongly disrecommended. Setting this to true may lead to subtle and bizarre bugs. (文檔原文)作者強(qiáng)烈建議不使用的一個(gè)屬性--> <property name="forceIgnoreUnresolvedTransactions">false</property> <!--每60秒檢查所有連接池中的空閑連接.Default: 0 不檢測(cè) --> <property name="idleConnectionTestPeriod">60</property> <!--初始化時(shí)獲取三個(gè)連接,取值應(yīng)在minPoolSize與maxPoolSize之間. Default: 3 --> <property name="initialPoolSize">3</property> <!--最大空閑時(shí)間,60秒內(nèi)未使用則連接被丟棄.若為0則永不丟棄.Default: 0 --> <property name="maxIdleTime">60</property> <!--連接池中保留的最大連接數(shù).Default: 15 --> <property name="maxPoolSize">15</property> <!--JDBC的標(biāo)準(zhǔn)參數(shù),用以控制數(shù)據(jù)源內(nèi)加載的PreparedStatements數(shù)量.但 由于預(yù)緩存的statements 屬于單個(gè)connection而不是整個(gè)連接池.所以設(shè)置這個(gè)參數(shù)需要考慮到多方面的因素. 如果maxStatements與maxStatementsPerConnection均為0,則緩存被關(guān)閉.Default: 0--> <property name="maxStatements">100</property> <!--maxStatementsPerConnection定義了連接池內(nèi)單個(gè)連接所 擁有的最大緩存statements數(shù).Default: 0 --> <property name="maxStatementsPerConnection"></property> <!--c3p0是異步操作的,緩慢的JDBC操作通過幫助進(jìn)程完成.擴(kuò)展這些操作可以有效的提升性 能 通過多線程實(shí)現(xiàn)多個(gè)操作同時(shí)被執(zhí)行.Default: 3--> < property name="numHelperThreads">3</property> <!--當(dāng)用戶調(diào)用getConnection()時(shí)使root用戶成為去獲取連接的用戶.主要用于 連接池連接非c3p0 的數(shù)據(jù)源時(shí).Default: null--> <property name="overrideDefaultUser">root</property> <!--與overrideDefaultUser參數(shù)對(duì)應(yīng)使用的一個(gè)參數(shù).Default: null--> <property name="overrideDefaultPassword">password</property> <!--暗碼.Default: null--> <property name="password"></property> <!--定義所有連接測(cè)試都執(zhí)行的測(cè)試語句.在使用連接測(cè)試的情況下這個(gè)一顯著提高測(cè)試速度.注意： 測(cè)試的表必須在初始數(shù)據(jù)源的時(shí)候就存在.Default: null--> < property name="preferredTestQuery">select id from test where id=1</property> <!--用戶修改系統(tǒng)配置參數(shù)執(zhí)行前最多等待300秒.Default: 300 --> <property name="propertyCycle">300</property> <!--因性能消耗大請(qǐng)只在需要的時(shí)候使用它.如果設(shè)為true那么在每個(gè)connection提交 的 時(shí)候都將校驗(yàn)其有效性.建議使用idleConnectionTestPeriod或automaticTestTable 等方法來提升連接測(cè)試的性能.Default: false --> <property name="testConnectionOnCheckout">false</property> <!--如果設(shè)為true那么在取得連接的同時(shí)將校驗(yàn)連接的有效性.Default: false --> <property name="testConnectionOnCheckin">true</property> <!--用戶名.Default: null--> <property name="user">root</property> <!--早期的c3p0版本對(duì)JDBC接口采用動(dòng)態(tài)反射代理.在早期版本用途廣泛的情況下這個(gè)參數(shù) 允許用戶恢復(fù)到動(dòng)態(tài)反射代理以解決不穩(wěn)定的故障.最新的非反射代理更快并且已經(jīng)開始 廣泛的被使用,所以這個(gè)參數(shù)未必有用.現(xiàn)在原先的動(dòng)態(tài)反射與新的非反射代理同時(shí)受到 支持,但今后可能的版本可能不支持動(dòng)態(tài)反射代理.Default: false--> <property name="usesTraditionalReflectiveProxies">false</property>
2.spring+mybatis+c3p0的基本配置MYSQL數(shù)據(jù)庫
<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>
初始化基本配置信息如下：MYSQL數(shù)據(jù)庫
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 ]
三、遇到的問題：MYSQL數(shù)據(jù)庫
1.問題log：MYSQL數(shù)據(jù)庫
嚴(yán)重: 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中MYSQL數(shù)據(jù)庫
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(服務(wù)器關(guān)閉非交互連接之前等待活動(dòng)的秒數(shù))造成的.MySQL會(huì)根據(jù)wait_timeout設(shè)置每個(gè)空閑連接的超時(shí)時(shí)間,時(shí)間到了就會(huì)斷開.MYSQL數(shù)據(jù)庫
2.查看mysql的wait_timeoutMYSQL數(shù)據(jù)庫
mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set
默認(rèn)設(shè)置28800秒,即8小時(shí),明顯連接時(shí)間55,518,630 milliseconds超過了mysql數(shù)據(jù)庫設(shè)置的wait_timeoutMYSQL數(shù)據(jù)庫
修改命令：mysql>set global wait_timeout=28800;MYSQL數(shù)據(jù)庫
3.問題解決MYSQL數(shù)據(jù)庫
1)log中也給了解決方案：MYSQL數(shù)據(jù)庫
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.MYSQL數(shù)據(jù)庫
你應(yīng)該考慮到期和/或有效性測(cè)試連接在應(yīng)用程序中使用之前,增加服務(wù)器為客戶機(jī)超時(shí)配置值,或使用連接器/ J連接屬性“autoReconnect = true”來避免這個(gè)問題.MYSQL數(shù)據(jù)庫
2)后兩種辦法顯然不太實(shí)用MYSQL數(shù)據(jù)庫
增加mysql數(shù)據(jù)庫的超時(shí)時(shí)間,由于最大超時(shí)時(shí)間是2147483一年,不可無限制增加,再說也不應(yīng)該隨便增加.'autoReconnect=true'如果使用的時(shí)候reconnect會(huì)影響效率,而且據(jù)說mysql5以上無效(本人沒試),而且官方也不建議http://bugs.mysql.com/bug.php?id=5020MYSQL數(shù)據(jù)庫
3)采用c3p0提供的方案MYSQL數(shù)據(jù)庫
可參考：http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testingMYSQL數(shù)據(jù)庫
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.
MYSQL數(shù)據(jù)庫
最可靠的是退出時(shí)間測(cè)試連接.但這也是最昂貴的從客戶端性能的角度選擇.大多數(shù)應(yīng)用程序應(yīng)該使用idleConnectionTestPeriod和testConnectionOnCheckin相當(dāng)可靠.閑置的測(cè)試和登記測(cè)試是異步執(zhí)行的,這可能導(dǎo)致更好的性能,感知和實(shí)際.MYSQL數(shù)據(jù)庫
對(duì)于某些應(yīng)用程序,高性能比偶爾的風(fēng)險(xiǎn)更重要數(shù)據(jù)庫異常.在默認(rèn)配置中,c3p0沒有連接測(cè)試.設(shè)置一個(gè)相當(dāng)長(zhǎng)的idleConnectionTestPeriod,而不是測(cè)試是一個(gè)很好的檢驗(yàn)和登記,高性能的辦法.MYSQL數(shù)據(jù)庫
考慮再三可以如下設(shè)置MYSQL數(shù)據(jù)庫
設(shè)置c3p0中連接池內(nèi)連接的生存周期(idleConnectionTestPeriod)小于數(shù)據(jù)庫中的wait_timeout的值MYSQL數(shù)據(jù)庫
<!--每5小時(shí)檢查所有連接池中的空閑連接.防止mysql wait_timeout(默認(rèn)的為8小時(shí)) --> <property name="idleConnectionTestPeriod" value="18000"/>
四、c3p0中用到的定時(shí)任務(wù)是Java中的Timer實(shí)現(xiàn)的,實(shí)際上是TimerThread的定時(shí)執(zhí)行MYSQL數(shù)據(jù)庫
checkidle源碼MYSQL數(shù)據(jù)庫
BasicResourcePool.java
MYSQL數(shù)據(jù)庫
// 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();
}
}
}
最終測(cè)試：MYSQL數(shù)據(jù)庫
C3P0PooledConnectionPool.java
MYSQL數(shù)據(jù)庫
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問題的解決辦法,希望對(duì)大家有所幫助,如果大家有任何疑問請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的.在此也非常感謝大家對(duì)維易PHP網(wǎng)站的支持!MYSQL數(shù)據(jù)庫
維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql應(yīng)用C3P0連接池+MySQL的配置及wait_timeout問題的解決方法》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/13592.html
