安装mysql初始化数据库失败C3P0连接池+MySQL的配置及wait_timeout问题的解决⽅法
⼀、配置环境
spring4.2.4+mybatis3.2.8+c3p0-0.9.1.2+Mysql5.6.24
⼆、c3p0的配置详解及spring+c3p0配置
1.配置详解
<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: hange.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="hange.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 hange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTim
三、遇到的问题：
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: ptions.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 millisec ### 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: ptions.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 ; 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 conside java.SocketException: Connection reset by peer: socket write error
at java.SocketOutputStream.socketWrite0(Native Method)
at java.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
at java.SocketOutputStream.write(SocketOutputStream.java:159)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
sql.jdbc.MysqlIO.send(MysqlIO.java:3634)
sql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2460)
sql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
sql.SQL(ConnectionImpl.java:2551)
sql.uteInternal(PreparedStatement.java:1861)
sql.ute(PreparedStatement.java:1192)
hange.v2.c3p0.ute(NewProxyPreparedStatement.java:989)
flect.GeneratedMethodAccessor46.invoke(Unknown Source)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
at com.sun.proxy.$ute(Unknown Source)
at org.utor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)
at org.utor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
at org.utor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
at org.utor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.utor.BaseExecutor.query(BaseExecutor.java:137)
at org.utor.CachingExecutor.query(CachingExecutor.java:96)
at org.utor.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)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:606)
batis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
at com.sun.proxy.$Proxy357.selectList(Unknown Source)
batis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
at org.apache.ibatis.uteForMany(MapperMethod.java:119)
at org.apache.ibatis.ute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$ProductIndex(Unknown Source)
at com.mango.service.ProductIndex(ProductServiceImpl.java:25)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.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 ansaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at ansaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280)
at ansaction.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.$ProductIndex(Unknown Source)
at ller.PageController.index(PageController.java:57)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:606)
at org.hod.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMet
hod.java:221)
at org.hod.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
at org.springframework.web.hod.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.hod.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814) at org.springframework.web.hod.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
at org.springframework.web.hod.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.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.mango.filter.BaseFilter.doFilter(BaseFilter.java:34)
at org.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.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.t.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.t.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.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.tfilter.ContentBufferingFilter.bufferAndPostProcess(ContentBufferingFilter.java:169)
at org.tfilter.ContentBufferingFilter.doFilter(ContentBufferingFilter.java:126)
at org.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:120)
at fig.ConfigurableSiteMeshFilter.doFilter(ConfigurableSiteMeshFilter.java:163)
at org.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.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.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.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.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.tor.CoyoteAdapter.service(CoyoteAdapter.java:407)
at http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
at AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
at at.util.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1813)
at urrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at urrent.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 ex 很容易看出是由于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）后两种⽅法显然不太实⽤
3）采⽤c3p0提供的⽅案
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
{
//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
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
{
// 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 = ThrowableHolder();
status = ((UnifiedConnectionTester) connectionTester).activeCheckConnection( testConn, testQuery, throwableHolder );
}
else if (connectionTester instanceof QueryConnectionTester)
status = ((QueryConnectionTester) connectionTester).activeCheckConnection( testConn, testQuery );
else
{
// println("[c3p0] WARNING: testQuery '" + testQuery +
// "' ignored. Please set a ConnectionTester that implements " +
// "hange.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 " +
"hange.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;
// 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)
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:
//
case ConnectionTester.CONNECTION_IS_INVALID:
Exception throwMe;
if (rootCause == null)
throwMe = new SQLException("Connection is invalid");
else
throwMe = SQLException("Connection is invalid", rootCause);
throw throwMe;
default:
throw new Error("Bad Connection Tester (" +
connectionTester + ") " +
"returned invalid status (" + status + ").");
}
}
以上所述是⼩编给⼤家介绍的C3P0连接池+MySQL的配置及wait_timeout问题的解决⽅法，希望对⼤家有所帮助，如果⼤家有任何疑问请给我留⾔，⼩编会及时回复⼤家的。在此也⾮常感谢⼤家对⽹站的⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。