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

Question

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 timeout but I try to prevent it with the following:

• spring.datasource.url=jdbc:postgresql://mydb?autoReconnect=true
• I use @transactional for certain statements. But in general I only use the JPA-Repository from 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.

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

Do I need 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.

Answer 1

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

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

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.

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

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.

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.