Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-315

Cannot cancel Streaming result sets

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.2, 1.4.6
    • Fix Version/s: 2.0.0-RC
    • Component/s: Other
    • Labels:
      None
    • Environment:
      Windows 7

      Description

      The MariaDB J connector documentation says:

      Streaming result sets
      By default, Statement.executeQuery() will read the full result set from the server before returning. With large result sets, this will require large amounts of memory. Better behavior in this case would be reading row-by-row, with ResultSet.next(), so called "streaming". This is activated using Statement.setFetchSize(Integer.MIN_VALUE)

      The problem is that if an application decides to stop reading the result set, the connector still insists to fetch all the rows of the result set before closing it. This can make very long queries with big tables even a small part of it is useful.

      The here attached application JdbcCancel.java demonstrates this. It is written to read and dispay the first rows of a big table (the original one has 10 millions rows) until a row containing a "-" in the rln column is met.

      In previous versions of the MySQL J connector (for instance version 5.1.17) a turnaround was to cancel the statement before closing it. Unfortunately later versions (for instance 6.0.2) as well as the MariaDB J connector (I tested versions 1.4.2 and 1.4.6) fail to stop the reading. Even if a cancel command is executed the reading continues.

      Here is the console output of the program execution:

      Connection org.mariadb.jdbc.MariaDbConnection@20c684 established
      recordnb, diagnb, rln, diag, dp
      339858, 5, A49U9MQ94, 0, 0
      985921, 1, LFFXXDGOL, 0, 0
      1441640, 1, 8W887WRWG, 0, 0
      1760557, 0, 2MMXOF1JU, 0, 0
      1760558, 0, 5DDDSJDTD, 0, 0
      1760559, 0, 7UQQS7SKK, 0, 0
      Canceling query
      Closing statement
      Statement closed
      

      All connectors display the same output, the difference is that between the last two lines, more than 3 minutes are spent reading the rest of the file by the new versions.

      Trying the cancel the statement from another thread does not work either.

      Attached is the java application and the first 100 rows of the file that was used the make the diag table.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              diego dupin Diego Dupin
              Reporter:
              bertrandop Olivier Bertrand
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.