CODEWITHSUNDEEP
javamysqljpaeclipselink
rootkit
rootkit

Reputation: 2165

MySQL communications link failure on specific SQL with EclipseLink

EclipseLink 2.5.1, Tomcat 7, MySQL JDBC driver 5.1.31. I have a specific SELECT statement that consistently produces comm link failure when executed by my webapp (running exactly the same SELECT in mysql client works just fine, and results come back in about 800ms). I have fiddled around with timeout settings on MySQL, no difference.

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)

The MySQL server is local, here is 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"/>

and here is 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>

Upvotes: 1

Views: 2987

Answers (1)

Chris
Chris

Reputation: 21145

The problem seems to be that the socketTimeout=900 value is not enough time for a query expected to take 800ms in the test environment. This value causes the socket itself to timeout before the query can return. Changing this value to give the query the time it needs to complete when the system and network are under load will resolve the issue.

Upvotes: 1

Related Questions