I have an issue with a legacy web-forms application that uses ADO.NET and MySQL.
Quite often I get the error of 'max_user_connections' and talking through the issue with my web host, it appears that my code is not killing off idle connections somewhere.
Here is my connection code, does anything stand out as obvious that could be causing this issue?
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());
}
}
}
}
I'm allowed 30 connections on my web-host (this is controlled at package level so can't be changed) and this issue happens with very few users (< 10) on the site.
Accroding to your description,as far as I think,you could set max_connections.Max_connections is the total connection limit and max_user_connections is the per user limit.
The value of max_user_connections must never exceed the value of max_connections.
The default limit of max_connections is 151,your max_user_connections value is 30 and the user cann't more than 5.
So,you could reset your max_connections.
Best regards,
Yijing Sun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Accroding to your description,as far as I think,there are two solutions:
1.Close the connection object inside the Finally block.
2. Upgrade your account to higher hosting plan package for more connections.
Best regards,
Yijing Sun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
140 Points
590 Posts
ADO.NET & MySQL - max_user_connections
Jul 12, 2020 04:03 PM|RageRiot|LINK
Hi everyone,
I have an issue with a legacy web-forms application that uses ADO.NET and MySQL.
Quite often I get the error of 'max_user_connections' and talking through the issue with my web host, it appears that my code is not killing off idle connections somewhere.
Here is my connection code, does anything stand out as obvious that could be causing this issue?
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()); } } } }I'm allowed 30 connections on my web-host (this is controlled at package level so can't be changed) and this issue happens with very few users (< 10) on the site.
Thank you
Contributor
3730 Points
1427 Posts
Re: ADO.NET & MySQL - max_user_connections
Jul 13, 2020 07:59 AM|yij sun|LINK
Hi RageRiot,
Accroding to your description,as far as I think,you could set max_connections.Max_connections is the total connection limit and max_user_connections is the per user limit.
The value of max_user_connections must never exceed the value of max_connections.
The default limit of max_connections is 151,your max_user_connections value is 30 and the user cann't more than 5.
So,you could reset your max_connections.
Best regards,
Yijing Sun
Member
140 Points
590 Posts
Re: ADO.NET & MySQL - max_user_connections
Jul 13, 2020 08:02 AM|RageRiot|LINK
Unfortunately that isn't possible, as the limit is set by the hosting package.
In theory, this wouldn't fix the issue it would just mean it happens less often.
Contributor
3730 Points
1427 Posts
Re: ADO.NET & MySQL - max_user_connections
Jul 14, 2020 08:50 AM|yij sun|LINK
Hi RageRiot,
Accroding to your description,as far as I think,there are two solutions:
1.Close the connection object inside the Finally block.
2. Upgrade your account to higher hosting plan package for more connections.
Best regards,
Yijing Sun
Member
110 Points
115 Posts
Re: ADO.NET & MySQL - max_user_connections
Jul 15, 2020 05:57 AM|Masterpiece88|LINK
Hi RageRiot,
From the error message, it seems that your provider limit your connection usage. You may need to contact them, maybe time to upgrade to higher plan.