Pomelo.EntityFrameworkCore.MySql
Pomelo.EntityFrameworkCore.MySql copied to clipboard
PomeloFoundation
wait_timeout and interactive_timeout issues after upgrading to MySQL 8.0.24
I am using Pomelo version 3.2.4 in some projects (.net core 3.1) but since updating to MySQL 8.0.24 i get issues in our services related to wait_timeout and interactive_timeout. More specifically, i get an unhandled exception in the service:
The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
This is referred also in the documentation: Connection Management Notes. Tried updating to 3.2.7 but i get the same issue. Any idea how to resolve it?
@mguinness we use pomelo and not the standard mysql-connector refered by the post.
Pomelo uses MySqlConnector under the hood as mentioned in the README.
Pomelo.EntityFrameworkCore.MySqlis the most popular Entity Framework Core provider for MySQL compatible databases. It supports EF Core up to its latest version and uses MySqlConnector for high-performance database server communication.
@mguinness thanks i understand , but i wanted to know if there is any workaround for project using EF Core 3.1.x.
And one more question, does Pomelo team plans to address this issue for versions using EF Core 3.1.x?
@e-simo What is your connection string look like and what does the following query return?
select @@wait_timeout;
Also, please post your connection string options you are using.
Are you using a single web server (for app hosting) and a single database server, or is this some kind of failover, load balancing or serverless environment with multiple web servers or databases?
Finally, have you setup the EF Core retry strategy option?
Hi, So the wait_timeout is 28800.
Connection string: server=xxxxx;port=3306;database=xxxxxx;uid=xxx;password=xxxx;SslMode=None;ConvertZeroDateTime=true;"
Disabling pooling(pooling=no in conn string), fixes the issue, however in our scenario we have some real-time streams processing and saving in db and we have a lot of opening/closing connections. I don't know how disabling pooling would affect our services.
From the section of the Connection Pooling Options, you could use the ConnectionLifeTime option with a value smaller than your wait_timeout value of 28800, e.g. 21600 (6 hours).
The other way to handle this is to use the EnableRetryOnFailure() option to automatically reestablish the connection once MySQL closes it because of the reached wait_timeout.
The same error with last pomelo lib 6.0.1 (mysql server 8.0.26)
@olx This should not be a Pomelo issue. If you suffer from this symptomatic, it could be rooted in a misconfiguration between MySQL and the MySqlConnector connection pool:
From the section of the Connection Pooling Options, you could use the
ConnectionLifeTimeoption with a value smaller than yourwait_timeoutvalue of28800, e.g.21600(6 hours).The other way to handle this is to use the EnableRetryOnFailure() option to automatically reestablish the connection once MySQL closes it because of the reached
wait_timeout.
Other options are to increase MySQL's wait_timeout system variable (up to it its max. value of 25,85 days on Windows and 365 days for other operating systems) or to disable connection pooling altogether.
For normal operations, I would suggest to just set ConnectionLifeTime to fewer seconds than wait_timeout (e.g. 1 hour less).
On the other hand, you should not reach the wait_timeout by default (even if you are using an old version of MySqlConnector like 0.69.10), because the default value of ConnectionIdleTimeout is 180, which should close idle connections within 4 minutes (unless you set MinimumPoolSize to a value greater than 0).
@olx Please post the connection string you are using (remove credentials), so we can take a look at it. Also please share with us, what version of MySQL you were using before switching to 8.0.26.
The MySQL 8.0.24 release notes state the following:
Connection Management Notes
Previously, if a client did not use the connection to the server within the period specified by the wait_timeout system variable and the server closed the connection, the client received no notification of the reason. Typically, the client would see Lost connection to MySQL server during query (CR_SERVER_LOST) or MySQL server has gone away (CR_SERVER_GONE_ERROR).
In such cases, the server now writes the reason to the connection before closing it, and client receives a more informative error message, The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. (ER_CLIENT_INTERACTION_TIMEOUT).
The previous behavior still applies for client connections to older servers and connections to the server by older clients.
This change should have no impact on this issue here.
Sounds related to the MySQL issue: Bug #105229 Reconnect option is not working
It was fixed in version 8.0.30. Here are the release notes with the fix: Changes in MySQL 8.0.30 (2022-07-26, General Availability)
API: Applications that previously used the MySQL client library to perform an automatic reconnection to the server received the following mysql_query error after the server was upgraded:
[4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. (Bug #105229, Bug #34007830)
