java - 使用 EclipseLink 的特定 SQL 的 MySQL 通信链接失败

Question

EclipseLink 2.5.1、Tomcat 7、MySQL JDBC 驱动程序 5.1.31。我有一个特定的 SELECT 语句，当我的 webapp 执行时，它始终产生通信链接失败(在 mysql 客户端中运行完全相同的 SELECT 工作得很好，结果在大约 800 毫秒内返回)。我在 MySQL 上摆弄过超时设置，没有区别。

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 912 milliseconds ago.  The last packet sent successfully to the server was 901 milliseconds ago.
Error Code: 0
Call: SELECT ...

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 912 milliseconds ago.  The last packet sent successfully to the server was 901 milliseconds ago.
    at sun.reflect.GeneratedConstructorAccessor523.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3567)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3456)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3997)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)
    ... 35 more
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)

MySQL服务器是本地的，这里是JNDI:

<Resource name="jdbc/my-mysql"
          auth="Container"
          type="javax.sql.DataSource"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"
          maxActive="100"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          removeAbandonedTimeout="60"
          removeAbandoned="true"
          logAbandoned="true"
          minEvictableIdleTimeMillis="30000"
          username="my"
          password="my"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mydb?zeroDateTimeBehavior=convertToNull&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;socketTimeout=900&amp;autoReconnectForPools=true"/>

这是 persistence.xml:

<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="my-persistence-unit" transaction-type="RESOURCE_LOCAL">
        <validation-mode>NONE</validation-mode>
        <non-jta-data-source>java:comp/env/jdbc/my-mysql</non-jta-data-source>
        <properties>
            <property name="eclipselink.weaving" value="static"/>
            <property name="eclipselink.query-results-cache" value="true"/>
            <property name="eclipselink.connection-pool.initial" value="16" />
            <property name="eclipselink.connection-pool.min" value="64" />
            <property name="eclipselink.connection-pool.max" value="128" />
        </properties>
    </persistence-unit>
</persistence>

Answer 1

问题似乎是 socketTimeout=900 值对于在测试环境中预计需要 800 毫秒的查询来说时间不够。该值会导致套接字本身在查询返回之前超时。更改此值以在系统和网络处于负载下时为查询提供完成所需的时间将解决此问题。