Zipkin-mysqlConnector8.0 sql拦截器

背景

最近在做微服务框架Springboot的版本升级，其中最为复杂的便是兼容性测试。
引起兼容性问题的几个主要原因：

• 依赖jar包方法不存在：No Such Method Error。解决办法为我们需要熟悉：maven依赖jar包优先级；
• 基础框架包的兼容性。比如：Springboot与Spring Cloud的兼容性。
• 框架升级会导致某些依赖版本发生变化，从而影响已经存在的代码或外部jar包。

Release Train	Boot Version
2021.0.x aka Jubilee	2.6.x
2020.0.x aka Ilford	2.4.x, 2.5.x (Starting with 2020.0.3)
Hoxton	2.2.x, 2.3.x (Starting with SR5)
Greenwich	2.1.x
Finchley	2.0.x
Edgware	1.5.x
Dalston	1.5.x

做好这件事情，需要有以下知识储备：

• 熟悉maven依赖jar包优先级，推荐阅读
• 依赖问题解决办法，点我阅读

Mysql驱动升级8.0

Zipkin Mysql拦截器

拦截器的作用：Mybatis动态SQL生成完毕后，会交给mysql驱动让数据库执行。sql拦截器是Mysql连接驱动提供的，所以在拦截器中可以完整地获取到即将交给数据库执行的SQL语句。

在不同的MySQL驱动版本中，拦截器名称有所不同：

• 5.x：StatementInterceptorV2
• 8.x：QueryInterceptor

数据库连接url中添加参数：queryInterceptors=brave.mysql8.TracingQueryInterceptor&exceptionInterceptors=brave.mysql8.TracingExceptionInterceptor，即可生效。

添加过参数后，zipkin就可以获取到sql执行历史了。如果我们仔细看下zipkin中拦截到数据库span，对于更好理解数据库的事务隔离将很有帮助。举一个select的例子，sql执行过程大概如下：

1. set session transaction read only;
2. set autocommit=0;
3. sql执行；
4. set autocommit=1;
5. set session transaction read write;

Connector8.0 sql拦截器，官方文档
gitee备用

Zipkin-mysql拦截器实现

官方提供了2个java文件，实现zipkin的SQL拦截：
TracingExceptionInterceptor.java

/*
 * Copyright 2013-2020 The OpenZipkin Authors
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License
 * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
 * or implied. See the License for the specific language governing permissions and limitations under
 * the License.
 */
package brave.mysql8;

import brave.Span;
import brave.propagation.ThreadLocalSpan;
import com.mysql.cj.exceptions.ExceptionInterceptor;
import com.mysql.cj.log.Log;
import java.sql.SQLException;
import java.util.Properties;

/**
 * A MySQL exception interceptor that will annotate spans with SQL error codes.
 *
 * <p>To use it, both TracingQueryInterceptor and TracingExceptionInterceptor must be added by
 * appending <code>?queryInterceptors=brave.mysql8.TracingQueryInterceptor&exceptionInterceptors=brave.mysql8.TracingExceptionInterceptor</code>.
 */
public class TracingExceptionInterceptor implements ExceptionInterceptor {

  @Override public ExceptionInterceptor init(Properties properties, Log log) {
    String queryInterceptors = properties.getProperty("queryInterceptors");
    if (queryInterceptors == null ||
      !queryInterceptors.contains(TracingQueryInterceptor.class.getName())) {
      throw new IllegalStateException(
        "TracingQueryInterceptor must be enabled to use TracingExceptionInterceptor.");
    }
    return new TracingExceptionInterceptor();
  }

  @Override public void destroy() {
    // Don't care
  }

  /**
   * Uses {@link ThreadLocalSpan} as there's no attribute namespace shared between callbacks, but
   * all callbacks happen on the same thread. The span will already have been created in {@link
   * TracingQueryInterceptor}.
   *
   * <p>Uses {@link ThreadLocalSpan#CURRENT_TRACER} and this interceptor initializes before
   * tracing.
   */
  @Override public Exception interceptException(Exception e) {
    Span span = ThreadLocalSpan.CURRENT_TRACER.remove();
    if (span == null || span.isNoop()) return null;

    span.error(e);
    if (e instanceof SQLException) {
      span.tag("error", Integer.toString(((SQLException) e).getErrorCode()));
    }

    span.finish();

    return null;
  }
}

racingQueryInterceptor.java

/*
 * Copyright 2013-2020 The OpenZipkin Authors
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License
 * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
 * or implied. See the License for the specific language governing permissions and limitations under
 * the License.
 */
package brave.mysql8;

import brave.Span;
import brave.propagation.ThreadLocalSpan;
import com.mysql.cj.MysqlConnection;
import com.mysql.cj.Query;
import com.mysql.cj.interceptors.QueryInterceptor;
import com.mysql.cj.jdbc.JdbcConnection;
import com.mysql.cj.log.Log;
import com.mysql.cj.protocol.Resultset;
import com.mysql.cj.protocol.ServerSession;
import java.net.URI;
import java.sql.SQLException;
import java.util.Properties;
import java.util.function.Supplier;

import static brave.Span.Kind.CLIENT;

/**
 * A MySQL query interceptor that will report to Zipkin how long each query takes.
 *
 * <p>To use it, append <code>?queryInterceptors=brave.mysql8.TracingQueryInterceptor</code>
 * to the end of the connection url. It is also highly recommended to add
 * <code>&exceptionInterceptors=brave.mysql8.TracingExceptionInterceptor</code> so errors are also
 * included in spans.
 */
public class TracingQueryInterceptor implements QueryInterceptor {

  /**
   * Uses {@link ThreadLocalSpan} as there's no attribute namespace shared between callbacks, but
   * all callbacks happen on the same thread.
   *
   * <p>Uses {@link ThreadLocalSpan#CURRENT_TRACER} and this interceptor initializes before
   * tracing.
   */
  @Override
  public <T extends Resultset> T preProcess(Supplier<String> sqlSupplier, Query interceptedQuery) {
    // Gets the next span (and places it in scope) so code between here and postProcess can read it
    Span span = ThreadLocalSpan.CURRENT_TRACER.next();
    if (span == null || span.isNoop()) return null;

    String sql = sqlSupplier.get();
    int spaceIndex = sql.indexOf(' '); // Allow span names of single-word statements like COMMIT
    span.kind(CLIENT).name(spaceIndex == -1 ? sql : sql.substring(0, spaceIndex));
    span.tag("sql.query", sql);
    parseServerIpAndPort(connection, span);
    span.start();
    return null;
  }

  private MysqlConnection connection;
  private boolean interceptingExceptions;

  @Override
  public <T extends Resultset> T postProcess(Supplier<String> sql, Query interceptedQuery,
    T originalResultSet, ServerSession serverSession) {
    if (interceptingExceptions && originalResultSet == null) {
      // Error case, the span will be finished in TracingExceptionInterceptor.
      return null;
    }
    Span span = ThreadLocalSpan.CURRENT_TRACER.remove();
    if (span == null || span.isNoop()) return null;

    span.finish();

    return null;
  }

  /**
   * MySQL exposes the host connecting to, but not the port. This attempts to get the port from the
   * JDBC URL. Ex. 5555 from {@code jdbc:mysql://localhost:5555/database}, or 3306 if absent.
   */
  static void parseServerIpAndPort(MysqlConnection connection, Span span) {
    try {
      URI url = URI.create(connection.getURL().substring(5)); // strip "jdbc:"
      String remoteServiceName = connection.getProperties().getProperty("zipkinServiceName");
      if (remoteServiceName == null || "".equals(remoteServiceName)) {
        String databaseName = getDatabaseName(connection);
        if (databaseName != null && !databaseName.isEmpty()) {
          remoteServiceName = "mysql-" + databaseName;
        } else {
          remoteServiceName = "mysql";
        }
      }
      span.remoteServiceName(remoteServiceName);
      String host = getHost(connection);
      if (host != null) {
        span.remoteIpAndPort(host, url.getPort() == -1 ? 3306 : url.getPort());
      }
    } catch (Exception e) {
      // remote address is optional
    }
  }

  private static String getDatabaseName(MysqlConnection connection) throws SQLException {
    if (connection instanceof JdbcConnection) {
      return ((JdbcConnection) connection).getCatalog();
    }
    return "";
  }

  private static String getHost(MysqlConnection connection) {
    if (!(connection instanceof JdbcConnection)) return null;
    return ((JdbcConnection) connection).getHost();
  }

  @Override
  public boolean executeTopLevelOnly() {
    return true;  // True means that we don't get notified about queries that other interceptors issue
  }

  @Override
  public QueryInterceptor init(MysqlConnection mysqlConnection, Properties properties,
    Log log) {
    String exceptionInterceptors = properties.getProperty("exceptionInterceptors");
    TracingQueryInterceptor interceptor = new TracingQueryInterceptor();
    interceptor.connection = mysqlConnection;
    interceptor.interceptingExceptions = exceptionInterceptors != null &&
      exceptionInterceptors.contains(TracingExceptionInterceptor.class.getName());
    if (!interceptor.interceptingExceptions) {
      log.logWarn("TracingExceptionInterceptor not enabled. It is highly recommended to "
        + "enable it for error logging to Zipkin.");
    }
    return interceptor;
  }

  @Override
  public void destroy() {
    // Don't care
  }
}