Understanding & Resolving “Too many connections” Exception

Understanding & Resolving “Too many connections” Exception:

 

Exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: “Too many connections”

Issue Fix:

Close the open connections properly, because too many open connection error indicates too many connections are opened but not closed.

Existing Code which has the issue:

 

In the below code, finally block still has the code snippet to close the too many connections exception. But I did very badly, closing the connection by opening it newly in finally block instead of the one used inside the try block.

[java]
public List<CouponDBPojo> getCouponzForPublish(int start, int rowLimit) {
// int start = 10;
// int rowLimit =10;
List<CouponDBPojo> couponLst = new ArrayList<CouponDBPojo>();
ResultSet rs = null;
Statement st = null;

try {
// our SQL SELECT query.
// if you only need a few columns, specify them by name instead of using “*”
//String query = “SELECT * FROM couponz where coupon_type=’C’ and is_published=’N’ limit ” + start + “, ” + rowLimit + “”;
String query =” select * from couponz where coupon_shop_identity is not null and coupon_shop_identity not REGEXP ‘^[0-9]+$’ and coupon_type=’C’ and is_published=’N’ limit ” + start + “, ” + rowLimit + “”;

// create the java statement
st = getDBConnection().createStatement();

// execute the query, and get a java resultset
rs = st.executeQuery(query);

// iterate through the java resultset
while (rs.next()) {
CouponDBPojo couponDBPojo = new CouponDBPojo();
couponDBPojo.setCoupon_id(rs.getInt(“coupon_id”));
couponDBPojo.setCouponTitle(rs.getString(“coupon_title”));
couponDBPojo.setCouponCategories(rs.getString(“coupon_category”));
couponDBPojo.setCouponTags(rs.getString(“coupon_tags”));
couponDBPojo.setCouponType(rs.getString(“coupon_type”));
couponDBPojo.setCouponCode(rs.getString(“coupon_code”));
couponDBPojo.setCouponDesc(rs.getString(“coupon_desc”));
couponDBPojo.setCouponShopName(rs.getString(“coupon_shop_identity”));
couponDBPojo.setAffiliateURL(rs.getString(“affiliate_url”));
couponLst.add(couponDBPojo);
}

} catch (Exception e) {
System.err.println(“Got an exception! “);
System.err.println(e.getMessage());
}
finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) { /* ignored */}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) { /* ignored */}
}
if (getDBConnection() != null) {
try {
getDBConnection().close();
} catch (SQLException e) { /* ignored */}
}
}
return couponLst;
}

public static Connection getDBConnection() {
// create our mysql database connection
String myDriver = “org.gjt.mm.mysql.Driver”;
String myUrl = “jdbc:mysql://localhost/czc”;
Connection conn = null;
try {
Class.forName(myDriver);
conn = DriverManager.getConnection(myUrl, “root”, “naveen”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return conn;
}

[/java]

 

Fixed Code: [Resolved: “Too many connections” Exception ]

• getDBConnection() is opened and closed in the above code, which is wrong and need to close the connection which actually created and used inside the try block.
• Also, Note if you put finally block inside getDBConnection() and try to close then you will face this exception “No operations allowed after conection closed.”.

[java]
public List<CouponDBPojo> getCouponzForPublish(int start, int rowLimit) {
// int start = 10;
// int rowLimit =10;
List<CouponDBPojo> couponLst = new ArrayList<CouponDBPojo>();
ResultSet rs = null;
Statement st = null;
Connection conn = null;
try {
// our SQL SELECT query.
// if you only need a few columns, specify them by name instead of using “*”
//String query = “SELECT * FROM couponz where coupon_type=’C’ and is_published=’N’ limit ” + start + “, ” + rowLimit + “”;
String query =” select * from couponz where coupon_shop_identity is not null and coupon_shop_identity not REGEXP ‘^[0-9]+$’ and coupon_type=’C’ and is_published=’N’ limit ” + start + “, ” + rowLimit + “”;

// create the java statement
conn= getDBConnection();
st =conn.createStatement();

// execute the query, and get a java resultset
rs = st.executeQuery(query);

// iterate through the java resultset
while (rs.next()) {
CouponDBPojo couponDBPojo = new CouponDBPojo();
couponDBPojo.setCoupon_id(rs.getInt(“coupon_id”));
couponDBPojo.setCouponTitle(rs.getString(“coupon_title”));
couponDBPojo.setCouponCategories(rs.getString(“coupon_category”));
couponDBPojo.setCouponTags(rs.getString(“coupon_tags”));
couponDBPojo.setCouponType(rs.getString(“coupon_type”));
couponDBPojo.setCouponCode(rs.getString(“coupon_code”));
couponDBPojo.setCouponDesc(rs.getString(“coupon_desc”));
couponDBPojo.setCouponShopName(rs.getString(“coupon_shop_identity”));
couponDBPojo.setAffiliateURL(rs.getString(“affiliate_url”));
couponLst.add(couponDBPojo);
}

} catch (Exception e) {
System.err.println(“Got an exception! “);
System.err.println(e.getMessage());
}
finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) { /* ignored */}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) { /* ignored */}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) { /* ignored */}
}
}
return couponLst;
}

public static Connection getDBConnection() {
// create our mysql database connection
String myDriver = “org.gjt.mm.mysql.Driver”;
String myUrl = “jdbc:mysql://localhost/czc”;
Connection conn = null;
try {
Class.forName(myDriver);
conn = DriverManager.getConnection(myUrl, “root”, “naveen”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return conn;
}
[/java]

Feel free to provide your comments below to improve this blog better.