mysql会频繁的出现以下异常，以为是mysql的8小时超时问题，根据网上说的，已经把mysal的time_out时间设到很大了。但是貌似没有效果。
wait_timeout=2147483
interactive_timeout=2147483
请问哪位知道以下问题的原因吗
以下数据源的配置
<!-- Connection Info -->
<!-- Connection Pooling Info -->
<property name="maxActive" value="${dbcp.maxActive}" />
<property name="maxIdle" value="${dbcp.maxIdle}" />
<property name="defaultAutoCommit" value="false" />
<property name="timeBetweenEvictionRunsMillis" value="3600000"/>
<property name="minEvictableIdleTimeMillis" value="3600000"/>
</bean>
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
jdbc.username=admin
jdbc.password=12345678
dbcp.maxActive=200
dbcp.maxIdle=200
问题log
url: /passport/login
DEBUG 2015-11-11 09:08:40,426 http-bio-80-exec-1 org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'websiteServiceMgr'
DEBUG 2015-11-11 09:08:40,426 http-bio-80-exec-1 org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'transactionManager'
DEBUG 2015-11-11 09:08:40,426 http-bio-80-exec-1 org.springframework.jdbc.datasource.DataSourceTransactionManager - Participating in existing transaction
DEBUG 2015-11-11 09:08:40,426 http-bio-80-exec-1 org.mybatis.spring.SqlSessionUtils - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e77a197] from current transaction
DEBUG 2015-11-11 09:08:40,427 http-bio-80-exec-1 java.sql.PreparedStatement - ==> Executing: SELECT ID, TITLE, META_KEYWORDS, META_DESCRIPTION, TEMPLATE, LAYOUT, THEME, LOGO, AREA_HEADER, AREA_SIDEBAR, AREA_BANNER, AREA_FOOTER, ICP FROM web_website
DEBUG 2015-11-11 09:08:40,427 http-bio-80-exec-1 java.sql.PreparedStatement - ==> Parameters:
DEBUG 2015-11-11 09:08:40,428 http-bio-80-exec-1 org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Unable to translate SQLException with Error code '0', will now try the fallback translator
DEBUG 2015-11-11 09:08:40,428 http-bio-80-exec-1 org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e77a197]
DEBUG 2015-11-11 09:08:40,428 http-bio-80-exec-1 org.springframework.jdbc.datasource.DataSourceTransactionManager - Participating transaction failed - marking existing transaction as rollback-only
DEBUG 2015-11-11 09:08:40,428 http-bio-80-exec-1 org.springframework.jdbc.datasource.DataSourceTransactionManager - Setting JDBC transaction [org.apache.commons.dbcp.PoolableConnection@3b984fc8] rollback-only
十一月 11, 2015 9:08:40 上午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [web-app] in context with path [] threw exception
org.springframework.dao.RecoverableDataAccessException:
The last packet successfully received from the server was 71,311,449 milliseconds ago. The last packet sent successfully to the server was 69,875,829 milliseconds ago.
The last packet successfully received from the server was 71,311,449 milliseconds ago. The last packet sent successfully to the server was 69,875,829 milliseconds ago.
; SQL []; Communications link failure
The last packet successfully received from the server was 71,311,449 milliseconds ago. The last packet sent successfully to the server was 69,875,829 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 71,311,449 milliseconds ago. The last packet sent successfully to the server was 69,875,829 milliseconds ago.
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
at com.sun.proxy.$Proxy13.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:189)
at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:85)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:65)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
at com.sun.proxy.$Proxy21.findAll(Unknown Source)
at com.dnion.doms.dwz.business.website.impl.WebsiteServiceMgrImpl.getWebsite(WebsiteServiceMgrImpl.java:94)
at sun.reflect.GeneratedMethodAccessor255.invoke(Unknown Source)
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:319)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy23.getWebsite(Unknown Source)
at com.dnion.doms.dwz.adapter.web.filter.WebsiteContextFilter.doFilter(WebsiteContextFilter.java:74)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.dnion.doms.dwz.adapter.web.filter.XssFilter.doFilter(XssFilter.java:37)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 71,311,449 milliseconds ago. The last packet sent successfully to the server was 69,875,829 milliseconds ago.
at sun.reflect.GeneratedConstructorAccessor92.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3352)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1971)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:117)
at sun.reflect.GeneratedMethodAccessor63.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:45)
at com.sun.proxy.$Proxy179.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:216)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:95)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
at sun.reflect.GeneratedMethodAccessor61.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:31)
at com.dnion.doms.dwz.dal.mybatis.plugin.OffsetLimitInterceptor.intercept(OffsetLimitInterceptor.java:61)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:42)
at com.sun.proxy.$Proxy178.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:69)
at sun.reflect.GeneratedMethodAccessor80.invoke(Unknown Source)
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:338)
... 42 more
Caused by: java.net.SocketException: 断开的管道
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:3333)
... 72 more
另外
从问题log来看，有一点非常不解，我的操作是登录，为什么会是加入一个存在的事务。【Participating in existing transaction】，对比了正常的log，是开一个新的事务。【Creating new transaction with name】
确认是你的分页插件问题OffsetLimitInterceptor 中 有连接没有释放导致连接数沾满了
我同样遇到这个问题, 只要不使用总数统计这个问题就不会出现pageBounds.isContainsTotalCount() 如果是false 就没事儿
查询代码是SQLHelp的getCount 方法有问题, 这个貌似只支持那种可以直接关闭物理连接的数据源或者不用数据源的情况
集成阿里的druid 就会出现这个问题,由于我的项目中有其他人在直接连接所以这里我没法修改,自己写了总数统计不用ContainsTotalCount 就可以了