How to automatically reconnect a DataSource connection in Spring?

Question

Problem: when my spring application is running, and meanwhile the database server is stopped/restarted, then then db connection is lost and never restored.

I tested as follows:

execute query: OK
service mysql stop
execute query: exception:

Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
service mysql start
execute query: still exception!

Question: how can I tell spring DataSource to automatically reconnect after connection has been lost?

This is my configuration:

spring.datasource.url=jdbc:mysql://localhost/tablename?useSSL=false
spring.datasource.username=root
spring.datasource.password=rootpw
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.tomcat.validation-query=SELECT 1
spring.datasource.tomcat.validation-query-timeout=5000
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.test-on-connect=true

score 1 · Answer 1 · answered Nov 14 '17 at 11:20

1

I'm using a connection pool like HikariCP to do a reconnect automatically.

see the example at Stackoverflow: How do I configure HikariCP in my Spring Boot app in my application.properties files?

answered Nov 14 '17 at 11:20

Lara

21
1

score 0 · Accepted Answer · answered Nov 14 '17 at 10:48

0

My bad, I had a misconfiguration. The config above is all that's needed for auto reconnect.

answered Nov 14 '17 at 10:48

membersound

81,582
193
585
1,120

kindly add full prop for `auto reconnect` – Sarz Apr 02 '18 at 12:43
@membersound. Uh no, it isn't it keeps the connection alive, but if a network outage happens it still does not reconnect. – dlamblin Mar 02 '21 at 19:57
@dlamblin so what is missing for auto reconnect? – membersound Mar 03 '21 at 07:35

How to automatically reconnect a DataSource connection in Spring?

2 Answers2

Linked