Make Deno MySQL driver works better

Deno is a JavaScript and TypeScript runtime similar to Node.js, built on Rust and the V8 JavaScript engine.

Deno MySQL driver (also be called as deno_mysql) is a library that allows us to connect our deno application to SQL servers similar to MySQL or MariaDB.

However, deno_mysql is not fully compatible with mysql protocol, causing it not works with all MySQL versions and some MySQL-compatible database. such as TiDB.

In this post, I will introduce two compatible issues of the deno_mysql v2.10.3 and how to solve them.

Authentication Method Mismatch

Authentication Method Mismatch is a new feature of MySQL 8.0. It is a phase of connection which is used to prevent the downgrade attack of the authentication method. When the client and server use different authentication methods, the server will send an AuthSwitchRequest packet to the client. The client can then choose to use the authentication method that the server supports.

deno_mysql does not support the authentication method mismatch, so it may fail to connect to MySQL 8.0.x and other MySQL-compatible databases which trigger the mismatch.

switch (authResult) {
    case AuthResult.AuthMoreRequired:
          const adaptedPlugin = (authPlugin as any)[handshakePacket.authPluginName];
          handler = adaptedPlugin;
          break;
    case AuthResult.MethodMismatch:
          // TODO: Negotiate
          throw new Error("Currently cannot support auth method mismatch!");
}

Here is an example code to support it:

1. Parse AuthSwitchRequest
2. Send AuthSwitchResponse with authData
3. Do not allow to change the auth plugin more than once

case AuthResult.MethodMismatch:
    // 1. parse AuthSwitchRequest
    const authSwitch = parseAuthSwitch(receive.body); 
    // If CLIENT_PLUGIN_AUTH capability is not supported, no new cipher is sent and we have to keep using the cipher sent in the init packet. 
    if ( authSwitch.authPluginData === undefined || authSwitch.authPluginData.length === 0 ) { 
        authSwitch.authPluginData = handshakePacket.seed; 
    } 
    // 2. build authData
    let authData; 
    if (password) { 
        authData = auth(authSwitch.authPluginName, password, authSwitch.authPluginData); 
    } else { 
        authData = Uint8Array.from([]); 
    } 
    // 3. send AuthSwitchResponse with authData
    await new SendPacket(authData, receive.header.no + 1).send(this.conn); 
    // 4. do not allow to change the auth plugin more than once
    receive = await this.nextPacket(); 
    const authSwitch2 = parseAuthSwitch(receive.body); 
    if (authSwitch2.authPluginName !== "") { 
        throw new Error( "Do not allow to change the auth plugin more than once!"); 
    }

1. Parse AuthSwitchRequest

Once Auth Method Mismatch, server will send a AuthSwitchRequest to client. We need to parse the AuthSwitchRequest follow the payload:

2. Send AuthSwitchResponse with authData

Client will select the authentication method that the server supports first. deno_mysql supports mysql_native_password and caching_sha2_password now. So, it will throw error if server does not support both.

Then client will encode password and plugin provided data with the authentication method. The result is authData.

At last, client need to send AuthSwitchResponse to server with the authData.

3. Change the auth plugin only once

Authentication method mismatch is not allowed to occur more than once. It is not a part of MySQL protocol. go-sql-driver also has the same rule.

CLIENT_DEPRECATE_EOF

MySQL deprecated EOF packet since MySQL v5.7.5 and CLIENT_DEPRECATE_EOF was introduced since then.

Once Client and Server both support CLIENT_DEPRECATE_EOF, server will:

1. Send OK packet rather than EOF packet in some cases.
2. Not send EOF packet in some cases.

deno_mysql does support the CLIENT_DEPRECATE_EOF flag. However, there are some problems of the implementation. it causes the deno_mysql not works with the latest mariadb and other MySQL-compatible databases which support CLIENT_DEPRECATE_EOF

1. Deprecated EOF packet without CLIENT_DEPRECATE_EOF flag

deno_mysql judge the EOF packet by the version of the server. For example, it will expect an EOF packet for the version less than 5.7.

if (this.lessThan5_7() || this.isMariaDBAndVersion10_0Or10_1()) {
    // EOF(less than 5.7 or mariadb version is 10.0 or 10.1)
    receive = await this.nextPacket();
    if (receive.type !== PacketType.EOF_Packet) {
        throw new ProtocolError();
    }
}

It is too hacker and not reliable. It may block some MySQL-compatible databases which do not follow the version rule. The best practice is to judge the EOF packet by the CLIENT_DEPRECATE_EOF flag. Here is an example:

if (!(this.capabilities & ServerCapabilities.CLIENT_DEPRECATE_EOF)) {
    receive = await this.nextPacket();
    if (receive.type !== PacketType.EOF_Packet) {
          throw new ProtocolError();
    }
}

2. EOF packet need to be replaced by OK packet

deno_mysql always expect an EOF packet after the result set.

if (!iterator) {
        while (true) {
          receive = await this.nextPacket();        
          if (receive.type === PacketType.EOF_Packet) {
            break;
          } else {
            const row = parseRow(receive.body, fields);
            rows.push(row);
          }
        }
        return { rows, fields };
      }

EOF packet's header is 0xFE, OK packet's header can be either 0x00 or 0xFE. When the client and server both support CLIENT_DEPRECATE_EOF, serve will send OK packet. It will not work once the server send OK packet with 0x00 header.

We need to support OK packet too:

        while (true) {
          receive = await this.nextPacket();
          // OK_Packet when CLIENT_DEPRECATE_EOF is set. OK_Packet can be 0xfe or 0x00
          if ( receive.type === PacketType.EOF_Packet ||receive.type === PacketType.OK_Packet ) {
            break;
          } else {
            const row = parseRow(receive.body, fields);
            rows.push(row);
          }
        }
        return { rows, fields };
      }

3. More things to do

There are more things to do if deno_mysql want to support CLIENT_DEPRECATE_EOF. For example, the prepared statement protocol was introduced in MySQL 4.1. deno_mysql need to handle the CLIENT_DEPRECATE_EOF once it support this ability.

In fact, there is no clear solution to support CLIENT_DEPRECATE_EOF. MariaDB and MySQL also behavior different with CLIENT_DEPRECATE_EOF flag. It is the main reason that some driver does not support CLIENT_DEPRECATE_EOF, for example, the go-sql-driver.

For deno_mysql, fully supporting CLIENT_DEPRECATE_EOF may be too far ahead.

Test with TiDB

TiDB is fully compatible with MySQL protocol. There is also a TiDB Cloud.

You may find deno_mysql <= v2.10.3 can't work with TiDB <= v6.3 and TiDB Cloud. The former because of the problem of CLIENT_DEPRECATE_EOF and the latter because of the unsupported of Authentication Method Mismatch.

Now, deno_mysql is able to work with all the versions of TiDB and the TiDB Cloud.