spring+mybatis 频繁出现数据库连接失败问题,mysql timeout时间设长也无效

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

3个回答

问题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:

Error querying database. Cause: 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.

The error may involve defaultParameterMap

The error occurred while setting parameters

Cause: 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.
; 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 就可以了

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐