Weird DB Session error, "Mysql::Error: Lost connection to MySQL server during query"

[Jonzo]

I've discovered something really weird in rails 1.2.3 (It could be
fixed in 2.0? but I don't know).

Recently I started getting the error "Mysql::Error: Lost connection to
MySQL server during query" in my application. It seemed like it had
popped up almost without relation to anything. There was a page in the
RoR wiki with possible solutions but none of them had worked (http://
wiki.rubyonrails.org/rails/pages/Mysql+Connection+Problems), it was
driving me crazy!

Anyway, one day the error message changed, and turned into something
like "data too long for field: sessions.data". Now this message was a
bit more obvious what was goin on! I was storing too much in my
session! And I hadn't realised that there was a limit to that...
Specifically what I was doing was storing my cart in the session, and
this was overloading the db field sometimes, but not all the time, and
only after I started playing with my site a lot.

I fixed the cart problem by turning it into an active record object,
and just storing the id in session. BAM! there were no more DB errors!
magic! no more lost connections, nada.

Until... I found another place I was storing objects in session - In
my wizards. Sometimes, if I am creating a particularly complex object,
I use a 'wizard' to do it. I do this by storing my object in session,
and slowly build onto it with each step/page. I'm trying to figure out
some way to do this without storing the whole object in session. Any
suggestions?

[Wyatt Greene]

In Ruby on Rails, four options come to mind for storing data between
HTTP requests:

* use Rails' session
* store the data in the database
* store the data on a file on the server
* store the data on the client's computer, such as in a JavaScript
variable

Why do you want to avoid storing the whole object in the session? Is
it slow? Or is it causing errors?

Cheers,
Wyatt Greene

[Jonzo]

Yeah, I discovered that when I store ActiveRecord objects in the
session, and the sessions are stored in the database, i get the
following error a lot:

Mysql::Error: Lost connection to MySQL server during query

I might be crazy, I know I have been in the past, but it went away
after I stopped storing so much stuff in session, and If I
deliberately start creating objects in session (by accessing all of my
wizards) then the errors start occurring again. The errors don't
discriminate against controllers/actions, once I have active record
objects stored in session I get them all over the site, like chicken
pox, only less chicken.

I might see if I can store the objects in the database instead... and
have some kind of "complete" flag that is set on save, after
validation, or something.

Thanks,
Jonathan.

[Wyatt Greene]

Hi Jonathan,

You probably already thought of this, but have you tried changing the
type of column that stores the session data (to, for example,
mediumtext)? Another idea is to check the MySQL error log when this
happens.

Good luck!
Wyatt Greene

[Jonzo]

Yeah I thought of doing that, but I don't think I will because it's
really only prolonging the problem for a little longer rather than
solving it...

Thanks for the luck! I'll probably need it ;-)

[Zeke Ki]

Jonzo wrote:
> Yeah I thought of doing that, but I don't think I will because it's
> really only prolonging the problem for a little longer rather than
> solving it...
>
> Thanks for the luck! I'll probably need it ;-)

Any "luck" getting this solved? I'm storing a simple object in session
(attr_accessors only) and this started happening as soon as I did.
--
Posted via http://www.ruby-forum.com/.

[Jonzo]

Well, yes and no.

No I never figured out the exact problem, but yes I worked around it,
and actually ended up with a better application because of it!

My main solution was that I stopped storing any kind of *active
record* object in session. Instead I only store id's in session and
retrieve the object from the database when it is required.

For things like wizards it was a little more complicated. I developed
a technique that I quite liked, but isn't totally DRY. I created an
extra model in my application called xyz_builder. So if you are
building an account it would be called account_builder. This allowed
me to completely separate complete and incomplete accounts, so I don't
need to worry about checking conditions every time I deal with
accounts.

This had a few other useful side effects:
- I can now save all changes made to builders without worrying about
consequences, which makes the wizard more usable.
- I can define a lot more wizard based logic and keep it separate
from the account model. E.g. I defined separate validation methods for
each step such as step_one_complete? So you can do whatever you want
in the controller and the view based on which steps are complete.
- It became much easier to test the creation of accounts and the
wizard through unit tests

and finally my controllers became very thin:

def step_1
if request.post?
account_builder.update_attributes(params[:account]) #
account_builder is a method that fetches the correct builder
redirect_to :action => :step_2 if account_builder.step_1_complete?
end
end

def step_2
if request.post?
account_builder.update_attributes(params[:account])
if account_builder.complete?
account = account_builder.create_account
redirect_to :action => :show, :id => account
end
end
end

Note: I just made up this example, so there may be errors, but it's
essentially what my controllers ended up looking like.