Chapter 13 Using Connector/J with OpenTelemetry

OpenTelemetry is a set of APIs, libraries, agents, and instrumentation to provide observability for applications and their interactions with one another. It enables developers to instrument their code so that they can export observability data including traces, metrics, and logs, enabling increased granularity of profiling, debugging, and testing.

The OpenTelemetry project provides automatic instrumentation for JDBC libraries. However, when it comes to distributed tracing, the automatic instrumentation is not able to propagate the context to the database layer, causing the trace chain to be broken. Also, the automatic instrumentation applies only to the visible layer of the JDBC instrumentation, keeping out any internal operations that are worth tracing as well.

MySQL Enterprise Server 8.4.0 has the capability of collecting observability data for the server operations in the OpenTelemetry format (see Telemetry for details). The feature is supported by component_telemetry. MySQL Connector/J 8.4.0 introduces the client-side counterpart feature, with the capability of propagating the context to the MySQL Server it connects to and allowing a more complete observability for an application stack.

Applications using Connector/J that wish to enable OpenTelemetry tracing need the following 3rd-party libraries:

• The opentelemetry-api library.

• The opentelemetry-context library

• The opentelemetry-sdk library if you choose manual instrumentation, or the opentelemetry-javaagent if you prefer automatic instrumentation.

The Connector/J connection property openTelemetry controls how observability data production is to be handled on a per connection basis. This option accepts the following values:

• REQUIRED: An OpenTelemetry library must be available at run time, or connections to the MySQL Server will fail. Note that the opentelemtry-api library alone does not produce any output traces.

• PREFERRED: Enables generating OpenTelemetry instrumentation, provided that an OpenTelemetry library is available at run time; a warning is issued if a library is not available.

• DISABLED: Turns off generating OpenTelemetry instrumentation by Connector/J. However, this does not prevent external means of instrumentation, such as the automatic instrumentation provided by the OpenTelemetry Java agent.

Not setting a value for the property is equivalent to setting it as PREFERRED, except that no warning is issued when no OpenTelemetry library is available at runtime.

Notice that MySQL Connector/J does not provide any means for configuring its own OpenTelemetry exporters—it relies entirely on the calling application for the exporter configuration.

The following is a demonstration for how to use OpenTelemetry. It assumes that client application, MySQL Server, and the observability backend are all running on the same machine. It also assumes that component_telemetry is enabled and is properly configured on the MySQL Server. The Java agent is used for the sake of simplicity. The opentelemetry-instrumentation-annotations library is also used, so there is no need to write any OpenTelemetry code in the sample class.

This simple demonstration contains a class OTelDemo, which creates a connection to the Sakila database and executes an SQL SELECT statement that returns five rows from the table film. The purpose of the demonstration is just to generate a sequence of traces, not to produce anything practically useful. Here are the contents of the source file src/demo/OTelDemo.java

package demo;
import java.sql.*;
import io.opentelemetry.instrumentation.annotations.WithSpan;
public class OTelDemo {
    public static void main(String[] args) throws Exception {
        listFiveFilms();
    }
    @WithSpan
    private static void listFiveFilms() throws Exception {
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://johndoe:s3cr3t@localhost:3306/sakila")) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM film LIMIT 5");
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
        }
    }
}

The top level trace that will be observed is created within the application code via the annotation @WithSpan. This trace becomes the current context (parent) for any subsequent spans created inside the JDBC driver. Subsequently, via context propagation with the executed commands, a trace created by the driver becomes the parent of the spans created within the MySQL Server.

The code can be compiled by issuing the command:

$ javac -classpath "lib/*" -d bin src/demo/OTelDemo.java

Before you run the demonstration, set up an observability backend with, for example, Jaeger. You can then execute the code with the command:

$ java -javaagent:agent/opentelemetry-javaagent.jar \
  -Dotel.traces.exporter=jaeger \
  -Dotel.metrics.exporter=none \
  -Dotel.service.name=OTelDemo \
  -Dotel.instrumentation.common.default-enabled=false \
  -Dotel.instrumentation.opentelemetry-api.enabled=true \
  -Dotel.instrumentation.opentelemetry-instrumentation-annotations.enabled=true \
  -classpath "bin:lib/*" \
  demo.OTelDemo
 [otel.javaagent 2024-04-12 16:10:32:140 +0100]
 [main] INFO io.opentelemetry.javaagent.tooling.VersionLogger - opentelemetry-javaagent - version: 1.32.0
 ACADEMY DINOSAUR
 ACE GOLDFINGER
 ADAPTATION HOLES
 AFFAIR PREJUDICE
 AFRICAN EGG

You can now open the Jaeger backend in your web browser and search for the traces of the OTelDemo service.

Distributed tracing in MySQL is limited to statement executions. This limitation comes from the fact that context propagation is implemented through query attributes and is only supported for query executions. While running, the server generates spans for other operations as well. Those spans can be seen in the observability backend too, but they are unlinked from any of the traces started by the client application or library. Similarly, spans created by Connector/J that do not produce server commands or those producing commands lacking support for query attributes are depicted as terminal nodes in the trace graphs. This is exemplified by operations such as the PING command. Nonetheless, the server still generates a span for the corresponding operation, just that it appears unlinked from the originating trace.