mysql-otp / mysql-otp-poolboy Goto Github PK

mysql-otp pool handling

License: GNU Lesser General Public License v3.0

Makefile 2.19% Erlang 97.81%

mysql-otp-poolboy's Introduction

MySQL/OTP + Poolboy

Status: Beta.

MySQL/OTP + Poolboy provides connection pooling for MySQL/OTP using Poolboy. It contains convenience functions for executing SQL queries on a connection in a pool and lets you choose between two methods for creating and managing connection pools:

Use it as a library that helps you supervise your own MySQL connection pools.
Use it as an application that manages its own supervisor for connection pools.

I want to supervise my own connection pools

Use mysql_poolboy:child_spec/3 to get a child spec for a pool that you can use in your own supervisor.

%% my own supervisor
init([]) ->
    PoolOptions  = [{size, 10}, {max_overflow, 20}],
    MySqlOptions = [{user, "aladdin"}, {password, "sesame"}, {database, "test"},
                    {prepare, [{foo, "SELECT * FROM foo WHERE id=?"}]}],
    ChildSpecs = [
        %% MySQL pools
        mysql_poolboy:child_spec(pool1, PoolOptions, MySqlOptions),
        %% other workers...
        {some_other_worker, {some_other_worker, start_link, []},
         permanent, 10, worker, [some_other_worker]}
    ],
    {ok, {{one_for_one, 10, 10}, ChildSpecs}}.

Let MySQL/OTP + Poolboy supervise my pools

This approach requires you to start the application mysql_poolboy. Typically this is done by adding {applications, [mysql_poolboy]} to your .app.src file and then relying on your favourite release tool for the rest.

Pools can be added at run-time using mysql_poolboy:add_pool/3.

Pools can also be created at start-up by defining configuration parameters for mysql_poolboy. The name of each configuration parameter is the pool name and the value is a pair on the form {PoolOptions, MySqlOptions}.

Example:

Start your Erlang node with erl -config mypools.config where mypools.config is a file with the following contents:

[
 {mysql_poolboy, [
    {pool1, {[{size, 10}, {max_overflow, 20}],
             [{user, "aladdin"}, {password, "sesame"}, {database, "test"},
              {prepare, [{foo, "SELECT * FROM foo WHERE id=?"}]}]}}
]}].

Using the connection pools

The most commonly used MySQL functions are available with wrappers in mysql_poolboy.

1> mysql_poolboy:query(pool1, "SELECT * FROM foo WHERE id=?", [42]).
{ok,[<<"id">>,<<"bar">>],[[42,<<"baz">>]]}
2> mysql_poolboy:execute(pool1, foo, [42]).
{ok,[<<"id">>,<<"bar">>],[[42,<<"baz">>]]}

For transactions, the connection pid is passed to the transaction fun as the first parameter.

3> mysql_poolboy:transaction(pool1, fun (Pid) ->
       ok = mysql:query(Pid, "INSERT INTO foo VALUES (?, ?)", [1, <<"banana">>]),
       ok = mysql:query(Pid, "INSERT INTO foo VALUES (?, ?)", [2, <<"kiwi">>]),
       hello
   end).
{atomic, hello}

Sometimes you need to checkout a connection to execute multiple queries on it, without wrapping it in an SQL transaction. For this purpose you can use either a pair of calls to checkout/1 and checkin/2 or a call to with/2 with a fun as in this example:

4> mysql_poolboy:with(pool1, fun (Pid) ->
       {ok, _, [[OldTz]]} = mysql:query(Pid, "SELECT @@time_zone"),
       ok = mysql:query(Pid, "SET time_zone = '+00:00'"),
       %% Do some stuff in the UTC time zone...
       ok = mysql:query(Pid, "SET time_zone = ?", [OldTz])
   end).
ok

Use this as a dependency

Using erlang.mk, put this in your Makefile:

DEPS = mysql_poolboy
dep_mysql_poolboy = git https://github.com/mysql-otp/mysql-otp-poolboy 0.2.1

Using rebar, put this in your rebar.config:

{deps, [
    {mysql_poolboy, ".*", {git, "https://github.com/mysql-otp/mysql-otp-poolboy",
                           {tag, "0.2.1"}}}
]}.

License

GNU Lesser General Public License (LGPL) version 3 or any later version. Since the LGPL is a set of additional permissions on top of the GPL, both license texts are included in the files COPYING.LESSER and COPYING respectively.

mysql-otp-poolboy's People

Contributors

Stargazers

Watchers

mysql-otp-poolboy's Issues

last_insert_id ?

Hi - how could I check the last_insert_id (https://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html) for a mysql_poolboy:execute() command ?

execute/3,4 for prepared statements

mysql_poolboy:execute(mypool, mystmt, [1, 2]).

For this, we need to know that the statement is prepared on the actual connection that we get from the pool. This can be solved if a statement is prepared directly when a connection is opened. For this we need an option for mysql:start_link/1 like {prepare, [...]}. That would have to be fixed in mysql-otp first.

Prepare = [{mystmt, "INSERT INTO tardis (doctor, dalek) VALUES (?, ?)"},
           {blabla, "SELECT doctor FROM tardis WHERE dalek = ?"}],
{ok, Pid} = mysql:start_link([{user, "aladdin"},
                              {password, "sesame"},
                              {prepare, Prepare}].

rebar.config and Makefile mysql-otp mismatch

in release 0.1.7

rebar.config has mysql-otp version 1.2.0 defined
Makefile has mysql-otp version 1.1.1 defined

is this intentional or for some other reason why the versions mismatch?

Closed connection is not detected

Hi,

I tried mysql_poolboy and for some reason I ended up with one of the connections to mysql being closed without it being removed or restarted by the pool management. This resulted in failed db requests every time this instance was selected in the pool while the other instances worked obviously. Does mysql_poolboy have problems detecting a closed connection? My assumption was that mysql_poolboy would detect this and remove the closed connection from the pool, or am I wrong?

The exception I got when the connection was closed was the following:
** Reason for termination ==
** {{{{badmatch,{error,closed}},
[{mysql_protocol,prepare,3,[{file,"src/mysql_protocol.erl"},{line,98}]},
{mysql,handle_call,3,[{file,"src/mysql.erl"},{line,585}]},
{gen_server,try_handle_call,4,[{file,"gen_server.erl"},{line,629}]},
{gen_server,handle_msg,5,[{file,"gen_server.erl"},{line,661}]},
{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]},
{gen_server,call,
[<0.1350.0>,
{param_query,"",
[72,133]},
infinity]}},
[{gen_server,call,3,[{file,"gen_server.erl"},{line,212}]},
{mysql,query_call,2,[{file,"src/mysql.erl"},{line,752}]},
{poolboy,transaction,3,[{file,"src/poolboy.erl"},{line,76}]},
{db,handle_cast,2,[{file,"src/db.erl"},{line,55}]},
{gen_server,try_dispatch,4,[{file,"gen_server.erl"},{line,615}]},
{gen_server,handle_msg,5,[{file,"gen_server.erl"},{line,681}]},
{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]}

, where the actual query has been removed right now. On line 55 in db.erl I do mysql_poolboy:query which causes this exception.

Use lazy connect and let server disconnect on inactivity

Currently, when you start an application using MySQL/OTP+Poolboy, you immediately see a number of connections to the DB server. Most of them are idle.

This is an idea of reducing the number of connections to the server to what is actually needed. With MySQL/OTP 1.6.0, no error is logged when the server disconnects. This, with the new lazy connect strategy, allows us to have a more server-friendly approach:

Change poolboy strategy to LIFO (which is the default for poolboy, so we just remove the {strategy, fifo} option)
Turn off keep-alive
Set connect strategy to lazy

The scenario would look like this:

Poolboy starts starts a pool of workers. The workers don't connect to the DB yet.
When a worker is used and a query is executed, the worker connects to the DB.
On load spikes, more workers are used, thus more workers connect to the DB.
When the load goes down, the last connected nodes will be disconnected because of inactivity. (The timeout can be set on the server side.) This causes poolboy to restart the worker, but it will be started in disconnected (lazy connect) state.

I suggest we make these options the default in this project.

Rename add_pool OR change what it does

add_pool is not the best name for a wrapper for poolboy:child_spec/3. It doesn't actuallly add anything but it just returns a child spec. I think it should be called child_spec/3 and take 3 args just like poolboy:child_spec/3.

Maybe we actually want an application with a supervisor for multiple pools? It doesn't mean people have to use it that way. If we are careful, we can make it possible for people to choose (A) to supervise their own pools or, if they want, (B) application:start(mysql_poolboy) and then add_pool(...) to add a pool to mysql_poolboy's top-level supervisor which would be familiar for Emysql users. It could also be possible start pools from config files on start-up (like in the example for poolboy+pgsql).

To supervise their own pools (A), they wouldn't need to start this application. They just call mysql_poolboy:child_spec/3 from their own supervisor.

with/2 for checkout-Fun()-checkin without SQL transaction

Actually also cowboy is also using a pool so it can also reuse one process for another http connection so even those processes are not short-lived ones. That means that it is important to checkin the mysql connection after usage, which normally has to be in a try-after block to be sure it is not forgotten, i.e.

Conn = mysql_poolboy:checkout(mypool),
try
    mysql:query(Conn, "SELECT INSERT BLA BLA BLA")
after
    mysql_poolboy:checking(mypool, Conn)
end.

so we can wrap this in a with/2 which I imagine is a wrapper for poolboy:transaction/2 but it's not an SQL transaction so calling it a transaction could be confusing. I suggest the name "with" which is a construct in python for doing the equivallent of try-after with a resource like this.

mysql_poolboy:with(mypool, fun (Conn) -> mysql:query(Conn, "SELECT BLA BLA") end).

Function name "checkout_connection" is too long

I suggest these are renamed to shorter names checkin and checkout for convenience.

These are useful functions and it is not a problem if you sometimes forget to do checkin afterwards. Poolboy keeps a monitor on the user process so if it dies, the connection is returned to the pool. So at least for short-lived processes it is not a big deal if you forget to checkin. (For long-lived processes see #3.)

It is often necessary to do checkout to be able to do more than one thing on a connection. Examples: 1. Make an insert and afterwards use insert_it/1 to get the id of the inserted row. 2. Use warning_count/1 or any other of those functions after another query.

Bump deps MySQL/OTP 1.1.1

There was a really not nice bug in 0.9.0 up to 1.1.0 that made the driver not work for mysql server older than version 5.5. The bug was fixed in 1.1.1.

PS. When updating the version tag, also update the README.md. It still says 0.1.0 in the rebar and makefile example how to use it as a dep.

Look into the changes of poolboy 1.5.X

Test if new version of poolboy works as intended.

Add this to Hex?

mysql-otp is in Hex; it would be handy if mysql-otp-poolboy was too.

Not prepared error

Hello having the following code:

1> {ok, _} = application:ensure_all_started(mysql_poolboy).
{ok,[mysql,poolboy,mysql_poolboy]}
2> mysql_poolboy:with(mysql_slave_pool, fun(Pid) -> {ok, get_sip_credentials} = mysql:prepare(Pid, get_sip_credentials, <<"SELECT sip_password, account_uuid FROM sip_endpoints WHERE sip_username = ?">>) end).
{ok,get_sip_credentials}
3> mysql_poolboy:execute(mysql_slave_pool, get_sip_credentials, [<<"silviu">>]).
{error,not_prepared}

What exactly is wrong ? As time prepare returns ok I'm not expected to get {error,not_prepared} on execute

could you update rebar.config to point to the latest mysql-otp ?

thank you.

I get the tcp_closed every day

Hi, I using mysql-otp-poolboy on my project, but I get following error every day, Could you please help to resolve it. Thanks!

Here is the log for your reference.

2019-06-03 15:47:28.534 [error] <0.575.0> Connection Id 170 closing with reason: tcp_closed
2019-06-03 15:47:28.535 [error] <0.575.0> gen_server <0.575.0> terminated with reason: tcp_closed
2019-06-03 15:47:28.535 [error] <0.575.0> CRASH REPORT Process <0.575.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-03 15:47:28.535 [error] <0.571.0> Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.575.0> exit with reason tcp_closed in context child_terminated
2019-06-03 15:47:28.536 [error] <0.572.0>@mysql_protocol:ping:120 gen_server <0.572.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.536 [error] <0.572.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.572.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.536 [error] <0.580.0>@mysql_protocol:ping:120 gen_server <0.580.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.536 [error] <0.579.0>@mysql_protocol:ping:120 gen_server <0.579.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.537 [error] <0.581.0>@mysql_protocol:ping:120 gen_server <0.581.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.537 [error] <0.583.0>@mysql_protocol:ping:120 gen_server <0.583.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.543 [error] <0.583.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.583.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.543 [error] <0.578.0>@mysql_protocol:ping:120 gen_server <0.578.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.544 [error] <0.577.0> Connection Id 172 closing with reason: tcp_closed
2019-06-03 15:47:28.544 [error] <0.577.0> gen_server <0.577.0> terminated with reason: tcp_closed
2019-06-03 15:47:28.544 [error] <0.576.0> Connection Id 171 closing with reason: tcp_closed
2019-06-03 15:47:28.544 [error] <0.576.0> gen_server <0.576.0> terminated with reason: tcp_closed
2019-06-03 15:47:28.544 [error] <0.582.0>@mysql_protocol:ping:120 gen_server <0.582.0> terminated with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.544 [error] <0.580.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.580.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.544 [error] <0.579.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.579.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.544 [error] <0.581.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.581.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.544 [error] <0.578.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.578.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.544 [error] <0.577.0> CRASH REPORT Process <0.577.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-03 15:47:28.544 [error] <0.576.0> CRASH REPORT Process <0.576.0> with 0 neighbours exited with reason: tcp_closed in gen_server:handle_common_reply/8 line 751
2019-06-03 15:47:28.544 [error] <0.582.0>@mysql_protocol:ping:120 CRASH REPORT Process <0.582.0> with 0 neighbours crashed with reason: no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120
2019-06-03 15:47:28.551 [error] <0.17628.0>@mysql_conn:init:94 CRASH REPORT Process <0.17628.0> with 0 neighbours crashed with reason: no match of right hand value {error,econnrefused} in mysql_conn:init/1 line 94
2019-06-03 15:47:28.552 [error] <0.570.0>@Poolboy:new_worker:283 gen_server serviceServer terminated with reason: no match of right hand value {error,{{badmatch,{error,econnrefused}},[{mysql_conn,init,1,[{file,"/Services/_build/default/lib/mysql/src/mysql_conn.erl"},{line,94}]},{gen_server,init_it,2,[{file,"gen_server.erl"},{line,374}]},{gen_server,init_it,6,[{file,"gen_server.erl"},{line,342}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,249}]}]}} in poolboy:new_worker/1 line 283
2019-06-03 15:47:28.552 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.572.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.552 [error] <0.570.0>@Poolboy:new_worker:283 CRASH REPORT Process serviceServer with 0 neighbours crashed with reason: no match of right hand value {error,{{badmatch,{error,econnrefused}},[{mysql_conn,init,1,[{file,"/Services/_build/default/lib/mysql/src/mysql_conn.erl"},{line,94}]},{gen_server,init_it,2,[{file,"gen_server.erl"},{line,374}]},{gen_server,init_it,6,[{file,"gen_server.erl"},{line,342}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,249}]}]}} in poolboy:new_worker/1 line 283
2019-06-03 15:47:28.552 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.583.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.552 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.580.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.553 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.579.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.553 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.581.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.554 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.578.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.554 [error] <0.571.0> Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.577.0> exit with reason tcp_closed in context child_terminated
2019-06-03 15:47:28.555 [error] <0.571.0> Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.576.0> exit with reason tcp_closed in context child_terminated
2019-06-03 15:47:28.555 [error] <0.571.0>@mysql_protocol:ping:120 Supervisor {<0.571.0>,poolboy_sup} had child mysql started with {mysql,start_link,undefined} at <0.582.0> exit with reason no match of right hand value {error,closed} in mysql_protocol:ping/2 line 120 in context child_terminated
2019-06-03 15:47:28.555 [error] <0.569.0>@Poolboy:new_worker:283 Supervisor mysql_poolboy_sup had child serviceServer started with poolboy:start_link([{strategy,fifo},{name,{local,serviceServer}},{worker_module,mysql},{size,10},{max_overflow,500}], [{host,"1.2.3.4"},{port,3306},{user,"serviceServer"},{password,"password"},{database,"serviceServer"},...]) at <0.570.0> exit with reason no match of right hand value {error,{{badmatch,{error,econnrefused}},[{mysql_conn,init,1,[{file,"/Services/_build/default/lib/mysql/src/mysql_conn.erl"},{line,94}]},{gen_server,init_it,2,[{file,"gen_server.erl"},{line,374}]},{gen_server,init_it,6,[{file,"gen_server.erl"},{line,342}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,249}]}]}} in poolboy:new_worker/1 line 283 in context child_terminated

Restarting the mysql server is not detected by the driver.

Only after some queries fails the disconnection is detected and the entire pool is restarted. On Emysql the reconnection mechanism is much stable