java - 从 MySQL、JDBC 接收到的带有大型 ResultSet 的数据包乱序

Question

我有一个问题，我已经好几天没能解决了。所以，我试图用解决方案逐行列出一个大的结果集，在互联网上被多次引用，包括stackoverflow :

Statement stmt = readOnlyConn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);

stmt.setFetchSize(Integer.MIN_VALUE);

它似乎有效。至少有一段时间(它将这些行写到一个文本文件中)直到我得到下面的错误。我用谷歌搜索试图找出可能导致此问题的原因，尝试了一些 mysql 服务器调整，但没有任何效果。谁能帮帮我吗？每个人都用上述解决方案报告了出色的结果，但它似乎特别讨厌我。我将不胜感激任何帮助。

    java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 15 milliseconds ago.  The last packet sent successfully to the server was 6,239 milliseconds ago.
    at gblaster.main.lambda$main$1(main.java:134)
    at gblaster.main$$Lambda$3/792791759.accept(Unknown Source)
    at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1359)
    at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:580)
    at gblaster.main.main(main.java:124)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 15 milliseconds ago.  The last packet sent successfully to the server was 6,239 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:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3720)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4160)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:928)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2053)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3554)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:491)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3245)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2413)
    at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1500)
    at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:857)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2313)
    at db.mysql.GMySQLConnector.loadAllOrfsForGenomeId(GMySQLConnector.java:296)
    at gblaster.deploy.Deployer.unloadORFsForGenomeToFile(Deployer.java:124)
    at gblaster.main.lambda$main$1(main.java:128)
    ... 9 more
Caused by: java.io.IOException: Packets received out of order
    at com.mysql.jdbc.MysqlIO.readRemainingMultiPackets(MysqlIO.java:3782)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3707)
    ... 23 more

Answer 1

你的代码很难理解因为Integer.MIN_VALUE是负的，

stmt.setFetchSize(Integer.MIN_VALUE);

我想你会发现它更适合

stmt.setFetchSize(100); // <-- I also bet this works better then 1. 

最后，Statement.setFetchSize(int) javadoc 说(强调)，

Throws:

SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied.

所以你的代码应该得到一个SQLException。