7 Replies Latest reply on Oct 25, 2005 11:58 AM by Avi Abrami

    Connection reset by peer

    user457523
      Hi

      I have j2re1.4.2_08 and Oracle 9.2
      When I tried to update database with huge data I got following exception:

      java.sql.SQLException: Io exception: Connection reset by peer: socket write error
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
           at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2061)
           at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
           at edu.bnmit.BookDBUpdateThread.run(BookDBUpdateThread.java:80)
           at edu.bnmit.BookDBUpdateThread.update(BookDBUpdateThread.java:46)
           at java.lang.Thread.run(null:-1)
      method used to update database is:
      void update(Books b[]){
      Connection dbConnection;
      PreparedStatement stmt;
      try{
      dbConnection = DriverManager.getConnection(
      "...",
      "...",
      "...");
      dbConnection.setAutoCommit(false);
      String sqlStmt = "UPDATE Books SET " +
      "bookName=?, bookAuthor=?" +
      "WHERE bookId=?";
      stmt = dbConnection.prepareStatement(sqlStmt);
      for(int i = 0; i < b.length; i++){
      stmt.setString(1,b.bookName);
      stmt.setString(2,b[i].bookAuthor);
      stmt.setInt(3,b[i].bookId);
      stmt.executeUpdate();
      }
      dbConnection.commit();
      stmt.close();
      dbConnection.close();
      }catch(SQLException e){
      System.err.println("Error While Updating Table");
      String s = e.toString();
      StackTraceElement trace[] = e.getStackTrace();
      for(int j = 0; j < trace.length; j++){
      s += "\n\tat "
      + trace[j].getClassName() + trace[j].getMethodName()
      + "(" + trace[j].getFileName()
      + ": " + trace[j].getLineNumber() + ")";
      }
      System.err.println(s);
      }
      }

      Orcale server is distently placed. I get this exception when I send 10,000+ records to update.
      Can anyone help me.

      Thanks
        • 1. Re: Connection reset by peer
          JustinCave
          So, you get this error when b in your FOR loop is 10,000+?

          It appears that there is a network problem. If the Oracle server is "distantly placed", I'm guessing that there are a variety of switches, routers, firewalls, etc between the app server and the database. One (or more) of them probably have problems keeping a connection open long enough to process 10,000 single-row UPDATE statements.

          Your networking group probably has tools to trace where in the network the problem is occurring.

          Justin
          Distributed Database Consulting, Inc.
          http://www.ddbcinc.com/askDDBC
          • 2. Re: Connection reset by peer
            user457523
            Hi

            Now I am getting even for 1000+ is it related to network connection?
            But network admin tells network is having good and stable bandwidth.

            Thanks
            • 3. Re: Connection reset by peer
              JustinCave
              This looks like a network issue, yes. Something is causing your connection to be dropped.

              I would tend to focus on any Oracle-specific connectivity that has been set up-- firewalls, for example, have a tendency to break connections that are open too long. Also be aware that connections to Oracle databases tend to be much longer-lived than other types of connections (i.e. HTTP), so a network that has no problem handling lots of small HTTP requests and responses might have a problem with Oracle connections that are kept open for hours or days.

              Justin
              Distributed Database Consulting, Inc.
              http://www.ddbcinc.com/askDDBC
              • 4. Re: Connection reset by peer
                user457523
                Hi,

                I placed traces to find what time it is taking to update. It was taking average of 30 min. per 100 updated.

                I also tried batch commit where batch size of 200 still I was getting exception. I reduced batch size to 50 even then I am getting exception. Then I tried to handle this exception: if exception occurs then I re-connect, create prepare statements and start from beginning of current batch. It was working fine but once if there exception, then in all comming batchs was throwing exception. So if batch size is too large it is eating time. So there is some other way to handle this.

                If it N/W issue, is there any thing I can do to tackle?

                Thanks & Regards
                Ram
                • 5. Re: Connection reset by peer
                  Avi Abrami
                  Ram,
                  I don't have an answer for you, just a suggestion. From your post, I see you are creating and populating a 10,000+ array. I'm thinking that these 10,000+ records must be populated from some data source -- like a file or a database query or something similar. If that's the case, then I think perhaps a java program is not the best tool for the job. Have you considered "SQL*Loader", for example?

                  Sometimes it's better to ask, "What's the best way to solve my problem (of updating 10,000+ rows in a database table)?", rather than asking, "Why doesn't my solution work?".

                  Good Luck,
                  Avi.
                  • 6. Re: Connection reset by peer
                    user457523
                    Hi Abramia,

                    I appreciate your suggestion. This product will be used by Liberians who have little knowledge on SQL or Database and I need to provide them suitable interface to do bulk update, so can we use SQL* loader from java.

                    Thanks
                    Ram
                    • 7. Re: Connection reset by peer
                      Avi Abrami
                      Ram,
                      Although I haven't tried it, I imagine you can run SQL*Loader from a java class.
                      Another suggestion may be to write a PL/SQL procedure to do the bulk update and invoke the procedure from your java code.

                      The best way would depend on how your librarians intend to do bulk updates. Do they enter the changes, one book at a time, or do they want to update the same thing for a whole load of books?

                      Good Luck,
                      Avi.