Forums

2013, 'Lost connection to MySQL server during query'

Hi... in my web2py app, I've been getting this 2013 error and also a 2006, 'MySQL server has gone away' error. Sporadically.

I think I've narrowed it down in my case. My batch code does some screen scraping and then writes its results to the database. By design -- part(1): it collates all of the pages it's going to download. This process can take 1 to 3 minutes, depending on the source website's load. Once that's all done, it starts with part (2): it downloads each page and writes it to the database.

Since this is a web2py app, the connection to the database is established before part (1), when the models are run. So the connection is established anywhere from 1 to 3 minutes before it's actually used for anything.

I get the 2013 and 2006 error as soon as part (2) starts -- not all the time, but usually at busier times of day. So what seems to be happening is that PA's MySQL server is configured to keep connections alive only for a minute or two? When the connection dies, my program fails too. I am using web2py's pooled connections, but I guess it doesn't matter because the connection that I was handed is no longer alive.

This would also explain why this problem only happens with my batch programs and never with my live app -- my understanding is that web2py by design runs its models --thus refreshing its connections-- with every http request.

Can you validate my hypothesis, and if it's not too much bother, can you raise the MySQL keepalive parameter for my database to 5 minutes? This would give me time to think about how to refactor my code. (I may have to run the web2py models after part (1), or find a way to ping or reconnect my database handle, or some other solution.)

I've seen several others have reported the 2013 and 2006 errors, so hopefully this hypothesis will help someone else. best -Ricardo

Thanks for the suggestion! I've bumped up the MySQL wait_timeout for the DB you're using to 300 seconds; you can double-check this by running

show global variables like "wait_timeout";

in a MySQL shell. If it's set to 300 then that should be enough to test this hypothesis.

@giles: Great. I see wait_timeout=300 now. Humor me: what was the previous wait_timeout value? I'll give you some feedback in a few days when it's run through high and low website loads. -Ricardo

It was 120 seconds -- enough for a web app, but perhaps not for the kind of batch processing you're doing. Looking forward to hearing back about whether it helps!

@giles, thanks for upping wait_timeout. I can now confirm that it helped. Now, I'm looking into the best way to refactor my code so I don't depend on the higher wait_timeout.