jhalliday's quarkus agroal logging rant / cheat sheet

Agroal, the connection pool in Quarkus, has no trace logging. We need to infer its behaviour from observation of the underlying db.

Depending on the db driver it may not have adequate logging either, so we need to inject some.

P6Spy is intended for this use case, but hasn't been updated in years and doesn't play nice with Quarkus out of the box.

It's going to be a long day...

add library

        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.9.1</version>
        </dependency>

wire datasource plugin

Create a shim class that implements P6DataSource and wraps the chosen db drivers' real DataSource Add setters to wire the url and credentials from the quarkus config through to the driver's datasource. Then, install it in place of the original datasource in application.properties Don't change the url to the spy one.

quarkus.datasource.db-kind=other
quarkus.datasource.jdbc.driver=org.acme.P6SpyH2DataSource
quarkus.datasource.jdbc.url=jdbc:h2:/tmp/test
quarkus.datasource.username=admin
quarkus.datasource.password=admin
quarkus.datasource.jdbc.transactions=xa

ref quarkus db config guide

You can't wire P6Spy in its own way, because that tries to lookup the underlying datasource from JNDI, which Quarkus doesn't have.

Note this is clunky as you need one shim class per underlying db type, but they are fairly simple. The shim also bypasses most P6Spy initialization and config code, because that's done as a side effect of datasource initialization and we're supplying the datasource directly instead. Don't expect spy.properties to work.

Note also the Quarkus docs are poor - '.driver' doesn't have to be a java.sql.Driver, it can be anything that Agroal's ConnectionFactory will accept as a provider. This is key to working with XA, where you need an XADataSource instead.

configure logging

Logging from P6Spy is a mess.

Normally you override it by ServiceLoader, but that doesn't work with Quarkus classloading and class indexing.

So, we override the logging factory via code in the shim.

This installs a custom event listener which receives events from P6Spy's JDBC wrapper classes and sends output to JBossLogging, which means you can configure the pattern, level and such in the normal way.

Except Agroal. Did I mention Agroal doesn't have any logging? I may have a bit of a thing about that.

ref quarkus logging config guide

interesting events

By default P6Spy will log only SQL, which frankly you can get more easily just by turning on show_sql in hibernate in most cases.

With the custom logger you can add output for whatever interests you.

Except XA.

(sigh)

For some reason P6Spy wraps everything in JDBC except for XAResource. Most drivers implement XAResource as stored procedure calls (i.e. SQL) over the driver wire protocol, but since the calls are driver internal the spy wrappers don't see that SQL.

So, the datasource shim also intercepts getXAResource and wraps the result. The wrapper logs calls to the same logger used by the event listener, though not via events. Now you get the XA calls and the rest of the driver activity all logged to the same place.

appendix: db's own tracing

Varies by db type and pretty much never plays nice with JBossLogging. YMMV.

h2 logging TL;DR: quarkus.datasource.jdbc.url=jdbc:h2:/tmp/test;TRACE_LEVEL_SYSTEM_OUT=3