一夜之间发生MySql错误 - “发送到服务器的最后一个数据包是0毫秒前” [英] MySql error occurs overnight - "Last packet sent to the server was 0 ms ago"

问题描述

我似乎在一夜之间在我的应用程序中遇到异常。

I seem to be experiencing an exception in my application overnight.

我的环境是一个使用Tomcat的Java Web应用程序，用Java6编写并在MySQL上运行，Hibernate3用于连接数据库（使用MySQL连接器5.0.3 - mysql-connector-java-5.0.3-bin.jar）

My environment is a Java web application using Tomcat, written in Java6 and running on MySQL, with Hibernate3 used to connect to the database (using the MySQL connector 5.0.3 - mysql-connector-java-5.0.3-bin.jar)

有一个预定的作业，它在一夜之间运行（使用quartz作为调度程序），并在凌晨3点运行时，在尝试访问数据库时它会出现以下异常（注意，我已经用xxx重命名堆栈跟踪的位，因为它是我工作的公司的内部代码）：

There is a scheduled job which runs overnight (using quartz as the scheduler) and when run at 3am, it gives the following exception when trying to access the database (note, I've renamed bits of the stack trace with "xxx" as it is internal code for the company I work for):

03:00:00 ERROR bernate.transaction.JDBCTransaction: JDBC begin failed
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:1913)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
    at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5215)
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:63)
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
    at xxx.HibernateSession.beginThreadTransaction(HibernateSession.java:75)
    at xxx.HibernateSession.beginTransaction(HibernateSession.java:141)
    at xxx.TestCaseManager.runAllTestsInBackground(TestCaseManager.java:228)
    at xxx.scheduler.Job.execute(Job.java:42)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2515)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
    at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5215)
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:63)
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
    at xxx.HibernateSession.beginThreadTransaction(HibernateSession.java:75)
    at xxx.HibernateSession.beginTransaction(HibernateSession.java:141)
    at xxx.TestCaseManager.runAllTestsInBackground(TestCaseManager.java:228)
    at xxx.scheduler.Job.execute(Job.java:42)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)
03:00:00 ERROR bernate.transaction.JDBCTransaction: JDBC begin failed
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

早上登录应用程序时（这也需要数据库访问）它提供了类似的异常，但在等待大约5分钟后再次尝试我们可以进入应用程序。

In the morning, when logging into the application (which also requires database access) it gives a similar exception, but after waiting about 5 minutes and trying again we can get into the app.

我已经检查了数据库我可以对它试图连接的表执行简单的SELECT。任何帮助，将不胜感激。

I've checked the database and I can perform simple SELECTs on tables it's trying to connect to. Any help would be appreciated.

推荐答案

您的DBCP连接池超时使用以下配置...

Your DBCP Connection pool is timing out use the configuration below...

简单的解决方案是在将DBCP提供给调用者之前进行DBCP验证连接。
将以下属性添加到BasicDataSource配置中：

Simple solution is to make DBCP verify connections before giving them to the caller. Add the following properties to your BasicDataSource configuration:

<property name="testOnBorrow" value="true"/>
<property name="validationQuery" value="SELECT 1"/>

有关所有选项的列表，请参阅此处： http://commons.apache.org/dbcp/configuration.html

For a list of all options see here: http://commons.apache.org/dbcp/configuration.html

编辑：
如果对发生的情况稍作修正，请参阅下面的MJB评论。

For slight correction in what is happening see MJB's comment below.

这篇关于一夜之间发生MySql错误 - “发送到服务器的最后一个数据包是0毫秒前”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！