Announcing a new MySQL driver: MyXQL

Your Libraries & OS Mentoring Libraries
,
1

Hi everyone,

We would like to announce that Plataformatec is working on a new MySQL driver called MyXQL. Our goal is to eventually integrate it with Ecto and make it the default MySQL driver in Ecto.

Today the main driver to talk to MySQL is Mariaex. The work on Mariaex started in December/2014 and it has been an extremely important software to the community. We are very thankful to the team behind Mariaex, as it allowed many companies dependent on MySQL databases to use Elixir in production through libraries like Ecto and Phoenix.

Many things happened since Mariaex launched:

  • Newer MySQL versions such as v5.7 and v8.0 which introduced new authentication modes, protocol changes, new data types and more. Unfortunately, for a project like Mariaex, it means support all of those features across different versions

  • Mariaex development started right after Elixir v1.0. This means it often does not leverage more recent Elixir idioms, such as the with special form. Elixir v1.3 also introduced the new calendar types, which would conflict with the date/time types that ships with Mariaex

  • When Mariaex started, Ecto was on version v0.3.0. Since then, we released Ecto v1.0 and then Ecto v2.0. In Ecto v1.0, Ecto was the one responsible for handling connection pools, transactions and much more. However, in Ecto v2.0, we moved those concerns to drivers through a common project called DBConnection. This new approach makes the drivers more efficient and more useful outside of Ecto. James Fish led this effort and covered those topics in his ElixirConf EU 2017 talk. Despite the benefits, the migration meant more code churn to existing drivers

Due to these and other factors, I personally believe the Mariaex repository grew in complexity, which complicates its maintenance. Currently there are 40 open issues in the repository and 9 open pull requests.

We have long debated how to best improve Mariaex’ codebase. After careful consideration, we have decided the best approach is to start with a clean slate, as it would be hard to untangle the complexity accumulated throughout the years. This is what led to the creation of MyXQL.

The MyXQL effort is led by Wojtek Mach and our goals are:

  • MyXQL will require Elixir v1.4 (almost 2 years old) and MySQL v5.7.9+, which is the first General Availability release from the 5.7 branch, released in Oct/2015. This will allow us to support many features and data types recently added to MySQL and properly map them to their equivalents in Elixir

  • Leverage the knowledge we have gathered by writing and contributing to Postgrex and Mariaex drivers. In practice, we want the MyXQL adapter to mirror the organization and code structure found in Postgrex and apply the lessons that have been learned in Mariaex. The plan is to reuse parts from both projects, as deemed necessary

Note that MyXQL won’t be ready in time for the upcoming Ecto v3.0. For now, developers should continue to use Mariaex and we have already sent pull requests to ensure Mariaex is compatible with Ecto 3.0. Once MyXQL is ready, we will allow developers to choose between Mariaex and MyXQL. Although the migration should be seamless for the huge majority of users, this will give developers time to move from one tool to the other. Support for Mariaex will be eventually removed from Ecto (or, to be more precise, removed from Ecto.SQL).

The current code is work in progress. If your company or job heavily depends on MySQL, consider watching the MyXQL repository to follow the on-going work as well as take part in future discussions. It is important that other community members get involved with the codebase and contribute to ensure the codebase continues to grow healthily, regardless of external factors. It is not “high-profile” work but it is an essential part of the Elixir infrastructure.

Wojtek will also write a series of articles on Plataformatec’s blog covering all of the steps to write a database driver with DBConnection. We hope the series will make the whole process more accessible and easier for others to jump into.

Thanks!

40 Likes
2

Hi everyone,

Here’s a quick update on the MyXQL project.

We have reached an important milestone which is passing all of Ecto’s MySQL tests. We think we are
at a point where early adopters can start testing it and we would appreciate the feedback.

You can use MyXQL with Ecto by setting following dependencies in your mix.exs:

defp deps() do
  [
    # ...
    {:ecto_sql, github: "wojtekmach/ecto_sql", branch: "wm-myxql"},
    {:myxql, github: "elixir-ecto/myxql"}
  ]
end

and that should be it!

Note, there’s still more work to be done before the first release. You can track the progress on
the ecto_sql PR and in MyXQL v0.1 milestone.

You can see existing issues and report new ones in the issue tracker.

We have also published a 4-part blog series on building the adapter:

Finally, we’d like to list a few notable changes between MyXQL and Mariaex:

  1. MyXQL supports MySQL Pluggable Authentication system and ships with mysql_native_password, sha256_password, and caching_sha2_password authentication methods. This is especially important since MySQL 5.7 defaults to mysql_native_password, but MySQL 8.0 to the caching_sha2_password method. By supporting both we ensure good out of the box experience

  2. Mariaex.query/4 function defaults to using binary protocol (prepared statements) and if that fails (some statements are not preparable), it falls back to the text protocol.

    MyXQL.query/4, on the other hand, uses the text protocol on empty params list, and otherwise it uses the binary protocol.

  3. MyXQL.Error struct contains :mysql field for MySQL errors (e.g.: %{mysql: %{code: 1062, name: :ER_DUP_ENTRY}}) and :socket field for socket errors (e.g.: %{socket: :nxdomain}) which should make it easier for users to understand and handle the errors.

  4. MyXQL defaults to using UNIX domain socket for connecting to the server. Forcing TCP with default options is easy too: MyXQL.start_link(protocol: :tcp). Mariaex defaults to TCP.

That’s it for now, stay tuned for updates!

27 Likes
3

Hi everyone,

Here’s another update on MyXQL:

  1. We shipped MyXQL v0.2.0. It contains a bunch of bugfixes, new features and performance
    improvements. It should also be a drop-in replacement for Mariaex (more on that below).
    We think it’s ready for real world testing.

  2. We’ve added a MARIAEX_COMPATIBILITY.md page explaining differences between MyXQL and Mariaex.

  3. For backwards-compatiblity reasons, Ecto.Adapters.MySQL will continue to use Mariaex, and there’s a separate built-in adapter for MyXQL, Ecto.Adapters.MyXQL.

MyXQL support landed on Ecto SQL master and it will be part of the upcoming v3.1 release.

You can test it by following these steps:

  1. Use myxql and ecto_sql master:

      [
    # ...
    {:ecto_sql, github: "elixir-ecto/myxql"},
    {:myxql, "~> 0.2.0"}
  ]
end

  2. Set adapter: Ecto.Adapters.MyXQL in your Repo

and that should be it.

Please give it a try and let us know if you run into any issues!

10 Likes
4 
{:ecto_sql, github: "elixir-ecto/myxql"},

correction:

{:ecto_sql, github: "elixir-ecto/ecto_sql"},
6 Likes
5

Oops, thank you!

1 Like