Basicdatasource连接超时问题（使用mysql） [英] Basicdatasource connection time out problem (using mysql)

问题描述

我在我的应用程序中使用BasicDatasource。此应用程序正在处理大量原始数据。有时1次查询可能需要15分钟以上。 （使用mysql作为db）

I am using BasicDatasource in my application. This application is processing huge amount of raw data. Sometimes 1 query can take more than 15 minutes. (using mysql as db)

这是我的问题，我从池中获取连接，然后对其执行多个查询。但是当我使用相同的连接超过15分钟时，我得到以下错误。在mysql服务器中，max_wait设置为180小时，因此保持连接活动并且没有防火墙规则设置为终止超过一定时间的活动连接不应该是一个问题。

Here is my question, I acquire a connection from pool, then execute several queries on it. But when I use the same connection more than 15 minutes, I get the error below. In the mysql server max_wait is set to 180 hours so it shouldn t be a problem to keep the connection alive and no firewall rule set to kill connections that are alive more than a certain amount of time.

你觉得我在这里想念的是什么？

What am I missing here do you think ?

The last packet successfully received from the server was 928,374 milliseconds ago.  The last packet sent successfully to the server was 928,374 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        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:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
        at com.adsclick.logs.cron.adsclickv261.global.ProcessBase.executeUpdate(ProcessBase.java:766)

推荐答案

你可以尝试的事情：

1. setMaxWait（-1）。这告诉它无限期地等待连接。

1. setMaxWait(-1) on the BasicDataSource. That tells it to wait indefinitely for a connection.

检查MySQL服务器上的 wait_timeout 是设置为默认值8h。

Check that the wait_timeout on your MySQL server is set to the default 8h.

在JDBC URL上设置？autoReconnect = true

Set ?autoReconnect=true on your JDBC URL

setTestOnBorrow（true）。这样可以防止它发布过时的连接，但会增加你的应用程序的开销（尽管如果你已经有了这么长的单个查询，你可能甚至都不会注意到那个部分）。

setTestOnBorrow(true) on the BasicDataSource. This will prevent it from handing out stale connections but will add overhead to your app (though if you've already got such long single queries you probably won't even notice that part).

一般来说，我发现继续重用连接是一个坏主意。对我来说，拥有游泳池的关键是我不必那样做。

In general, I find it a bad idea to keep re-using a connection. For me, the point of having a pool is that I don't have to do that.

您的查询是否是交易性的？是否有一些非常长的查询锁定主表？

Are your queries transactional? Is some really long query locking up a major table?

这篇关于Basicdatasource连接超时问题（使用mysql）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！