Aurora MySQL中的即时查询超时（使用jOOQ、MariaDB驱动程序）

Question

当我使用jOOQ和MariaDB驱动程序连接到Aurora MySQL数据库时，我的Java应用程序运行良好。简而言之SQL我的查询如下所示，请注意时间戳：

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();

但是，当我以更大的时间戳间隔运行同一个查询时，它会立即失败，并出现超时错误（AFAIK no actual timeout have a opportunity），例如，该查询将立即失败（再次注意时间戳）：

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();

上述查询在直接通过MySQL控制台连接时成功，并且我的系统中还有一些使用相同堆栈的其他长时间运行的查询，这些堆栈成功运行，没有超时投诉。问题只出现在我的Java应用程序中，其中一些（但不是全部！）查询的时间间隔很大——比如一个月（但不是间隔几天）。我的堆栈包括：

• 打开JDK 8

我的日志中的堆栈跟踪（附在下面）到处显示“连接超时”和“读取超时”。知道为什么查询中较长的间隔会导致瞬时超时吗？如果需要，我会提供更多详细信息，只需在评论中询问。

更新：我将我的驱动程序更改为使用标准MySQL Java驱动程序，查询现在可以工作了！我将保留这个问题，因为我仍然想了解为什么会发生这种情况，MariaDB驱动程序有什么问题，以及是否有可能找到解决方法（因为我更喜欢使用MariaDB驱动程序）

org.jooq.exception.DataAccessException: SQL [<query>]; (conn=349133) Communications link failure with primary host <host>. 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:?]
<redacted>
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 <host>. 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 <host>. Connection timed out
on HostAddress{host='<host>', 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

Answer 1

Aurora配置的默认套接字超时为10秒：请参阅中的文档https://mariadb.com/kb/en/library/about-mariadb-connector-j/:

默认值：0（标准配置）或10000ms（使用“aurora”故障切换配置）。

将该值更改为0表示没有超时。

这可以使用连接字符串来完成，例如jdbc： maryadb： aurora：//myHost/db？ ocketTimeout=0