[Zope3-dev] rdb: Disappearing Connection

Christian Theune ct at gocept.com
Tue Dec 13 15:56:06 EST 2005


Hi,

Am Dienstag, den 13.12.2005, 13:47 -0500 schrieb 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"

I love that one. Really. And I had lots of that kind of trouble with
mysql lately. What a fabulous database. ;)

> 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.

ACK.

> 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.

MySQL has problems like that a lot but it might be that the Zope 3
adapter isn't as robust as it could be. (That's just guessing from my
point of view.)

> 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.

Well. First, the error would be something like an OperationalError (or
similar). Hmm. Not too distinguishable. Ideally we could:

- differentiate this kind of error from other OperationalErrors (like a
syntax error in your query)
- delete the _v_connection
- restart the transaction

Otherwise you might start over within a transaction that is out ouf sync
with the state of the mysql database.

Christian
-- 
gocept gmbh & co. kg - schalaunische str. 6 - 06366 koethen - germany
www.gocept.com - ct at gocept.com - phone +49 3496 30 99 112 -
fax +49 3496 30 99 118 - zope and plone consulting and development
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://mail.zope.org/pipermail/zope3-dev/attachments/20051213/02cb58ce/attachment.bin


More information about the Zope3-dev mailing list