mysql-otp / mysql-otp-poolboy Goto Github PK
View Code? Open in Web Editor NEWmysql-otp pool handling
License: GNU Lesser General Public License v3.0
mysql-otp pool handling
License: GNU Lesser General Public License v3.0
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
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:
{strategy, fifo}
option)The scenario would look like this:
I suggest we make these options the default in this project.
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.
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}].
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.
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.
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
Test if new version of poolboy works as intended.
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?
mysql-otp
is in Hex; it would be handy if mysql-otp-poolboy
was too.
Only after some queries fails the disconnection is detected and the entire pool is restarted. On Emysql the reconnection mechanism is much stable
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).
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.
thank you.
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 ?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.