On Sunday, July 29, 2012 4:24:37 AM UTC-7, azi wrote:
>
> Changing the wait_timeout to a higher value is not something that's in my
> hands. But I am surely going to try the shardedthreadedconnectionpooling
> and the queue implementation of connection_handling. I will let you know
> the results very soon.
>
You can't even override it on a per-connection basis?
> But, I still can't understand the behavior of DB.disconnect. DB.disconnect
> as I understand, in a default threadedconnectionpool, closes all
> connections which are not currently in use. Since I am using DB.disconnect
> at the end of every thread, there can't be an instance that a connection
> wasn't used for 10 seconds and DB.disconnect wasn't called in between. So,
> why am I getting errors related to wait_timeout? It goes without saying
> that none of my threads take more than 10 seconds to complete the request
>
I thought I explained this in an earlier message. The connection is only
checked out for a minimum of time, it does not remain checked out to a
thread. If you have a web request with multiple database queries, each
database query could use a different connection. So if you call
DB.disconnect after the action, there's no guarantee that any of the
connections you used during the web request is not currently in use by a
different thread.
DB.disconnect is the wrong way to approach what you are doing. It's
designed to disconnect all of the databases connections, you just want the
a single one disconnected.
FWIW, what you are doing would work fine in single threaded mode, but I'm
guessing you don't want to run single threaded.
>
> And then there is one hypothetical problem that I want an answer to. Is
> there a way to get a situation like the one I am mentioning below handled
> by the sequel connection pool itself with the restriction of of not
> changing the wait_timeout at mysql level:
>
> get "/*" do
> DB.execute("statement 1")
> sleep(11)
> DB.execute("statement 2")
> end
>
> Connection pools like JNDI handle this using the validation query.
>
With Sequel, that may work fine, as you aren't necessarily going to have
the same connection used for both queries. Or if the same connection is
used by both queries, it could also be used by other threads while this
thread is sleeping, so that it won't be stale when it comes to execute the
second statement.
Were I you, I would try to get the wait_timeout setting changed. Whoever
is forcing the wait_timeout to 10 is incompetent unless they have a good
reason for doing so. If that is impossible, I think your best bet is
modifying the connection pool to disconnect the connection on release to
the pool (instead of returning it to the pool), and then manually using
Database#synchronize if you want the same connection used by multiple
queries.
Sequel does not validate connections on pool checkout. It assumes
connections are valid, and raises a DatabaseDisconnectError if the database
raises an exception if it can detect a disconnection. The reason for this
is that it is not possible in the general case to ensure the state of the
new connection matches the state of the disconnected connection, so even if
a connection could be detected as invalid, disconnecting it and replacing
it with another connection would not be guaranteed to work. You can try
rescuing DatabaseDisconnectError and retrying, but that's a bad idea unless
what you are retrying is idempotent. Even then, retrying will only work in
certain cases and not other cases.
Consider the following code:
get "/*" do
DB.transaction do
DB.execute("statement 1")
sleep(11)
DB.execute("statement 2")
end
end
This is impossible to work with a wait_timeout of 10, and there's nothing
that can be done. Since you are using a transaction, you need to keep the
same connection in use the entire time. You can't disconnect and
reconnect, since the new connection will not be inside the transaction.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/dDp4lPoHXPoJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
