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

2024-03-11 22:18:02 46

一、配置环境

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-->
<propertyname="acquireIncrement">3</property>
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default:30-->
<propertyname="acquireRetryAttempts">30</property>
<!--两次连接中间隔时间，单位毫秒。Default:1000-->
<propertyname="acquireRetryDelay">1000</property>
<!--连接关闭时默认将所有未提交的操作回滚。Default:false-->
<propertyname="autoCommitOnClose">false</property>
<!--c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么
属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试
使用。Default:null-->
<propertyname="automaticTestTable">Test</property>
<!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效
保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试
获取连接失败后该数据源将申明已断开并永久关闭。Default:false-->
<propertyname="breakAfterAcquireFailure">false</property>
<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出
SQLException,如设为0则无限期等待。单位毫秒。Default:0-->
<propertyname="checkoutTimeout">100</property>
<!--通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径。
Default:com.mchange.v2.c3p0.impl.DefaultConnectionTester-->
<propertyname="connectionTesterClassName"></property>
<!--指定c3p0libraries的路径，如果（通常都是这样）在本地即可获得那么无需设置，默认null即可
Default:null-->
<propertyname="factoryClassLocation">null</property>
<!--Stronglydisrecommended.Settingthistotruemayleadtosubtleandbizarrebugs.
（文档原文）作者强烈建议不使用的一个属性-->
<propertyname="forceIgnoreUnresolvedTransactions">false</property>
<!--每60秒检查所有连接池中的空闲连接。Default:0不检测-->
<propertyname="idleConnectionTestPeriod">60</property>
<!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default:3-->
<propertyname="initialPoolSize">3</property>
<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default:0-->
<propertyname="maxIdleTime">60</property>
<!--连接池中保留的最大连接数。Default:15-->
<propertyname="maxPoolSize">15</property>
<!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements
属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default:0-->
<propertyname="maxStatements">100</property>
<!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default:0-->
<propertyname="maxStatementsPerConnection"></property>
<!--c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能
通过多线程实现多个操作同时被执行。Default:3-->
<propertyname="numHelperThreads">3</property>
<!--当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0
的数据源时。Default:null-->
<propertyname="overrideDefaultUser">root</property>
<!--与overrideDefaultUser参数对应使用的一个参数。Default:null-->
<propertyname="overrideDefaultPassword">password</property>
<!--密码。Default:null-->
<propertyname="password"></property>
<!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意：
测试的表必须在初始数据源的时候就存在。Default:null-->
<propertyname="preferredTestQuery">selectidfromtestwhereid=1</property>
<!--用户修改系统配置参数执行前最多等待300秒。Default:300-->
<propertyname="propertyCycle">300</property>
<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的
时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
等方法来提升连接测试的性能。Default:false-->
<propertyname="testConnectionOnCheckout">false</property>
<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default:false-->
<propertyname="testConnectionOnCheckin">true</property>
<!--用户名。Default:null-->
<propertyname="user">root</property>
<!--早期的c3p0版本对JDBC接口采用动态反射代理。在早期版本用途广泛的情况下这个参数
允许用户恢复到动态反射代理以解决不稳定的故障。最新的非反射代理更快并且已经开始
广泛的被使用，所以这个参数未必有用。现在原先的动态反射与新的非反射代理同时受到
支持，但今后可能的版本可能不支持动态反射代理。Default:false-->
<propertyname="usesTraditionalReflectiveProxies">false</property>

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

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

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

Initializingc3p0pool...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()forservlet[ETeam]incontextwithpath[/ETeam]threwexception[Requestprocessingfailed;nestedexceptionisorg.springframework.dao.RecoverableDataAccessException:
###Errorqueryingdatabase.Cause:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Thelastpacketsuccessfullyreceivedfromtheserverwas55,518,630millisecondsago.Thelastpacketsentsuccessfullytotheserverwas55,518,631millisecondsago.islongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.
###Theerrormayexistincom/mango/mapper/ProductMapper.java(bestguess)
###TheerrormayinvolvedefaultParameterMap
###Theerroroccurredwhilesettingparameters
###SQL:SELECT*FROMproduct
###Cause:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Thelastpacketsuccessfullyreceivedfromtheserverwas55,518,630millisecondsago.Thelastpacketsentsuccessfullytotheserverwas55,518,631millisecondsago.islongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.
;SQL[];Thelastpacketsuccessfullyreceivedfromtheserverwas55,518,630millisecondsago.Thelastpacketsentsuccessfullytotheserverwas55,518,631millisecondsago.islongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.;nestedexceptioniscom.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Thelastpacketsuccessfullyreceivedfromtheserverwas55,518,630millisecondsago.Thelastpacketsentsuccessfullytotheserverwas55,518,631millisecondsago.islongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.]withrootcause
java.net.SocketException:Connectionresetbypeer:socketwriteerror
atjava.net.SocketOutputStream.socketWrite0(NativeMethod)
atjava.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
atjava.net.SocketOutputStream.write(SocketOutputStream.java:159)
atjava.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
atjava.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
atcom.mysql.jdbc.MysqlIO.send(MysqlIO.java:3634)
atcom.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2460)
atcom.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
atcom.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
atcom.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
atcom.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
atcom.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
atsun.reflect.GeneratedMethodAccessor46.invoke(UnknownSource)
atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
atjava.lang.reflect.Method.invoke(Method.java:606)
atorg.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
atcom.sun.proxy.$Proxy138.execute(UnknownSource)
atorg.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)
atorg.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
atorg.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
atorg.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
atorg.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
atorg.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
atorg.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
atorg.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
atorg.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)
atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
atjava.lang.reflect.Method.invoke(Method.java:606)
atorg.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
atcom.sun.proxy.$Proxy357.selectList(UnknownSource)
atorg.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
atorg.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
atorg.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
atorg.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
atcom.sun.proxy.$Proxy376.getProductIndex(UnknownSource)
atcom.mango.service.impl.ProductServiceImpl.getProductIndex(ProductServiceImpl.java:25)
atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)
atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
atjava.lang.reflect.Method.invoke(Method.java:606)
atorg.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
atorg.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
atorg.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
atorg.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280)
atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
atorg.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
atorg.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
atcom.sun.proxy.$Proxy377.getProductIndex(UnknownSource)
atcom.mango.controller.PageController.index(PageController.java:57)
atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)
atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
atjava.lang.reflect.Method.invoke(Method.java:606)
atorg.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
atorg.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
atorg.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
atorg.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)
atorg.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
atorg.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
atorg.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
atorg.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
atorg.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
atorg.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:860)
atjavax.servlet.http.HttpServlet.service(HttpServlet.java:621)
atorg.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
atjavax.servlet.http.HttpServlet.service(HttpServlet.java:722)
atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
atcom.mango.filter.BaseFilter.doFilter(BaseFilter.java:34)
atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
atorg.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
atorg.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:115)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:169)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
atorg.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:121)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66)
atorg.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
atorg.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:134)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
atorg.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
atorg.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
atorg.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
atorg.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
atorg.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
atorg.sitemesh.webapp.contentfilter.ContentBufferingFilter.bufferAndPostProcess(ContentBufferingFilter.java:169)
atorg.sitemesh.webapp.contentfilter.ContentBufferingFilter.doFilter(ContentBufferingFilter.java:126)
atorg.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:120)
atorg.sitemesh.config.ConfigurableSiteMeshFilter.doFilter(ConfigurableSiteMeshFilter.java:163)
atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
atorg.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
atorg.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
atorg.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
atorg.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
atorg.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
atorg.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
atorg.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
atorg.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
atorg.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
atorg.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
atorg.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
atorg.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
atorg.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1813)
atjava.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
atjava.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
atjava.lang.Thread.run(Thread.java:745)

从问题log中

Thelastpacketsuccessfullyreceivedfromtheserverwas55,518,630millisecondsago.Thelastpacketsentsuccessfullytotheserverwas55,518,631millisecondsago.islongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.;nestedexceptioniscom.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Thelastpacketsuccessfullyreceivedfromtheserverwas55,518,630millisecondsago.Thelastpacketsentsuccessfullytotheserverwas55,518,631millisecondsago.islongerthantheserverconfiguredvalueof'wait_timeout'.Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.]withrootcause

很容易看出是由于wait_timeout（服务器关闭非交互连接之前等待活动的秒数）造成的。MySQL会根据wait_timeout设置每个空闲连接的超时时间，时间到了就会断开。

2.查看mysql的wait_timeout

mysql>showglobalvariableslike'wait_timeout';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|wait_timeout|28800|
+---------------+-------+
1rowinset

默认设置28800秒，即8小时，明显连接时间55,518,630milliseconds超过了mysql数据库设置的wait_timeout

修改命令：mysql>setglobalwait_timeout=28800;

3.问题解决

1）log中也给了解决方案：

Youshouldconsidereitherexpiringand/ortestingconnectionvaliditybeforeuseinyourapplication,increasingtheserverconfiguredvaluesforclienttimeouts,orusingtheConnector/Jconnectionproperty'autoReconnect=true'toavoidthisproblem.

你应该考虑到期和/或有效性测试连接在应用程序中使用之前,增加服务器为客户机超时配置值,或使用连接器/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

ThemostreliabletimetotestConnectionsisoncheck-out.Butthisisalsothemostcostlychoicefromaclient-performanceperspective.MostapplicationsshouldworkquitereliablyusingacombinationofidleConnectionTestPeriodandtestConnectionOnCheckin.Boththeidletestandthecheck-intestareperformedasynchronously,whichcanleadtobetterperformance,bothperceivedandactual.
Forsomeapplications,highperformanceismoreimportantthantheriskofanoccasionaldatabaseexception.Initsdefaultconfiguration,c3p0doesnoConnectiontestingatall.SettingafairlylongidleConnectionTestPeriod,andnottestingoncheckoutandcheck-inatallisanexcellent,high-performanceapproach.

最可靠的是退出时间测试连接。但这也是最昂贵的从客户端性能的角度选择。大多数应用程序应该使用idleConnectionTestPeriod和testConnectionOnCheckin相当可靠。闲置的测试和登记测试是异步执行的,这可能导致更好的性能,感知和实际。

对于某些应用程序,高性能比偶尔的风险更重要数据库异常。在默认配置中,c3p0没有连接测试。设置一个相当长的idleConnectionTestPeriod,而不是测试是一个很好的检验和登记,高性能的方法。

考虑再三可以如下设置

设置c3p0中连接池内连接的生存周期（idleConnectionTestPeriod）小于数据库中的wait_timeout的值

<!--每5小时检查所有连接池中的空闲连接。防止mysqlwait_timeout（默认的为8小时）-->
<propertyname="idleConnectionTestPeriod"value="18000"/>

四、c3p0中用到的定时任务是Java中的Timer实现的，实际上是TimerThread的定时执行

checkidle源码

BasicResourcePool.java

//thisisrunbyasingle-threadedtimer,sowedon'thave
//toworryaboutmultiplethreadsexecutingthetaskatthesame
//time
classCheckIdleResourcesTaskextendsTimerTask
{
publicvoidrun()
{
try
{
//System.err.println("c3p0-JENNIFER:refurbishingidleresources-"+newDate()+"["+BasicResourcePool.this+"]");
if(Debug.DEBUG&&Debug.TRACE>=Debug.TRACE_MED&&logger.isLoggable(MLevel.FINER))
logger.log(MLevel.FINER,"Refurbishingidleresources-"+newDate()+"["+BasicResourcePool.this+"]");
synchronized(BasicResourcePool.this)
{checkIdleResources();}
}
catch(ResourceClosedExceptione)//oneofourasyncthreadsdied
{
//e.printStackTrace();
if(Debug.DEBUG)
{
if(logger.isLoggable(MLevel.FINE))
logger.log(MLevel.FINE,"aresourcepoolasyncthreaddied.",e);
}
unexpectedBreak();
}
}
}

最终测试：

C3P0PooledConnectionPool.java

privatevoidtestPooledConnection(Objectresc)throwsException
{
PooledConnectionpc=(PooledConnection)resc;
Throwable[]throwableHolder=EMPTY_THROWABLE_HOLDER;
intstatus;
Connectionconn=null;
ThrowablerootCause=null;
try
{
//wedon'twantanycallbackswhilewe'retestingtheresource
pc.removeConnectionEventListener(cl);
conn=pc.getConnection();//checkoutproxyconnection
//ifthisisac3p0pooled-connection,let'sgetunderneaththe
//proxywrapper,andtestthephysicalconnectionsometimes.
//thisisfaster,whenthetestQuerywouldnototherwisebecached,
//anditavoidsapotentialstatusOnException()double-checkbythe
//PooledConnectionimplementationshouldthetestqueryprovokean
//Exception
ConnectiontestConn;
if(scache!=null)//whenthereisastatementcache...
{
//ifit'stheslow,defaultquery,fastertotesttherawConnection
if(testQuery==null&&connectionTesterIsDefault&&c3p0PooledConnections)
testConn=((AbstractC3P0PooledConnection)pc).getPhysicalConnection();
else//testwilllikelybefasterontheproxiedConnection,becausethetestqueryisprobablycached
testConn=conn;
}
else//wherethere'snostatementcache,bettertousethephysicalconnection,ifwecangetit
{
if(c3p0PooledConnections)
testConn=((AbstractC3P0PooledConnection)pc).getPhysicalConnection();
else
testConn=conn;
}
if(testQuery==null)
status=connectionTester.activeCheckConnection(testConn);
else
{
if(connectionTesterinstanceofUnifiedConnectionTester)
{
throwableHolder=thp.getThrowableHolder();
status=((UnifiedConnectionTester)connectionTester).activeCheckConnection(testConn,testQuery,throwableHolder);
}
elseif(connectionTesterinstanceofQueryConnectionTester)
status=((QueryConnectionTester)connectionTester).activeCheckConnection(testConn,testQuery);
else
{
//System.err.println("[c3p0]WARNING:testQuery'"+testQuery+
//"'ignored.PleasesetaConnectionTesterthatimplements"+
//"com.mchange.v2.c3p0.advanced.QueryConnectionTester,orusethe"+
//"DefaultConnectionTester,totestwiththetestQuery.");
logger.warning("[c3p0]testQuery'"+testQuery+
"'ignored.PleasesetaConnectionTesterthatimplements"+
"com.mchange.v2.c3p0.QueryConnectionTester,orusethe"+
"DefaultConnectionTester,totestwiththetestQuery.");
status=connectionTester.activeCheckConnection(testConn);
}
}
}
catch(Exceptione)
{
if(Debug.DEBUG)
logger.log(MLevel.FINE,"AConnectiontestfailedwithanException.",e);
//e.printStackTrace();
status=ConnectionTester.CONNECTION_IS_INVALID;
//System.err.println("rootCause------>");
//e.printStackTrace();
rootCause=e;
}
finally
{
if(rootCause==null)
rootCause=throwableHolder[0];
elseif(throwableHolder[0]!=null&&logger.isLoggable(MLevel.FINE))
logger.log(MLevel.FINE,"InternalConnectionTestException",throwableHolder[0]);
if(throwableHolder!=EMPTY_THROWABLE_HOLDER)
thp.returnThrowableHolder(throwableHolder);
ConnectionUtils.attemptClose(conn);//invalidateproxyconnection
pc.addConnectionEventListener(cl);//shouldwemovethistoCONNECTION_IS_OKAYcase?(itshouldworkeitherway)
}
switch(status)
{
caseConnectionTester.CONNECTION_IS_OKAY:
break;//noproblem,babe
caseConnectionTester.DATABASE_IS_INVALID:
rp.resetPool();
//intentionalcascade...
caseConnectionTester.CONNECTION_IS_INVALID:
ExceptionthrowMe;
if(rootCause==null)
throwMe=newSQLException("Connectionisinvalid");
else
throwMe=SqlUtils.toSQLException("Connectionisinvalid",rootCause);
throwthrowMe;
default:
thrownewError("BadConnectionTester("+
connectionTester+")"+
"returnedinvalidstatus("+status+").");
}
}

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

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

热门推荐

随机推荐