Search code examples
sqlmuleamazon-redshiftcloudhub

Redshift query hangs on mule cloudhub

I am facing an issue where simple redshift query hangs. I am using mule and deployed my code to cloudhub. Believe it has got to do with resultset size. When the result set size is very low, probably less than 50 rows, it works fine. However, anything beyond that appears to hang and I occasionally (after about 2hours of request), see this error in the logs. I am using the connection url as:

jdbc:postgresql://xyz?tcpKeepAlive=true&useCursors=true&user=abc&password=123

And by the way, everything works just fine in my local mule runtime.

********************************************************************************
Message               : [Amazon](500150) Error setting/closing connection: Connection reset by peer. (java.sql.SQLException). Message payload is of type: ParameterMap
Code                  : MULE_ERROR-29999
--------------------------------------------------------------------------------
Exception stack is:
1. [Amazon](500150) Error setting/closing connection: Connection reset by peer. (com.amazon.support.exceptions.GeneralException)
  com.amazon.jdbc.communications.channels.MessagesSocketChannel:-1 (null)
2. [Amazon](500150) Error setting/closing connection: Connection reset by peer. (java.sql.SQLException)
  com.amazon.jdbc.communications.channels.MessagesSocketChannel:-1 (null)
3. [Amazon](500150) Error setting/closing connection: Connection reset by peer. (java.sql.SQLException). Message payload is of type: ParameterMap (org.mule.api.MessagingException)
  org.mule.module.db.internal.processor.AbstractDbMessageProcessor:93 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html)
--------------------------------------------------------------------------------
Root Exception stack trace:
com.amazon.support.exceptions.GeneralException: [Amazon](500150) Error setting/closing connection: Connection reset by peer.
    at com.amazon.jdbc.communications.channels.MessagesSocketChannel.readMessages(Unknown Source)
    at com.amazon.jdbc.communications.channels.AbstractMessagesSocketChannel.read(Unknown Source)
    + 2 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************
Solution

  • As Guy mentioned in the comments, the issue seems to be Redshift using a fixed MTU.

    See MuleSoft Knowledge Base article Mule application hangs on database select or insert when using Postgres on AWS Redshift for details.