Too Many Connections - ADO.NET pooling issues (C#)
Posted by: Jay Haynes
Date: July 06, 2020 03:19AM

I am using a combination of ADO.NET and C# with a MySQL database and I have an intermittent issue that has been occurring for a couple of years.

My hosting allows a maximum of 30 concurrent MySQL connections and pooling is enabled. However, occasionally on both low/high traffic days - I get the error 'max_user_connections' and I'm unable to connect to the database (as this point, I need to ask my webhost to clear them down to regain access). When these are cleared down, it sometimes fixes the problem and sometimes it happens again shortly after.

And then everything can be fine for days/weeks (but it always occurs again at some point). My webhost has confirmed my site is the only site with active connections.

Here is my connection code:

public class MySQL
{
private static string connectionString = ConfigurationManager.ConnectionStrings["MySQLConnectionInfo"].ConnectionString;

public class Get
{
public static DataTable GetDataTableUsingParms(string inSQL, List<dbParm> inParms, string from = null)
{
try
{
using (MySqlConnection myConnection = new MySqlConnection(connectionString))
{
using (MySqlCommand myCommand = new MySqlCommand(inSQL, myConnection))
{
foreach (dbParm inParm in inParms)
{
MySqlParameter currentParm = new MySqlParameter();

currentParm.ParameterName = inParm.ParmName;
currentParm.Value = inParm.ParmValue;

myCommand.Parameters.Add(currentParm);
}

DataTable myDataTable = new DataTable();
using (MySqlDataAdapter myAdapter = new MySqlDataAdapter(myCommand)) {
myAdapter.Fill(myDataTable);
}

return myDataTable;
}
}
}
catch (MySql.Data.MySqlClient.MySqlException err)
{
throw new System.Exception(err.ToString());
}
catch (System.TimeoutException err)
{
throw new System.Exception(err.ToString());
}
catch (System.Exception err)
{
throw new System.Exception(err.ToString());
}
}
}
}

Can anyone advise where to look next? I'm at a total loss.

Options: ReplyQuote


Subject
Written By
Posted
Too Many Connections - ADO.NET pooling issues (C#)
July 06, 2020 03:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.