exceeded the 'max_user_connections' resource

Question

exceeded the 'max_user_connections' resource

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I'm not sure what the reason for the error I'm having is but it looks like it's something about the open connections in the database, where an error is occurring if the application tries to create more connections than the limit. I think this is the reason for the error.

I'm closing every Connection I open for an interaction (insert / update / delete / select).

I've read some topics on Stack Overflow, but none of those based on C # has solved this problem.

Here is the connection code for my db and an interaction as an example:

Connection Class:

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

       conectar.Open();
       return conectar;           
     }  
}

Interaction:

        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:

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

mysql c#

asked by anonymous 03.02.2017 / 20:30

2 answers

How to add a MaterialDesignInXamlToolkit controller with C # in XAML Getting values in String from ArrayListObject

score 3 · Answer 1

Assuming you are using 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
        }

This is the way to do the right checks on every place on your system to use.

score 1 · Answer 2

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

There are actually two parameters to change:

max_connections sets the maximum number of connections that can exist simultaneously.
max_user_connections establishes the maximum number of concurrent connections that can exist for each user, this is the maximum connection count per USER of MySQL.

If you have access to my.cnf just change the parameters as you want, be aware that CPU usage and mainly RAM will increase according to the largest number of concurrent requests, or else you can define " 99999999 "There will be hardware limits.

Verify by giving SHOW FULL PROCESSLIST in MySQL to see the connections that are open and what is being done.

In addition, in order to not have to restart MySQL for the new parameters to be used, modify using SET GLOBAL .

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

For this to work for the MySQL user, who will execute this query , he needs the global privilege. This change is not permanent, whenever MySQL hangs it will return to the value set in my.cnf , so changing my.cnf is necessary.