C # Troppe connessioni in MySQL

Provo ad eseguire SELECT su un tavolo in MySql e ottengo questo errore:

  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 

Lo faccio su iis con .net e C #. Qualche idea su come posso risolvere questo problema?

Questo è per esempio come faccio a selezionare:

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(); 

Ecco come cancello:

  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(); 

Ecco come inserisco:

 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; 

Tutti gli esempi sopra mostrano la stessa debolezza. Non si utilizza l’ istruzione using che garantisce la chiusura e lo smaltimento della connessione e di altri oggetti monouso. Se una o più delle tue istruzioni generano un’eccezione, il codice che chiude la connessione non viene eseguito e potresti finire con l’errore di connessioni troppe

Per esempio

 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(SqlDataReader msdr = cmd.ExecuteReader()) { while (msdr.Read()) { //Read data } } } // Here the connection will be closed and disposed. (and the command also) 

Diminuisci il wait_timeout :

 [mysqld] wait_timeout=900 

Poiché ADO.NET utilizza il pool di connessioni, manterrà la connessione triggers anche se la si smaltisce. La soluzione è dire a MySQL di eliminare le connessioni.