hugsql 2023-01-13 | Slack Archive

Rich Wood18:01:49

I’ve been stuck since yesterday trying to get a c3p0 connection pool working with mysql. Currently I’m getting a “No suitable driver” error. I have mysql/mysql-connector-java {:mvn/version "8.0.19"} in my deps.edn file. It works fine with a single connection (e.g. (db/set-default-db-connection! dbspec)but when I try to use the ComboPooledDataSource I get the error code:

(defmodel sample :sample)
  (def dbspec {:subprotocol "mysql"
               :subname "//localhost:3306/******"
               :user "root"
               :password "******"
               :server "******"
               :port 3306
               :dbname "******"})

  (def ds (doto (ComboPooledDataSource.)
            (.setJdbcUrl (str "jdbc:"
                              (:subprotocol dbspec)
                              (:subname dbspec)))
            (.setUser (:user dbspec nil))
            (.setPassword (:password dbspec nil))

          ;; Pool Size Management
            (.setMinPoolSize 3)
            (.setMaxPoolSize 15)

          ;; Connection eviction
            (.setMaxConnectionAge  3600) ; 1 hour
            (.setMaxIdleTime 1800)       ; 1/2 hour
            (.setMaxIdleTimeExcessConnections 120)

          ;; Connection testing
            (.setTestConnectionOnCheckin false)
            (.setTestConnectionOnCheckout false)
            (.setIdleConnectionTestPeriod 600)))

  (db/set-default-db-connection! {:datasource ds})

  (sample 1)

error:

com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5919e9f8 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
                                      com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run     ThreadPoolAsynchronousRunner.java:  696
                                com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run                BasicResourcePool.java: 1855
                                              com.mchange.v2.resourcepool.BasicResourcePool.access$800                BasicResourcePool.java:   44
 com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess                BasicResourcePool.java: 1114
                                               com.mchange.v2.resourcepool.BasicResourcePool.doAcquire                BasicResourcePool.java: 1127
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource         C3P0PooledConnectionPool.java:  200
                               com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection  WrapperConnectionPoolDataSource.java:  206
                               com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection  WrapperConnectionPoolDataSource.java:  220
                                             com.mchange.v2.c3p0.DriverManagerDataSource.getConnection          DriverManagerDataSource.java:  172
                                                    com.mchange.v2.c3p0.DriverManagerDataSource.driver          DriverManagerDataSource.java:  278
                                                                      java.sql.DriverManager.getDriver                    DriverManager.java:  298
java.sql.SQLException: No suitable driver
     SQLState: "08001"
    errorCode: 0

Thanks!

lukasz18:01:12

Are you pulling the mysql driver as a dependency?

Rich Wood18:01:54

:deps {amazonica/amazonica {:mvn/version "0.3.162"}
        amperity/vault-clj {:mvn/version "1.1.3"}
        buddy/buddy-core {:mvn/version "1.10.413"}
        buddy/buddy-sign {:mvn/version "3.4.333"}
        circleci/rollcage {:mvn/version "1.0.203"}
        clj-http/clj-http {:mvn/version "3.12.0"}
        clojure.java-time/clojure.java-time {:mvn/version "0.3.2"}
        clojure.jdbc/clojure.jdbc-c3p0 {:mvn/version "0.3.1"}
        com.datomic/dev-local {:mvn/version "1.0.243"}
        com.fzakaria/slf4j-timbre {:mvn/version "0.3.21"}
        com.novemberain/langohr {:mvn/version "5.2.0"}
        com.novemberain/monger {:mvn/version "3.5.0"}
        com.taoensso/carmine {:mvn/version "3.1.0"}
        com.taoensso/timbre {:mvn/version "5.1.2"}
        compojure/compojure {:mvn/version "1.6.1"}
        google-apps-clj/google-apps-clj {:mvn/version "0.6.1"}
        metosin/ring-http-response {:mvn/version "0.9.2"}
        mysql/mysql-connector-java {:mvn/version "8.0.19"}

Rich Wood18:01:05

from my deps.edn file - looks right?

lukasz18:01:29

shouldn't it be this one? https://mvnrepository.com/artifact/com.mysql/mysql-connector-j/8.0.31 - I haven't used MySQL in almost 10 years, but I had the same error when I was pulling in wrong Postgres JDBC driver

Rich Wood18:01:16

I’ll try that!

lukasz18:01:19

also, I think you need to tell c3p0 explicitly the jdbc driver class - I found that jdbc URIs to be tricky sometime (H2 for example, at least in my experience)

Rich Wood18:01:46

Yeah, I was doing that and got a message saying it was deprecated and it would pick the correct driver for me. 🤷

lukasz18:01:18

so maybe... use hikari-cp? :-) I settled on using it after trying c3p0 out few years back

lukasz18:01:22

also - one more thing, you're pulling in clojure.jdbc - it's recommended to use next.jdbc - hugsql has an adapter for it

Rich Wood18:01:24

hmm, not doing that explicitly

(ns com.bamboohr.clj-utils.mysql-orm
  (:require [next.jdbc :as jdbc]
            [com.bamboohr.clj-utils.config :as config]
            [com.bamboohr.clj-utils.credentials :as credentials]
            [com.bamboohr.clj-utils.env :as env]
            [com.bamboohr.clj-utils.logger :as logger]
            [com.bamboohr.clj-utils.utils :as utils]
            [clojure.string :as str]
            [toucan.db :as db]
            [toucan.models :refer [defmodel]])
  (:import java.sql.DriverManager)
  (:import com.mchange.v2.c3p0.ComboPooledDataSource))

Rich Wood18:01:01

tried the other mysql connector - same error - maybe i’ll have to try hikari-cp…

lukasz18:01:27

Do you need this dep: clojure.jdbc/clojure.jdbc-c3p0 {:mvn/version "0.3.1"}? why not pull in c3p0 directly?

Rich Wood19:01:50

That’s the only reference I found for importing c3p0 with deps.edn - how would I reference it directly?

lukasz19:01:55

from here: https://mvnrepository.com/artifact/com.mchange/c3p0/0.9.5.5

lukasz19:01:31

com.mchange/c3p0 {:mvn/version "0.9.5.5"}

Rich Wood19:01:35

same result