[mod_python] 2006 "MySQL server has gone away" failures

Fred of UrlBit.Us wrote:
> Hello, all.
>
> I intend to use mod_python for a couple of major websites, but I am running 
> into an annoying problem.
>
> I've seen something similar posted elsewhere, but no firm resolution.
>
> My mod_python server will run just fine for a while following a restart of 
> Apache, but after some time I get the following OperationalError exception:
>
> OperationalError: (2006, 'MySQL server has gone away')
>
> I can't seem to nail this one down, or even why it would happen. I even tried 
> pinging the connection early on in the handler, but still no dice.
>
> A restart of the server eliminates the problem for a while. Also, sometimes 
> the problem goes away on its own (but comes back later). And of course, when 
> I am sitting there trying to reproduce the problem, it works perfectly!
>
> Any clues or suggestions would be extremely helpful.
>
> Setup: Apache 2, Python 2.5, mod_python 3.2.10
>
> stack dump:
>
> ERROR In PyClops plugin <module 'app.urlbit' 
> from '/home/urlbit/site/web/php/app/urlbit/__init__.py'> 
>             Traceback (most recent call last):
>   File "/home/ecms/py/pyclops/plugins/__init__.py", line 34, in 
> _application_dispatcher
>     if plug.handle(pyreq):
>   File "/home/urlbit/site/web/php/app/urlbit/__init__.py", line 52, in handle
>     psp.handle(pyreq)
>   File "/home/ecms/py/pyclops/psp.py", line 25, in handle
>     p.run()
>   File "/usr/local/lib/python2.5/site-packages/mod_python/psp.py", line 213, 
> in run
>     exec code in global_scope
>   File "/home/urlbit/site/web/create.html", line 100, in <module>
>   File "/usr/local/lib/python2.5/site-packages/MySQLdb/cursors.py", line 163, 
> in execute
>     self.errorhandler(self, exc, value)
>   File "/usr/local/lib/python2.5/site-packages/MySQLdb/connections.py", line 
> 35, in defaulterrorhandler
>     raise errorclass, errorvalue
> OperationalError: (2006, 'MySQL server has gone away')
>
>
>   
You didn't say what versions of MySQL and MySQLdb you are using.  If 
your connection is lost after being idle for 8 hours and it is version 
MySQL 5, then this from
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html is a
clue:

'''
The reconnect flag in the MYSQL structure is set to 0 by
mysql_real_connect(). Only those client programs which did not
explicitly set this flag to 0 or 1 after mysql_real_connect()
experience a change. Having automatic reconnection enabled by default
was considered too dangerous (due to the fact that table locks,
temporary tables, user variables, and session variables are lost after
reconnection).
'''

The problem is MySQLdb isn't setting the reconnect flag required by 
version MySQL 5.  A mysqldb patch can be found here:
http://sourceforge.net/tracker/index.php?func=detail&aid=1483074&group_id=22307&atid=374934

If you are restarting your application daily to take backups of your 
database or for some other reason, then an alternative is to just up the 
default timeout from 8 hours to 25 hours by adding a wait_timeout to  
/etc/my.cnf.

'''
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# rdh 2006-12-27 fix for OperationalError: (2006, 'MySQL server has gone 
away')
wait_timeout=90000
    
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
'''

The default MySQL timeout is set to 8 hours, you can see it by doing a:
   /usr/libexec/mysqld --help --verbose

Roger