mysql HTTP Status 500 – Internal Server Error

本文详细解析了MySQL连接超时错误的根源,即wait_timeout参数的限制,并提供了修改配置文件或直接通过SQL命令调整该参数的方法,以避免因长时间闲置导致的连接断开问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

HTTP Status 500 – Internal Server Error


Type Exception Report

Message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 140,755,209 milliseconds ago. The last packet sent successfully to the server was 140,755,210 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.

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 140,755,209 milliseconds ago.  The last packet sent successfully to the server was 140,755,210 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.
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

Root Cause

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 140,755,209 milliseconds ago.  The last packet sent successfully to the server was 140,755,210 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.
	org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:289)
	org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
	cn.gson.oasys.services.user.UserLongRecordService$$EnhancerBySpringCGLIB$$a20cc8c7.save(<generated>)
	cn.gson.oasys.controller.login.LoginsController.loginCheck(LoginsController.java:109)
	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.doInvoke(InvocableHandlerMethod.java:205)
	org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

Root Cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 140,755,209 milliseconds ago.  The last packet sent successfully to the server was 140,755,210 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:425)
	com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
	com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3746)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
	com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4815)
	sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
	org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
	org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
	com.sun.proxy.$Proxy69.setAutoCommit(Unknown Source)
	org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:267)
	org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
	cn.gson.oasys.services.user.UserLongRecordService$$EnhancerBySpringCGLIB$$a20cc8c7.save(<generated>)
	cn.gson.oasys.controller.login.LoginsController.loginCheck(LoginsController.java:109)
	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.doInvoke(InvocableHandlerMethod.java:205)
	org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

Root Cause

java.net.SocketException: 断开的管道 (Write failed)
	java.net.SocketOutputStream.socketWrite0(Native Method)
	java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
	java.net.SocketOutputStream.write(SocketOutputStream.java:155)
	sun.security.ssl.OutputRecord.writeBuffer(OutputRecord.java:431)
	sun.security.ssl.OutputRecord.write(OutputRecord.java:417)
	sun.security.ssl.SSLSocketImpl.writeRecordInternal(SSLSocketImpl.java:886)
	sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:857)
	sun.security.ssl.AppOutputStream.write(AppOutputStream.java:123)
	java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3728)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
	com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4815)
	sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	java.lang.reflect.Method.invoke(Method.java:498)
	org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
	org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
	org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
	com.sun.proxy.$Proxy69.setAutoCommit(Unknown Source)
	org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:267)
	org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447)
	org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
	org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
	cn.gson.oasys.services.user.UserLongRecordService$$EnhancerBySpringCGLIB$$a20cc8c7.save(<generated>)
	cn.gson.oasys.controller.login.LoginsController.loginCheck(LoginsController.java:109)
	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.doInvoke(InvocableHandlerMethod.java:205)
	org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
解决方案:https://blog.youkuaiyun.com/cynhafa/article/details/19676865

http://zeusami.iteye.com/blog/1112827

 

MySQL第二天早上第一次连接超时报错,解决方法com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure Last packet sent to the server was 0 ms ago



最近碰到一个mysql5数据库的问题。就是一个标准的servlet/tomcat网络应用,后台使用mysql数据库。问题是待机一晚上后,第二天早上第一次登录总是失败。察看日志发现如下错误:

“com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.”


经过一番调研,发现很多人都碰到过类似问题,但网上令人满意的回答并不多。mysql网站上的提问也很多,但并没有正确答案;百度知道上倒是有一个近似正确的回答。现将本人的解决办法总结一下:

上述问题是由mysql5数据库的配置引起的。mysql5将其连接的等待时间(wait_timeout)缺省为8小时。在其客户程序中可以这样来查看其值:

mysql﹥

mysql﹥ show global variables like 'wait_timeout';

+---------------+---------+

| Variable_name | Value |

+---------------+---------+

| wait_timeout | 28800 |

+---------------+---------+

1 row in set (0.00 sec)


28800 seconds,也就是8小时。

如果在wait_timeout秒期间内,数据库连接(java.sql.Connection)一直处于等待状态,mysql5就将该连接关闭。这时,你的Java应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时,就碰到上述错误。这解释了为什么我的程序第二天不能登录 的问题。

你可能会想到在tomcat的数据源配置中有没有办法解决?的确,在jdbc连接url的配置中,你可以附上“autoReconnect=true”,但这仅对mysql5以前的版本起作用。增加“validation query”似乎也无济于事。

本人觉得最简单的办法,就是对症下药:既然问题是由mysql5的全局变量wait_timeout的缺省值太小引起的,我们将其改大就好了。

查看mysql5的手册,发现对wait_timeout的最大值分别是24天/365天(windows/linux)。以windows为 例,假设我们要将其设为21天,我们只要修改mysql5的配置文件“my.ini”(mysql5 installation dir),增加一行:wait_timeout=1814400

需要重新启动mysql5。

linux系统配置文件:/etc/my.cnf

测试显示问题解决了。


也可以直接设置

mysql修改wait_timeout

  mysql mysql> show global variables like 'wait_timeout';

  其默认值为8小时

  mysql的一个connection空闲时间超过8小时,mysql会自动断开该连接。

  1.修改配置

  vi /etc/my.cnf

  [mysqld] wait_timeout=10

  # /etc/init.d/mysql restart

  2.直接用sql命令行修改 mysql> set global wait_timeout=604800;

除了wait_timeout,还有一个'interactive_timeout'
-----------------------------------------------

客户端操作:

show global variables like "wait_timeout";

set global wait_timeout=604800;

SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

set global interactive_timeout=604800;



转载于:https://www.cnblogs.com/xyyou/p/10875578.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值