Streaming from MySQL with clojure.java.jdbc

问题:

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)))))))))

回答1:

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.

回答2:

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.