mysql连接超时问题

2010-06-30 15:39:50,474 INFO [com.sumavision.tjwh.service.ArticleServiceImpl] - **********定时调度,拷贝数据库内容到本地开始************
2010-06-30 15:39:52,618 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2010-06-30 15:39:52,739 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2010-06-30 15:39:52,882 WARN [org.springframework.jdbc.support.SQLErrorCodesFactory] - Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:

** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1907)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2298)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2797)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1567)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1659)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3112)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3041)
 at com.mysql.jdbc.Statement.execute(Statement.java:686)
 at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
 at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429)
 at com.sumavision.tjwh.dao.ArticleDaoImpl.deleteAll(ArticleDaoImpl.java:182)
 at com.sumavision.tjwh.service.ArticleServiceImpl.schoduleJobCopyData(ArticleServiceImpl.java:107)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
 at $Proxy48.schoduleJobCopyData(Unknown Source)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:276)
 at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:260)
 at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
 at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
 at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:531)

** END NESTED EXCEPTION **

 

Last packet sent to the server was 3 ms ago.
在天津中间件演示项目中，现场出现该问题，解决办法:在MySql数据库的安装目录下my.ini文件中，加入

wait_timeout=31536000

以下资料未验证过：

1、一般的解决方法大多是在数据库连接字符串中增加“autoReconnect=true ”选项。但是这只对mysql4以前的版本有效。在最新的mysql中是无效的。其实要解决这个问题也有一个简单的方法，就是修改mysql的启动参数。 缺省情况下mysql的timeout时间是28800秒，正好是8小时，增加一个0就可以了。

同理也可以在" my.ini"文件中增加此参数。

mysqld-nt --default-table-type=innodb --interactive_timeout=288000

2、从根源入手，设置mysql的wait_timeout为31536000（一年）。

mysql> show variables;

| wait_timeout                    | 28800

| interactive_timeout             | 28800      

" my.ini"文件中修改此参数

[mysqld]
wait_timeout=31536000
interactive_timeout=31536000

重启服务，OK！