mysql数据库大数据量jdbc查询报错: Can not read response from server

Question

jdbc连接mysql数据库查询数据, 要查询的表中有8000万条数据, 查询报错Can not read response from server. Expected to read 62 bytes, read 26 bytes before connection was unexpectedly lost. 而数据量在1000万条查询不会报错。这个断开连接的原因会是什么呢？我抓包发现是数据库主机Sending data后发送的FIN包,但是Java程序还没有获取到结果集

代码

        String driverName = "com.mysql.cj.jdbc.Driver";
        String username = "root";
        String password = "password";
        String jdbcURL = "jdbc:mysql://10.1.1.1:3306/person?useSSL=false&requireSSL=false";

        Class.forName(driverName);

        Connection connection = DriverManager.getConnection(jdbcURL, username, password);
        if (null == connection) {
            System.out.println("get connection failed");
            return;
        }

        String sql = "SELECT name,password,id FROM worker";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()) {
            String name = resultSet.getString(1);
            String password_str = resultSet.getString(2);
            int id = resultSet.getInt(3);
        }
        System.out.println("select over");
        resultSet.close();
        preparedStatement.close();
        connection.close();

报错信息

Exception in thread "main" java.sql.SQLException: Can not read response from server. Expected to read 62 bytes, read 26 bytes before connection was unexpectedly lost.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
        at com.demo.SelectOriginMysql.main(SelectOriginMysql.java:22)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 62 bytes, read 26 bytes before connection was unexpectedly lost.
        at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67)
        at com.mysql.cj.protocol.a.SimplePacketReader.readMessageLocal(SimplePacketReader.java:137)
        at com.mysql.cj.protocol.a.SimplePacketReader.readMessage(SimplePacketReader.java:102)
        at com.mysql.cj.protocol.a.SimplePacketReader.readMessage(SimplePacketReader.java:45)
        at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readMessage(TimeTrackingPacketReader.java:62)
        at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readMessage(TimeTrackingPacketReader.java:41)
        at com.mysql.cj.protocol.a.MultiPacketReader.readMessage(MultiPacketReader.java:66)
        at com.mysql.cj.protocol.a.MultiPacketReader.readMessage(MultiPacketReader.java:44)
        at com.mysql.cj.protocol.a.ResultsetRowReader.read(ResultsetRowReader.java:75)
        at com.mysql.cj.protocol.a.ResultsetRowReader.read(ResultsetRowReader.java:42)
        at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1651)
        at com.mysql.cj.protocol.a.TextResultsetReader.read(TextResultsetReader.java:87)
        at com.mysql.cj.protocol.a.TextResultsetReader.read(TextResultsetReader.java:48)
        at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1664)
        at com.mysql.cj.protocol.a.NativeProtocol.readAllResults(NativeProtocol.java:1718)
        at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1064)
        at com.mysql.cj.NativeSession.execSQL(NativeSession.java:665)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:893)
        ... 2 more

Answer 1

数据量太大，你应该分页，分批去查询。