locked
ADO.NET & MySQL - max_user_connections RRS feed

  • Question

  • User-195907812 posted

    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

    Sunday, July 12, 2020 4:03 PM

All replies

  • User1535942433 posted

    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

    Monday, July 13, 2020 7:59 AM
  • User-195907812 posted

    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.

    Monday, July 13, 2020 8:02 AM
  • User1535942433 posted

    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

    Tuesday, July 14, 2020 8:50 AM
  • User932909087 posted

    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. 

    Wednesday, July 15, 2020 5:57 AM