How to reconnect database if the connection closed in spring jpa?

Question

I am using spring-boot, spring-jpa, mysql in my web application.When my application is running for some hours, I always got below exceptions:

2016-07-30 21:27:12.434 ERROR 13553 --- [http-nio-8090-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : No operations allowed after connection closed.
2016-07-30 21:27:12.434  WARN 13553 --- [http-nio-8090-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08003
2016-07-30 21:27:12.434 ERROR 13553 --- [http-nio-8090-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : No operations allowed after connection closed.
2016-07-30 21:27:12.438 ERROR 13553 --- [http-nio-8090-exec-8] [.[.[.[.c.c.Go2NurseJerseyConfiguration] : Servlet.service() for servlet [com.cooltoo.config.Go2NurseJerseyConfiguration] in context with path [] threw exception [org.springframework.dao.DataAccessResourceFailureException: could not prepare statement; nested exception is org.hibernate.exception.JDBCConnectionException: could not prepare statement] with root cause

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3119) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3570) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4110) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) ~[mysql-connector-java-5.1.25.jar!/:na]
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322) ~[mysql-connector-java-5.1.25.jar!/:na]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]

I have checked that the database is running well. I have to restart my spring-boot application when that happened. How can I check what the problem is? Why the database connection got closed? If that happened, whether I can re-connect the database? Below is my application.properties:

spring.datasource.url=jdbc:mysql://192.168.99.100:3306/test?characterEncoding=utf8
spring.datasource.username=admin
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-active=150

Answer 1

This seems like a common error with MySQL.

1) Add this to your application.properties and see how it goes:

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

testOnBorrow is detailed in the spring doc and this other stackoverflow question. I'm however unable to find a reference on validationQuery in Spring's doc, but it seems to do the trick.

2) Or, you may use testWhileIdle as suggested here http://christoph-burmeister.eu/?p=2849

He suggests adding this to your application.properties:

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

This solution is also mentionned in the other stackoverflow question, it was just not the accepted answer, but seems to be the solution for some.

3) In this case, they also added timeBetweenEvictionRunsMillis:

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1
spring.datasource.timeBetweenEvictionRunsMillis = 3600000

EDIT: Another stackoverflow question that covers this (with a very complete answer)