Streaming query shardingsphere-proxy always interrupted java.sql.SQLException: null

[GPF199541]

Bug Report

streaming query always interrupted

ShardingSphere-Proxy version:5.4.1
Java 1.8

ShardingSphere-Proxy ERROR

java.sql.SQLException: null
        at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.close(DatabaseConnector.java:401)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.close(MySQLComQueryPacketExecutor.java:128)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand$original$OA8Nqe3z(CommandExecutorTask.java:139)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand$original$OA8Nqe3z$accessor$Vbh5tAMm(CommandExecutorTask.java)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask$auxiliary$7v0fw8rO.call(Unknown Source)
        at org.apache.shardingsphere.agent.core.advisor.executor.type.InstanceMethodAdviceExecutor.advice(InstanceMethodAdviceExecutor.java:72)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:121)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run$original$OA8Nqe3z(CommandExecutorTask.java:78)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run$original$OA8Nqe3z$accessor$Vbh5tAMm(CommandExecutorTask.java)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask$auxiliary$DzxI85sW.call(Unknown Source)
        at org.apache.shardingsphere.agent.core.advisor.executor.type.InstanceMethodAdviceExecutor.advice(InstanceMethodAdviceExecutor.java:72)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java)
        at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

---

ShardingSphere-Proxy-CONFIG:

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: test54101
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500

authority:
  users:
    - user: root@%
      password: root
  privilege:
    type: ALL_PERMITTED
sqlParser:
  sqlCommentParseEnabled: false
  sqlStatementCache:
    initialCapacity: 2000
    maximumSize: 65535
  parseTreeCache:
    initialCapacity: 128
    maximumSize: 1024

logging:
  loggers:
  - loggerName: ShardingSphere-SQL
    additivity: true
    level: DEBUG
    props:
      enable: true

sqlFederation:
  sqlFederationEnabled: false
  executionPlanCache:
    initialCapacity: 2000
    maximumSize: 65535

props:
  system-log-level: DEBUG
  max-connections-size-per-query: 1
  kernel-executor-size: 1 
  proxy-frontend-flush-threshold: 1
  sql-show: true
  check-table-metadata-enabled: false
  proxy-backend-query-fetch-size: -1
  proxy-frontend-executor-size: 0
  proxy-frontend-max-connections: 0
  proxy-default-port: 3307
  proxy-netty-backlog: 1024
  cdc-server-port: 33071
  proxy-frontend-ssl-enabled: false
  proxy-frontend-ssl-cipher: ''
  proxy-frontend-ssl-version: TLSv1.2,TLSv1.3

---

JAVA-CODE

public class tools {

    private static Driver driver;

    static {
        try {
            driver = new Driver();
        } catch (Exception e) {
            log.error("error", e);
        }
    }

    @Autowired
    BaseConfig baseConfig;
    @PostConstruct
    private void init() {
        try {
            DataSource ds = dbPool(baseConfig.getJdbcUrl(), baseConfig.getUserName(), baseConfig.getPassWord(), baseConfig.getDbDriverName());
            Connection connection = ds.getConnection();
            connection.setAutoCommit(false);
            String dbSql = "use " + baseConfig.getDbName();

            PreparedStatement preparedStatement = connection.prepareStatement(dbSql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setFetchSize(Integer.MIN_VALUE);
            preparedStatement.execute();
            ResultSet resultSet = preparedStatement.executeQuery(baseConfig.getQuerySql());
            dealResultSet(resultSet);
            connection.commit();
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
        }
    }

    private HikariDataSource dbPool(String url,String userName,String passWd,String driverClassName){

        DataSource dataSource = DataSourceBuilder
                .create()
                .url(url)
                .username(userName)
                .password(passWd)
                .driverClassName(driverClassName)
                .build();

        HikariDataSource ds = (HikariDataSource) dataSource;
        ds.setConnectionTimeout(baseConfig.getConnectTimeout());
        ds.setMaximumPoolSize(baseConfig.getMaxPoolSize());
        ds.setIdleTimeout(baseConfig.getIdleTimeout());
        ds.setConnectionTestQuery("select 1");
        ds.setKeepaliveTime(baseConfig.getKeepalivedTimeout());

        return ds;
    }
    private void dealResultSet(ResultSet resultSet){
        try {
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            if (columnCount == 0) {
                return;
            }
            Integer rowNum = 0;
            while (resultSet.next()) {
                List<Object> data = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    data.add(resultSet.getString(i));
                }
                rowNum++;
                sleep(baseConfig.getSleep());
                log.info("rowNum:{},data is:{}",rowNum,data);
            }
        }catch (Exception e){
            log.error(e.getMessage());
            e.printStackTrace();
        }
    }

}

---

code error:

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 152 bytes, read 29 bytes before connection was unexpectedly lost.

STACKTRACE:

java.io.EOFException: Can not read response from server. Expected to read 152 bytes, read 29 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.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.result.ResultsetRowsStreaming.next(ResultsetRowsStreaming.java:194)
        at com.mysql.cj.protocol.a.result.ResultsetRowsStreaming.close(ResultsetRowsStreaming.java:116)
        at com.mysql.cj.jdbc.result.ResultSetImpl.realClose(ResultSetImpl.java:1950)
        at com.mysql.cj.jdbc.result.ResultSetImpl.close(ResultSetImpl.java:564)
        at com.zaxxer.hikari.pool.HikariProxyResultSet.close(HikariProxyResultSet.java)
        at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.closeResultSets(DatabaseConnector.java:410)
        at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.close(DatabaseConnector.java:395)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.close(MySQLComQueryPacketExecutor.java:128)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand$original$OA8Nqe3z(CommandExecutorTask.java:139)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand$original$OA8Nqe3z$accessor$Vbh5tAMm(CommandExecutorTask.java)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask$auxiliary$7v0fw8rO.call(Unknown Source)
        at org.apache.shardingsphere.agent.core.advisor.executor.type.InstanceMethodAdviceExecutor.advice(InstanceMethodAdviceExecutor.java:72)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:121)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run$original$OA8Nqe3z(CommandExecutorTask.java:78)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run$original$OA8Nqe3z$accessor$Vbh5tAMm(CommandExecutorTask.java)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask$auxiliary$DzxI85sW.call(Unknown Source)
        at org.apache.shardingsphere.agent.core.advisor.executor.type.InstanceMethodAdviceExecutor.advice(InstanceMethodAdviceExecutor.java:72)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java)
        at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)


** END NESTED EXCEPTION **
```

[GPF199541]

I want to stream query 50 million rows of data

[TeslaCN]

Try setting netTimeoutForStreamingResults

[GPF199541]

This is useful,thanks @TeslaCN