Wednesday, September 13, 2006

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.

18 comments:

Dmitrii Dimandt said...

Strings as binaries....

You're forgetting Unicode, especially UTF-8. There will be absolutely no way of telling whether

657285 is 65 7285 or 6572 85 or even 65 72 85...

Man, I wish there was a nice string library for Erlang... Or should we just hook up with IBM's ICU, i wonder...

Yariv said...

The previous implementation called binary_to_list on all return values, and all I did was remove that call. I think it's wrong to assume that all strings should be returned as list, where often all you want to do is send the string to the browser verbatim, in which case a binary is more efficient.

Coder said...

Fantastic work! Just what we needed for our killer application.

Charles said...

You're on the right track. Keep up the good work, Yariv!

Steve Jenson said...

Does ErlyDB have any protection against SQL Injection attacks? Your examples show you creating sql directly from potential user input.

Yariv said...

This example shows how to use the MySQL driver directly and hence it's not recommended for use in a production system. ErlyDB (alpha) currently doesn't help you avoid SQL injection attacks except for in simple queries. However, I'm currently working on a SQL query generator that will be included in the next version of ErlyDB. It will prevent SQL injection attacks and hence will be the preferred way of running using MySQL from Erlang.

Dmitrii Dimandt aka Mamut said...

I think it's wrong to assume that all strings should be returned as list, where often all you want to do is send the string to the browser verbatim, in which case a binary is more efficient. //


Good point. Didn't think about that :)

Erlang Blues I « Devender’s Weblog said...

[...] seems to be no documentation on how to use this, I finally found a blog with some so I gave it a try, apparently it only works if there is a password, if your password is [...]

Martin's personal blog said...

Mysql Erlang Client...



Original Auszug unter: 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 Erlan...

Trokhon said...

Is it possible to insert Erlang float into float MYQL columns
with this library ?

Trokhon said...

should read MYSQL column, and of course with prepared statements?

Trokhon said...

found out by myself: everything works fine :)

iAm said...

i got err...

fetch <> (id )
{error,{mysql_result,[],[],0,
<>}}

It's a procedure with internal select. It's working from Perl like
$ret{'list'}=$dbh->selectall_arrayref("CALL ppl_request('$id');");

iAm said...

Ehm...
sorry, can't repeat syntax ::(

error was::
#0A000PROCEDURE dbb.ppl_request can't return a result set in the given context

Zamous said...

Is there a way to connect without a password?

Dude said...

Is there going to be any Stored Procedures support in the future?

Shiv Indap said...

Hi,

I am new to erlang and would like to know how to install this library on OS X 10.5. Should I simply compile the files and then copy them to the erlang folder, btw I am using Erlang obtained from Macports and not compiled from source, could you please help me out.

Wenew Zhang said...

use erlang-mysql in my project,
it's a supervisor->work structure and start mysql:start_link() in the work,
when the work crashed,
the mysql-connections to still keep alive,
i add process_flag(trap_exit, true) in mysql:init and mysql_conn:init
but it doesn't working!
what shall i do?