The MySQL wire protocol: the greeting, capability flags and caching_sha2_password

MySQL's client/server protocol is the other SQL wire protocol everything speaks. Laravel, Rails, Django, WordPress, Prisma, Go's database/sql, JDBC and every MySQL GUI connect with it. Loams SQL, our serverless MySQL-compatible database, is TiDB compute on Loams' TiKV, and TiDB already speaks this protocol, so for Loams SQL the job is not to implement MySQL but to stand in front of it: terminate TLS, authenticate, wake a suspended branch, and then get out of the way.

How the protocol worksCopy Anchor Link

Framing. A MySQL packet is a 4-byte header and a payload. The header is a 3-byte little-endian payload length and a 1-byte sequence id. The sequence id starts at 0 for each command and goes up by one with every packet in either direction, so a parser can tell a stray packet from the next one in an exchange. A payload of 2²⁴−1 bytes or more is split into maximum-size packets and a final shorter one.

The server speaks first. This is the defining difference from Postgres. On connect the server immediately sends Protocol::HandshakeV10: protocol version 10, a server version string, a connection id, 20 bytes of random scramble (in two parts), the server's capability flags, the default character set, status flags, and the name of the authentication plugin it wants.

Capability flags. Both sides hold a 32-bit set of CLIENT_* flags, and what is in force is the intersection. They change the wire format, not just features: CLIENT_PROTOCOL_41 selects the 4.1 packet layouts, CLIENT_DEPRECATE_EOF replaces the EOF packet after rows with an OK packet, CLIENT_PLUGIN_AUTH enables pluggable authentication, CLIENT_CONNECT_ATTRS lets the client send key-value attributes, CLIENT_SSL says TLS is wanted, CLIENT_LOCAL_FILES allows LOAD DATA LOCAL, CLIENT_COMPRESS turns on compressed framing. A proxy that relays bytes between two connections has to make sure both legs agreed on the same layout-changing flags, or it relays packets the client will misparse.

TLS is an upgrade, mid-handshake. A client that wants TLS answers the greeting with an SSLRequest: the first 32 bytes of a HandshakeResponse41 with CLIENT_SSL set and no user name. Both sides then run a TLS handshake on the socket, and the client sends the full HandshakeResponse41 inside TLS (user, auth response, database, attributes), with the sequence id continuing from where it was. So the greeting, the scramble and the capability offer always travel in clear.

Authentication plugins. The HandshakeResponse41 carries an auth response computed for the plugin the server named. If the server wants a different plugin for this user, it sends an AuthSwitchRequest (0xFE) with a new plugin name and scramble, and the client answers again.

• mysql_native_password is a SHA-1 scramble. MySQL deprecated it in 8.0.34, disabled the server plugin by default in 8.4, and removed it from the server in 9.0 (the client side survives as a loadable plugin, so new clients can still speak to older servers).
• caching_sha2_password, the default since MySQL 8.0, sends a SHA-256-based scramble. If the server has this user in its cache it answers AuthMoreData 0x03 (fast auth success) and then OK. Otherwise it answers 0x04 (perform full authentication), and the client must send the password itself: in clear if the connection is TLS, or encrypted with the server's RSA public key if not (the client requests it with 0x02). That is why a non-TLS caching_sha2_password login fails against a server with no RSA key.

Commands. After OK, every client message is a command identified by its first byte: COM_QUERY (0x03, SQL text, results in the text protocol), COM_STMT_PREPARE (0x16) and COM_STMT_EXECUTE (0x17, binary protocol results), COM_INIT_DB (0x02), COM_PING (0x0E), COM_QUIT (0x01), COM_CHANGE_USER (0x11), and the replication commands. A result set is a column count, one column-definition packet per column, the rows, and an EOF or OK. Errors are ERR packets (0xFF) with a numeric code and a five-character SQLSTATE.

Why Loams speaks itCopy Anchor Link

Loams SQL exists because we wanted a Neon-like MySQL, and the straightest road was TiDB in keyspace mode on the TiKV cluster Loams already runs (design 47, D720, D721). TiDB's protocol and SQL are the compatibility contract, published rather than hidden: MySQL 8.0 protocol and syntax, repeatable read as snapshot isolation, pessimistic row locks, no gap locks, SERIALIZABLE refused, no stored procedures, triggers or XA (D735). The Loams SQL Learn page and TiKV and PD cover the storage side.

What TiDB cannot do is be serverless on its own: it cannot wake itself when a client connects to a suspended branch, and it cannot check a Loams credential. Hence a gate in front of it, written in Rust like everything Loams builds (D731).

Separately, the engine has a small MySQL listener over collections, for the same reason as its Postgres one: some tools only speak MySQL.

How we implement itCopy Anchor Link

loams-sqlgate: the gate in front of TiDBCopy Anchor Link

The gate's codec is sans-I/O: it takes bytes, nonces and verdicts and returns bytes and actions, with no sockets, clocks or randomness, so the same code is driven by the server, the tests and five fuzz targets (packet framing, the handshake response, the connection phase, the auth exchange and the decoders). The fuzz corpus is real: handshakes captured from the five clients in the table above against keyspace-mode TiDB v8.5.8, each client pinned by version or image digest.

Its own greeting. Because the server speaks first, a gate cannot pick an upstream before greeting the client: it does not yet know the user, the database or (before TLS) the SNI. So the gate never relays TiDB's greeting. It sends its own, with a fresh OS-random scramble, the version 8.0.11-TiDB-v8.5.8-Loams, CLIENT_SSL and caching_sha2_password. After authentication it logs in to TiDB as a separate client, over TLS, as the role's internal user ri_<role>, with a PROXY protocol v2 header carrying the real client address.

TLS is required. The gate offers TLS 1.2 or later with a certificate chosen by SNI, and accepts plaintext only from loopback peers. Its connection-phase state machine allows one SSLRequest, requires the response after TLS to carry CLIENT_SSL and the same capabilities as the SSLRequest, and refuses a plaintext response that claims CLIENT_SSL, which is the shape of a downgrade. The branch is identified from the role user, which is globally unique, or from the SNI.

Authentication is full caching_sha2_password over TLS. Role passwords are 32 random bytes, stored as Argon2id hashes (D732). The fast path is the protocol's own: a cache of recently verified users (100,000 entries for an hour by default) answers 0x03, and everything else gets 0x04 and sends the password inside TLS, where the gate checks it against the hash.

Capabilities it will not offer, whatever TiDB supports: CLIENT_LOCAL_FILES (it lets the server read files from the client's machine), CLIENT_COMPRESS (the gate relays uncompressed frames), multi-factor auth, optional result-set metadata and query attributes (they change packet layouts the gate does not track). The flags that change result framing are kept identical on both legs so packets can be copied byte for byte.

Packets only, no SQL parsing. After login the gate reads only each command's first byte. It relays the fifteen commands TiDB v8.5.8 dispatches (query, the prepared-statement family, ping, init-db, set-option, reset-connection and a few more) and answers the rest itself: COM_CHANGE_USER, the replication commands, COM_SHUTDOWN and COM_DEBUG get error 1235, anything TiDB does not dispatch gets 1047. One exception peeks at text: a statement starting with KILL is refused, because the connection id in the client's greeting is the gate's, not TiDB's. That is a usability guard, not a security control, and the code says so.

Serverless behaviour. After login a suspended branch is woken; the client is held up to 30 seconds and then gets 1040 database is resuming, retry. A suspend closes idle sessions with 1053, MySQL's server-shutdown error, which pools understand. For scale-in and upgrades, the gate can move an idle session (no transaction or cursor open) to another tidb-server using TiDB's session-state statements, the mechanism TiProxy uses. TiProxy itself is Go and is not used; the gate reimplements the idea in Rust because it also has to wake branches and verify Loams credentials.

The gate is built and tested on dev (SQ1 Tasks 3 to 6) but is not in the 0.1.0 release images. In 0.1.0 the compose stack runs TiDB directly on port 4000 inside the compose network, with a root user that has no password until you set one. Set one before forwarding that port anywhere.

The collection listener (loams --mysql-listen)Copy Anchor Link

The mysql-wire feature serves collections over MySQL using opensrv-mysql 0.7. It is deliberately small:

• One read-only SELECT per COM_QUERY. The text is parsed with the MySQL dialect; multiple statements or anything that is not a query get ER_READ_ONLY_MODE.
• No prepared statements. COM_STMT_PREPARE and COM_STMT_EXECUTE get ER_UNSUPPORTED_PS. Drivers that prepare by default (Connector/J with useServerPrepStmts, some ORMs) must be told to use client-side preparation.
• The database is the namespace. USE or a connect-time database other than the listener's namespace gets ER_BAD_DB_ERROR.
• Types: integers map to LONGLONG, floats to DOUBLE, booleans to TINY, and everything else is sent as a string column.
• Timeouts: 10 seconds to send the handshake response, an hour idle between commands; a running query never counts against the idle timer.
• Loopback only, no authentication, no TLS, like the Postgres listener, under D111.

GotchasCopy Anchor Link

• Some drivers do not do TLS unless told. In the captures, mysql 8.4 and Connector/J answered a greeting that offered CLIENT_SSL with an SSLRequest; mysql2 and Go-MySQL-Driver (without tls=) and mariadb --skip-ssl answered in plaintext. Against the gate they need TLS turned on explicitly (ssl: {} in mysql2, tls=true in the Go DSN).
• Non-TLS caching_sha2_password needs RSA. If you connect to TiDB directly without TLS and the user is caching_sha2_password, full authentication asks for the server's RSA key, and TiDB in our captures had none: error 1045. Use TLS.
• mysql_native_password is on its way out. A MySQL 8.4 server does not load it unless you turn it on, and 9.0 removed the server plugin. TiDB still greets with it and switches to caching_sha2_password for users created with that plugin.
• LOAD DATA LOCAL INFILE does not work through the gate. The flag is never offered. Load from object storage instead.
• KILL <id> through the gate is refused, because the connection id your client saw in the greeting is the gate's, not TiDB's. Close the connection instead.
• Snapshot isolation is not InnoDB's repeatable read. Check-then-insert patterns that relied on gap locks need a unique constraint or SELECT … FOR UPDATE on existing rows (D735).
• No ALPN. MySQL has no id in the IANA ALPN registry (PostgreSQL has postgresql) and no client sends one; the gate accepts mysql if one ever does, and Postgres's ALPN-based tricks from part 1 do not carry over.

Next: Arrow Flight SQL and ADBC, where results leave as Arrow batches instead of rows.