Fork me on GitHub
#sql
<
2019-07-25
>
seancorfield01:07:18

@jonpither @dominicm Thank you for the embedded PG information -- next.jdbc now includes automated tests against (embedded) PostgreSQL on master! So of course now the test suite is full of conditionals since that's the first DB in the automated suite that returns lowercase table/column names 🙂

🎉 4
shaun-mahood18:07:05

I'm having some problems getting HikariCP to work with next.jdbc, I'm running on Ubuntu and connecting to MS SQL

shaun-mahood18:07:43

(jdbc/execute! (jdbc/get-datasource db-spec) [query]) works fine using both mssql and jtds drivers

shaun-mahood18:07:09

When I run

(with-open [^HikariDataSource ds (connection/->pool HikariDataSource db-spec)]
    (jdbc/execute! ds [query]))
with jtds, it throws a SQLException I/O Error: GSS Failed: No valid credentials provided (Mechanism level: Failed to find any Kerberos tgt).

shaun-mahood18:07:55

with mssql, I get Login failed for user

seancorfield18:07:12

Because the options to Hikari do not use the standard :user/`:password` options like regular datasources.

seancorfield18:07:45

You have to provide :mixedCase keywords that match Hikari's world view. Very annoying but a fact of life, unfortunately.

seancorfield18:07:43

Try :username instead of :user. Based on the docs, I think that should work.

seancorfield18:07:22

I think that might be true of c3p0 as well...

seancorfield18:07:00

...nope, that uses :user (for .setUser()). Ain't that great?

shaun-mahood18:07:16

These are confusing docs! They have props.setProperty("dataSource.user", "test"); right below ds.setUsername("bart");

shaun-mahood18:07:06

Yep, that's all I had to do - thanks!

seancorfield18:07:24

I'll update the docs to add a warning about that...

shaun-mahood18:07:40

Yeah, that would be great! I should have it working with Integrant right away, do you think that would be worth adding to the docs underneath the Component section? Or maybe move it into a separate HikariCP document.

shaun-mahood18:07:58

It also doesn't seem to work with jtds, mssql is fine though.

seancorfield20:07:42

Interesting. But the jtds db-spec works fine without HikariCP in the mix?

seancorfield20:07:37

The Getting Started page (on master) now says:

Note: HikariCP expects :username instead of :user when supplying authentication credentials. c3p0 expects :user, just like in a regular db-spec hash map.

shaun-mahood20:07:07

Yeah - it's not listed at https://github.com/brettwooldridge/HikariCP#popular-datasource-class-names as supported and I found a couple posts an slack overflow that made it seem like it jtds didn't really work with it.

shaun-mahood20:07:48

That looks very helpful 🙂 If I put both :user and :username in my db-spec it doesn't cause any problems with either jdbc/get-datasource or HikariCP, though I haven't tested with C3PO.

shaun-mahood20:07:17

I'm going to try it with C3PO now, and see if any of these things will break it, too.

seancorfield21:07:02

I appreciate all this testing and feedback! Thank you!

shaun-mahood21:07:12

I appreciate having fun new libraries to play with 🙂

shaun-mahood21:07:10

Here are some REPL tests with results, in case you want to experiment at all

seancorfield22:07:35

I totally expect HikariCP to fail when you dissoc :username, and I totally expect the other cases to fail when you dissoc :user.

seancorfield22:07:28

So the HikariCP/jtds combo is the interesting oddity. Do you have more details about what exactly fails?

shaun-mahood23:07:47

Not really - I kind of wondered if I was doing something wrong there, which is partly why I thought I would just hit every option I could think of. I found https://stackoverflow.com/questions/42247864/configure-hikaricp-in-spring-boot-with-jtds/50891863 which at least looks like the same error message - not sure if it's actually relevant, though.

seancorfield23:07:09

Ah, so specify :connectionTestQuery "SELECT 1" and it should go away.

seancorfield23:07:57

LMK if that works and I'll add another note to Getting Started 🙂

shaun-mahood23:07:12

I’ll give it a try tomorrow and see! Thanks!

shaun-mahood18:07:03

Yep, adding that to the db-spec fixes the 2 places that were throwing AbstractMethodErrors. I think that means everything works as expected now.

seancorfield19:07:50

Brilliant! I'll update the Getting Started docs -- thank you!

shaun-mahood19:07:42

The only other thing that I think could cause problems - jtds didn't work for me with :username only (though mssql did), so if someone is trying to get up and running with jtds and HikariCP I can totally see them running into problems doing non-pooled queries if they don't include :user as well.

seancorfield19:07:17

Really? That's a bit weird. HikariCP ignores :user as far as I know...

seancorfield19:07:11

Oh, I see what you mean. Right :username is only needed for HikariCP. It is not needed otherwise. :user is the right thing to use.

seancorfield19:07:21

I'm a bit surprised that mssql works with :username instead of :user -- that's non-standard (well, in the world of "standard" here).

shaun-mahood19:07:22

Yeah - I think the only issue will be if people are thinking it's a replacement instead of an additive thing, not sure if it's necessary to clarify that in the docs or not. I bet the mssql team threw it in because they got sick of dealing with support questions from HikariCP developers.

shaun-mahood19:07:29

I haven't supported anything like this, so I don't know if it would be worth special casing any of this in spec->url+etc - I have no idea how you are still relatively sane after dealing with all the little inconsistencies of different SQL stuff for this long. 🙂

seancorfield19:07:30

I can't imagine people using both pooled and non-pooled connections in the same app, to be honest.

seancorfield19:07:58

However, I've updated the wording in that caveat -- see if it is clearer now?

shaun-mahood19:07:22

Yeah, that wording looks great to me.

4