HikariCP
HikariCP copied to clipboard
brettwooldridge
maxLifeTime(20000) millisecond value less than wait_timeout(28800) second
@csc365kl agree with that, and i have a same question?Is that a bug or not?how shold i do?
Originally posted by @jerry100chan in https://github.com/brettwooldridge/HikariCP/issues/1483#issuecomment-642406054
To be sure, check in your database the true value of wait_timeout, doc says that it is 28800 but in my case it was set to 600, you can check it by firing this command in mariadb:
show global variables like "wait_timeout";
mysql> show global variables like "wait_timeout"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 120 | +---------------+-------+ 1 row in set (0.05 sec)
mysql> show session variables like "wait_timeout"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.09 sec)
Wondering if this confusion is due to the session vs. global. Which one does maxLifeTime get compared to?
I have a similar problem:
show global variables like "wait_timeout";
Result: 28800
`show session variables like "wait_timeout"; Result: 28800
Application.properties
spring.datasource.hikari.minimumIdle:5 spring.datasource.hikari.maximumPoolSize:10 spring.datasource.hikari.idleTimeout=30000 spring.datasource.hikari.pool-name:MFAC-HIKARI spring.datasource.hikari.maxLifetime=2000000 spring.datasource.hikari.connectionTimeout=30000
But, idle time less than 4 minutes is enough to close the connection
16:42:32.398 WARN 24372 : MFAC-HIKARI - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6e8e90bc (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value. 16:53:10.989 WARN 24372 : MFAC-HIKARI - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@c11be50 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value. 17:01:15.328 WARN 24372 : MFAC-HIKARI - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@2d1870c8 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value. 17:10:26.457 WARN 24372 : MFAC-HIKARI - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@2f01d38f (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value. 17:20:46.564 WARN 24372 : MFAC-HIKARI - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@14485a79 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
My database is mysql 5.7 (aws RDS)
same problem Mysql 8.0.22，Mysql/ConnectorJ 8.0.22, HikariCP-3.4.5
com.zaxxer.hikari.pool.PoolBase HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@606407aa (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
And, I post Connection Session Level Mysql variables(SHOW VARIABLES LIKE "wait_timeout") and the Rumtime HikariConfig(spring-boot-starter-jdbc default configuration)
It shows:
HikariConfig
...
connectionTestQuery: null
idleTimeout: 600000 = 10min
maxLifetime: 1800000 = 30min
Mysql variables
'wait_timeout', '7200' = 120min
idleTimeout&maxLifetime are shorter than wait_timeout, but the problem still hanppened.
And I Checked com.zaxxer.hikari.pool.PoolBase#isConnectionAlive
it may caused by Connection#isValid，but why?
I have the same problem:
MariaDB [(none)]> SHOW VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
My app settings are:
max-lifetime: 590000 #milliseconds wait time
Whatever the value I set there, I get this warning:
2021-07-20 14:57:00.025 WARN 80508 --- [nio-8080-exec-2] com.zaxxer.hikari.pool.PoolBase : Hikari - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@8a5fa7a (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
@SimpleIto mybe connectionTestQuery
select 1?
I tried this. It doesn't work for me. As time goes on, it still cause the same problem. And I have saw the source code of HikariCP
// com.zaxxer.hikari.pool.PoolBase#isConnectionAlive
// if "ConnectionTestQuery" is null, "isUseJdbc4Validation" will be true
if (isUseJdbc4Validation) {
return connection.isValid(validationSeconds);
}
In short, i cannot fix it in the end. Maybe it's a cloud RDS connection limit problem between the application and database.
Any update on this? Pairing HIkariCP max-lifetime with MariaDB wait_timeout, taking care of time unit difference doesn't work, even lowering the wait_timeout time for a few seconds when setting max-lifetime.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
max-lifetime: 28780000 #milliseconds wait time
I set maxLifetime 20 seconds lower, but I still get the warning:
2021-12-15 09:50:47.617 WARN 90694 --- [nio-8080-exec-5] com.zaxxer.hikari.pool.PoolBase : Hikari - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@8930bc6 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
Having similar issues with MySQL 8.0.20 and HikariCP 4.0.3 (deployed in Docker)
MySQL
--wait-timeout=3900 # 65 min
HikariCP
max-lifetime: 3600000 # 60 min
com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@62551351 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
Thought I'd pile on with everyone else.
SHOW SESSION VARIABLES LIKE "wait_timeout";
------------------------
| wait_timeout | 28800 |
------------------------
SHOW GLOBAL VARIABLES LIKE "wait_timeout";
---------------------
| wait_timeout | 60 |
---------------------
I just discovered that the GLOBAL was at 60. Not sure which one takes precedence though. I have Hikari set to 5 minutes (maxLifetime: 300000). I don't get the "No operations allowed message though". I just get loads of warnings about connection failures and connections being evicted.
HikariPool-AccountService - Failed to validate connection
software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ConnectionImpl@750edeae
(The active SQL connection has changed due to a connection failure. Please re-configure session state if required.).
Possibly consider using a shorter maxLifetime value.
I'm going to change the global wait_timeout and/or my maxLifetime and see if magic happens. But seeing this thread makes me doubt that it will make a difference.
Updating here I was not able to (yet) update the global timeout on my database. But I was able to change it in the connection url!
jdbc:mysql:aws://HOST:3306/thedb?sessionVariables=wait_timeout=1800000
Seems to have made my problem go away. So I'm pretty sure setting the global wait_timeout will make a difference. But setting the session wait_timeout also helps.
I have the same problem，mysql wait_timeout is 28800(both global and session) .
then I found that my mysql server is under proxy/loadBalance , and the proxy always killed idle connection more than 120s
