search

readysettech / readyset

Readyset is a MySQL and Postgres wire-compatible caching layer that sits in front of existing databases to speed up queries and horizontally scale read throughput. Under the hood, ReadySet caches the results of cached select statements and incrementally updates these results over time as the underlying data changes.
https://readyset.io
Other
4.54k stars 125 forks source link

mariadb jdbc client sends incorrect `SET @@tx_isolation` command #1385

Closed jasobrown-rs closed 1 month ago

jasobrown-rs commented 1 month ago

Summary

A user application is using the mariadb jdbc client driver to connect to readyset (and an upstream RDS MySQL instance). As the application is connecting, one of the connections got this error: `Unknown system variable 'tx_isolation`` (stack trace below).

This app uses the mariadb 2.7.10 driver, as the 3.x+ series does not support mysql.

Looking at the driver source for that function:

  public int getTransactionIsolation() throws SQLException {
    Statement stmt = createStatement();
    String sql = "SELECT @@tx_isolation";

    if (!protocol.isServerMariaDb()) {
      if ((protocol.getMajorServerVersion() >= 8 && protocol.versionGreaterOrEqual(8, 0, 3))
          || (protocol.getMajorServerVersion() < 8 && protocol.versionGreaterOrEqual(5, 7, 20))) {
        sql = "SELECT @@transaction_isolation";
      }
    }

    ResultSet rs = stmt.executeQuery(sql);
.....

the @@tx_isolation is sent only if the driver thinks the database is an actual MariaDB (modulo a version check). Long story short, the driver thinks readyset is actually a MariaDB because of this code when interpreting the server capabilities on connection set up:

    // since MariaDB 10.2
    if ((serverCapabilities4FirstBytes & MariaDbServerCapabilities.CLIENT_MYSQL) == 0) {
      serverCapabilities =
          (serverCapabilities4FirstBytes & 0xffffffffL) + (mariaDbAdditionalCapacities << 32);
      serverMariaDb = true;
    } else {
      serverCapabilities = serverCapabilities4FirstBytes & 0xffffffffL;
    }

MariaDbServerCapabilities.CLIENT_MYSQL is a flag in the Mysql capabilities flags that MariaDB has reappropriated; the original flag name under mysql was CLIENT_LONG_PASSWORD. Since modern MySQL instances always set that value to 1, MariaDB basically reuses that flag as a hint to indicate if the server is MySQL (as the value will be 1, or if it's MariaDB, which will set the value to 0. (This is what CLIENT_MYSQL means).

The original meaning of that flag, CLIENT_LONG_FLAG, was to indicate if the server could use modern password hashing algos, 41-bytes in length, indicated by the flag being on (1), or if the server used an older hashing algo, 20-bytes. The original implementation of the 41-byte algo is the plugin named mysql_native_password. Readyset already implements this algo, however we never set the CLIENT_LONG_PASSWORD correctly in the server capabilities flag that we return to the client.

Thus, by adding/setting the flag correctly in the server capabilities, we can correctly indicate that we support "modern" 41-byte hashing algos as well as satisfying recent mariadb clients.

Stack trace as reported by user:

{:via
 [{:type java.sql.SQLException,
   :message "Connections could not be acquired from the underlying database!",
   :at [com.mchange.v2.sql.SqlUtils toSQLException "SqlUtils.java" 118]}
  {:type com.mchange.v2.resourcepool.CannotAcquireResourceException,
   :message "A ResourcePool could not acquire a resource from its primary factory or source.",
   :at [com.mchange.v2.resourcepool.BasicResourcePool awaitAvailable "BasicResourcePool.java" 1507]}
  {:type java.sql.SQLTransientConnectionException,
   :message "(conn=8) Unknown system variable 'tx_isolation'",
   :at [org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory createException "ExceptionFactory.java" 79]}
  {:type org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException,
   :message "Unknown system variable 'tx_isolation'",
   :at [org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException of "MariaDbSqlException.java" 34]}
  {:type java.sql.SQLException,
   :message "Unknown system variable 'tx_isolation'",
   :at [org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol readErrorPacket "AbstractQueryProtocol.java" 1693]}],
 :trace
 [[org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol readErrorPacket "AbstractQueryProtocol.java" 1693]
  [org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol readPacket "AbstractQueryProtocol.java" 1555]
  [org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol getResult "AbstractQueryProtocol.java" 1518]
  [org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol executeQuery "AbstractQueryProtocol.java" 257]
  [org.mariadb.jdbc.MariaDbStatement executeInternal "MariaDbStatement.java" 356]
  [org.mariadb.jdbc.MariaDbStatement executeQuery "MariaDbStatement.java" 611]
  [org.mariadb.jdbc.MariaDbConnection getTransactionIsolation "MariaDbConnection.java" 920]
  [com.mchange.v2.c3p0.impl.NewPooledConnection <init> "NewPooledConnection.java" 120]
  [com.mchange.v2.c3p0.WrapperConnectionPoolDataSource getPooledConnection "WrapperConnectionPoolDataSource.java" 181]
  [com.mchange.v2.c3p0.WrapperConnectionPoolDataSource getPooledConnection "WrapperConnectionPoolDataSource.java" 147]
  [com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager