title: Instantaneous query timeout in Aurora MySQL (using jOOQ, MariaDB driver)
body: <p>My Java application works fine when I execute a query using jOOQ and the MariaDB driver for connecting to an Aurora MySQL database. In plain SQL my query looks like this, and notice the timestamps:</p>
  
  <pre><code>select
    A.id, B.x, B.y,
    coalesce(A.modified, A.created) as modified
  from
    A join B on A.b_id = B.id
  where
    A.created between date_sub(now(), interval 1 day) and now()
    or A.modified between date_sub(now(), interval 1 day) and now();
  </code></pre>
  
  <p>But when I run the same query with a bigger timestamp interval, it fails <em>immediately</em> with a timeout error (AFAIK no actual timeout had a chance to happen), for example this query will fail at once (again, notice the timestamps):</p>
  
  <pre><code>select
    A.id, B.x, B.y,
    coalesce(A.modified, A.created) as modified
  from
    A join B on A.b_id = B.id
  where
    A.created between date_sub(now(), interval 1 month) and now()
    or A.modified between date_sub(now(), interval 1 month) and now();
  </code></pre>
  
  <p>The above query succeeds when connecting directly through a MySQL console, and I have some other long-running queries in my system using the same stack that run successfully, with no timeout complaints. The problem only occurs from my Java application, with <em>some</em> (but not all!) queries that have a big time interval - say, a month (but no with an interval of a couple of days). My stack includes:</p>
  
  <ul>
  <li>Open JDK 8</li>
  <li>jOOQ 3.11</li>
  <li>MariaDB Connector/J 2.2</li>
  <li>Aurora MySQL 5.6.10a</li>
  </ul>
  
  <p>The stack trace in my logs (attached below) shows "Connection timed out" and "Read timed out" all over the place. Any ideas why a longer interval in the query causes an instantaneous timeout? I'll provide more details if needed, just ask in the comments.</p>
  
  <p><strong>UPDATE:</strong> I changed my driver to use the standard MySQL Java driver, and the query now works! I'll leave this question open, because I'd still like to understand <em>why</em> this is happening, what's broken with the MariaDB driver, and if it's possible to find a workaround (because I'd prefer to use the MariaDB driver.)</p>
  
  <pre><code>org.jooq.exception.DataAccessException: SQL [&lt;query&gt;]; (conn=349133) Communications link failure with primary host &lt;host&gt;. Connection timed out
  at org.jooq_3.11.4.MARIADB.debug(Unknown Source) ~[?:?]
  at org.jooq.impl.Tools.translate(Tools.java:2384) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:811) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:393) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:380) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.fetchStream(AbstractResultQuery.java:351) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.fetchStreamInto(AbstractResultQuery.java:356) ~[jooq-3.11.4.jar:?]
  &lt;redacted&gt;
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_222]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_222]
  at java.lang.Thread.run(Thread.java:748) [?:1.8.0_222]
  Caused by: java.sql.SQLException: (conn=349133) Communications link failure with primary host &lt;host&gt;. Connection timed out
  at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3483) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:268) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350) ~[jooq-3.11.4.jar:?]
  ... 17 more
  Caused by: java.sql.SQLException: Communications link failure with primary host &lt;host&gt;. Connection timed out
  on HostAddress{host='&lt;host&gt;', port=3306},master=true. Driver has reconnect connection
  at org.mariadb.jdbc.internal.failover.AbstractMastersListener.throwFailoverMessage(AbstractMastersListener.java:517) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.handleFailOver(FailoverProxy.java:354) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:292) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:263) ~[Mariadb-java-client-2.2.x.jar:?]
  at com.sun.proxy.$Proxy48.executeQuery(Unknown Source) ~[?:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3483) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:268) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350) ~[jooq-3.11.4.jar:?]
  ... 17 more
  Caused by: java.sql.SQLException: Read timed out
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:1786) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1344) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1323) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:252) ~[Mariadb-java-client-2.2.x.jar:?]
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_222]
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_222]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_222]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_222]
  at org.mariadb.jdbc.internal.failover.impl.MastersSlavesListener.invoke(MastersSlavesListener.java:217) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:270) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:263) ~[Mariadb-java-client-2.2.x.jar:?]
  at com.sun.proxy.$Proxy48.executeQuery(Unknown Source) ~[?:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3483) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:268) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350) ~[jooq-3.11.4.jar:?]
  ... 17 more
  Caused by: java.net.SocketTimeoutException: Read timed out
  at java.net.SocketInputStream.socketRead0(Native Method) ~[?:1.8.0_222]
  at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[?:1.8.0_222]
  at java.net.SocketInputStream.read(SocketInputStream.java:171) ~[?:1.8.0_222]
  at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[?:1.8.0_222]
  at java.io.BufferedInputStream.fill(BufferedInputStream.java:246) ~[?:1.8.0_222]
  at java.io.BufferedInputStream.read1(BufferedInputStream.java:286) ~[?:1.8.0_222]
  at java.io.BufferedInputStream.read(BufferedInputStream.java:345) ~[?:1.8.0_222]
  at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:237) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:207) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1342) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1323) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:252) ~[Mariadb-java-client-2.2.x.jar:?]
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_222]
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_222]
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_222]
  at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_222]
  at org.mariadb.jdbc.internal.failover.impl.MastersSlavesListener.invoke(MastersSlavesListener.java:217) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:270) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:263) ~[Mariadb-java-client-2.2.x.jar:?]
  at com.sun.proxy.$Proxy48.executeQuery(Unknown Source) ~[?:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150) ~[Mariadb-java-client-2.2.x.jar:?]
  at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3483) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:268) ~[jooq-3.11.4.jar:?]
  at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350) ~[jooq-3.11.4.jar:?]
  ... 17 more
  </code></pre>