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
Zope3-dev mailing list
Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com

Reply via email to