mysql-otp/mysql-otp-poolboy

mysql-otp pool handling

mysql-otp-poolboy's Issues

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

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:

1. Poolboy starts starts a pool of workers. The workers don't connect to the DB yet.
2. When a worker is used and a query is executed, the worker connects to the DB.
3. On load spikes, more workers are used, thus more workers connect to the DB.
4. 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.

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.

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}].

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.

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.

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

Look into the changes of poolboy 1.5.X

Test if new version of poolboy works as intended.

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?

Add this to Hex?

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

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

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).

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.

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

thank you.

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 ?