C# Too many connections in MySQL

Question

I try to run SELECT on a table in MySql and i get this error:

    Server Error in '/MyApp' Application.
Too many connections
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Too many connections

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[MySqlException (0x80004005): Too many connections]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket() +517
   MySql.Data.MySqlClient.NativeDriver.Open() +702
   MySql.Data.MySqlClient.Driver.Open() +245
   MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) +297
   MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() +18
   MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() +403
   MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() +228
   MySql.Data.MySqlClient.MySqlPool.GetConnection() +106
   MySql.Data.MySqlClient.MySqlConnection.Open() +1468


Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

I run it on iis with .net and C#. Any idea how i can fix this problem?

This is for example how i make select:

MySqlDataReader msdr;

    MySqlConnection connect = new MySqlConnection(connectionStringMySql);
    MySqlCommand cmd = new MySqlCommand();

    string commandLine = "SELECT * FROM Table WHERE active=1;

    commandLine = commandLine.Remove(commandLine.Length - 3);
    cmd.CommandText = commandLine;

    cmd.Connection = connect;
    cmd.Connection.Open();

    msdr = cmd.ExecuteReader();

    while (msdr.Read())
    {
        //Read data
    }

    msdr.Close();
    cmd.Connection.Close();

This is how i Delete:

                MySqlConnection connect = new MySqlConnection(connectionStringMySql);
                MySqlCommand cmd = new MySqlCommand();

                cmd.Connection = connect;
                cmd.Connection.Open();

                string commandLine = @"DELETE FROM Table WHERE id=@id;";

                cmd.CommandText = commandLine;

                cmd.Parameters.AddWithValue("@id", slotId);

                cmd.ExecuteNonQuery();
                cmd.Connection.Close();

This is how i insert:

MySqlConnection connect = new MySqlConnection(connectionStringMySql);
                MySqlCommand cmd = new MySqlCommand();

                cmd.Connection = connect;
                cmd.Connection.Open();

                string commandLine = @"INSERT INTO Table (id, weekday, start, end) VALUES" +
                    "(@ id, @weekday, @start, @end);";

                cmd.CommandText = commandLine;

                cmd.Parameters.AddWithValue("@ id", id);
                cmd.Parameters.AddWithValue("@weekday", item.weekday);
                cmd.Parameters.AddWithValue("@start", new TimeSpan(item.starthour, item.startmin, 0));
                cmd.Parameters.AddWithValue("@end", new TimeSpan(item.endhour, item.endmin, 0));

                cmd.ExecuteNonQuery();
                long id = cmd.LastInsertedId;
                cmd.Connection.Close();
                return id;

Convert all your code to implement a `using( ){ }` construct you're problem appears to be that you are not `Disposing` and of your `SqlCommand` objects — MethodMan, Jun 22 '13 at 20:33
Just wondering, is the space in the parameter name in `cmd.Parameters.AddWithValue("@ id", item.babysitterid);` valid? — Andrew Morton, Jun 22 '13 at 20:43

Steve · Accepted Answer · 2018-11-07T07:23:29.570

11

All the examples above show the same weakness. You don't use the using statement that will ensure the propert closing and disposing of the connection and other disposable objects. If one or more of your statements throw an exception, the code that close the connection is not executed and you could end with the too many connections error

For example

string commandLine = "SELECT * FROM Table WHERE active=1";
commandLine = commandLine.Remove(commandLine.Length - 3);
using(MySqlConnection connect = new MySqlConnection(connectionStringMySql))
using(MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
    connect.Open();
    using(MySqlDataReader msdr = cmd.ExecuteReader())
    {
        while (msdr.Read())
        {
            //Read data
        }
    }
} // Here the connection will be closed and disposed.  (and the command also)

edited Nov 07 '18 at 07:23

answered Jun 22 '13 at 20:35

Steve

213,761
22
232
286

Thank you for the example... i will implement it on the my code. – YosiFZ Jun 22 '13 at 20:50
It should be an easy fix because you are already doing things right. You create the objects only when needed and don't keep them around in global variables. – Steve Jun 22 '13 at 20:52
1

Shouldn't the reader returned from the `ExecuteReader` be in a using block too? – Scott Chamberlain Jun 22 '13 at 21:08
Yes, it would be better to enclose also the SqlDataReader in the using block for the disposing – Steve Jun 22 '13 at 21:13
@Steve can you edit please your post with the SqlDataReader example – YosiFZ Jun 22 '13 at 21:14
2

Done, moved also the Handling of the commandtext outside the using block and created the MySqlCommand with the constructor that accepts commandText and connection to reduce the code inside the using block – Steve Jun 22 '13 at 21:16
@Steve i have another question.the dispose is equal to close connection? there is any diffrent between them? – YosiFZ Jun 23 '13 at 16:48
Dispose calls Close in the standard implementation of SqlServer. I suppose that it is the same for MySql. Close terminates the connection, eventually you could reopen a closed connection. Dispose frees all the unmanaged resources and after that you need to recreate the object. – Steve Jun 23 '13 at 16:51
@Steve and if in the while i place `return`, the connetion will still close and disposed? – YosiFZ Jun 23 '13 at 16:53
Yes, the using statement is a shortcut for try/finally, so it is not important the way in which you exit from the using block, the hidden (in the closing brace) finally clause that dispose the connection is always executed – Steve Jun 23 '13 at 16:56
will the connection closed automatically if the object disposed ? – Agus Sapurta Sijabat Jul 10 '18 at 03:23
As I have said. Dispose calls Close in the standard implementation. This is the same for MySql – Steve Jul 10 '18 at 06:37

score 4 · Answer 2 · answered Jun 22 '13 at 21:27

4

Decrease the wait_timeout:

[mysqld]
wait_timeout=900

Because ADO.NET use connection pooling, it will keep your connection alive even if you dispose it. The solution is to tell MySQL to drop the connections.

answered Jun 22 '13 at 21:27

Max Toro

28,282
11
76
114

Sorry but I do not agree. The purpose of the connection pool is to give back to the program a connection already available. If you force a connection to be removed from the pool I think that the only _benefit_ is a decrease in performance not a better handling of the _not closed connections_ I think it is like curing the symptoms not the cause. However I am not an expert in MySql nuisances and so if you could point me to a document supporting this approach I will be interested. – Steve Jun 22 '13 at 22:08
`using` only helps when an exception happens, the user is already closing the connection, which is the same as disposing it. The problem is that MySQL keeps the connections alive for too long, new connections are created faster than old ones are destroyed. – Max Toro Jun 22 '13 at 22:48
Please explain to me if my understanding of connection pooling is flawed. I open a new connection, the pool is empty and thus a new connection is established with MySql. I close the connection in whichever way I prefer and the connection is not closed but kept in the pool. Now, I open a new connection USING THE SAME CONNECTION STRING, the pool kicks in and give me back the previous connection without requiring a new one to the MySql server. Unless a mutltithread op or multiple request from different clients there is only ONE connection. Am I wrong? – Steve Jun 22 '13 at 22:55
Your logic is correct. When you have concurrent access, and maybe other reasons I don't know of, you end up with more than one connection in the pool. – Max Toro Jun 22 '13 at 23:15
great! I was experiencing connection exceptions , but only after the server was running for a while. I already thought it's something around connection pooling and connections not being closed properly and was trying different things but this finally solved the issue! Awesome Max, thanks a lot! – skhro87 May 19 '15 at 05:18

C# Too many connections in MySQL

2 Answers2

Linked