Jmeter JDBC Connection to CrateDB

I’ve been iterating through quite a few different connection settings but haven’t been able to get anything to work yet.

There is a whitepaper that mentions connecting Jmeter to CrateDB directly. Does anyone have any feedback on how to implement?

1 Like

Hi @sleggett,

which version of CrateDB and JMeter are you using? Do you see any errors in the JMeter or CrateDB logs?

I followed the Building a Database Test Plan docs and this basically works out of the box.

Make sure you added PostgreSQL JDBC Driver to your JMeter ./lib folder. Use default settings for your JDBC Connection Configuration with Database URL jdbc:postgresql://your.cratedb.cluster:5432/doc, JDBC Driver class org.postgresql.Driver and correct username and password.

With these settings you should be able to connect to CrateDB.
Happy hacking,
Johannes

2 Likes

Ah, I was trying to use the Crate driver directly, that makes sense.

@jayeff Is there a trick to getting the standard crate user to connect without a password?

Cannot create PoolableConnectionFactory (FATAL: password authentication failed for user “crate”)? I tried pointing to a NULL and passing that, but same outcome.

The superuser crate can only be authenticated from the local host and without any password.
So if you are on the local host you can just leave it empty.

Otherwise you would have to create a new user and grant the necessary privileges.

Also see

and

The user exists in the database, permissions updated, still getting authentication failed.
“password authentication failed for user “test”)”

JMeter is version 5.4.3; Crate is version 4.6.7; Postgres driver is version 42.3.3

Any other things I could check possibly?

2022-03-14 15:53:42,824 DEBUG o.a.j.p.j.c.DataSourceElement: MaxPool: 1 Timeout: 10000 TrimInt: 60000 Auto-Commit: true Preinit: true
2022-03-14 15:53:42,825 DEBUG o.a.j.p.j.c.DataSourceElement: KeepAlive: true Age: 5000 CheckQuery: select 1
2022-03-14 15:53:42,825 DEBUG o.a.j.p.j.c.DataSourceElement: Driver: org.postgresql.Driver DbUrl: jdbc:postgresql://crate.mylab.com:5432/doc User: test
2022-03-14 15:53:42,988 ERROR o.a.j.p.j.c.DataSourceElement: Error preinitializing the connection pool: crateDb@394848485
java.sql.SQLException: Cannot create PoolableConnectionFactory (FATAL: password authentication failed for user "test")
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:656) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:534) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	at org.apache.jmeter.protocol.jdbc.config.DataSourceElement.initPool(DataSourceElement.java:308) [ApacheJMeter_jdbc.jar:5.4.3]
	at org.apache.jmeter.protocol.jdbc.config.DataSourceElement.testStarted(DataSourceElement.java:127) [ApacheJMeter_jdbc.jar:5.4.3]
	at org.apache.jmeter.engine.StandardJMeterEngine.notifyTestListenersOfStart(StandardJMeterEngine.java:205) [ApacheJMeter_core.jar:5.4.3]
	at org.apache.jmeter.engine.StandardJMeterEngine.run(StandardJMeterEngine.java:382) [ApacheJMeter_core.jar:5.4.3]
	at java.lang.Thread.run(Thread.java:833) [?:?]
Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "test"
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:646) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:180) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.Driver.makeConnection(Driver.java:400) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.Driver.connect(Driver.java:259) ~[postgresql-42.3.3.jar:42.3.3]
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:357) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:103) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:652) ~[commons-dbcp2-2.8.0.jar:2.8.0]
	... 7 more```

Do you have any Postgres / Postgres-like DBs running that could bind to port 5432?

Do you have access to the Admin UI and can run the query:

SELECT port['psql'] FROM sys.nodes;

@proddata

I don’t think there’s anything else explicitly binding there.

3 results from the sys.nodes on Port 5432 from 3 clusters.

could you post your crate.yml / config?

If you try to authenticate with user test with either psql or on CrateDB Admin UI does this work?

There is a proxy in front for HTTP (below), I’m thinking how to authenticate with different credentials. I don’t believe the UI just let’s me click Logout (I might have missed it).

Here’s proxy info from yml:
containers:
- name: crate-proxy
image: “bitnami/oauth2-proxy:7.1.2”
imagePullPolicy: “IfNotPresent”
ports:
- containerPort: 4180
protocol: TCP
name: http

Question: does the psql port need to be explicitly set in the crate.yml in the “ports” section of “spec”? 5432?

The Admin UI does not have a logout button. The easiest method is to use a private browser session and login there. Once you close the this private window you will be logged out.

Not necessarily. CrateDB will uses the first free port in range 5432-5532 Node-specific settings — CrateDB: Reference

Your error message (FATAL: password authentication failed for user "test") seems to indicate that connection can be established to CrateDB but auth does not work for your user test either because the user doesn’t exist or password is wrong.