DEV Community: Xiang Zhang

Connect TiDB Serverless From Databricks

Xiang Zhang — Thu, 22 Jun 2023 04:08:48 +0000

TiDB Serverless by default allows access from Internet and requires secure connection. To connect TiDB Serverless clusters from Databricks, you need to turn on SSL connection from the client.

github_events = spark.read
  .format("jdbc")
  .option("url", "jdbc:mysql://gateway01.us-west-2.prod.aws.tidbcloud.com:4000/sample_data")
  .option("dbtable", "github_events")
  .option("user", <username>)
  .option("password", <password>)
  .load()

This is a simple Python snippet to load the github_events sample table from a TiDB Serverless cluster. It will return a error:

ERROR 1105 (HY000): Connections using insecure transport are prohibited. See https://docs.pingcap.com/tidbcloud/secure-connections-to-serverless-tier-clusters

By default the JDBC driver doesn't use SSL to connect. According to https://docs.databricks.com/external-data/mysql.html, seems the underlying driver is mariadb-connector/j, so just add the corresponding SSL option to our snippet:

github_events = spark.read
  .format("jdbc")
  .option("url", "jdbc:mysql://gateway01.us-west-2.prod.aws.tidbcloud.com:4000/sample_data?useSsl=true")
  .option("dbtable", "github_events")
  .option("user", <username>)
  .option("password", <password>)
  .load()

Only the useSsl=true option is needed. There is no need to specify any CA file since currently TiDB Serverless uses Let's Encrypt ISRG Root X1 as the signing authority. Usually, it's bundled into JDKs.

TiDB Serverless also supports private link connection. It helps if you need private connections.

Connecting Deepnote to TiDB Cloud Serverless Cluster

Xiang Zhang — Thu, 29 Dec 2022 08:50:02 +0000

Deepnote is a new kind of data notebook beyond Jupyter. While investigating how to connect Deepnote to TiDB Cloud Serverless Tier(a serverless MySQL-compatible cloud database and TiDB Cloud Serverless Tier enforce TLS connections), I found it's not that easy.

There are two ways connecting Deepnote notebooks to MySQL, one is using Deepnote's integrations, one is directly from code.

Using integration

Deepnote provides a built-in MySQL integration, you could use it to connect to MySQL. But unfortunately, when it involves TLS, it just does not work. As you could see in the image, it says "Connections between Deepnote and your data source are encrypted by SSL (TLS)." but there is no place to specify server's cert. Maybe it uses the system bundled CAs to verify? If it does so, TiDB Cloud Serverless Tier uses ISRG X1 root and it could definitely work. But not :-(

Connecting from code

Integration does not work, but nothing prevents us connecting directly from code. To connect to MySQL, first you need a driver. There are three popular Python MySQL drivers:

mysqlclient
PyMySQL
mysql-connector-python.

PyMySQL would be easy to install since it's pure Python, you just need to

!pip install PyMySQL

at top of the notebook. But I used to use mysqlclient. It's not that easy to make everything work if you want to use mysqlclient.

First, simply

!pip install mysqlclient

won't work. mysqlclient is a Python wrapper of libmysqlclient, so you need to install libmysqlclient first. According to https://deepnote.com/docs/custom-environments#default-environment , Deepnote use Debian Buster, so the installation would be

!apt install -y default-libmysqlclient-dev
!pip install mysqlclient

Then when using mysqlclient to securely connecting to MySQL, there are also two points to notice:

We are in Debian Buster, the installed libmysqlclient is actually libmariadbclient. libmaradbclient's interface is not same as libmysqlclient. It leads to mysqlclient doesn't accept ssl_mode tls argument like in other platforms.
We want to connect securely, definitely we want to verify the server's cert. As I said above, TiDB Cloud Serverless Tier uses ISRG X1 root, which is commonly in system built-in CA bundle. You could specify the CA as

ssl={
      "ca": "/etc/ssl/certs/ca-certificates.crt"
    }

For more info about connecting to TiDB Cloud Serverless Cluster securely, you could refer to https://docs.pingcap.com/tidbcloud/secure-connections-to-serverless-tier-clusters .

UPDATE on 2023-03-03: DeepNote has fixed the problem and complete a whole feature for connecting securely https://deepnote.com/docs/securing-connections.

Use JDBC to connect TiDB Cloud through TLS

Xiang Zhang — Mon, 05 Sep 2022 02:48:16 +0000

At the time of writing, TiDB Cloud uses the default configurations to deploy clusters(dedicated tier), supporting TLSv1.1, TLSv1.2 and TLSv1.3. When users use MySQL Connector/J to connect TiDB Cloud, they need to take care of which JDK version and JDBC driver version are in use. In this article, JDK 17 is used for test.

JDBC 8.0.26

Using the simplest DSN(with only username and password) to connect TiDB Cloud clusters

jdbc:mysql://<host>:4000/test?user=root&password=<password>

throws error like

Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
        at java.base/sun.security.ssl.HandshakeContext.<init>(HandshakeContext.java:172)
        at java.base/sun.security.ssl.ClientHandshakeContext.<init>(ClientHandshakeContext.java:103)
        at java.base/sun.security.ssl.TransportContext.kickstart(TransportContext.java:240)
        at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:443)
        at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:421)
        at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:320)
        at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:194)
        at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:101)
        at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:308)

The reason for the error is that JDBC 8.0.26 will by default use TLSv1 and TLSv1.1 against low version MySQL server. Although TiDB Cloud supports TLSv1.1, but high version JDK doesn't support TLSv1.1 any more. Let's see the critical code path

private static String[] getAllowedProtocols(PropertySet pset, ServerVersion serverVersion, String[] socketProtocols) {
    String[] tryProtocols = null;

    // If enabledTLSProtocols configuration option is set, overriding the default TLS version restrictions.
    // This allows enabling TLSv1.2 for self-compiled MySQL versions supporting it, as well as the ability
    // for users to restrict TLS connections to approved protocols (e.g., prohibiting TLSv1) on the client side.
    String enabledTLSProtocols = pset.getStringProperty(PropertyKey.enabledTLSProtocols).getValue();
    if (enabledTLSProtocols != null && enabledTLSProtocols.length() > 0) {
        tryProtocols = enabledTLSProtocols.split("\\s*,\\s*");
    }
    // It is problematic to enable TLSv1.2 on the client side when the server is compiled with yaSSL. When client attempts to connect with
    // TLSv1.2 yaSSL just closes the socket instead of re-attempting handshake with lower TLS version. So here we allow all protocols only
    // for server versions which are known to be compiled with OpenSSL.
    else if (serverVersion == null) {
        // X Protocol doesn't provide server version, but we prefer to use most recent TLS version, though it also means that X Protocol
        // connection to old MySQL 5.7 GPL releases will fail by default, user must use enabledTLSProtocols=TLSv1.1 to connect them.
        tryProtocols = TLS_PROTOCOLS;
    } else if (serverVersion.meetsMinimum(new ServerVersion(5, 7, 28))
            || serverVersion.meetsMinimum(new ServerVersion(5, 6, 46)) && !serverVersion.meetsMinimum(new ServerVersion(5, 7, 0))
            || serverVersion.meetsMinimum(new ServerVersion(5, 6, 0)) && Util.isEnterpriseEdition(serverVersion.toString())) {
        tryProtocols = TLS_PROTOCOLS;
    } else {
        // allow only TLSv1 and TLSv1.1 for other server versions by default
        tryProtocols = new String[] { TLSv1_1, TLSv1 };
    }

    List<String> configuredProtocols = new ArrayList<>(Arrays.asList(tryProtocols));
    List<String> jvmSupportedProtocols = Arrays.asList(socketProtocols);

    List<String> allowedProtocols = new ArrayList<>();
    for (String protocol : TLS_PROTOCOLS) {
        if (jvmSupportedProtocols.contains(protocol) && configuredProtocols.contains(protocol)) {
            allowedProtocols.add(protocol);
        }
    }
    return allowedProtocols.toArray(new String[0]);

}

getAllowedProtocols calculates the possible TLS protocols used in the handshake phase. The version TiDB Cloud returns is 5.7.25-TiDB-v6.1.0, so the code finally goes into the else branch, protocols being TLSv1 and TLSv1.1(enabledTLSProtocols parameter in DSN is also treated here).

private static List<ProtocolVersion> getActiveProtocols(
        List<ProtocolVersion> enabledProtocols,
        List<CipherSuite> enabledCipherSuites,
        AlgorithmConstraints algorithmConstraints) {
    boolean enabledSSL20Hello = false;
    ArrayList<ProtocolVersion> protocols = new ArrayList<>(4);
    for (ProtocolVersion protocol : enabledProtocols) {
        if (!enabledSSL20Hello && protocol == ProtocolVersion.SSL20Hello) {
            enabledSSL20Hello = true;
            continue;
        }

        if (!algorithmConstraints.permits(
                EnumSet.of(CryptoPrimitive.KEY_AGREEMENT),
                protocol.name, null)) {
            // Ignore disabled protocol.
            continue;
        }

getActiveProtocols calculates the final protocol and cipher. No matter TLSv1 or TLSv1.1, it will be rejected in algorithmConstraints.permits and then getActiveProtocols returns an empty list.

@Override
public boolean permits(Set<CryptoPrimitive> primitives,
        String algorithm, AlgorithmParameters parameters) {

    boolean permitted = true;

    if (peerSpecifiedConstraints != null) {
        permitted = peerSpecifiedConstraints.permits(
                                primitives, algorithm, parameters);
    }

    if (permitted && userSpecifiedConstraints != null) {
        permitted = userSpecifiedConstraints.permits(
                                primitives, algorithm, parameters);
    }

    if (permitted) {
        permitted = tlsDisabledAlgConstraints.permits(
                                primitives, algorithm, parameters);
    }

    if (permitted && enabledX509DisabledAlgConstraints) {
        permitted = x509DisabledAlgConstraints.permits(
                                primitives, algorithm, parameters);
    }

    return permitted;
}

TLSv1.1 will be rejected at tlsDisabledAlgConstraints.permits(primitives, algorithm, parameters);. tlsDisabledAlgConstraints is checked against jdk.tls.disabledAlgorithms in java.security file. For JDK 11, its value is

jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \
     DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \
     include jdk.disabled.namedCurves

JDBC 8.0.29

Compared with 8.0.26, getAllowedProtocols changed in 8.0.29, returns TLSv1.2 and TLSv1.3. So normally you could connect successfully to TiDB Cloud.

private static String[] getAllowedProtocols(PropertySet pset, @SuppressWarnings("unused") ServerVersion serverVersion, String[] socketProtocols) {
    List<String> tryProtocols = null;

    RuntimeProperty<String> tlsVersions = pset.getStringProperty(PropertyKey.tlsVersions);
    if (tlsVersions != null && tlsVersions.isExplicitlySet()) {
        // If tlsVersions configuration option is set then override the default TLS versions restriction.
        if (tlsVersions.getValue() == null) {
            throw ExceptionFactory.createException(SSLParamsException.class,
                    "Specified list of TLS versions is empty. Accepted values are TLSv1.2 and TLSv1.3.");
        }
        tryProtocols = getValidProtocols(tlsVersions.getValue().split("\\s*,\\s*"));
    } else {
        tryProtocols = new ArrayList<>(Arrays.asList(VALID_TLS_PROTOCOLS));
    }

    List<String> jvmSupportedProtocols = Arrays.asList(socketProtocols);
    List<String> allowedProtocols = new ArrayList<>();
    for (String protocol : tryProtocols) {
        if (jvmSupportedProtocols.contains(protocol)) {
            allowedProtocols.add(protocol);
        }
    }
    return allowedProtocols.toArray(new String[0]);
}

JDBC 5.1.49

protected static void transformSocketToSSLSocket(MysqlIO mysqlIO) throws SQLException {
    SocketFactory sslFact = new StandardSSLSocketFactory(getSSLSocketFactoryDefaultOrConfigured(mysqlIO), mysqlIO.socketFactory, mysqlIO.mysqlConnection);

    try {
        mysqlIO.mysqlConnection = sslFact.connect(mysqlIO.host, mysqlIO.port, null);

        String[] tryProtocols = null;

        // If enabledTLSProtocols configuration option is set then override the default TLS version restrictions. This allows enabling TLSv1.2 for
        // self-compiled MySQL versions supporting it, as well as the ability for users to restrict TLS connections to approved protocols (e.g., prohibiting
        // TLSv1) on the client side.
        // Note that it is problematic to enable TLSv1.2 on the client side when the server is compiled with yaSSL. When client attempts to connect with
        // TLSv1.2 yaSSL just closes the socket instead of re-attempting handshake with lower TLS version.
        String enabledTLSProtocols = mysqlIO.connection.getEnabledTLSProtocols();
        if (enabledTLSProtocols != null && enabledTLSProtocols.length() > 0) {
            tryProtocols = enabledTLSProtocols.split("\\s*,\\s*");
        } else if (mysqlIO.versionMeetsMinimum(5, 7, 28) || mysqlIO.versionMeetsMinimum(5, 6, 46) && !mysqlIO.versionMeetsMinimum(5, 7, 0)
                || mysqlIO.versionMeetsMinimum(5, 6, 0) && Util.isEnterpriseEdition(mysqlIO.getServerVersion())) {
            // allow all known TLS versions for this subset of server versions by default
            tryProtocols = TLS_PROTOCOLS;
        } else {
            // allow TLSv1 and TLSv1.1 for all server versions by default
            tryProtocols = new String[] { TLSv1_1, TLSv1 };

        }

Although name is different, but 5.1.49 shares the same logic with 8.0.26, goes into the else branch and returns only TLSv1/TLSv1.1.

Conclusion

For failed versions, we could concat enabledTLSProtocols=TLSv1.2,TLSv1.3 in the DSN to force JDBC driver choose higher version TLS protocols. To connect consistently, you could always add the parameter. But from 8.0.28, enabledTLSProtocols is changed to tlsVersions and remains an alias. It might be removed in the future.

UPDATE on 2022/09/19:

TiDB Cloud dedicated clusters recently provide their private CA for users to download and verify. They recommend using verify_identity argument in DSN. One thing to note is that the certs of the clusters use SAN, to do identity verification, you need to use at least MySQL Connector/J 8.0.22. From this version, SAN is supported.

UPDATE on 2024/01/04

TiDB kernel advertise their MySQL version as 8.0.11 from v7.4.0. So if you connect to versions later than this, even JDBC 8.0.26 could connect easily. TiDB Serverless currently use TiDB kernel v7.1.0, but it advertise its MySQL version as 5.7.28 to provide more convenience for old version JDBC drivers.