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.
Zope3-dev mailing list