连接到MySQL时通信链接失败

Question

我使用JAVA JDBC驱动程序从MySQL中获取数据。

以下异常发生：

“通信链路故障的最后一个数据包成功接收 从服务器为14380298毫秒前的最后一个数据包发送 成功服务器是14380634毫秒以前。”。

此错误正在发生。这意味着如果我第一次运行这个没有发生异常，但第二次发生这种异常。然后在第三轮中，没有例外，第四场比赛再次出现异常。我使用TimerTask运行程序每四个小时。

程序和数据库之间的通信在没有例外的情况下正常运行。

代码如下：

public class CreatePO extends TimerTask { 

    public CreatePO() { 
     handler = new RFCHandler(); 
    } 

    @Override 
    public void run() { 
     // TODO Auto-generated method stub 
     try { 
      System.out.println("Run Create PO"); 
      getItemFromDB(); 
      sendDataToSap(); 
     } catch (Exception e) { 
      e.printStackTrace(); 
     } 
    } 

    private void getItemFromDB() { 
     // TODO Auto-generated method stub 
     System.out.println("Run get items from DB"); 
     Connection connection = null; 

     DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); 
     Date date = new Date(); 
     System.out.println(dateFormat.format(date)); 

     String queryOne = "SELECT reference_no, vendor_acc_no, date, mat_no," 
       + "po_qty, po_unit_measure, rate, order_price_unit, plant, user_name, email_id " 
       + "FROM tbl_po_data WHERE status <> 'X'"; 

     try { 
      pooler = DBPool_POSMS.getInstance(); 
      dataSource1 = pooler.getDataSource(); 
      System.out.println("PO pooler"); 
     } catch (Exception e) { 
      // TODO Auto-generated catch block 
      System.out.println(" : PO pooler error"); 
     } 

     try { 
      connection = dataSource1.getConnection(); 
      connection.setAutoCommit(false); 

      Statement st = connection.createStatement(); 
      ResultSet rs = st.executeQuery(queryOne); 

      int lineitem = 0; 

      rs.last(); 
      rcount = rs.getRow(); 
      rs.beforeFirst(); 
      System.out.println("rcount = " + rcount); 

      System.out.println("PO while loop"); 
      itemData = new Object[11][rcount]; 

      if (rcount > 0) { 

       while (rs.next()) { 
        itemData[0][lineitem] = rs.getString("reference_no"); 
        itemData[1][lineitem] = rs.getString("vendor_acc_no"); 
        itemData[2][lineitem] = rs.getDate("date"); 
        itemData[3][lineitem] = rs.getString("mat_no"); 
        itemData[4][lineitem] = rs.getString("po_qty"); 
        itemData[5][lineitem] = rs.getString("po_unit_measure"); 
        itemData[6][lineitem] = rs.getString("rate"); 
        itemData[7][lineitem] = rs.getString("order_price_unit"); 
        itemData[8][lineitem] = rs.getString("plant"); 
        itemData[9][lineitem] = rs.getString("user_name"); 
        itemData[10][lineitem] = rs.getString("email_id"); 
        lineitem = lineitem + 1; 
       } 

       rs.close(); 
       st.close(); 

      } 

     } catch (SQLException e1) { 
      // TODO Auto-generated catch block 
      System.out.println(" : PO while loop error"); 
      e1.printStackTrace(); 

     } finally { 
      try { 

       connection.close(); 
       System.out.println("Close connection one"); 
      } catch (SQLException e) { 
       // TODO Auto-generated catch block 
       System.out.println(" : Connection close error"); 
      } 
     } 

    } 

    private void sendDataToSap() { 

     System.out.println("Send Data to Sap"); 
     Table IT_LIST = null; 
     Table IT_RESPONSE = null; 

     try { 

      if (rcount > 0) { 



       } 

      } 

     } catch (Exception e) { 
      // TODO: handle exception 
      handler.releaseClient(); 
      System.out.println(e + " : Handler release error"); 
     } 

     finally { 
      rcount = 0; 
     } 

    } 

例外...

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 

The last packet successfully received from the server was 14,380,298 milliseconds ago. The last packet sent successfully to the server was 14,380,634 milliseconds ago. 
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) 
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) 
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) 
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989) 
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3743) 
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2506) 
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) 
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) 
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4842) 
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371) 
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328) 
    at CreatePO.getItemFromDB(CreatePO.java:84) 
    at CreatePO.run(CreatePO.java:53) 
    at java.util.TimerThread.mainLoop(Timer.java:555) 
    at java.util.TimerThread.run(Timer.java:505) 
Caused by: java.net.SocketException: Connection reset by peer: socket write error 
    at java.net.SocketOutputStream.socketWrite0(Native Method) 
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) 
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153) 
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) 
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) 
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3725) 
    ... 10 more 

Answer 1

的问题是，你的连接打开时间过长，是由MySQL服务器（关闭由指示“连接由对等体重置：套接字写入错误“堆栈跟踪中的）。您需要配置您的连接池，以关闭过时的连接，在交付之前验证（测试）连接或将其组合在一起。

您也可以尝试增加MySQL中的超时配置，但说实话，只需要关闭一个打开时间接近4小时（1400万毫秒）的连接即可。