Spring Boot Multi-data Source Configuration and No Operation Allowed After Connected Closed Connection Anomaly Solution

Recently, when the project went online, it encountered a weird bug.

Let's start with my project configuration: Spring Booot + Spring MVC + Spring Data JPA + two MySql s

That is, my project has multiple data sources.

Pre-development is no problem, everything works well.

But when the project goes online for testing, often the next day the tester comes to report that the project is dead.

After checking the log, the following exception was found:

2018-01-27 08:09:15,361 - Servlet.service() for servlet [dispatcherServlet] in context with path [/shop] threw exception [Request processing failed;
 nested exception is org.springframework.transaction.CannotCreateTransactionException: 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.] with root cause

java.net.SocketException: Disconnected Pipeline (Write failed)......

Attention should be paid to the key point of eliminating anomalies: No operations allowed after connection closed.

From this place we know that mysql's link has been closed. Accessing a closed link is certainly an exception.

Reason:

This exception occurs because after MySQL 5.0, a processing has been done for very long DB connections, that is, if a DB connection passes 8 hours without any operation (Mysql server default "wait_timeout" is 8 hours), Mysql automatically closes the connection. This is the problem. If the connection pool is idle for more than 8 hours, MySQL disconnects it, and the connection pool itself does not know that the connection has failed. If a client requests a connection, the connection pool will provide the failed connection to the Client, which will cause the above exception.
Therefore, when configuring data source, it is necessary to configure the corresponding connection pool parameters to check the validity of the connection and regularly clean up the invalid connection.

So what's the solution?

Add the following connection pool configuration under the configuration of two data sources of application.properties:

#The following is the configuration of the connection pool parameters
spring.datasource.primary.max-idle=10
spring.datasource.primary.max-wait=10000
spring.datasource.primary.min-idle=5
spring.datasource.primary.initial-size=5
spring.datasource.primary.validation-query=SELECT 1
spring.datasource.primary.test-on-borrow=false
spring.datasource.primary.test-while-idle=true
spring.datasource.primary.time-between-eviction-runs-millis=18800

#The following is the configuration of the connection pool parameters
spring.datasource.secondary.max-idle=10
spring.datasource.secondary.max-wait=10000
spring.datasource.secondary.min-idle=5
spring.datasource.secondary.initial-size=5
spring.datasource.secondary.validation-query=SELECT 1
spring.datasource.secondary.test-on-borrow=false
spring.datasource.secondary.test-while-idle=true
spring.datasource.secondary.time-between-eviction-runs-millis=18800

As I said before, I configure it twice because I am two data sources. Of course, if you have only one data source, configure one.

In fact, the reason for this problem is that I neglected the configuration of connection pools and some common connection pools. A little knowledge is added here. The connection pool that spring boot will use preferentially by default is tomcat connection pool, provided that tomcat connection pool is available.