【杂谈\Java\数据库】SpringMVC 连接MySQL超时问题解决方案

原创 2016年08月30日 00:20:56

      这几天一直在烦恼这个问题!关于SpringMVC&Spring&MySQL项目上传到服务器上时,超过一定时间以后就没法再访问的问题而且还有一点就是,我在排查故障的时候发现,Tomcat服务器和MySQL服务压根就没关闭过!下面是报错的内容:

PS:很长!!可以直接翻到最后去看,这里主要是看看大伙儿有木有通过下面的报错信息得到和我一样的答案,有的话证明!你很厉害!我找这个错误可花了3天的时间。。。

HTTP Status 500 - Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed:

type Exception report

message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed:

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    cn.com.dnyy.web.SystemContextFilter.doFilter(SystemContextFilter.java:32)
    org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
root cause

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: 
    org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:334)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    com.sun.proxy.$Proxy23.list(Unknown Source)
    cn.com.dnyy.web.MainController.index(MainController.java:99)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:100)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:604)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:565)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    cn.com.dnyy.web.SystemContextFilter.doFilter(SystemContextFilter.java:32)
    org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
root cause

org.hibernate.TransactionException: JDBC begin failed: 
    org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:92)
    org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
    org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:334)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    com.sun.proxy.$Proxy23.list(Unknown Source)
    cn.com.dnyy.web.MainController.index(MainController.java:99)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:100)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:604)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:565)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    cn.com.dnyy.web.SystemContextFilter.doFilter(SystemContextFilter.java:32)
    org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.Util.getInstance(Util.java:386)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1290)
    com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1282)
    com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5236)
    org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
    org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
    org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:87)
    org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
    org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:334)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    com.sun.proxy.$Proxy23.list(Unknown Source)
    cn.com.dnyy.web.MainController.index(MainController.java:99)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:100)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:604)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:565)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    cn.com.dnyy.web.SystemContextFilter.doFilter(SystemContextFilter.java:32)
    org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 81,397,238 milliseconds ago.  The last packet sent successfully to the server was 81,397,238 milliseconds ago. is longer than the server configured value of 'wait_timeout'. 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.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3871)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
    com.mysql.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:5428)
    com.mysql.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:5421)
    org.apache.commons.dbcp.DelegatingConnection.setReadOnly(DelegatingConnection.java:337)
    org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setReadOnly(PoolingDataSource.java:327)
    sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74)
    com.sun.proxy.$Proxy34.setReadOnly(Unknown Source)
    org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:155)
    org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:508)
    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:334)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    com.sun.proxy.$Proxy23.list(Unknown Source)
    cn.com.dnyy.web.MainController.index(MainController.java:99)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:100)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:604)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:565)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    cn.com.dnyy.web.SystemContextFilter.doFilter(SystemContextFilter.java:32)
    org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
root cause

java.net.SocketException: 断开的管道
    java.net.SocketOutputStream.socketWrite0(Native Method)
    java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3852)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
    com.mysql.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:5428)
    com.mysql.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:5421)
    org.apache.commons.dbcp.DelegatingConnection.setReadOnly(DelegatingConnection.java:337)
    org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setReadOnly(PoolingDataSource.java:327)
    sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74)
    com.sun.proxy.$Proxy34.setReadOnly(Unknown Source)
    org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:155)
    org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:508)
    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:334)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    com.sun.proxy.$Proxy23.list(Unknown Source)
    cn.com.dnyy.web.MainController.index(MainController.java:99)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:100)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:604)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:565)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    cn.com.dnyy.web.SystemContextFilter.doFilter(SystemContextFilter.java:32)
    org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.70 logs.

      说了吧,很长,放这么长主要还是方便大家了解这个问题的所在!我们仔细看一下,或者你可以复制下面的话来搜索一下我们的问题所在!

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 81,397,238 milliseconds ago.

      这句话是什么意思呢?MySQL给我们异常提示,说我们已经有81,397,238秒没有访问这个服务器了,意思就是这个SSH链接管道已经被断开了,这点我们可以在最后一次root cause信息中可以验证java.net.SocketException: 断开的管道 ,既然问题找到了,我们就要想一下是什么造成的,可能是防火墙或者是系统把MySQL连接池里头的这个连接管道进程给Kill掉了。

      我们翻阅了下MySQL的文档,找到了这个东西:wait_timeout,非常明确的写出了这行字:

The number of seconds the server waits for activity on a noninteractive connection before closing it.
(系统关闭处于非交互状态下的服务器链接活动的等待时长)

      打开我们能看到MySQL在不同系统环境下的超时时长,Windows下为2147483秒(),而其它系统则是31536000秒,然而一般情况下不会这么长的,这只是最大值,这是MySQL的特性,我们没法修改,因此,我们需要做的就是在某个恰当的时间来触发一次交互,使其继续保持连接状态!

      那我们就另辟蹊径找一下我们的服务器连接部分!终于哈,在Apache文档里头找到了叫DBCP(BasicDataSource Configuration Parameters)的东西,在这个文档里头找到了一些有趣的东西,例如:

validationQuery:The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. If not specified, connections will be validation by calling the isValid() method.
(验证查询:SQL查询将会被用作连接MySQL连接池的验证,这将会被返回给调用者。如果被设置了,那么这个查询必须是一个至少返回一行的SQL SELECT语句;如果没有被设置,验证则通过调用isValid()方法来进行。)

testOnBorrow:The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
(借阅测试[也不懂翻译得对不对]:指示是否会在连接池被borrow之前验证对象。如果对象未通过验证,则它会从连接池中移除,并且我们会尝试去borrow另一个。)

testWhileIdle:The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool.
(闲置测试:指示是否通过限制对象移除机制来验证对象[如果有的话]。如果对象未通过验证,则它会被移除连接池。)

      艾玛,翻译完了,真心不容易啊,为了确保我们能够彻底解决这个问题,还是在bean.xml数据库连接属性里面修改如下:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="testOnBorrow" value="true"/>
</bean>

      如此!我们就解决了这个问题!

      艰辛!但是结果很愉快~哈哈!

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Spring数据库数据源JDBC连接池连接MySQL的超时问题

Spring数据库数据源JDBC连接池连接MySQL的超时问题,Spring 数据库数据源DBCP配置说明...

mysql数据库连接超过8小时失效的解决方案(springboot)

最近由于业务需要,开发了一个定时程序,每天执行一次,从mysql库里取出数据处理。这是前提。 结果今天早上查看错误日志,发现了如下的日志:2017-03-12 03:00:02.539 ERROR ...

java线程(超时等待+简单数据库连接池)

超时等待模式在一般的等待/通知机制上添加超时控制,使得方法执行时间过长也不会一直造成阻塞,而是在一段时间后返回。 通常java连接数据库时会将其执行过程交由一个线程进行处理,使得在一些I/O操作或需...
  • only06
  • only06
  • 2016年10月30日 18:05
  • 1442

postgresql 查看数据库连接数

查看所有连接的用户:select * from pg_stat_activity;查看连接总数:select count(*) from pg_stat_activity;...

JDBC通过SSH Tunnel连接MySQL数据库

时间:2013-04-26 18:04来源:Internet 作者:Internet 点击:599次 有时候我们无法直接访问某台数据库,因为没有授权或者ip限制,但是可以通过登陆...

java jdbc使用SSH隧道连接mysql数据库demo

package com.yws.echo_socket; import com.jcraft.jsch.JSch; import com.jcraft.jsch.Session; import ...
  • earbao
  • earbao
  • 2015年12月08日 12:16
  • 8458

java中MySQL中文乱码问题解决方案

  • 2014年04月14日 16:54
  • 28KB
  • 下载

java连接mysql的方法及问题解决方案

java连接mysql数据库简单操作

备份Mysql数据库时,报:Couldn't execute 'show fields from的一种问题解决方案

因为数据库的表出现问题了,通过命令:     myisamchk -r -q *.MYI     执行了修复,可是通过mysqldump数据的时候,老是报Couldn't execute 'sho...

关于powerdesigner链接Mysql数据库和出现的问题解决方案

第一步 需要安装MySQL Connetion/ODBC,因为PowerDesigner连接MySQL,需要中间的纽带,MySQL Connetion/ODBC 相当于中间商一样,既可以和MySQL...
  • judyfun
  • judyfun
  • 2017年01月14日 14:56
  • 1551
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【杂谈\Java\数据库】SpringMVC 连接MySQL超时问题解决方案
举报原因:
原因补充:

(最多只允许输入30个字)