MYSQL数据库的C3P0连接池错误

Question

最近，我将MYSQL数据库从5.7版更新到8版，因此将MYSQL连接器依赖性从版本5更新为8，并将c3p0版本0.9.5的最新版本更新到Clojure项目。更新之后，在对数据库执行请求时，c3p0连接池出现了错误。

以下是例外情况的一部分：

com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection (C3P0PooledConnectionPool.java:692)
    com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection (AbstractPoolBackedDataSource.java:140)
    clojure.java.jdbc$get_connection.invoke (jdbc.clj:278)
    clojure.java.jdbc$db_query_with_resultset.invoke (jdbc.clj:798)
    clojure.java.jdbc$query.doInvoke (jdbc.clj:832)
    clojure.lang.RestFn.invoke (RestFn.java:425)

以下是可能影响c3p0连接池的依赖关系：

[postgresql                 "9.1-901-1.jdbc4"]
[com.mchange/c3p0           "0.9.5"]
[yesql "0.4.2"]  

这里是我建立c3p0连接的地方：

(ns myapp.database.database-pooling
  (:import com.mchange.v2.c3p0.ComboPooledDataSource))

(defn pooled-database
  [specification]
  (let [cpds (doto (ComboPooledDataSource.)
               (.setDriverClass (:classname specification))
               (.setJdbcUrl (str "jdbc:" (:subprotocol specification) ":" (:subname specification)))
               (.setUser (:user specification))
               (.setPassword (:password specification))
               ;; expire excess connections after 30 minutes of inactivity:
               (.setMaxIdleTimeExcessConnections (* 30 60))
               ;; expire connections after 3 hours of inactivity:
               (.setMaxIdleTime (* 3 60 60)))]
    {:datasource cpds}))

不幸的是，我还没有找到一个在线解决方案。

我不知道我在c3p0连接上做错了什么，因为我寻找了许多可能性，但仍然失败了。

你的帮助会更加感激。

Answer 1

我有一个使用H2数据库的旧演示，可能会有一些帮助。源代码在这里。

The project.clj

(defproject demo  "0.1.0-SNAPSHOT"
  :description    "FIXME: write description"
  :url            "http://example.com/FIXME"
  :license {:name "Eclipse Public License"
            :url  "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [
     [com.h2database/h2 "1.4.197"]
     [hikari-cp "2.3.0"]
     [org.clojure/java.jdbc "0.7.5"]
     [org.clojure/clojure "1.9.0"]
     [org.clojure/spec.alpha "0.1.143"]
     [org.clojure/test.check "0.9.0"]
     [prismatic/schema "1.1.9"]
     [tupelo "0.9.111"]
     ]
  :profiles {:dev     {:dependencies []
                       :plugins      [[com.jakemccrary/lein-test-refresh "0.22.0"]
                                      [lein-ancient "0.6.15"]
                                      [lein-codox "0.10.3"]]}
             :uberjar {:aot :all}}

  :global-vars {*warn-on-reflection* false}
  :main ^:skip-aot demo.core

  :source-paths ["src"]
  :test-paths ["src"]
  :java-source-paths ["src-java"]
  :target-path "target/%s"
  :jvm-opts ["-Xms500m" "-Xmx2g"]
  )

连接池部分如下所示：

(ns tst.demo.jdbc-pool
  (:use demo.core
        tupelo.core
        tupelo.test)
  (:require
    [clojure.java.jdbc :as jdbc]
    [hikari-cp.core :as pool]
    ))

(def datasource-options-sample {:auto-commit        true
                                :read-only          false
                                :connection-timeout 30000
                                :validation-timeout 5000
                                :idle-timeout       600000
                                :max-lifetime       1800000
                                :minimum-idle       10
                                :maximum-pool-size  10
                                :pool-name          "db-pool"
                                :adapter            "h2" ; "postgresql"
                                :username           "sa"
                                :password           ""
                                :database-name      "database"
                                :server-name        "localhost"
                                :port-number        5432
                                :register-mbeans    false})

(def datasource-options {:adapter  "h2"
                         :url      "jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1"
                         :username "sa"
                         :password ""})

(def ^:dynamic db-conn nil)

(defn with-connection-pool
  "Creates and uses a connection for test function"
  [tst-fn]
  (let [datasource (pool/make-datasource datasource-options)]
    (binding [db-conn {:datasource datasource}]
      (tst-fn)
      (pool/close-datasource datasource)))) ; close the connection - also closes/destroys the in-memory database

(use-fixtures
  :once with-connection-pool) ; use the same db connection pool for all tests

(dotest
  ; creates & drops a connection (& transaction) for each command
  (jdbc/db-do-commands db-conn ["drop table if exists langs"
                                "drop table if exists releases"])

  (jdbc/db-do-commands
    db-conn
    [(jdbc/create-table-ddl :langs
                            [[:id :serial]
                             [:lang "varchar not null"]])
     (jdbc/create-table-ddl :releases
                            [[:id :serial]
                             [:desc "varchar not null"]

                             [:langId "numeric"]])])
  (jdbc/insert-multi! db-conn :langs ; => ({:id 1} {:id 2})
                      [{:lang "Clojure"}
                       {:lang "Java"}])
  (let [result (jdbc/query db-conn ["select * from langs"])]
    (is= result [{:id 1, :lang "Clojure"}
                 {:id 2, :lang "Java"}]))

  ; Wraps all commands in a single transaction
  (jdbc/with-db-transaction
    [tx db-conn]
    (let [clj-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Clojure'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "ancients" :langId clj-id}
                           {:desc "1.8" :langId clj-id}
                           {:desc "1.9" :langId clj-id}]))
    (let [java-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Java'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "dusty" :langId java-id}
                           {:desc "8" :langId java-id}
                           {:desc "9" :langId java-id}
                           {:desc "10" :langId java-id}])))
  (let [
        ; note cannot wrap select list in parens or get "bulk" output
        result-0 (jdbc/query db-conn ["select langs.lang, releases.desc
                                             from    langs join releases
                                             on     (langs.id = releases.langId)
                                             where  (lang = 'Clojure') "])
        result-1 (jdbc/query db-conn ["select l.lang, r.desc
                                             from    langs as l
                                                       join releases as r
                                             on     (l.id = r.langId)
                                             where  (l.lang = 'Clojure') "])
        result-2 (jdbc/query db-conn ["select langs.lang, releases.desc
                                              from    langs, releases
                                              where  ( (langs.id = releases.langId)
                                                and    (lang = 'Clojure') ) "])
        result-3 (jdbc/query db-conn ["select l.lang, r.desc
                                            from    langs as l, releases as r
                                            where  ( (l.id = r.langId)
                                              and    (l.lang = 'Clojure') ) "])]

    (let [expected [{:lang "Clojure", :desc "1.8"}
                    {:lang "Clojure", :desc "1.9"}
                    {:lang "Clojure", :desc "ancients"}]]
      (set= expected result-0)
      (set= expected result-1)
      (set= expected result-2)
      (set= expected result-3))
    (println "all tests finished") ) )

要运行，只需键入：

> lein clean ; lein test 
----------------------------------
   Clojure 1.9.0    Java 15.0.2
----------------------------------

lein test tst.demo.jdbc

result-0 => 
({:lang "Clojure", :desc "ancients"}
 {:lang "Clojure", :desc "1.8"}
 {:lang "Clojure", :desc "1.9"})

lein test tst.demo.jdbc-pool
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
all tests finished

Ran 3 tests containing 7 assertions.
0 failures, 0 errors.