Hi everyone,
I hope you’re doing well.
I’ve been encountering some issues with Percona MySQL and ProxySQL for load balancing in my web applications. Here’s a brief overview of the problem:
Problem Description:
- After deploying my web applications, they initially work fine for around 10 or 15 minutes.
- However, after this initial period, the connections to Percona MySQL through ProxySQL are being closed unexpectedly.
Error Message:
-
The error message I’m receiving indicates that the connections are being closed prematurely. Here’s a sample error message:
HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@… (No operations allowed after connection closed.)
Configuration Details:
-
I’m using Percona MySQL as the backend database and ProxySQL for load balancing.
-
The connection pool is managed by HikariCP.
Troubleshooting Steps Taken:
- I’ve checked the database server logs and configuration, but I couldn’t find any issues on that end.
- I’ve also reviewed the configuration of ProxySQL and HikariCP, ensuring that they’re set up correctly.
Request for Assistance:
- I would appreciate any insights or suggestions on how to troubleshoot and resolve this issue.
- If anyone has experienced similar issues or has expertise in Percona MySQL and ProxySQL configuration, your guidance would be invaluable.
Additional Information:
- If there are specific logs, configurations, or settings that you’d like me to provide for further analysis, please let me know, and I’ll gladly share them.
Thank you in advance for your help and support.
Best regards,
sandhoshpandi
@Santhosh_Pandi, what is wait_timeout variable in MySQL and maxLifetime in Hikari config ? also, check the same mysql-wait_timeout in proxysql. thanks
will recommend to have set wait_timeout variable in MySQL to be greater than maxLifetime in Hikari, so as to avoid connections being killed by MySQL. e,g by giving maxLifeTime(20000) value less than wait_timeout(28800) in mysql
thank you for your immediate response,
i will check and revert back soon
2024-02-06 13:10:47 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:40452
2024-02-06 13:10:49 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:40478
2024-02-06 13:10:49 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:40494
2024-02-06 13:10:52 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:40542
this error was throws 20.113.158.117 this is applications is deployed
MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection this error also occur sir how to solve this issue
@Santhosh_Pandi If the client disconnects in a not graceful way, proxysql will generate a warning.
This can be useful in identifying patterns, for example, if the issue is mostly from an application and not from another
ProxySQL 2.0.8 introduced a new variable mysql-log_unhealthy_connections to suppress messages related to unhealthy client’s connections being closed.
2024-02-07 08:25:38 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:43590
2024-02-07 08:25:40 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:43632
2024-02-07 08:25:42 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57662
2024-02-07 08:25:42 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57670
2024-02-07 08:25:42 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57686
2024-02-07 08:25:43 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57696
2024-02-07 08:25:43 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57702
2024-02-07 08:25:44 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57714
2024-02-07 08:25:48 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57748
2024-02-07 08:25:48 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57758
2024-02-07 08:25:49 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57774
2024-02-07 08:25:52 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:57790
2024-02-07 08:26:00 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:56634
2024-02-07 08:26:02 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:35876
2024-02-07 08:26:02 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:35888
2024-02-07 08:26:04 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:35950
20.113.158.117 this is ip is i had deployed server ip why this error can occurs ,but some time working and some time not working
2024-02-07 08:29:18 MySQL_Session.cpp:5509:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] mysql-max_connections reached. Returning ‘Too many connections’
2024-02-07 08:29:19 MySQL_Session.cpp:5509:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] mysql-max_connections reached. Returning ‘Too many connections’ i have connect two users only but throw too many connections error
hread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.52.153.225:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.170.28.206:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:56252
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:56264
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36140
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36126
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36138
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:38420
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:38422
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:38430
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:43006
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.170.29.122:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.170.28.206:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.170.29.122:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.170.29.122:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.52.153.225:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3631:process_data_on_data_stream(): [WARNING] Detected broken idle connection on 20.52.153.225:3306
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:58736
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:56240
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:56262
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:42998
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:43004
2024-02-07 11:13:06 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:43024
2024-02-07 11:13:33 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:51450
2024-02-07 11:13:36 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:51452
2024-02-07 11:13:38 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:51462
2024-02-07 11:13:43 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:42572
2024-02-07 11:13:48 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:42590
2024-02-07 11:14:11 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:50452
2024-02-07 11:14:13 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:50424
2024-02-07 11:14:18 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:50442
2024-02-07 11:14:31 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:52808
2024-02-07 11:14:33 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:45366
2024-02-07 11:14:47 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36100
2024-02-07 11:14:51 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36124
how to solve above issue any one help me
please configure mysql-max_connections to same as MySQL db , if all three nodes are reader that mean writer is also writer, I belive you might have set max_connection in proxysql host group as 1000 so for each node proxysql is able to create 1000 connection so set mysql-max_connections=4000 on proxysql and max_connections=4000 on MySQL db . thanks obviously laod and save proxysql variable to runtime and disks
[quote=“Santhosh_Pandi, post:10, topic:28290”]
2024-02-07 11:14:33 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:45366
2024-02-07 11:14:47 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36100
2024-02-07 11:14:51 MySQL_Thread.cpp:3939:process_all_sessions(): [WARNING] Closing unhealthy client connection 20.113.158.117:36124
[/quote
Any one facing this issue