C3P0连接池+MySQL的配置及wait_timeout问题的解决方法
一、配置环境
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问题的解决方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!