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，重启服务即可

参考文章：

http://i5tt.iteye.com/blog/380324

http://zlj214.iteye.com/blog/1276576