Spring Cloud + Spring MVC + Spring Data JPA + MySql + Postgresql
Among them, the project is equipped with multiple data sources, and there is no problem in the early development test, but when the project is tested on the server, the next morning there is an abnormal database connection. After checking the log, the following exception is found:
Note the exception information: No operations allowed after connection closed.
That is to say, the connection acquired by jpa is closed, and the operation of a closed link causes 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.
Solution
Add the following connection pool configuration (red font section) under the configuration of the two data sources of application.yml:
eureka: client: service-url: defaultZone: http://localhost:8761/eureka server: port: 9013 spring: application: name: api datasource: druid: #Database Connection 1 mysql: name: mysql type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/datacube?useUnicode=true&characterEncoding=utf8&useSSL=false username: root password: rootrot #Database Connection 2 greenplum: name: greenplum type: com.alibaba.druid.pool.DruidDataSource driver-class-name: org.postgresql.Driver url: jdbc:postgresql://localhost:5432/datacube?useUnicode=true&characterEncoding=utf8&useSSL=false username: root password: rootroot # Here are additional settings for connection pooling that apply to all of the above data sources # Initialization size, minimum, maximum initialSize: 5 minIdle: 10 maxActive: 1000 #Configuration to get the connection waiting timeout time maxWait: 60000 #How often is the configuration interval detected to detect idle connections that need to be closed in milliseconds? timeBetweenEvictionRunsMillis: 60000 #Configure the minimum lifetime of a connection in the pool in milliseconds minEvictableIdleTimeMillis: 300000 #Verify that the connection is valid. This parameter must be set to a non-empty string, and the following three items must be set to true to take effect. validationQuery: SELECT 1 #Indicates whether the connection is checked by the idle connection reclaimer (if any). If the detection fails, the connection will be removed from the pool. testWhileIdle: true #Indicates whether the connection is checked before it is removed from the pool, and if the check fails, removes the connection from the pool and tries to take another one out. testOnBorrow: true #Indicate whether to inspect before returning to the pool testOnReturn: false #Open the PSCache and specify the size of the PSCache on each connection poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 #Configure filters that are intercepted by monitoring statistics, and sql can not be counted after removing them.'wall'is used for firewall filters: stat,wall,log4j #Open mergeSql function by connecting Properties attribute; slow SQL record connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=1000;druid.stat.logSlowSql=true #Merge monitoring data from multiple Druid Data Sources useGlobalDataSourceStat: true # WebStatFilter: # exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" # stat-view-servlet: # login-username: admin # login-password: admin jpa: database-platform: org.hibernate.dialect.MySQL5InnoDBDialect show-sql: true hibernate: #dialect: org.hibernate.dialect.MySQL5Dialect ddl-auto: none naming: strategy: org.hibernate.cfg.ImprovedNamingStrategy
In this project, Ali's druid connection pool is used, which is simple to configure except for data base Address, driver class, username and password are all defaults. At the beginning, because the project updates online more frequently, there are not too many problems, and later changed the library. As a result, previous links fail, requests go from good to bad, and code and other project configurations are followed. One property, testOnBorrow, is set to false (default setting is false), while testOnBorrow = false does not detect the availability of connections in the pool.
So if the connection in the connection pool is closed by the database, the application may get these unavailable connections through the connection pool getConnection, and if these connections are not recycled by other threads, they will not be abolished or re-created, occupying the number of connection pools, the project itself as a server, the database links are closed, and the customers will not be reclaimed by the connection pool. There will be a lot of timeout when the client calls the server. The client sets the timeout time. However, when the server disconnects actively, the server will inevitably appear close_wait. Because the default maximum number of threads of Tomcat is 200, it will soon hang up. Although most of the sources, no problem data sources, links will die when they are sent, so it is said to increase the default threads of Tomcat (server.tomcat.max-threads=3000). The links to other data sources will not die in a short time.
The default configuration does not apply to all scenarios, so it needs to be used in conjunction with the scenario.
Because testOnborrow =true consumes a lot of performance, in order to ensure the stability of the server, it can be avoided with other configurations, with testWhileIdle=true (but default is false) and timeBetweenEviction Runs Millis to avoid this problem, so setting testOnborrow =false can improve efficiency.