MySQL 流式查詢的用法和坑詳情 - mysql,jdbc tuhooo 博客

引言

本文整理了 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/

tuhooo 博客

tuhooo 博客

博客 / 詳情