Pylons error - 'MySQL server has gone away'

Question!

[I hope this isn't too obscure… I'll ask the newsgroup if nobody knows here]

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
By : swilliams


Answers

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 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
By : swilliams


I'm tempted to point you to the Design Patterns book for this generic question :p

Seriously, I think the answer is no. You can't write extensible code by default, it will be both hard to write/extend and awfully inefficient (Mozilla started with the idea of being very extensible, used XPCOM everywhere, and now they realized it was a mistake and started to remove it where it doesn't make sense).

what makes sense to do is to identify the pieces of your system that can be meaningfully extended and support a proper API for these cases (e.g. language support plug-ins in an editor). You'd use the relevant patterns, but the specific implementation depends on your platform/language choice.

IMO, it also helps to use a dynamic language - makes it possible to tweak the core code at run time (when absolutely necessary). I appreciated that Mozilla's extensibility works that way when writing Firefox extensions.

By : Nickolay


This video can help you solving your question :)
By: admin