Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Debugging Communication Link Failure exceptions in Connector/J

Have you seen error messages similar to the following:

Communications link failure – Last packet sent to the server was X ms ago.

Judging from the forums, many people have had problems with this.  Here’s a brief overview of the causes, and potential solutions.

Generally speaking, this error suggests that the network connection has been closed. There can be several root causes:

  • Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol doesn’t ping).
  • The MySQL Server may be closing idle connections which exceed the wait_timeout or interactive_timeout threshold

There’s a couple of useful diagnostic details which can be useful.  For starters, when a recent (5.1.13) version of Connector/J is used, you should see additional details around both the last packet sent and received.  Older versions may simply indicate the last time a packet was sent to the server, which is frequently zero ms ago.  That’s not terribly useful, and it may be that you just sent a packet, but haven’t received a packet from the server for 12 hours.  Knowing how long it’s been since Connector/J last received a packet from the server is useful information, so if you are not seeing this in your exception message, update your driver.

The second useful diagnostic detail shows up when Connector/J notices that the time a packet was last sent/received exceeds the wait_timeout or interactive_timeout threshold.  It will attempt to notify you of this in the exception message.

The following can be helpful in avoiding such problems, but ultimately network connections can be volatile:

  • Ensure connections are valid when checked out of connection pool (use query which starts with “/* ping */” *exactly* to execute lightweight ping instead of full query)
  • Minimize duration a Connection object is left idle while other application logic is executed
  • Explicitly validate Connection before using after being left idle for extended period of time
  • Ensure wait_timeout and interactive_timeout are set sufficiently high
  • Ensure tcpKeepalive is enabled
  • Ensure that any configurable firewall or router timeout setting accounts for maximum expected idle connection time.
  • Make sure that you are not setting socketTimeout, or that it is set to a sufficiently high value to avoid socket timeouts.

I’ve seen exception messages which indicate Connections being used after sitting idle for hours – sometimes days.  If you do this, make sure that you are explicitly testing the connection before using it after lengthy idle periods.  Network connections fail, and applications need to be prepared to handle that.  But expecting connections to survive extended periods where left idle and work magically when used again hours later is just asking for trouble.

Single Post Navigation

4 thoughts on “Debugging Communication Link Failure exceptions in Connector/J

  1. Swakesh on said:

    Hello,

    We are using Hibernate to Connect to My SQL Data base.
    Our Application is deployed on Tomcat.

    After 5 hrs using the Application we are reeving the below Error.

    Any Suggestion would be highly appreciated

    org.hibernate.exception.JDBCConnectionException: Communications link failure

    The last packet successfully received from the server was 1,847,704 milliseconds ago. The last packet sent successfully to the server was 1,847,720 milliseconds ago.
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy30.executeQuery(Unknown Source)
    at org.hibernate.persister.entity.AbstractEntityPersister.getDatabaseSnapshot(AbstractEntityPersister.java:1472)
    at org.hibernate.engine.internal.StatefulPersistenceContext.getDatabaseSnapshot(StatefulPersistenceContext.java:313)
    at org.hibernate.engine.internal.ForeignKeys.isTransient(ForeignKeys.java:217)
    at org.hibernate.engine.internal.ForeignKeys$Nullifier.isNullifiable(ForeignKeys.java:165)
    at org.hibernate.engine.internal.ForeignKeys$Nullifier.nullifyTransientReferences(ForeignKeys.java:94)
    at org.hibernate.engine.internal.ForeignKeys$Nullifier.nullifyTransientReferences(ForeignKeys.java:72)
    at org.hibernate.action.internal.AbstractEntityInsertAction.nullifyTransientReferencesIfNotAlready(AbstractEntityInsertAction.java:128)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:69)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:203)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:183)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:167)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:320)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:287)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:193)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:204)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:189)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:753)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:745)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:741)
    at com.tetrus.prea.server.aop.AuditActionInterceptor.invoke(AuditActionInterceptor.java:105)
    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
    at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
    at com.tetrus.prea.server.ApplicationServiceImpl$$EnhancerByGuice$$f80e7fea.getSearchResult()
    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 com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
    at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
    at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:964)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:304)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

    The last packet successfully received from the server was 1,847,704 milliseconds ago. The last packet sent successfully to the server was 1,847,720 milliseconds ago.
    at sun.reflect.GeneratedConstructorAccessor71.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3352)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1971)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    … 62 more
    Caused by: java.net.SocketException: Connection reset by peer: socket write error
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3333)
    … 72 more

  2. Todd Farmer on said:

    Hi Swakesh,

    The exception indicates that the connection was left idle for just over 30 minutes before the attempted operation, which failed because the network connection was closed. You can look for several root causes:

    1. Is socketTimeout set at all? If set to 30m or less, this would explain the behavior
    2. Do you receive other similar exceptions citing 30m or more in idle time? If so, maybe you have network infrastructure with corresponding timeouts
    3. If you have connection pooling enabled, have you configured maintenance operations properly so that connections in the pool (not checked out) are periodically validated? You will want to make sure that idle connections are validated more frequently than every 30m, based on this exception.
    4. If you have connection pooling enabled, you might consider validating the connection on check-out from the pool – if your library supports it. This will limit the opportunity for a connection to die between validation and application use (although you can never be 100% guaranteed that a validated connection will still work when the application actually goes to use it).

    I hope that helps! If you are using MySQL 5.6, you might also consider checking to see how idle your connections are.

  3. Swakesh on said:

    Hi Todd,

    Thank you very much for your Time and reply.

    I tried to set the idle connection time out to 300s.

    If still its not fixed, i will try other options mentioned in comment.

    Thanks,
    Swakesh

  4. Todd Farmer on said:

    Hi Swakesh,

    Glad to help – if that doesn’t solve the problem, check whether your connection pool library allows for validation before checkout. While that adds latency when obtaining a Connection from the pool (the application thread waits while the Connection is validated), it assures the highest probability that the Connection will be valid when the application starts to use it.

Leave a Reply

Your email address will not be published. Required fields are marked *


1 × = nine

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>