Streaming from MySQL with clojure.java.jdbc

Question

According to the release notes for the MySQL JDBC driver , it should pass results if and only if the connection to the concurrency is read-only, only for the direct result and the sample size is exactly Integer / MIN_VALUE.

However, when I try to create exactly these conditions (against [mysql/mysql-connector-java "5.1.21"] ), my SQL query is still executed forever (or rather, until it runs out of memory JVM and will not start).

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

Answer 1

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

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

 (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 specifies another requirement for enabling streaming: "The connection should not be in autosave mode." By default, a connection is created using autocommit, but using (clojure.java.jdbc / transaction), internal code will be executed with autocommit disabled. You can also call .setAutoCommit directly from your connection.

Answer 2

Since with-query-results will also accept a raw PreparedStatement, you can try to create it yourself by explicitly passing all the correct parameters and see if you have any other behavior. This will at least tell you if there is a problem with clojure.java.jdbc creating the PreparedStatement, or if you need to go deeper into the driver / database configuration.