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.
Trackbacks
Use this link to trackback from your own site.
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…
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.
Fantastic work! Just what we needed for our killer application.
You’re on the right track. Keep up the good work, Yariv!
Does ErlyDB have any protection against SQL Injection attacks? Your examples show you creating sql directly from potential user input.
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.
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 :)
[...] 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 [...]
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…
Is it possible to insert Erlang float into float MYQL columns
with this library ?
should read MYSQL column, and of course with prepared statements?
found out by myself: everything works fine :)
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’);”);
Ehm…
sorry, can’t repeat syntax ::(
error was::
#0A000PROCEDURE dbb.ppl_request can’t return a result set in the given context
i tried to use mysql erlang driver on solaris but i got an error message when i tried to run this command on the shell
mysql:start_link(p1,”localhost”,”root”,”root”,”test”).
and this the error message:
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
beam/beam_load.c(1301): Error loading module mysql_conn:
use of opcode 136; this emulator supports only up to 129
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
Loading of /Desktop/mysql_r12_driver/mysql_conn.beam failed: badfile
** exited: {undef,[{mysql_conn,start,
["localhost",
3306,
"root",
"root",
"test",
undefined]},
{mysql,init,1},
{gen_server,init_it,6},
{proc_lib,init_p,5}]} **
7>
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:49:59 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:50:00 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:50:00 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:50:00 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:50:00 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:50:00 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
sh: crypto_drv: not found
=ERROR REPORT==== 22-Jun-2008::14:50:00 ===
** Generic server crypto_server terminating
** Last message in was {’EXIT’,#Port,normal}
** When Server state == {#Port,[]}
** Reason for termination ==
** {port_died,normal}
=INFO REPORT==== 22-Jun-2008::14:50:00 ===
application: crypto
exited: shutdown
type: temporary
may u help me?!
Hi guys.
can we connect several mysql databases using this mysql driver.
thanks
Is there a way to connect without a password?
Is there going to be any Stored Procedures support in the future?
SEO ZONE is a search engine optimization(seo) firm, provides seo, seo article, seo tools,seo news and seo related informations,helping companies leverage the internet to increase revenues and profits.