Comparison of MySQL clients

Viktor Söderqvist edited this page Apr 14, 2018 · 6 revisions

This page aims at comparing some different MySQL clients/drivers for Erlang.

Performance comparison

Here is a benchmark: https://tjheeta.github.io/2015/05/31/elixir-and-erlang-mysql-drivers/

Feature comparison

TODO: Update (e.g. the compared versions) and add more modern drivers, e.g. mariaex.

This chart was made in 2015 and compares MySQL/OTP to the most used ones. More are listed in less detail below.

MySQL/OTP Emysql erlang-mysql-driver YXA
Compared version 0.7.1 0.4.1 ? (updated 2007) ?
Text protocol yes yes yes yes
Binary protocol for prepared statements yes no no no
Transactions yes no yes, problematic [1] no
Nestable transactions yes no no no
Restartable transactions yes no no no
Query timeouts without reconnect yes no no no
Connection pool 3rd party build-in built-in simple round-robin
Usable without connection pool yes ? ? yes
Records in public API no yes yes yes
Auth method "native" yes yes yes yes
Auth method "old" no yes yes yes
Other auth methods no no no no
SSL no no no no
Compression no no no no
Old handshake (MySQL < 4.1) no yes yes yes
New handshake (MySQL >= 4.1) yes yes yes yes
Code coverage of tests 97% some none none
Values converted to Erlang terms yes yes yes no [2]
DECIMAL without precision loss yes [3] no, float ? N/A
Microseconds in TIME and DATETIME
(MySQL 5.6.4+)
yes no no N/A
Negative TIME yes no ? N/A
BIT(N) representation bitstring binary binary N/A
License LGPLv3+ Expat 3-clause BSD 3-clause BSD

Notes:

  1. Uncaught exceptions such as throw(foo) don't cause the transaction to rollback.
  2. All values are returned as binary or string (option) except NULL which is returned as an atom.
  3. DECIMAL values are represented as integers or floats when possible whithout precision loss. High precision decimals are returned as binaries on the form <<"3.120000000000000000000000042">>. This representation of DECIMALs is inspired by the odbc OTP application. (Emysql converts these to floats which results in precision loss.)

Emysql

Emysql is a rewrite of the older erlang-mysql-driver. It works with Erlang/OTP 7.1 with warnings for deprecated types.

Architecture: The application supervises a "connection manager" which is a gen_server that manages multiple connection pools. A connection is not a process but just a record in the state of the connection manager. When a query is executed, a process is spawned to execute the query and error handling is done by monitoring this process. Instead of a timeout on the recv calls from the socket, the process is killed after a certain time if a query takes too long to execute, whereafter a new connection is opened and returned to the connection pool. This design makes error handling very messy and transactions not feasible to implement. Therefore, Emysql still as of 2014-12-24 lacks transaction support.

erlang-mysql-driver

There are multiple forks of this historic driver. It shares much of the design with Emysql and thus many of Emysql's problems. The source code at https://code.google.com/p/erlang-mysql-driver/ has not been updated since 2007. This is the driver listed by Oracle on the page https://dev.mysql.com/doc/refman/5.7/en/connectors-apis.html (2014-12-30).

The queries are executed in the caller's process. The process dictionary of caller's process is used for things such as transaction state. There is transaction handling but it is not bomb-proof. (Exceptions such as throw(foo) that doesn't always cause the transaction to rollback.)

Description from the project:

This MySQL driver for Erlang is based on the Yxa driver obtained from Process One (at https://support.process-one.net/doc/display/CONTRIBS/Yxa). It includes several new features such as prepared statements, transactions, binary queries, type-converted query results, more efficient logging and a new connection pooling mechanism.

YXA

The first MySQL native driver seems to be this one, written by Magnus Ahltorp at KTH in 2002 for the YXA SIP software. Source code: https://github.com/fredrikt/yxa/tree/master/src/mysql.

ProcessOne seem to be using this driver in ejabberd with only minor modifications. It appears to be in use and has commits from 2014. Source code: https://github.com/processone/mysql.

Emysql and erlang-mysql-driver are based on an early version of this one.

mysqlerl

This is a port driver using one OS process per connection. This is not what you usually want. "ODBC compatible MySQL Erlang port driver, using libmysqlclient." Source code: https://github.com/bjc/mysqlerl.

ErlMySQL

This client by Alexei Krasnopolski is said to be modelled after MySQL Connector/C, the main C client library. It has implemented many features such as the binary protocol for prepared statements. It is unknown whether this client is actually in use. The library contains multiple unprefixed modules with general names such as "connection" and "helper_common". The source code is available at http://sourceforge.net/p/erlmysql/code/HEAD/tree/source/trunk/ and generated EDoc at http://erlmysql.sourceforge.net/.

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.