How to Build a MySQL Protocol Server from Scratch for the DSP Database

Why Choose MySQL Protocol Compatibility?

Most mainstream BI tools, ORM frameworks, and command‑line clients natively support the MySQL wire protocol. By implementing this protocol, DSP (Sloth) database achieves zero client‑side adaptation cost—users can connect with the standard mysql client without any special driver.

Netty Pipeline: Core of Protocol Decoding

The entry point is FrontEndMain, which assembles a handler chain via ChannelInitializer:

LengthFieldBasedFrameDecoder   ← split by length field
→ ByteBufToPackageDecoder    ← bytes → MysqlPackage object
→ AuthenticationHandler      ← handshake authentication
→ MysqlPackageHandler        ← command dispatch

The LengthFieldBasedFrameDecoder is configured for MySQL’s 3‑byte length + 1‑byte sequence number format: lengthFieldOffset = 0 – length field starts at byte 0 lengthFieldLength = 3 – length field occupies 3 bytes lengthAdjustment = 1 – skip the 1‑byte sequence after the length initialBytesToStrip = 0 – keep the full frame

After this configuration Netty automatically frames MySQL packets, so downstream handlers work with complete ByteBuf objects without worrying about sticky or half packets.

Handshake Authentication: Three‑Step Exchange

Step 1 – Server Greeting : When the TCP connection becomes active, NettyConnectionHandler.channelActive() sends a ServerGreeting packet that mimics MySQL 5.7.22, containing protocol version 0x0a, charset 33 (utf8), two random salts, and the authentication plugin name mysql_native_password.

Step 2 – Client Login Request : The client replies with a LoginRequest carrying username, encrypted password, optional database, and capability flags. ByteBufToPackageDecoder decides based on the channel’s authentication state whether to construct a LoginRequest (unauthenticated) or a Command (authenticated).

Step 3 – Server Auth Response : AuthenticationHandler validates the login request via compareUsernameAndPassword(), which currently always returns true (placeholder for future implementation), and then sends either an OK or an Error packet.

Command Dispatch: Two‑Level Routing

After successful authentication, all subsequent packets are handled by MysqlPackageHandler, which implements a two‑level dispatch:

First level – Command Type : The first byte of the MySQL packet indicates the command (e.g., COM_QUERY = 0x03, COM_INIT_DB = 0x02, COM_QUIT = 0x01). Each type maps to a specific handler such as QueryCommandHandler, UseDatabaseCommandHandler, or QuitCommandHandler.

Second level – SqlNode Type : For COM_QUERY, the QueryCommandHandler parses the SQL with Calcite, producing a SqlNode. Depending on the concrete subclass ( SqlSelect, SqlInsert, SqlCreateTable, etc.), the handler is looked up in HandlerHolder.SQL_TYPE_TO_HANDLER_MAP and dispatched accordingly.

This design cleanly separates protocol translation from SQL execution.

Result Set Encoding: From Rows to MySQL Packets

After query execution, PackageUtils.buildResultSet() builds a MySQL‑compliant result set consisting of:

Column Count Packet → number of columns (LengthEncodedInteger)
Column Definition × N → metadata for each column (catalog/schema/table/name/type)
EOF Packet (0xFE) → end of column definitions
Row Data × M → each row encoded as LengthEncodedString per column
EOF Packet (0xFE) → end of result set

Column definitions follow the ColumnDefinition41 format with 12 fields (catalog, schema, table, orgTable, name, originalName, filler, charset, columnLength, columnType, flags, decimals). Type mapping examples: Calcite INTEGER → MySQL MYSQL_TYPE_INT24 (0x09); BIGINT → MYSQL_TYPE_LONG (0x08); VARCHAR → MYSQL_TYPE_VAR_STRING (0xfd).

LengthEncodedInteger: Variable‑Length Encoding

DSP’s IOUtils.writeLengthEncodedInteger() implements MySQL’s length‑encoded integer encoding:

Value < 251: single‑byte representation

Value < 2^16: prefix 0xFC + 2‑byte little‑endian

Value < 2^24: prefix 0xFD + 3‑byte little‑endian

Larger values: prefix 0xFE + 8‑byte little‑endian

This scheme balances protocol overhead and range, and is used for column counts, string lengths, and row data.

Environment Variable Replacement: Handling @@variables

When the client sends queries like SELECT @@version, DSP replaces the variables at the AST level using EnvironmentReplaceVisitor (subclass of Calcite’s SqlShuttle) through the following steps:

Identify SqlIdentifier that starts with @@.

Look up the variable in ConnectionContext.properties (session‑level).

If not found, look in EnvironmentValueHolder (global‑level).

Replace the identifier with SqlLiteral.createCharString(value).

This AST‑based replacement avoids accidental changes inside string literals.

Summary and Reflections

The DSP protocol layer demonstrates the essential challenges of a MySQL‑compatible server:

Packet framing using Netty’s LengthFieldBasedFrameDecoder.

State‑machine decoding based on authentication status.

Two‑level routing: Command Type → SqlNode class.

Strict adherence to MySQL result‑set format.

AST‑level handling of MySQL‑specific @@variable syntax.

Current shortcomings include a placeholder authentication check, lack of a business thread pool (SQL execution blocks Netty I/O threads), no support for prepared statements, and missing SSL/TLS. These gaps must be addressed for production use, but the code serves as an excellent learning resource for understanding MySQL protocol mechanics and building custom protocol servers with Netty.