Error: Data source rejected establishment of connection, message from server: "Too many connections"

Question

I created an application that writes data to the database every 5 minutes.

However after some time this error appears:

Error: Data source rejected establishment of connection, message from server: "Too many connections"

I've been searching around and tells you to close the connection to the database after every request side.

I tried this:

conexao.close();

But it gives me this error:

No operations allowed after conection closed.

I apologize if the question is not well formulated.

Thanks for the help

---------------------What I tried but didn't work---------------------------

Add

finally{ 
    if(conexao!=null)
   conexao.close();
   }

---

  Class.forName("com.mysql.jdbc.Driver");
        Connection conexao = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/bdTeste", "root", "root");
        Statement stm = conexao.createStatement();
        BufferedReader reader = new BufferedReader(new FileReader("C:/Users/RPR1BRG/Desktop/test.txt"));

        String dados[] = new String[6];
        String linha = reader.readLine();

        while (linha != null) {

            StringTokenizer st = new StringTokenizer(linha, ";\"");

            dados[0] = st.nextToken();
            dados[1] = st.nextToken(); 
            dados[2] = st.nextToken();
            dados[3] = st.nextToken();
            dados[4] = st.nextToken();
            dados[5] = st.nextToken();

             DateFormat dateFormat = new SimpleDateFormat("d-M-yy");

    PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement("replace into registos"
    + " (data_registo, hora_registo, IdSensor, Temperatura, Humidade, pt_orvalho) values (?,?,?,?,?,?)");
                    try {
                        stmt.setDate(1, new java.sql.Date(dateFormat.parse(dados[0]).getTime()));
                        stmt.setString(2, dados[1]);
                        stmt.setString(3, dados[2]);
                        stmt.setString(4, dados[3]);
                        stmt.setString(5, dados[4]);
                        stmt.setString(6, dados[5]);

                    } catch (java.text.ParseException ex) {
                        Exceptions.printStackTrace(ex);
                    }


stmt.executeUpdate();

            linha = reader.readLine();
            PrintWriter writer = new PrintWriter("C:/Users/RPR1BRG/Desktop/test.txt"); 
            writer.print("");
            writer.close();
            Verifica();
            }

    } catch (ClassNotFoundException | SQLException | IOException e) {

        System.err.println("Erro: " + e.getMessage());

    }finally{ 
    if(conexao!=null)
   conexao.close();
   }

Answer 1

This kind of problem arises when you are NOT properly closing the connection after usage.

Please use finally block after catch to close the connections appropriately. This is because to ensure that the connection gets closed properly even when there is an unexpected exception or error. Please note that statements inside finally block gets executed always. it allows the programmer to avoid having cleanup code accidentally bypassed by a return, continue, or break

Note: If the JVM exits while the try or catch code is being executed, then the finally block may not execute. Likewise, if the thread executing the try or catch code is interrupted or killed, the finally block may not execute even though the application as a whole continues.

As you have asked in comment, I have added the code sample to demonstrate practically!

Connection con = null
try{
 //Establishing connection to datasource
 con = DBConnection.getConnection();
 //perform DB operations
 ...
 ...
 ...
}catch(SQLException sqlEx){
 /*To catch any SQLException thrown during DB 
  *Operations and continue processing like sending alert to admin
  *that exception occurred.
  */
}finally{
 /*This block should be added to your code
  * You need to release the resources like connections
  */
 if(con!=null)
  con.close();
}

Please note that the declaration of Connection variable should be in proper scope to close it in finally block.

Hope this helps!

Answer 2

This may be because of the configured max_connections is not suitable with the Connection Pool size set in JDBC or number of connections you open against DB.

to check number of max_connections in mysql:

show variables like 'max_connections';

make sure you have proper value of opened connections with the DB Max connections.