Monday, May 6, 2024

MySQL Protocol: Collations

This story starts with a pull request for go-mysql to allow setting the collation in auth handshake that I was reviewing. The reason why the author wanted to do this is to speedup the connection setup as he has a latency sensitive application and a lot of connection setups and tear downs.

While looking at this I noticed that the collation would be stored in a single byte. However the list of supported collations shows collations with an ID that’s more than 255.

mysql> SELECT MIN(ID),MAX(ID) FROM information_schema.collations;
| MIN(ID) | MAX(ID) |
|       1 |     323 |
1 row in set (0.00 sec)

The protocol documentation for Protocol::HandshakeResponse41 says that the value sent here is only the lower 8-bits.

So I was wondering how do other connectors send this to the server? Are the other 8-bits sent elsewhere in the protocol?

So I used MySQL Connector/Python to try this out.

import mysql.connector
c = mysql.connector.connect(
cur = c.cursor()
cur.execute("SHOW SESSION VARIABLES LIKE '%collation%'");
for col in cur:

Here utf8mb4_ja_0900_as_cs is used which has collation ID 303 or 0x12F in hex. Another way to write this down is 0x01 0x2F

This is how this looked in Wireshark:

So the collation is set to latin1_bin (47) instead of utf8mb4_ja_0900_as_cs (303). Note that 47 in binary is 0x2F. And then we can spot the other part of this as 0x01 in the Unused field, for which the docs say it should be a filler of all zeros.

So I concluded that the protocol docs on this are probably outdated and I started a few merge/pull requests for this:

First this MR for Wireshark to have it decode the 2 bytes that we have seen as a single collation number. And then this PR for mysql-server to update the docs. I also created this PR for MySQL Connector/Python to update the comments in the code.

After doing this I also added a comment to the MariaDB docs for the protocol. And here Vladislav Vaintroub quickly responded with a conclusion which was different than my conclusion. He checked the code to see what the server (both MySQL and MariaDB) is reading and there it is only reading one byte.

After that I have created another MR for Wireshark to revert some of my changes. I also added some comments to my previous PRs/bugreports.

I also did some more testing with Connector/Python and found out that depending on whether or not the C Extension is used and also depending on the version that it might set the collation during the handshake but also after the handshake with SET NAMES. And not just once, it sometimes does it twice. And the second time it only specifies the character set and not the collation, causing the collation to reset to the default one for that character set. I’ve filed this bug for that.

The current state is that the collation can only be set during the handshake if it is <= 255. For other cases you need to send a SET NAMES statement. This isn’t great as this adds at least one roundtrip to the connection setup.

As all character sets have a default collation that’s <=255 this is only an issue with some of the non-default collations. It might also become an issue collations that might be added in the future.

mysql> SELECT MAX(id) FROM information_schema.collations WHERE IS_DEFAULT='YES';
| MAX(id) |
|     255 |
1 row in set (0.01 sec)

Another indication on why this only affects few users is the MySQL C API.

This is how one can set the character set:

mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "latin1");
mysql_real_connect(&mysql, ...);

Note that MySQL doesn’t send this latin1 string to the server, it looks up the default collation and sends the ID of that to the server instead. So anything that uses the C API isn’t affected by this as it has to use SET NAMES to set non-standard collations.

So I assume that the field in the handshake packet either wasn’t considered once collations with ID’s of more than 255 were added or it was a way to send the character set in a more efficient way.

Note that character sets’s have only names and no numeric ID.

As MySQL Connector/Python takes a collation name, but sends a collation ID it needs to keep a list so it can lookup the ID before connecting to the server. So to connect with a new collation you might have to update the MySQL Connector/Python version.

Monday, November 6, 2023

Notes on Compression in the MySQL Protocol

The MySQL Protocol is the network protocol that is used between a MySQL server and a client. This is called the “classic” protocol as there is now a newer protobuf based protocol called X Protocol. However the “classic” protocol is used by many database drivers, applications, etc. and also by MySQL Replication.

The MySQL Protocol has the option to compress network traffic. Most client libraries don’t enable this by default as this increases CPU usage.

Some history

Initially this was added in MySQL 3.22 (based on historical release notes) and was based on zlib.

Then in MySQL 8.0.18 a second compression algorithm, Zstandard, was added based on a contribution by Facebook.

So zlib has been there basically forever and Zstandard support is new-ish (8.0.18 was released in October 2019).

Why you might want to use compression.

There are multiple usecases that are often mentioned when talking about the compressed protocol. The first one is slow connections. This could, for example, be a DBA doing remote work.

The second usecase is metered connections. This could be a DBA using a mobile connection or to reduce network transfer cost in a Cloud environment.

Basic usage

The usage is as simple as mysql --compression-algorithms=zstd .... Instead of zstd you can also use zlib or even provide both (zstd,zlib) and see what’s available on the server. Most other clients like mysqldump, MySQL Shell, etc also support this setting. Older versions use mysql --compress ... which enables zlib based compression. What is used in the end depends on what the server supports.

For zstd there is a --zstd-compression-level option that allows you to set a level between 1 and 22, where the default is 3. This allows you to better balance between faster and better compression.

Once connected you could check the Compression, Compression_algorithm and Compression_level status variables to verify what is being used.

sql> SHOW STATUS LIKE 'Compression%';
| Variable_name         | Value |
| Compression           | ON    |
| Compression_algorithm | zstd  |
| Compression_level     | 3     |
3 rows in set (0.0043 sec)

In MySQL Shell the \status command also provides you with this information:

sql> \status
MySQL Shell version 8.2.0

Connection Id:                30
Current schema:               test
Current user:                 root@localhost
SSL:                          Cipher in use: TLS_AES_128_GCM_SHA256 TLSv1.3
Using delimiter:              ;
Server version:               8.2.0 MySQL Community Server - GPL
Protocol version:             Compressed 10                               <------------------
Client library:               8.2.0
Connection:          via TCP/IP
TCP port:                     3306
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Enabled (zstd)                              <------------------
Uptime:                       1 day 3 hours 20 min 4.0000 sec

Threads: 2  Questions: 200  Slow queries: 0  Opens: 348  Flush tables: 3  Open tables: 266  Queries per second avg: 0.002

The connectors for your favorite programming language probably have settings for this as well. Some might only allow you to enable compression with zlib and not yet offer you the option for zstd.

Language Driver zlib zstd Setting
Python Connector/Python compress=True
Java Connector/J ❌, Bug #112732 useCompression=true
Go go-sql-driver/mysql #24 -
Perl DBD::mysql mysql_compression=zlib

Note that many drivers are based on libmysqlclient and only require minimal changes to support this.

A look at the internals


When establishing a connection both the client and server send a set of capability flags. There are two flags that are used for compression: First there is CLIENT_COMPRESS that is used to advertise support for zlib and then there is CLIENT_ZSTD_COMPRESSION_ALGORITHM that is used to advertise support for Zstandard. If both the client and the server have a flag set then that feature is used.

A bit of a special case is possible when both the client and server set both of the compression flags, in that case zlib is used. You can try this out by running --compression-algorithms=zstd,zlib and looking at the status variables.

If the client has the CLIENT_ZSTD_COMPRESSION_ALGORITHM flag set then the Protocol::HandshakeResponse41 also contains a compression level for Zstandard.

Packet compression

The MySQL Protocol consists of “MySQL Packets” that are send between the client and server. When compression is used this is added as a separate layer in the network stack.

So the stack basically looks something like this:

|          MySQL Protocol           |
|      MySQL Compressed Protocol    |
|          TLS (Optional)           |
| TCP/IP | Unix Socket |   .....    |

And yes, if you want you can use the compressed protocol over a Unix Socket. But I can’t think of a good reason to do so.

The ..... above is because of other connection options on Windows, like Shared memory and Named Pipes.

As this is a separate layer there isn’t a 1-to-1 mapping between compressed packets and regular packets.

So what do these MySQL Compressed Protocol packets look like?

Let’s start by looking at the header.

| <compressed_length[3]> | <compressed_sequence[1]> | <uncompressed_length[3] |

Example values (hex):
| 15 00 00               | 00                       | 000000                  |

So here we have: - Compressed Length: 21. This is 15 00 00 in hex. This is a 24 bit (3 byte) integer. - Compressed Sequence: 0 - Uncompressed Length: 0

If you know the regular MySQL Protocol this probably looks very similar, including the use of the somewhat excentric 24 bit integer.

That the Uncompressed Length is set to zero means that for this packet the payload isn’t actually compressed. This happens when the side that does compression doesn’t think it makes sense to compress the payload. MySQL doesn’t compress the payload if it is less than MIN_COMPRESS_LENGTH, which is 50 bytes.

After the header we get the payload which depending on the capability flags might be compressed with zlib or Zstandard or might be uncompressed if the Uncompressed Length is set to 0.

This is how this looks in Wireshark:

Wireshark: Compressed Packet

A compressed protocol packet can also contain multiple (regular) MySQL packets:

Wireshark: Multiple compressed packets

Here there are eight regular MySQL Packets inside the compressed packet.

Wireshark can uncompress the compressed payload (both zlib and zstd). This is why it is able to show you what’s in the compressed packet. On the bottom you can see “Fram (202 bytes)” and “compressed data (147 bytes)”.

Wireshark: Uncompressed payload

For this packet we have: - Compressed Length: 129 bytes - Uncompressed Length: 147 bytes - TCP Payload Length: 136 bytes (this matches our expectation: 129 bytes payload + 7 byte header)

So instead of sending 147 bytes we sent 136 bytes. We saved 11 bytes (~7.5%).

The actual compression ratio depends on the size (bigger is better), randomness of the content, the compression algorithm and compression level. For zlib the compression level is hardcoded, so you can only use different compression levels for Zstandard.

We talked about two situations: The first situation is one compressed packet with one MySQL packet and the second situation is one compressed packet with multiple MySQL packets. But there is a third situation: Multiple compressed packets for one MySQL packet.

A single MySQL Protocol Packet can be split over multi compressed packets.


To get good results with compression in general it really helps if you’re compressing larger sets of data and if the compressed data isn’t very random.

Let’s see what results we can get in a best case senario by using REPEAT() to send a string of 100 million times x.

To do this we need to make sure we set a large max_allowed_packet for both the server and client. We also disable SSL/TLS as that makes capturing things easier. In case you really need this, Wireshark is able to decrypt MySQL sessions with TLS.

$ mysql --compression-algorithms=zlib -h -u root -pRoot123@ --ssl-mode=DISABLED --max-allowed-packet=1073741824
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 237
Server version: 8.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> pager wc
PAGER set to 'wc'
mysql> select repeat('x',100*1024*1024);
      5       9 419431449
1 row in set (1.06 sec)

mysql> ^DBye

The traffic that we captured in Wireshark looks like this: