mysql数据库 timeout_mysql数据库的timeout设置

这几天上线了一个小网站，基本上是晚上测试没问题然后挂到网上。可是第二天就无法正常登录，并报出大量异常。

重启服务器后又可以正常运行，但是第二天问题依旧。

type Exception report

message

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

exception

org.springframework.transaction.TransactionSystemException: Could not roll back Hibernate transaction; nested exception is org.hibernate.TransactionException: JDBC rollback failed

org.springframework.orm.hibernate3.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:677)

org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:753)

org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:730)

org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:332)

org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)

org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)

org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

$Proxy0.findAllUser(Unknown Source)

com.hust.action.user.LoginAction.execute(LoginAction.java:118)

sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

java.lang.reflect.Method.invoke(Method.java:601)

com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:453)

com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:292)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:255)

org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)

com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)

org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)

com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:236)

com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:236)

com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:90)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:243)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)

com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:192)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)

com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)

org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)

org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:511)

org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)

org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)

com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)

org.apache.struts2.dispatcher.ActionContextCleanUp.doFilter(ActionContextCleanUp.java:102)

上面异常中有一行关键代码：

Could not roll back Hibernate transaction; nested exception is org.hibernate.TransactionException: JDBC rollback failed

经过网上查阅，发现是可能是mysql数据库的timeout问题，打开MySQL命令行控制端。

输入show global variables like 'wait_timeout' ；

显示默认timeout=28800，即8小时。也就是是经过8小时数据库无操作后会自动释放连接，我的MySQL版本为5.5，因此

只需要在MySQL安装目录下找到my.ini文件，在[mysqld]后面添加上以下两行代码：

wait_timeout=20

interactive_timeout=20

保存后， win+R打开运行，输入net stop mysql；然后输入net start mysql重启MySQL服务即可。此时我服务器开启后登录成功，退出后

等了一分钟，再次登录；错误重现。因此，对于此问题，可以将时间进行修改，服务器运行一段时间后再维护。

查看mysql5的手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。我们使用的Windows系统，假设我们要将其设为21天，

我们只要修改mysql5的配置文件“my.ini”中的[mysqld]后面添加上：

wait_timeout=1814400

interactive_timeout=1814400，重启服务即可

参考文章：