文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >Aurora MySQL中的瞬时查询超时(使用jOOQ、MariaDB驱动程序)

问Aurora MySQL中的瞬时查询超时(使用jOOQ、MariaDB驱动程序)
EN

Stack Overflow用户
提问于 2019-09-23 12:29:15
回答 1查看 2.2K关注 0票数 3

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

代码语言：javascript
运行
复制
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没有实际超时的机会)，例如，该查询将一次失败(再次注意时间戳)：

代码语言：javascript
运行
复制
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应用程序中，有一些(但不是全部！)有很长时间间隔的查询--比如一个月(但没有间隔几天)。我的堆栈包括：

  • Open 8
  • jOOQ 3.11
  • MariaDB Connector/J2.2
  • Aurora MySQL 5.6.10a

F 211

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

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

代码语言：javascript
运行
复制
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
java
mysql
mariadb
jooq
amazon-aurora
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-09-25 09:00:56

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

默认值:0(标准配置)或10000 or (使用“极光”故障转移配置)。

将该值更改为0意味着没有超时。

这可以使用连接字符串来完成，比如jdbc:mariadb:aurora://myHost/db?socketTimeout=0。

票数 4
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/58062475

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档