32295

Database Poller : MySQLNonTransientConnectionException: Too many connections

Question:

I have written a WebService which is responsible to poll DB every 3 seconds and send the Latest Data

Unfortunately i am getting this exception in my console after some time .

Connection Failed! Check output console com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885) at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247) at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775) at com.mysql.jdbc.Connection.<init>(Connection.java:1555) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285) at java.sql.DriverManager.getConnection(DriverManager.java:579) at java.sql.DriverManager.getConnection(DriverManager.java:221) at com.serviceees.DBPollerService.getUpdates(DBPollerService.java:47) at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:168) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:70) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:279) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:86) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:136) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:74) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1357) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1289) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1239) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1229) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:420) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:497) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:684) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:291) at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:877) at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:594) at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1675) at java.lang.Thread.run(Thread.java:722)

<strong>This is my program</strong>

package com.serviceees; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import javax.ws.rs.GET; import javax.ws.rs.Path; import javax.ws.rs.Produces; import com.google.gson.Gson; import dto.Orders; @Path("/updates") public class DBPollerService { @GET @Produces("application/json") public String getUpdates() { String clientResponse = "Error"; ArrayList<Orders> newOrdersList = new ArrayList<Orders>(); Gson gson = new Gson(); try { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection connection = null; PreparedStatement preparedStatement = null; String selectSQL = "SELECT *\r\n" + "FROM Orders\r\n" + "INNER JOIN (\r\n" + " SELECT id, updated_at AS modified from Orders\r\n" + " UNION\r\n" + " SELECT id, created_at from Orders \r\n" + ") AS whatever ON whatever.id = Orders.id\r\n" + "ORDER BY whatever.modified DESC;\r\n" + ""; try { connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "", ""); preparedStatement = connection.prepareStatement(selectSQL); while (true) { try { ResultSet rs = preparedStatement.executeQuery(selectSQL); while (rs.next()) { Orders order = new Orders(); order.setId(rs.getInt(1)); order.setVendorName(rs.getString(2)); order.setItem(rs.getString(3)); newOrdersList.add(order); } clientResponse = "jsonCallback(["+gson.toJson(newOrdersList)+"])"; Thread.sleep(3000); // System.out.println("clientResponse"+clientResponse); return clientResponse; } catch (InterruptedException e) { e.printStackTrace(); } } } catch (SQLException e) { // System.out.println("Connection Failed! Check output console"); e.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } // clientResponse = gson.toJson(newOrdersList); return clientResponse; } }

Answer1:

Note: this has been <a href="https://stackoverflow.com/questions/11564000/java-mysql-too-many-connection" rel="nofollow">answered</a> numerous times.

You need to close your jdbc Connection, PreparedStatement, and ResultSet objects. This is most easily achieved by adding a finally block to the end of your try / catch blocks wrapping your calls to getConnection and prepareStatement:

try { connection = DriverManager.getConnection(...) ... } catch ... ... } finally { // Each of these needs wrapped in a try / catch block if(resultSet != null) { resultSet.close() } if(preparedStatement != null) { preparedStatement.close() } if(connection != null) { connection.close() } }

When working with JDBC, I'll usually write helper methods to handle the closing tasks. By placing them in a finally, you ensure they are always executed (whether you get an exception or return from within the try block.

Answer2:

You can make use of <strong>try with resources</strong> feature that will take care of closing the connection

try(Connection con=DriverManager.getConnection()){ //sql queries } catch(SQLException ex){ //error handling code }

Recommend