Building a new MySQL adapter for Ecto, Part II: Encoding/Decoding

• Wojtek Mach
• December 3rd, 2018
• ecto, elixir, myxql

Welcome to the “Building a new MySQL adapter for Ecto” series:

• Part I: Hello World
• Part II: Encoding/Decoding (you’re here!)
• Part III: DBConnection Integration
• Part IV: Ecto Integration

Last time we briefly looked at encoding and decoding data over MySQL wire protocol. In this article we’ll dive deeper into that topic, let’s get started!

Basic Types

MySQL protocol has two “Basic Data Types”: integers and strings. Within integers we have fixed-length and length-encoded integers. The simplest type is int<1> which is an integer stored in 1 byte.

To recap, MySQL is using little endianess when encoding/decoding integers as binaries. Let’s define a function that takes an int<1> from the given binary and returns the rest of the binary:

defmodule MyXQL.Types do
  def take_int1(data) do
    <<value::8-little-integer, rest::binary>> = data
    {value, rest}
  end
end

iex> MyXQL.Types.take_int1(<<1, 2, 3>>)
{1, <<2, 3>>}

We can generalize this function to accept any fixed-length integer:

def take_fixed_length_integer(data, size) do
  <<value::little-integer-size(size)-unit(8), rest::binary>> = data
  {value, rest}
end

iex> MyXQL.Types.take_fixed_length_integer(<<1, 2, 3>>, 2)
{513, <<3>>}

(See <<>>/1 for more information on bitstrings.)

Decoding a length-encoded integer is slightly more complicated. Basically, if the first byte value is less than 251, then it’s a 1-byte integer; if the first-byte is 0xFC, then it’s a 2-byte integer and so on up to a 8-byte integer:

def take_length_encoded_int1(<<int::8-little-integer, rest::binary>>) when int < 251, do: {int, rest}

def take_length_encoded_int2(<<0xFC, int::16-little-integer, rest::binary>>), do: {int, rest}

def take_length_encoded_int3(<<0xFD, int::24-little-integer, rest::binary>>), do: {int, rest}

def take_length_encoded_int8(<<0xFE, int::64-little-integer, rest::binary>>), do: {int, rest}

iex> MyXQL.Types.take_length_encoded_int1(<<1, 2, 3>>)
{1, <<2, 3>>}

iex> MyXQL.Types.take_length_encoded_int2(<<0xFC, 1, 2, 3>>)
{513, <<3>>}

Can we generalize this function to a single binary pattern match, the same way we did with take_fixed_length_integer/2? Unfortunately we can’t. Our logic is essentially a case with 4 clauses and such cannot be used in pattern matches. For this reason, the way we decode data is by reading some bytes, decoding them, and returning the rest of the binary.

It’s a shame that MySQL doesn’t encode the size of the binary in the first byte because otherwise our decode function could be easily implemented in a single binary pattern match, e.g.:

iex> <<size::8, value::little-integer-size(size)-unit(8), rest::binary>> = <<2, 1, 2, 3>>
iex> {value, rest}
{513, <<3>>}

In fact, it’s common for protocols to encode data as Type-Length-Value (TLV) which as you can see above, it’s very easy to implement with Elixir.

In any case, we can still leverage binary pattern matching in the function head. Here’s our final take_length_encoded_integer/1 function:

def take_length_encoded_integer(<<int::8, rest::binary>>) when int < 251, do: {int, rest}
def take_length_encoded_integer(<<0xFC, int::int(2), rest::binary>>), do: {int, rest}
def take_length_encoded_integer(<<0xFD, int::int(3), rest::binary>>), do: {int, rest}
def take_length_encoded_integer(<<0xFE, int::int(8), rest::binary>>), do: {int, rest}

There’s one last thing that we can do. Because take_fixed_length_integer/2 is so simple and basically uses a single binary pattern match (in particular, it does not have a case statement), we can replace it with a macro instead. All we need to do is to emit little-integer-size(size)-unit(8) AST so that we can use it in a bitstring; that’s easy:

defmacro int(size) do
  quote do
    little-integer-size(unquote(size))-unit(8)
  end
end

Because it’s a macro we need to require or import it to use it:

iex> import MyXQL.Types

iex> <<value::int(1), rest::binary>> = <<1, 2, 3>>
iex> {value, rest}
{1, <<2, 3>>}

iex> <<value::int(2), rest::binary>> = <<1, 2, 3>>
iex> {value, rest}
{513, <<3>>}

A really nice thing about using a macro here is we get encoding for free:

iex> <<513::int(2)>>
<<1, 2>>

We could write a macro for encoding length-encoded integers (we could even invoke it as 513::int(lenenc) to mimic the spec, by adjusting int/1 macro) but I decided against it as it won’t be usable in a binary pattern match.

Encoding/decoding MySQL strings is very similar so we will not be going over that and we’ll jump into the next section on bit flags. (Sure enough, working with strings would be easy, even in binary pattern matches, if not for an EOF-terminated string<eof> and string<lenenc> types.)

Bit Flags

MySQL provides “Capability Flags” like:

CLIENT_PROTOCOL_41 0x00000200
CLIENT_SECURE_CONNECTION 0x00008000
CLIENT_PLUGIN_AUTH 0x00080000

The idea is we represent a set of capabilities as a single integer on which we can use Bitwise operations like: 0x00000200 ||| 0x00008000, flags &&& 0x00080000 etc.

We definitely don’t want to pass these “magic” bytes around so we should encapsulate them somehow. We could store them as module attributes, e.g.: @client_protocol_41 0x00000200; if we mistype the name of the flag, we’ll get a helpful compiler warning. Using functions, however, gives us a bit more flexibility as we can generate great error messages as well as “hide” usage of bitwise operations underneath. Let’s implement a function that checks whether given flags has a given capability:

defmodule MyXQL.Messages do
  use Bitwise

  def has_capability_flag?(flags, :client_protocol_41), do: (flags &&& 0x00000200) == 0x00000200
  def has_capability_flag?(flags, :client_secure_connection), do: (flags &&& 0x00008000) == 0x00008000
  def has_capability_flag?(flags, :client_plugin_auth), do: (flags &&& 0x00080000) == 0x00080000
  # ...
end

iex> MyXQL.Messages.has_capability_flag?(0, :client_protocol_41)
false
iex> MyXQL.Messages.has_capability_flag?(0x00000200, :client_protocol_41)
true

iex> MyXQL.Messages.has_capability_flag?(0x00000200, :bad)
** (FunctionClauseError) no function clause matching in MyXQL.Messages.has_capability_flag?/2

    The following arguments were given to MyXQL.Messages.has_capability_flag?/2:

        # 1
        512

        # 2
        :bad

    Attempted function clauses (showing 3 out of 3):

        def has_capability_flag?(flags, :client_protocol_41)
        def has_capability_flag?(flags, :client_secure_connection)
        def has_capability_flag?(flags, :client_plugin_auth)

This is a very useful error message, we can see what are all available capabilities. If we want something more customized, all we need to do is define an additional catch-all clause at the end:

def has_capability_flag?(flags, other) do
  raise ...
end

and raise an error there. That way we could, for example, implement a “Did you mean?” hint.

Last but not least, instead of manually defining each function head by hand, we can use Elixir meta-programming capabilities to define them at compile time:

capability_flags = [
  client_protocol_41: 0x00000200,
  client_secure_connection: 0x00008000,
  client_plugin_auth: 0x00080000,
]

for {name, value} <- capability_flags do
  def has_capability_flag?(flags, unquote(name)), do: (flags &&& unquote(value)) == unquote(value)
end

Packets

Finally, let’s bring this all together to handle packets. We need a data structure that’s going to store packet fields and we basically have two options: structs and records. Structs are great when data has to be sent between modules, especially because they are polymorphic. However, when the data belongs to a single module, or separate modules that are considered private API, using records may make more sense as they are more space efficient. Let’s verify that using :erts_debug module and instead of comparing structs and records let’s just compare their internal representations: maps and tuples, respectively:

iex> :erts_debug.size(%{x: 1})
6
iex> :erts_debug.size(%{x: 1, y: 2})
8
iex> :erts_debug.size(%{x: 1, y: 2, z: 3})
10

iex> :erts_debug.size({:Point, 1})
3
iex> :erts_debug.size({:Point, 1, 2})
4
iex> :erts_debug.size({:Point, 1, 2, 3})
5

As you can see, as we add more keys to the map our data structure grows twice as fast and the reason is we store both keys and values whereas tuple stores the size of the tuple once and then just values. Since we may be processing thousands of packets per second, this difference may add up, so we’re going to use records here.

The final packet we discussed in the last article was the OK Packet. Let’s now write a function to decode it (it’s not fully following the spec for brevity):

# https://dev.mysql.com/doc/internals/en/packet-OK_Packet.html
defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count]

def decode_ok_packet(data, capability_flags) do
  <<0x00, rest::binary>> = data

  {affected_rows, rest} = take_length_encoded_integer(rest)
  {last_insert_id, rest} = take_length_encoded_integer(rest)

  packet = ok_packet(
    affected_rows: affected_rows,
    last_insert_id: last_insert_id
  )

  if has_capability_flag?(capability_flags, :client_protocol_41) do
    <<
      status_flags::int(2),
      warning_count::int(2)
    >> = rest

    ok_packet(packet,
      status_flags: status_flags,
      warning_count: warning_count
    )
  else
    packet
  end
end

And let’s test this with the OK packet we got at the end of the last article (00 00 00 02 00 00 00):

iex> ok_packet(affected_rows: affected_rows) = decode_ok_packet(<<0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00>>, 0x00000200)
iex> affected_rows
0

It works!

Conclusion

In this article, we discussed encoding and decoding basic data types, handling bit flags, and finally using both of these ideas to decode packets. Using these tools we should be able to fully implement MySQL protocol specification and with examples of :gen_tcp.send/2 and :gen_tcp.recv/2 calls from Part I, we could interact with the server. However, that’s not enough to build a resilient and production-quality driver. For that, we’ll look into DBConnection integration in Part III. Stay tuned!

P.S.: This post was originally published on Plataformatec’s blog.