基本数据源连接超时问题（使用mysql）
我在我的应用程序中使用 BasicDatasource。该应用程序正在处理大量原始数据。有时 1 个查询可能需要超过 15 分钟。 （使用mysql作为数据库）
这是我的问题，我从池中获取一个连接，然后对其执行多个查询。但是当我使用同一连接超过 15 分钟时，我收到以下错误。在 mysql 服务器中 max_wait 设置为 180 小时，因此保持连接活动应该不成问题，并且没有防火墙规则设置来终止活动时间超过一定时间的连接。
你认为我在这里缺少什么？
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)
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)
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)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以尝试以下操作：
setMaxWait(-1)。这告诉它无限期地等待连接。检查 MySQL 服务器上的
wait_timeout是否设置为默认的 8 小时。在您的 JDBC URL 上设置
?autoReconnect=truesetTestOnBorrow(true)< /code> 在 BasicDataSource 上。这将防止它分发过时的连接，但会增加应用程序的开销（尽管如果您已经有这么长的单个查询，您可能甚至不会注意到该部分）。一般来说，我发现继续重复使用连接是一个坏主意。对我来说，拥有一个游泳池的意义在于我不必这样做。
您的查询是事务性的吗？是否有一些非常长的查询锁定了一个主表？
Things you can try:
setMaxWait(-1)on the BasicDataSource. That tells it to wait indefinitely for a connection.Check that the
wait_timeouton your MySQL server is set to the default 8h.Set
?autoReconnect=trueon your JDBC URLsetTestOnBorrow(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?