连接到MySQL时通信链路失败

Question

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

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服务器关闭（如stacktrace中的“连接由对等端重置：套接字写入错误”所指示的）。您需要将您的连接池配置为关闭太旧的连接、在分配之前验证（测试）连接或两者的组合。

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