Connection issue with next.jdbc

I am playing with next-jdbc and I’m quite liking it. I like the fact that you can use a literal or a Connection and it just works- for the REPL it’s great.

Still, I don’t understand why, if I have a database:

(def CX
  {:dbtype "mariadb"
   :dbname "jimmy2times"
   :user "root"
   :password ""})

And I run:

(def DS (jdbc/get-datasource CX))
(doseq [x (range 100000)]
  (jdbc/execute! DS ["SELECT 1"]))

Will sooner or later die with an error like Execution error (ConnectException) at java.net.PlainSocketImpl/socketConnect (PlainSocketImpl.java:-2). Can't assign requested address (connect failed).

If I do:

(with-open [conn (jdbc/get-datasource CX)]
  (doseq [x (range 100000)]
    (jdbc/execute! conn ["SELECt count(*) FROM j2t_facts"])))

It works.

What is the difference? I mean, DS in the first example was bound and not changed.

My first reaction is that is a bug, suggesting that database connections are not always being correctly closed in that first example (they should be tho’).

I will investigate and update this thread with my findings.

FWIW, none of this has produced that error against Percona 5.7 using the standard MySQL driver:

$ (doseq [x (range 1000)] (jdbc/execute! ds ["select 1"]))
$ (doseq [x (range 10000)] (jdbc/execute! ds ["select 1"]))
$ (doseq [x (range 100000)] (jdbc/execute! ds ["select 1"]))
$ 

I will re-test this against the MariaDB driver shortly. If it repros there, it may be a bug in that JDBC driver rather than next.jdbc.

I have not been able to reproduce with the MariaDB driver either (version 2.5.4) but I will leave the issue open and continue trying.

Can you provide more details about the versions of the JDBC driver and the exact database you are using?

I will update the bug on GitHub - thanks for posting.

I think my question here was more on the informative side: are connections obtained as get-datasource closed on every use? and is there any difference between the def and the with-opencases? because I see the connection identifiers in Maria go up a lot.

If you pass a DataSource (or db-spec hash map or URI string) to any of next.jdbc's APIs that accept such a thing, then next.jdbc will call .getConnection() (possibly after creating a DataSource from the hash map or string) to get a fresh connection and will close it before returning the result. next.jdbc uses with-open internally to ensure all things that get opened – which includes Connection, PreparedStatement, and a few other things – also get properly closed.

The basic javax.sql.DataSource does not have a .close method (and doesn’t need one). HikariCP, c3p0, etc that provide connection pooled datasources have methods to shutdown the pool and those are usually named .close (but there’s no common base type to use for type hinting – I discuss that in the next.jdbc docs).

For folks reading this thread and not looking at the test results added to the GitHub issue above, it seems that this issue is related to a specific combination of environmental features, related to running MariaDB server on macOS (possibly restricted to specific versions of those).