merb_activerecord does not verify active connections
Steve Tooke
I've been seeing a problem with ActiveRecord connections to MySQL
becoming stale when there is no activity for a while, and reporting
the error message:
Mysql::Error: MySQL server has gone away
The Mysql server is still running but the ActiveRecord connection
seems to have timed out.
Merb::Orms::ActiveRecord#connect seems to have a keep-alive line in it
Thread
.new
{ loop
{ sleep(60*60); ::ActiveRecord::Base.verify_active_connections! } }
But this doesn't appear to be working properly.
Nick's LH ticket seems to demonstrate that the way the keep alive
thread is started, the active connections are not available, so they
can't be kept alive?
Does anybody have any idea why this might be happening, what we can do
to fix it, and how we an spec it to stop it creeping back in?
Cheers,
Steve
Julian Leviston
I think this usually happens when you don't have the ruby-mysql
bindings in place... but it's also something to do with versions
sometimes, and also something to do with the mysql socket symlink or
something.
Was that vague enough for you? ;-)
Google it, because I know one of my workmates had the same issue and
got around it with something he found from googling it on the front
page of the results.
Regards,
Julian.
Steve Tooke
On 28 Oct 2008, at 10:12, Julian Leviston wrote:
>
> I think this usually happens when you don't have the ruby-mysql
> bindings in place... but it's also something to do with versions
> sometimes, and also something to do with the mysql socket symlink or
> something.
>
> Was that vague enough for you? ;-)
No that's a great starting point. I'm wondering if it might be because
I have the mysql gem bundled with my app? It seems possible that this
might not be building properly on the server with a thor
merb:gems:redeploy - so perhaps I should just have it installed in the
servers system gems.
Cheers,
Steve
Martyn Loughran
the native mysql bindings.
IIRC the way Rails handles this is to check whether a connection is
active before the start of every request. However it isn't handled
internally by Active Record.
I solved the problem by putting something like this in a before filter
on Application (where Model can be any model class in your app)
unless Model.connection.active?
Model.connection.reconnect!
end
This unfortunately causes a slight bit of extra latency (but only
really noticeable if you're using a remote MySQL server.
Just a disclaimer: this fix has been in for several months and has
worked great. However I haven't removed it recently to see whether the
default merb behaviour has changed.
Hope this is useful,
Martyn
Julian Leviston
It happens with PHP, too. It's a mysql/os-level issue. Google it.
Julian.
Steve Tooke
ActiveRecord::Base has a verification_timeout attribute, which is used
to verify db connections, and ensure that they don't hit the MySQL
wait_timeout.
verification_timeout is only used by
ActiveRecord::Base#verify_active_connections! so it does nothing by
itself.
Rails calls this method in Dispatcher before dispatch
http://github.com/rails/rails/tree/master/actionpack/lib/action_controller/dispatcher.rb#L26
The merb fix is to start a thread to run this method at regular
intervals, this doesn't seem to be working as expected. Nick Dufresne
in the LH ticket shows that running in the thread AR reports no active
connections.
The fix that Martyn suggested is essentially the same idea, just
adding it as before filter on Application.
Is there a way that merb_activerecord can inject calling
ActiveRecord::Base#verify_active_connections! into the dispatch process?
Steve
Steve Tooke
The same issue can be recreated by restarting the mysql server after a
connection has been made.
If you add a before filter to the top of Application which calls
ActiveRecord::Base#verify_active_connections! this fixes the problem
(when the SQL server is restarted anyway - and I assume it will work
for the MySQL wait_timeout issue too).
So by calling verify_active_connections within the dispatch loop would
appear to solve the problem. As merb will currently fail if the MySQL
server is restarted I think this is a bug?
I've worked up a simple patch to merb_activerecord that appears to
solve this problem for me. It calls
ActiveRecord::Base#verify_active_connections! before the action is
dispatched. I'm not sure if this is the right way to go about it, and
I'm not sure how I can spec this?
Michael Klishin
> I've worked up a simple patch to merb_activerecord that appears to
> solve this problem for me. It calls
> ActiveRecord::Base#verify_active_connections! before the action is
> dispatched. I'm not sure if this is the right way to go about it, and
> I'm not sure how I can spec this?
I don't this it is the right way to go about it. MySQL drops
connection if no activity in certain period of time.
The real fix is to either increase this period of time in my.cnf (set
it to one week or something) or spawn a thread
that will ping MySQL every once in a while.
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
And remember, PostgreSQL never goes away from you.
--
MK
Michael Klishin
> I don't this it is the right way to go about it. MySQL drops
> connection if no activity in certain period of time.
This should read:
I don't *think* this it is the right way to go about it. MySQL drops
connection if *it sees* no activity in certain period of time.
--
MK
Michael Klishin
> http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
This link will get you straight to the variable description in the manual:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout
--
MK
Steve Tooke
> I don't this it is the right way to go about it. MySQL drops
> connection if no activity in certain period of time.
>
> The real fix is to either increase this period of time in my.cnf (set
> it to one week or something) or spawn a thread
> that will ping MySQL every once in a while.
>
> http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
The problem also seems to occur if the MySQL server is restarted.
ActiveRecord holds on to the connection, and unless you check its
still valid it tries to use it, without retrying. So increasing the
wait_timeout variable or pinging MySQL every once in a while wouldn't
help with this.
> And remember, PostgreSQL never goes away from you.
I've been meaning to look at PostgreSQL for a long time, perhaps this
will be the catalyst.
Michael Klishin
> The problem also seems to occur if the MySQL server is restarted.
> ActiveRecord holds on to the connection, and unless you check its
> still valid it tries to use it, without retrying. So increasing the
> wait_timeout variable or pinging MySQL every once in a while wouldn't
> help with this.
I am by no means a deployment expert but usually DB restart is
something that is quite special,
and servers cluster restart is not (god or monit do if for you as soon
as the app hits memory limit, and a few people are worried about it).
So doing a cluster restart is acceptable for simple applications that
only use
one DB and need no really high availability. This helps with
disconnection issue.
Adding an overhead to literally every request is something I'd rather
avoid in the framework. If it's the way you choose,
you can make a plugin out of this patch since it uses public Merb API
(just one hook), so you can keep it around and add it to several
applications.
I'd rather go with a thread that wakes up every 5 minutes or so to
ping MySQL server.
--
MK
Ezra Zygmuntowicz
I would say maybe a tradeoff is in order? Maybe keep a counter of
requests and verify_active_connections every 10-50 requests? This way
you would not incur the overhead on every hit but would be likely to
keep the connection going?
Or maybe we can add a global rescue for AR projects that catch the
connection errors and then call verify_active_connections?
I don't use AR so I guess it is up to the folks who do to decide what
way is best> it is a tradeoff between always reconnecting and always
paying the price of connecting overhead.
Cheers-
-Ezra
Ezra Zygmuntowicz
e...@engineyard.com
Martyn Loughran
Neither the periodic check nor the check with counter approach are
great because if the connection is lost it will not be re-established
for n seconds / n requests. The best approach I found which avoids the
per request overhead was to rescue the error and reconnect.
Unfortunately the gone away error is just a generic mysql error so
you'll be catching other errors too. I even tried to redirect to the
requested page after rescuing and reconnecting but, while that
sometimes works, sometimes you'll get infinite redirects which you'll
never hear about - not great...
In the end it was decided that even rescuing caused too many breakages
and we have had to settle for a per request check. (In my case I
needed to connect to a remote database for some models which I'm sure
increased the frequency of mysql "going away". So the tradeoff for
others may be different.)
Martyn
--
Martyn Loughran
Creating fresh, flexible and fast-growing web applications is our passion.
Steve Tooke
On 28 Oct 2008, at 17:32, Michael Klishin wrote:
> I am by no means a deployment expert but usually DB restart is
> something that is quite special,
> and servers cluster restart is not (god or monit do if for you as soon
> as the app hits memory limit, and a few people are worried about it).
> So doing a cluster restart is acceptable for simple applications that
> only use
> one DB and need no really high availability. This helps with
> disconnection issue.
You're right restarting the DB is unlikely to happen very often for
the majority of apps, so that issue is of less consequence
> Adding an overhead to literally every request is something I'd rather
> avoid in the framework. If it's the way you choose,
> you can make a plugin out of this patch since it uses public Merb API
> (just one hook), so you can keep it around and add it to several
> applications.
The overhead to every request was one thing I was worried about, and
especially as the problem is specific to AR and MySQL I think. I
suppose it could be useful to have a merb_activerecord_mysql_keepalive
plugin or something.
> I'd rather go with a thread that wakes up every 5 minutes or so to
> ping MySQL server.
I think perhaps my patch has diverted from the issue that there is
currently a thread that should verify connections every hour, but that
isn't working as intended.
Nick Dufresne has suggested a fix by moving the thread into
Merb::BootLoader.after_app_loads.
Steve