mysql连接自动断掉问题

项目开发中遇到一个问题，当天weblogic启动连接上后没问题，第二天mysql连接就自动断掉了，错误如下所示

========================================================================================================

com.mysql.jdbc.CommunicationsException: 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:1903)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2349)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2860)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
 at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1120)
 at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:675)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)
 at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:80)
 at com.sitechasia.fw.persist.jdbc.impl.EboJdbcDataAccessor.load(EboJdbcDataAccessor.java:408)
 at com.sitechasia.fw.persist.impl.EboPersistenceProvider.load(EboPersistenceProvider.java:89)
 at com.sitechasia.tm.tmcinema.core.TMCinemaManager.getTMCinemaDomainInfoByDomainName(TMCinemaManager.java:431)
 at com.sitechasia.tm.tmcinema.client.TMCinemaDelegate.getTMCinemaDomainInfoByDomainName(TMCinemaDelegate.java:356)
 at com.sitechasia.tm.tmreg.core.TMRegManager.getCinemaDomainInfoByURL(TMRegManager.java:615)
 at com.sitechasia.tm.tmreg.core.TMRegManager.putCinemaIIDIntoContext(TMRegManager.java:495)
 at com.sitechasia.tm.tmreg.client.TMRegDelegate.putCinemaIIDIntoContext(TMRegDelegate.java:601)
 at com.sitechasia.tm.tmreg.util.EboPortalCorpInfoOfCinema.putCorpIDIntoContext(EboPortalCorpInfoOfCinema.java:27)
 at com.sitechasia.portal.core.EboPortalServletBase.setCorpID(EboPortalServletBase.java:364)
 at com.sitechasia.portal.core.EboPortalServletBase.service(EboPortalServletBase.java:112)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
 at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:971)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:402)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:305)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6350)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:317)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:118)
 at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3635)
 at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2585)
 at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
 at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)

** END NESTED EXCEPTION **

 

Last packet sent to the server was 14 ms ago.
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2561)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2860)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
 at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1120)
 at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:675)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027)
 at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:80)
 at com.sitechasia.fw.persist.jdbc.impl.EboJdbcDataAccessor.load(EboJdbcDataAccessor.java:408)
 at com.sitechasia.fw.persist.impl.EboPersistenceProvider.load(EboPersistenceProvider.java:89)
 at com.sitechasia.tm.tmcinema.core.TMCinemaManager.getTMCinemaDomainInfoByDomainName(TMCinemaManager.java:431)
 at com.sitechasia.tm.tmcinema.client.TMCinemaDelegate.getTMCinemaDomainInfoByDomainName(TMCinemaDelegate.java:356)
 at com.sitechasia.tm.tmreg.core.TMRegManager.getCinemaDomainInfoByURL(TMRegManager.java:615)
 at com.sitechasia.tm.tmreg.core.TMRegManager.putCinemaIIDIntoContext(TMRegManager.java:495)
 at com.sitechasia.tm.tmreg.client.TMRegDelegate.putCinemaIIDIntoContext(TMRegDelegate.java:601)
 at com.sitechasia.tm.tmreg.util.EboPortalCorpInfoOfCinema.putCorpIDIntoContext(EboPortalCorpInfoOfCinema.java:27)
 at com.sitechasia.portal.core.EboPortalServletBase.setCorpID(EboPortalServletBase.java:364)
 at com.sitechasia.portal.core.EboPortalServletBase.service(EboPortalServletBase.java:112)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
 at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:971)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:402)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:305)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6350)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:317)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:118)
 at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3635)
 at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2585)
 at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
 at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
com.sitechasia.fw.exception.EboRuntimeException: 检索操作失败。
 at com.sitechasia.fw.persist.jdbc.impl.EboJdbcDataAccessor.load(EboJdbcDataAccessor.java:438)
 at com.sitechasia.fw.persist.impl.EboPersistenceProvider.load(EboPersistenceProvider.java:89)
 at com.sitechasia.tm.tmcinema.core.TMCinemaManager.getTMCinemaDomainInfoByDomainName(TMCinemaManager.java:431)
 at com.sitechasia.tm.tmcinema.client.TMCinemaDelegate.getTMCinemaDomainInfoByDomainName(TMCinemaDelegate.java:356)
 at com.sitechasia.tm.tmreg.core.TMRegManager.getCinemaDomainInfoByURL(TMRegManager.java:615)
 at com.sitechasia.tm.tmreg.core.TMRegManager.putCinemaIIDIntoContext(TMRegManager.java:495)
 at com.sitechasia.tm.tmreg.client.TMRegDelegate.putCinemaIIDIntoContext(TMRegDelegate.java:601)
 at com.sitechasia.tm.tmreg.util.EboPortalCorpInfoOfCinema.putCorpIDIntoContext(EboPortalCorpInfoOfCinema.java:27)
 at com.sitechasia.portal.core.EboPortalServletBase.setCorpID(EboPortalServletBase.java:364)
 at com.sitechasia.portal.core.EboPortalServletBase.service(EboPortalServletBase.java:112)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
 at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:971)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:402)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:305)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6350)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:317)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:118)
 at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3635)
 at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2585)
 at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
 at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
com.sitechasia.fw.exception.EboUnrecoverableSystemException: No corpid in your system，maybe use com.sitechasia.fw.factory.Ebofactory.CreateEbiContext()
 at com.sitechasia.fw.directory.client.EboDirectoryHelper.getCorpID(EboDirectoryHelper.java:130)
 at com.sitechasia.pub.enterpriseregmgmt.util.CorpCacheConfigzHelper.getCacheEnable(CorpCacheConfigzHelper.java:34)
 at com.sitechasia.portal.manager.EboPresentationManager.isCorpPortalCacheEnable(EboPresentationManager.java:1264)
 at com.sitechasia.portal.manager.EboPresentationManager.displayComponent(EboPresentationManager.java:153)
 at com.sitechasia.portal.manager.EboPresentationManager.displayPage(EboPresentationManager.java:1186)
 at com.sitechasia.portal.manager.EboPresentationManager.displayPage(EboPresentationManager.java:1129)
 at com.sitechasia.portal.core.EboPortalServletBase.service(EboPortalServletBase.java:190)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
 at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:971)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:402)
 at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:305)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6350)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:317)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:118)
 at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3635)
 at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2585)
 at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
 at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

========================================================================================================

上网查了一下，发现了好多的解决方法：

一、怀疑是驱动的问题：

1、下载个最新的支持mysql 5.0 的jdbc驱动再测试安装。
2.实在不行就用mysql 3.x或4.x安装，以确定是否跟mysql或jdbc驱动有关系
连接方式先用mysql front测试，行了再到安装向导设置。

二、怀疑是URL写法有问题

注意一下URL的书写：
url="jdbc:mysql://host:port/dbname?user=xxx&password=xxx&useUnicode=true&characterEncoding=GB2312";

三、如果使用了hibernate的话：

你可以试一试加入以下三行：
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">0</property>
<property name="hibernate.c3p0.timeout">0</property>
好像最重要的是 idle_test_period
我上次也遇见过相同的问题，试来试去就解决了，也不确定到底方案是什么。

test_idle_period 其实只要小过 MySQL 的 wait_timeout 就可以了。
增加这句话可以解决您的问题.
如果还不能解决您的问题,那你应该查找代码的原因,用的连接是否是同一个连接,或者你打开的连接和关闭的连接不是同一个.(因为我自己就犯过这样的错误)

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

使用hibernate：
<property name="connection.autoReconnect">true</property>　　<!--这个是最主要的-->
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>
加入以上property，可解决此问题，如果未使用hibernate等持久化管理框架，可在mysql的url中加入autoReconnect=true，这样就可以解决。
mysql的站点上说是JDBC3.1.0-alpha及以前版本会出现此问题，推荐下载新的JDBC驱动，无所谓了，只要能解决问题，我也懒的换，至少现在运行了1天还未出现以前的错误，应该是算解决了，呵呵，等待更长时间的测试。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

引发这个异常的可能是hibernate用尽了数据库的连接造成的，请先确定你的session都是关闭了的，如果问题还是存在，可以试试如下方法：
设置mysql等待时间：
如果mysql已经启动：
SET GLOBAL interactive_timeout=604800;
SET GLOBAL wait_timeout=604800;
如果没有启动，可以在启动时加上参数--wait_timeout=604800。
还不行的话建议升级一下mysql试试，呵呵，再不行我就没办法了

 

四、高手回复

连接池配置：

 hibernate.c3p0.max_size=100
  
 hibernate.c3p0.min_size=2
  
 hibernate.c3p0.timeout=1000

 hibernate.c3p0.max_statements=100

 hibernate.c3p0.idle_test_period=300

 hibernate.c3p0.acquire_increment=5

 hibernate.c3p0.validate=false< property="" />

网上找到http://swforum.sun.com/jive/thread.jspa?threadID=51022&messageID=186798

其中一段：

原文：

Well, one of the key things that should be apparent to mysql users, but may not be,
be sure wait_timeout in your my.cnf file is set to a value greater than the idle_timeout set in your connection pool. (they both are seconds in units)

otherwise the server will kill the connection from behind, with the pool thinking it should still be alive, you'll get one of these here SocketException fangles.

意思是：mysql配置中的wait_timeout值一定要大于等于连接池种的idel_timeout值。否则mysql会在wait_timeout的时间后关闭连接，然而连接池还认为该连接可用，这样就会产生SocketException。

根据这种说法我把连接池的timeout值有1000改成50(暂定认为mysql中的wait_timeout值是50),看能否解决问题。

五、经过对以上这些回答进行总结和试验：

这个问题的原因我找到了，因为在mysql中有个设置“Wait timeout”参数用来等待活动连接的超时时间默认是“28800”刚好是8小时，可以把它设成一个比较大的值即可，碰到此问题的兄弟可以试试；

六、如果还不行，而且你用的是weblogic的话在“Connection Pools”中把“Test Reserved Connections”和“Test Created Connections”选中，在“Test Table Name”中填写一个数据库中的表名，然后在每次调用连接池中的连接时都会进行一个测试是否可用的操作；