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 https://stackoverflow.com/questions/39765943/clojure-java-jdbc-lazy-query/50589249#50589249 — Sean Corfield, May 31 '18 at 05:03

score 0 · Answer 1 · answered Aug 03 '12 at 13:55

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.

score 0 · Accepted Answer · edited Oct 30 '12 at 00:54

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.

This suffers from the same problem as the OP: the `throw` only executes when you have the entire result set. See https://stackoverflow.com/questions/39765943/clojure-java-jdbc-lazy-query/50589249#50589249 — Sean Corfield, May 31 '18 at 05:04

Streaming from MySQL with clojure.java.jdbc

2 Answers2

Linked