Erlang MySQL Driver, Reloaded

In my quest to make ErlyDB a top-notch database abstraction tool (since I'm planning on using it myself, I wouldn't want it to be any less :) ), I have spent the past few days improving the Yxa MySQL driver for Erlang that I obtained from Process One to bring it up to par with ErlyDB's needs. I ended up rewriting much of the driver's internals as well as adding a bunch of features and optimizations.

I created a Google Code project for this revamped driver, where I will make any future changes and bug fixes. You can access it at http://code.google.com/p/erlang-mysql-driver/.

(Btw, I even resisted getting pulled into a prolonged blog debate about Erlang's merits as a web development language as opposed to Ruby. Astonishingly, it looks like my last attempt at brainwashing all developers into becoming Erlang devotees has produced mixed results! I guess it's time to think about the next escalation in the propaganda war :) )

Here's a summary of my changes:

• Added support for transactions


• Added support for prepared statements (you can execute prepared statement in and outside of a transaction)


• Added support for binary queries (this is the preferred way of sending queries because it's more efficient than using strings)


• Rewrote the connection pooling mechanism to make it more efficient.


• Numbers and date/time values in result sets are automatically converted to their Erlang equivalents. Strings are treated as binaries.


• Logging statements can now be instantiated lazily based on the desired logging level. This significantly reduces the overhead of logging, especially when debug level logging is disabled.


• Other internal modifications.

Following is a short tutorial illustrating how to use the new features.

Create a MySQL database called "test" and the run the following script:


create table developer(
id integer auto_increment primary key,
name varchar(30),
country varchar(20),
created_on timestamp)
type=InnoDB;


(Setting the table type to InnoDB in necessary for transaction support.)

Here's some Erlang code showing how to use some of the new APIs.


%% Start the MySQL dispatcher and create the first connection
%% to the database. 'p1' is the connection pool identifier.
mysql:start_link(p1, "localhost", "root", "password", "test"),

%% Add 2 more connections to the connection pool
mysql:connect(p1, "localhost", undefined, "root",
"password", "test", true),
mysql:connect(p1, "localhost", undefined, "root",
"password", "test", true),

mysql:fetch(p1, <<"DELETE FROM developer">>),

mysql:fetch(p1, <<"INSERT INTO developer(name, country) "
"VALUES "
"('Claes (Klacke) Wikstrom', 'Sweden'),"
"('Ulf Wiger', 'USA')">>),

%% Execute a query (using a binary)
Result1 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result1: ~p~n", [Result1]),

%% Register a prepared statement
mysql:prepare(update_developer_country,
<<"UPDATE developer SET country=? where name like ?">>),

%% Execute the prepared statement
mysql:execute(p1, update_developer_country, [<<"Sweden">>,
<<"%Wiger">>]),

Result2 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result2]),

%% Make some statements
S1 = <<"INSERT INTO developer(name, country) VALUES "
"('Joe Armstrong', 'USA')">>,

S2 = <<"DELETE FROM developer WHERE name like 'Claes%'">>,

%% Create a transaction
T1 = mysql:new_transaction(p1),
T2 = mysql:add_query(T1, S1),

%% You can execute prepared statements inside transactions
T3 = mysql:add_execute(T2, update_developer_country,
[<<"Sweden">>, <<"%Armstrong">>]),

T4 = mysql:add_query(T3, S2),
mysql:commit(T4),

Result3 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result3]),

%% Another way of doing a transaction
S3 = <<"DELETE FROM developer WHERE country='USA'">>,
mysql:transaction(p1, fun(T) ->
mysql:add_queries(T, [S1,S2,S3])
end),

Result4 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result4]),

%% Transactions are automatically rolled back if
%% any of their queries results in an error.
mysql:transaction(
p1, fun(T) ->
mysql:add_queries(T, [<<"DELETE FROM developer">>,
<<"bad bad query">>])
end),

Result5 = mysql:fetch(p1, <<"SELECT * FROM developer">>),
io:format("Result2: ~p~n", [Result5]),

ok.


Additional documentation is in mysql.erl.

In the next few days, I am planning to integrate these new features into ErlyDB. From some research I've done, I've learned that using prepared statements significantly lowers the load on the database. Given that the database is often a scalability bottleneck in web applications, I think it's important for ErlyDB to use prepared statements as much as possible.

I have done some testing, but given the large number of potentially destabilizing changes, I don't recommend this driver for production use at the moment. You can test it yourself to get comfortable with the code's stability or wait a month or two until it's more hardened.

Some notes about the driver's internals

When initialized, the driver spawns an Erlang process of type gen_server, called mysql_dispatcher, which maintains connection pools to MySQL databases. Each connection has an Erlang process. When the process crashes (i.e. the connection is lost), the dispatcher (by default) tries to reconnect to the database from a new process.

Erlang client processes communicate with the MySQL dispatcher using standard gen_server calls. This structure allows a large number of Erlang processes in a single VM to share relatively few database connections.

Prepared statements are actually prepared lazily per connection. When the user executes a previously defined prepared statement against a connection in which the statement hasn't been prepared, the dispatcher will issue the PREPARE call for the connection before executing the statement. If the execute call is part of a transaction, the dispatcher will issue the PREPARE call before the transaction begins.

If any statement inside a transaction results in an error, the driver automatically tries to roll back the transaction.