Pylons error - 'MySQL server has gone away'

Question

I'm using Pylons (a python framework) to serve a simple web application, but it seems to die from time to time, with this in the error log: (2006, 'MySQL server has gone away')

I did a bit of checking, and saw that this was because the connections to MySQL were not being renewed. This shouldn't be a problem though, because the sqlalchemy.pool_recycle in the config file should automatically keep it alive. The default was 3600, but I dialed it back to 1800 because of this problem. It helped a bit, but 3600 should be fine according to the docs. The errors still happen semi-regularly. I don't want to lower it too much though and DOS my own database :).

Maybe something in my MySQL config is goofy? Not sure where to look exactly.

Other relevant details:

  Python 2.5
  Pylons: 0.9.6.2 (w/ sql_alchemy)
  MySQL: 5.0.51

I got this error after a script I wrote went AWOL and inserted 10000 rows into a table simultaneously. Seemed like a failsafe to me - not sure if that relates to your problem... — Andrew G. Johnson, Aug 11 '08 at 20:24

score 8 · Accepted Answer · answered Sep 05 '08 at 18:07

8

I think I fixed it. It's turns out I had a simple config error. My ini file read:

sqlalchemy.default.url = [connection string here]
sqlalchemy.pool_recycle = 1800

The problem is that my environment.py file declared that the engine would only map keys with the prefix: sqlalchemy.default so pool_recycle was ignored.

The solution is to simply change the second line in the ini to:

sqlalchemy.default.pool_recycle = 1800

answered Sep 05 '08 at 18:07

swilliams

48,060
27
100
130

Just recently saw some traffic on this post. It should be noted that this issue was relating to Pylons 0.9.6. This should be taken care of in Pylons 0.9.7, and the 'sqlalchemy.default' property goes away in favor of just plain 'sqlalchemy' – swilliams Feb 22 '10 at 19:32

Jon Bright · Answer 2 · 2011-11-04T11:33:54.620

You might want to check MySQL's timeout variables:

show variables like '%timeout%';

You're probably interested in wait_timeout (less likely but possible: interactive_timeout). On Debian and Ubuntu, the defaults are 28800 (MySQL kills connections after 8 hours), but maybe the default for your platform is different or whoever administrates the server has configured things differently.

AFAICT, pool_recycle doesn't actually keep the connections alive, it expires them on its own before MySQL kills them. I'm not familiar with pylons, but if causing the connections to intermittently do a SELECT 1; is an option, that will keep them alive at the cost of basically no server load and minimal network traffic. One final thought: are you somehow managing to use a connection that pylons thinks it has expired?

I can't say for certain; I don't know the internals of Pylons well enough yet. I haven't had a chance to poke through the documentation yet either (busy busy). I don't think the SELECT 1; thing is an option though, seems kind of kludgey too :). — swilliams, Aug 19 '08 at 23:06

Pylons error - 'MySQL server has gone away'

2 Answers2