Fork me on GitHub
#sql
<
2023-11-16
>
ag04:11:32

So I have this local DB, and I'm using :ssl true :sslcert, etc. in my db-spec. And when I connect to it with jdbc/get-connection, it works fine. I can send queries, etc. But I can't seem to get it working with a connection pool. It would complain that: "password authentication failed for user". What I am doing wrong?

Cam Saul04:11:47

c3p0 or hikari?

ag04:11:24

Both not working, with similar messages. c3p0 churns for a bit (like almost a minute) and then spits out: 'The server requested SCRAM-based authentication, but no password was provided', hikari fails faster

Cam Saul04:11:19

Try seeing if you can get a connection with java.sql.DriverManager . That will probably pinpoint your issue

Cam Saul04:11:18

If you don't want to debug that way you could reify a DataSource and have its implementation of getConnection call whatever you're using that's working and return that. With c3p0 you can create a PooledDataSource from a normal unpooled one

Cam Saul04:11:57

Been a while since I used Hikari but I think you can do the same thing there

Cam Saul04:11:44

Take a look at how I did it here for example. This lib is like a million years old but maybe it can point you in the right direction https://github.com/metabase/connection-pool

seancorfield06:11:33

@U0G75ARHC What exactly is HikariCP failing with? Did you change :user to :username per the docs?

ag17:11:22

The problem might be specific to CockroachDB. It has default root user. The root account has no password and is required to present a cert on SQL. That works with single connections, but seems to be problematic for connection pooling.

seancorfield18:11:08

You probably need to provide those options differently. When you pass a db-spec in to HikariCP, it will only use the properties it knows about I suspect, but there is a datasource properties option that lets you pass additional properties directly through to the underlying JDBC driver I think...?

seancorfield18:11:35

Are you using next.jdbc.connection/->pool for this? Did you read this part of the docs: If you need to pass in connection URL parameters, it can be easier to use next.jdbc.connection/jdbc-url to construct URL, e.g.,

(->pool HikariDataSource {:jdbcUrl (jdbc-url {:dbtype .. :dbname .. :useSSL false}) :username .. :password ..})

seancorfield18:11:33

In addition, read https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.894/doc/getting-started#connection-pooling carefully for what the db-spec can contain for HikariCP (and it talks about the datasource properties in that section too).

ag19:11:43

Yes, I'm experimenting with the ->pool fn. I'll keep trying. Thank you. And I'll try jdbc-url as well. I'll post an update once I figure this out.

seancorfield19:11:54

Part of the issue is the mismatch between what HikariCP accepts as properties and what JDBC drivers at large accept. At work, we specify :maxLifetime and :maximumPoolSize which are HikariCP-specific, for example.

ag19:11:53

the brownfield codebase I'm digging through uses c3p0 (the person who wrote the code no longer available), I probably will try to make it work with that. I'm gonna need to find some compelling reasons to suggest a switch to HikariCP.

ag19:11:26

maybe I won't have to. It should work with either.

seancorfield19:11:02

One "gotcha" is c3p0 uses user (as does JDBC) but HikariCP uses username.

seancorfield19:11:27

(we used to use c3p0 and switched to HikariCP)

ag19:11:05

Yay... it finally worked. I needed jdbc-url:

(with-open [ds (connection/->pool
                  ComboPooledDataSource
                  {:jdbcUrl (connection/jdbc-url db-spec)})]

ag19:11:45

Gosh, I just needed to read the docs, paying a bit more attention.

seancorfield19:11:23

Glad the docs are good 🙂