关于mysql:MySQL-流式查询的用法和坑

引言

本文整顿了 MySQL 流式查问一些原理和用法, 包含 MySQL 官网文档对于 ResultSet 流式查问的阐明以及很多网友对于 MySQL 散失查问踩坑的阐明. 最初给出了解决流式查问的 connection 在未查问完后果集的数据之前又被其余中央应用导致报错的解决办法, 心愿能对读者有所帮忙.

原文地址: No statements may be issued when any streaming result sets are open and in use on a given connection

欢送拜访我的博客: http://blog.duhbb.com/

报错日志

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL []; Could not create connection to database server. Attempted reconnect 3 times. Giving up.; nested exception is java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:763)
    at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:829)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
    at org.springframework.jdbc.core.JdbcTemplate$RowCallbackHandlerResultSetExtractor.extractData(JdbcTemplate.java:1607)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:679)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
    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)
Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:903)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
    at com.mysql.cj.jdbc.ConnectionImpl.handleReconnect(ConnectionImpl.java:2694)
    at com.mysql.cj.NativeSession.invokeReconnectListeners(NativeSession.java:1215)
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1067)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:930)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    ... 69 common frames omitted
Caused by: java.sql.SQLException: Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@5869a708 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    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.ConnectionImpl.pingInternal(ConnectionImpl.java:1524)
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:848)
    ... 79 common frames omitted

MySQL 后果集官网解释

原文地址: 6.4 JDBC API Implementation Notes

后果集

默认状况下, ResultSet 被齐全检索并存储在内存中. 在大多数状况下, 这是最无效的操作形式, 并且因为 MySQL 网络协议的设计, 更容易实现. 如果您正在应用具备大量行或大值的 ResultSet, 并且无奈在 JVM 中为所需的内存调配堆空间, 您能够通知驱动程序一次将后果流回一行.

Statement 要启用此性能, 请按以下形式创立实例:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

给后果集设置 forward-only, read-only 以及将 fetch size 设置为 Integer.MIN_VALUE, 即表明让 MySQL 以流式传输后果集中的数据. 在此之后, 将逐行检索应用该语句创立的任何后果集.

这种办法有一些注意事项. 您必须先读取后果集中的所有行 (或将其敞开), 而后能力对连贯收回任何其余查问, 否则将引发异样.

这些语句持有的锁最早能够被开释 (无论它们是 MyISAM 表级锁还是其余存储引擎中的行级锁, 例如 InnoDB) 是在语句实现时.

如果语句在事务范畴内, 则在事务实现时开释锁 (这意味着语句须要先实现). 与大多数其余数据库一样, 在读取语句上所有待处理的后果或敞开语句的流动后果集之前, 语句是不残缺的.

因而, 如果应用流式后果, 如果您想放弃对生成后果集的语句所援用的表的并发拜访, 请尽快解决它们.

另一种抉择是每次应用基于游标的流来检索一组行. 这能够通过将连贯属性 useCursorFetch 设置为 true, 而后调用 setFetchSize(int) 来设置每次要获取的行数:

conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
stmt = conn.createStatement();
stmt.setFetchSize(100);
rs = stmt.executeQuery("SELECT * FROM your_table_here");

起因剖析

来自网友的剖析

以下谬误音讯 “ 流式传输后果集 com.mysql.jdbc.RowDataDynamic@XXXXXX 依然处于活动状态. 当任何流式传输后果集关上并在给定连贯上应用时, 不会收回任何语句. 确保您已调用 .close() 在尝试更多查问之前在任何流动的流式传输后果集上 ” 来自 MySQL.

起因在于 MySQL 的流模式, Streaming 模式是从 MySQL 获取数据的最快形式.

然而, 流模式有一个限度: 当后果集在 MySQL 连贯上以流模式关上时, 您不能应用同一数据库连贯来运行其余查问. 这正是下面的谬误音讯所通知的. 该问题的解决方案是将 MySQL 连贯设置更改为应用 CursorFetch 模式而不是 Streaming 模式.

CursorFetch 是 MySQL 中第二快的读取办法. 不同之处在于, 在 Streaming 模式下, 服务器尝试立刻开始将数据发送给消费者, 在 CursorFetch 上, 它首先填充缓冲区, 而后从缓冲区提供数据. 在十分大的数据集上, 在 MySQL 开始以 CursorFetch 模式为您获取数据之前会有一些初始提早, 但对于任何理论目标而言, 差别通常能够忽略不计.

以上来自: Error-message-Streaming-result-set-com-mysql-jdbc-RowDataDynamic

流式查问与 fetchsize

既然 fetchsize 这么好用, 那 MySQL 间接设一个值, 不就也能够用到缓冲区, 不用每次都将全量后果集装入内存. 然而, 十分遗憾, MySQL 的 JDBC 驱动实质上并不反对设置 fetchsize, 不论设置多大的 fetchsize, JDBC 驱动仍然会将 select 的全副后果都读取到客户端后再解决, 这样的话当 select 返回的后果集十分大时将会撑爆 Client 端的内存.

但也不是齐全没方法, PreparedStatement/Statement 的 setFetchSize 办法设置为 Integer.MIN_VALUE 或者应用办法 Statement.enableStreamingResults(), 也能够实现流式查问, 在执行 ResultSet.next() 办法时, 会通过数据库连贯一条一条的返回, 这样也不会大量占用客户端的内存.

MySQL 流式查问的坑

其实 MySQL 自身并没有 FetchSize 办法, 它是通过应用 CS 阻塞形式的网络流控制实现服务端不会一下发送大量数据到客户端撑爆客户端内存, 这种实现形式比起商业数据库 Oracle 应用客户端, 服务器端缓冲块暂存查问后果数据来说, 几乎是弱爆了! 这样带来的问题: 如果应用了流式查问, 一个 MySQL 数据库连贯同一时间只能为一个 ResultSet 对象服务, 并且如果该 ResultSet 对象没有敞开, 势必会影响其余查问对数据库连贯的应用! 此为大坑, 难怪 sharding-sphere 吃力心理要提供两种数据库连贯模式, 如果利用对数据库连贯的耗费要求严苛, 那么流式查问就不再适宜.

以上来自: 深刻理解 MySQL 的流式查问机制

解决方案

用了 jdbcTemplate 进行流式查问, 而后在流式查问的 callback 中又调用 jdbcTemplate 查问其余相干数据. 生产环境呈现了这个问题, 然而本地没有复现.

解决办法: 间接用原始的 jdbc connection 查问, 防止 connection 进行流式查问的时候又被别的中央应用了.

原文地址: No statements may be issued when any streaming result sets are open and in use on a given connection

欢送拜访我的博客: http://blog.duhbb.com/