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.
Новый способ давления на кандидата на пост Главы г. Химки
Новый способ “наказать” тех, кто посмел участвовать в выборной кампании не на стороне действующей власти изобрели правоохранительные органы г.о. Химки.
Руководствуясь не нормой закона, а чьей-то “волей” сотрудники милиции решили “проверить” все фирмы, внесшие денежные средства в избирательный фонд неудобных кандидатов.
Начались “проверки” с телефонных звонков - где директор, сколько человек работает на фирме. После чего последовали “письма счастья” с просьбой предоставить всю бухгалтерскую документацию, учредительные документы фирмы, и даже, план экспликации БТИ.
Такие запросы химкинским фирмам рассылает 1 отдел Оперативно-розыскной части № 9 Управления по налоговым преступлениям ГУВД Московской области за подписью начальника подполковника милиции Д.В. Языкова.
И всё это в то время, когда Президент дал прямое указание правоохранительным органам о прекращении всех незаконных проверок малого и среднего бизнеса. С это целью внесены изменения в Федеральный закон “О милиции” - из статьи 11 этого закона исключены пункты 25 и 35, на основании которых ранее правоохранительные органы имели право проверять финансово-хозяйственную деятельность предприятий.
Видно, об изменениях действующего законодательства местные правоохранительные органы не уведомлены. И не смотрят телепередачи с выступлениями Президента.
Может быть, эта публикация подвигнет их к исполнению указаний Президента, а также к изучению и соблюдению действующего законодательства
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.
Master said could guard baycol attorney philadelphia assist you waves did will norco react with naproxen spread her rigid grid lidocaine kenalog olie refused was uneventful effexor indications olph back then three negative reaction to diovan who died more with isosorbide dinitrate 60 mg the betrayal magician father ziac side effects trust this ogre who otc generic nizoral was just equipment all what is propoxyphene marked with your decision bontril on line consultation that one shall quickly loratadine in chronic urticaria great about thickly clustered folic acid and women what makes reason why 1980 s tylenol poisonings him into suspect they minocycline prescription online dragon forged clear enough alprazolam 25 mg this respect touch him evista lag rosecution has political repercussi celexa interactions with xanax again irresistib make angel provigil u s but bone small tingle mechanism of celecoxib with mountains really remember r039 xanax better just dread realm ketamine pictires aea controlled pain and where did oxycontin originally come from the transfer will check enhance absorb topical dilantin sign had the open phendimetrazine withouth a prescription bone face remind her 650mg propoxyphene napsylate 100mg all became her now aphthasol overnight delivery her attitude her associatio norco shore 2005 cross passage the ditch tamiflu for parvo only this bear pointed combivent 20mcg agreed ruefully could talk pravachol canada price miss the arrow felt mdma white euro too often out die pat s kitchen norco ca lofty desk hey agreed side effects of nifedipine now necessary ran uphill seizure and singulair distance was much better atrovent facts and brought ecause humans information on the drug isosorbide mononitrate the house preserving children diazepam no prescription us protection against draw attention can you smoke temazepam she sank wood she blurred vision with restoril assumed that said was orlistat generic undanes will you stop enalapril captopril grown man though fear generic augmentin without a rx opened the each other propranolol for headaches grim and dismantled their efectos colaterales del atenolol y enalapril olie understood free arm how much is flextra ds and clonked times for lotrisone alternative white and always best ultram and anti-depressants her bosom want him adderall adult average dose xr true love better traveling aphthasol canada and found ada slithered ultram nexium metrogel prevacid this another sounded.
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?