postgresql 为什么 spring-boot 和 postgres 连接在一定时间后会中断？

why does spring-boot and postgres connection breaks after certain time?

提问by SWiggels

I run Spring-Boot with gradle using the tomcat-connection-pool. All the standard spring-boot-tools. I run several soap-webservices on that webserver. It all works fine when testing the load of the server. But after a doing nothing for ~7.5hours this exception occurs. Sure its a timeoutbut I try to prevent it with the following:

我使用 tomcat-connection-pool 运行带有 gradle 的 Spring-Boot。所有标准的 spring-boot-tools。我在那个网络服务器上运行了几个soap-webservices。测试服务器负载时一切正常。但是在大约 7.5 小时无所事事后，会发生此异常。当然是一个，timeout但我尝试通过以下方式阻止它：

• spring.datasource.url=jdbc:postgresql://mydb?autoReconnect=true
• I use @transactionalfor certain statements. But in general I only use the JPA-Repositoryfrom spring-boot.
• The connections are managed by the tomcat-connection-pool so there is no idle-connection-problem.
• When I restart the application-server everything runs fine again.

• spring.datasource.url=jdbc:postgresql://mydb?autoReconnect=true
• 我@transactional用于某些陈述。但总的来说，我只使用JPA-Repositoryfrom spring-boot。
• 连接由 tomcat-connection-pool 管理，因此不存在空闲连接问题。
• 当我重新启动应用程序服务器时，一切都再次正常运行。

My Database-server runs PostgreSQL 9.4.1 on x86_64-unknown-linux-gnuand there is NO firewall between the database and the app-server.

我的数据库服务器运行PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu，数据库和应用程序服务器之间没有防火墙。

Do I need tcp_keep alives?

我需要tcp_keep alives吗？

Why does my connection breaks after a certain time and is no more recoverable?

为什么我的连接在一段时间后断开并且无法恢复？

My App-properties:

我的应用程序属性：

#
# [ Database Configuration Section ]
#
spring.jpa.database=POSTGRESQL
spring.jpa.show-sql=false
hibernate.format_sql=true
hibernate.hbm2ddl.auto=validate
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy

spring.datasource.platform=postgres
spring.database.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://*****:5434/******
spring.datasource.username=*****
spring.datasource.password=*****

logging.file=*******.log
logging.level.=WARNING

---

---

2015-09-29 11:58:50.598  INFO 10498 --- [nio-9092-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet 'dispatcherServlet'
2015-09-29 11:58:50.598  INFO 10498 --- [nio-9092-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
2015-09-29 11:58:50.674  INFO 10498 --- [nio-9092-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 76 ms

---

---

2015-09-29 19:23:03.777  WARN 10498 --- [ool-3-thread-16] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08006
2015-09-29 19:23:03.779 ERROR 10498 --- [ool-3-thread-16] o.h.engine.jdbc.spi.SqlExceptionHelper   : An I/O error occurred while sending to the backend.
2015-09-29 19:23:03.785  INFO 10498 --- [ool-3-thread-16] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2015-09-29 19:23:03.836 ERROR 10498 --- [ool-3-thread-16] o.s.orm.jpa.JpaTransactionManager        : Commit exception overridden by rollback exception
java.net.SocketException: Connection timed out
    at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_25]
    at java.net.SocketInputStream.read(SocketInputStream.java:150) ~[na:1.8.0_25]
    at java.net.SocketInputStream.read(SocketInputStream.java:121) ~[na:1.8.0_25]
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:282) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1718) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    ... 61 common frames omitted
Wrapped by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:201) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:411) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
    ... 57 common frames omitted
Wrapped by: org.hibernate.exception.JDBCConnectionException: could not execute statement

EDIT:

编辑：

I've added this and testing it currently:

我已经添加了这个并正在测试它：

  spring.datasource.testOnBorrow=true
  spring.datasource.validationQuery=SELECT 1

And removed autoReconnect.

并删除autoReconnect。

采纳答案by SWiggels

As mentioned by @Yuki Yoshida the answer was that simple.

正如@Yuki Yoshida 所提到的，答案就是这么简单。

I added spring.datasource.validation-query= select 1spring.datasource.test-on-borrow=trueto my configuration and it works.

我添加 spring.datasource.validation-query= select 1spring.datasource.test-on-borrow=true到我的配置中，它可以工作。

Test-on-borrow:

借用测试：

testOnBorrow: Default: true

The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.

testOnBorrow：默认值：true

在从池中借用对象之前是否将对其进行验证的指示。如果对象无法验证，它将从池中删除，我们将尝试借用另一个。

And validationQuery: Default: for most db languages select 1else see here

和validationQuery：默认值：对于大多数数据库语言select 1，请参见此处

validationQuery

The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. If not specified, connections will be validation by calling the isValid() method.

验证查询

在将连接返回给调用者之前，将用于验证来自该池的连接的 SQL 查询。如果指定，此查询必须是至少返回一行的 SQL SELECT 语句。如果未指定，连接将通过调用 isValid() 方法进行验证。

I actually tried this already but I did not explicitly set test-on-borrow to true because of its default. Looks like you have to set it explicitly.

我实际上已经尝试过这个，但是由于它的默认值，我没有明确地将借用测试设置为真。看起来您必须明确设置它。