Streaming from MySQL with clojure.java.jdbc

Question

According to the release notes for the MySQL JDBC driver, it should stream results if and only if using a connection with concurrency read-only, forward-only results, and a fetch-size of exactly Integer/MIN_VALUE.

However, when I attempt to generate exactly these conditions (against [mysql/mysql-connector-java "5.1.21"]), my SQL query still runs forever (or, rather, until it exhausts the JVM's memory and goes boom).

(let [query (query-only (fetch-all big-table))]
  (clojure.java.jdbc/with-connection (get-connection (:db query))
    (clojure.java.jdbc/with-query-results rows
      (into [{:fetch-size Integer/MIN_VALUE
              :concurrency :read-only
              :result-type :forward-only} (:sql-str query)]
            (:params query))
      (throw (Exception. (str "retrieved a row: " (pr-str (first rows)))))))))

The problem here is that you are not processing results as they are streamed from the DB -- only when you have the complete result set. See stackoverflow.com/questions/39765943/… — Sean Corfield
– Sean Corfield, Commented May 31, 2018 at 5:03

Charles Duffy · Accepted Answer · 2012-10-30 00:54:10Z

0

This answer refers to postgresql instead of MySQL, but should apply to both.

Wrap your with-query-results function with (clojure.java.jdbc/transaction), so:

(let [query (query-only (fetch-all big-table))]
  (clojure.java.jdbc/with-connection (get-connection (:db query))
    (clojure.java.jdbc/transaction        
      (clojure.java.jdbc/with-query-results rows
        (into [{:fetch-size Integer/MIN_VALUE
                :concurrency :read-only
                :result-type :forward-only} (:sql-str query)]
              (:params query))
        (throw (Exception. (str "retrieved a row: " (pr-str (first rows))))))))))

The postgresql docs specify one more requirement for enabling streaming: "The Connection must not be in autocommit mode." By default the connection is created with autocommit on, but wrapping with (clojure.java.jdbc/transaction) will run the inner code with autocommit off. You could also call .setAutoCommit on the connection yourself.

edited Oct 30, 2012 at 0:54

Charles Duffy

300k43 gold badges442 silver badges498 bronze badges

answered Aug 29, 2012 at 0:22

Shawn Lewis

2143 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Corfield Over a year ago

This suffers from the same problem as the OP: the throw only executes when you have the entire result set. See stackoverflow.com/questions/39765943/…

Alex · Accepted Answer · 2012-08-03 13:55:08Z

0

Since with-query-results will also accept a raw PreparedStatement, you might try creating one yourself, explicitly passing all the correct parameters, and see if you get any different behavior. That will at least tell you if the problem is with clojure.java.jdbc creating the PreparedStatement, or if you need to look deeper in the driver/database configuration.

answered Aug 3, 2012 at 13:55

Alex

14k1 gold badge40 silver badges51 bronze badges

Collectives™ on Stack Overflow

Streaming from MySQL with clojure.java.jdbc

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related