Communication to DB error in production

Question

I did some web app to deploy in production. But after several hours (looks like 8 hour), I can't communicate with DB.

Here is my Connect.java

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import javax.xml.ws.Response;

public class Connect {
    public static Connection connection = null;

    public static Connection getConnection() {
        if (connection != null) {
            return connection;
        } else {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost/DBPRK?autoReconnect=true&useUnicode=true&characterEncoding=utf8";
            String username = "prk";
            String password = "prk";

            try {
                Class.forName(driver);
                connection = DriverManager.getConnection(url, username, password);
            } catch (Exception e) {
                System.out.println("ERROR");
            }
            return connection;
        }
    }
}

and Here is error message that i got from tomcat log:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Software caused connection abort: socket write error

STACKTRACE:

java.net.SocketException: Software caused connection abort: socket write error
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2616)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2547)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1512)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2370)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183)
    at controller.GetAllClients.processRequest(GetAllClients.java:39)
    at controller.GetAllClients.doGet(GetAllClients.java:73)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    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:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:745)


** END NESTED EXCEPTION **


    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2634)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2547)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1512)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2370)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183)
    at controller.GetAllClients.processRequest(GetAllClients.java:39)
    at controller.GetAllClients.doGet(GetAllClients.java:73)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    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:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:745)

Btw I use pure jsp with no framework. Any help is very appreciate.

are you able connect from cmd or any other client to the DB, when the exception occurs? — Saravana, Jun 11 '16 at 04:50
Checked network connection to the machine on which your DB server is running? — ujulu, Jun 11 '16 at 06:11

score 1 · Answer 1 · edited Jun 11 '16 at 07:00

1

This problem may occur in following cases:

When we are trying to get too many DB connections.
When a DB connection is idle for a long time.

So, in your program try to close connection after usage, and get a new connection.

edited Jun 11 '16 at 07:00

Mark Rotteveel

100,966
191
140
197

answered Jun 11 '16 at 04:39

mallikarjun

88
1
7

Yes, the connection absolutely idle for long time. How can I get new connection to the DB? – Kim Jun 11 '16 at 14:43
Close DB connection after usage in every method, and whenever you need to communicate with DB again call Connect.getConnection() to get new connection. – mallikarjun Jun 11 '16 at 15:01

miiiii · Answer 2 · 2017-07-26T06:09:30.307

0

This is showing exception in your code at following line:

at controller.GetAllClients.processRequest(GetAllClients.java:39)
at controller.GetAllClients.doGet(GetAllClients.java:73)

The reason that might be causing this exception is that probably you are not closing connections once obtained and used.

In any computer system, there are a limited number of sockets available for connections and this strongly depends on your hardware specification and driver you are using for connection.

A connection to a database is something that has high cost in terms of resources and performance. This must be taken care every time when you think of connecting to a database.

Now assume a situation, where you have 5 bikes and 7 riders, all riders need a bike, so you can allocate 1 bike to each rider but only upto 5 riders, then what about rest 2 ?. So now only option is to wait for any two riders who complete their ride and return the bike so that you can allocate it to other twos... But in your case, first 5 took away the bike (got connection to database), completed their ride (executed their queries to database) but didn't return bike (are not closing the connection).

Likewise, in your case it is being allotted but not being released after usage, which in turn occupies all available socket and no socket is available to provide connection.

I assume you are catching the "connection" (returned by getConnection() method) in "con" variable.

Now,

con.close();

just only this needs to be written there.

And one more thing, currently all sockets are occupied you need to restart the database server (MySQL etc.) and project server (Tomcat etc.). And add above 1 line code everywhere after database usage is over.

edited Jul 26 '17 at 06:09

answered Jun 11 '16 at 06:52

miiiii

1,580
1
16
29

1

If you are not getting this, please let me know.. I'll try to help in different way... Have a nice Day... – miiiii Jun 11 '16 at 06:53
The fact you use a quote block, suggests you copied this from another source, please add a link to that source. If this is your own text, written specifically for this question, then don't use a quote block. – Mark Rotteveel Jun 11 '16 at 07:00
1

@MarkRotteveel Dude, I'm new on StackOverflow.com so if this is the way to write that you are saying, I'll take care for next time . AND THOSE ARE MY TEXT, WHILE TYPING Stackoverflow.com Suggest such things to grab focus of readers to read Important Points Quickly. – miiiii Jun 11 '16 at 07:08
1

@MarkRotteveel I didn't believe in COPY. hahaha... Thanks Dude.. – miiiii Jun 11 '16 at 07:10
Quote blocks are intended to convey that you sourced the text from elsewhere, it is not intended as a highlight. Please don't abuse it as such. As a second point, please use capitals only at the start of sentences and for names; Using them mid-sentence reduces readability. – Mark Rotteveel Jun 11 '16 at 07:10
@MarkRotteveel sure, sure... thanks for guiding me.. I'll be careful from next time, Thanks again... – miiiii Jun 11 '16 at 07:12
No problem, I edited your post to address these points. – Mark Rotteveel Jun 11 '16 at 07:14
@MarkRotteveel Oh thanks... It looks nice.. Thanks .. Keep guiding me.. have a nice day..!! – miiiii Jun 11 '16 at 07:17
1

@ManojShukla I already tried conn.close(). But it cause other problem. Whenk I did that, I got error message stated that "No operation allowed after connection closed" – Kim Jun 11 '16 at 14:44
@Kim please share your code where you catch connection returned from getConnection(); method. – miiiii Jun 11 '16 at 18:04
@Kim that method which is calling getConnection(); is required to track the new error.. – miiiii Jun 11 '16 at 18:05
@Kim also I was expecting this new error prior to your comment, because it is a basic mistake we all did at the time of learning, don't worry .. – miiiii Jun 11 '16 at 18:14

score 0 · Answer 3 · answered Jun 11 '16 at 09:28

0

Buddy, add port number (3306) in your connection in the String Url

answered Jun 11 '16 at 09:28

bademba

237
4
14

Communication to DB error in production

3 Answers3