MTA MTA - 5 months ago 54
SQL Question

Too many connections Exception

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?

EDIT



I add some code from my server:

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", item.babysitterid);
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;

Answer

Open up the Admin page on your (local) MySql monitor and observe to see if the number of connection grows with time. If this is unexpected behaviour, then you may have unclosed connection objects in your code (see comment from Steve below your question). If this is not unexpected i.e. you know that your app will generate and hold a lot of connections, then you may need to change the setting in MySQL

https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

by adjusting max_connections:

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections

UPDATE: if you don't expect and don't need so many open connections, and want to make sure you haven't overlooked any close() statements, then good practice is to employ the using construction around objects implementing IDisposable, such as connections, to make sure that they are closed as soon as possible, allowing the garbage collector to clean up objects no longer in use.

Comments