Error analysis and positioning
A database cluster is used in the project. When using it, it is found that the project is running and the database has not been operated for a period of time. If the database is operated again after a period of time, the following error will appear on the console.
Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@de1855 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
When the project integrated the MySQL cluster, it used the dynamic data source tool developed by Baomidou.
<!--Dynamic data source, providing @DS annotation to facilitate the use of dynamic data sources-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>2.5.4</version>
</dependency>
Looking at the log, I probably know that it is a connection pool error, and the hikari database connection pool is used. I only knew Druid before but didn’t know Hikari. When integrating dynamic data source tools, I didn’t configure the relevant connection pool. The error content suggests that we use a smaller maxLifetime value, so the reason for this error is probably that the parameter maxLifetime of the connection pool is greater than the connection lifetime of the database. The connection in the connection pool has not expired, but the database has expired, and then operations after The connection is closed.
mysql connection timeout parameters
Mysql has two parameters related to connection timeout, one is interactive_timeout and the other is wait_timeout, the unit is s
Use the following statement to view
show variables like '%timeout%';
The default for both parameters is 28800s, which is 8 hours;
Interactive_timeout refers to the number of seconds mysql has to wait before closing an interactive connection; wait_timeout refers to the number of seconds mysql has to wait before closing a non-interactive connection. Connecting to the database through the mysql client is an interactive connection, and connecting to the database through jdbc is a non-interactive connection.
HikariCP connection timeout parameters
Druid and HikariCP are active database connection pools, which are said to be simpler and faster. But this article focuses on its connection timeout parameters.
Check the source code and official documentation to learn
CONNECTION_TIMEOUT is the maximum number of milliseconds to wait for a connection from the pool. The default is 30000ms, which is 30 seconds. At least 250 ms.
IDLE_TIMEOUT is the maximum time a connection is allowed to be idle in the pool. The default is 600000ms, which is 10 minutes. This value is at least 10 seconds, 0 means infinite. This value is only valid when minimumIdle is set.
MAX_LIFETIME is the maximum life cycle of the connection in the pool. The default is 1800000 ms, which is 30 minutes. This value is at least 30 seconds, 0 means unlimited.
These values are far less than mysql’s 8 hours
We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit
It is strongly recommended that we set this value to a value that is a few seconds shorter than the database connection timeout.
Timeout mechanism
If the Connection is not used for a long time, the Connection will be closed by Mysql (but not null). An exception will be thrown when calling the Connection at this time.
But logically speaking, mysql expires in 8 hours and the database connection pool expires in 30 minutes. This problem should not occur, and this problem occurs within tens of seconds.
Use the command to view current connections
SHOW PROCESSLIST;
It was found that there were a large number of surviving connections, about thirty or forty, which should be some connections left over from the previous program startup. MySQL did not close the connections. It was a problem with the connection pool.
That was still a hikari configuration problem, so I had to follow the prompts and try to change max-lifetime.
If you are not using a dynamic link library, you can use the following configuration.
spring:
datasource:
hikari:
max-lifetime: 300000
Since a dynamic link library is used, this hikari must be configured under its configuration.
spring:
datasource:
dynamic:
datasource:
hikari:
max-lifetime: 300000
Changing it to 800000 and sending a request after being idle for five minutes can stably trigger an error. When it is set to 30000, no error occurs again. At this time, the wait_timeout of the mysql database is still 8 hours.
Change wait_timeout to 300 seconds, which is the same as the above modification, five minutes. After the modification, a large number of connections disappeared. When I restarted the project, I found that there were four connections at startup. Their maximum time value does not exceed 300. If it exceeds, it will start counting from 0. This value is restricted by max-lifetime. As the number of subsequent requests increases, the number of connections increases, but it is ultimately fixed at 10 connections. These 10 connections are restricted by hikari’s maximum number of threads. Since the idle waiting time and number of idle threads are not set, there are probably 10 connections during the running of this program. When I exit the program, the time exceeds 300. The connection will disappear directly and will no longer start counting from 0.
One very confusing point here is wait_timeout and interactive_timeout.
MySQL has two levels of configuration, one is session and the other is global. The statement below to buy noodles defaults to session level:
show variables like '%timeout%';
If you want to see the global level of mysql, you need to add the global parameter:
show global variables like '%timeout%';
There are four parameters below these two levels:
- global level interactive_timeout
- global level wait_timeout
- session-level interactive_timeout
- session level wait_timeout
For the detailed experimental process of each parameter, you can read this post, but it is really dizzying. Let me summarize here, there are a few things you need to pay attention to:
- In interactive mode, the session level interactive_timeout inherits the global level
interactive_timeout . - In interactive mode, the connection duration is affected by interactive_timeout.
- In non-interactive mode, session-level wait_timeout inherits global-level wait_timeout.
- In non-interactive mode, the connection duration is affected by wait_timeout.
- In interactive mode, session-level wait_timeout inherits global-level interactive_timeout.
- Global-level value modifications are valid for subsequent connections, and session-level values are valid for the current connection.
- Parameters at the same level do not affect each other.
Someone came to the conclusion based on Article 5: To modify wait_timeout, you must also modify the global level interactive_timeout at the same time.
The reason is that they always view and modify session-level configuration, but this is of no significance to this article.
Our purpose is to modify the timeout time of subsequent connections in our connection pool (non-interactive mode), that is, we hope that the value of subsequent session-level wait_timeout will be the value we set. According to the third rule, we can know that at this time we just set the global level wait_timeout.
set global wait_timeout=300;
After understanding the timeout mechanism of mysql, it is not difficult to understand that the global level wait_timeout corresponds to maxlifetime. In order to prevent network delays from causing the request to be sent before maxlifetime has expired, wait_timeout has just expired when it reaches mysql, so maxlifetime must be set smaller than maxlifetime. seconds. As for why the previous 8-hour wait_timeout and 30-minute maxlifetime will lose the connection in about 5 minutes, I still don’t understand this for a while, so let’s configure it like this first.