IIS Worker Process Recycle is periodically breaking MySQL client in .Net Core 3 application

0

The automatic daily IIS recycle of worker process is occasionally (every 5-10 days) breaking the ability of the application to connect to AWS Aurora (MySQL) Server.

After the recycle - we get these errors. Other worker processes in the same IIS instance continue working just fine - all configured similarly. The errant worker process then becomes hung and IIS itself has to be recyled to fix the problem.

Oracle MySql.Data.MySqlClient .Net Core Class Library 8.0.20

MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.Driver.Open() at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open() ...

More information from logs... It would appear that it handles the first few new requests - then has problems - so might be coincidental - no event log errors that correspond to the problem though...

Connection Pool is using defaults: Max Pool Size 100 Connection Lifetime 0

Additional Information: Logs show before complete breakdown that some executions are fine, others taking many seconds instead of less than 100ms - in the same worker thread. So something pool related might be going awry or the instance.

Definitely going to adjust when the cycling occurs.

mysql
iis
.net-core
asked on Stack Overflow Oct 15, 2020 by Robin Johnson • edited Oct 19, 2020 by Robin Johnson

1 Answer

0

After more research I discovered that we were just letting C# garbage collection handle the SqlConnection object instead of properly encapsulating the object in a "using" declaration. This is a problem because until garbage collection runs and destroys the SqlConnection object, the underlying database connection is not returned to the connection pool. And so when things are especially busy we stupidly starve to death despite having a warehouse full of food because the garbage collection is deferred and the connections are not being returned to the pool in a timely fashion.

The modern C# syntax allows this really simple declaration which runs the destructor immediately when the object goes out of scope. So when the object(s) leave scope the system immediately returns the underlying connection back into the pool preventing the application from starving to death.

using MySqlConnection z_connection = new MySqlConnection();
...
using MySqlCommand z_command = z_connection.CreateCommand();
...
using MySqlDataReader z_reader = z_command.ExecuteReader();
...
answered on Stack Overflow Feb 23, 2021 by Robin Johnson

User contributions licensed under CC BY-SA 3.0