Enable JDBC tracing with OpenTelemetry

[doppleware]

Feature description

Enabling OTEL tracing seems to only include the HTTP endpoints. It would be extremely useful to have the ability to enable exporting JDBC spans as well.

[scprek]

You'd have to configure yourself, but there are libraries that have a standalone library (not part of the java agent) https://github.com/open-telemetry/opentelemetry-java-instrumentation/blob/main/docs/supported-libraries.md#libraries--frameworks

https://github.com/open-telemetry/opentelemetry-java-instrumentation/tree/main/instrumentation/jdbc/library

There is also redis lettuce, mongo, etc

We've decided to stick with the Java agent so we can immediately enable our older apps w/o micronaut otel support

[GeitV]

If someone has idea on how to enable JDBC tracing, then please let us know. We have been trying to enable it in our app, but without success. What we've tried is using BeanCreatedEventListener and BeanInitializedEventListener, but without luck.

Example of non-working solution:

@Singleton
class DataSourceInitializer : BeanCreatedEventListener<DataSource> {
    override fun onCreated(event: BeanCreatedEvent<DataSource>): DataSource {
        return OpenTelemetryDataSource(event.bean, GlobalOpenTelemetry.get())
    }
}

[sammad]

You can use third party open source library

https://github.com/opentracing-contrib/java-jdbc

Add maven or gradle dependency and wrap the jdbc driver with tracing driver in application.yml

datasources: default: driver-class-name: io.opentracing.contrib.jdbc.TracingDriver

This should start showing sql traces in your Jaeger or Zipkin dashboard

[GeitV]

@sammad we have configured OpenTelemetry and used OpenTelemetry JDBC. The same kind of config could be done with that module, the same way you described, but we did not see any traces.

Although when adding breakpoint into OpenTelemetryDriver, the driver gets called and I can see the SQL, so might be an issue of Elastic not working with JDBC traces? I don't know all the details, but I expected the JDBC driver sends out similar messages as other traces, so it's odd.

Our config, as described in the OpenTelemetry JDBC:

datasources:
  default:
    url: jdbc:otel:postgresql://${RDS_ENDPOINT}/${RDS_DATABASE}
    driverClassName: io.opentelemetry.instrumentation.jdbc.OpenTelemetryDriver

[sammad]

@GeitV to isolate the issue you can hook Jaeger and see if you can see jdbc traces in Jaeger, if you can see those, then most certainly Elastic transport is an issue. You can use Jaeger all-in-one docker image to quickly test it https://www.jaegertracing.io/docs/1.6/getting-started/

[doppleware]

I was not able to make JDBC work with Micrometer and OTEL even by following the io.opentelemetry.instrumentation.jdbc instructions. It seems like others were also not successful. Would it be possible to show a short working sample of such an integration?

[graemerocher]

This works for me otel-test.zip

[doppleware]

Thank you that worked!

[doppleware]

@graemerocher thanks for providing help. I had to go through some more steps and I wanted to share in case it helps other folks.

The solution provided by @graemerocher worked, however, I was unable to apply it. There were two reasons:

1. The project I was using was running Micrometer 3.8.6 and the OTEL package referenced by that version did not have an overload of OpenTelemetryDataSource that could be injected from DI. So this code didn't work:

It was significant because I had to make sure the JDBC driver used the same OTEL context as Micrometer - I'll get to that later.

2. The project (maybe because of the version of Micrometer, or because it was not referencingmicronaut-data-jdbc) did not have any DataSourceResolver bean (and in fact no class implementing that interface).

This took me down a different route - setting the driver-class in the datasource configuration, which is also a solution I've seen referenced in other places. I set it as follows in dev:

datasources:
  default:
    schema-generate: CREATE_DROP
    url: 'jdbc:otel:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE'
    username: sa
    password: ''
    driver-class-name: io.opentelemetry.instrumentation.jdbc.OpenTelemetryDriver

This SEEMED to work but in fact failed miserably, in some projects. Digging a little deeper I found that some beans (related to connection pools for Hikari) were setting the GlobalOpentelemtry context early on with a different context than the one used by micrometer. As a result, the two diverged and were not appending spans into the same traces.

To solve this, I had to ensure that the Micrometer Opentelmetry bean (DefaultOpenTelemetryFactory) initialized first, and then set the GlobalOpentelemetry context to that instance (which doesn't happen by default), so that the jdbc OTEL driver uses it as well. Unfortunately, in this version at least the OpenTelemetryDriver is HARD CODED to use GlobalOpentelemetry and cannot be injected with something else.

The culprit is this code here, which as you can see just hard codes the reference to GloabOpenTelemtry (which Micronaut has not set)

package io.opentelemetry.instrumentation.jdbc.internal;
...

public final class JdbcSingletons {
  public static final Instrumenter<DbRequest, Void> STATEMENT_INSTRUMENTER =
      createStatementInstrumenter(GlobalOpenTelemetry.get());

  public static Instrumenter<DbRequest, Void> statementInstrumenter() {
    return STATEMENT_INSTRUMENTER;
  }

  private JdbcSingletons() {}
}

I couldn't find a good way to control the bean startup order (maybe someone else can provide ideas?) So I thought to create a markup bean that will depend on Opentelemetry and run BEFORE the other beans, forcing the otel context to be created first. The bean will then set GlobalOpentelemetry to match the same context so that Hikari/other libraries using that context will be in sync.

This is the code I added that fixed all of these issues:

@Singleton
        @Context
        @Order(-1)
        OtelMarkupCofig otel(OpenTelemetry telemetry) {
            GlobalOpenTelemetry.set(telemetry);
            return new OtelMarkupCofig();
        }

        class OtelMarkupCofig{

        }

Again, really not proud of it, and I really didn't need to inject another bean but it works - if someone else encountered the same issue.

PS: It seems that the bean workaround was only needed if other libraries were present that were triggering that early call to initialize the OTEL context (like Hibernate). In another project I used this was not needed.

[GeitV]

Just want to say that for us, we were using Micronaut 4.2.2 at that time and well, exactly on the change of new year, the DB traces started to be coming to Elastic... No clue what happened. No changes, no commits, it just started working by itself.

[graemerocher]

https://github.com/micronaut-projects/micronaut-tracing/pull/646