Rails - Dec 2006 - Using ActiveRecord sessions causes loss of MySQL connection

On the advice of AWDWR2, I switched from using PStore for my sessions to
using ActiveRecord. Now on about 50% of page loads I get the following
error:

Mysql::Error: Lost connection to MySQL server during query: SELECT * ...

Nothing has changed in my database, so this is clearly a result of the
switch to ActiveRecord sessions. (Though I note that the query
referenced in the error message is a page query and not the session
query. However, I do get the occasional:

    Application error

    Change this error message for exceptions thrown outside of an action
(like in
    Dispatcher setups or broken Ruby code) in public/500.html

as well, which I presume is a database failure when reading the
session.)

Any ideas why this would be happening? I''m Ruby 1.8.5, Rails 1.1.6, OS
X.

Thanks!

/afb

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 21, 9:57 am, Adam Block
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> On the advice of AWDWR2, I switched from using PStore for my sessions to
> using ActiveRecord. Now on about 50% of page loads I get the following
> error:
>
Before jumping on the ActiveRecord bandwagon you might want to take a
look at this reference:
http://scott.elitists.net/sessions.html

Especially take a look at this graph:
http://scott.elitists.net/img/sessions.png

A MySQL AcitveRecord session store doesn''t consistently beat the PStore
default until the number of sessions exceeds 10,000.

Aaron


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> A MySQL AcitveRecord session store doesn''t consistently beat the
PStore
> default until the number of sessions exceeds 10,000.
That''s good enough to convince me to go back to PStore (though I note 
that I''m not having the same problem on my production FC5 machine). I 
can''t see having more than 10,000 sessions for quite a while (assuming
I
clean out the old session files on a reasonable schedule).

Thanks!

/afb

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Adam Block wrote:
> 
> On the advice of AWDWR2, I switched from using PStore for my sessions to
> using ActiveRecord. Now on about 50% of page loads I get the following
> error:
> 
> Mysql::Error: Lost connection to MySQL server during query: SELECT * ...
> 
> Nothing has changed in my database, so this is clearly a result of the
> switch to ActiveRecord sessions. 
I don''t think "clearly" can be used in this context. The
"Lost connection to MySQL server..." error that you most likely seeing
is
one where a dispatcher has an OLD mysql connection. Old being greater than 8
hours by default. After the timeout period MySQL
Server will kill the connection. ActiveRecord isn''t smart enough to
reconnect, instead it throws this error. This is a shortcoming
of current ActiveRecord MysqlAdapter implementation IMO.

You can fix this in a few different ways:
 1 - Change your default wait_timeout setting to 9999999999999999999,
Essentially you''ll never have a connection timeout.
 2 - Kill your dispatchers once every 8 hours, and let them respawn
 3 - Fix ActiveRecord

#3 is my favorite, but if you''re not up for that #2 or #1 will fix your
problem for now.

For MySQL settings please refer to
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

Zach
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFitjAMyx0fW1d8G0RAs+OAJ9XO9bBmHnyOXxzmmWfQH76Ccy8kACfZhBV
WCdqOd/tbFNTZc/hrCIEWL8=0f7D
-----END PGP SIGNATURE-----

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> I don''t think "clearly" can be used in this context. The
"Lost
> connection to MySQL server..." error that you most likely seeing is
> one where a dispatcher has an OLD mysql connection.
Zach, this is good information but doesn''t explain why I never have
this
problem when I am using PStore (where there of course is AR activity in 
the DB, just not session-related).

Also, and I wasn''t clear about this in my first posting, I can fix the 
problem by refreshing the page a few times; it always load eventually. 
So it doesn''t appear as if MySQL is refusing all requests. Though I
know
almost nothing about dispatchers; AWDWR has little to say about them. Is 
there a recommended way of doing your #2? I Googled and didn''t find 
anything declarative.

Thanks!

/afb

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Adam Block wrote:
> 
>> I don''t think "clearly" can be used in this context.
The "Lost
>> connection to MySQL server..." error that you most likely seeing
is
>> one where a dispatcher has an OLD mysql connection.
> 
> Zach, this is good information but doesn''t explain why I never
have this
> problem when I am using PStore (where there of course is AR activity in
> the DB, just not session-related).
I don''t know the exact scenario you''re hitting, but with
PStore you''re
not dealing with MySQL for session data. So you bypass having to hit
MySQL altogether. So even if the problem did exist you may not be seeing
it because of using PStore.
> 
> Also, and I wasn''t clear about this in my first posting, I can fix
the
> problem by refreshing the page a few times; it always load eventually.
This is probably the result of using a fresh dispatcher, or eventually
getting a good dispatcher. Keep reading for more information.
> So it doesn''t appear as if MySQL is refusing all requests. 
MySQL isn''t refusing requests. The "Lost connection to ... "
error
indicates that there was a connection between a MySQL client and server
already made, and that the client has sent a query to the server, but
either:
 a - never heard back from the server at all
 b - didn''t hear a complete message back from the server

"a" is usually the case because after 8 hours of inactivity from a
connection, the server will disregard any future requests.
>Though I know
> almost nothing about dispatchers; AWDWR has little to say about them.
When you run your rails application using Apache or Lighttpd you run
more then one instance of your application. If you do a "ps ax | grep
dispatcher" you''ll probably see multiple instances.

Rails is setup to run as a FastCGI process, which is a way that allows
Apache and Lighttpd to reuse existing processes for processing incoming
requests.

Based on your Apache/Lighttpd configuration you will have a minimum
number of dispatchers always running by default. When your server gets
hit by alot of people more dispatchers will be started (this is the
#{RAILS_ROOT}/public/dispatch.fcgi file) by Apache/Lighttpd. Those
dispatchers usually remain in existence for a set period of time, until
they''re not needed or forever. It depends on your configuration.

To give you a simplified example scenario of what I think might be
happening.

 - Apache starts, and spawns 3 dispatcher processes.
 - Each dispatcher starts up and creates a database connection to the server
 - Over the next 8 hours you never have more then 2 concurrent users
which mean the first two dispatcher''s will service those requests
 - After eight hours you get 3 concurrent users.
 - The third user goes to the third dispatcher process and you receive
the "Lost connection to ...." error.

Why does this happen? Because the third dispatcher created a connection
to the MySQL database over 8 hours ago, and the connection has
essentially timed out.
>  Is
> there a recommended way of doing your #2? 
I don''t know your setup or your requirements, and I am not necessarily
recommending that you do this, but I am giving you this information so
you can evaluate the needs of your environment and your application.

I would start with option #3 rather then #2. Increase the wait_timeout
in your mysql server configuration to 24 hours or even longer.

If you still need do #2, then create a cronjob which kills dispatcher
processes in the middle of the night. Your web server will respawn them.
This should take a split second to do this.

You should of course test any of this. Do note that I''ve seen varying
results for how efficiently Apache2 and Lighttpd respawns dispatcher
processes. On shared web hosts like dreamhost it can take up 15 minutes,
which is NOT ideal, but on actual production systems that I run it is
NOT a problem.

Zach

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFjMOFMyx0fW1d8G0RAvrnAJ9vGfPgNNGkUoGJ9UpAlk7gmM1YYwCaAtgk
KTr6A8VRTxhwsIGL01I5ULI=/tjX
-----END PGP SIGNATURE-----

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Zach --

This is all really useful information. It''s not the problem
I''m having,
which only occurs on my dev machine running WEBrick. None of the other 
components that will be there for my production machine, and that you 
detail so well, are part of the environment that is failing every other 
page load (on average).

So I will simply stay away from AR sessions for the development system 
and reread your post when I set up the final production environment. 
Thanks again for the detailed explanation.

/afb

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi Adam,

FWIW, I had the same problem when I switched to AR sessions.  After
researching and finding many suggested solutions, I found one that
worked for me.  I discovered that I was storing some large objects in
my sessions.  I changed my technique to only store the object ID of the
larger objects and my "dropped connections" problem ceased.  Of course
there is the added overhead of having to "re-find" the object as
needed, but for me at least this was a minor inconvenience.  Anyway, my
persisted session data hovers around 300 bytes.

Kendall


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

kendall wrote:
> I discovered that I was storing some large objects in
> my sessions.
Yes, that sounds right. I''m storing the logged-in user object and not 
just the user id. I''ll try changing it and see what happens. Thanks for
the suggestion.

/afb

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---