{"id":7933,"date":"2018-11-14T11:16:32","date_gmt":"2018-11-14T13:16:32","guid":{"rendered":"http:\/\/blog.plataformatec.com.br\/?p=7933"},"modified":"2019-01-04T18:07:55","modified_gmt":"2019-01-04T20:07:55","slug":"building-a-new-mysql-adapter-for-ecto-part-i-hello-world","status":"publish","type":"post","link":"https:\/\/blog.plataformatec.com.br\/2018\/11\/building-a-new-mysql-adapter-for-ecto-part-i-hello-world\/","title":{"rendered":"Building a new MySQL adapter for Ecto, Part I: Hello World"},"content":{"rendered":"<p>As you may have seen in the <a href=\"https:\/\/elixirforum.com\/t\/announcing-a-new-mysql-driver-myxql\/17124\" target=\"_blank\" rel=\"noopener\">announcement<\/a>, Plataformatec is working on a new MySQL driver called MyXQL.<\/p>\n<p>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:<\/p>\n<ul>\n<li><a href=\"http:\/\/blog.plataformatec.com.br\/2018\/11\/building-a-new-mysql-adapter-for-ecto-part-i-hello-world\/\">Part I: Hello World<\/a> (you&#8217;re here!)<\/li>\n<li><a href=\"http:\/\/blog.plataformatec.com.br\/2018\/12\/building-a-new-mysql-adapter-for-ecto-part-ii-encoding-decoding\/\" target=\"_blank\" rel=\"noopener\">Part II: Encoding\/Decoding<\/a><\/li>\n<li><a href=\"http:\/\/blog.plataformatec.com.br\/2018\/12\/building-a-new-mysql-adapter-for-ecto-part-iii-dbconnection-integration\/\" target=\"_blank\" rel=\"noopener\">Part III: DBConnection Integration<\/a><\/li>\n<li><a href=\"http:\/\/blog.plataformatec.com.br\/2019\/01\/building-a-new-mysql-adapter-for-ecto-part-iv-ecto-integration\/\" target=\"_blank\" rel=\"noopener\">Part IV: Ecto Integration<\/a><\/li>\n<\/ul>\n<p>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&#8217;s get started!<\/p>\n<h3>Hello World<\/h3>\n<p>Our &#8220;Hello World&#8221; will involve performing a &#8220;handshake&#8221;: 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&#8217;s create one:<\/p>\n<pre><code>$ mysql --user=root -e \"CREATE USER myxql_test\"\n<\/code><\/pre>\n<p>We can check if everything went well by trying to log in as that user:<\/p>\n<pre><code>$ mysql --user=myxql_test -e \"SELECT NOW()\"\n+---------------------+\n| NOW()               |\n+---------------------+\n| 2018-10-04 18:35:11 |\n+---------------------+\n<\/code><\/pre>\n<p>If you don&#8217;t have MySQL installed, I recommend setting it up via Homebrew, if you&#8217;re on macOS, or Docker. I ended up using Docker because I knew I needed to test on multiple server versions. Here&#8217;s how I set it up:<\/p>\n<pre><code>$ docker run --publish=3306:3306 --name myxql_test -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0.12\n# note we connect via TCP, instead of the default UNIX domain socket:\n$ mysql --protocol=tcp --user=root --password=secret -e \"CREATE USER myxql_test;\"\n\n$ mysql --protocol=tcp --user=myxql_test -e \"SELECT NOW()\"\n+---------------------+\n| NOW()               |\n+---------------------+\n| 2018-10-04 18:40:04 |\n+---------------------+\n<\/code><\/pre>\n<p>We can now connect to the server from IEx session:<\/p>\n<pre><code>iex&gt; {:ok, sock} = :gen_tcp.connect('127.0.0.1', 3306, [:binary, active: false], 5000)\n{:ok, #Port&lt;0.6&gt;}\n<\/code><\/pre>\n<p>Let&#8217;s break this down. <a href=\"http:\/\/erlang.org\/doc\/man\/gen_tcp.html#connect-4\" target=\"_blank\" rel=\"noopener\"><code>:gen_tcp.connect\/4<\/code><\/a> accepts:<\/p>\n<ol>\n<li>Hostname (as charlist)<\/li>\n<li>Port<\/li>\n<li>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 <code>:binary<\/code> option.<br \/>\n<code>active: false<\/code> means we&#8217;ll work with the socket in &#8220;passive mode&#8221;, meaning we&#8217;ll read data using blocking <a href=\"http:\/\/erlang.org\/doc\/man\/gen_tcp.html#recv-3\" target=\"_blank\" rel=\"noopener\"><code>:gen_tcp.recv\/3<\/code><\/a> call.<\/li>\n<li>Timeout (in milliseconds)<\/li>\n<\/ol>\n<p>Let&#8217;s now read data from the socket: (<code>0<\/code> means we read all available bytes, <code>5000<\/code> is the timeout in milliseconds)<\/p>\n<pre><code>iex&gt; {:ok, data} = :gen_tcp.recv(sock, 0, 5000)\niex&gt; data\n&lt;&lt;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, ...&gt;&gt;\n<\/code><\/pre>\n<p>To make sense of this, we&#8217;re gonna need to look into MySQL manual.<br \/>\nEach <a href=\"https:\/\/dev.mysql.com\/doc\/internals\/en\/mysql-packet.html\" target=\"_blank\" rel=\"noopener\">MySQL packet<\/a> has 3 elements: length of the payload (3-byte integer), sequence id (1-byte integer), and payload.<br \/>\nIn this case, the actual payload is the <a href=\"https:\/\/dev.mysql.com\/doc\/internals\/en\/connection-phase-packets.html#packet-Protocol::Handshake\">&#8220;Initial Handshake Packet&#8221;<\/a>. Let&#8217;s extract the payload part using binary matching (see <a href=\"https:\/\/hexdocs.pm\/elixir\/Kernel.SpecialForms.html#%3C%3C%3E%3E\/1\">&lt;&lt;&gt;&gt;\/1<\/a> for more information on binary matching):<\/p>\n<pre><code>iex&gt; &lt;&lt;payload_length::24, sequence_id::8, payload::binary&gt;&gt; = data\niex&gt; payload_length\n4849664\niex&gt; byte_size(payload)\n74\n<\/code><\/pre>\n<p>Wait, the size of the payload is <code>74<\/code> so why <code>payload_length<\/code> is <code>4849664<\/code>?! Numerical values when stored in a binary have <a href=\"https:\/\/en.wikipedia.org\/wiki\/Endianness\" target=\"_blank\" rel=\"noopener\">&#8220;endianness&#8221;<\/a> which basically means whether we should read bits\/bytes from &#8220;little-end&#8221; (least significant bit) or &#8220;big-end&#8221; (most significant bit).<br \/>\nThus, a 3-byte integer <code>&lt;&lt;74, 0, 0&gt;&gt;<\/code> in &#8220;big-endian&#8221; is indeed <code>4849664<\/code> but in &#8220;little-endian&#8221; it&#8217;s <code>74<\/code>. Fortunately, bitstring syntax has great support for endianess and it&#8217;s as easy as adding <code>little<\/code> modifier (&#8220;big-endian&#8221; is the default):<\/p>\n<pre><code>iex&gt; &lt;&lt;payload_length::24-little, sequence_id::8, payload::binary&gt;&gt; = data\niex&gt; payload_length\n74\n<\/code><\/pre>\n<p>To make sense of the remaining payload we&#8217;re gonna use the <a href=\"https:\/\/hex.pm\/packages\/binpp\" target=\"_blank\" rel=\"noopener\"><code>binpp<\/code><\/a> package:<\/p>\n<pre><code>iex&gt; :binpp.pprint(payload)\n0000 0A 38 2E 30 2E 31 32 00 0F 00 00 00 27 73 79 59  .8.0.12.....'syY\n0001 7A 34 26 3B 00 FF FF FF 02 00 FF C3 15 00 00 00  z4&amp;;.\u00ff\u00ff\u00ff..\u00ff\u00c3....\n0002 00 00 00 00 00 00 00 43 55 6B 60 74 5A 71 08 75  .......CUk`tZq.u\n0003 6F 08 2F 00 63 61 63 68 69 6E 67 5F 73 68 61 32  o.\/.caching_sha2\n0004 5F 70 61 73 73 77 6F 72 64 00                    _password.\n<\/code><\/pre>\n<p>We can see up to 16 bytes in each row and at the far right we have ASCII interpretation of each byte. Per <a href=\"https:\/\/dev.mysql.com\/doc\/internals\/en\/connection-phase-packets.html#packet-Protocol::Handshake\">&#8220;Initial Handshake Packet&#8221;<\/a> the first byte is the protocol version, always <code>10<\/code> (<code>0x0A<\/code>), and what follows is a null-terminated server version string. Let&#8217;s extract that:<\/p>\n<pre><code>iex&gt; &lt;&lt;10, rest::binary&gt;&gt; = payload\niex&gt; [server_version, rest] = :binary.split(rest, &lt;&lt;0x00&gt;&gt;)\niex&gt; server_version\n\"8.0.12\"\n<\/code><\/pre>\n<p>We can parse the server version, that&#8217;s a good start! There are other fields in this packet that in a complete adapter we&#8217;d have to handle, but for now we&#8217;ll simply ignore them. We&#8217;ll just take a note of the authentication method at the end to the packet, a null-terminated string <code>\"caching_sha2_password\"<\/code>.<\/p>\n<p>After receiving &#8220;Initial Handshake Packet&#8221; the client is supposed to send <a href=\"https:\/\/dev.mysql.com\/doc\/internals\/en\/connection-phase-packets.html#packet-Protocol::HandshakeResponse\" target=\"_blank\" rel=\"noopener\">&#8220;Handshake Response&#8221;<\/a>. We&#8217;ll again just gloss over the details:<\/p>\n<pre><code>iex&gt; use Bitwise\niex&gt; capability_flags = 0x00000200 ||| 0x00008000 ||| 0x00080000\niex&gt; max_packet_size = 65535\niex&gt; charset = 0x21\niex&gt; username = \"myxql_test\"\niex&gt; auth_response = &lt;&lt;0x00&gt;&gt;\niex&gt; client_auth_plugin = \"caching_sha2_password\"\niex&gt; payload = &lt;&lt;\n       capability_flags::32-little,\n       max_packet_size::32-little,\n       charset, 0::8*23,\n       username::binary, 0x00,\n       auth_response::binary,\n       client_auth_plugin::binary, 0x00\n>&gt;\niex&gt; sequence_id = 1\niex&gt; data = &lt;&lt;byte_size(payload)::24-little, sequence_id, payload::binary&gt;&gt;\n<\/code><\/pre>\n<p>Let&#8217;s break this down:<\/p>\n<p>First, we use <code>CLIENT_PROTOCOL_41<\/code>,<code>CLIENT_SECURE_CONNECTION<\/code>, and <code>CLIENT_PLUGIN_AUTH<\/code> <a href=\"https:\/\/dev.mysql.com\/doc\/internals\/en\/capability-flags.html#packet-Protocol::CapabilityFlags\" target=\"_blank\" rel=\"noopener\">capability flags<\/a> using &#8220;bitwise OR&#8221;. Secondly, we set the max packet size, charset (<code>0x21<\/code> is <code>utf8_general_ci<\/code>), filler (<code>0<\/code>s repeated 23 times), username, auth response (empty password is a null byte), and auth plugin name. Note, we encode <code>username<\/code> and <code>client_auth_plugin<\/code> as null-terminated strings. Finally, we generate <code>payload<\/code> and encode it in a packet with payload length and sequence id (it&#8217;s 2nd packet so sequence id is <code>1<\/code>). Let&#8217;s now send this and receive response from the server:<\/p>\n<pre><code>iex&gt; :ok = :gen_tcp.send(sock, data)\niex&gt; {:ok, data} = :gen_tcp.recv(sock, 0)\niex&gt; &lt;&lt;payload_length::24-little, sequence_id::8, payload::binary&gt;&gt; = data\niex&gt; :binpp.pprint(payload)\n0000 00 00 00 02 00 00 00\n<\/code><\/pre>\n<p>The first byte of the response is <code>0x00<\/code> which corresponds to the <a href=\"https:\/\/dev.mysql.com\/doc\/internals\/en\/packet-OK_Packet.html\" target=\"_blank\" rel=\"noopener\"><code>OK_Packet<\/code><\/a>, authentication succedded! Even though we&#8217;ve glossed over many details, we&#8217;ve shown that we can integrate with the server end-to-end and that&#8217;s going to be a foundation we&#8217;ll built upon. There are many more packets that we&#8217;ll need to encode or decode and we&#8217;re gonna need a more structured approach which we will discuss in part II.<\/p>\n<p><a href=\"https:\/\/pages.plataformatec.com.br\/elixir-development-subscription\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-7816\" src=\"http:\/\/blog.plataformatec.com.br\/wp-content\/uploads\/2018\/10\/Elixir_2-1-1024x213.png\" alt=\"banner-elixir development subscription\" width=\"1024\" height=\"213\" srcset=\"https:\/\/blog.plataformatec.com.br\/wp-content\/uploads\/2018\/10\/Elixir_2-1-1024x213.png 1024w, https:\/\/blog.plataformatec.com.br\/wp-content\/uploads\/2018\/10\/Elixir_2-1-300x63.png 300w, https:\/\/blog.plataformatec.com.br\/wp-content\/uploads\/2018\/10\/Elixir_2-1-768x160.png 768w, https:\/\/blog.plataformatec.com.br\/wp-content\/uploads\/2018\/10\/Elixir_2-1.png 1200w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>As you may have seen in the announcement, Plataformatec is working on a new MySQL driver called MyXQL. 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 &#8230; <a class=\"read-more-link\" href=\"https:\/\/blog.plataformatec.com.br\/2018\/11\/building-a-new-mysql-adapter-for-ecto-part-i-hello-world\/\">\u00bb<\/a><\/p>\n","protected":false},"author":70,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[1],"tags":[238,143],"aioseo_notices":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7933"}],"collection":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/users\/70"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/comments?post=7933"}],"version-history":[{"count":35,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7933\/revisions"}],"predecessor-version":[{"id":8332,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/posts\/7933\/revisions\/8332"}],"wp:attachment":[{"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/media?parent=7933"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/categories?post=7933"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.plataformatec.com.br\/wp-json\/wp\/v2\/tags?post=7933"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}