英文:
How to solve "Socket read timed out" when using hikari connection pool
问题
我正在使用Play Framework(版本2.8.0),Java(版本1.8)以及Oracle数据库(版本12C)开发应用程序。
每天只有零次或一次对数据库的访问,但我遇到了以下错误。
java.sql.SQLRecoverableException: IO错误:套接字读取超时
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:138)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:447)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketTimeoutException: 套接字读取超时
at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174)
at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82)
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139)
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101)
at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80)
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98)
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908)
... 6 common frames omitted
db {
default {
driver=oracle.jdbc.OracleDriver
url="jdbc:oracle:thin:@XXX.XXX.XXX.XX:XXXX/XXXXXXX"
username="XXXXXXXXX"
password="XXXXXXXXX"
hikaricp {
dataSource {
cachePrepStmts = true
prepStmtCacheSize = 250
prepStmtCacheSqlLimit = 2048
}
}
}
}
看起来是由于数据库连接处于非活动状态引起的。我应该如何解决这个问题?
如果需要其他信息,请告诉我。
英文:
I am developing an application using play framework (version 2.8.0), java(version 1.8) with an oracle database(version 12C).
There is only zero or one hit to the database in a day, I am getting below error.
java.sql.SQLRecoverableException: IO Error: Socket read timed out
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:919)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005)
at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:138)
at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:447)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketTimeoutException: Socket read timed out
at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:174)
at oracle.net.ns.NIOHeader.readHeaderBuffer(NIOHeader.java:82)
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:139)
at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:101)
at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:80)
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:98)
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908)
... 6 common frames omitted
db {
default {
driver=oracle.jdbc.OracleDriver
url="jdbc:oracle:thin:@XXX.XXX.XXX.XX:XXXX/XXXXXXX"
username="XXXXXXXXX"
password="XXXXXXXXX"
hikaricp {
dataSource {
cachePrepStmts = true
prepStmtCacheSize = 250
prepStmtCacheSqlLimit = 2048
}
}
}
}
It seems it is causing due to inactive database connection, How can I solve this?
Please let me know if any other information is required?
答案1
得分: 2
你可以为JDBC启用TCP keepalive - 通过设置JVM指令或在连接字符串中添加"ENABLE=BROKEN"来实现。
但是:
- 通常情况下,思科/Juniper在连接不活动超过一个小时时会断开TCP连接。
- 而Linux内核在两小时后(tcp_keepalive_time)开始发送keepalive探测。因此,如果你决定打开TCP keepalive,你还需要以root身份更改内核可调参数为较低的值(10-15分钟)。
- 此外,HikariCP不应保持任何连接超过30分钟 - 默认情况下是这样的。
因此,如果您的防火墙、Linux内核和HikariCP都使用默认设置,那么您的系统中不应该出现此错误。
请参阅HikariCP 官方文档
> maxLifetime:
> 此属性控制池中连接的最大生命周期。正在使用的连接永远不会被弃用,只有在关闭连接时才会被移除。针对每个连接,会应用轻微的负面衰减,以避免在池中发生大规模连接消失。我们强烈建议设置此值,并且它应该比数据库或基础架构设定的连接时间限制短几秒钟。值为0表示没有最大生命周期(无限生命周期),当然还要受到idleTimeout设置的影响。允许的最小值为30000毫秒(30秒)。默认值:1800000(30分钟)
英文:
You can enable TCP keepalive for JDBC - either be setting JVM directive or by adding "ENABLE=BROKEN" into connection string.
But:
- Usually Cisco/Juniper cuts off TCP connection when it is inactive for more that on one hour.
- While Linux kernel starts sending keepalive probes after two hours(tcp_keepalive_time). So if you decide to turn tcp keepalive on, you will also need root to change this kernel tunable to lower value(10-15 minutes)
- Moreover HikariCP should not keep open any connection for longer than 30 minutes - by default.
So if your FW, Linux kernel and HikariCP all use default settings, then this error should not occur in your system.
See HikariCP official documentation
> maxLifetime:
> This property controls the maximum lifetime of a connection in the
> pool. An in-use connection will never be retired, only when it is
> closed will it then be removed. On a connection-by-connection basis,
> minor negative attenuation is applied to avoid mass-extinction in the
> pool. We strongly recommend setting this value, and it should be
> several seconds shorter than any database or infrastructure imposed
> connection time limit. A value of 0 indicates no maximum lifetime
> (infinite lifetime), subject of course to the idleTimeout setting. The
> minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30
> minutes)
答案2
得分: 0
我已在配置文件中添加了以下HikariCP的配置,并且它正常工作。
## 数据库连接池
play.db.pool = hikaricp
play.db.prototype.hikaricp.connectionTimeout = 120000
play.db.prototype.hikaricp.idleTimeout = 15000
play.db.prototype.hikaricp.leakDetectionThreshold = 120000
play.db.prototype.hikaricp.validationTimeout = 10000
play.db.prototype.hikaricp.maxLifetime = 120000
英文:
I have added the below configuration for hickaricp in configuration file and it is
working fine.
## Database Connection Pool
play.db.pool = hikaricp
play.db.prototype.hikaricp.connectionTimeout=120000
play.db.prototype.hikaricp.idleTimeout=15000
play.db.prototype.hikaricp.leakDetectionThreshold=120000
play.db.prototype.hikaricp.validationTimeout=10000
play.db.prototype.hikaricp.maxLifetime=120000
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论