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
@transactional
for certain statements. But in general I only use the JPA-Repository
from spring-boot.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
.
So instead of installing PostgreSQL as a separate application, we'll use Docker Compose to run Spring Boot and PostgreSQL. 2. Creating the Spring Boot Project Let’s go to the Spring Initializer and create our Spring Boot project. We’ll add the PostgreSQL Driver and Spring Data JPA modules.
Use Spring Data JPA to connect to a PostgreSQL database Add a dependency for PostgreSQL JDBC driver, which is required to allow Java applications to be able to talk with a PostgreSQL database server. Configure data source properties for the database connection information
To load a database that is not embedded, in Spring Boot 2 we need to add spring.datasource.initialization-mode=always . To avoid conflicts, we turn off automatic schema creation with spring.jpa.hibernate.ddl-auto=none .
As you have seen, Spring Boot greatly simplifies the programming, and you can choose to use Spring JDBC or Spring Data JPA. Watch the following video to see the coding in action: If playback doesn't begin shortly, try restarting your device.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With