exceeded the 'max_user_connections' Resource

Question

I’m not sure what the reason for the error I’m having but it seems that it is something related to open connections in the bank, where error is occurring if the application tries to create more connections than the limit. I believe this is the reason for the mistake.

I am closing all Connection I open for an interaction (Insert/update/delete/select).

I’ve read a few topics about this here on Stack Overflow, but no C# based that solved this problem.

Follow my connection code db and an interaction as an example:

Class for Connection:

 public class ClassConexao  
    {
     public static MySqlConnection ObterConexao()
     {           
       MySqlConnection conectar = new MySqlConnection("server=ENDEREÇO; database=NOME; Uid=USER; pwd=****");

       conectar.Open();
       return conectar;           
     }  
}

Interacting :

        try
        {   //ABRINDO CONEXAO01
            MySqlConnection conexão01= ClassConexao.ObterConexao();

            MySqlCommand _comandoSel = new MySqlCommand(String.Format("SELECT Column1, Column2 FROM tableA WHERE Column1 = " + "500" + ""), conexão01);
            MySqlDataReader 01_reader = _comando01.ExecuteReader();
            Sel_reader.Read();

            textbox1.Text = 01_reader.GetString(0);
            textbox2.Text = 01_reader.GetString(1);

            //FECHANDO CONEXAO01
            conexão01.Close();

        catch (Exception error)
        {
            MessageBox.Show(String.Format("Algo está errado com a operação! {0}", error.Message));
            return;
        }

Error message returned from:

Is there any way to increase this limit? Or other solution that does not limit connections?

Answer 1

Assuming you’re using the Mysqlconnection then you should be able to do so:

try
   {   //ABRINDO CONEXAO01
       MySqlConnection conexão01= ClassConexao.ObterConexao();

       MySqlCommand _comandoSel = new MySqlCommand(String.Format("SELECT Column1, Column2 FROM tableA WHERE Column1 = " + "500" + ""), conexão01);
       MySqlDataReader 01_reader = _comando01.ExecuteReader();
       Sel_reader.Read();

       textbox1.Text = 01_reader.GetString(0);
       textbox2.Text = 01_reader.GetString(1);

       //FECHANDO CONEXAO01
       conexão01.Close();

       catch (Exception error)
       {
          MessageBox.Show(String.Format("Algo está errado com a operação! {0}", error.Message));
          return;
       }
        finally
       {
           conexão01.Dispose(); // << AQUI VOCÊ LIBERA TODOS OS RECURSOS USADOS
        }

So it’s more accurate, do the proper checks at each place on your system that you use.

Answer 2

If you want to expand the connection limit, as mentioned, the limit should be expanded directly on my.cnf, if you have access to this file.

Actually there are two parameters to be changed:

• max_connections establishes the maximum connections that can exist simultaneously.

• max_user_connections establishes the maximum number of simultaneous connections that can exist for each user, that is the maximum number of connection for each USER mysql.

If you have access to my.cnf simply change the parameters as you wish, be aware that the use of CPU and mainly RAM will increase as the number of simultaneous requests, that is, as much as you can define "99999999" there will be hardware limits.

Check by giving a SHOW FULL PROCESSLIST in Mysql to see which connections are open and what is being done.

---

Also, to not have to restart Mysql for the new parameters to be used, modify using the SET GLOBAL.

SET GLOBAL max_user_connections = 500;
SET GLOBAL max_connections = 500;

For this to work the Mysql user, which will execute this query, need global privilege. This change is not permanent, whenever Mysql shuts down will return to the value set in my.cnf, therefore amend the my.cnf it is necessary.