Clojurians Log v2

Hi @seancorfield, that is roughly what I have (except for spraying things over namespaces - for lack of guidance) (defn get-pool [] (connection/->pool ComboPooledDataSource db-spec)) but I am getting this Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'AEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support. given (def db-spec {:port 3306 :host "localhost" :dbtype "mysql" :dbname "test" :user "tester" :password "password" :useSSL false :serverTimezone "UTC"}) I can kick the tyres on Hikari

this seems to be a new burden on the next.jdbc dbtype to notice :serverTimezone and append it to the jdbcUrl - just hypothesising

If you're making a non-pooled datasource, everything in the hash map goes into the URL already. So this is just about keeping either HikariCP or c3p0 happy.

Looking at the docs for those, neither seem to support it as a connection parameter which is a pain...

I guess I'm going to need to expose the machinery in next.jdbc.connection that actually builds JDBC URLs so you can separate out the connection string from the pooled datasource parameters 😞

https://github.com/seancorfield/next-jdbc/issues/138

That's not going to be as easy as I'd hoped -- the URL-builder only builds the minimal JDBC URL and then assumes everything else can be passed as properties when the connection is requested -- which is fine for the simple call to the DriverManager but not so good for the pooled datasource...

@gmercer OK, seancorfield/next.jdbc {:mvn/version "1.1.582"} is available on Clojars for you -- it adds next.jdbc.connection/jdbc-url: see its docstring for usage details.

Not well-tested but should get you going again. I'd probably recommend omitting :user/`:password` from the db-spec and instead provide them in the hash map along with :jdbcUrl that you pass into the pooling library (and be careful that one expects :user like JDBC and the other lib expects :username instead -- which is mentioned in the docs).

@seancorfield thanks - I tried mimicking your spec->url+etc .. nearly there but will try the latest version ... thanks heaps considering timezones 😉

Yeah, the spec->url+etc function is only about half of what you need...

@seancorfield thanks - works like a bought one

I definitely need to create some tests around it and expand the documentation... but I just wanted to get this out for you a.s.a.p.

We are using the db-query-with-resultset to apply a result-set-fn on the result-set lazily (in the db sense) but the fetch-size doesnt seem to be respected. If I do a (count result-set) it returns the size of the all the rows expected from the query instead of the fetch size, this is how our function looks like. clojure.java.jdbc version is "0.3.5"

(defn do-lazy-read [db-spec sql-params size result-set-fn]
  (jdbc/db-query-with-resultset
    db-spec
    (into [] (cons {:fetch-size size} sql-params))
    (fn [result-set]
      (prn (count result-set))
      (-> result-set
          (jdbc/result-set-seq :identifiers qstr/underscores->hyphens)
          result-set-fn))))

@frozenfire1992 that is expected : fetch size is not a limit, it's just a hint for each "chunk" of the overall result set during database access.

But we are facing memory issues and we think this not being lazy is the cause, number of rows are in the order of a few 100,000 rows

to a million

You need reducible-query

Haven’t used it before, but it seems it will close the connection after reducing the result-set, how would I go about maintaining the cursor?

I am going through the documentation, will explore reducible query. But the question is if lets say the fetch size 1000 is just a hint, why is the hint not considered? Why would it always return all the rows, that too rows close to a million?

Reading this answer of yours https://stackoverflow.com/questions/39765943/clojure-java-jdbc-lazy-query/39775018#39775018 and the linked docs and the other SO question on why jdbc ignores setFetchSize

@frozenfire1992 I was on my phone earlier, now at my desk so I can answer in full...

1. fetch size tells the JDBC driver to try to only fetch that many rows at a time but it is not a limit on how many rows come back in the result set

2. the result set is built lazily -- so result-set is a lazy sequence and if you call count you will realize the entire sequence, which will be you 1M rows

3. even trying to process result set lazily and using fetch, you are at the usual mercy of Clojure's treatment of very large lazy sequences -- and you must completely process the result set before c.j.j. closes the connection (otherwise you'll get errors when you try to realize the next piece of the lazy result set -- because it relies on the connection staying open).

4. since all of that is very tricky (as you're discovering), reducible-query was added so you can process the result set in a single pass reduction without needing to worry about laziness

FWIW, next.jdbc is built on that concept as a primary API: next.jdbc/plan is explicitly a reducible that is also "foldable" (in the clojure.core.reducers/fold sense so you can achieve some level of concurrency as well).

The reducible-query function in c.j.j. is the predecessor to next.jdbc/plan -- but the latter is better designed for performance (as is the whole of next.jdbc).

As another part of #3 above: holding onto the head is definitely a possibility -- as with processing any very large lazy sequence, but you're dealing with a Clojure problem there, not a JDBC problem.

Does that help @frozenfire1992?

That helps a lot @seancorfield, going to share this with my team. Thanks a ton for the elaborate response and of course for all your work.

It's also worth pointing out here that the linked community documentation site http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#processing-a-result-set-lazily hasn't been updated in a very long time due to technical issues with the hosting infrastructure so there are several c.j.j updates to those docs that have not yet been published -- and of course c.j.j itself really isn't maintained these days (just critical bug fixes), as all my effort has gone into next.jdbc -- the 1.0.0 release that c.j.j could never have 🙂