14 Replies Latest reply on Sep 11, 2010 7:35 PM by jschellSomeoneStoleMyAlias

    communication link failure exception in database connection webservice

    843859

      Hi,

      All i am getting following exception.I want to reconnect to database from web service automatically once connection timeout in tomcat serer.This is my error log:

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

      ** BEGIN NESTED EXCEPTION **

      java.net.SocketException
      MESSAGE: Connection reset

      STACKTRACE:

      java.net.SocketException: Connection reset
      at java.net.SocketInputStream.read(SocketInputStream.java:168)
      at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
      at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
      at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1862)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2256)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2756)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536)
      at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1159)
      at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:684)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1049)
      at testdbdiscoverorg.TestDbDiscover.getSearchORbycomp(TestDbDiscover.java:102)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:194)
      at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:102)
      at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
      at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
      at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:173)
      at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:167)
      at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:142)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      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.Http11AprProcessor.process(Http11AprProcessor.java:859)
      at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
      at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1555)
      at java.lang.Thread.run(Thread.java:619)


      ** END NESTED EXCEPTION **



      Last packet sent to the server was 170406 ms ago.
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2467)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2756)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536)
      at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1159)
      at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:684)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1049)
      at testdbdiscoverorg.TestDbDiscover.getSearchORbycomp(TestDbDiscover.java:102)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:194)
      at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:102)
      at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
      at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
      at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:173)
      at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:167)
      at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:142)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      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.Http11AprProcessor.process(Http11AprProcessor.java:859)
      at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
      at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1555)
      at java.lang.Thread.run(Thread.java:619)

        • 1. Re: communication link failure exception in database connection webservice
          forumKid2
          Share your code that does the reconnect and point out the problematic area. Otherwise everyone here is in the dark.
          • 2. Re: communication link failure exception in database connection webservice
            843859
             try {
                    Class.forName("com.mysql.jdbc.Driver");
                     con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/iscoverorg?autoReconnect=true","root","replace");
            
                }
                catch (Exception e) {
                        e.printStackTrace();
                    }
            Hi all,

            This is how i am making connection.Please ask me the queries you need further to come towards bright part of my doubt.Thanks for helping me with my issue.
            • 3. Re: communication link failure exception in database connection webservice
              forumKid2
              So when you get this error "java.net.SocketException MESSAGE: Connection reset", what do you try to do then? Or is that the question on what you should do when that happens?
              • 4. Re: communication link failure exception in database connection webservice
                796311
                Is this you issue?
                1. You make connection and do some work.
                2. Later you try to do some more work and get this error?
                • 5. Re: communication link failure exception in database connection webservice
                  jschellSomeoneStoleMyAlias
                  SHAMUDRIGA wrote:
                  All i am getting following exception.I want to reconnect to database from web service automatically once connection timeout in tomcat serer.
                  Wrong.

                  That is specifically what you do not want to do.

                  You should be doing ONE of the following.
                  1. Use a connection pool. tomcat has one so use it. Get a connection from the pool, use it immediately and return it immediately (close it) when done. This of course means you must set the pool up.
                  2. Create a connection using the jdbc driver. Use it immediately and close it immediately when done.

                  If you want to keep the connection open then you do the first. If you don't want to keep the connection open you do the second.
                  • 6. Re: communication link failure exception in database connection webservice
                    843859
                    This might be of some interest here

                    [http://darksleep.com/notablog/articles/Tomcat_Connection_Pooling|http://darksleep.com/notablog/articles/Tomcat_Connection_Pooling]
                    • 7. Re: communication link failure exception in database connection webservice
                      843859
                      Hi ,

                      @ForumKid2: Yes my question is after i am getting this error.How to rectify or correct the error?

                      @darted:Yes what u said is correct once i connect to MYSQL server and run my application.MYSQL server time out is 1 hour.So after an hour when i try to connect i am getting this error.But increasing time out in MYSQL we cant do.As server is external server.So within Axis webservice how can we overcome this error?This is my doubt.

                      @jschell:Thanks For helping me.This is what i need.Can u detail me out like how can i do this in java progrmataically for closing JDBC connection.Or for tomcat pooling.

                      @DynamicBasics :Thanks.But still i didnt get clear picture/or vision
                      • 8. Re: communication link failure exception in database connection webservice
                        843859
                        Hi all,


                        I tried even by closing JDBC connection with con.close();.But no progress.I tried documentation for tomcat pooling in java axis web services(Which i am working with).But i find no blog or anything for describing how to configure tomcat pooling with axis webservices.So please say me how can i do for tomcat pooling in java programmatic.:-?
                        • 9. Re: communication link failure exception in database connection webservice
                          forumKid2
                          Why don't you start out with this:

                          1) Get a connection and grab your data
                          2) Close the connection

                          When you need more data do #1 and #2 again. Just keep doing that. It's not efficient as a pool is the way to go, but get this working first. Then you can easily migrate to a pool.

                          So after you close the connection. If you need to get more data, open the connection first, get the data, close the connection.
                          • 10. Re: communication link failure exception in database connection webservice
                            796311
                            SHAMUDRIGA wrote:
                            Hi all,


                            I tried even by closing JDBC connection with con.close();.But no progress.I tried documentation for tomcat pooling in java axis web services(Which i am working with).But i find no blog or anything for describing how to configure tomcat pooling with axis webservices.So please say me how can i do for tomcat pooling in java programmatic.:-?
                            When using a connection pool close returns the connection to the pool not physically close the connection. One easy thing to do is just retry on error once to manage this message. Another is a 'keep alive' on the connection definition for the pool. Many factors enter into what is best (volume of transactions, frequency, etc.). Even better is put a timeout on your idle connections so that they are slightly less than the server timeout.

                            As far as using a JNDI connection pool, you either inject it with IOC or you explicitly look it up in your handler for the web service. For Tomcat I believe you must specify the JNDI resource as a resource for the service.
                            • 11. Re: communication link failure exception in database connection webservice
                              843859
                              SHAMUDRIGA wrote:
                              Hi all,


                              I tried even by closing JDBC connection with con.close(); But no progress.
                              I think you are trying to use a connection that is already closed. Are you sure this is not the case? And why would the connect be open for one hour (you configured time out in this case).
                              • 12. Re: communication link failure exception in database connection webservice
                                843859
                                Hi all,

                                Below is my coding which i get exception .Still my response is same exception.So only as u all said i thought of moving to pool.Please can anybody explain me pool configuration in Java Apache Axis web services.
                                Else say me alternate way i can do to get out of exception.But i thank u all For helping me.It is so great attitude :-)

                                public class Main {
                                 Connection con;
                                   
                                  public Main(){
                                     try {
                                        Class.forName("com.mysql.jdbc.Driver");
                                         con = (Connection) DriverManager.getConnection("jdbc:mysql://iscoverydb.com/iscoverorg?autoReconnect=true","root","recover");
                                    }
                                    catch (Exception e) {
                                            e.printStackTrace();
                                        }
                                    
                                  }
                                
                                
                                  public void connection(){
                                      try {
                                        Class.forName("com.mysql.jdbc.Driver");
                                         con = (Connection) DriverManager.getConnection("jdbc:mysql://iscoverydb.com/iscoverorg?autoReconnect=true","root","recover");
                                    }
                                    catch (Exception e) {
                                            e.printStackTrace();
                                        }
                                  }
                                  public  String getAuthentication (String username,String password) {
                                                //TODO write your implementation code here:
                                                 String id = null;
                                        try {
                                            if (con.isClosed()) {
                                                connection();
                                            }
                                        } catch (SQLException ex) {
                                            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                                        }
                                
                                        try{
                                         String simpleProc = "SELECT id FROM users WHERE handle = ? AND password=? LIMIT 0,1";
                                           java.sql.PreparedStatement cs = con.prepareStatement(simpleProc);
                                           cs.setString(1, username);
                                           cs.setString(2, password);
                                            java.sql.ResultSet  rs =  cs.executeQuery();
                                                     rs.last();
                                             int i=  rs.getRow();
                                             rs.first();
                                           System.out.println(i);
                                           System.out.println(rs.getString(1));
                                              id=rs.getString(1);
                                              Authentication s=new Authentication(rs.getString(1));
                                               rs.close();
                                            con.close();
                                            }catch ( Exception e) {
                                            e.printStackTrace();
                                            System.out.print(e);
                                        }
                                        return id;
                                    }
                                Edited by: SHAMUDRIGA on Sep 9, 2010 11:47 PM
                                • 13. Re: communication link failure exception in database connection webservice
                                  EJP
                                  Don't use DriverManager. Look up how to use javax.sql.DataSource. That way you will get pooled connections, and connection outages become someone else's problem.
                                  • 14. Re: communication link failure exception in database connection webservice
                                    jschellSomeoneStoleMyAlias
                                    SHAMUDRIGA wrote:
                                    Hi all,

                                    Below is my coding which i get exception .Still my response is same exception.So only as u all said i thought of moving to pool.
                                    You use the pool because you want to keep the connection open.
                                    You do not use the pool to 'solve' your problem.

                                    You first need to understand that connections, statements and result sets are resources.
                                    They must be closed. All the time. You are not doing that. You are not closing the statements and result sets at all. You are not closing the connection when exceptions occur.

                                    And all of that is true whether you use a pool or not.