Bug #54284 To many connections
Submitted: 7 Jun 2010 10:37 Modified: 13 Jun 2010 23:30
Reporter: Martin Barker Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.1.3.0 OS:Microsoft Windows (7 (VS 2010))
Assigned to: CPU Architecture:Any
Tags: Connections, fail to close, flood

[7 Jun 2010 10:37] Martin Barker
Description:
I have code for handling all MySQL query's though 1 connection, (see attached)

This connection is opened and closed, every for each individualize query,

I Have a single thread that uses this class there is nothing else in the application querying the data while this code runs.

Will attach calling code.

This has been repoted as a bug to be filed as "not a bug" Well it is,

Every thing i though 1 connection, this bug is creating problems as this is one connection why every time i send a new query to the server is it opening an new mysql connection -- you need to check the connections are being closed when the close() method is called,

How to repeat:
Create a loop,

That opens a connection, sends a query then closes the connection for more than the connections limit in the my.cnf

Suggested fix:
Check that all the Connections are closing when the close is called
[7 Jun 2010 10:41] Martin Barker
This file handles all calls to the MySQL Server

Attachment: MySqlConnection.cs (text/plain), 8.92 KiB.

[7 Jun 2010 10:43] Martin Barker
This is the Method that is calling the MySQL_connection.cs

Attachment: getInformation.txt (text/plain), 5.43 KiB.

[7 Jun 2010 10:46] Martin Barker
This is a sample of the file that the Get Information method is parsing (CSV)

Attachment: config_sample.txt (text/plain), 22.19 KiB.

[7 Jun 2010 10:54] Martin Barker
i have all-so tried

Adding a Thread.Sleep above every connection.open(),

That waited 10 Seconds before trying to connect again,

Witch would say that the connection is being kept alive

All-so supported by the closing of the application which then closes every connection no problems
[7 Jun 2010 11:12] Martin Barker
Updated Secerity, 

No Available workaround, and causes Applications Halt then Crash,
[7 Jun 2010 15:35] Bobby Ward
Also seeing this.

App is single threaded and opens a new connection for each query.  
Problem showed up after an upgrade from 5.1.4 to 6.2.3
[7 Jun 2010 17:11] Martin Barker
Also Verified by another developer in our office asked him to try with his local MySQL Server same problem occurs.
[8 Jun 2010 10:44] Vladislav Vaintroub
The code attached has number of cases where connection.Close() will be missed due to (eaten) exception. And this is likely the problem.

It is really the best to use "using" blocks, like

using (MySQLConnection connection = new MySQLConnection(..))
{
  // use connection here
}

In that case, no matter what you do, whether you return inside the block, or whether you have exception inside of the block, connection will be closed at the end of "using" block.
[8 Jun 2010 10:47] Vladislav Vaintroub
Here an example of "eaten" exception which misses Close (from the attached MySQLConnection.cs)

 try
 {
    Reader = command.ExecuteReader();
 }
 catch (Exception Ex)
 {
    //MessageBox.Show(Ex.Message);
    return;
 }
 connection.Close();
[9 Jun 2010 8:09] Tonci Grgin
Martin, please do as Wlad suggested and get back to us with results. Also, c/NET 6.1 is a bit old, please upgrade if possible. We will be testing the problem, if necessary, with latest sources.
[9 Jun 2010 9:07] Martin Barker
Vladislav Vaintroub,

Hey Where is there one that's open and not closed the only place i can see where it could fail to close is in the connect methods then it returns false and prevents any attempt to query the database.

Could you point out where it would fail to close? i might be missing some thing.
[9 Jun 2010 12:25] Vladislav Vaintroub
it could leak here:

- num_rows() (if connection is opened, command.ExecuteReader() or Reader.Read() throws exception, Close is missing)

- connect() (If command.ExecuteReader() throws an exception)

- select_db() (If command.ExecuteReader() throws an exception)

- Query() (If command.ExecuteReader(), Reader.Read, or Reader.GetValue() throws an exception)

So basically every method in WindowsFormsApplication3.Class.MySQLConnection can leak connections, because no care is taken to close them in case of exception.

As I said, the simplest method to handle it automatically is "using" keyword
[10 Jun 2010 13:25] Martin Barker
if (Query.IndexOf("INSERT") != -1)
            {
                totalResults = 0;
                resultsCount = 0;
                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();
                connection_count--;
            }
            else if (Query.IndexOf("SELECT") != -1)
            {
                totalResults = 0;
                resultsCount = 0;

                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }catch(Exception Ex){
                    MessageBox.Show(Ex.Message);
                    return;
                }
                while (Reader.Read())
                {
                    totalResults++;
                }

                results = new string[totalResults][];
                connection.Close();
                connection_count--;

                connection.Open();
                connection_count++;
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        thisrow += Reader.GetValue(i).ToString() + ",";
                    }
                    this.callback(thisrow.Split(','));

                }
                connection.Close();
                connection_count--;
            }
            else if (Query.IndexOf("UPDATE") != -1)
            {
                totalResults = 0;
                resultsCount = 0;
                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();
                connection_count--;
            }
            else if (Query.IndexOf("DELETE") != -1)
            {
                totalResults = 0;
                resultsCount = 0;
                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();
                connection_count--;
            }

Every one of my Query Commands has

                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();

Now look after the catch (so after error has been found) it calls connection.Close 
if a try/catch in place then if it fails in the try it runs the catch it then continues to run the code, if there is not a try/catch it would halt the application and thus then leave the connections open.

Learn how to program before commenting
[10 Jun 2010 13:33] Martin Barker
I Would like to say that upgrading to 6.2.3.0 has fixed the problem but you might want to pull 6.1.3.0 out of the archives just in-case people find it.
[13 Jun 2010 23:34] Vladislav Vaintroub
[10 Jun 15:25] Martin Barker 
<skip>
>Every one of my Query Commands has
>
>                try
>                {
>                    Reader = command.ExecuteReader();
>                }
>                catch (Exception Ex)
>                {
>                    MessageBox.Show(Ex.Message);
>                    return;
>                }
>                connection.Close();

>Now look after the catch (so after error has been found) it calls >connection.Close 

No, it calls "return" in the "catch". Thus *after* the catch code is just unreachable.