Building a MySQL Database Driver in Elixir

Have you ever wondered what happens beneath the covers when you talk to your Database? Well, you are in for a treat! In this talk, we are going to uncover the dark magic behind Database Drivers. We will look at everything that is needed to talk to a database, query its data and transform it into the native data types in Elixir.

Transcript

1. Building MySQL database
   driver in Elixir
   Mohd Maqbool Alam (@maqboolism)
   https://blog.maqbool.net
   https://t.me/elixirdelhi (Elixir Delhi)
   

   View Slide

2. Erlang Ecosystem Foundation
   https://erlef.org/
   

   View Slide

3. Goals
   ● Learning about Elixir binary pattern matching
   ● Building a ELF binary parser (readelf)
   ● Making socket connections on the BEAM and
   learning more about the various :gen_tcp API
   ● Understanding MySQL client-server protocol and
   convert its various packets into Elixir native
   types
   

   View Slide

4. Binary pattern Matching
   Binary pattern matching is the
   powerful feature of Elixir for
   extracting bits and piece out of a
   binary string
   

   View Slide

5. iex(1)> string = <<"Functional Conf 2019">>
   "Functional Conf 2019"
   iex(2)> <> = string
   "Functional Conf 2019"
   iex(3)> first
   "Functional"
   

   View Slide

6. Unit and Size
   Type Unit
   integer 1 bit
   float 1 bit
   binary 8 bit
   

   View Slide

7. size * unit
   

   View Slide

8. Modifiers
   signed integer
   unsigned
   (default)
   integer
   little integer, float, utf16, utf32
   big (default) integer, float, utf16, utf32
   native integer, utf16, utf32
   

   View Slide

9. iex(1)> File.read!("foo.png")
   <<137, 80, 78, 71, 13, 10, 26, 10, 0, 0, 0, 13, 73, 72, 68, 82, 0, 0, 3,
   151, 0, 0, 0, 145, 8, 6, 0, 0, 0, 87, 36, 76, 101, 0, 0, 0, 4, 115, 66,
   73, 84, 8, 8, 8, 8, 124, 8, 100, 136, 0, ...>>
   iex(2)> File.read!("bar.jpeg")
   <<255, 216, 255, 224, 0, 16, 74, 70, 73, 70, 0, 1, 1, 0, 0, 1, 0, 1, 0,
   0, 255, 219, 0, 67, 0, 5, 3, 4, 4, 4, 3, 5, 4, 4, 4, 5, 5, 5, 6, 7, 12,
   8, 7, 7, 7, 7, 15, 11, 11, 9, ...>>
   

   View Slide

10. defmodule ImageTyper do
    @png_signature <<137::size(8), 80::size(8), 78::size(8), 71::size(8), 13::size(8),
    10::size(8), 26::size(8), 10::size(8)>>
    @jpg_signature <<255::size(8), 216::size(8)>>
    def type(<<@png_signature, rest::binary>>), do: :png
    def type(<<@jpg_signature, rest::binary>>), do: :jpg
    def type(_), do: :unknown
    end
    credit: Elixir Docs
    

    View Slide

11. ~ ⚡ readelf -h a.out
    ELF Header:
    Magic: 7f 45 4c 46 02 01 01 00 00 00 00 00 00 00 00 00
    Class: ELF64
    Data: 2's complement, little endian
    Version: 1 (current)
    OS/ABI: UNIX - System V
    ABI Version: 0
    Type: EXEC (Executable file)
    Machine: Advanced Micro Devices X86-64
    Version: 0x1
    Entry point address: 0x401040
    Start of program headers: 64 (bytes into file)
    Start of section headers: 16360 (bytes into file)
    Flags: 0x0
    Size of this header: 64 (bytes)
    Size of program headers: 56 (bytes)
    Number of program headers: 11
    Size of section headers: 64 (bytes)
    Number of section headers: 30
    Section header string table index: 29
    

    View Slide

12. iex(1)> File.read!("a.out") |> Base.encode16
    "7F454C4602010100000000000000000002003E000100000040104000000000004000000000000000E83F0000000000000
    0000000400038000B0040001E001D000600000004000000400000000000000040004000000000004000400000000000680
    2000000000000680200000000000008000000000000000300000004000000A802000000000000A802400000000000A8024
    000000000001C000000000000001C000000000000000100000000000000010000000400000000000000000000000000400
    00000000000004000000000003804000000000000380400000000000000100000000000000100000005000000001000000
    000000000104000000000000010400000000000C501000000000000C501000000000000001000000000000001000000040
    00000002000000000000000204000000000000020400000000000500100000000000050010000000000000010000000000
    0000100000006000000102E000000000000103E400000000000103E4000000000001402000000000000180200000000000
    000100000000000000200000006000000202E000000000000203E400000000000203E400000000000D001000000000000D
    00100000000000008000000000000000400000004000000C402000000000000C402400000000000C402400000000000440
    00000000000004400000000000000040000000000000050E57464040000002820000000000000282040000000000028204
    000000000003C000000000000003C00000000000000040000000000000051E574640600000000000000000000000000000
    000000000000000000000000000000000000000000000000000000000100000000000000052E5746404000000102E00000
    0000000103E400000000000103E400000000000F001000000000000F00100000000000001000000000000002F6C6962363
    42F6C642D6C696E75782D7838362D36342E736F2E3200040000001000000001000000474E5500000000000300000002000
    00000000000040000001400000003000000474E5500E40E42211785A2618243355D362220624CC6039F010000000100000
    00100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    B0000001200000000000000000000000000000000000000120000001200000000000000000000000000000000000000300
    000002000000000000000000000000000000000000000006C6962632E736F2E36007072696E7466005F5F6C6962635F737
    46172745F6D61696E00474C4942435F322E322E35005F5F676D6F6E5F73746172745F5F000000000200020000000100010
    0010000001000000000000000751A6909000002002 … "
    

    View Slide

13. defmodule ReadElf do
    end
    defstruct [
    :magic, :class, :endian, :version, :os_abi, :abi_version, :type, :machine, :version,
    :entry_point_address, :start_of_program_headers, :start_of_section_headers, :flags,
    :size_of_this_header, :size_of_program_headers, :number_of_program_headers,
    :size_of_section_headers, :number_of_section_headers, :section_header_string_table_index
    ]
    def parse(
    <elf_version::binary-size(2), padding::binary-size(16), e_type::binary-size(4),
    rest::binary>>
    ) do
    elf = %ReadELF{
    magic: magic_number,
    version: elf_version,
    machine: arch
    }
    end
    def main(path) do
    path |> File.read! |> Base.encode16 |> parse |> IO.inspect
    end
    

    View Slide

14. The Berkeley socket API
    ● socket()
    ● bind()
    ● listen()
    ● connect()
    ● accept()
    ● send()
    ● recv()
    ● close()
    ● ...
    

    View Slide

15. Credit: https://www.cse.iitk.ac.in/users/dheeraj/cs425/lec18.html
    

    View Slide

16. :gen_tcp
    ● connect/3,4
    ● accept/1,2
    ● close/1
    ● listen/2
    ● recv/2,3
    ● send/2
    ● ...
    

    View Slide

17. host = ‘localhost’
    port = 3306
    opts = [packet: :raw, mode: :binary, active: false]
    {:ok, socket} = :gen_tcp.connect(host, port, 5000, opts)
    {:ok, #Port<0.181>}
    Connecting to a MySQL database Server using :gen_tcp erlang module
    

    View Slide

18. Behaviours
    ● OTP design pattern
    ● Generic behaviour module(server) and Specific
    callback module(client)
    ● Separation of concern
    ● Easier to read and understand code written by other
    developers
    

    View Slide

19. -module(pattern).
    -export([start/1, init/1]).
    start(Args) -> % Start the server.
    spawn(server, init, [Args]).
    init(Args) -> % Initialize the internal process state
    State = initialize_state(Args),
    loop(State).
    loop(State) -> % Receive and handle messages.
    receive
    {handle, Msg} ->
    NewState = handle(Msg, State),
    loop(NewState);
    stop ->
    terminate(State) % Stop the process.
    end.
    terminate(State) -> % Cleanup prior to termination.
    clean_up(State).
    

    View Slide

20. Generic
    ● Initializing the server
    state
    ● The loop data
    ● The client requests
    ● Handling client requests
    ● Contents of server reply
    ● Cleaning up
    ● Spawning the server
    ● Storing the loop data
    ● Sending the requests to
    the server
    ● Sending replies to the
    client
    ● Receiving server replies
    ● Stopping the server
    Specific
    Designing for Scalability with Erlang/OTP
    

    View Slide

21. Standard Erlang/OTP behaviours
    ❏ gen_server
    ❏ gen_statem
    ❏ gen_event
    ❏ supervisor
    

    View Slide

22. Example GenServer
    # Start the server (Generic)
    {:ok, pid} = GenServer.start_link(Stack, [:hello])
    # This is the client
    GenServer.call(pid, :pop)
    #=> :hello
    GenServer.cast(pid, {:push, :world})
    #=> :ok
    GenServer.call(pid, :pop)
    #=> :world
    Credit: Elixir Docs
    

    View Slide

23. def handle_cast({:push,
    element}, state) do
    {:noreply, [element |
    state]}
    end
    # Callbacks (specific)
    def init(stack) do
    {:ok, stack}
    end
    def handle_cast({:push,
    element}, state) do
    {:noreply, [element |
    state]}
    end
    

    View Slide

24. db_connection
    “Database connection behaviour and database
    connection pool designed for handling transaction,
    prepare/execute, cursors and client process
    describe/encode/decode”
    

    View Slide

25. db_connection API (Behaviour)
    ★ start_link/2
    ★ close/3 & close!/3
    ★ execute/4 & execute!/4
    ★ status/2
    ★ ...
    Generic
    ❏ connect/1
    ❏ disconnect/2
    ❏ ping/1
    ❏ handle_prepare/3
    ❏ handle_execute/4
    ❏ ...
    Specific
    

    View Slide

26. MySQL
    Client /Server
    Communication
    

    View Slide

27. Protocol Basics
    ● Structure of a standard packet
    ○ 4 bytes header + Packet body
    ○ Packet length int<3>
    ○ Sequence number int<1>
    ○ Packet body int
    ○ <payload::binary>> = data
    ● Protocol data types
    ○ int, string
    ● Connection & command phase
    

    View Slide

28. View Slide

29. Protocol data types
    int<1> Fixed-length integers
    int Length-encoded integers
    string Fixed-length strings
    string Null-terminated strings
    string Length-encoded strings
    string End-of-file length strings
    

    View Slide

30. Initial Handshake Packet (V10)
    1 [0a] protocol version
    string[NUL] server version
    4 connection id
    string[8] auth-plugin-data-part-1
    1 [00] filler
    2 capability flags (lower 2 bytes)
    if more data in the packet:
    1 character set
    2 status flags
    2 capability flags (upper 2 bytes)
    if capabilities & CLIENT_PLUGIN_AUTH {
    1 length of auth-plugin-data
    } else {
    1 [00]
    }
    string[10] reserved (all [00])
    if capabilities & CLIENT_SECURE_CONNECTION {
    string[$len] auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
    if capabilities & CLIENT_PLUGIN_AUTH {
    string[NUL] auth-plugin name
    }
    

    View Slide

31. https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::Handshake
    

    View Slide

32. iex> <server_version::little-binary-size(6), 0,
    connection_id::little-integer-size(32),
    auth_plugin_data_part_1::little-binary-size(8),
    filler::little-integer-size(8),
    capability_flags_lower::little-integer-size(16),
    character_set::little-integer-size(8),
    status_flags::little-integer-size(16),
    capability_flags_upper::little-integer-size(16),
    auth_plugin_data_len::little-integer-size(8),
    reserved::little-binary-size(10),
    auth_plugin_data_part_2::little-binary-size(13),
    auth_plugin_name::binary>> = body
    iex> [auth_plugin_name, _] = String.split(auth_plugin_name, <<0>>)
    

    View Slide

33. Handshake Response Packet
    4 capability flags, CLIENT_PROTOCOL_41 always set
    4 max-packet size
    1 character set
    string[23] reserved (all [0])
    string[NUL] username
    if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA {
    lenenc-int length of auth-response
    string[n] auth-response
    } else if capabilities & CLIENT_SECURE_CONNECTION {
    1 length of auth-response
    string[n] auth-response
    } else {
    string[NUL] auth-response
    }
    if capabilities & CLIENT_CONNECT_WITH_DB {
    string[NUL] database
    }
    if capabilities & CLIENT_PLUGIN_AUTH {
    string[NUL] auth plugin name
    }
    if capabilities & CLIENT_CONNECT_ATTRS {
    lenenc-int length of all key-values
    lenenc-str key
    lenenc-str value
    if-more data in 'length of all key-values', more keys and value pairs
    }
    

    View Slide

34. https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
    ● capability_flags (4) -- capability flags of the client as defined in
    Protocol::CapabilityFlags
    ● max_packet_size (4) -- max size of a command packet that the client wants to send to the
    server
    ● character_set (1) -- connection's default character set as defined in
    Protocol::CharacterSet.
    ● username (string.fix_len) -- name of the SQL account which client wants to log in -- this
    string should be interpreted using the character set indicated by character set field.
    ● auth-response (string.NUL) -- opaque authentication response data generated by
    Authentication Method indicated by the plugin name field.
    ● database (string.NUL) -- initail database for the connection -- this string should be
    interpreted using the character set indicated by character set field.
    ● auth plugin name (string.NUL) -- the Authentication Method used by the client to generate
    auth-response value in this packet. This is an UTF-8 string.
    Fields
    

    View Slide

35. iex> use 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 = <0::8*23, username::binary, 0x00, auth_response::binary, client_auth_plugin::binary,
    0x00>>
    iex> sequence_id = 1
    iex> data = <>
    

    View Slide

36. View Slide

37. Resources
    ● Understanding MySQL Internals by Sasha Pachev
    ● http://blog.plataformatec.com.br/2018/11/building-a-new-m
    ysql-adapter-for-ecto-part-i-hello-world/
    ● https://www.youtube.com/watch?v=lBaDmsFTJxY
    ● https://andrealeopardi.com/posts/handling-tcp-connections
    -in-elixir/
    ● https://hexdocs.pm/db_connection/DBConnection.html
    

    View Slide