[Zope3-dev] rdb: Disappearing Connection

[Jim Washington]

I have an rdb connection that keeps disappearing.

I know the real problem is that the default wait_timeout and 
interactive_timeout for MySql 5.0 is set at 8 hours.  The site I am 
working on for development waits 8 hours overnight for me to start 
working again, then I get

"Mysql server has gone away"

or something like that. The easy way to refresh the connection is to 
restart the zope3 instance.

The question is

Where should this be handled?  Hooking a 
connection-check/reconnect-if-down in each method contacting the sql 
server seems to violate DRY.

So, should the connection check be in zope.app.rdb.ZopeCursor, or 
further up the chain in ZopeDatabaseAdapter or MySQLdb?  I'm thinking 
that connections disappearing after 8 hours idle is probably a good 
thing, so than any process that forgets to conn.close() eventually 
releases the connection back to the pool (zope is not the only thing 
that connects to this db).  I think that at the moment, this is specific 
to MySQL 5.0.  But it would not surprise me if other databases might 
adopt this sort of policy to reduce "out of handles" errors.

Spelunking a bit in the code, 
zope.app.rdb.ZopeDatabaseAdapter.isConnected() looks at whether 
_v_connection is present, not whether the connection is actually alive.  
If we fix this here, isConnected() perhaps should handle the case where 
the connection might be reaped by the back end db.

I know that I can set wait_timeout and interactive_timeout to higher 
numbers in my.cnf.  But let's say I have authentication looking at MySQL 
for data and I guess a too-small number for the timeout (what would be a 
good default, anyway?  My users are often at schools that take breaks 
for weeks at a time).  Then, several users attempt to log in and get the 
"contact Jim" page I provide for errors like this.  Then, of course, my 
telephone starts ringing. Not good.

Possibly, I could restart the instance every morning at 3AM.  But this 
is not my favorite solution.

Thoughts, anyone? Collector search for 'rdb' did not turn up anything, 
so I'll submit a collector issue if I see consensus that zope.app.rdb is 
the place to remedy this.

-Jim Washington