MySQL Bugs: #72025: connection in CLOSE_WAIT after failed connect (connection limit)

Bug #72025	connection in CLOSE_WAIT after failed connect (connection limit)
Submitted:	12 Mar 2014 16:37	Modified:	16 Jul 2014 23:31
Reporter:	Johannes Abt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.8.3	OS:	Any
Assigned to:	Francisco Alberto Tirado Zavala	CPU Architecture:	Any

Description:
When the MySQL server rejects a connection becuase of a connection limit, MySqlConnection.Open() fails. That's OK. 

Though the error handling on MySqlConnection.Open() leaves the TCP connection in CLOSE_WAIT state. This is a severe resource leak. The abandoned network stream objects sum up and the process hits the open file handle limit after calling Open() several times.

If MySqlConnection.Open() fails, it has to release all acquired resources.

How to repeat:
using System;
using System.Collections.Generic;
using System.Data;

using MySql.Data.MySqlClient;

namespace Test
{
    public static class MySqlTest
    {
        public static void Test1() {
            var connectionString = "SERVER=...;DATABASE=...;uid=...;password=...;Pooling=true;Max Pool Size=1000;";
            var connections = new List<IDbConnection>();
            for (int n = 0; n < 150; n++) {
                Console.WriteLine(n);
                MySqlConnection con = null;
                try {
                    con = new MySqlConnection(connectionString);
                    con.Open();
                    using (var cmd = con.CreateCommand()) {
                        cmd.CommandText = "select 0";
                        cmd.ExecuteNonQuery();
                    }
                    connections.Add(con); ;
                } catch (Exception ex) {
                    Console.WriteLine(ex);
                    if (con != null) {
                        con.Close();
                    }
                }
            }
            Console.WriteLine("Now check 'netstat'! Then press return to quit.");
            Console.ReadLine();
            Console.WriteLine(connections.Count);
        }
    }
}

Suggested fix:
File Source\MySql.Data\Driver.cs, in method "Create(MySqlConnectionStringBuilder settings)", line 214:

<<<<<<<<<<
      if (d == null)
        d = new Driver(settings);
      d.Open();
      return d;
    }
==========
    if (d == null)
        d = new Driver(settings);

      try {
          d.Open();
      } catch {
          d.Dispose();
          throw;
      }
      return d;
    }
>>>>>>>>>>>>>>>

The loop count in the demo code should be 1000 instead of 150.

Hello Johannes.

Connector/Net is working properly, all the connections that fails to open are set to Closed status.

The connection that you are seeing on "CLOSE_WAIT" status are the connections that you left open and are stored in your list object (var connections), when your app/program finish to run, your Server or Connector/Net will close any connection open that is not in use.

Thanks for your time.

Hello Francisco,

All the connections that fail to open are set to Closed status (Connector.SetState()). Right. Though "Closed status" is just an internal flag of Connector/NET. The real TCP connection remains open. That's what I call a resource leak.

> The connection that you are seeing on "CLOSE_WAIT" status are the connections > that you left open and are stored in your list object (var connections)

That's wrong. All connections in "var connections" are in state "CONNECTED".

When I try to open too many connections, the following happens:

1. Connector/NET opens a TCP connection
2. mysql accepts the connection request
3. Connector/NET sends the credentials
4. mysql sends reponse "too many connections error"
5. mysql closes TCP connection => The connection is in CLOSE WAIT state on client side.
6. Connector/NET receives "too many connections error"
7. Connector/NET throws a "MySqlException"

If you do TCP programming, you need to close connections on _both_ sides. Otherwise, the socket will remain in "CLOSE_WAIT" and keep occupying a file handle. File handles are a limited resource.

Connector/NET forgets to close the TCP connection. Consequently, Connector/NET has a resource leadk. qed.

> when your app/program finish to run, your Server or Connector/Net will close 
> any connection open that is not in use.

I am not sure that I understand you. It sounds to me like: "You do not need to call 'free()' in a C program, because all the memory will be freed a the end of the program anyway." That's OK for a short program, but not for long running application.

I have written a second, simpler test method for you.
You need to set "max_connections" to "1" and restart mysql before running.

You will see two connections in "netstat". One in CONNECTED state,
the other in CLOSE_WAIT state. This is because only
one connection can be established successfully.
Connector/NET fails to close the second TCP connection immediately after receiveing "too man connections" from the server.

[TestMethod]
public void TestMethod2() {
    var connectionString = "SERVER=...;DATABASE=...;uid=...;password=...;Pooling=true;";
    using (var con1 = new MySqlConnection(connectionString)) {
        con1.Open();
        try {
            using (var con2 = new MySqlConnection(connectionString)) {
                con2.Open();
                Assert.Fail("max_connections should be set to 1");
            }
        } catch (MySqlException ex) {
            Console.WriteLine(ex.Message);
            Console.WriteLine("check 'netstat' now");
            Console.ReadLine();
        }
    }
}

Hello Johannes.

I was unable to reproduce the issue with your second example, but I identified how to always reproduce it.

The sockets connection are left when the client try to get a connection from the server when the max number of connections is reached. When this happen the server send a FIN message to the client waiting for a ACK message back but this is never sent. 
The OS handle the open socket connections that are in that status and clean it after some time, in Windows is after 4 minutes by default, in some tests that I did was the sockets were closed in 2 minutes.

I tested the fix that you suggest and now there are no more open sockets on CLOSE_WAIT status, we really appreciate your help. Thanks a lot for your contribution, we're going to add it in the next maintenance release.

Thanks for your time.

Fixed as of the upcoming Connector/Net releases, and here's the changelog entry:

When the connection limit was exceeded, "MySqlConnection.Open()" would
leave the TCP connections in a "CLOSE_WAIT" state, but now closes them.

Thank you for the bug report.

Versions fixed: 6.6.7 6.7.6, 6.8.5, and 6.9.0.