Building a new MySQL adapter for Ecto, Part I: Hello World
Writing a complete driver involves quite a bit of work. To name just a few things, we need to support: all protocol messages and data types, authentication schemes, connection options (TCP/SSL/UNIX domain socket), transactions and more. Rather than going through all of these in detail, I plan to distill this knowledge into 4 parts, each with a quick overview of a given area:
- Part I: Hello World (you’re here!)
- Part II: Encoding/Decoding
- Part III: DBConnection Integration
- Part IV: Ecto Integration
This also mimics how I approached the development of the library, my end goal was to integrate with Ecto and I wanted to be integrating end-to-end as soon and as often as possible. Rather than implementing each part fully, I implemented just enough to move forward knowing I can later go back and fill in remaining details. Without further ado, let’s get started!
Hello World
Our “Hello World” will involve performing a “handshake”: connecting to a running MySQL server and authenticating a user. To avoid getting bogged down in authentication details, the simplest possible thing to do is to log in as user without password. Let’s create one:
$ mysql --user=root -e "CREATE USER myxql_test"We can check if everything went well by trying to log in as that user:
$ mysql --user=myxql_test -e "SELECT NOW()"
+---------------------+
| NOW() |
+---------------------+
| 2018-10-04 18:35:11 |
+---------------------+If you don’t have MySQL installed, I recommend setting it up via Homebrew, if you’re on macOS, or Docker. I ended up using Docker because I knew I needed to test on multiple server versions. Here’s how I set it up:
$ docker run --publish=3306:3306 --name myxql_test -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0.12
# note we connect via TCP, instead of the default UNIX domain socket:
$ mysql --protocol=tcp --user=root --password=secret -e "CREATE USER myxql_test;"
$ mysql --protocol=tcp --user=myxql_test -e "SELECT NOW()"
+---------------------+
| NOW() |
+---------------------+
| 2018-10-04 18:40:04 |
+---------------------+We can now connect to the server from IEx session:
iex> {:ok, sock} = :gen_tcp.connect('127.0.0.1', 3306, [:binary, active: false], 5000)
{:ok, #Port<0.6>}
Let’s break this down. :gen_tcp.connect/4 accepts:
- Hostname (as charlist)
- Port
-
Options (as proplist); by default, data from the socket is returned as iolist, however for us
binary will be more convenient to work with, so we pass
:binaryoption.active: falsemeans we’ll work with the socket in “passive mode”, meaning we’ll read data using blocking:gen_tcp.recv/3call. - Timeout (in milliseconds)
Let’s now read data from the socket: (0 means we read all available bytes, 5000 is the timeout in milliseconds)
iex> {:ok, data} = :gen_tcp.recv(sock, 0, 5000)
iex> data
<<74, 0, 0, 0, 10, 56, 46, 48, 46, 49, 50, 0, 12, 0, 0, 0, 11, 9, 19, 27, 96, 108, 77, 116, 0, 255, 255, 255, 2, 0, 255, 195, 21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 37, 62, 29, 59, 1, ...>>
To make sense of this, we’re gonna need to look into MySQL manual.
Each MySQL packet has 3 elements: length of the payload (3-byte integer), sequence id (1-byte integer), and payload.
In this case, the actual payload is the “Initial Handshake Packet”. Let’s extract the payload part using binary matching (see <<>>/1 for more information on binary matching):
iex> <<payload_length::24, sequence_id::8, payload::binary>> = data
iex> payload_length
4849664
iex> byte_size(payload)
74
Wait, the size of the payload is 74 so why payload_length is 4849664?! Numerical values when stored in a binary have “endianness” which basically means whether we should read bits/bytes from “little-end” (least significant bit) or “big-end” (most significant bit).
Thus, a 3-byte integer <<74, 0, 0>> in “big-endian” is indeed 4849664 but in “little-endian” it’s 74. Fortunately, bitstring syntax has great support for endianess and it’s as easy as adding little modifier (“big-endian” is the default):
iex> <<payload_length::24-little, sequence_id::8, payload::binary>> = data
iex> payload_length
74To make sense of the remaining payload we’re gonna use the binpp package:
iex> :binpp.pprint(payload)
0000 0A 38 2E 30 2E 31 32 00 0F 00 00 00 27 73 79 59 .8.0.12.....'syY
0001 7A 34 26 3B 00 FF FF FF 02 00 FF C3 15 00 00 00 z4&;.ÿÿÿ..ÿÃ....
0002 00 00 00 00 00 00 00 43 55 6B 60 74 5A 71 08 75 .......CUk`tZq.u
0003 6F 08 2F 00 63 61 63 68 69 6E 67 5F 73 68 61 32 o./.caching_sha2
0004 5F 70 61 73 73 77 6F 72 64 00 _password.
We can see up to 16 bytes in each row and at the far right we have ASCII interpretation of each byte. Per “Initial Handshake Packet” the first byte is the protocol version, always 10 (0x0A), and what follows is a null-terminated server version string. Let’s extract that:
iex> <<10, rest::binary>> = payload
iex> [server_version, rest] = :binary.split(rest, <<0x00>>)
iex> server_version
"8.0.12"
We can parse the server version, that’s a good start! There are other fields in this packet that in a complete adapter we’d have to handle, but for now we’ll simply ignore them. We’ll just take a note of the authentication method at the end to the packet, a null-terminated string "caching_sha2_password".
After receiving “Initial Handshake Packet” the client is supposed to send “Handshake Response”. We’ll again just gloss over the details:
iex> import Bitwise
iex> capability_flags = 0x00000200 ||| 0x00008000 ||| 0x00080000
iex> max_packet_size = 65535
iex> charset = 0x21
iex> username = "myxql_test"
iex> auth_response = <<0x00>>
iex> client_auth_plugin = "caching_sha2_password"
iex> payload = <<
capability_flags::32-little,
max_packet_size::32-little,
charset, 0::8*23,
username::binary, 0x00,
auth_response::binary,
client_auth_plugin::binary, 0x00
>>
iex> sequence_id = 1
iex> data = <<byte_size(payload)::24-little, sequence_id, payload::binary>>Let’s break this down:
First, we use CLIENT_PROTOCOL_41,CLIENT_SECURE_CONNECTION, and CLIENT_PLUGIN_AUTH capability flags using “bitwise OR”. Secondly, we set the max packet size, charset (0x21 is utf8_general_ci), filler (0s repeated 23 times), username, auth response (empty password is a null byte), and auth plugin name. Note, we encode username and client_auth_plugin as null-terminated strings. Finally, we generate payload and encode it in a packet with payload length and sequence id (it’s 2nd packet so sequence id is 1). Let’s now send this and receive response from the server:
iex> :ok = :gen_tcp.send(sock, data)
iex> {:ok, data} = :gen_tcp.recv(sock, 0)
iex> <<payload_length::24-little, sequence_id::8, payload::binary>> = data
iex> :binpp.pprint(payload)
0000 00 00 00 02 00 00 00
The first byte of the response is 0x00 which corresponds to the OK_Packet, authentication succedded! Even though we’ve glossed over many details, we’ve shown that we can integrate with the server end-to-end and that’s going to be a foundation we’ll built upon. There are many more packets that we’ll need to encode or decode and we’re gonna need a more structured approach which we will discuss in part II.
P.S.: This post was originally published on Plataformatec’s blog.