Search code examples
elixirsinglestore

How do I connect to MemSQL from Elixir

MemSQL is binary compatible with Mysql and uses the same drivers. However this nice-in-theory does not seem to apply when you connect from Elixir with mariaex or mysqlex.

With mariaex, I can connect fine: enter image description here

but I cannot query because of the enable_binary_protocol issue:

enter image description here

Enabling binary protocol in memsql.cnf for the aggregator kind of works for some short queries, but not for longer ones. First, after editing memsql.cnf to include the line "enable_binary_protocol=true" and restarting the cluster using "memsql-ops memsql-restart" I checked that the parameter was indeed set:

enter image description here

And now some stuff works, but not others:

enter image description here

BTW that query works fine in the memSQL command line interface:

enter image description here

The mysqlex driver, meantime, does not even connect:

enter image description here

So I'm basically stumped. Is anybody successfully using Elixir with memSQL? If so, what's the secret ingredient?

One option might be to use an Erlang library. This one (also available on Hex) might be good but I have no idea how to call the Erlang functions from Elixir. The closest I got was successfully compiling it in iex after adding the dependency {:mysql, "~> 1.0"} to my mix.exs.

Solution

  • I don't have any experience with MemSQL but it does look like mysql-otp prepares query statements on the client side. You can try using it like this:

    # Connect
{:ok, pid} = :mysql.start_link(host: 'localhost', user: 'foo', password: 'hello', database: 'test')

# Select
{:ok, columns, rows} = :mysql.query(pid, "SELECT * FROM mytable WHERE id = ?", [1])

# Transactions
result = :mysql.transaction(pid, fn ->
  :ok = :mysql.query("INSERT INTO mytable (foo) VALUES (1)")
  raise "foo"
  :ok = :mysql.query("INSERT INTO mytable (foo) VALUES (1)")
end)

    See the official Erlang/Elixir crash course for more details.