Make Deno MySQL driver works better
Tags: deno, protocal, mysql, opensource
Published: 2022-12-01 11:07:09



[**Deno**](http://deno.land/) is a JavaScript and TypeScript runtime similar to Node.js, built on Rust and the V8 JavaScript engine.

[**Deno MySQL driver**](https://github.com/denodrivers/mysql) (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](https://www.pingcap.com/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](https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html#sect_protocol_connection_phase_auth_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.

![Authentication Method Mismatch](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/af5sarmn6zuj7iugc3yq.png)

`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:

| Type        | Name                 | Description                                           |
| ----------- | -------------------- | ----------------------------------------------------- |
| int<1>      | 0xFE (254)           | status tag                                            |
| string[NUL] | plugin name          | name of the client authentication plugin to switch to |
| string[EOF] | plugin provided data | Initial authentication data for that client plugin    |

### 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](https://github.com/go-sql-driver/mysql) also has the same rule.


## CLIENT_DEPRECATE_EOF

MySQL deprecated EOF packet since MySQL v5.7.5 and [CLIENT_DEPRECATE_EOF](https://dev.mysql.com/doc/dev/mysql-server/latest/group__group__cs__capabilities__flags.html#gaad8e6e886899e90e820d6c2e0248469d) 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](https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_command_phase_ps.html) 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](https://github.com/go-sql-driver/mysql/pull/1153).

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


## Test with TiDB

[TiDB](https://www.pingcap.com/tidb/) is fully compatible with MySQL protocol. There is also a [TiDB Cloud](https://tidbcloud.com/).

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.