Bug #112123 Pool exhaustion after timeouts in transactions

• Submitted: 21 Aug 2023 9:15
• Modified: 27 Nov 2023 21:33
• Reporter: Kristoffer Sjöberg (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 8.0.31, 8.1.0
• OS: Any
• CPU Architecture: Any
• Tags: regression

[21 Aug 2023 9:15] Kristoffer Sjöberg

Description:
Perhaps related to #109390. Bug present in 8.0.31 and later (including 8.1.0). I think S1 severity, due to an eventual complete and irrecoverable lack of service.

Each time an error occurs while executing a MySQL command while in a transaction, the connection is neither discarded from nor returned to the pool but remains marked as "InUse".

Since the connection pool is 100 connections by default, 100 timeout errors is enough to cause pool exhaustion. This causes all further uses of the database to fail (in transactions or not), because a connection cannot be retrieved from the pool. Any connections using the same connection string will throw:

MySql.Data.MySqlClient.MySqlException
"error connecting: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached."
  StackTrace:
   at MySql.Data.MySqlClient.MySqlPool.<GetConnectionAsync>d__35.MoveNext()
   at MySql.Data.MySqlClient.MySqlConnection.<OpenAsync>d__94.MoveNext()
   at MySql.Data.MySqlClient.MySqlConnection.Open()

How to repeat:
Execute the following code snippet some 100+ times, until the exception's message no longer reads "Fatal error encountered during command execution". It will then be a connection pool error, and it will not recover.

private static void CauseCommandTimeout()
{
    using (var conn = new MySqlConnection("server=localhost;uid=root;password=xxx"))
    {
        conn.Open();
        using (var tran = conn.BeginTransaction())
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "DO SLEEP(5);";
                cmd.CommandTimeout = 1;
                cmd.ExecuteNonQuery();
            }
        }
    }
}

static void Main(string[] args)
{
    for (var i = 0; i < 200; i++)
    {
        try
        {
            CauseCommandTimeout();
        } catch(MySqlException ex)
        {
            if (ex.Message != "Fatal error encountered during command execution.")
            {
                throw;
            }
        }
    }
}

[22 Aug 2023 7:26] MySQL Verification Team

Hello Kristoffer,

Thank you for the report and feedback.
Related - Bug #109390, Bug #107110

regards,
Umesh

[27 Nov 2023 21:33] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.3.0 release, and here's the proposed changelog entry from the documentation team:

The timeout period of a transaction could elapsed prior to obtaining a
connection from the connection pool. This might occur because all pooled
connections were neither discarded from nor returned to the pool, but
remained marked as InUse. The fix resolves an issue that prevented the
removal of a connection from the connection pool after an exception was
thrown.

Thank you for the bug report.