# Using ActiveRecord sessions causes loss of MySQL connection **URL:** \ **Category:** Rails\ **Created:** [December 21, 2006, 5:57pm UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141 "2006-12-21T17:57:12Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 21, 2006, 5:57pm UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/1 "2006-12-21T17:57:12Z")
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 ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 21, 2006, 6:37pm UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/2 "2006-12-21T18:37:01Z")
On Dec 21, 9:57 am, Adam B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](http://scott.elitists.net/sessions.html) Especially take a look at this graph: [http://scott.elitists.net/img/sessions.png](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 ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 21, 2006, 6:45pm UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/3 "2006-12-21T18:45:24Z")
> 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 ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 23, 2006, 5:25am UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/4 "2006-12-23T05:25:28Z")
> 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 ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 21, 2006, 7:57pm UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/5 "2006-12-21T19:57:16Z")
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Adam B. 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](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](http://enigmail.mozdev.org) iD8DBQFFitjAMyx0fW1d8G0RAs+OAJ9XO9bBmHnyOXxzmmWfQH76Ccy8kACfZhBV WCdqOd/tbFNTZc/hrCIEWL8= =0f7D -----END PGP SIGNATURE----- ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 23, 2006, 7:14am UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/6 "2006-12-23T07:14:17Z")
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 ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 23, 2006, 6:44am UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/7 "2006-12-23T06:44:25Z")
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Adam B. 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](http://enigmail.mozdev.org) iD8DBQFFjMOFMyx0fW1d8G0RAvrnAJ9vGfPgNNGkUoGJ9UpAlk7gmM1YYwCaAtgk KTr6A8VRTxhwsIGL01I5ULI= =/tjX -----END PGP SIGNATURE----- ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 27, 2006, 7:05pm UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/8 "2006-12-27T19:05:14Z")
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 ---
**Author:** ![adambeazley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/5f9b8f/32.png) [@adambeazley](https://www.ruby-forum.com/u/adambeazley)\ **Post date:** [December 28, 2006, 1:16am UTC](https://www.ruby-forum.com/t/using-activerecord-sessions-causes-loss-of-mysql-connection/80141/9 "2006-12-28T01:16:26Z")
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